Обсуждение: Wiki editor request: Fixing Sequences

Поиск
Список
Период
Сортировка

Wiki editor request: Fixing Sequences

От
Joe Krill
Дата:
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. I suspect the problems stem from the fact that we have a "multi-tenant" app that uses schemas to partition each "tenant", so we have a lot of tables and columns with the same names, only in different schemas. The current query returned a lot of duplicate resulting queries for us. It also seems to actually return invalid queries in some cases. I've come up with a slightly simpler query that shouldn't produce duplicate or invalid results:

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'


So I was hoping to propose that as a possible replacement (or alternative).

Thanks!

-Joe

Re: Wiki editor request: Fixing Sequences

От
Alvaro Herrera
Дата:
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

Re: Wiki editor request: Fixing Sequences

От
Joe Krill
Дата:
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@2ndquadrant.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