Wiki editor request: Fixing Sequences

From: Joe Krill <joe(at)joekrill(dot)com>
To: pgsql-www(at)postgresql(dot)org
Subject: Wiki editor request: Fixing Sequences
Date: 2018-03-27 17:13:59
Message-ID: CAGjD_Eaq5haWco96dA1Y6Jxtd21=gx6L2b8gnj0rNLabcQskEA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-www

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

Responses

Browse pgsql-www by date

  From Date Subject
Next Message Alvaro Herrera 2018-03-27 20:16:55 Re: Wiki editor request: Fixing Sequences
Previous Message Ivan E. Panchenko 2018-03-23 13:53:36 Re: Postgres Pro build for windows