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);

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

http://archives.postgresql.org

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
>

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.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend