[SQL] C functions and int8?
I have written a few Postgres extension functions in C, and want to modify some of them to return an int8. However, I don't see any int8 definition in postgres.h. (I have the 7.0.2 RPMs installed.) How should I accomplish this?
Re: [SQL] C functions and int8?
On Thu, 21 Sep 2000, Forest Wilkinson wrote: > I have written a few Postgres extension functions in C, and want to modify > some of them to return an int8. However, I don't see any int8 definition > in postgres.h. (I have the 7.0.2 RPMs installed.) How should I > accomplish this? in the source tree: src/include/c.h typedef signed char int8; /* == 8 bits */ ... but I not sure if this file is included in the RPM package. Karel
Re: [SQL] sql query not using indexes
On Wed, 20 Sep 2000, Stephan Szabo wrote: > On Wed, 20 Sep 2000, User Lenzi wrote: > > > if I start a query: > > > > explain select * from teste where login = 'xxx' > > results: > > Index Scan using teste1 on teste (cost=0.00..97.88 rows=25 ) > > > > > > however a query: > > explain select * from teste where login > 'AAA' > > results: > > Seq Scan on teste > > > > > > On a machine running version 6.5 both queries results index scan. > > > > this results that the version 6.5 is faster than version 7.0.2 on this > > kind of > > query. > > > > > > Any explanation??? > > Have you done a vacuum analyze on the table? Also, what does the row > count for the second query look like? It's probably deciding that > there are too many rows that will match login >'AAA' for index scan > to be cost effective. So, actually, also, what does > select count(*) from teste where login>'AAA" give you on the 7.0.2 box. Ok I agree with you on the real database there are 127,300 rows and there are certanly a great number of rows > 'AAA'. But, supose I make a query select * from table where code > 'AAA' limit 10. it will read the entire table only to give me the first 10 while in release 6.5 it will fetch the index for the first 10 in a very fast manner, indeed the 6.5 release resolves in 1 second while the 7.0 release resolves in 10-20 sec. Is there a way to tell the optimizer to consider going on indixes?? I did make a vaccum analyze on both tables.. and the result remains the same... Thanks for any help...
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
At 11:29 AM 9/21/00 +1000, you wrote: >The main reason I use them is to find the 'next' or 'previous' record in a >list (eg. next date, next ID). eg. > > select , (select ID from table where id > this.id > order by id asc limit 1) as next_id ... Doesn't this give the same result (without order by): > select , (select min(ID) from table where id > this.id) as next_id Frank
[SQL] Multiple Index's
Hello all, How would I prevent a user from submitting information to a table once they have already done so for that day. I would need them to be able information on future dates as well as have information in the table from past dates from that user. I am looking for something like insert user_id, date, info where user_id and date are not the same... does that make sense? Brian C. Doyle
Re: [SQL] Multiple Index's
> Hello all, > > How would I prevent a user from submitting information to a table once they > have already done so for that day. The best you could probably do is to go back and delete undesired recoords at the end of the day because if it is as you said, they've already put the information into the database. > I would need them to be able > information on future dates as well as have information in the table from > past dates from that user. Not positive what you mean here but just use a date (or timestamp) column in the table to indicate when the record was added. > I am looking for something like insert user_id, date, info where user_id > and date are not the same... does that make sense? Nope, it doesn't -- at least to me :-) How about some table structures and some more information, I'm just not exactly sure what you'd like to do.. -Mitch
Re: [SQL] sql query not using indexes
On Thu, 21 Sep 2000, Sergio de Almeida Lenzi wrote: > > > On a machine running version 6.5 both queries results index scan. > > > > > > this results that the version 6.5 is faster than version 7.0.2 on this > > > kind of > > > query. > > > > > > > > > Any explanation??? > > > > Have you done a vacuum analyze on the table? Also, what does the row > > count for the second query look like? It's probably deciding that > > there are too many rows that will match login >'AAA' for index scan > > to be cost effective. So, actually, also, what does > > select count(*) from teste where login>'AAA" give you on the 7.0.2 box. > > Ok I agree with you on the real database there are 127,300 rows and there > are certanly a great number of rows > 'AAA'. But, supose I make a query > select * from table where code > 'AAA' limit 10. it will read the entire > table only to give me the first 10 while in release 6.5 it will fetch the > index for the first 10 in a very fast manner, indeed the 6.5 release > resolves in 1 second while the 7.0 release resolves in 10-20 sec. Hmm, I believe Tom Lane was doing alot of stuff with the optimizer and limit but I don't remember if that was before or after the 7.0 release. It might be worth trying on current sources to see if that goes back to an index scan. Or if your data set is safe to give out, I could try it on my current source machine. > Is there a way to tell the optimizer to consider going on indixes?? Well, there is a SET you can do to turn off seqscans unless that's the only way to go, but that's a broad instrument since it affects all statements until you change it back.
Re: [SQL] Multiple Index's
On Thu, 21 Sep 2000, Brian C. Doyle wrote: > Hello all, > > How would I prevent a user from submitting information to a table once they > have already done so for that day. I would need them to be able > information on future dates as well as have information in the table from > past dates from that user. > > I am looking for something like insert user_id, date, info where user_id > and date are not the same... does that make sense? If you want the first data to go through, maybe a unique index on (user_id, date) would work.
Re: [SQL] Multiple Index's
CREATE TABLE user_info(user_id name, entry_date date, info text); CREATE UNIQUE INDEX user_info_key ON user_info(user_id, entry_date); "Brian C. Doyle" wrote: > > Hello all, > > How would I prevent a user from submitting information to a table once they > have already done so for that day. I would need them to be able > information on future dates as well as have information in the table from > past dates from that user. > > I am looking for something like insert user_id, date, info where user_id > and date are not the same... does that make sense? > > Brian C. Doyle
Re: [SQL] Multiple Index's
See if this help the table has userid | date | helped_customers An employ will enter in their userid, the date and how many customer they helped that day. What I want to do is prevent the employees from enter the data more than once a day At 10:28 AM 9/21/00 -0700, Mitch Vincent wrote: > > Hello all, > > > > How would I prevent a user from submitting information to a table once >they > > have already done so for that day. > >The best you could probably do is to go back and delete undesired recoords >at the end of the day because if it is as you said, they've already put the >information into the database. > > > I would need them to be able > > information on future dates as well as have information in the table from > > past dates from that user. > >Not positive what you mean here but just use a date (or timestamp) column in >the table to indicate when the record was added. > > > I am looking for something like insert user_id, date, info where user_id > > and date are not the same... does that make sense? > >Nope, it doesn't -- at least to me :-) > >How about some table structures and some more information, I'm just not >exactly sure what you'd like to do.. > >-Mitch
Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]
At 11:50 21/09/00 -0400, Frank Bax wrote: >At 11:29 AM 9/21/00 +1000, you wrote: >>The main reason I use them is to find the 'next' or 'previous' record in a >>list (eg. next date, next ID). eg. >> >> select , (select ID from table where id > this.id >> order by id asc limit 1) as next_id ... > >Doesn't this give the same result (without order by): > >> select , (select min(ID) from table where id > this.id) as >next_id Yes, but I don't think PostgreSQL is smart enough to use indexes to evaluate the Min() function. Also, min/max does not work quite so well with a slightly more complex example: select , (select ID from table where date_field > this.date_field order by date_field asc limit 1) as next_id ... (ie. if the date_field and id are not correlated, but you want the id corresponding to the next date). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
[SQL] Convert from Seconds-Since-Epoch to Timestamp
Hi all, How do I convert from seconds (an integer) to timestamp? I am sure it is easy, but I can't find it in the docs, so far. Thanks Webb __ Do You Yahoo!? Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
Hi, Webb, I am not quit sure what you really want to do, however, I assume that following maybe what you mean: db=# select interval(reltime '12313221 secs'); interval 4 mons 22 12:20:21 (1 row) Webb Sprague wrote: > Hi all, > > How do I convert from seconds (an integer) to > timestamp? > > I am sure it is easy, but I can't find it in the docs, > so far. > > Thanks > Webb > > __ > Do You Yahoo!? > Send instant messages & get email alerts with Yahoo! Messenger. > http://im.yahoo.com/ -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
how would you do that with in a query? ie select date , interval(reltime 'table.secs'); when the value in table.secs = 54321 and "secs" is not a part of it? At 11:35 AM 9/21/00 -0700, Jie Liang wrote: >Hi, Webb, > >I am not quit sure what you really want to do, however, I assume that >following maybe >what you mean: > > >db=# select interval(reltime '12313221 secs'); > interval > > 4 mons 22 12:20:21 >(1 row) > > >Webb Sprague wrote: > > > Hi all, > > > > How do I convert from seconds (an integer) to > > timestamp? > > > > I am sure it is easy, but I can't find it in the docs, > > so far. > > > > Thanks > > Webb > > > > __ > > Do You Yahoo!? > > Send instant messages & get email alerts with Yahoo! Messenger. > > http://im.yahoo.com/ > >-- >Jie LIANG > >Internet Products Inc. > >10350 Science Center Drive >Suite 100, San Diego, CA 92121 >Office:(858)320-4873 > >[EMAIL PROTECTED] >www.ipinc.com
Re: [SQL] Convert from Seconds-Since-Epoch to Timestamp
Hi, there, urldb=# create table foo(sec int4); CREATE urldb=# insert into foo values(54321); INSERT 382942319 1 urldb=# select interval(reltime (sec||'secs')) from foo; interval -- 15:05:21 (1 row) "Brian C. Doyle" wrote: > how would you do that with in a query? > > ie select date , interval(reltime 'table.secs'); > > when the value in table.secs = 54321 and "secs" is not a part of it? > > At 11:35 AM 9/21/00 -0700, Jie Liang wrote: > >Hi, Webb, > > > >I am not quit sure what you really want to do, however, I assume that > >following maybe > >what you mean: > > > > > >db=# select interval(reltime '12313221 secs'); > > interval > > > > 4 mons 22 12:20:21 > >(1 row) > > > > > >Webb Sprague wrote: > > > > > Hi all, > > > > > > How do I convert from seconds (an integer) to > > > timestamp? > > > > > > I am sure it is easy, but I can't find it in the docs, > > > so far. > > > > > > Thanks > > > Webb > > > > > > __ > > > Do You Yahoo!? > > > Send instant messages & get email alerts with Yahoo! Messenger. > > > http://im.yahoo.com/ > > > >-- > >Jie LIANG > > > >Internet Products Inc. > > > >10350 Science Center Drive > >Suite 100, San Diego, CA 92121 > >Office:(858)320-4873 > > > >[EMAIL PROTECTED] > >www.ipinc.com -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
RE: [SQL] sql query not using indexes
> -Original Message- > From: Sergio de Almeida Lenzi > > On Wed, 20 Sep 2000, Stephan Szabo wrote: > > > On Wed, 20 Sep 2000, User Lenzi wrote: > > > > > if I start a query: > > > > > > explain select * from teste where login = 'xxx' > > > results: > > > Index Scan using teste1 on teste (cost=0.00..97.88 rows=25 ) > > > > > > > > > however a query: > > > explain select * from teste where login > 'AAA' > > > results: > > > Seq Scan on teste > > > > > > > > > On a machine running version 6.5 both queries results index scan. > > > > > > this results that the version 6.5 is faster than version 7.0.2 on this > > > kind of > > > query. > > > > > > > > > Any explanation??? > > > > Have you done a vacuum analyze on the table? Also, what does the row > > count for the second query look like? It's probably deciding that > > there are too many rows that will match login >'AAA' for index scan > > to be cost effective. So, actually, also, what does > > select count(*) from teste where login>'AAA" give you on the 7.0.2 box. > > Ok I agree with you on the real database there are 127,300 rows and there > are certanly a great number of rows > 'AAA'. But, supose I make a query > select * from table where code > 'AAA' limit 10. it will read the entire > table only to give me the first 10 while in release 6.5 it will fetch the > index for the first 10 in a very fast manner, indeed the 6.5 release > resolves in 1 second while the 7.0 release resolves in 10-20 sec. > Probably the distribution of rows in teste where login > 'AAA' isn't uniform. You had better add 'ORDER BY login' to your query. Regards. Hiroshi Inoue
Re: [SQL] C functions and int8?
That's an int8 meaning "eight bit integer". I want to work with an int8 meaning "64 bit integer", as described in the docs: http://www.postgresql.org/users-lounge/docs/7.0/user/datatype.htm#AEN942 So how do I return one of these suckers from a C function? Forest On Thu, 21 Sep 2000 10:32:50 +0200 (CEST), Karel Zak wrote: > >On Thu, 21 Sep 2000, Forest Wilkinson wrote: > >> I have written a few Postgres extension functions in C, and want to modify >> some of them to return an int8. However, I don't see any int8 definition >> in postgres.h. (I have the 7.0.2 RPMs installed.) How should I >> accomplish this? > > in the source tree: src/include/c.h > > typedef signed char int8; /* == 8 bits */ > > > ... but I not sure if this file is included in the RPM package. > > > Karel
Re: [SQL] C functions and int8?
Forest Wilkinson <[EMAIL PROTECTED]> writes:
> That's an int8 meaning "eight bit integer". I want to work with an int8
> meaning "64 bit integer", as described in the docs:
> http://www.postgresql.org/users-lounge/docs/7.0/user/datatype.htm#AEN942
> So how do I return one of these suckers from a C function?
Emulate the code in src/backend/utils/adt/int8.c.
Currently this involves palloc'ing an int8, setting it, and returning
a pointer to it. For instance, int8 addition is
int64 *
int8pl(int64 *val1, int64 *val2)
{
int64 *result = palloc(sizeof(int64));
if ((!PointerIsValid(val1)) || (!PointerIsValid(val2)))
return NULL;
*result = *val1 + *val2;
return result;
}
In 7.1 it'll be a lot cleaner (IMNSHO anyway ;-)):
Datum
int8pl(PG_FUNCTION_ARGS)
{
int64val1 = PG_GETARG_INT64(0);
int64val2 = PG_GETARG_INT64(1);
PG_RETURN_INT64(val1 + val2);
}
which actually does about the same things under the hood, but you
don't have to sully your hands with 'em ...
regards, tom lane
