postgresql - करन - सेटिंग खोलें




जब यह सिंक से बाहर हो जाता है तो पोस्टग्रेस 'प्राथमिक कुंजी अनुक्रम को रीसेट कैसे करें? (17)

अनुक्रम नाम के बारे में किसी भी गलत धारणा से बचने के लिए pg_get_serial_sequence का उपयोग किया जा सकता है। यह अनुक्रम को एक शॉट में रीसेट करता है:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

या अधिक संक्षेप में:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

हालांकि यह फॉर्म खाली तालिकाओं को सही तरीके से संभाल नहीं सकता है, क्योंकि अधिकतम (आईडी) शून्य है, और न ही आप 0 को सेट कर सकते हैं क्योंकि यह अनुक्रम की सीमा से बाहर होगा। इसके लिए एक वर्कअराउंड ALTER SEQUENCE सिंटैक्स यानी का सहारा लेना है

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

लेकिन ALTER SEQUENCE सीमित उपयोग का है क्योंकि अनुक्रम नाम और पुनरारंभ मूल्य अभिव्यक्ति नहीं हो सकता है।

ऐसा लगता है कि सबसे अच्छा ऑल- setval समाधान तीसरा पैरामीटर के रूप में झूठ के साथ setval को कॉल करना है, जिससे हमें "उपयोग करने के लिए अगला मान" निर्दिष्ट करने की अनुमति मिलती है:

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

यह मेरे सभी बक्से लगाता है:

  1. वास्तविक अनुक्रम नाम हार्ड कोडिंग से बचाता है
  2. सही टेबल सही ढंग से संभालती है
  3. मौजूदा डेटा के साथ तालिकाओं को संभालता है, और अनुक्रम में एक छेद नहीं छोड़ता है

अंत में, ध्यान दें कि pg_get_serial_sequence केवल तभी काम करता है जब अनुक्रम कॉलम के स्वामित्व में है। यह मामला होगा यदि वृद्धिशील कॉलम को serial प्रकार के रूप में परिभाषित किया गया था, हालांकि अगर अनुक्रम मैन्युअल रूप से जोड़ा गया था तो यह अतिरिक्त ALTER SEQUENCE .. OWNED BY सुनिश्चित करना आवश्यक है ALTER SEQUENCE .. OWNED BY किया गया है।

यानी यदि टेबल निर्माण के लिए serial प्रकार का उपयोग किया गया था, तो यह सब काम करना चाहिए:

CREATE TABLE t1 (
  id serial,
  name varchar(20)
);

SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

लेकिन अगर अनुक्रम मैन्युअल रूप से जोड़े गए थे:

CREATE TABLE t2 (
  id integer NOT NULL,
  name varchar(20)
);

CREATE SEQUENCE t2_custom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);

ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence

SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'

-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

मैं इस समस्या में भाग गया कि मेरा प्राथमिक कुंजी अनुक्रम मेरी तालिका पंक्तियों के साथ समन्वयित नहीं है।

यही है, जब मैं एक नई पंक्ति डालता हूं तो मुझे एक डुप्लिकेट कुंजी त्रुटि मिलती है क्योंकि धारावाहिक डेटाटाइप में उल्लिखित अनुक्रम एक संख्या देता है जो पहले से मौजूद है।

ऐसा लगता है कि अनुक्रम को सही ढंग से बनाए रखने के आयात / पुनर्स्थापना के कारण नहीं होता है।


इससे पहले कि मैंने अभी तक कोड का प्रयास नहीं किया था: निम्नलिखित में मैंने क्लॉस और उपयोगकर्ता 457226 समाधानों के लिए एसक्यूएल कोड के लिए संस्करण पोस्ट किया जो मेरे पीसी [पोस्टग्रेस 8.3] पर काम करता था, क्लाउस वन और मेरे संस्करण के लिए कुछ छोटे समायोजन के साथ उपयोगकर्ता 457226 एक के लिए।

क्लाउस समाधान:

drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION  rebuilt_sequences() RETURNS integer as
$body$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
      constraint_column_usage.table_name as tablename,
      constraint_column_usage.table_name as tablename, 
      constraint_column_usage.column_name as columnname,
      replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
      from information_schema.constraint_column_usage, information_schema.columns
      where constraint_column_usage.table_schema ='public' AND 
      columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
      AND constraint_column_usage.column_name = columns.column_name
      AND columns.column_default is not null
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$body$ LANGUAGE plpgsql;

select rebuilt_sequences();

उपयोगकर्ता 457226 समाधान:

--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
  DECLARE seqname character varying;
          c integer;
  BEGIN
    select tablename || '_' || columnname || '_seq' into seqname;
    EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
    if c is null then c = 0; end if;
    c = c+1; --because of substitution of setval with "alter sequence"
    --EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
    EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
    RETURN nextval(seqname)-1;
  END;
$body$ LANGUAGE 'plpgsql';

select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
       reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname || '_seq'
where sequence_schema='public';

क्लाउस उत्तर सबसे उपयोगी है, थोड़ी सी मिस के लिए निष्पादित: आपको चुनिंदा कथन में DISTINCT जोड़ना होगा।

