<...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 th
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:
'^[0-9]
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 tha
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
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 text
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
> 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 repres
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 alread
> 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 "conversatio
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 un
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?
P
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 "simil
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 wro
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 wan
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 on
> > 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 choo
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 des
> > 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
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 recor
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 showe
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 PROT
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 ine
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:
http://regexl
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
-
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:
http://www.postgresql.org/mailpref/pgs
25 matches
Mail list logo
