Re: [GENERAL] dumb question

2016-06-02 Thread David G. Johnston 
On Thu, Jun 2, 2016 at 5:44 PM, Kevin Grittner  wrote:

> On Thu, Jun 2, 2016 at 3:23 PM, David G. Johnston
>  wrote:
> > On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce 
> wrote:
> >> Thanks all the below seem to do the trick.
>
> I doubt it -- using NOT IN requires (per the SQL specification)
> handling NULLs in a way that probably does not give you the answer
> you want.  (NOT IN also is often much slower than the NOT EXISTS
> test which will actually give you the answer you want.)
>
> test=# create table t (id int not null primary key, ref_id int, sts
> int not null default 0);
> CREATE TABLE
> test=# insert into t values
> (1,null,0),(2,1,1),(3,null,0),(4,null,0),(5,4,1),(6,null,0),(7,6,1);
> INSERT 0 7
> test=# select max(id) from t where sts=0 and id not in (select ref_id from
> t);
>  max
> -
>
> (1 row)
>
> test=# select max(id) from t t1 where sts = 0 and not exists (select *
> from t t2 where t2.ref_id = t1.id);
>  max
> -
>3
> (1 row)
>
> Note that providing minimal setup (like the above) helps in getting
> good answers quickly.
>
> >> do note, this is whats known as an 'anti-join', and these can be pretty
> >> expensive on large tables.
> >
> > +1
>
> *Can* be.  Proper indexing can make them very reasonable.
>

​Doh (me)...

Indeed, NOT IN (...) doesn't qualify as an anti-join since (for one) it
cannot (I don't think) be optimized in this way as the entire contents of
the IN() need to be determined.  IOW, its not really a join but just
another predicate condition whose one side is a subquery.

That said, writing out a full anti-join NOT EXISTS (or, similarly, a
semi-join EXISTS) clause can be a bit tedious for ad-hoc stuff while the
IN() variation is a bit more succinct and, I'd venture to say,
unfortunately familiar.  In can be made to work in this situation by
writing the expression as IN (SELECT ref_id FROM t WHERE ref_id IS NOT
NULL).

David J.

Re: [GENERAL] dumb question

2016-06-02 Thread Kevin Grittner 
On Thu, Jun 2, 2016 at 3:23 PM, David G. Johnston
 wrote:
> On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce  wrote:
>> Thanks all the below seem to do the trick.

I doubt it -- using NOT IN requires (per the SQL specification)
handling NULLs in a way that probably does not give you the answer
you want.  (NOT IN also is often much slower than the NOT EXISTS
test which will actually give you the answer you want.)

test=# create table t (id int not null primary key, ref_id int, sts
int not null default 0);
CREATE TABLE
test=# insert into t values
(1,null,0),(2,1,1),(3,null,0),(4,null,0),(5,4,1),(6,null,0),(7,6,1);
INSERT 0 7
test=# select max(id) from t where sts=0 and id not in (select ref_id from t);
 max
-

(1 row)

test=# select max(id) from t t1 where sts = 0 and not exists (select *
from t t2 where t2.ref_id = t1.id);
 max
-
   3
(1 row)

Note that providing minimal setup (like the above) helps in getting
good answers quickly.

>> do note, this is whats known as an 'anti-join', and these can be pretty
>> expensive on large tables.
>
> +1

*Can* be.  Proper indexing can make them very reasonable.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dumb question

2016-06-02 Thread David G. Johnston 
On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce  wrote:

> On 6/2/2016 11:10 AM, Steve Clark wrote:
>
> Thanks all the below seem to do the trick.
>
> On 06/02/2016 01:58 PM, David G. Johnston wrote:
>
> select max(id) from yourtable where sts=0 and id not in (select ref_id
> from yourtable);
>
>
> select max(id) from yourtable where sts=0 and id not in (select ref_id
> from yourtable);
>
>
> do note, this is whats known as an 'anti-join', and these can be pretty
> expensive on large tables.
>
​+1
​

​Though I suspect that with a partial index on (id, sts=0) and (ref_id,
ref_id IS NOT NULL), though highly sensitive to density, that even for
large​ total row counts it would perform pretty well; but I'm not
knowledgeable in how smart we are here.  Selecting, in descending order,
(id where sts = 0), from the index and then poking into index(ref_id)
should, particularly if the cross-set is sparse, pretty quickly find a
non-match.

David J.

Re: [GENERAL] dumb question

2016-06-02 Thread David G. Johnston 
​

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:
> pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark
> Sent: Thursday, June 2, 2016 9:56 AM
> To: pgsql <pgsql-general@postgresql.org>
> Subject: [GENERAL] dumb question
>
> Hi List,
>
> I am a noob trying to do something that seems like it should be easy but I
> can't figure it out.
>
> I have a table like so:
>
> id | ref_id | sts
> --
> 1  ||  0
> 2  | 1  |  1
> 3  ||  0
> 4  ||  0
> 5  | 4  |  1
> 6  ||  0
> 7  | 6  |  1
>
> I want to find the max(id) whose sts is 0 but whose id is not referenced
> by ref_id.
>

On Thu, Jun 2, 2016 at 4:07 PM, Dann Corbit <dcor...@connx.com> wrote:

> This is your request, translated directly into SQL
>
> select max(id) from sometable where sts=0 and ref_id IS NULL
>
> Looking at your sample, it seems that sts is always 1 when ref_id exists,
> so it may possibly simplify to:
>
> select max(id) from sometable where sts=0
>
>
​Please don't top-post.

​Your query would select "id=6", which is disqualified due to id=7...

For the record one reads:  "whose id is not referenced by ref_id" AS "id
NOT IN (ref_ids)"; ref_id IS NULL means "that lacks a ref_id" and is
evaluated independent of the id.

David J.

Re: [GENERAL] dumb question

2016-06-02 Thread Steve Clark 

On 06/02/2016 04:07 PM, Dann Corbit wrote:

This is your request, translated directly into SQL

select max(id) from sometable where sts=0 and ref_id IS NULL

Looking at your sample, it seems that sts is always 1 when ref_id exists, so it 
may possibly simplify to:

