Note that this insensitivity also applies if you modify a table via the special statements “ UPDATE/DELETE. This means that changes to the data underneath the cursor that happen after the cursor has started processing are not reflected in the data fetched from the cursor. The PostgreSQL equivalent of “insensitive” is “asensitive,” which denotes that the sensitivity is implementation-dependent. PostgreSQL cursors are always insensitive. ![]() Since a PostgreSQL statement always sees a stable snapshot of the data, this database doesn’t have sensitive cursors. This means that if you scroll back to an earlier row, you might get a different result. A sensitive cursor changes as the data underneath it changes. The SQL standard distinguishes SENSITIVE, INSENSITIVE and ASENSITIVE cursors. Here is a quick illustration of a scrollable cursor: BEGIN /* This cursor is scrollable by default */ DECLARE c SCROLL CURSOR FOR SELECT * FROM generate_series(1, 10) FETCH 5 FROM c generate_series ═════════════════ 1 2 3 4 5 (5 rows) MOVE BACKWARD 2 FROM c FETCH BACKWARD 2 FROM c generate_series FETCH ABSOLUTE 6 FROM c generate_series 2 1 (2 rows) /* The sixth result row */ ═════════════════ 6 (1 row) FETCH ALL FROM c generate_series ═════════════════ 7 8 9 10 (4 rows) COMMIT Cursor sensitivity Because the server must cache the entire result set, such cursors add overhead. Other, more complicated execution plans require the explicit keyword SCROLL for the cursor to become scrollable. Scrollable cursors for these types of queries are included at no extra charge because PostgreSQL computes query result rows “on demand” and streams them to the client. With this kind of execution plan, a query’s cursor is automatically scrollable, so you can move it backwards in the result set. Some execution plans, such as a sequential scan or a B-tree index scan, can be carried out both ways. SQL cursors are closed with the CLOSE statement, or by the end of the transaction. Like PL/pgSQL, SQL also has a MOVE statement that moves the cursor position without retrieving rows. There is also an SQL statement FETCH that is more powerful than its PL/pgSQL equivalent, in that it can fetch more than one row at a time. ASENSITIVE and INSENSITIVE are redundant in PostgreSQL and are there for SQL standard compatibility.WITH HOLDmakes a cursor that won't close on its own when a transaction is finished.SCROLL means that you can move the cursor position backwards to fetch the same rows several times.BINARY will fetch the results in the internal binary format, which may be useful if you want to read bytea columns and avoid the overhead of escaping them as strings.Here is a brief explanation of each choice: Read more MongoDB profiler and database performance problem diagnosis and identification The following is a simple example of PL/pgSQL code that makes use of a cursor: LANGUAGE plpgsql $$DECLARE and / CURSOR FOR SELECT table_schema, table_name FROM information_schema.tables, * declare and open * WHERE table_name LIKEs "old_%" AND table_schema = "mydata" text in the schema and names OPEN LOOP /* prevent SQL injection /* EXECUTE format('DROP TABLE%I.%I', v_schema, v_name) END LOOP /* get the next result row /* FETCH c INTO v_schema, v_name /* system variable FOUND is set by FETCH /* EXIT WHEN NOT FOUND /* prevent SQL injection /* Not required close the window end $ ![]() Another benefit of using a cursor is that you can run multiple SQL statements concurrently, which is typically not possible in a single database session. In procedural code on the client or in the database, cursors are especially helpful because they let you loop through the results of a query. A position within a result set is indicated by a cursor. A cursor, however, enables you to fetch the result rows one at a time. You receive the entire result set in one step when a regular query is executed. When a query is ready to be run, PostgreSQL makes a portal where the result rows can be fetched. We will also see the dangers involved and how to properly use WITH HOLD cursors in a PL/pgSQL procedure. ![]() This article describes how cursors and transactions interact and how WITH HOLD can work around their limitations. Feature article NPMD solutions play a key role in helping IT ops support increasingly complex technologies and services with network visibility, detection of performance issues and root cause analysisĬursors and transactions serve as the basic building blocks for creating database applications.The first of its kind, DPM provides decision support for each stage of the performance problem lifecycle ![]() DPM DPM is an innovative platform for IT production database performance management.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |