Small changes to cleanup_schema() function

[gioagu]

Hi guys! First off: this is not a bug, but a suggestion/request for a small enhancement.

Use case:

1. I am a (city)db superuser. I create some users with ro and rw privileges.
2. I would like to allow users with rw privileges to truncate the database (e.g. via the cleanup_schema() function)
3. To do so, I must first grant a rw-user the TRUNCATE privileges for the citydb schema(s).
4. I do it and now, the rw-user can truncate tables.

PROBLEM:

The cleanup_schema() function is written a bit "old style" (i.e. pre PostgreSQL 10) and causes the operation to fail, because the rw-user is not the owner of the sequences. This is caused by these lines:

  FOR rec IN 
    SELECT sequence_name FROM information_schema.sequences where sequence_schema = 'citydb'
    AND sequence_name <> 'ade_seq'
    AND sequence_name <> 'schema_seq'
  LOOP
    EXECUTE format('ALTER SEQUENCE citydb.%I RESTART', rec.sequence_name);	
  END LOOP;

This limitation might be overcome (at least in PostgreSQL) by dropping the above-mentioned part and slightly modifying the TRUNCATE command as follows:

EXECUTE format('TRUNCATE TABLE citydb.%I RESTART IDENTITY CASCADE', rec.table_name);

Basically, you add the "RESTART IDENTITY" part, which is possible (AFAIK) already since PostgreSQL 10.

What do you think?

G