Postgres Pro
Downloads
Home   >   mailing lists

Thread: Indexes and sequences

Indexes and sequences

From
Jeff Willden
Date:
I'm cleaning up a database that someone else made and have a couple
questions. When I look at a table in pgAdmin it shows the DDL below.
Do I need an index on the groupid_seq sequence? Doesn't the sequence
already include one? Also, even if I need it, it doesn't need to be a
unique index because the sequence already ensures uniqueness, right?
On top of that there's a primary key constraint that also ensures
uniqueness. Isn't there a bunch of redundant stuff here?

CREATE TABLE buddygroup
(
   groupid integer NOT NULL DEFAULT nextval('groupid_seq'::text),
   userid integer NOT NULL,
   title character varying(255) NOT NULL,
   CONSTRAINT buddygroup_pkey PRIMARY KEY (groupid)
)
WITH OIDS;
ALTER TABLE buddygroup OWNER TO postgres;

-- Index: bg_groupid_idx

-- DROP INDEX bg_groupid_idx;

CREATE UNIQUE INDEX bg_groupid_idx
   ON buddygroup
   USING btree
   (groupid);

-- Index: bg_userid_idx

-- DROP INDEX bg_userid_idx;

CREATE INDEX bg_userid_idx
   ON buddygroup
   USING btree
   (userid);

Re: Indexes and sequences

From
"Sean Davis"
Date:


On Jan 19, 2008 5:04 PM, Jeff Willden <jeff@pavanell.com> wrote:
I'm cleaning up a database that someone else made and have a couple
questions. When I look at a table in pgAdmin it shows the DDL below.
Do I need an index on the groupid_seq sequence? Doesn't the sequence
already include one? Also, even if I need it, it doesn't need to be a
unique index because the sequence already ensures uniqueness, right?
On top of that there's a primary key constraint that also ensures
uniqueness. Isn't there a bunch of redundant stuff here?

A "sequence" does not include an index, no.  A sequence is NOT a column, but rather a separate data entity that increments.  The value of a sequence can be inserted into a column, but the column and the sequence are NOT the same thing.  Indexes are created on columns.

You do need to create an index, and it should be a unique index if you want uniqueness.  Since the DEFAULT value is taken from the sequence, the values will be unique as long as you do not EVER specify the value of the column in an insert or update.  That is a dangerous assumption, so specify a unique index.

The primary key will automatically create a unique index if one does not exist.

There is a section in the manual about "serial" data types that will answer many of these questions in more detail.

Hopefully that helps. 

Sean

 

CREATE TABLE buddygroup
(
  groupid integer NOT NULL DEFAULT nextval('groupid_seq'::text),
  userid integer NOT NULL,
  title character varying(255) NOT NULL,
  CONSTRAINT buddygroup_pkey PRIMARY KEY (groupid)
)
WITH OIDS;
ALTER TABLE buddygroup OWNER TO postgres;

-- Index: bg_groupid_idx

-- DROP INDEX bg_groupid_idx;

CREATE UNIQUE INDEX bg_groupid_idx
  ON buddygroup
  USING btree
  (groupid);

-- Index: bg_userid_idx

-- DROP INDEX bg_userid_idx;

CREATE INDEX bg_userid_idx
  ON buddygroup
  USING btree
  (userid);

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org

Function in function

From
"Lukas"
Date:
Hello,

 If I have two PostgreSQL functions:
Function_one()
and
Function_two()

I call one from other:

Function_one
Begin
return function_two()
end

 Now if I change something in body of Function_two and will NOT
recreate/replace Function_one I will still get old value.
 My question is: Is it possible somehow recreate Function_two that it
changed in every function it was used?

thx
Lukas


--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

Re: Function in function

From
Tom Lane
Date:
"Lukas" <lukas@fmf.vtu.lt> writes:
>  Now if I change something in body of Function_two and will NOT
> recreate/replace Function_one I will still get old value.

Really?  Works fine for me.  Please show a complete test case.

            regards, tom lane