select max(id) from sometable where sts=0

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark
Sent: Thursday, June 2, 2016 9:56 AM
To: pgsql <pgsql-general@postgresql.org>
Subject: [GENERAL] dumb question

Hi List,

I am a noob trying to do something that seems like it should be easy but I 
can't figure it out.

I have a table like so:

id | ref_id | sts
--
1  ||  0
2  | 1  |  1
3  ||  0
4  ||  0
5  | 4  |  1
6  ||  0
7  | 6  |  1

I want to find the max(id) whose sts is 0 but whose id is not referenced by 
ref_id.

so the answer would be id=3.

Thanks for any pointers,
Steve


Hi Dan,

Thanks for the response - but I think that would give me id=6 and not id=3.

--
Stephen Clark



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dumb question

2016-06-02 Thread Dann Corbit 
If ref_id is an instance of id and you are trying to filter that out, then use 
a self join

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dann Corbit
Sent: Thursday, June 2, 2016 1:08 PM
To: 'Steve Clark' <steve.cl...@netwolves.com>; pgsql 
<pgsql-general@postgresql.org>
Subject: Re: [GENERAL] dumb question

This is your request, translated directly into SQL

select max(id) from sometable where sts=0 and ref_id IS NULL

Looking at your sample, it seems that sts is always 1 when ref_id exists, so it 
may possibly simplify to:

select max(id) from sometable where sts=0

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark
Sent: Thursday, June 2, 2016 9:56 AM
To: pgsql <pgsql-general@postgresql.org>
Subject: [GENERAL] dumb question

Hi List,

I am a noob trying to do something that seems like it should be easy but I 
can't figure it out.

I have a table like so:

id | ref_id | sts
--
1  ||  0
2  | 1  |  1
3  ||  0
4  ||  0
5  | 4  |  1
6  ||  0
7  | 6  |  1

I want to find the max(id) whose sts is 0 but whose id is not referenced by 
ref_id.

so the answer would be id=3.

Thanks for any pointers,
Steve

-- 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dumb question

2016-06-02 Thread John R Pierce 

On 6/2/2016 11:10 AM, Steve Clark wrote:

Thanks all the below seem to do the trick.

On 06/02/2016 01:58 PM, David G. Johnston wrote:
select max(id) from yourtable where sts=0 and id not in (select 
ref_id from yourtable);


select max(id) from yourtable where sts=0 and id not in (select ref_id 
from yourtable);




do note, this is whats known as an 'anti-join', and these can be pretty 
expensive on large tables.




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] dumb question

2016-06-02 Thread Dann Corbit 
This is your request, translated directly into SQL

select max(id) from sometable where sts=0 and ref_id IS NULL

Looking at your sample, it seems that sts is always 1 when ref_id exists, so it 
may possibly simplify to:

select max(id) from sometable where sts=0

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark
Sent: Thursday, June 2, 2016 9:56 AM
To: pgsql <pgsql-general@postgresql.org>
Subject: [GENERAL] dumb question

Hi List,

I am a noob trying to do something that seems like it should be easy but I 
can't figure it out.

I have a table like so:

id | ref_id | sts
--
1  ||  0
2  | 1  |  1
3  ||  0
4  ||  0
5  | 4  |  1
6  ||  0
7  | 6  |  1

I want to find the max(id) whose sts is 0 but whose id is not referenced by 
ref_id.

so the answer would be id=3.

Thanks for any pointers,
Steve

-- 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dumb question

2016-06-02 Thread Steve Clark 

Hi List,

I am a noob trying to do something that seems like it should be easy but I 
can't figure it out.

I have a table like so:

id | ref_id | sts
--
1  ||  0
2  | 1  |  1
3  ||  0
4  ||  0
5  | 4  |  1
6  ||  0
7  | 6  |  1

I want to find the max(id) whose sts is 0 but whose id is not referenced by 
ref_id.

so the answer would be id=3.

Thanks for any pointers,
Steve

--



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dumb question

2016-06-02 Thread Steve Clark 

Thanks all the below seem to do the trick.

On 06/02/2016 01:58 PM, David G. Johnston wrote:

select max(id) from yourtable where sts=0 and id not in (select ref_id from 
yourtable);


select max(id) from yourtable where sts=0 and id not in (select ref_id from 
yourtable);

--
Stephen Clark

Re: [GENERAL] dumb question

2016-06-02 Thread Steve Crawford 
On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos  wrote:

>
>
> 2016-06-02 14:23 GMT-03:00 Steve Crawford 
> :
>
>> Something like:
>>
>> select max(id) from yourtable where sts=0 and ref_id is null;
>>
>> That assumes that ref_id is null. It would help to see your table
>> structure and the query you tried that doesn't work. If ref_id is actually
>> a character string then you might need ref_id='' or coalesce(ref_id,'')=''
>> if it can be null or empty string.
>>
>> Cheers,
>> Steve
>>
>>
>> On Thu, Jun 2, 2016 at 10:16 AM, Steve Clark 
>> wrote:
>>
>>> Hi List,
>>>
>>> I am a noob trying to do something that seems like it should be easy but
>>> I can't figure it out.
>>>
>>> I have a table like so:
>>>
>>> id | ref_id | sts
>>> --
>>> 1  ||  0
>>> 2  | 1  |  1
>>> 3  ||  0
>>> 4  ||  0
>>> 5  | 4  |  1
>>> 6  ||  0
>>> 7  | 6  |  1
>>>
>>> I want to find the max(id) whose sts is 0 but whose id is not referenced
>>> by ref_id.
>>>
>>> so the answer would be id=3.
>>>
>>> Thanks for any pointers,
>>> Steve
>>>
>>> --
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>
> I think sts=0 means ref_id is null
>
> So, what I think he wants to achieve is:
>
> select max(id) from yourtable where sts=0 and id not in (select ref_id
> from yourtable);
>
> Isn't it?
>
> The OP will need to explain further as we are all guessing. As I mentioned
in my earlier (accidental top - curses GMail) post, table structures and
the query or queries that don't work would be useful. So would a
description of the problem that is being solved since there could be better
approaches.

Cheers,
Steve

Re: [GENERAL] dumb question

