Re: Wiki editor request: Fixing Sequences

Thanks Álvaro! I hadn't considered that the sequence could be in a
different schema. I was only using the `quote_foo` functions because the
existing query uses it, but using format definitely makes it much easier to
read. I'm not totally clear on what needs to be cast to a regclass, though.
Would you mind elaborating on that? All of the oid comparisons are to other
oid types, so I didn't think there was a need to cast. But this is getting
a bit outside of my scope of knowledge on this stuff.

Here's the updated query using format and the correct schema:

SELECT
format('SELECT SETVAL(''%I.%I'', COALESCE(MAX(%I), 1) ) FROM %I.%I;',
SEQUENCE_SCHEMAS.nspname, SEQUENCES.relname, COLUMNS.attname,
TABLE_SCHEMAS.nspname, TABLES.relname)
FROM pg_class SEQUENCES
INNER JOIN pg_namespace SEQUENCE_SCHEMAS ON SEQUENCE_SCHEMAS.oid =
SEQUENCES.relnamespace
INNER JOIN pg_depend ON pg_depend.objid = SEQUENCES.oid
INNER JOIN pg_attribute COLUMNS ON COLUMNS.attrelid = pg_depend.refobjid
AND COLUMNS.attnum = pg_depend.refobjsubid
INNER JOIN pg_class TABLES ON TABLES.oid = COLUMNS.attrelid
INNER JOIN pg_namespace TABLE_SCHEMAS ON TABLE_SCHEMAS.oid =
TABLES.relnamespace
WHERE SEQUENCES.relkind = 'S';

Thanks,

Joe

On Tue, Mar 27, 2018 at 8:16 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Joe Krill wrote:
> > Hello,
> >
> > My wiki account name is JoeK
> >
> > I was hoping to share an updated Fixing Sequences query (
> > https://wiki.postgresql.org/wiki/Fixing_Sequences). We tried to use the
> > query as it currently stands and ran into a handful of problems.
>
> You're an editor now. I think your proposed query will fail if you have
> a default sequence in a different schema than the table it's default
> for; watch out for that case. Also, I propose that instead of || and
> quote_foo(), the function "format" and cast of the pg_class.oid column to
> regclass result in easier-to-read code.
>
> Thanks
>
> > SELECT 'SELECT SETVAL(' ||
> > quote_literal(quote_ident(SCHEMAS.nspname) || '.' ||
> > quote_ident(SEQUENCES.relname)) ||
> > ', COALESCE(MAX(' ||quote_ident(COLUMNS.attname)|| '), 1) ) FROM ' ||
> > quote_ident(SCHEMAS.nspname)|| '.'||quote_ident(TABLES.relname)|| ';'
> > FROM pg_class SEQUENCES
> > INNER JOIN pg_namespace SCHEMAS ON SCHEMAS.oid = SEQUENCES.relnamespace
> > INNER JOIN pg_depend ON pg_depend.objid = SEQUENCES.oid
> > INNER JOIN pg_attribute COLUMNS ON COLUMNS.attrelid = pg_depend.refobjid
> > AND COLUMNS.attnum = pg_depend.refobjsubid
> > INNER JOIN pg_class TABLES ON TABLES.oid = COLUMNS.attrelid
> > WHERE SEQUENCES.relkind = 'S'
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>