My post at the bottom.
On 9/23/08, Craig Ringer [EMAIL PROTECTED] wrote:
Dodgy forum software. Lots of it uses an IP address as a fake username for
unregistered users, rather than doing the sensible thing and tracking both
IP address and (if defined) username.
How I'd want to do
Phoenix Kiula wrote:
My post at the bottom.
...
No. You have no idea what the design is for. Not forum crap.
What happens when you need to store in a table the activity log?
ACTIVITY_ID
USER_STAMP (currently user_id or ip for registered and unregistered resp.)
And here it gets
On Mon, Sep 22, 2008 at 05:59:25PM +0100, Joao Ferreira gmail wrote:
I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
address
192.168.90.3
10.3.2.1
any help please...
use this regular expression:
'^[0-9]{1,3}(.[0-9]{1,3}){3}$'
warning: do not use like or similar to.
Phoenix Kiula wrote:
Ever tried this crap on a table of 10 million records on a live
website, where this query is happening at 3000 times per second? No
such function schtick will match the raw speed of a simpler indexed
query. Or did you mean my index should contain the COALESCE already?
thank you depesz
it seems a pretty good fix for my problem. Actually yestreday I came up
with something similar but your's is better.
cheers
joao
On Tue, 2008-09-23 at 09:26 +0200, hubert depesz lubaczewski wrote:
On Mon, Sep 22, 2008 at 05:59:25PM +0100, Joao Ferreira gmail wrote:
I'm
Please forgive my attempt to help you based on a woefully insufficient
description of your problem and situation. I will not make any attempt to do
so again.
Actually it was not my problem, this is a thread started by some one
else. I use Gmail so I see the entire thread as a conversation
Phoenix Kiula wrote:
Ever tried this crap on a table of 10 million records on a live
website, where this query is happening at 3000 times per second? No
such function schtick will match the raw speed of a simpler indexed
query. Or did you mean my index should contain the COALESCE already?
If you don't want to store IPs for registered users, I'd use:
user_id INTEGER,
ip cidr,
CONSTRAINT must_have_userstamp
CHECK ( user_id IS NOT NULL OR ip IS NOT NULL)
... and yes, I'd use a functional index to look it up, or even a
trigger-maintained cache of the text representation
Hi,
Phoenix Kiula wrote:
Please forgive my attempt to help you based on a woefully insufficient
description of your problem and situation. I will not make any attempt to do
so again.
To others: thanks for your suggestions, but this issue is not one of
session IDs, nor is it solved by storing
Phoenix Kiula wrote:
If you don't want to store IPs for registered users, I'd use:
user_id INTEGER,
ip cidr,
CONSTRAINT must_have_userstamp
CHECK ( user_id IS NOT NULL OR ip IS NOT NULL)
... and yes, I'd use a functional index to look it up, or even a
trigger-maintained cache of the
Phoenix Kiula wrote:
1. What extra tax will this constraint levy on an INSERT or UPDATE on
this table? There are about 100,000 inserts a day, and over three
times as many UPDATES. The concurrency is pretty high -- I mean
sometimes 1,000 users at the same time but no more than that. If the
Craig Ringer wrote:
Phoenix Kiula wrote:
1. What extra tax will this constraint levy on an INSERT or UPDATE on
this table? There are about 100,000 inserts a day, and over three
times as many UPDATES. The concurrency is pretty high -- I mean
sometimes 1,000 users at the same time but no more
On Sep 23, 2008, at 12:26 AM, hubert depesz lubaczewski wrote:
On Mon, Sep 22, 2008 at 05:59:25PM +0100, Joao Ferreira gmail wrote:
I'm unable to build a LIKE or SIMILAR TO expression for matching
and ip
address
192.168.90.3
10.3.2.1
any help please...
use this regular expression:
...snip...
I'd try a functional index first. If that didn't do the job, I'd use a
trigger-maintained column _purely_ as an optimisation (ie I could drop
it and lose no data) that stored text representations of the data.
Honestly, though, I expect the functional index would be more than
hello all,
I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
address
192.168.90.3
10.3.2.1
any help please...
thanks
joao
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On Mon, Sep 22, 2008 at 05:59:25PM +0100, Joao Ferreira gmail wrote:
I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
address
192.168.90.3
10.3.2.1
any help please...
any reason not to use standard inet datatype? which does the validation.
Best regards,
depesz
--
On 22/09/2008 17:59, Joao Ferreira gmail wrote:
I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
address
There are built-in types in PG for handling IP addresses - are they any
use to you?
If not, there's a useful site here which may get you started:
On Mon, Sep 22, 2008 at 10:59 AM, Joao Ferreira gmail
[EMAIL PROTECTED] wrote:
hello all,
I'm unable to build a LIKE or SIMILAR TO expression for matching and ip
address
192.168.90.3
10.3.2.1
As already mentioned inet / cidr types should work. Example:
postgres=# create table inettest (a
well...
my IP addresses are stored in a TEXT type field. that field can actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
:(
joao
On Mon, 2008-09-22 at 11:13 -0600, Scott Marlowe wrote:
On Mon, Sep 22, 2008 at 10:59 AM, Joao Ferreira gmail
[EMAIL
On Mon, Sep 22, 2008 at 11:16 AM, Joao Ferreira gmail
[EMAIL PROTECTED] wrote:
well...
my IP addresses are stored in a TEXT type field. that field can actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
Then cast them to inet and use the method I showed
Hi,
Joao Ferreira gmail wrote:
well...
my IP addresses are stored in a TEXT type field. that field can actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
Any reason not to change this in the first place?
For a quick fix you could use regex to find the
my IP addresses are stored in a TEXT type field. that field can actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
I think this is common DB design on many websites that have registered
user IDs.
My humble suggestion would be to make another column in
Phoenix Kiula wrote:
my IP addresses are stored in a TEXT type field. that field can actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
I think this is common DB design on many websites that have registered
user IDs.
Is it? Name one! Sounds like crappy
I think this is common DB design on many websites that have registered
user IDs.
Is it? Name one! Sounds like crappy design to me.
It might sound crappy design to you, but for websites that allow users
to do something while they are registered OR unregistered, will choose
to
Tino Wildenhain wrote:
Phoenix Kiula wrote:
my IP addresses are stored in a TEXT type field. that field can
actually
contain usernames like 'joao' or 'scott' and it can contain IP
addresses
I think this is common DB design on many websites that have registered
user IDs.
Is it? Name
25 matches
Mail list logo