हालांकि, यदि आप सुनिश्चित हैं कि कोई तालिका + कॉलम नाम दो अलग-अलग तालिकाओं के बराबर नहीं हो सकता है, तो आप इसका भी उपयोग कर सकते हैं:

select sequence_name, --PG_CLASS.relname, PG_ATTRIBUTE.attname
       reset_sequence(split_part(sequence_name, '_id_seq',1))
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
     on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname
where sequence_schema='public';

जो मामले के लिए user457226 समाधान का विस्तार है जब कुछ इच्छुक कॉलम नाम 'आईडी' नहीं है।


जनता से सभी अनुक्रम रीसेट करें

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS 
$body$  
  DECLARE 
  BEGIN 
  EXECUTE 'SELECT setval( ''' 
  || tablename  
  || '_id_seq'', ' 
  || '(SELECT id + 1 FROM "' 
  || tablename  
  || '" ORDER BY id DESC LIMIT 1), false)';  
  END;  
$body$  LANGUAGE 'plpgsql';

select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
        where sequence_schema='public';

फिर भी एक और plpgsql - केवल max(att) > then lastval रीसेट करता है

do --check seq not in sync
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

लाइन - --execute format('alter sequence भी टिप्पणी --execute format('alter sequence सूची देगा, वास्तव में मूल्य को रीसेट नहीं करेगा


मेरा संस्करण पहले त्रुटि का उपयोग करता है, कुछ त्रुटि जांच के साथ ...

BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
 PERFORM 1
 FROM information_schema.sequences
 WHERE
  sequence_schema = _table_schema AND
  sequence_name = _sequence_name;
 IF FOUND THEN
  EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name  || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
 ELSE
  RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
 END IF;
END; 
$BODY$
 LANGUAGE 'plpgsql';

SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;

मैंने मिश्रित केस टेबल और कॉलम का उपयोग करके डेटाबेस के साथ काम करने के लिए djsnowsill के उत्तर को प्राप्त करने का प्रयास करने में एक घंटे बिताए, फिर आखिर में मैनुअल डार्वेउ की एक टिप्पणी के समाधान के कारण समाधान पर ठोकर खाई, लेकिन मैंने सोचा कि मैं इसे हर किसी के लिए थोड़ा स्पष्ट कर सकता हूं:

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$I'', %2$L),
        (SELECT COALESCE(MAX(%2$I)+1,1) FROM %1$I), false)',tablename,columnname);
END;
$body$  LANGUAGE 'plpgsql';

SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name) 
FROM information_schema.columns WHERE column_default like 'nextval%';

इसका लाभ है:

  • यह नहीं मानते कि आईडी कॉलम को एक विशेष तरीके से लिखा गया है।
  • यह नहीं मानते कि सभी तालिकाओं में अनुक्रम है।
  • मिश्रित केस टेबल / कॉलम नामों के लिए काम करना।
  • अधिक संक्षेप में प्रारूप का उपयोग कर।

व्याख्या करने के लिए, समस्या यह थी कि pg_get_serial_sequence जिस pg_get_serial_sequence जिक्र कर रहे हैं उसे काम करने के लिए तार लेते हैं, इसलिए यदि आप ऐसा करते हैं:

"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!

यह प्रारूप स्ट्रिंग में ''%1$I'' का उपयोग करके हासिल किया जाता है, '' एक एस्ट्रोफ़े 1$ अर्थ पहला तर्क है, और I मतलब कोट्स में है


यदि आप प्रारंभ में प्रारंभ करने के लिए कस्टम SQL डेटा लोड करते समय यह त्रुटि देखते हैं, तो इससे बचने का एक और तरीका यह है:

लिखने के बजाय:

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

प्रारंभिक डेटा से id (प्राथमिक कुंजी) निकालें

INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

यह पोस्टग्रेस अनुक्रम को सिंक में रखता है!


यह तालिका से सभी स्तंभों को कॉलम या कॉलम नामों के बारे में कोई धारणा नहीं देगा। संस्करण 8.4 पर परीक्षण किया गया

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) RETURNS "pg_catalog"."void" AS 

    $body$  
      DECLARE 
      BEGIN 

      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || ') FROM ' || tablename || ')' || '+1)';



      END;  

    $body$  LANGUAGE 'plpgsql';


    select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') from information_schema.columns where column_default like 'nextval%';

