sequence's plpgsql

[Tim McAuley]

Hi,

I've hit a little problem and was wondering if anyone might be able to
give some help.

Set-up:

- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.

(now the bold bit)
- We have a stored procedure that actually updates one of the sequences
as well. It increments one at a time using this code:

SELECT INTO NewPK pkIndex FROM PrimaryKeyGenerator WHERE Name =
SequenceName FOR UPDATE;
UPDATE PrimaryKeyGenerator SET pkIndex = NewPK + 1 WHERE Name =
SequenceName;

I believe the "FOR UPDATE" won't actually do any good inside a plpgsql
call. Am I right?

Problem:

I have just called this stored procedure from outside the system using
10 threads and have got some errors due to duplicate entries on the
unique index. If it was only the stored procedures using this pk
generator then I could use a postgresql sequence but it isn't. If the
entity beans were to use the sequence, they'd have to make a database
call every time.

Any thoughts?

I'm thinking I may need to switch to using a sequence because the entity
beans don't actually update this particular table very often but would
prefer not to for portability reasons.

Thanks,

Tim

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

On Wednesday 24 September 2003 17:40, Tim McAuley wrote:

Hi,

I've hit a little problem and was wondering if anyone might be able to
give some help.

Set-up:

- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.

You might want to try just using sequences - PG does some caching of generated
values for you. Sorry - can't remember how you alter the cache size, but try
SELECT * FROM my_sequence;
to see the details of its settings.

Use sequences, and from your sequence-holding class do something like:
SELECT nextval('myseq'),nextval('myseq'),...10 times...
That will give you a block of 10 sequence values in one go, and off you go.

If you'd rather have the values in one column, create a single-column table
"seq_count" and populate with values 1..10 then:
SELECT nextval('myseq'),seq_count.id FROM seq_count;

That any use?
--
Richard Huxton
Archonet Ltd

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

http://archives.postgresql.org

[Tim McAuley]

Richard Huxton wrote:

On Wednesday 24 September 2003 17:40, Tim McAuley wrote:

Hi,

I've hit a little problem and was wondering if anyone might be able to
give some help.

Set-up:

- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.

You might want to try just using sequences - PG does some caching of generated
values for you. Sorry - can't remember how you alter the cache size, but try
SELECT * FROM my_sequence;
to see the details of its settings.

Use sequences, and from your sequence-holding class do something like:
SELECT nextval('myseq'),nextval('myseq'),...10 times...
That will give you a block of 10 sequence values in one go, and off you go.

If you'd rather have the values in one column, create a single-column table
"seq_count" and populate with values 1..10 then:
SELECT nextval('myseq'),seq_count.id FROM seq_count;

That any use?

Thanks for that. I investigated using your suggestion but actually
discovered that these tables were only being updated by the stored
procedures so this meant it was safe to use sequences there. I have now
set these up however I am now getting "deadlock detection" errors.

I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e.

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected

Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by

How can I get this? I must be overlooking something because I've set the debug level to debug5 and still only get a single message saying
ERROR: deadlock detected
DEBUG: AbortCurrentTransaction

I've switched from using Postgresql 7.3.2 on a linux server to 7.3.4 running on my own machine through cygwin.

I've got output from "select * from pg_locks;" but am not getting very far with this. The only locks marked as false do not give a table oid, only the transaction id. i.e.
| | 11515901 | 30440 | ShareLock | f

Will continue working away to see if I can locate the deadlock.

Tim


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

[Tom Lane]

Tim McAuley <mc******@tcd.ie> writes:

I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e. Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected
Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by How can I get this?

This display is a new feature in 7.4. Can you try your problem on a
7.4 beta release?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tim McAuley]

I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e.
Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected
Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by
How can I get this?

This display is a new feature in 7.4. Can you try your problem on a
7.4 beta release?

Ah, that would make sense.

Another question now. I am unable to compile Postgresql 7.4 beta 3 under
cygwin (Windows 2K, using cgyipc 2).

I am getting the error:
"
creating information schema... ERROR: end-of-copy marker does not match
previous newline style
CONTEXT: COPY FROM, line 361
"

Any ideas?

Thanks,

Tim

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