Re: Sequence w/o 'holes', my implementation (in pl/pgsql)?

From: Teschi(at)gmx(dot)de
Date: Wed, 22 May 2002 22:10:52 +0200 (MEST)
Subject: [GENERAL] Sequence w/o 'holes', my implementation (in pl/pgsql)?

As for your second question, the minimum gap number seems to
be retrieved by ones(see below) as well as yours . I wouldn't think
they have an adverse effect on the performance. But, I haven't yet
done its test.

Regards,
Masaru Sugawara

they don't have an adverse effect on the performance.

--------------------------------------------------------
CREATE TABLE engel (id int4 PRIMARY KEY NOT NULL,
name text,
CONSTRAINT ct_id_check CHECK(id > 0));

CREATE VIEW vi_engel AS SELECT name FROM engel;

CREATE OR REPLACE FUNCTION fn_get_gap() RETURNS int4 AS '
declare
rec RECORD;
iCurrent int4 := 1;
iBefore int4 := 0;
BEGIN
FOR rec IN SELECT * FROM engel ORDER BY id LOOP
IF rec.id > iCurrent THEN
EXIT;
END IF;
iCurrent := iCurrent + 1;
iBefore := rec.id;
END LOOP;
RETURN iBefore + 1;
END;
' LANGUAGE 'plpgsql';

CREATE RULE rl_gapless AS ON INSERT
TO vi_engel
DO INSTEAD INSERT INTO engel VALUES(fn_get_gap(),NEW.name);

-------------------------------------------------------
renew=# INSERT INTO vi_engel (name) VALUES ('sachiel');
renew=# INSERT INTO vi_engel (name) VALUES ('shamshel');
renew=# DELETE FROM engel WHERE name='sachiel';
renew=# INSERT INTO vi_engel (name) VALUES ('ramiel');
renew=# INSERT INTO vi_engel (name) VALUES ('gaghiel');
renew=# SELECT * FROM engel;
id | name
---+----------
2 | shamshel
1 | ramiel
3 | gaghiel
(3 rows)

renew=# select version();
version
--------------------------------------------------------------------
PostgreSQL 7.2.1 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66

>
> i wrote a sequence counter that does not produce holes in pl/pgsql.
> but i don't really know if thats a good one.
> it would be very nice if someone could help me out with my three questions
> about this code below:
>
> - what do i have to do to make this one multi user save (i don't think it is
> right now)?
> - is there a way to get this running without all these dynamic queries (e.g.
> passing a whole table to a pl/pgsql function), i don't think it is very
> performant right now.
> - is there a way to make the trigger-function independent on the row name?
> in my case i use a column OLD.id but i want that "id" to be passed to the
> trigger as a string:
> BUT i can't use OLD in a dynamic query, and i can't find a way to use the
> column name stored in a string on OLD either.
>
>
> About the functions:
>
> gapless_create (TEXT seq_name, INT start_value)
> creates a table with one column (free_id) filled with one row of the
> start_value;
> there is a rule assigned to that table which prevents the last value
> from being deleted, and increases it instead.
>
> gapless_drop (TEXT seq_name)
> drops the table.
>
> gapless_get (TEXT seq_name)
> used to get a free sequence number.
> returns the smallest value in the table and deletes it.
>
> gapless_free (TEXT seq_name, INT value)
> puts a number that has freed in the table, so get function can return
> it.
> this value must be smaller than the biggest value of the table or there
> will be holes.
>
> tr_gapless_free()
> same as free, but this has to be used as a trigger function.
>
>
>
> an example of being used:
> -------------------------
>
> SELECT gapless_create ( 'engel_id_seq' , 0);
> CREATE TABLE engel
> (
> id INT PRIMARY KEY DEFAULT gapless_get ( 'engel_id_seq' ),
> name TEXT
> );
>
> CREATE TRIGGER engel_trigger AFTER DELETE ON engel
> FOR EACH ROW EXECUTE PROCEDURE tr_gapless_free( 'engel_id_seq' );
>
>
> INSERT INTO engel (name) VALUES ('sachiel');
> INSERT INTO engel (name) VALUES ('shamshel');
> DELETE FROM engel WHERE name='sachiel';
> INSERT INTO engel (name) VALUES ('ramiel');
> INSERT INTO engel (name) VALUES ('gaghiel');
>
>
> ----
> ---- the Functions:
> -------------------
>
> CREATE FUNCTION gapless_create ( text , int ) RETURNS int AS '
> BEGIN
> EXECUTE '' CREATE TABLE ''||$1||'' ( free_id INT PRIMARY KEY );
> '';
> EXECUTE '' INSERT INTO ''||$1||'' ( free_id ) VALUES ( ''||$2||'' );
> '';
> EXECUTE '' CREATE RULE ''||$1||''_gl_rule AS ON DELETE TO ''||$1||''
> WHERE 1 = (SELECT count(*) FROM ''||$1||'' )
> DO INSTEAD UPDATE ''||$1||'' SET free_id = 1+(SELECT max(free_id) FROM
> ''||$1||'' );
> '';
> RETURN $2;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> CREATE FUNCTION gapless_drop ( text ) RETURNS int AS '
> BEGIN
> EXECUTE '' DROP TABLE ''||$1||'';
> '';
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> CREATE FUNCTION gapless_get ( text ) RETURNS int AS '
> DECLARE
> rec RECORD;
> retval INT;
> BEGIN
> FOR rec IN EXECUTE '' SELECT min(free_id) AS min_id FROM ''||$1||''; ''
> LOOP
> retval := rec.min_id;
> END LOOP;
>
> EXECUTE '' DELETE FROM ''||$1||'' WHERE free_id =
> ''||to_char(retval,''9999999999'')||'';
> '';
> RETURN retval;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> CREATE FUNCTION gapless_free ( text , int) RETURNS int AS '
> BEGIN
> EXECUTE '' INSERT INTO ''||$1||'' ( free_id ) VALUES ( ''||$2||'' );
> '';
>
> RETURN $2;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> CREATE FUNCTION tr_gapless_free() RETURNS OPAQUE AS '
> DECLARE
> tmpvar INT;
> BEGIN
>
> IF TG_NARGS = 0 THEN
> RAISE EXCEPTION ''Function: fr_gapless_free MUST have an Argument'';
> END IF;
>
> tmpvar = OLD.id;
> ---------------- WOULD BE NICE IF id COULD BE DYNAMIC
>
> EXECUTE '' INSERT INTO ''||TG_ARGV[0]||'' ( free_id ) VALUES (
> ''||to_char(tmpvar,''9999999999'')||'' );
> '';
> RETURN NULL;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> -- END OF CODE
>
>
> Thanks for reading and/or your help
> Teschi
>
>
> --
> GMX - Die Kommunikationsplattform im Internet.
> http://www.gmx.net