2016-06-02 Thread David G. Johnston 
On Thu, Jun 2, 2016 at 1:48 PM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos 
> wrote:
>
>> I think sts=0 means ref_id is null
>>
>> So, what I think he wants to achieve is:
>>
>> select max(id) from yourtable where sts=0 and id not in (select ref_id
>> from yourtable);
>>
>> Isn't it?
>>
>> The OP will need to explain further as we are all guessing. As I
> mentioned in my earlier (accidental top - curses GMail) post, table
> structures and the query or queries that don't work would be useful. So
> would a description of the problem that is being solved since there could
> be better approaches.
>
>
​Maybe we mis-interpreted but as written this is the solution.  My out-loud
thinking was a more verbose version of this.​


​David J.​

Re: [GENERAL] dumb question

2016-06-02 Thread Felipe Santos 
2016-06-02 14:23 GMT-03:00 Steve Crawford :

> Something like:
>
> select max(id) from yourtable where sts=0 and ref_id is null;
>
> That assumes that ref_id is null. It would help to see your table
> structure and the query you tried that doesn't work. If ref_id is actually
> a character string then you might need ref_id='' or coalesce(ref_id,'')=''
> if it can be null or empty string.
>
> Cheers,
> Steve
>
>
> On Thu, Jun 2, 2016 at 10:16 AM, Steve Clark 
> wrote:
>
>> Hi List,
>>
>> I am a noob trying to do something that seems like it should be easy but
>> I can't figure it out.
>>
>> I have a table like so:
>>
>> id | ref_id | sts
>> --
>> 1  ||  0
>> 2  | 1  |  1
>> 3  ||  0
>> 4  ||  0
>> 5  | 4  |  1
>> 6  ||  0
>> 7  | 6  |  1
>>
>> I want to find the max(id) whose sts is 0 but whose id is not referenced
>> by ref_id.
>>
>> so the answer would be id=3.
>>
>> Thanks for any pointers,
>> Steve
>>
>> --
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

I think sts=0 means ref_id is null

So, what I think he wants to achieve is:

select max(id) from yourtable where sts=0 and id not in (select ref_id from
yourtable);

Isn't it?

Re: [GENERAL] dumb question

2016-06-02 Thread Steve Crawford 
Something like:

select max(id) from yourtable where sts=0 and ref_id is null;

That assumes that ref_id is null. It would help to see your table structure
and the query you tried that doesn't work. If ref_id is actually a
character string then you might need ref_id='' or coalesce(ref_id,'')='' if
it can be null or empty string.

Cheers,
Steve


On Thu, Jun 2, 2016 at 10:16 AM, Steve Clark 
wrote:

> Hi List,
>
> I am a noob trying to do something that seems like it should be easy but I
> can't figure it out.
>
> I have a table like so:
>
> id | ref_id | sts
> --
> 1  ||  0
> 2  | 1  |  1
> 3  ||  0
> 4  ||  0
> 5  | 4  |  1
> 6  ||  0
> 7  | 6  |  1
>
> I want to find the max(id) whose sts is 0 but whose id is not referenced
> by ref_id.
>
> so the answer would be id=3.
>
> Thanks for any pointers,
> Steve
>
> --
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] dumb question

2016-06-02 Thread David G. Johnston 
On Thursday, June 2, 2016, Steve Clark  wrote:

> Hi List,
>
> I am a noob trying to do something that seems like it should be easy but I
> can't figure it out.
>
> I have a table like so:
>
> id | ref_id | sts
> --
> 1  ||  0
> 2  | 1  |  1
> 3  ||  0
> 4  ||  0
> 5  | 4  |  1
> 6  ||  0
> 7  | 6  |  1
>
> I want to find the max(id) whose sts is 0 but whose id is not referenced
> by ref_id.
>
> so the answer would be id=3.
>
> Thanks for any pointers,
> Steve
>
>
So, of all the rows whose sts is 0 and the id is not in (or not exists)
ref_idsubquery for selection
Give me the maximum id...parent query with group by.

David J.

[GENERAL] dumb question

2016-06-02 Thread Steve Clark 

Hi List,

I am a noob trying to do something that seems like it should be easy but I 
can't figure it out.

I have a table like so:

id | ref_id | sts
--
1  ||  0
2  | 1  |  1
3  ||  0
4  ||  0
5  | 4  |  1
6  ||  0
7  | 6  |  1

I want to find the max(id) whose sts is 0 but whose id is not referenced by 
ref_id.

so the answer would be id=3.

Thanks for any pointers,
Steve

--



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dumb question involving to_tsvector and a view

2013-02-24 Thread Kevin Grittner 
Jasen Betts ja...@xnet.co.nz wrote:
On 2013-02-23, Raymond C. Rodgers sinful...@gmail.com wrote:

 On 02/23/2013 05:26 AM, Tom Lane wrote:
 A virtual tsvector like that is probably going to be useless for
 searching as soon as you get a meaningful amount of data, because the
 only way the DB can implement a search is to compute the tsvector
 value for each table row and then examine it for the target word(s).
 What you want is a GIST or GIN index on the contents of the tsvector.

 I think the only real advantage to using something like this would be a
 space savings in terms of storing the tsvector data, but I don't see
 that being a significant enough reason to go ahead and use this idea in
 a production situation. As mentioned [by pretty much all of us], once
 the table size is sufficiently large there would be a performance
 penalty by to_tsvector being executed on every record in the table.

Unless the plan comes out as a table scan the index will be used
instead ot to_tsvector()

When there is a table scan to_tsvector will be used instead of reading
from disk, I don't know how fast to_tsvector is compared to disk, but
usually computing a result is faster than reading it from disk.

Storing the tsvector in the table is likely to be faster only when a
tablescan is done and the table is fully cached in ram.

I guess I was being dumb in assuming that it was obvious that a GIN
or GiST index would be needed for decent performance at scale. 
Without that, a scan of the whole table (or at least all rows
matching other search criteria) is needed, which is going to hurt. 
The benchmarks I mentioned were for a GIN index on the results of
the function which generated the tsvector, versus a GIN index on
the stored tsvector.  In our case, a typical scan for document text
against years of accumulated court documents was about 300 ms
versus about 1.5 seconds.  It may matter that we weren't just
looking for matches, but the top K matches based on the ranking
function.

