Postgresql – what is “local block” in pg_stat_statements

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

Arrays types can only be built upon well-known types. Your option is to announce such a type to the system and cast the record to it before forming the array. Create a well-known composite type:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

I am guessing data types for lack of information here. Fill in your actual types.

Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.

This has a simple reason.

In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.

There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.