Table of contents

Benefits of PL/pgSQL

Its main benefit is running directly on the database server, reducing client/server network connections and data-transfer time.

Getting Started with an Example

Suppose user and organization tables both contain very large amounts of data, such as hundreds of millions of rows. We need a wide table containing all user information plus organization_name from the related organization.

We will use a PL/pgSQL procedure.

Consider the Challenges

Even before knowing PL/pgSQL syntax, we must understand the requirement: copy data from these two huge tables into another table. Since PL/pgSQL runs on the database server, such a large migration will take a long time and may use substantial I/O because hundreds of millions of inserts require continual writes. The database must also keep serving ordinary business traffic, which must not be disrupted. What can we do? Process in batches during selected time windows. Run for five or six hours overnight when traffic is low, then stop the migration during daytime demand.

Example Program

Based on this approach, write the following procedure:

CREATE OR REPLACE PROCEDURE POSTGRES.REDUNDANT_USER_ORG_INFO() LANGUAGE 'plpgsql' AS $BODY$
DECLARE
    -- Declare variables
	last_id bigint;
	row_num integer := 0;
	-- Declare a cursor
	row_cursor CURSOR FOR
	SELECT
	   t.*,
	org.organization_name
	FROM postgres.user t
	INNER JOIN postgres.organization org ON t.organization_id = org.id
	WHERE
	t.id > last_id
	ORDER BY t.id ASC
	;
BEGIN
	-- Query the synchronization log for the ID at the previous stopping point and assign it to last_id
	SELECT last_sync_id INTO last_id FROM postgres.redundant_user_org_info_migration_record;
	-- Log progress
	RAISE NOTICE 'start init redundant data at % and last_id is %', now(), last_id;
	-- Loop and commit
	FOR row_rec IN row_cursor LOOP
		last_id := row_rec.id;
		BEGIN
			-- INSERT SQL
			INSERT INTO postgres.user_org_info
			(
			   id,user_name,organization_name,creation_date,last_updated
			)
			VALUES
			   (
				   row_rec.id,row_rec.user_name,row_rec.organization_name,row_rec.creation_date,clock_timestamp()
			   );
			row_num := row_num + 1;
		EXCEPTION
			-- Ignore unique-constraint violations
			WHEN unique_violation THEN
			-- do nothing, just ignore
		END;
		-- Commit every 100,000 iterations and update the synchronization log
		IF mod(row_num, 100000) = 0 THEN
		RAISE NOTICE 'reach 100000 records commit transaction at % and update redundant_user_org_info_migration_record and last_id is % ', now(), last_id;
		UPDATE postgres.redundant_user_org_info_migration_record SET last_sync_id = last_id;
		COMMIT;
		END IF;
	END LOOP;
	-- Update the synchronization log when finished
	RAISE NOTICE 'final commit transaction at % and update redundant_user_org_info_migration_record and last_id is %',  now(), last_id;
	UPDATE postgres.redundant_contact_migration_record SET last_sync_id = last_id;
	COMMIT;
	RAISE NOTICE 'end init data...... at %', now();
END;
$BODY$;

Procedure Versus Function

A procedure has no return value. Our example does not need one, so we use a procedure.

A procedure does not have a return value. A procedure can therefore end without a RETURN statement. If you wish to use a RETURN statement to exit the code early, write just RETURN with no expression.

Official documentation

PL/pgSQL Structure

The example shows this basic structure:

DECLARE
	variable_name variable_type := default_value;
BEGIN
	-- SQL statements
	-- DECLARE-BEGIN-ENG structures can be nested here
ENG;

Variables in PL/pgSQL

Besides the variable usage above, the example also uses:

SELECT column_name INTO variable_name FROM query;

Because the program runs repeatedly, each run needs to know where the previous run stopped. The SQL query also sorts the results, ensuring processed data is not scanned repeatedly.

What if the first query returns no data? Either handle that in the program with a default value, or insert a row into the migration log in advance. We use the second approach, so the query always returns data. The documentation also describes the first approach. Official documentation

Cursors in PL/pgSQL

Developers may know that a huge JDBC result set can exhaust JVM memory if not read as a stream. Database cursors address a similar concern.

Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows.

Official documentation

Declare a Cursor

The declaration format is:

cursor_name CURSOR FOR query;

Use a Cursor with a FOR Loop

Usually, after declaring a cursor, you OPEN it, move through the result set with FETCH, and CLOSE it when finished. A FOR loop handles those steps internally for us.

FOR row_variable_name (called recordvar here) IN cursor_name LOOP
	-- Business SQL
END LOOP;

The FOR statement automatically opens the cursor, and it closes the cursor again when the loop exit

Official documentation

The variable recordvar is automatically defined as type record and exists only inside the loop (any existing definition of the variable name is ignored within the loop). Each row returned by the cursor is successively assigned to this record variable and the loop body is executed

The row variable returned by the FOR loop is of record type.

Row Type Versus Record Type

Like JDBC ResultSet, query processing needs a variable to access each row and its fields. PL/pgSQL provides Row Type and Record Type for this.

Declaring a Row Type

name table_name%ROWTYPE;

Declaring a Row Record

name RECORD;

Once a Row Type is declared, its field types are fixed. A Row Record differs: the fields and their types are determined by the actual result row assigned to the variable.

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to

Official documentation

Transaction Handling in PL/pgSQL

Notice that the commit-every-100,000 logic is outside the nested BEGIN-END block, at the same level as that block within the FOR loop. Many readers might place it inside, which causes an error when executing the procedure.

We also do not explicitly start a transaction; we simply use COMMIT.

it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command

Official documentation

A final commit also occurs outside the loop. With 100,001 rows, the last row does not trigger the IF condition, so one final commit is required.

Exception Handling in PL/pgSQL

The program catches unique-constraint violations and does nothing. Our data may violate uniqueness, but we do not care about those rows. To keep processing, we choose not to roll back the transaction and allow it to continue. If your business requires a rollback or other action, handle it in the exception block. Official documentation

Error Codes and Exception Names

Real applications may need to handle exceptions beyond unique-constraint violations. Use the appendix in the official documentation to find the relevant ones. Official error-code documentation Use the Condition Name column to catch exceptions.

Logging in PL/pgSQL

Logging is useful not just for recording execution, but especially for debugging. If a PL/pgSQL program does not produce the expected result and we do not know what happened, print relevant information to locate the issue. Use the format in the example. Official documentation

Call the Procedure

We now understand the syntax, business logic, and reasons behind the code, and have debugged it. Next, run it.

CALL POSTGRES.REDUNDANT_USER_ORG_INFO();

postgres in this program is a schema I created. You can create your own with any name.

Call the Procedure on a Schedule

We have completed about 90% of the work. The final step is running the procedure in selected time windows. Could a scheduled job do this automatically, like crontab on Linux? Others have already considered this: use pg_cron. Its usage is outside this article’s scope.

Further Optimization Ideas

All our requirements are now met, which is satisfying. Can the program be optimized further? Both tables have hundreds of millions of rows and the program uses an inner join. Even with good indexes, joining two huge tables processes substantial data. user may also have many columns, so SQL execution can be lengthy. How can we improve it? The example SQL has a creation_data field from user. We can make a large table effectively smaller through partitioning, for example partitioning user by creation_date annually. Processing one year’s data per query should then be fast. This is only an idea. What if one year already has hundreds of millions of users? Then we must consider another approach. In my work, however, that many users generally accumulate over four or five years, or even six or seven, rather than one year. That is as far as this optimization discussion goes; it is just an approach to consider.

References

• Official PL/pgSQL documentation