-- 
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dumb question involving to_tsvector and a view

2013-02-23 Thread Tom Lane 
Kevin Grittner kgri...@ymail.com writes:
 Raymond C. Rodgers sinful...@gmail.com wrote:
 As I went to add a tsvector column, it occurred to me that it
 might be possible to add a dynamic tsvector column through the
 use of a view, so I created a temporary view with a command along
 the lines of:
 
  CREATE TEMPORARY VIEW ftstest AS SELECT id, field1, field2,
 TO_TSVECTOR(COALESCE(field1,'') || ' ' ||
 COALESCE(field2,'')) AS txtsrch FROM mytable;
 
 To my surprise, it worked. Now, I'm sitting here thinking about
 the performance impact that doing this would have.

 I had a similar situation and benchmarked it both ways.  For my
 situation I came out ahead writing the extra column for inserts and
 updates than generating the tsvector values on the fly each time it
 was queried.  YMMV.  It probably depends mostly on the ratio of
 inserts and updates to selects.

A virtual tsvector like that is probably going to be useless for
searching as soon as you get a meaningful amount of data, because the
only way the DB can implement a search is to compute the tsvector value
for each table row and then examine it for the target word(s).

What you want is a GIST or GIN index on the contents of the tsvector.
You can either realize the tsvector as a table column and put a regular
index on it, or you can build a functional index on the to_tsvector()
expression.  The latter is kind of like your idea in that the tsvector
as a whole isn't stored anywhere --- but there's an index containing all
the words, which is what you need for searching.

I think there are examples of both ways in the text search chapter of
the manual.  (If not, there should be ...)

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dumb question involving to_tsvector and a view

2013-02-23 Thread Raymond C. Rodgers 

On 02/23/2013 05:26 AM, Tom Lane wrote:
A virtual tsvector like that is probably going to be useless for 
searching as soon as you get a meaningful amount of data, because the 
only way the DB can implement a search is to compute the tsvector 
value for each table row and then examine it for the target word(s). 
What you want is a GIST or GIN index on the contents of the tsvector. 
You can either realize the tsvector as a table column and put a 
regular index on it, or you can build a functional index on the 
to_tsvector() expression. The latter is kind of like your idea in that 
the tsvector as a whole isn't stored anywhere --- but there's an index 
containing all the words, which is what you need for searching. I 
think there are examples of both ways in the text search chapter of 
the manual. (If not, there should be ...) regards, tom lane 
I think the only real advantage to using something like this would be a 
space savings in terms of storing the tsvector data, but I don't see 
that being a significant enough reason to go ahead and use this idea in 
a production situation. As mentioned [by pretty much all of us], once 
the table size is sufficiently large there would be a performance 
penalty by to_tsvector being executed on every record in the table. (If 
I'm not mistaken, with the way I wrote that create view, every record 
in mytable would be subject to the function call, then any narrowing 
parameters in the where clause would be applied afterwards.)


Any way, like I said originally, it was a dumb question. It might be ok 
to use that in a situation where the table size is known to be small, 
but there's little to no reason to do it in a production situation.


Thanks!
Raymond


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dumb question involving to_tsvector and a view

2013-02-23 Thread Jasen Betts 
On 2013-02-23, Raymond C. Rodgers sinful...@gmail.com wrote:
 On 02/23/2013 05:26 AM, Tom Lane wrote:
 A virtual tsvector like that is probably going to be useless for 
 searching as soon as you get a meaningful amount of data, because the 
 only way the DB can implement a search is to compute the tsvector 
 value for each table row and then examine it for the target word(s). 
 What you want is a GIST or GIN index on the contents of the tsvector.

 I think the only real advantage to using something like this would be a 
 space savings in terms of storing the tsvector data, but I don't see 
 that being a significant enough reason to go ahead and use this idea in 
 a production situation. As mentioned [by pretty much all of us], once 
 the table size is sufficiently large there would be a performance 
 penalty by to_tsvector being executed on every record in the table.

Unless the plan comes out as a table scan the index will be used
instead ot to_tsvector()

When there is a table scan to_tsvector will be used instead of reading
from disk, I don't know how fast to_tsvector is compared to disk, but
usually computing a result is faster than reading it from disk.

Storing the tsvector in the table is likely to be faster only when a
tablescan is done and the table is fully cached in ram.

-- 
⚂⚃ 100% natural



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Dumb question involving to_tsvector and a view

2013-02-22 Thread Raymond C. Rodgers 

Hi folks,
I'm building a PHP script for a web site I'm developing. At the 
moment, there is absolutely no real data in the database, so obviously 
performance is pretty good right now. I'm in the midst of developing an 
administration page for the site, which will do a full text search on 
several tables separately, and I realized that one of the tables 
currently doesn't have a tsvector column. As I went to add a tsvector 
column, it occurred to me that it might be possible to add a dynamic 
tsvector column through the use of a view, so I created a temporary view 
with a command along the lines of:


CREATE TEMPORARY VIEW ftstest AS SELECT id, field1, field2, 
TO_TSVECTOR(COALESCE(field1,'') || ' ' || COALESCE(field2,'')) AS 
txtsrch FROM mytable;


To my surprise, it worked. Now, I'm sitting here thinking about the 
performance impact that doing this would have. I can't help but think 
that a query to this view when the table is filled with thousands or 
tens of thousands of entries would be painfully slow, but would there be 
any real advantage to doing it in a view rather than just adding the 
column to the table? (That's the dumb question.) If the site only had a 
few dozen users, and the amount of data on the site was minimal, this 
wouldn't be too big an issue. Still a bad design decision, but are there 
any good reasons to do it?


Thanks for your patience with this dumb question. :)
Raymond


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dumb question involving to_tsvector and a view

2013-02-22 Thread Kevin Grittner 
Raymond C. Rodgers sinful...@gmail.com wrote:

 As I went to add a tsvector column, it occurred to me that it
 might be possible to add a dynamic tsvector column through the
 use of a view, so I created a temporary view with a command along
 the lines of:

 CREATE TEMPORARY VIEW ftstest AS SELECT id, field1, field2,
 TO_TSVECTOR(COALESCE(field1,'') || ' ' ||
 COALESCE(field2,'')) AS txtsrch FROM mytable;

 To my surprise, it worked. Now, I'm sitting here thinking about
 the performance impact that doing this would have.

