Sequence Generator

Let assume you need to get a specific number of records as a data source. The actual content of the records does not matter much. You can achieve this be the following query.

with recursive R(n) as (
      values(1)
      union all
      select N + 1
        from R
       where N < 100 -- replace 100 with the number of records you want
)
select N from R
;

Quick regression test statement

WITH base_new
     AS (SELECT -- TODO select column list
           FROM table_with_new_data
          WHERE -- TODO adapt where clause
                     )
   , base_old
     AS (SELECT -- TODO copy column list from new data table
           FROM table_with_old_data
          WHERE -- TODO copy where clause from new data table
                     )
   , new
     AS (SELECT 'new' AS src, t.*
           FROM base_new t
         MINUS
         SELECT 'new' AS src, t.*
           FROM base_old t)
   , old
     AS (SELECT 'old' AS src, t.*
           FROM base_old t
         MINUS
         SELECT 'old' AS src, t.*
           FROM base_new t)
   , uni
     AS (SELECT * FROM old
         UNION ALL
         SELECT * FROM new)
  SELECT /*+ parallel(4) */
        *
    FROM uni
ORDER BY 2 ASC, 3 ASC, 1 ASC;

Equivalent of Oracle database link

One can use a combination of foreign server (defining the host, port and database name), user mapping (defining user and password on an already created foreign server) and one of the extensions postgres_fdw (foreign data wrapper for external PostgreSQL servers) and dblink (older, less transparent and standards-compliant syntax but more flexible than postgres_fdw) and foreign table (defining tables not in the very database instance).

It might even be possible to db link to other database systems by other yet to write foreign data wrappers. Files can be sourced as tables already.

Sourcing files as database tables

This is possible by using foreign tables with the file foreign data wrapper.

LISTEN/NOTIFY

Do different sessions have there own listener instances, i. e. if session A makes a logger listen to its queue A with level A, does the making the logger listen to its queue B with a level B initialised by session B the logger listening to queue A using level B?
To be tried out... really, I do not see the point in the listener/notifier. It is not at all asynchronous, because the notify queue flushes only after a commit of a notifier to the listeners, and only for this notifiers messages. To me it wantseems only a matter of push instead of pull. The commit restriction makes it unsuitable for a logger as I do not want a logger waiting for a commit and, much more gravely, I certainly do not want a logger not getting a notifiers' entries when the transaction of the latter rolls back. An important point for the logger to me is, that it makes a trail to the point of a rollback such that finding the problem is half way done.

Log file location

Is it possible to change <LOG FILE> in a session such that its valid only for that session?

No, at least not straight forwardly for me.

Command:
set session log_directory to '/home/thiemo/tmp';

Error:
FEHLER:  Parameter „log_directory“ kann jetzt nicht geändert werden

I do not know what that's supposed to mean? A reload of the configuration did not make a difference. Maybe that it is not possible to change it by the set command.

For my purpuses this is not important as long as the parameter log_min_messages is not verbose. Debug and such message will not land in the sever log. Nonetheless, I do not feel totally at ease with this.

Logging level at the client

Is it possible to change CLIENT_MIN_MESSAGES in a session such that its valid only for that session?

Yes it is. And it can be done by the session user such that there is no need for a superuser function to achieve that.

Command used to alter the session in plain SQL: set session client_min_messages to debug;
Anonymous PLPGSQL block used to test in the different sessions:

do language PLPGSQL
$anonymous$
declare
     V_SQL                    text;
     V_REC                    record;
begin
      raise debug     'Session ALTERED';
      raise log       'Session ALTERED';
      raise info      'Session ALTERED';
      raise notice    'Session ALTERED';
      raise warning   'Session ALTERED';
      raise exception 'Session ALTERED';
end;
$anonymous$

There is however something strange. The levels for the raise command are different from the levels the client_min_message parameter takes.
Offending code:
do language PLPGSQL
$anonymous$
declare
     V_SQL                    text;
     V_REC                    record;
begin
      set session client_min_messages to exception;
      raise debug     'Session ALTERED';
      raise log       'Session ALTERED';
      raise info      'Session ALTERED';
      raise notice    'Session ALTERED';
      raise warning   'Session ALTERED';
      raise exception 'Session ALTERED';
end;
$anonymous$

Error message (sorry for the German babble ;-) ):

do language PLPGSQL
$anonymous$
declare
     V_SQL                    text;
     V_REC                    record;
begin
      set session client_min_messages to exception;
      raise debug     'Session ALTERED';
      raise log       'Session ALTERED';
      raise info      'Session ALTERED';
      raise notice    'Session ALTERED';
      raise warning   'Session ALTERED';
      raise exception 'Session ALTERED';
end;
$anonymous$

A very brief search with Ecosia with "postgresql log level raise level mapping" has not revealed a mapping table for the levels. I leave this to be tested or search for to a later date probably just before I get killed by boredom that is at the Sankt-Nimmerleins-Tag.