Re: [GENERAL] Problems with pgsql 7.1.2 and ExecEvalExpr
"Gregory S. Youngblood" <[EMAIL PROTECTED]> writes: > I got the data loaded that I need to work with, and am having a > problem with one specific query. It keeps dumping out with > ExecEvalExpr: unknown expressiong type 704. And the query is? The only known cause of that at the moment is if you do select foo from foo; 7.2 will give a more useful error message... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] aggregate function for median calculation
"Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes:
> I'm still a bit confused about how to declare the type of the transition state.
> I have a structure that will hold the current state:
> struct state {
> int elem_count; //how many numbers have been assigned so far
> int *elem_area; //ptr to area holding these integers whose median I'll later
>calculate
> }
> My question is: how do I declare this in Pg?
Since you haven't made this a full-fledged Postgres type, you don't.
There's no real *need* to make it a full-fledged type, actually, since
nobody except your two aggregate functions will ever manipulate the
value. So I'd suggest cheating. Make it a legal "varlena" value by
having the first word of the struct contain the total length in bytes
of the object (including itself). Then pretend in the SQL transition
function declaration and aggregate declaration that the transition
datatype is any old varlena type --- text or bytea would do fine.
Nothing except your code will look at anything except the varlena
length, so the fact that the body of the value means something unusual
won't matter.
Or, if you want to be rigidly correct, you could make the transition
value be a legitimate int4 array (_int4), which is only a field or
three more overhead. But I don't see any value in it, except possibly
for manual testing of your transition functions.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Update and cursor
Patrick COLLIN <[EMAIL PROTECTED]> writes: > I first tried a global update on each column, but I have not enough > memory and swap to do that. > FOR nouvEnreg IN SELECT * FROM mfnf00 LOOP >nouvCoupal := 2 * nouvEnreg.coupal; >UPDATE mfnf00 SET coupal = nouvCoupal > WHERE cbase = nouvEnreg.cbase AND > satel = nouvEnreg.satel AND > citm8 = nouvEnreg.citm8; > END LOOP; I think the problem here is not so much the UPDATEs as it is the SELECT; IIRC, plpgsql will try to fetch the whole result of the select into memory before it starts to run the loop. You could work around that, I think, by using a cursor to fetch the rows one at a time. But in this case, you're just coding a gratutiously inefficient way of doing a global update: why not replace the whole loop with UPDATE mfnf00 SET coupal = 2 * coupal; which will be vastly faster as well as not having a memory issue. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] postgres.h missing? (fwd)
"Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]> writes: > So do I just copy it manually from src/include/ ? >> >> add -I/usr/local/pgsql/include and -L/usr/local/pgsql/lib To do backend code development, you need to either add a -I pointing at the source include tree, or do make install-all-headers. If you are talking about client-side code, it probably shouldn't depend on any of the backend-side headers. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] aggregate function for median calculation
On Tue, 19 Jun 2001, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Sure, you create a (static) global variable and reallocate memory for it
> > in each call and free it by the finalizer function.
>
> A static would be a bad idea (consider a query with multiple instances
> of this aggregate being evaluated in parallel).
>
> But there's no reason that the transition function can't palloc a larger
> and larger chunk of memory for each result (as long as you don't run out
> of memory, anyway).
>
> regards, tom lane
>
I'm still a bit confused about how to declare the type of the transition state.
I have a structure that will hold the current state:
struct state {
int elem_count; //how many numbers have been assigned so far
int *elem_area; //ptr to area holding these integers whose median I'll later
calculate
}
So the transition function is:
struct state *trans_function(struct state *last_state,int new_element){
//Allocate mem for a new state structure to hold all previous numbers + new one
//Copy over all the previous elements from last_state and add the new_element
//Return ptr to this new state struct
}
My question is: how do I declare this in Pg?
declare function transition_func(???,int4) RETURNS ??? AS
'path_to_.so_file','trans_function' LANGUAGE 'C';
I assume that I have mixed up what can be a transition state :-/
Any help welcome.
TIA,
thalis
---(end of broadcast)---
TIP 3: 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] ODBC option question
Does anyone know what the option "Use declare fetch" on the windows ODBC driver mean? Is there anything in particular to be fine-tuned in ODBC when transfering large data sets? TIA, thalis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Web site gripes
> THESE ARE NOT WHINING GRIPES. They are pefectly valid points about > how the current website design is flawed. Your responses were pretty > much "Make your own bookmark", "it doesn't work", and "you aren't > working hard enough to find things." This attitude is unacceptable. I thought that was the main benefit of open source, you didn't have to write good documentation. : ) On a realistic note, did you really have to CC 4 or 5 people on that? I'm sure that they all read the list, and don't really need duplicate copies. steve ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Web site gripes
On Wed, 20 Jun 2001, Adam Haberlach wrote: > On Wed, Jun 20, 2001 at 04:38:58PM -0400, Vince Vielhaber wrote: > > On Wed, 20 Jun 2001, Norman J. Clarke wrote: > > > > > You do an excellent job Vince. By and large the website is awesome, and I > > > love some of the newest additions such as the interactive docs. But you > > > should listen to his gripes and respond in a more polite way. If you think > > > he is wrong, then explain why. > > > > > > Responding with a personal attack is not helpful at all and seems really > > > out of sync with the tone of the rest of the list. When I saw his first > > > email I cringed because from reading your previous posts I knew you were > > > going to respond exactly as you did. > > > > I'll try to keep this plain and simple. > > > > If you (anyone) have suggestions for the website, I'm all ears. But if > > you're looking to be blown off, insulted, treated rudely, etc. then you > > need to preface it with a bunch of whining gripes that have little to > > no foundation. Make yourself look as clueless as possible. If you're > > going to go looking for things to bitch about as your lead-in to the > > suggestions you have, don't get upset if I tell you where to go. If I > > did it to you I'd expect the same result. On the flip side of the coin, > > I'm not one for praises either. > > Ok, I've held my tongue on this for a while, now, but your attitude > is just annoying the shit out of me. I'll keep it plain and simple, > too. You started out fine, then you got to the whining: > o. When I /do/ make it to a docs page, I end up at a "oldest stuff at > the top" list, when it is likely that the first thing people want is > going to be the newest docs. > > o. When I finally make it to the documentation for my version, I get > a list of no less then 38 different links, covering almost all > permutations of That should be enough, but as you know (since you wrote it) there is more. I'm not going to get into it again, you don't like my attitude, I don't like yours or your whining. End of conversation. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Hardware Config
> > Will PostgreSQL take advantage of more than 1 CPU? If so, are there any > > benchmarks showing how it scales with multiple CPUs? We run PG on a quad xeon, and it works wonderfully. From PG's design of forking off a new backend for each connection, it is inherantly scalable, as each backend can run on another CPU. And, of course, having extra CPU's to handle kernel code, I/O, interrupts, and other things is always good. steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] performance tuning or real bug ?
On 14 Jun 2001, denis wrote: > I use a linux/mandrake 7.2 on PIII 350 > when doing > 1 - create an initialisation file > i=0; > loadfile="/usr/local/pgsql/param/loadfile" > rm -fr $loadfile ; > #creating a file with 1500 records > while [ $i -lt 1500 ] ; do > i=`expr $i + 1`; > mod=`expr $i % 10`; > #creating a field amount with different numbers > mont=`expr $mod \* 18 + $i `; > echo $i"|"nom$i"|"prenom$i"|"$mont>>$loadfile; > if [ $mod -eq 0 ] ; then > echo " $i lignes created " ; > fi > done > echo "Load file done " ; > > * 2 - creating and populating database > psql essai <<++ > create table names ( > id integer, > nom char(40) , > prenom char(20), > montant decimal > ); > copy names from '/usr/local/pgsql/param/loadfile' delimiters '|'; > create unique index id_names on names(id); > create index nom_names on names(nom); ++ > > > ** 3 - executing a select > psql essai <<++ > select sum (montant) from names > where nom in (select nom from names where nom like '%1%' ); > ++ > > *** 4 - checking results > command : time sql.sh > sum > --- > 787494.00 > (1 row) > > 0.01user 0.01system 0:12.08elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k > 0inputs+0outputs (421major+110minor)pagefaults 0swaps > > it took 12 seconds (I did the same with an informix SE database and the result is > (sum) > >787494.00 > > 1 row(s) retrieved. > real0m0.62s > user0m0.03s > sys 0m0.03s > > ** 5 - Other tests > if I change the like clause and execute : > psql essai <<++ > select sum (montant) from names > where nom in (select nom from names where nom like '%12%' ); > ++ > > the result is > sum > --- > 157132.00 > (1 row) > > 0.02user 0.01system 0:00.56elapsed 5%CPU (0avgtext+0avgdata 0maxresident)k > 0inputs+0outputs (422major+109minor)pagefaults 0swaps > > with informix it's nearly the same > > In both cases I also did the same whith dropping the indexes > and the results are quite the same. > > can someone explain me why there's a so huge difference > between LIKE '%1%' and LIKE '%12%' ? You're probably running into problems with the IN, which tends to be slow (see the FAQ for workarounds). Also, why are you using an in, isn't the above equivalent to: select sum(montant) from names where nom like '%12%'; -- or '%1%' ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Copy Error
Give the fullname of the file. Make sure the file is on the server machine and not your local machine (in case you are forwarding pgaccess to another X terminal) cheers, thalis On 16 Jun 2001, Chakravarthy K Sannedhi wrote: > Hi, > > When I am trying to import a file in my home directory into the the > pgsql database using the *pgaccess* gui tool, it is giving the > following message. > > ERROR: COPY command, running in backend with effective uid 26, could > not open file 'myfile' for reading. Errno=No such file > > Can any one please tell me what I am missing here. Is it necessary for > the 'postgresql' to own the file named 'myfile' under my home > directory. > > TIA > Chakravarthy K Sannedhi > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] ExecEvalExpr: unknown expression type 704 problems
On Wed, 20 Jun 2001, Gregory Youngblood wrote: > I have an ID column which is a bigint, and I have another ID column which was > created using SERIAL, so it is an integer. > > Is it possible to use SERIAL to get a int8 datatype instead int (int4)? Not as far as I know, but there is talk to actually make it int8 in the future. > > Finally, is it possible to get unsigned integer data types in a table? No. cheers, thalis > > Thanks, > Greg > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Hardware Config
>From a previous thread, I remember being said that it is mostly an OS issue. So if >you make your OS kernel aware of your multiple CPUs, Pg will be just fine. cheers, thalis On Thu, 14 Jun 2001, Rich Bowman wrote: > Will PostgreSQL take advantage of more than 1 CPU? If so, are there any > benchmarks showing how it scales with multiple CPUs? > > -- > Rich Bowman > Bennett Supply Company > [EMAIL PROTECTED] > > > > ---(end of broadcast)--- > TIP 3: 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 > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] problem with 010.pgsql.sh start
Do you have the postmaster configured to listen on the port as opposed to only the local socket? (Either the -i command line option or an option in the postgres.conf should do it I believe) On Sat, 16 Jun 2001, Sam Wun wrote: > Hi, > > When I finished running ./010.pgsql.sh start, then tried to run my php > script, it reports the following err msg: > > Warning: Unable to connect to PostgreSQL server: connectDBStart() -- > connect() failed: Connection refused Is the postmaster > running (with -i) at 'fastline' and accepting > connections on TCP/IP port 5432? in > /usr/local/apache/phplib/db_pgsql.inc on line 40 > > Database error: Link-ID == false, pconnect failed > PostgreSQL Error: 0 () > Session halted. > > I found that port 5432 is not being listening. > > The following is the output of ps -axf | grep pg command: > > root@fastline /<2>etc/rc.d # ps -axf | grep pg > 623 p2 I 0:00.03 /usr/local/bin/postmaster -D > /usr/local/pgsql/data (postgres) > > My question is why port# 5432 is not running if the pgsql.sh start > running successfuly? unless the pgsql.sh start command failed. > > Does anyone have any suggestion about this problem? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Web site gripes
On Wed, 20 Jun 2001, Norman J. Clarke wrote: > You do an excellent job Vince. By and large the website is awesome, and I > love some of the newest additions such as the interactive docs. But you > should listen to his gripes and respond in a more polite way. If you think > he is wrong, then explain why. > > Responding with a personal attack is not helpful at all and seems really > out of sync with the tone of the rest of the list. When I saw his first > email I cringed because from reading your previous posts I knew you were > going to respond exactly as you did. I'll try to keep this plain and simple. If you (anyone) have suggestions for the website, I'm all ears. But if you're looking to be blown off, insulted, treated rudely, etc. then you need to preface it with a bunch of whining gripes that have little to no foundation. Make yourself look as clueless as possible. If you're going to go looking for things to bitch about as your lead-in to the suggestions you have, don't get upset if I tell you where to go. If I did it to you I'd expect the same result. On the flip side of the coin, I'm not one for praises either. Now then, even tho you can find it in the archives, I'll let everyone in on some of the reasons for the site's layout and why some things happen and some don't. Documentation: The release docs and the development docs (the ones that don't apply to the released version - things not there, things working differently, etc) have to be kept as far apart as possible. Time and time again I/we'd get email from people crabbing about how they depended on a specific feature that's in the documentation that doesn't work - only to find out they're reading the devel docs and using the release version or even an older version of the software. In the release docs section all of the indexes are created with a couple of scripts so a bunch of editing doesn't have to take place if things are added (such as a pdf version) or removed. Just run the script. This happens alot at release time for one reason or another. When you click on English docs, you get a page that has links for the different versions with the current release at the top. True, they're links to anchors on the same page but they're not visible at that time unless your browser's broken or you have your browser set to some rather large size. The anchors are far enough away that it shouldn't matter what order they're in. In the developer docs some work is still needed there to make it simpler. Mailing List Info: This too is separated between stuff that users are interested in and what developers are interested in. Why? Partly because too many people were getting confused about what lists they should be reading. Imagine, the hackers list was too advanced for a few folks. What's the difference between a developer and a user? A developer is someone that's actually working on the PostgreSQL sources either making additions, fixing bugs, working on docs, etc. for future releases. A user is one who uses, administrates, develops apps that use PostgreSQL, etc. typically in the current release. If someone has a better suggestion for names for developer and user, I'm all ears - but note the comments in the first paragraph (not the first sentence). Why Aren't Betas Announced? I tried using red, red bold, red bold and larger font size but too many people didn't see it and complained rather loudly that this version they found on the website didn't work and they lost their data. Never mind the fact it was beta, had beta in the filename, said on the website in big bold red letters that it was beta, if it was there, it should have been safe to use. Why isn't the webmaster's address on the website? If you made it this far on the internet that you're using, deploying, developing, etc. an open source database then [EMAIL PROTECTED] should be a no-brainer. When it was there I got 60-70 HotBabes/AcceptCreditCards/GetALoan/etc... spams each and every day. Now it's finally down to about 10/day. If, however, anyone wants me to forward some of the spam I get to them 'cuze they like getting that kinda stuff, I'll be more than happy to share!! :) BTW, I'm planning on a new look for the website in the not too distant future - but with graduations, weddings, etc. at this time of the year there's no set date. Perhaps the new look will satisify some of the things that folks dislike and/or maybe some of the things will flow together better, but I still expect that not everyone will be happy and many will be downright upset about it. Hmmm, maybe I'll do it in frames.. Lotsa little frames.. *evil laugh* Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstoreht
Re: [GENERAL] Red Hat DB announced - why not PostgreSQL?
> It looks like Red Hat has announced an open source database called Red Hat > Database: > > (via slashdot:) > http://dailynews.yahoo.com/h/cn/20010619/tc/red_hat_to_play_in_oracle_s_ar ena_1.html > > Why is this not PostgreSQL? Why can't the Red Hat folks sell support to > PostgreSQL, rather than developing another open source database and > supporting that? Is it a conflict with GreatBridge? This looks like it could > have been a great opportunity for PostgreSQL If it's "Red Hat Database", then not only does it promote their brand and supposed worth, then people will more readily come to them for a support contract. If it were Postgres, they'd probably just sign up to the mailing list. : ) steve ---(end of broadcast)--- TIP 3: 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] Red Hat Database
Also the comment that RHDB would be better for small businesses than large ones makes me think that it's not exactly high-performance or full-featured. : ) steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