I had a similar situation and benchmarked it both ways.  For my
situation I came out ahead writing the extra column for inserts and
updates than generating the tsvector values on the fly each time it
was queried.  YMMV.  It probably depends mostly on the ratio of
inserts and updates to selects.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Dumb question about binary cursors and #ifdef HAVE_INT64_TIMESTAMP

2007-12-11 Thread Dann Corbit 
If I create a binary cursor on a recent version of PostgreSQL, how can I
tell if the timestamp data internally is an 8 byte double or an 8 byte
integer?

I see an #ifdef that changes the code path to compute timestamps as one
type or the other, but I do not know how to recognize the internal
format of the type that will be returned in a binary cursor.

How can I do that?

Re: [GENERAL] Dumb question about binary cursors and #ifdef HAVE_INT64_TIMESTAMP

2007-12-11 Thread Tom Lane 
Dann Corbit [EMAIL PROTECTED] writes:
 If I create a binary cursor on a recent version of PostgreSQL, how can I
 tell if the timestamp data internally is an 8 byte double or an 8 byte
 integer?

PQparameterStatus(conn, integer_datetimes)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Dumb question about binary cursors and #ifdef HAVE_INT64_TIMESTAMP

2007-12-11 Thread Alvaro Herrera 
Dann Corbit wrote:
 If I create a binary cursor on a recent version of PostgreSQL, how can I
 tell if the timestamp data internally is an 8 byte double or an 8 byte
 integer?
 
 I see an #ifdef that changes the code path to compute timestamps as one
 type or the other, but I do not know how to recognize the internal
 format of the type that will be returned in a binary cursor.
 
 How can I do that?

SHOW integer_timestamp;

(actually, IIRC, this is one of the params that the server will send you
at session start).

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
Y eso te lo doy firmado con mis lágrimas (Fiebre del Loco)

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Dumb question about binary cursors and #ifdefHAVE_INT64_TIMESTAMP

2007-12-11 Thread Dann Corbit 
 -Original Message-
 From: Alvaro Herrera [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, December 11, 2007 1:11 PM
 To: Dann Corbit
 Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Dumb question about binary cursors and
 #ifdefHAVE_INT64_TIMESTAMP
 
 Dann Corbit wrote:
  If I create a binary cursor on a recent version of PostgreSQL, how
can I
  tell if the timestamp data internally is an 8 byte double or an 8
byte
  integer?
 
  I see an #ifdef that changes the code path to compute timestamps as
one
  type or the other, but I do not know how to recognize the internal
  format of the type that will be returned in a binary cursor.
 
  How can I do that?
 
 SHOW integer_timestamp;
 
 (actually, IIRC, this is one of the params that the server will send
you
 at session start).

I guess that I am supposed to check for error on the statement?  What
does it look like when the query works?

This is what I get against PostgreSQL 8.2.5 using PG Admin III query
tool:

ERROR:  unrecognized configuration parameter integer_timestamp

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Dumb question about binary cursors and #ifdefHAVE_INT64_TIMESTAMP

2007-12-11 Thread Dann Corbit 
 -Original Message-
 From: Alvaro Herrera [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, December 11, 2007 1:11 PM
 To: Dann Corbit
 Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Dumb question about binary cursors and
 #ifdefHAVE_INT64_TIMESTAMP
 
 Dann Corbit wrote:
  If I create a binary cursor on a recent version of PostgreSQL, how
can I
  tell if the timestamp data internally is an 8 byte double or an 8
byte
  integer?
 
  I see an #ifdef that changes the code path to compute timestamps as
one
  type or the other, but I do not know how to recognize the internal
  format of the type that will be returned in a binary cursor.
 
  How can I do that?
 
 SHOW integer_timestamp;
 
 (actually, IIRC, this is one of the params that the server will send
you
 at session start).

Tom's post clued me in.
It's:
show integer_datetimes;

Or (in my case):
PQparameterStatus(conn, integer_datetimes)


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

2007-02-13 Thread Pavan Deolasee 

On 2/13/07, Walter Vaughan [EMAIL PROTECTED] wrote:



select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
  last_autovacuum | last_autoanalyze
-+--
  |
...snip lots of identically blank lines...
  |
  |
(939 rows)

Does that mean it's working or not configured right?



It means auto vacuum/analyze did not trigger on any of the
tables. You may want to try:

SELECT name, setting from pg_settings where name like  '%autovacuum%';

to get the settings of autovacuum and check if autovacuum is turned on or
not.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

2007-02-13 Thread Shoaib Mir 

Make sure you have stats collector enabled, if auto vacuum is doing the
analyze and vacuum it should be recording that info in this view. For
details on this you can have a look at --
http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html

Just for a test try doing a VACUUM or ANALYZE manually and see if that gets
updated in the last_vacuum of pg_stats_all_tables.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/13/07, Walter Vaughan [EMAIL PROTECTED] wrote:


Shoaib Mir wrote:

 pg_stat_all_table view should help you:

 select last_autovacuum, last_autoanalyze from pg_stat_all_tables;

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
  last_autovacuum | last_autoanalyze
-+--
  |
...snip lots of identically blank lines...
  |
  |
(939 rows)

Does that mean it's working or not configured right?

Thanks,
Walter

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

2007-02-12 Thread Sic Transit Gloria Mundi 

Hi,

I couldnt find this on google, the archives, or the manual.  But with the 
changes to what the autovacuum daemon logs, how can we verify it's doing its 
thing?  Is there a way to query the last time a table was vacuumed?  But I 
don't see that in the system catalog.

Thanks!


 
-
Need Mail bonding?
Go to the Yahoo! Mail QA for great tips from Yahoo! Answers users.

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

2007-02-12 Thread Tom Lane 
Sic Transit Gloria Mundi [EMAIL PROTECTED] writes:
 I couldnt find this on google, the archives, or the manual.  But with the 
 changes to what the autovacuum daemon logs, how can we verify it's doing its 
 thing?  Is there a way to query the last time a table was vacuumed?  But I 
 don't see that in the system catalog.

Try the pg_stat views.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

2007-02-12 Thread Shoaib Mir 

pg_stat_all_table view should help you:

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/9/07, Sic Transit Gloria Mundi [EMAIL PROTECTED] wrote:



Hi,

I couldnt find this on google, the archives, or the manual.  But with the
changes to what the autovacuum daemon logs, how can we verify it's doing its
thing?  Is there a way to query the last time a table was vacuumed?  But I
don't see that in the system catalog.

Thanks!

--
Need Mail bonding?
Go to the Yahoo! Mail 
QAhttp://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTEwOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=asksid=396546091for
 great
tips from Yahoo! 
Answershttp://answers.yahoo.com/dir/index;_ylc=X3oDMTFvbGNhMGE3BF9TAzM5NjU0NTEwOARfcwMzOTY1NDUxMDMEc2VjA21haWxfdGFnbGluZQRzbGsDbWFpbF90YWcx?link=asksid=396546091users.

Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x

2007-02-12 Thread Walter Vaughan 

Shoaib Mir wrote:


pg_stat_all_table view should help you:

select last_autovacuum, last_autoanalyze from pg_stat_all_tables;


select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
 last_autovacuum | last_autoanalyze
-+--
 |
...snip lots of identically blank lines...
 |
 |
(939 rows)

Does that mean it's working or not configured right?

Thanks,
Walter

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] dumb question

2006-02-12 Thread Craig White 
How do I change the owner of a schema?

ALTER SCHEMA public OWNER to some_user;   #?

Craig


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] dumb question

