26

Re: Double posts and online users

best way for programing db apps in pgsql is to use stored procedures. They runs faster than
sending multiple queries to db, you can easily handle cases like this in stored procedures.

CREATE OR REPLACE FUNCTION bb_login(user_id text, ident text, logged timestamp) returns void as '
DECLARE
rows integer;
BEGIN

UPDATE online SET logged=current_timestamp WHERE user=user_id;

GET DIAGNOSTICS rows = ROW_COUNT;

IF rows = 0 THEN
  INSERT INTO online (user_id, ident, logged) VALUES(user_id,ident,logged);
END IF;
END;
'
language 'plpgsql';

27

Re: Double posts and online users

Smartys wrote:

OK, ignore me, it appears that the stuff I'm talking about is for stored procedures, not regular usage. wink

No probs. Thanks for putting time into trying to sort this. It is greatly appreciated. smile

28

Re: Double posts and online users

hsn wrote:

best way for programing db apps in pgsql is to use stored procedures. They runs faster than
sending multiple queries to db, you can easily handle cases like this in stored procedures.

CREATE OR REPLACE FUNCTION bb_login(user_id text, ident text, logged timestamp) returns void as '
DECLARE
rows integer;
BEGIN

UPDATE online SET logged=current_timestamp WHERE user=user_id;

GET DIAGNOSTICS rows = ROW_COUNT;

IF rows = 0 THEN
  INSERT INTO online (user_id, ident, logged) VALUES(user_id,ident,logged);
END IF;
END;
'
language 'plpgsql';

That one is way over my head. big_smile Where/how would that code be placed to utilise it?

29

Re: Double posts and online users

MattF wrote:
hsn wrote:

best way for programing db apps in pgsql is to use stored procedures. They runs faster than
sending multiple queries to db, you can easily handle cases like this in stored procedures.

CREATE OR REPLACE FUNCTION bb_login(user_id text, ident text, logged timestamp) returns void as '
language 'plpgsql';

That one is way over my head. :D Where/how would that code be placed to utilise it?

place definitions of stored procedures into SQL statements used during install for creating tables, indexes, etc.
They can be called like other functions from SQL statements. Instead of coding INSERT into ... call procedure
using SELECT bb_login(arg1,arg2,arg3) MySQL5 supports stored procedure too, but syntax is bit different.

30 (edited by MattF 2007-09-15 16:46)

Re: Double posts and online users

Cheers for that info on definitions. Been trying to get my head round the specifics but failing miserably upto just. big_smile

Did set me off wondering if something along the lines of the following may work as a temporary fix though, (albeit in a dirty hack type fashion). Encapsulating the default sql line(s) within functions.php with the following, or something like:

default:
   $online = $db->query('SELECT '.$pun_user['id'].' FROM '.$db->prefix.'online');
   if (!$db->fetch_assoc ($online))
   {
        [db query/insert line here]
        break;
   }

Would something along those lines work, as a put on?


Cheers all,

Matt

Re: Double posts and online users

No, it will simply reduce the likelihood of a race condition. A real hackish fix would be to simply keep the line as is but remove the "or error..." bit after. Then add a unique constraint just like MySQL has now. That way, you could never have duplicates: if the script tried to insert them, it would continue without erroring out.
However, as you might have guessed, we're looking for a bit more robust solution than that tongue

32

Re: Double posts and online users

Smartys wrote:

However, as you might have guessed, we're looking for a bit more robust solution than that tongue

Aye. That's why I made sure I mentioned the hackish temporary put on bit with the hack question. big_smile

I'll focus my attention back on the definitions bit then. smile

Cheers again,

Matt

Re: Double posts and online users

MattF wrote:
Smartys wrote:

However, as you might have guessed, we're looking for a bit more robust solution than that tongue

Aye. That's why I made sure I mentioned the hackish temporary put on bit with the hack question. big_smile

I'll focus my attention back on the definitions bit then. smile

Cheers again,

Matt

lol tongue
I know, I was just providing a hackish workaround tongue

Re: Double posts and online users

OK, I might FINALLY have a solution. For real this time. tongue

FIND

                // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
                switch ($db_type)
                {
                    case 'mysql':
                    case 'mysqli':
                        $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;

                    default:
                        $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;
                }

REPLACE WITH

                // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
                switch ($db_type)
                {
                    case 'mysql':
                    case 'mysqli':
                        $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;

                    case 'pgsql':
                        $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) SELECT '.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].' FROM '.$db->prefix.'users WHERE id = '.$pun_user['id'].' AND NOT EXISTS (SELECT 1 FROM '.$db->prefix.'online WHERE user_id = '.$pun_user['id'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;

                    default:
                        $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES('.$pun_user['id'].', \''.$db->escape($pun_user['username']).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                        break;
                }

FIND

        // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
        switch ($db_type)
        {
            case 'mysql':
            case 'mysqli':
                $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;

            default:
                $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;
        }

REPLACE WITH

        // With MySQL/MySQLi, REPLACE INTO avoids a user having two rows in the online table
        switch ($db_type)
        {
            case 'mysql':
            case 'mysqli':
                $db->query('REPLACE INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;

            case 'pgsql':
                $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) SELECT 1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].' FROM '.$db->prefix.'users WHERE id=1 AND NOT EXISTS (SELECT 1 FROM '.$db->prefix.'online WHERE user_id = 1 AND ident = \''.$db->escape($remote_addr).'\')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;

            default:
                $db->query('INSERT INTO '.$db->prefix.'online (user_id, ident, logged) VALUES(1, \''.$db->escape($remote_addr).'\', '.$pun_user['logged'].')') or error('Unable to insert into online list', __FILE__, __LINE__, $db->error());
                break;
        }

35

Re: Double posts and online users

Cheers. smile I shall try that out shortly and let you know. smile Thanks.

36

Re: Double posts and online users

Have put the code inplace. Haven't had chance to try and instigate the problem yet, but it hasn't thrown a wobbler over the code this time. big_smile Everything appears fine on the basic login/logout level with that new code. smile

37

Re: Double posts and online users

Just to keep this updated, the new code has been inplace for a few days, and the doppelganger effect hasn't reared it's head as of yet. smile I'll keep you updated on the outcome/progress. Thanks again Smarty's for all your help with this. smile

Re: Double posts and online users

No problem smile

39 (edited by MattF 2007-12-11 16:29)

Re: Double posts and online users

Just to keep this upto date, the problem still hasn't reared it's head with those updates inplace. smile Can't say with 100% certainty that it's gone, as it had a tendency to appear when quite a few people were online, (the forum's in casual drop in mode at the moment), big_smile but it definitely hasn't appeared within nigh on two months, so it looks like it may be a winner. big_smile Cheers again for all your help with this one Smartys. smile