Добавим в конец журнала строку:
postgres$ echo 'work_mem = 8MB' >> /etc/postgresql/9.6/main/postgresql.conf
Обновляем конфигурацию:
student$ sudo pg_ctlcluster 9.6 main reload
Проверяем:
=> SELECT current_setting('work_mem') AS work_mem;
work_mem ---------- 8MB (1 row)
Запишем в файл ddl.sql команду на создание таблицы с ключевыми словами PostgreSQL (для этого можно использовать и любой текстовый редактор):
student$ cat >ddl.sql <<EOF CREATE TABLE keywords ( word text, category text, description text ); EOF
Проверим содержимое файла:
student$ cat ddl.sql
CREATE TABLE keywords ( word text, category text, description text );
Заполним таблицу keywords в файле popilate.sql:
student$ echo 'INSERT INTO keywords SELECT * FROM pg_get_keywords();' > populate.sql
Проверим содержимое файла:
student$ cat populate.sql
INSERT INTO keywords SELECT * FROM pg_get_keywords();
Запускаем psql, выполняем скрипты и проверяем записи в таблице:
=> \i ddl.sql
CREATE TABLE
=> \i populate.sql
INSERT 0 419
=> SELECT * FROM keywords LIMIT 10;
word | category | description -----------+----------+------------- abort | U | unreserved absolute | U | unreserved access | U | unreserved action | U | unreserved add | U | unreserved admin | U | unreserved after | U | unreserved aggregate | U | unreserved all | R | reserved also | U | unreserved (10 rows)
Журнал можно открыть любым редактором. Каждая запись в журнале начинается с даты (настройки журнала после установки из пакета) и может состоять из нескольких строк. Записи за сегодняшний день будут в конце файла.
Чтобы автоматизировать задачу, используем потоковый редактор sed, который находит первую начинающуюся с сегодяшней даты запись и выводит все оставшиеся до конца файла строки.
postgres$ sed -n '/^2017-08-25/,$p' /var/log/postgresql/postgresql-9.6-main.log
2017-08-25 17:48:51 MSK [1801-1] LOG: database system was interrupted; last known up at 2017-08-25 17:48:49 MSK 2017-08-25 17:48:52 MSK [1802-1] [unknown]@[unknown] LOG: incomplete startup packet 2017-08-25 17:48:52 MSK [1805-1] postgres@postgres FATAL: the database system is starting up 2017-08-25 17:48:52 MSK [1801-2] LOG: database system was not properly shut down; automatic recovery in progress 2017-08-25 17:48:52 MSK [1801-3] LOG: invalid record length at F/27CF586C: wanted 24, got 0 2017-08-25 17:48:52 MSK [1801-4] LOG: redo is not required 2017-08-25 17:48:52 MSK [1801-5] LOG: MultiXact member wraparound protections are now enabled 2017-08-25 17:48:52 MSK [1800-1] LOG: database system is ready to accept connections 2017-08-25 17:48:52 MSK [1809-1] LOG: autovacuum launcher started 2017-08-25 17:48:55 MSK [1900-1] student@student ERROR: database "data_logical" does not exist 2017-08-25 17:48:55 MSK [1900-2] student@student STATEMENT: DROP DATABASE data_logical; 2017-08-25 17:48:56 MSK [1933-1] student@data_logical ERROR: relation "c" does not exist at character 15 2017-08-25 17:48:56 MSK [1933-2] student@data_logical STATEMENT: SELECT * FROM c; 2017-08-25 17:48:56 MSK [2507-1] student@student ERROR: database "data_physical" does not exist 2017-08-25 17:48:56 MSK [2507-2] student@student STATEMENT: DROP DATABASE data_physical; 2017-08-25 17:48:56 MSK [2507-3] student@student ERROR: tablespace "ts" does not exist 2017-08-25 17:48:56 MSK [2507-4] student@student STATEMENT: DROP TABLESPACE ts; 2017-08-25 17:48:57 MSK [2863-1] student@student ERROR: role "librarian" does not exist 2017-08-25 17:48:57 MSK [2863-2] student@student STATEMENT: DROP ROLE librarian; 2017-08-25 17:48:57 MSK [2863-3] student@student ERROR: role "storekeeper" does not exist 2017-08-25 17:48:57 MSK [2863-4] student@student STATEMENT: DROP ROLE storekeeper; 2017-08-25 17:48:57 MSK [2863-5] student@student ERROR: role "buyer" does not exist 2017-08-25 17:48:57 MSK [2863-6] student@student STATEMENT: DROP ROLE buyer; 2017-08-25 17:48:59 MSK [3542-1] student@student ERROR: role "librarian" does not exist 2017-08-25 17:48:59 MSK [3542-2] student@student STATEMENT: DROP ROLE librarian; 2017-08-25 17:48:59 MSK [3542-3] student@student ERROR: role "storekeeper" does not exist 2017-08-25 17:48:59 MSK [3542-4] student@student STATEMENT: DROP ROLE storekeeper; 2017-08-25 17:48:59 MSK [3542-5] student@student ERROR: role "buyer" does not exist 2017-08-25 17:48:59 MSK [3542-6] student@student STATEMENT: DROP ROLE buyer; 2017-08-25 17:49:00 MSK [3958-1] student@student ERROR: database "bstore_interaction" does not exist 2017-08-25 17:49:00 MSK [3958-2] student@student STATEMENT: DROP DATABASE bstore_interaction; 2017-08-25 17:49:55 MSK [4451-1] student@student ERROR: database "sql_func" does not exist 2017-08-25 17:49:55 MSK [4451-2] student@student STATEMENT: DROP DATABASE sql_func; 2017-08-25 17:49:57 MSK [5077-1] student@student ERROR: database "sql_row" does not exist 2017-08-25 17:49:57 MSK [5077-2] student@student STATEMENT: DROP DATABASE sql_row; 2017-08-25 17:49:58 MSK [5652-1] student@student ERROR: database "plpgsql_introduction" does not exist 2017-08-25 17:49:58 MSK [5652-2] student@student STATEMENT: DROP DATABASE plpgsql_introduction; 2017-08-25 17:50:04 MSK [7280-1] student@plpgsql_dynamic ERROR: a column definition list is required for functions returning "record" at character 15 2017-08-25 17:50:04 MSK [7280-2] student@plpgsql_dynamic STATEMENT: SELECT * FROM matrix(); 2017-08-25 17:50:07 MSK [8478-1] student@plpgsql_exceptions ERROR: division_by_zero 2017-08-25 17:50:07 MSK [8478-2] student@plpgsql_exceptions CONTEXT: PL/pgSQL function inline_code_block line 7 at RAISE 2017-08-25 17:50:07 MSK [8478-3] student@plpgsql_exceptions STATEMENT: DO $$ BEGIN BEGIN RAISE NOTICE 'Операторы try'; -- RAISE NOTICE '...исключение, которое не обрабатывается'; RAISE division_by_zero; EXCEPTION WHEN no_data_found THEN RAISE NOTICE 'Операторы catch'; END; RAISE SQLSTATE 'ALLOK'; EXCEPTION WHEN others THEN RAISE NOTICE 'Операторы finally'; IF SQLSTATE != 'ALLOK' THEN RAISE; END IF; END; $$; 2017-08-25 17:50:09 MSK [8765-1] student@bookstore ERROR: new row for relation "books" violates check constraint "books_onhand_qty_check" 2017-08-25 17:50:09 MSK [8765-2] student@bookstore DETAIL: Failing row contains (1, Сказка о царе Салтане, -71). 2017-08-25 17:50:09 MSK [8765-3] student@bookstore CONTEXT: SQL statement "UPDATE books SET onhand_qty = onhand_qty + NEW.qty_change WHERE book_id = NEW.book_id" PL/pgSQL function update_onhand_qty() line 3 at SQL statement 2017-08-25 17:50:09 MSK [8765-4] student@bookstore STATEMENT: INSERT INTO operations(book_id, qty_change) VALUES (1,-100); 2017-08-25 17:50:11 MSK [10146-1] student@bookstore ERROR: role "employee" already exists 2017-08-25 17:50:11 MSK [10146-2] student@bookstore STATEMENT: CREATE ROLE employee LOGIN PASSWORD 'employee'; 2017-08-25 17:50:12 MSK [10602-1] student@student ERROR: database "access_overview" does not exist 2017-08-25 17:50:12 MSK [10602-2] student@student STATEMENT: DROP DATABASE access_overview; 2017-08-25 17:50:12 MSK [10602-3] student@student ERROR: role "r" does not exist 2017-08-25 17:50:12 MSK [10602-4] student@student STATEMENT: DROP ROLE r; 2017-08-25 17:50:13 MSK [1800-2] LOG: received fast shutdown request 2017-08-25 17:50:13 MSK [1800-3] LOG: aborting any active transactions 2017-08-25 17:50:13 MSK [1809-2] LOG: autovacuum launcher shutting down 2017-08-25 17:50:13 MSK [1806-1] LOG: shutting down 2017-08-25 17:50:13 MSK [1800-4] LOG: database system is shut down 2017-08-25 17:50:14 MSK [10809-1] LOG: database system was shut down at 2017-08-25 17:50:13 MSK 2017-08-25 17:50:14 MSK [10809-2] LOG: MultiXact member wraparound protections are now enabled 2017-08-25 17:50:14 MSK [10808-1] LOG: database system is ready to accept connections 2017-08-25 17:50:14 MSK [10813-1] LOG: autovacuum launcher started 2017-08-25 17:50:15 MSK [10815-1] [unknown]@[unknown] LOG: incomplete startup packet 2017-08-25 17:50:17 MSK [10946-1] r@access_overview ERROR: permission denied for relation t 2017-08-25 17:50:17 MSK [10946-2] r@access_overview STATEMENT: UPDATE s.t SET key = key+1 WHERE key = 2; 2017-08-25 22:13:07 MSK [10808-2] LOG: received SIGHUP, reloading configuration files 2017-08-25 22:13:08 MSK [10808-3] LOG: received SIGHUP, reloading configuration files 2017-08-25 22:13:08 MSK [10808-4] LOG: parameter "work_mem" changed to "16MB" 2017-08-25 22:13:09 MSK [10808-5] LOG: received SIGHUP, reloading configuration files 2017-08-25 22:13:09 MSK [10808-6] LOG: parameter "work_mem" removed from configuration file, reset to default 2017-08-25 22:13:10 MSK [13406-1] student@student ERROR: database "mvcc_overview" does not exist 2017-08-25 22:13:10 MSK [13406-2] student@student STATEMENT: drop database mvcc_overview; 2017-08-25 22:13:11 MSK [14011-1] student@student ERROR: database "wal_overview" does not exist 2017-08-25 22:13:11 MSK [14011-2] student@student STATEMENT: drop database wal_overview; 2017-08-25 22:13:17 MSK [14562-1] student@test ERROR: relation "t" does not exist at character 15 2017-08-25 22:13:17 MSK [14562-2] student@test STATEMENT: SELECT * FROM t; 2017-08-25 22:13:17 MSK [15233-1] student@test ERROR: cannot drop schema special because other objects depend on it 2017-08-25 22:13:17 MSK [15233-2] student@test DETAIL: table t depends on schema special 2017-08-25 22:13:17 MSK [15233-3] student@test HINT: Use DROP ... CASCADE to drop the dependent objects too. 2017-08-25 22:13:17 MSK [15233-4] student@test STATEMENT: DROP SCHEMA special; 2017-08-25 22:13:18 MSK [15429-1] student@student ERROR: database "test" does not exist 2017-08-25 22:13:18 MSK [15429-2] student@student STATEMENT: DROP DATABASE test; 2017-08-25 22:13:18 MSK [15429-3] student@student ERROR: tablespace "ts" does not exist 2017-08-25 22:13:18 MSK [15429-4] student@student STATEMENT: DROP TABLESPACE ts; 2017-08-25 22:13:20 MSK [16127-1] student@student ERROR: database "bookstore" does not exist 2017-08-25 22:13:20 MSK [16127-2] student@student STATEMENT: DROP DATABASE bookstore; 2017-08-25 22:13:20 MSK [16127-3] student@student ERROR: role "librarian" does not exist 2017-08-25 22:13:20 MSK [16127-4] student@student STATEMENT: DROP ROLE librarian; 2017-08-25 22:13:20 MSK [16127-5] student@student ERROR: role "storekeeper" does not exist 2017-08-25 22:13:20 MSK [16127-6] student@student STATEMENT: DROP ROLE storekeeper; 2017-08-25 22:13:21 MSK [16393-1] student@student ERROR: role "librarian" does not exist 2017-08-25 22:13:21 MSK [16393-2] student@student STATEMENT: DROP ROLE librarian; 2017-08-25 22:13:21 MSK [16393-3] student@student ERROR: role "storekeeper" does not exist 2017-08-25 22:13:21 MSK [16393-4] student@student STATEMENT: DROP ROLE storekeeper; 2017-08-25 22:13:21 MSK [16393-5] student@student ERROR: role "buyer" does not exist 2017-08-25 22:13:21 MSK [16393-6] student@student STATEMENT: DROP ROLE buyer; 2017-08-25 22:13:23 MSK [17228-1] student@bookstore ERROR: new row for relation "books" violates check constraint "books_onhand_qty_check" 2017-08-25 22:13:23 MSK [17228-2] student@bookstore DETAIL: Failing row contains (1, Сказка о царе Салтане, -71). 2017-08-25 22:13:23 MSK [17228-3] student@bookstore CONTEXT: SQL statement "UPDATE books SET onhand_qty = onhand_qty + NEW.qty_change WHERE book_id = NEW.book_id" PL/pgSQL function update_onhand_qty() line 3 at SQL statement 2017-08-25 22:13:23 MSK [17228-4] student@bookstore STATEMENT: INSERT INTO operations(book_id, qty_change) VALUES (1,-100); 2017-08-25 22:13:23 MSK [17428-1] student@bookstore ERROR: role "employee" already exists 2017-08-25 22:13:23 MSK [17428-2] student@bookstore STATEMENT: CREATE ROLE employee LOGIN PASSWORD 'employee'; 2017-08-25 22:13:23 MSK [17636-1] student@student ERROR: database "bstore_schema" does not exist 2017-08-25 22:13:23 MSK [17636-2] student@student STATEMENT: DROP DATABASE bstore_schema; 2017-08-25 22:13:31 MSK [18416-1] student@student ERROR: role "librarian" does not exist 2017-08-25 22:13:31 MSK [18416-2] student@student STATEMENT: DROP ROLE librarian; 2017-08-25 22:13:31 MSK [18416-3] student@student ERROR: role "storekeeper" does not exist 2017-08-25 22:13:31 MSK [18416-4] student@student STATEMENT: DROP ROLE storekeeper; 2017-08-25 22:13:34 MSK [19716-1] student@student ERROR: database "sqlfunc" does not exist 2017-08-25 22:13:34 MSK [19716-2] student@student STATEMENT: drop database sqlfunc; 2017-08-25 22:13:34 MSK [19744-1] student@sqlfunc ERROR: relation "t" does not exist at character 55 2017-08-25 22:13:34 MSK [19744-2] student@sqlfunc STATEMENT: CREATE FUNCTION fill() RETURNS void AS $$ INSERT INTO t SELECT random() FROM generate_series(1,3); $$ LANGUAGE SQL; 2017-08-25 22:13:34 MSK [19744-3] student@sqlfunc ERROR: cannot change return type of existing function 2017-08-25 22:13:34 MSK [19744-4] student@sqlfunc HINT: Use DROP FUNCTION fill() first. 2017-08-25 22:13:34 MSK [19744-5] student@sqlfunc STATEMENT: CREATE OR REPLACE FUNCTION fill() RETURNS bigint AS $$ INSERT INTO t SELECT random() FROM generate_series(1,3); SELECT count(*) FROM t; $$ LANGUAGE SQL; 2017-08-25 22:13:35 MSK [19744-6] student@sqlfunc ERROR: COMMIT is not allowed in a SQL function 2017-08-25 22:13:35 MSK [19744-7] student@sqlfunc CONTEXT: SQL function "do_commit" during startup 2017-08-25 22:13:35 MSK [19744-8] student@sqlfunc STATEMENT: SELECT do_commit(); 2017-08-25 22:13:35 MSK [19744-9] student@sqlfunc ERROR: relation "t" does not exist at character 26 2017-08-25 22:13:35 MSK [19744-10] student@sqlfunc QUERY: TRUNCATE t; INSERT INTO t SELECT random() FROM generate_series(1,nrows); SELECT count(*)::integer, avg(a) FROM t; 2017-08-25 22:13:35 MSK [19744-11] student@sqlfunc CONTEXT: SQL function "fill" during startup 2017-08-25 22:13:35 MSK [19744-12] student@sqlfunc STATEMENT: SELECT fill(10); 2017-08-25 22:13:36 MSK [19744-13] student@sqlfunc ERROR: could not determine polymorphic type because input has type "unknown" 2017-08-25 22:13:36 MSK [19744-14] student@sqlfunc STATEMENT: SELECT maximum('A','B'); 2017-08-25 22:13:36 MSK [19744-15] student@sqlfunc ERROR: invalid input syntax for integer: "A" at character 18 2017-08-25 22:13:36 MSK [19744-16] student@sqlfunc STATEMENT: SELECT maximum(1,'A'); 2017-08-25 22:13:36 MSK [19744-17] student@sqlfunc ERROR: function maximum(integer, integer) is not unique at character 8 2017-08-25 22:13:36 MSK [19744-18] student@sqlfunc HINT: Could not choose a best candidate function. You might need to add explicit type casts. 2017-08-25 22:13:36 MSK [19744-19] student@sqlfunc STATEMENT: SELECT maximum(10,20); 2017-08-25 22:13:46 MSK [22025-1] student@student ERROR: database "sqlline" does not exist 2017-08-25 22:13:46 MSK [22025-2] student@student STATEMENT: drop database sqlline; 2017-08-25 22:13:47 MSK [22053-1] student@sqlline ERROR: a column definition list is required for functions returning "record" at character 15 2017-08-25 22:13:47 MSK [22053-2] student@sqlline STATEMENT: SELECT * FROM make_seat('A',42); 2017-08-25 22:13:49 MSK [23712-1] student@student ERROR: database "plpgsql_intro" does not exist 2017-08-25 22:13:49 MSK [23712-2] student@student STATEMENT: drop database plpgsql_intro; 2017-08-25 22:13:50 MSK [23740-1] student@plpgsql_intro ERROR: invalid input syntax for integer: "a" at character 12 2017-08-25 22:13:50 MSK [23740-2] student@plpgsql_intro QUERY: SELECT 2 + 'a' 2017-08-25 22:13:50 MSK [23740-3] student@plpgsql_intro CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE 2017-08-25 22:13:50 MSK [23740-4] student@plpgsql_intro STATEMENT: DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$; 2017-08-25 22:13:51 MSK [24512-1] student@student ERROR: database "plpgsql_queries" does not exist 2017-08-25 22:13:51 MSK [24512-2] student@student STATEMENT: drop database plpgsql_queries; 2017-08-25 22:13:51 MSK [24540-1] student@plpgsql_queries ERROR: column reference "id" is ambiguous at character 8 2017-08-25 22:13:51 MSK [24540-2] student@plpgsql_queries DETAIL: It could refer to either a PL/pgSQL variable or a table column. 2017-08-25 22:13:51 MSK [24540-3] student@plpgsql_queries QUERY: SELECT id, code FROM t WHERE id = id 2017-08-25 22:13:51 MSK [24540-4] student@plpgsql_queries CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement 2017-08-25 22:13:51 MSK [24540-5] student@plpgsql_queries STATEMENT: DO $$ DECLARE id integer := 1; code text; BEGIN SELECT id, code INTO id, code FROM t WHERE id = id; RAISE NOTICE '%, %', id, code; END; $$; 2017-08-25 22:13:52 MSK [24540-6] student@plpgsql_queries ERROR: query returned more than one row 2017-08-25 22:13:52 MSK [24540-7] student@plpgsql_queries CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement 2017-08-25 22:13:52 MSK [24540-8] student@plpgsql_queries STATEMENT: DO $$ DECLARE r record; BEGIN SELECT id, code INTO STRICT r FROM t; RAISE NOTICE '%', r; END; $$; 2017-08-25 22:13:52 MSK [24540-9] student@plpgsql_queries ERROR: query returned no rows 2017-08-25 22:13:52 MSK [24540-10] student@plpgsql_queries CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement 2017-08-25 22:13:52 MSK [24540-11] student@plpgsql_queries STATEMENT: DO $$ DECLARE r record; BEGIN SELECT id, code INTO STRICT r FROM t WHERE false; RAISE NOTICE '%', r; END; $$; 2017-08-25 22:13:52 MSK [24540-12] student@plpgsql_queries ERROR: query returned more than one row 2017-08-25 22:13:52 MSK [24540-13] student@plpgsql_queries CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement 2017-08-25 22:13:52 MSK [24540-14] student@plpgsql_queries STATEMENT: DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' RETURNING * INTO r; RAISE NOTICE 'Изменили: %', r; END; $$; 2017-08-25 22:13:52 MSK [24540-15] student@plpgsql_queries ERROR: query returned no rows 2017-08-25 22:13:52 MSK [24540-16] student@plpgsql_queries CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement 2017-08-25 22:13:52 MSK [24540-17] student@plpgsql_queries STATEMENT: DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = -1 RETURNING * INTO STRICT r; RAISE NOTICE 'Изменили: %', r; END; $$; 2017-08-25 22:13:53 MSK [25380-1] student@student ERROR: database "plpgsql_cursors" does not exist 2017-08-25 22:13:53 MSK [25380-2] student@student STATEMENT: drop database plpgsql_cursors; 2017-08-25 22:13:54 MSK [26186-1] student@student ERROR: database "plpgsql_dynamic" does not exist 2017-08-25 22:13:54 MSK [26186-2] student@student STATEMENT: drop database plpgsql_dynamic; 2017-08-25 22:13:55 MSK [26214-1] student@plpgsql_dynamic ERROR: a column definition list is required for functions returning "record" at character 15 2017-08-25 22:13:55 MSK [26214-2] student@plpgsql_dynamic STATEMENT: SELECT * FROM sel_city ('city_brn', 5); 2017-08-25 22:13:56 MSK [26671-1] student@student ERROR: database "plpgsql_arrays" does not exist 2017-08-25 22:13:56 MSK [26671-2] student@student STATEMENT: drop database plpgsql_arrays; 2017-08-25 22:13:57 MSK [26699-1] student@plpgsql_arrays ERROR: array subscript out of range 2017-08-25 22:13:57 MSK [26699-2] student@plpgsql_arrays CONTEXT: PL/pgSQL function inline_code_block line 6 at assignment 2017-08-25 22:13:57 MSK [26699-3] student@plpgsql_arrays STATEMENT: DO $$ DECLARE a integer[][] := '{{10,20,30},{100,200,300}}'; BEGIN RAISE NOTICE '%', a; a[4][4] := 1; END; $$ LANGUAGE plpgsql; 2017-08-25 22:13:58 MSK [27690-1] student@student ERROR: database "plpgsql_exceptions" does not exist 2017-08-25 22:13:58 MSK [27690-2] student@student STATEMENT: drop database plpgsql_exceptions; 2017-08-25 22:13:58 MSK [27718-1] student@plpgsql_exceptions ERROR: query returned no rows 2017-08-25 22:13:58 MSK [27718-2] student@plpgsql_exceptions CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement 2017-08-25 22:13:58 MSK [27718-3] student@plpgsql_exceptions STATEMENT: DO $$ DECLARE n integer; BEGIN SELECT id INTO STRICT n FROM t; END; $$; 2017-08-25 22:13:59 MSK [27718-4] student@plpgsql_exceptions ERROR: Сбой матрицы 2017-08-25 22:13:59 MSK [27718-5] student@plpgsql_exceptions DETAIL: При выполнении функции произошел непоправимый сбой матрицы 2017-08-25 22:13:59 MSK [27718-6] student@plpgsql_exceptions HINT: Обратитесь к системному администратору 2017-08-25 22:13:59 MSK [27718-7] student@plpgsql_exceptions CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE 2017-08-25 22:13:59 MSK [27718-8] student@plpgsql_exceptions STATEMENT: DO $$ BEGIN RAISE SQLSTATE 'ERR01' USING message = 'Сбой матрицы', detail = 'При выполнении функции произошел непоправимый сбой матрицы', hint = 'Обратитесь к системному администратору'; END; $$; 2017-08-25 22:13:59 MSK [27718-9] student@plpgsql_exceptions ERROR: division by zero 2017-08-25 22:13:59 MSK [27718-10] student@plpgsql_exceptions CONTEXT: PL/pgSQL function f3(integer) line 3 at RETURN PL/pgSQL function f2(integer) line 3 at RETURN PL/pgSQL function f1(integer) line 3 at RETURN 2017-08-25 22:13:59 MSK [27718-11] student@plpgsql_exceptions STATEMENT: SELECT f1(2); 2017-08-25 22:13:59 MSK [27718-12] student@plpgsql_exceptions ERROR: control reached end of function without RETURN 2017-08-25 22:13:59 MSK [27718-13] student@plpgsql_exceptions CONTEXT: PL/pgSQL function f3(integer) PL/pgSQL function f2(integer) line 3 at RETURN PL/pgSQL function f1(integer) line 3 at RETURN 2017-08-25 22:13:59 MSK [27718-14] student@plpgsql_exceptions STATEMENT: SELECT f1(2); 2017-08-25 22:14:00 MSK [27756-1] student@plpgsql_exceptions ERROR: duplicate key value violates unique constraint "categories_code_idx" 2017-08-25 22:14:00 MSK [27756-2] student@plpgsql_exceptions DETAIL: Key (code)=(games) already exists. 2017-08-25 22:14:00 MSK [27756-3] student@plpgsql_exceptions CONTEXT: SQL statement "INSERT INTO categories VALUES (code, description)" PL/pgSQL function change(text,text) line 9 at SQL statement 2017-08-25 22:14:00 MSK [27756-4] student@plpgsql_exceptions STATEMENT: SELECT change('games', 'Игры'); 2017-08-25 22:14:20 MSK [10810-1] LOG: checkpoints are occurring too frequently (22 seconds apart) 2017-08-25 22:14:20 MSK [10810-2] HINT: Consider increasing the configuration parameter "max_wal_size". 2017-08-25 22:14:39 MSK [29323-1] student@student ERROR: database "plpgsql_triggers" does not exist 2017-08-25 22:14:39 MSK [29323-2] student@student STATEMENT: DROP DATABASE plpgsql_triggers; 2017-08-25 22:14:46 MSK [29352-1] student@plpgsql_triggers ERROR: cannot update view "order_lines_v" 2017-08-25 22:14:46 MSK [29352-2] student@plpgsql_triggers DETAIL: Views that do not select from a single table or view are not automatically updatable. 2017-08-25 22:14:46 MSK [29352-3] student@plpgsql_triggers HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. 2017-08-25 22:14:46 MSK [29352-4] student@plpgsql_triggers STATEMENT: UPDATE order_lines_v SET description = 'Шуруп' WHERE id = 1; 2017-08-25 22:14:46 MSK [29352-5] student@plpgsql_triggers ERROR: Указанной позиции "Винт" не существует 2017-08-25 22:14:46 MSK [29352-6] student@plpgsql_triggers CONTEXT: PL/pgSQL function view_update() line 10 at RAISE 2017-08-25 22:14:46 MSK [29352-7] student@plpgsql_triggers STATEMENT: UPDATE order_lines_v SET description = 'Винт' WHERE id = 1 RETURNING *; 2017-08-25 22:14:47 MSK [30558-1] student@student ERROR: database "plpgsql_debug" does not exist 2017-08-25 22:14:47 MSK [30558-2] student@student STATEMENT: DROP DATABASE plpgsql_debug; 2017-08-25 22:15:06 MSK [30594-1] student@plpgsql_debug LOG: student,2017-08-25 22:15:06.084031+03,long_running_function. Stage 1/3... 2017-08-25 22:15:06 MSK [30594-2] student@plpgsql_debug CONTEXT: PL/pgSQL function raise_msg(text) line 7 at RAISE SQL statement "SELECT raise_msg('long_running_function. Stage 1/3...')" PL/pgSQL function long_running_function_2() line 3 at PERFORM 2017-08-25 22:15:06 MSK [30594-3] student@plpgsql_debug STATEMENT: SELECT long_running_function_2(); 2017-08-25 22:15:08 MSK [30594-4] student@plpgsql_debug LOG: student,2017-08-25 22:15:08.086518+03,long_running_function. Stage 2/3... 2017-08-25 22:15:08 MSK [30594-5] student@plpgsql_debug CONTEXT: PL/pgSQL function raise_msg(text) line 7 at RAISE SQL statement "SELECT raise_msg('long_running_function. Stage 2/3...')" PL/pgSQL function long_running_function_2() line 6 at PERFORM 2017-08-25 22:15:08 MSK [30594-6] student@plpgsql_debug STATEMENT: SELECT long_running_function_2(); 2017-08-25 22:15:11 MSK [30594-7] student@plpgsql_debug LOG: student,2017-08-25 22:15:11.087091+03,long_running_function. Stage 3/3... 2017-08-25 22:15:11 MSK [30594-8] student@plpgsql_debug CONTEXT: PL/pgSQL function raise_msg(text) line 7 at RAISE SQL statement "SELECT raise_msg('long_running_function. Stage 3/3...')" PL/pgSQL function long_running_function_2() line 9 at PERFORM 2017-08-25 22:15:11 MSK [30594-9] student@plpgsql_debug STATEMENT: SELECT long_running_function_2(); 2017-08-25 22:15:12 MSK [30594-10] student@plpgsql_debug LOG: student,2017-08-25 22:15:12.0902+03,long_running_function. Done. 2017-08-25 22:15:12 MSK [30594-11] student@plpgsql_debug CONTEXT: PL/pgSQL function raise_msg(text) line 7 at RAISE SQL statement "SELECT raise_msg('long_running_function. Done.')" PL/pgSQL function long_running_function_2() line 12 at PERFORM 2017-08-25 22:15:12 MSK [30594-12] student@plpgsql_debug STATEMENT: SELECT long_running_function_2(); 2017-08-25 22:15:45 MSK [30594-13] student@plpgsql_debug LOG: statement: SELECT get_count('pg_views'); 2017-08-25 22:15:45 MSK [30594-14] student@plpgsql_debug LOG: statement: SELECT trace('OFF'); 2017-08-25 22:15:45 MSK [30594-15] student@plpgsql_debug LOG: duration: 1.779 ms plan: Query Text: SELECT get_count('pg_tables'); Result (cost=0.00..0.26 rows=1 width=8) 2017-08-25 22:15:45 MSK [30594-16] student@plpgsql_debug LOG: duration: 0.485 ms plan: Query Text: SELECT COUNT(*) FROM pg_proc Aggregate (cost=94.37..94.38 rows=1 width=8) -> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..87.21 rows=2862 width=0) 2017-08-25 22:15:45 MSK [30594-17] student@plpgsql_debug CONTEXT: SQL statement "SELECT COUNT(*) FROM pg_proc" PL/pgSQL function get_count(text) line 10 at EXECUTE 2017-08-25 22:15:45 MSK [30594-18] student@plpgsql_debug LOG: duration: 1.163 ms plan: Query Text: SELECT get_count('pg_proc'); Result (cost=0.00..0.26 rows=1 width=8) 2017-08-25 22:15:46 MSK [32057-1] student@student ERROR: database "access_overview" does not exist 2017-08-25 22:15:46 MSK [32057-2] student@student STATEMENT: DROP DATABASE access_overview; 2017-08-25 22:15:46 MSK [32057-3] student@student ERROR: role "alice" does not exist 2017-08-25 22:15:46 MSK [32057-4] student@student STATEMENT: DROP ROLE alice; 2017-08-25 22:15:46 MSK [32057-5] student@student ERROR: role "bob" does not exist 2017-08-25 22:15:46 MSK [32057-6] student@student STATEMENT: DROP ROLE bob; 2017-08-25 22:15:47 MSK [32357-1] alice@access_overview ERROR: permission denied for database access_overview 2017-08-25 22:15:47 MSK [32357-2] alice@access_overview STATEMENT: CREATE SCHEMA alice; 2017-08-25 22:15:47 MSK [32596-1] bob@access_overview ERROR: permission denied for schema alice at character 15 2017-08-25 22:15:47 MSK [32596-2] bob@access_overview STATEMENT: SELECT * FROM alice.t1; 2017-08-25 22:15:48 MSK [32596-3] bob@access_overview ERROR: permission denied for relation t1 2017-08-25 22:15:48 MSK [32596-4] bob@access_overview STATEMENT: SELECT * FROM alice.t1; 2017-08-25 22:15:48 MSK [32596-5] bob@access_overview ERROR: permission denied for relation t1 2017-08-25 22:15:48 MSK [32596-6] bob@access_overview STATEMENT: DELETE FROM alice.t1; 2017-08-25 22:15:48 MSK [32596-7] bob@access_overview ERROR: permission denied for relation t2 2017-08-25 22:15:48 MSK [32596-8] bob@access_overview STATEMENT: SELECT * FROM alice.t2; 2017-08-25 22:15:48 MSK [32596-9] bob@access_overview ERROR: relation "t2" does not exist at character 32 2017-08-25 22:15:48 MSK [32596-10] bob@access_overview QUERY: SELECT count(*)::integer FROM t2; 2017-08-25 22:15:48 MSK [32596-11] bob@access_overview CONTEXT: SQL function "f" during inlining 2017-08-25 22:15:48 MSK [32596-12] bob@access_overview STATEMENT: SELECT alice.f(); 2017-08-25 22:15:48 MSK [32596-13] bob@access_overview ERROR: permission denied for function f 2017-08-25 22:15:48 MSK [32596-14] bob@access_overview STATEMENT: SELECT alice.f(); 2017-08-25 22:15:49 MSK [32596-15] bob@access_overview ERROR: permission denied for function f_new 2017-08-25 22:15:49 MSK [32596-16] bob@access_overview STATEMENT: SELECT alice.f_new(); 2017-08-25 22:15:50 MSK [1477-1] student@student ERROR: database "tools_overview" does not exist 2017-08-25 22:15:50 MSK [1477-2] student@student STATEMENT: DROP DATABASE tools_overview; 2017-08-25 22:15:50 MSK [10808-7] LOG: received SIGHUP, reloading configuration files 2017-08-25 22:15:50 MSK [10808-8] LOG: parameter "work_mem" changed to "8MB"