2006-02-12 Thread Michael Glaesemann 


On Feb 13, 2006, at 10:29 , Craig White wrote:


How do I change the owner of a schema?

ALTER SCHEMA public OWNER to some_user;   #?


http://www.postgresql.org/docs/current/interactive/sql-alterschema.html

The docs explain this very situation. HTML documentation ships with  
the PostgreSQL distribution and can also be found online.


Hope this helps.

Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] dumb question

2006-02-12 Thread Craig White 
On Mon, 2006-02-13 at 11:07 +0900, Michael Glaesemann wrote:
 On Feb 13, 2006, at 10:29 , Craig White wrote:
 
  How do I change the owner of a schema?
 
  ALTER SCHEMA public OWNER to some_user;   #?
 
 http://www.postgresql.org/docs/current/interactive/sql-alterschema.html
 
 The docs explain this very situation. HTML documentation ships with  
 the PostgreSQL distribution and can also be found online.

seeing as how the above line seems to me to be exactly like the page
that you just referred me to, I have included some clips of my terminal
transactions because quite clearly I am too stupid to understand this...

th-db_test= ALTER SCHEMA public OWNER TO tobyhouse;
ERROR:  syntax error at or near OWNER at character 23

th-db_test= ALTER SCHEMA public OWNER TO tobyhouse;
ERROR:  syntax error at or near OWNER at character 21

# rpm -q postgresql-server
postgresql-server-7.4.8-1.RHEL4.1

Craig


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] dumb question

2006-02-12 Thread Michael Glaesemann 


On Feb 13, 2006, at 11:27 , Craig White wrote:


th-db_test= ALTER SCHEMA public OWNER TO tobyhouse;
ERROR:  syntax error at or near OWNER at character 21

# rpm -q postgresql-server
postgresql-server-7.4.8-1.RHEL4.1


The docs I referred you to are for the current release. For earlier  
releases, such as 7.4, you need to refer to the appropriate docs,  
such as:


http://www.postgresql.org/docs/7.4/interactive/sql-alterschema.html

It appears that the ability to assign a new owner to a schema via  
ALTER SCHEMA was introduced some time after 7.4.


You may be able to munge the system tables, but you may want to  
consider upgrading your PostgreSQL server installation.


Michael Glaesemann
grzm myrealbox com


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] dumb question

2006-02-12 Thread Craig White 
On Mon, 2006-02-13 at 11:39 +0900, Michael Glaesemann wrote:
 On Feb 13, 2006, at 11:27 , Craig White wrote:
 
  th-db_test= ALTER SCHEMA public OWNER TO tobyhouse;
  ERROR:  syntax error at or near OWNER at character 21
 
  # rpm -q postgresql-server
  postgresql-server-7.4.8-1.RHEL4.1
 
 The docs I referred you to are for the current release. For earlier  
 releases, such as 7.4, you need to refer to the appropriate docs,  
 such as:
 
 http://www.postgresql.org/docs/7.4/interactive/sql-alterschema.html
 
 It appears that the ability to assign a new owner to a schema via  
 ALTER SCHEMA was introduced some time after 7.4.
 
 You may be able to munge the system tables, but you may want to  
 consider upgrading your PostgreSQL server installation.

seems to be a lot of work just for this...the problem is trying to use
rubyonrails and their 'migrations' which cause this...

$ rake clone_structure_to_test --trace
(in /home/craig/ruby-db/th-db)
** Invoke clone_structure_to_test (first_time)
** Invoke db_structure_dump (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db_structure_dump
** Invoke purge_test_database (first_time)
** Invoke environment
** Execute purge_test_database
** Execute clone_structure_to_test
psql:db/development_structure.sql:28: NOTICE:  CREATE TABLE will create
implicit sequence case_managers_id_seq for serial column
case_managers.id
psql:db/development_structure.sql:57: NOTICE:  CREATE TABLE will create
implicit sequence placements_id_seq for serial column
placements.id
psql:db/development_structure.sql:70: NOTICE:  CREATE TABLE will create
implicit sequence referral_notes_id_seq for serial column
referral_notes.id
psql:db/development_structure.sql:86: NOTICE:  CREATE TABLE will create
implicit sequence clients_id_seq for serial column clients.id
psql:db/development_structure.sql:103: NOTICE:  CREATE TABLE will create
implicit sequence facilities_id_seq for serial column
facilities.id
psql:db/development_structure.sql:122: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index case_managers_pkey for table
case_managers
psql:db/development_structure.sql:131: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index placements_pkey for table
placements
psql:db/development_structure.sql:140: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index referral_notes_pkey for table
referral_notes
psql:db/development_structure.sql:149: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index clients_pkey for table
clients
psql:db/development_structure.sql:158: NOTICE:  ALTER TABLE / ADD
PRIMARY KEY will create implicit index facilities_pkey for table
facilities
psql:db/development_structure.sql:211: ERROR:  must be owner of schema
public

So how can I just 'munge' the system tables? I have granted all
privileges to this user.

Thanks

Craig


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] dumb question