यह सब एक साथ डालें

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) 
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
  EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''id''),
  (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

दी गई तालिका के ' id' अनुक्रम को ठीक करेगा (उदाहरण के लिए आमतौर पर डीजेंगो के साथ आवश्यक)।


यहां कुछ वास्तव में कट्टर उत्तर दिए गए हैं, मुझे लगता है कि यह पूछे जाने पर लगभग वास्तव में खराब होता था, क्योंकि यहां से बहुत से उत्तर संस्करण 9.3 के लिए काम नहीं करते हैं। संस्करण 8.0 के बाद से documentation इस सवाल का उत्तर प्रदान करता है:

SELECT setval('serial', max(id)) FROM distributors;

इसके अलावा, अगर आपको केस-सेंसिटिव अनुक्रम नामों की देखभाल करने की आवश्यकता है, तो आप इसे कैसे करते हैं:

SELECT setval('"Serial"', max(id)) FROM distributors;

ये कार्य खतरे से भरे हुए होते हैं जब अनुक्रम नाम, कॉलम नाम, तालिका के नाम या स्कीमा नामों में मजाकिया वर्ण होते हैं जैसे रिक्त स्थान, विराम चिह्न, और इसी तरह। मैंने यह लिखा है:

CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS  $$
DECLARE
 tabrelid oid;
 colname name;
 r record;
 newmax bigint;
BEGIN
 FOR tabrelid, colname IN SELECT attrelid, attname
               FROM pg_attribute
              WHERE (attrelid, attnum) IN (
                      SELECT adrelid::regclass,adnum
                        FROM pg_attrdef
                       WHERE oid IN (SELECT objid
                                       FROM pg_depend
                                      WHERE refobjid = $1
                                            AND classid = 'pg_attrdef'::regclass
                                    )
          ) LOOP
      FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
          IF newmax IS NULL OR r.max > newmax THEN
              newmax := r.max;
          END IF;
      END LOOP;
  END LOOP;
  RETURN newmax;
END; $$ ;

आप इसे ओआईडी पास करके एक अनुक्रम के लिए कॉल कर सकते हैं और यह किसी भी तालिका द्वारा उपयोग किए जाने वाले उच्चतम नंबर को वापस कर देगा जिसमें अनुक्रम डिफ़ॉल्ट रूप से है; या आप इसे अपने डेटाबेस में सभी अनुक्रमों को रीसेट करने के लिए इस तरह की क्वेरी के साथ चला सकते हैं:

 select relname, setval(oid, sequence_max_value(oid))
   from pg_class
  where relkind = 'S';

एक अलग योग्यता का उपयोग करके आप केवल एक निश्चित स्कीमा में अनुक्रम को रीसेट कर सकते हैं, और इसी तरह। उदाहरण के लिए, यदि आप "सार्वजनिक" स्कीमा में अनुक्रम समायोजित करना चाहते हैं:

select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
  from pg_class, pg_namespace
 where pg_class.relnamespace = pg_namespace.oid and
       nspname = 'public' and
       relkind = 'S';

ध्यान दें कि कैसे सेटवल () काम करता है, आपको परिणाम में 1 जोड़ने की आवश्यकता नहीं है।

एक समापन नोट के रूप में, मुझे चेतावनी देना है कि कुछ डेटाबेस में अनुक्रमों से जुड़े डिफ़ॉल्ट रूप से ऐसे तरीके हैं जो सिस्टम कैटलॉग को उनकी पूरी जानकारी नहीं देते हैं। यह तब होता है जब आप psql \ d में इस तरह की चीजें देखते हैं:

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |                 Modificadores                  
---------+---------+------------------------------------------------
 a       | integer | default nextval(('foo_a_seq'::text)::regclass)

ध्यान दें कि उस डिफ़ॉल्ट खंड में अगली () कॉल में :: regclass cast के अलावा :: :: टेक्स्ट कास्ट है। मुझे लगता है कि यह पुराने PostgreSQL संस्करणों से pg_dump'ed डेटाबेस होने के कारण है। क्या होगा कि उपरोक्त फ़ंक्शन sequence_max_value () ऐसी तालिका को अनदेखा कर देगा। समस्या को ठीक करने के लिए, आप बिना किसी कलाकार के अनुक्रम को संदर्भित करने के लिए DEFAULT खंड को फिर से परिभाषित कर सकते हैं:

alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE

फिर psql इसे ठीक से प्रदर्शित करता है:

alvherre=# \d baz
                     Tabla «public.baz»
 Columna |  Tipo   |             Modificadores              
---------+---------+----------------------------------------
 a       | integer | default nextval('foo_a_seq'::regclass)

जैसे ही आप इसे ठीक कर चुके हैं, फ़ंक्शन इस तालिका के साथ-साथ अन्य सभी के लिए सही ढंग से काम करता है जो समान अनुक्रम का उपयोग कर सकते हैं।


सभी अनुक्रम को 1 उपयोग में पुनरारंभ करने के लिए:

-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
    relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$

DECLARE

BEGIN
    EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$

LANGUAGE 'plpgsql';

-- Use Function
SELECT 
    relname
    ,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';

सभी अनुक्रमों को रीसेट करें, नामों के बारे में कोई धारणा नहीं है सिवाय इसके कि प्रत्येक तालिका की प्राथमिक कुंजी "आईडी" है:

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
    EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
    (SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';

reindex आज़माएं।

अद्यतन: टिप्पणियों में बताया गया है, यह मूल प्रश्न के उत्तर में था।


SELECT setval... जेडीबीसी SELECT setval... बनाता है, इसलिए यह करने का जावा-संगत तरीका यहां है:

-- work around JDBC 'A result was returned when none was expected.'
-- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

स्रोत - रुबी फोरम