[TIP] Easy (semi) automated way of updating your sequences

[ROCLASI]

I posted a tip on ServoyCamp about how to easily update all your database sequences in your PostgreSQL database in a (semi) automated fashion .

http://www.servoycamp.com/topics/tips/1 ... ences.html

Enjoy!

[Adelo Herrero]

Hi:

We can automate it with an stored procedure in plpgsql (view code) we call :
pgadmin/psql

Code: Select all

select update_seq();


Servoy

Code: Select all

databaseManager.getDataSetByQuery("serverName","select update_seq()");


Code of function.

Code: Select all

-- Function: update_seq()

-- DROP FUNCTION update_seq();

CREATE OR REPLACE FUNCTION update_seq()
  RETURNS void AS
$BODY$-- Update sequences in database.
DECLARE
   sql_result information_schema.tables%rowtype;
   query text;
BEGIN
   query:="SELECT 'SELECT setval('''||_schema||'.'||_seq||''', (SELECT MAX('||_column||') FROM '||_schema||'.'||_table||')+1); ' AS updatestring
      FROM (SELECT n.nspname AS _schema,
          c.relname AS _table,
          a.attname AS _column,
          SUBSTRING(d.adsrc FROM E'nextval\\(''([^'')]+)''') AS _seq
      FROM pg_attrdef       AS d
          JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
          JOIN pg_class     AS c ON c.oid = d.adrelid
          JOIN pg_namespace AS n ON n.oid = c.relnamespace
      WHERE adsrc LIKE 'nextval(''%') t;";

    FOR sql_result in EXECUTE(query) LOOP
      EXECUTE(sql_result);   
    END LOOP;
      
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_seq() OWNER TO postgres;
COMMENT ON FUNCTION update_seq() IS 'Update sequences in database.';


[ROCLASI]

Very nice!
Just load this into the template1 database and it will be available in every database you create after that.

But this doesn't take into account the very reason why I made it a semi-automatic solution. Namely the same sequence can be used on multiple columns.
I guess 99% of the Servoy users probably won't use a sequence for more than 1 column but for that 1% the result won't be satisfactory.
When would you reuse a sequence object? For example in some accounting solution you might want to have a unique serial number covering multiple tables so you reuse a sequence object for this.
Anyway , this is something Michael Fuhr (the guy I reference in the ServoyCamp post) also pointed out.
To solve this you would have to check if a sequence name is returned more than once and act accordingly like so:

Code: Select all

SELECT COALESCE(MAX(id), 0)+1 AS maxall FROM (
  SELECT MAX(tableOneColumn) AS id FROM tableOne
  UNION
  SELECT MAX(tableTwoColumn) AS id FROM tableTwo
) AS s;

So your actual update SQL will be like this:

Code: Select all

SELECT setval('sequencename',SELECT COALESCE(MAX(id), 0)+1 AS maxall FROM (SELECT MAX(tableOneColumn) AS id FROM tableOne UNION SELECT MAX(tableTwoColumn) AS id FROM tableTwo) AS s);

So I guess you can script this with a shell script, PL/pgSQL or even in a Servoy method like so:

Code: Select all

var _sServer = "myConnectionName",
    _sQuery = "SELECT n.nspname as _schema, \
                    c.relname as _table, \
                    a.attname as _column, \
                    SUBSTRING(d.adsrc FROM E'nextval\\\\(''([^'')]+)''') AS _seq \
                FROM pg_attrdef   AS d \
                    JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum \
                    JOIN pg_class     AS c ON c.oid = d.adrelid \
                    JOIN pg_namespace AS n ON n.oid = c.relnamespace \
                WHERE adsrc LIKE 'nextval(''%' \
                ORDER BY _seq",
    _ds = databaseManager.getDataSetByQuery(_sServer, _sQuery, null, -1),
    _aGetMax = new Array(),
    _sUpdateSQL,
    _oException;
if (_ds.getException()) {
    application.output("Error:" + _ds.getException().getMessage(), LOGGINGLEVEL.ERROR);
} else {
    for (var i = 1; i <= _ds.getMaxRowIndex(); i++) {
        _aGetMax.push("SELECT MAX(" + _ds.getValue(i, 3) + ") AS maxid FROM " + _ds.getValue(i, 1) + "." + _ds.getValue(i, 2));
        if (i == _ds.getMaxRowIndex() || _ds.getValue(i + 1, 4) != _ds.getValue(i, 4)) {
            _sUpdateSQL = "SELECT setval('" + _ds.getValue(i, 4) + "', (SELECT COALESCE(MAX(maxid), 0)+1 AS maxall FROM (" + _aGetMax.join(" UNION ") + ") AS t ));";
            _oException = databaseManager.getDataSetByQuery(_sServer, _sUpdateSQL, null, 1).getException();
            if (_oException) {
                application.output("Error:" + _oException.getMessage(), LOGGINGLEVEL.ERROR);
                application.output(_sUpdateSQL, LOGGINGLEVEL.ERROR);
            }
            _aGetMax = new Array();
        }
    }
}

UPDATE: I removed the use of the rawSQL plugin in the method. Since we use a SELECT this can be done with a regular getDataSetByQuery.

[Adelo Herrero]

Hi:
Not really thought about it, I always use unique secuence per column, so it is good to learn from the best

Best regards.

[Karel Broer]

We can automate it with an stored procedure in plpgsql

Nice job Adelo! Thanks for your tip!