2006-02-12 Thread Michael Glaesemann 


On Feb 13, 2006, at 11:47 , Craig White wrote:


seems to be a lot of work just for this...the problem is trying to use
rubyonrails and their 'migrations' which cause this...


snip /


psql:db/development_structure.sql:211: ERROR:  must be owner of schema
public

So how can I just 'munge' the system tables? I have granted all
privileges to this user.


In all honesty, they're not meant for general use because it is quite  
easy to irreparably screw up your database. The fact that you have to  
ask how to do this makes me think that it's not a good idea in your  
case. Documentation on the system tables (also known as the system  
catalog) is included in the PostgreSQL documentation. And before you  
do anything, I highly recommend backing up.


I do think it would be easier for you to upgrade. 7.4 is now two  
releases behind. There's a lot of good stuff in 8.1.


As for the issue with Ruby on Rails you mention above, you'll need a  
database user with superuser privileges. Newly created databases are  
based on templates, and the public schema in the templates is most  
likely owned by someone other than the user you're connecting as for  
your tests. To be able to change the owner of a schema, the user  
running the command needs to be a superuser.


Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-11 Thread Jim C. Nasby 
On Mon, Oct 10, 2005 at 10:59:03PM -0400, Tom Lane wrote:
 CSN [EMAIL PROTECTED] writes:
  If integer's range is -2147483648 to +2147483647, why
  is serial's range only 1 to 2147483647 instead of 1 to
  about 4294967294?
 
 How are you going to stuff 4294967294 into an integer field, which as
 you just stated has an upper limit of 2147483647?
 
 If we had an unsigned int type, we could use it for serial and get
 that result, but we do not.

Out of curiosity... why don't we have unsigned ints? I for one would
certainly use them for id fields, as well as some other places where I
knew negative numbers weren't valid.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-11 Thread Tom Lane 
Jim C. Nasby [EMAIL PROTECTED] writes:
 Out of curiosity... why don't we have unsigned ints?

Quick, is 42 an int or an unsigned int?

I think it'd create a slew of new ambiguous cases in the
numeric-datatype hierarchy, for what is really pretty darn small gain.
We're already just barely getting by the problem that 42 might be
intended as an int2 or int8 constant --- and at least those three
datatypes have compatible comparison semantics, so that there aren't any
fundamental semantic problems created if you decide that a constant is
one or the other.  Adding unsigned types to the mix seems to me to be
likely to cause some serious issues.

But feel free to give it a try, if you think it's worth a nontrivial
amount of work.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-11 Thread Michael Glaesemann 


On Oct 11, 2005, at 15:12 , Jim C. Nasby wrote:


Out of curiosity... why don't we have unsigned ints? I for one would
certainly use them for id fields, as well as some other places where I
knew negative numbers weren't valid.


Check the archives. I know this has come up a number of times in the  
past, but don't recall the reasons off the top of my head.


Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-11 Thread Dann Corbit 
How about something like:

CREATE DOMAIN unsigned_small AS smallint check (VALUE = 0)

CREATE DOMAIN unsigned_int AS integer check (VALUE = 0)

CREATE DOMAIN unsigned_big AS bigint check (VALUE = 0)

The objection might be that we lose one bit of field width.
But the extra safety is probably worth it if you really need unsigned
and want to avoid ambiguity.

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:pgsql-general-
 [EMAIL PROTECTED] On Behalf Of Jim C. Nasby
 Sent: Monday, October 10, 2005 11:12 PM
 To: Tom Lane
 Cc: CSN; pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Dumb question about serial's upper limit
 
 On Mon, Oct 10, 2005 at 10:59:03PM -0400, Tom Lane wrote:
  CSN [EMAIL PROTECTED] writes:
   If integer's range is -2147483648 to +2147483647, why
   is serial's range only 1 to 2147483647 instead of 1 to
   about 4294967294?
 
  How are you going to stuff 4294967294 into an integer field, which
