hi,

im currently using this module with the postgres patch on drupal 4.7. and i noticed a problem when adding points to a user --

PHP Warning: ERROR: Cannot insert a duplicate key into unique index userpoints_txn_pkey query: INSERT INTO userpoints_txn VALUES (0, 1, 0, 1, 1182515549, 'admin', 'test add', 0)

on line 284 of userpoints.module under the userpoints_transaction function you have:

db_query("INSERT INTO {userpoints_txn} VALUES (0,%d, 0, %d, %d, '%s', '%s', %d)",

which was causing the duplicate key error as the first transaction is entered will have a txn_id of 0. succeeding transactions will also try to insert transaction records with a txn_id of 0.

so i had to edit it to this:

db_query("INSERT INTO {userpoints_txn} VALUES (nextval('public.userpoints_txn_txn_id_seq'),%d, 0, %d, %d, '%s', '%s', %d)",

which sets the txn_id to the next in sequence as it should be.

Comments

kbahey’s picture

kbahey commented
Status: Active » Postponed (maintainer needs more info)

Making the primary key SERIAL should do the trick, but I do not use PostgreSQL.

Can you check 5.x-2.11 (available in 12 hours on drupal.org). See if it is better for Postgres or not.

In any case, submitting working patches would be appreciated.

See http://drupal.org/patch for details on how to create one.

tanjerine’s picture

tanjerine commented
Component: Miscellaneous » Code: userpoints.module

actually you're right, this would work:

db_query("INSERT INTO {userpoints_txn} VALUES (default,%d, 0, %d, %d, '%s', '%s', %d)",

was probably making things more complicated for myself by not using default.

kbahey’s picture

kbahey commented
Status: Postponed (maintainer needs more info) » Needs work

Even simpler:

http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-SE...

CREATE TABLE foo (
col1  SERIAL, // This is the autoincrement field
col2  VARCHAR(20),
col3  INTEGER);

then we do

INSERT INTO blah (col2, col3) VALUES ('a', 10)

i.e. we leave out the serial field in the insert altogether. This will work for both postgres and mysql all the same.

tanjerine’s picture

tanjerine commented

Yes, that would be the best option. I didn't want to disturb the rest of the code (lest it breaks something for mysql) so i just tinkered with that line. Not so sure if this will work on mysql though.

Thanks!

jaime_pomales’s picture

jaime_pomales commented
Version: 4.7.x-2.2 » master
Component: Code: userpoints.module » Code: userpoints

Part of the problem with this transaction business is that txn_id has some sort of issue with its sequence in Postgresql. I don't really know what the particular mechanism of the error is, but postgresql creates a userpoints_txn_txn_id_seq SEQUENCE which is not referenced properly when points transactions are posted. Is txn_id some sort of reserved word? or is the "_" throwing off something. I haven't been able to come up with an acceptable answer.

In any case, other areas of drupal (other modules) use txnid as the transaction ids are. txnid works both in postgresql and mysql.

Summarily, in addition to the changes suggested above, I changed all references of txn_id to txnid (both in the schema and the php). Now transactions work just fine. I've written a bunch of patches to get this module working well with postgresql but I can't seem to get the lead developer to answer his email. Any developers care to take my patches and apply them? I'm willing to help out.

jredding’s picture

jredding commented
Status: Needs work » Postponed (maintainer needs more info)

Is this problem still occurring? There is a new v3 ready for some BETA testing. If you want to take a look at it and test it on postgres it would be much appreciated. The .install file definitely needs some postgres loving.

tanjerine’s picture

tanjerine commented

hi, I'll try and check it out. would the v3 be for drupal 5? or would it be compatible with drupal 4.7?

thanks!

jredding’s picture

jredding commented
Assigned: Unassigned » jredding

v3 is 5 only. Sorry.

jaime_pomales’s picture

jaime_pomales commented

Yes, I've got a fresh install on my testing server. I'll throw in the new version, test v3, and clean up the install file. Sorry I didn't respond here. I wish there was some sort of "new" tag on my username tracker page or something. My bad.

jredding’s picture

jredding commented

awesome. Thanks for helping out.

jaime_pomales’s picture

jaime_pomales commented

I did the install and there are some issues still. Looks like the DB schema changed a bit. Field "event" seems to have been dropped from the mysql install, but it still shows up in the pgsql install. Am I correct in assuming the mysql case is should be the absolute reference for what happens in the postgresql side? If so, I'll make the schema match the mysql side. There are, however, other references to field "event" so I'm not sure if it's an error or not. Let me know.

I'll try to get the updates (alter statements) working as well although I can't really promise they'll work, as Posgresql doesn't support that add column after function.

jredding’s picture

jredding commented

Sorry. The other day I *thought* I committed the changes to the .install file. I did make a commit and it was to the .install file but for whatever reason the changes didn't make it from my laptop to CVS. Obviously I made a mistake but I don't know what..

eh, whatever. I made another commit today AND I verified CVS. The correct .install file is in CVS. Please use the MySQL install statements as a basis for the postgres setup. The event field has been renamed to operation. The AFTER statements shouldn't make a difference because the field order isn't important as far as the module is concerned. INSERTS/UPDATES specifically name the fields, it'd be nice for consistency sake though.

THANKS!!!!! (seriously, Thanks for pitching in)

jredding’s picture

jredding commented

Jaime any updates? The new schema is correct in CVS

jredding’s picture

jredding commented
Component: Code: userpoints » Code: userpoints API
Status: Postponed (maintainer needs more info) » Closed (fixed)

Changes were added to the .install file and tested against postgres although not extensively. I am closing this issue.

dagl3’s picture

dagl3 commented

hi,
I've these table:
CREATE TABLE registro
(
usuario character varying(100) NOT NULL,
cargo character varying(100) NOT NULL,
proyecto character varying(100),
actividad integer NOT NULL,
fecha date NOT NULL,
dia integer NOT NULL,
semana integer NOT NULL,
ano integer NOT NULL,
tiempo real NOT NULL,
id_registro serial NOT NULL,
CONSTRAINT registro_pkey PRIMARY KEY (id_registro))

The pk is the serial id_registro, it's was working properly, but after a time didn't work, for the last regitry, id_registro was in 64992 and something happened because when I tried to insert the return was duplicate keys, cuolud be taht the sequence was lost, i want to know what happened with the table..... what was the problem....

the solution i fonud was the use of select setval('sequence', number) and now its working properly but itcolud ocurrs again and i dont want to repeat the problem, any one knows why this happened?????