Cycle through primary key with DBI

lestrrat has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re (tilly) 1: Cycle through primary key with DBI by tilly (Archbishop) on Jun 05, 2001 at 01:10 UTC
Advice. You are going to have significant race conditions. Instead of your current strategy I would recommend using a random integer as an ID, and keeping a date. On collisions just back off and get a different ID. Periodically delete all of the old records. This won't eliminate race conditions, but it will make them acceptably rare.	[reply]
Re: Cycle through primary key with DBI by arhuman (Vicar) on Jun 05, 2001 at 00:36 UTC
With MySQL your answer would be REPLACE. With postgres you could try and UPDATE .... WHERE ref=ID and is it fails use an INSERT... To get the last inserted key use : `$dbh->{'mysql_insertid'};` with mysql (Asim already answered for Postgres) "Only Bad Coders Code Badly In Perl" (OBC2BIP)	[reply] [d/l]
Re: Re: Cycle through primary key with DBI by fs (Monk) on Jun 05, 2001 at 00:58 UTC
If you're going to use the UPDATE method, you could probably simplify the logic by ensuring beforehand that the database is seeded up to the max limit.	[reply]
(jeffa) Re: Cycle through primary key with DBI by jeffa (Bishop) on Jun 05, 2001 at 00:19 UTC
You could calculate the number in Perl with (n + 1) % 1000. You would also need to perform a conditional check to see if the row exists or not (difference between insert and update). Another approach would be to prune out the old users on some kind of regular basis: maybe set up a trigger so that when the magic number is hit, a stored procedure that does the pruning is called. Just some thoughts :) Jeff R-R-R--R-R-R--R-R-R--R-R-R--R-R-R-- L-L--L-L--L-L--L-L--L-L--L-L--L-L--	[reply]
Re: Cycle through primary key with DBI by Asim (Hermit) on Jun 05, 2001 at 00:30 UTC
To do this, you'll have to have a few SQL commands run for every insert. First, the command needs to check to see if there is an ID greater than 1000. If there is , then you'll need to DELETE the first ID number, re-start the ID number sequence, and then INSERT the new data. If it's less than 1000, just INSERT it in. Getting back that ID number can be problematic. It's do-able in MYSQL and Access that I know of, I'd wager PostgreSQL has a LastAutonumberEntered or the like somewhere in the docs. If not, try timestamping, and then searching for that timestamp (records entered less than, say, 1 minute ago) or searching on a different possibly unique field. I'd recommend wrapping all this in a transaction, for obvious reasons. ----Asim, known to some as Woodrow.	[reply]
Re: Cycle through primary key with DBI by AidanLee (Chaplain) on Jun 05, 2001 at 00:06 UTC
You'll probably have to do a "SELECT MAX(ID) from MYTABLE" first to check if you've reached your upper limit. I'm not entirely sure why you'd want to do this.	[reply]
Re: Cycle through primary key with DBI by runrig (Abbot) on Jun 05, 2001 at 00:52 UTC
My thought is to do something I usually hate doing (because usually 'sequences' or 'serial fields' do the job but not in this case). Use a control table which just stores the 'next' id number to be used and insert one row with the 'first' id number. Then when you want to insert into your table, you'll first need to lock the control table (or just the record, I'm not sure if a table lock attempt will block or immediately fail in PostgreSQL, either way you could work it out) select the 'next' id number from the control table, update the control table, unlock the control table, delete from your table, then insert into your table. I'd probably do this all in a transaction so we can rollback if any of it fails. I'd just do the 'delete' from your table every time, because soon you'll have your thousand records and it won't be worth checking to see if you 'should' delete.	[reply]
(boo) Re: Cycle through primary key with DBI by boo_radley (Parson) on Jun 05, 2001 at 01:00 UTC
I'll mention this since you say you're not DB oriented. Some databases -- I admit that I'm Postgre ignorant, so it may not be an issue -- treat primary keys specially. If the structure of the table reveals something like `primary_key INT PRIMARY KEY` [download] or `primary_key INT AUTOINCREMENT` [download] Then chances are that you won't be able to overwrite these values; you'll need to issue some UPDATEs instead. I'm curious as to your reasoning of doing this -- it seems like if other tables have relationships with this one, arbitrarily hosing the data mayn't be such a good idea.	[reply] [d/l] [select]
Re: Cycle through primary key with DBI by lestrrat (Deacon) on Jun 05, 2001 at 01:15 UTC
Thanks everybody for your advice. I think I see my problem a little better now From what everybody says, there seems to be a better way to do this -- let me explain what I'm doing: We have a registration process for users. Users are not really registered until they confirm their registration after they get an email. So this table that I originally asked about is for the temporary database. I need to give the user a "temporary ID" that he can use when confirming. Only upon confirmation does the user gets a real userid. The reason for the arbitrary maximum was because we don't have much resource on this particular host machine -- we wanted to keep the data stored there the bare minimum, or at least fixed. But as I read everybody's advice it seems that simply inserting the new record is the way to go. maybe we'll clean up the table every 2 weeks or so. However, I still need a way to get the ID -- I will try what Asim wrote later when I get back to it. If there's better ways to do this, please let me know This has been most helpful. Thank you all for your help	[reply]
Re: Re: Cycle through primary key with DBI by asiufy (Monk) on Jun 05, 2001 at 01:43 UTC
I did something quite similar the other day... But instead of using a temporary table, I set up the normal users table with some extra fields: . Validated . Validation_Code + some others So, once the user signs up initially, he's sent the Validation_Code, and his entry in that table reads Validated="No". Once he validates himself (by sending back that validation code), just UPDATE his row to read Validated="Yes". You can do this using the normal user table, or you can create a secondary table linked to the main one by an ID (since you'll probably want to have a user ID anyway, as the primary key). As added "niceties", you can have a field showing how many times the user has attempted to send a validation code. I have a limit of 3 attempts, after that, the user is locked out for good. Also, I have a timestamp. After x number of days/hours/etc, I can scan that table and remove all the entries/users that have signed up, but never properly validated themselves. Hope this helps...	[reply]
Re: Cycle through primary key with DBI by Daddio (Chaplain) on Jun 05, 2001 at 03:24 UTC
And yet another idea would be to just keep on going with the primary key number, checking the total records on each write and deleting any > 1000. This would probably avoid more of the race conditions, allow for the same or faster execution time (I believe DELETing a "specified" row and WRITing the "next" would be at least as fast as an UPDATE or REPLACE), and give you cleaner code as you would always know the "last inserted" id, should you need it at some point. My $0.02... D a d d i o	[reply]
Re: Cycle through primary key with DBI by lestrrat (Deacon) on Jun 05, 2001 at 12:07 UTC
Well, duh SELECT nextval( sequence ); Of course, it was all worthwhile to ask this question here.... calrified lots for me. thanks again, everybody	[reply]