Double posts and online users (Page 2 of 2)

hsn · 2007-09-06 12:25

hsn
Junior Member
Offline

From: Czech Republic
Registered: 2007-09-06
Posts: 2

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';

MattF · 2007-09-06 17:07

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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.

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

MattF · 2007-09-06 17:13

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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. Where/how would that code be placed to utilise it?

hsn · 2007-09-07 00:24

hsn
Junior Member
Offline

From: Czech Republic
Registered: 2007-09-06
Posts: 2

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.

MattF · 2007-09-15 16:44

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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.

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

Smartys · 2007-09-15 16:54

Smartys
Former PunBB Developer
Offline

Registered: 2004-04-30
Posts: 7,844

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

MattF · 2007-09-15 17:01

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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

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

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

Cheers again,

Matt

Smartys · 2007-09-15 17:09

Smartys
Former PunBB Developer
Offline

Registered: 2004-04-30
Posts: 7,844

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
Aye. That's why I made sure I mentioned the hackish temporary put on bit with the hack question.
I'll focus my attention back on the definitions bit then.
Cheers again,
Matt

lol
I know, I was just providing a hackish workaround

Smartys · 2007-10-14 01:28

Smartys
Former PunBB Developer
Offline

Registered: 2004-04-30
Posts: 7,844

Re: Double posts and online users

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

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;
        }

MattF · 2007-10-14 10:31

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

Re: Double posts and online users

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

MattF · 2007-10-14 13:29

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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. Everything appears fine on the basic login/logout level with that new code.

MattF · 2007-10-29 00:17

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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. I'll keep you updated on the outcome/progress. Thanks again Smarty's for all your help with this.

Smartys · 2007-10-29 00:46

Smartys
Former PunBB Developer
Offline

Registered: 2004-04-30
Posts: 7,844

Re: Double posts and online users

No problem

MattF · 2007-12-11 16:28

MattF
Senior Member
Offline

From: South Yorkshire, England
Registered: 2007-03-15
Posts: 1,956

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. 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), but it definitely hasn't appeared within nigh on two months, so it looks like it may be a winner. Cheers again for all your help with this one Smartys.

Double posts and online users (Page 2 of 2)

Posts: 26 to 39 of 39

26 Reply by hsn 2007-09-06 12:25

Re: Double posts and online users

27 Reply by MattF 2007-09-06 17:07

Re: Double posts and online users

28 Reply by MattF 2007-09-06 17:13

Re: Double posts and online users

29 Reply by hsn 2007-09-07 00:24

Re: Double posts and online users

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

Re: Double posts and online users

31 Reply by Smartys 2007-09-15 16:54

Re: Double posts and online users

32 Reply by MattF 2007-09-15 17:01

Re: Double posts and online users

33 Reply by Smartys 2007-09-15 17:09

Re: Double posts and online users

34 Reply by Smartys 2007-10-14 01:28

Re: Double posts and online users

35 Reply by MattF 2007-10-14 10:31

Re: Double posts and online users

36 Reply by MattF 2007-10-14 13:29

Re: Double posts and online users

37 Reply by MattF 2007-10-29 00:17

Re: Double posts and online users

38 Reply by Smartys 2007-10-29 00:46

Re: Double posts and online users

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

Re: Double posts and online users

Posts: 26 to 39 of 39