{"action":"created","issue":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/1366","repository_url":"https://api.github.com/repos/npgsql/npgsql","labels_url":"https://api.github.com/repos/npgsql/npgsql/issues/1366/labels{/name}","comments_url":"https://api.github.com/repos/npgsql/npgsql/issues/1366/comments","events_url":"https://api.github.com/repos/npgsql/npgsql/issues/1366/events","html_url":"https://github.com/npgsql/npgsql/issues/1366","id":191810123,"number":1366,"title":"Add option to control NpgsqlTransaction.Commit() timeout parameter in Npgsql 2.x","user":{"login":"janagan-net","id":23664252,"avatar_url":"https://avatars.githubusercontent.com/u/23664252?v=3","gravatar_id":"","url":"https://api.github.com/users/janagan-net","html_url":"https://github.com/janagan-net","followers_url":"https://api.github.com/users/janagan-net/followers","following_url":"https://api.github.com/users/janagan-net/following{/other_user}","gists_url":"https://api.github.com/users/janagan-net/gists{/gist_id}","starred_url":"https://api.github.com/users/janagan-net/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/janagan-net/subscriptions","organizations_url":"https://api.github.com/users/janagan-net/orgs","repos_url":"https://api.github.com/users/janagan-net/repos","events_url":"https://api.github.com/users/janagan-net/events{/privacy}","received_events_url":"https://api.github.com/users/janagan-net/received_events","type":"User","site_admin":false},"labels":[{"id":278720744,"url":"https://api.github.com/repos/npgsql/npgsql/labels/waiting%20for%20answer","name":"waiting for answer","color":"fbca04","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2016-11-26T15:04:05Z","updated_at":"2016-11-27T16:30:52Z","closed_at":null,"body":"The `NpgsqlTransaction.Commit()` command sets 20 second timeout and hasn't options to control it. Note that I found this issue in Npgsql 2.x version (3.x version wasn't tested). I know that it's not supported any more. So, I'm ready to make a pull request.\r\n\r\nI know that `COMMIT;` usually runs fast. So, in general case timeout is a good idea to prevent from deadlocks. But all `DEFERRED` constraints are checked in the commit stage, and it can take a lot of time. So, option to control commit timeout parameter is important.\r\n\r\nA small proof of concept example. Let's create PostgreSql tables:\r\n\r\n\tCREATE TABLE section\r\n\t(\r\n\t  id bigint NOT NULL,\r\n\t  section_name character varying NOT NULL,\r\n\t  CONSTRAINT section_pkey PRIMARY KEY (id)\r\n\t);\r\n\tCREATE TABLE section_items\r\n\t(\r\n\t  id bigint NOT NULL,\r\n\t  section_id bigint NOT NULL,\r\n\t  item_name character varying NOT NULL,\r\n\t  previous_item bigint,\r\n\t  CONSTRAINT section_items_pkey PRIMARY KEY (id),\r\n\t  CONSTRAINT section_items_previous_item_fkey FOREIGN KEY (previous_item)\r\n\t\t  REFERENCES section_items (id) MATCH SIMPLE\r\n\t\t  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,\r\n\t  CONSTRAINT section_items_section_id_fkey FOREIGN KEY (section_id)\r\n\t\t  REFERENCES section (id) MATCH SIMPLE\r\n\t\t  ON UPDATE RESTRICT ON DELETE CASCADE,\r\n\t  CONSTRAINT section_items_section_id_excl EXCLUDE \r\n\t  USING btree (section_id WITH =) WHERE (previous_item IS NULL)\r\n\t  DEFERRABLE INITIALLY DEFERRED,\r\n\t  CONSTRAINT section_items_section_id_previous_item_key UNIQUE (section_id, previous_item),\r\n\t  CONSTRAINT section_items_check CHECK (id <> previous_item)\r\n\t);\r\n\r\nAnd run C# program:\r\n\r\n\tusing Npgsql;\r\n\r\n\tnamespace NpgsqlTest\r\n\t{\r\n\t\tstatic class Program\r\n\t\t{\r\n\t\t\tstatic void Main(string[] args)\r\n\t\t\t{\r\n\t\t\t\t//We set command timeout to infinity here\r\n\t\t\t\tusing (var connection = new NpgsqlConnection(\"User ID=postgres;Password=***;Host=127.0.0.1;Port=5432;Database=test;Timeout=60;CommandTimeout=0;\"))\r\n\t\t\t\t{\r\n\t\t\t\t\tconnection.Open();\r\n\r\n\t\t\t\t\tusing (var transaction = connection.BeginTransaction())\r\n\t\t\t\t\t{\r\n\t\t\t\t\t\tusing (var command = new NpgsqlCommand(\r\n\t@\"-- Delete all data\r\n\tTRUNCATE TABLE section CASCADE;\r\n\r\n\t-- We need just one section\r\n\tINSERT INTO section(id, section_name)\r\n\tVALUES(1, 'Section1');\r\n\r\n\t-- Let's add the list of items\r\n\tINSERT INTO section_items(id, section_id, item_name, previous_item)\r\n\tSELECT id, 1, 'Item' || id, CASE WHEN id <= 1 THEN NULL ELSE id - 1 END FROM generate_series(1, 50000) AS s(id);\"\r\n\t\t\t\t\t\t\t, connection))\r\n\t\t\t\t\t\t{\r\n\t\t\t\t\t\t\tcommand.ExecuteNonQuery();\r\n\t\t\t\t\t\t}\r\n\r\n\t\t\t\t\t\ttransaction.Commit();\r\n\t\t\t\t\t}\r\n\r\n\t\t\t\t\tusing (var transaction = connection.BeginTransaction())\r\n\t\t\t\t\t{\r\n\t\t\t\t\t\tusing (var command = new NpgsqlCommand(\r\n\t@\"-- We would like to use NpgsqlConnection.BeginTransaction() command\r\n\t--BEGIN;\r\n\r\n\t-- Let's remove every second item...\r\n\tDELETE FROM section_items WHERE id % 2 = 0;\r\n\r\n\t-- ...and update positions of items in the list\r\n\tUPDATE section_items SET previous_item = previous_item - 1 WHERE previous_item IS NOT NULL;\r\n\r\n\t--We would like to use NpgsqlTransaction.Commit() command\r\n\t--COMMIT;\"\r\n\t\t\t\t\t\t\t, connection))\r\n\t\t\t\t\t\t{\r\n\t\t\t\t\t\t\tcommand.ExecuteNonQuery();\r\n\t\t\t\t\t\t}\r\n\r\n\t\t\t\t\t\t//We'll see the timeout NpgsqlException here because CommandTimeout doesn't apply to the NpgsqlTransaction.Commit() command\r\n\t\t\t\t\t\t//Hint: You can execute BEGIN; and COMMIT; commands manually whithout NpgsqlTransaction. It works fine.\r\n\t\t\t\t\t\ttransaction.Commit();\r\n\t\t\t\t\t}\r\n\t\t\t\t}\r\n\t\t\t}\r\n\t\t}\r\n\t}\r\n\r\nYou'll see this `NpgsqlException`: `A timeout has occured. If you were establishing a connection, increase Timeout value in ConnectionString. If you were executing a command, increase the CommandTimeout value in ConnectionString or in your NpgsqlCommand object.`\r\n\r\nHint: in this example `transaction.Commit();` is too long because index doesn't exist on `previous_item` column for `section_items_previous_item_fkey FOREIGN KEY`. So, if add the index, commit will run much faster.\r\n\r\n\tCREATE INDEX section_items_previous_item_idx\r\n\t  ON section_items\r\n\t  USING btree\r\n\t  (previous_item);\r\n"},"comment":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/comments/263131694","html_url":"https://github.com/npgsql/npgsql/issues/1366#issuecomment-263131694","issue_url":"https://api.github.com/repos/npgsql/npgsql/issues/1366","id":263131694,"user":{"login":"roji","id":1862641,"avatar_url":"https://avatars.githubusercontent.com/u/1862641?v=3","gravatar_id":"","url":"https://api.github.com/users/roji","html_url":"https://github.com/roji","followers_url":"https://api.github.com/users/roji/followers","following_url":"https://api.github.com/users/roji/following{/other_user}","gists_url":"https://api.github.com/users/roji/gists{/gist_id}","starred_url":"https://api.github.com/users/roji/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/roji/subscriptions","organizations_url":"https://api.github.com/users/roji/orgs","repos_url":"https://api.github.com/users/roji/repos","events_url":"https://api.github.com/users/roji/events{/privacy}","received_events_url":"https://api.github.com/users/roji/received_events","type":"User","site_admin":false},"created_at":"2016-11-27T16:30:51Z","updated_at":"2016-11-27T16:30:51Z","body":"@janagan-net thanks for reporting this, it's true that `Internal Command Timeout` is pretty rarely-used, I'm not totally surprised a bug report on this hasn't come in further. I'll definitely accept a PR for fixing this in 3.1.10 if you want to take a stab at it, otherwise let me know and I'll look into it.\r\n\r\nMy sympathies for having to support Windows XP and .NET 4.0... :) I hesitated at the time whether to attempt to keep .NET 4.0 support but ended up dropping it because new features were impossible to support (e.g. async). Regarding Npgsql 2.x, I'm probably not going to have the time to review/merge/release a PR, but you can definitely submit it."}}
{"actor":{"display_login":"roji"}}