as
  you just stated has an upper limit of 2147483647?
 
  If we had an unsigned int type, we could use it for serial and get
  that result, but we do not.
 
 Out of curiosity... why don't we have unsigned ints? I for one would
 certainly use them for id fields, as well as some other places where I
 knew negative numbers weren't valid.
 --
 Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
 Pervasive Software  http://pervasive.comwork: 512-231-6117
 vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461
 
 ---(end of
broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-11 Thread Jim C. Nasby 
On Mon, Oct 10, 2005 at 11:52:40PM -0700, Dann Corbit wrote:
 How about something like:
 
 CREATE DOMAIN unsigned_small AS smallint check (VALUE = 0)
 
 CREATE DOMAIN unsigned_int AS integer check (VALUE = 0)
 
 CREATE DOMAIN unsigned_big AS bigint check (VALUE = 0)
 
 The objection might be that we lose one bit of field width.
 But the extra safety is probably worth it if you really need unsigned
 and want to avoid ambiguity.

Certainly. But I was more interested in the space. :P
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-11 Thread Jim C. Nasby 
On Tue, Oct 11, 2005 at 02:22:23AM -0400, Tom Lane wrote:
 Jim C. Nasby [EMAIL PROTECTED] writes:
  Out of curiosity... why don't we have unsigned ints?
 
 Quick, is 42 an int or an unsigned int?
 
 I think it'd create a slew of new ambiguous cases in the
 numeric-datatype hierarchy, for what is really pretty darn small gain.
 We're already just barely getting by the problem that 42 might be
 intended as an int2 or int8 constant --- and at least those three
 datatypes have compatible comparison semantics, so that there aren't any
 fundamental semantic problems created if you decide that a constant is
 one or the other.  Adding unsigned types to the mix seems to me to be
 likely to cause some serious issues.

Couldn't the same logic of starting with the most restrictive case and
working up work here as well?
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Dumb question about serial's upper limit

2005-10-10 Thread CSN 
If integer's range is -2147483648 to +2147483647, why
is serial's range only 1 to 2147483647 instead of 1 to
about 4294967294?

CSN



__ 
Yahoo! Music Unlimited 
Access over 1 million songs. Try it free.
http://music.yahoo.com/unlimited/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-10 Thread Tom Lane 
CSN [EMAIL PROTECTED] writes:
 If integer's range is -2147483648 to +2147483647, why
 is serial's range only 1 to 2147483647 instead of 1 to
 about 4294967294?

How are you going to stuff 4294967294 into an integer field, which as
you just stated has an upper limit of 2147483647?

If we had an unsigned int type, we could use it for serial and get
that result, but we do not.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-10 Thread CSN 

--- Tom Lane [EMAIL PROTECTED] wrote:

 CSN [EMAIL PROTECTED] writes:
  If integer's range is -2147483648 to +2147483647,
 why
  is serial's range only 1 to 2147483647 instead of
 1 to
  about 4294967294?
 
 How are you going to stuff 4294967294 into an
 integer field, which as
 you just stated has an upper limit of 2147483647?
 
 If we had an unsigned int type, we could use it for
 serial and get
 that result, but we do not.
 
   regards, tom lane
 

I was thinking about the types in the C code behind
PostgreSQL, rather than types in PG itself. Been a
long time since I coded in C but I thought it had
unsigned ints and maybe data types could be mapped as
so (pardon my ignorance about C/PG's inner workings):

PG int = C signed int
PG serial = C unsigned int

Anyhow, was just something I was curious about.

CSN




__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Dumb question about serial's upper limit

2005-10-10 Thread Michael Glaesemann 


On Oct 11, 2005, at 14:04 , CSN wrote:


I was thinking about the types in the C code behind
PostgreSQL, rather than types in PG itself. Been a
long time since I coded in C but I thought it had
unsigned ints and maybe data types could be mapped as
so (pardon my ignorance about C/PG's inner workings):

PG int = C signed int
PG serial = C unsigned int


Serial is not a datatype per se; it's essentially a macro to create a  
sequence (foo_seq) and an INT column that has a default value of  
nextval('foo_seq').


Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Dumb question about 8.1 beta test

2005-08-27 Thread Mike Nolan 
The notes on participating in the 8.1 beta suggest creating a dump using
both an old and new copy of pg_dump.  

Does this mean we can't use pg_dumpall or that we have to restore both
dumps?  (Or is that just a way of testing what works and what doesn't
between older dump files and the beta release?)
--
Mike Nolan

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Dumb question about 8.1 beta test

2005-08-27 Thread Tom Lane 
Mike Nolan [EMAIL PROTECTED] writes:
 The notes on participating in the 8.1 beta suggest creating a dump using
 both an old and new copy of pg_dump.  

 Does this mean we can't use pg_dumpall or that we have to restore both
 dumps?  (Or is that just a way of testing what works and what doesn't
 between older dump files and the beta release?)

(1) There's no reason not to use pg_dumpall.  (2) I think the point of
the suggestion is to make sure we have some test coverage for both ways
of doing the upgrade ... but that doesn't mean each tester has to do it
both ways.  Pick one.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Dumb question about count()

2005-07-22 Thread Ezequiel Tolnay 

Benjamin Smith wrote:
Now, I want to get a result like: 

classroom | students | seats 
101A 	0	25

101B22  30
102A11  0
... etc. 

Something somewhat akin to 

select classroom.title, 
	count(students.id) AS students, 
	count(seats.id) AS seats

from classrooms, students, seats
where classrooms.id=students.classrooms_id
and classrooms.id=seats.id 


try

select classroom.title,
(select count(*) from students
 where classrooms_id = c.id) AS students,
(select count(*) from seats
 where classrooms_id = c.id) AS students,
count(seats.id) AS seats
from classrooms c

Cheers,

Eze

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[GENERAL] Dumb question about count()

2005-07-21 Thread Benjamin Smith 
I'm sure I've done this before, but for some reason, my main noodle is drawing 
a blank. Assume we have three tables defined thusly: 

create table classrooms (
id serial unique not null, 
name varchar
); 

create table seats (
classrooms_id integer not null references classrooms(id), 
position varchar
); 

create table students(
classrooms_id integer not null references classrooms(id), 
name varchar
); 

Now, I want to get a result like: 

classroom | students | seats 
101A0   25
101B22  30
102A11  0
... etc. 

Something somewhat akin to 

select classroom.title, 
count(students.id) AS students, 
count(seats.id) AS seats
from classrooms, students, seats
where classrooms.id=students.classrooms_id
and classrooms.id=seats.id 

Except that it counts 0s for seats/students. 

Why can't I recall/find how to do this particular join? 

-Ben 
-- 
The best way to predict the future is to invent it.
- XEROX PARC slogan, circa 1978

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Dumb question about count()

2005-07-21 Thread Michael Fuhr 
On Thu, Jul 21, 2005 at 10:09:07PM -0700, Benjamin Smith wrote:
 select classroom.title, 
   count(students.id) AS students, 
   count(seats.id) AS seats
 from classrooms, students, seats
 where classrooms.id=students.classrooms_id
 and classrooms.id=seats.id 
 
 Except that it counts 0s for seats/students. 
 
 Why can't I recall/find how to do this particular join? 

Sounds like you're looking for an outer join.

http://www.postgresql.org/docs/8.0/static/tutorial-join.html
http://www.postgresql.org/docs/8.0/static/queries-table-expressions.html#QUERIES-FROM

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] dumb question: multiple postmasters on Solaris

2001-05-23 Thread Andrew Sullivan 

Hi,

I believe this is a dumb question, and I have a sneaky feeling that I've
seen something about this on the list before, but I can't find it in the
archives.

I am used to using PostgreSQL on Linux, but we have moved some of our
work onto Solaris multiprocessor boxes.  Now, I see multiple instances
of postmaster -i in the output from ps.  Am I right in believing that
this is just an artifact of the way the OS deals with threads, or is
pg_ctl doing something evil and spawning extra back ends?  I _think_ the
latter is impossible, because there's already one back end bound to the
port.  But a little reassurance from someone experienced with Solaris
would sure make my day better!

Thanks,
A

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html