Seqeuence problem!!!

[jefflessard]

Hi, i would like to know if there is any way to bypass this sequence problem?
...
create table myTable
(
pk SERIAL PRIMARY KEY,
value varchar check(value!='')
);

==>insert into myTable (value) values ('');
==>ERROR: ExecAppend: rejected due to CHECK constraint mytable_value

it's still ok but...
==>insert into myTable (value) values ('abc');
==>INSERT 18407 1

Now i would expect that pk of value == 'abc' to be 1
but here's the results:
==> select * from myTable;
pk | value
----+-------
2 | abc

I need to fix this!

 

[rosenk]

I do not believe that there is any (good) way to fix this.

Why would you ever need to, however?

 

[rycamor]

>Why would you ever need to, however?

Probably because he is expecting to use the numbers of the PK do signify some sort of extra information, which is not how relational databases should be modeled.

jefflessard -- If you really need a sequenced primary key, you should not worry about gaps in the sequence. You will have gaps anyway, once you delete any rows. If, for example, you are using your primary key in order to count your rows, that is a mistake. Count your rows upon receiving query results.

On the other hand, you might be able to use a RULE or TRIGGER which aborts the INSERT before attempting, thus preserving your sequence. This will introdude a little more overhead into your database performance, though.

Or, if you look into sequences a little more, you will realize that you can manage your sequence explicitly. In this case, you might want to just create a stored procedure which handles all INSERTS into the table, and keep the logic in there. -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[jefflessard]

This is good... but...
I need an ordered sequence number, there will be no delete on this table, so, is anybody knows an effecient way to do so in postgres?

Thanks for your help

 

[rycamor]

Read my last two paragraphs. Your choice will be between the two. I doubt performance will be a problem, except in extreme cases (such as a very high volumne of inserts, many exceptions, etc...).

The simple fact is, if you need specialized logic in your database, you need to spend the time to learn the tools:

http://www.postgresql.org/docs/

http://techdocs.postgresql.org/

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[jefflessard]

So here is my problem answer, if anybody needs it...

I created a plpgsql function returning the max value + 1 of the given column name in arguments (must be quoted with ') and if there is no value (empty table) returns 1.

In the create table definition, i removed the serial datatype and replaced it with int and a default value of the function...

example :
/* The function */
CREATE OR REPLACE FUNCTION getNextValue(NAME) RETURNS INT AS '
DECLARE
Column ALIAS FOR $1;
ObjTemp RECORD;
BEGIN
FOR ObjTemp IN EXECUTE
''SELECT max(''||Column||'') AS LASTVAL''
LOOP END LOOP;
IF ObjTemp.LASTVAL ISNULL THEN
return 1;
ELSE
RETURN ObjTemp.LASTVAL + 1;
END IF;
END;
'LANGUAGE 'plpgsql';

/* CreateTable definition */
create table myTable
(
pk INT PRIMARY KEY DEFAULT getNextValue('myTable.pk'),
value varchar check(value!='')
);

And then... there is no more sequence problem, no triggers or rules... You may think it could slow down to use MAX()... but since a primary key is indexed, it's not supposed to be big work!!!

Please reply any comments if needed or to specify something I might forgot.

 

[rycamor]

Looks like a good way to manage a sequence explicitly .

>i removed the serial datatype and replaced it with int and a default value of the function...

It's interesting to note that SERIAL is not really a datatype anyway. It is just an alias for an integer column with a default which calls the nextval() function on the sequence. If you dump your table definition, the column definition will actually look like this:

col1 integer DEFAULT nextval('"tablename_col1_seq"'::text) NOT NULL

So, you are really doing things very similarly to PostgreSQL's default mechanism. The only difference is that in the standard mechanism, the actual sequences are maintained outside of a table, and once advanced, they can never be reversed.

Your solution should work fine for a moderate table size. I think you might have trouble scaling it up a table of millions of records, but I might be wrong. -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[rosenk]

Without using a lock there's a small chance that an insert might fail on a duplicate key, if two inserts occur (near) simultaneously. Depending on your usage, though, perhaps this isn't an issue for you.

 

[jefflessard]

Actually it seems that no explicit lock is required, asx you can read in postgresl manual (v7.3) :

LOCK [ TABLE ] name [, ...] IN lockmode MODE

ROW EXCLUSIVE MODE
Note: The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any query that modifies the data in a table

So, if I'm wrong tell me, so I will implement an explicit lock.

In fact there is a problem to this function, it only treats integers, but somebody could try to pass an other datatype in arguements... so I need to implement an exception... or something else....

 

[rosenk]

In the same section of the manual, note that a ROW EXCLUSIVE MODE lock does not conflict with another ROW EXCLUSIVE MODE lock.

[tt]Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE modes.[/tt]

I think that you might need to wrap the whole insert transaction in a SHARE ROW EXCLUSIVE mode lock (which conflicts with other SHARE ROW EXCLUSIVE and ROW EXCLUSIVE locks.)

The problem is that two inserts can happen simultaneously such that the [tt]SELECT max(''||Column||'') AS LASTVAL[/tt] of each returns the same value.

(This is starting to get beyond my understanding of locks [implicit and explicit], but I believe that such a conflict is possible.)

 

[Leland123]

Hi jefflessard,

You might add you function to the postgres cook book. Others who visit the cookbook may find you code useful.

http://www.brasileiro.net:8080/postgres/cookbook/

LelandJ Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox