Re: [GENERAL] I got bit by that darn GEQO setting again...
Mike Mascari <[EMAIL PROTECTED]> writes: But just as a quick notice to those upgrading from 7.3 to 7.4 with fully normalized databases requiring > 11 joins, the GEQO setting can be a killer... Uh ... dare I ask whether you think it's too high? Or too low? Just a data point: With a fresh 7.4 and geqo=on geqo_threshold=11 geqo_generations=0 geqo_effort=1 geqo_pool_size=0 geqo_selection_bias=2 a 12 table join was taking a whole second to plan until I manually connected the tables (now it's in the order of a few ms). I figure geqo had kicked in at that point. d. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] I got bit by that darn GEQO setting again...
Mike Mascari <[EMAIL PROTECTED]> writes: > But just as a quick notice to those upgrading from 7.3 to 7.4 with fully > normalized databases requiring > 11 joins, the GEQO setting can be a > killer... Uh ... dare I ask whether you think it's too high? Or too low? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] I got bit by that darn GEQO setting again...
I know Tom would like a definitive and thorough testing to determine the proper GEQO threshold limit , and that is the right thing to do, of course. But just as a quick notice to those upgrading from 7.3 to 7.4 with fully normalized databases requiring > 11 joins, the GEQO setting can be a killer... How about a TIP: "For large number of joins, test whether the GEQO settings are right for you" Mike Mascari ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] 7.4 -> 7.4.1 upgrade with customization on freebsd
I installed 7.4 from the FreeBSD ports collection (I'm running 4.9-RELEASE). Did nothing special, just make && make install and all was well. I now have updated my ports collection via cvsup and see that the 7.4.1 port is available. So now I have several, very specific needs: 1) having now done some development in 7.4, I've decided I need to be able to exceed the default limit of 16 arguments for plpgsql functions 2) I would like to upgrade to 7.4.1 without having to re-edit any config files or change any paths. 3) I would like to prevent the database shown by "pkg_version -v" from having one entry for 7.4 and another for 7.4.1 Suggestions? Caveat: I'm a java + sql centered coder and makefiles make me wince. Bill McMilleon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: embedded/"serverless" (Re: [GENERAL] serverless postgresql)
Chris Ochs wrote: I still have to respectfully disagree. Postgresql is IMO just the wrong software for the job, and given that there are still a number of really important things that postgresql lacks, it should concentrate on those.I am not against it however for technical reasons, because those things can always be overcome. I just wouldn't want postgresql to start branching out in different directions at this point, it makes no sense if the project wants to keep focused and one day become comparable side by side to oracle. IMO that should be it's main goal, and embedded functionality would be a detour that has more chances of doing harm then good. I believe the basic mission of Postgres will stay the same [for a long time]: providing a 1) reliable database; 2) with advanced; 3) and standard compliant features. However, venturing into win32 world _will_ generate demands like embedded and other desktop app-oriented features since I expect that's what many people will using Postgres for in win32 (currently people are using IB/FB for this and not MySQL/Postgre; MySQL's embedded version is GPL/commercial). And we all know it's all a matter of what itches the developers the most. If enough people are bugging them about something, they will do it... :-) -- dave ---(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] YAGT (yet another GUID thread)
David Helgason wrote: I'm already using 'real' GUIDs, which in my case means that the database never generates them (since I don't have a generate_real_guid() function in the database (and don't need to). Neither GUID project on gborg (mentioned in another thread) seem to be Mac OSX compatible, which is my current platform (but I want to stay more-or-less free of platform dependance just yet). What do you use for generating GUID in OSX? Does OSX have a GUID generation API/syscall? What is its algorithm, does it show the MAC address verbatim on the resulting GUID? However the client app uses a hex representation internally (don't tell me it's silly, it's already coded, due to intricacies of our project management), and my PL/PgSQL hex2bit() and bit2hex() functions are damn slow, so I'll be converting them to C any day not-so-soon (tried PL/Perl too, but even its simpler implementation was 5x slower yet !?) Interesting. Care to share your plperl code? I would expect Perl to be not so far behind C for this (at least if your using hex() & pack()/unpack() and the "b" template; since the hard work will be done in C routine anyway). -- dave ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] error creating sql function
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > I was trying to create a sql function today (see below) using > postgresql 7.3.3. I don't see how to get around this error, anyone > have any suggestions? > tocr=# CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar) > ... > tocr'# copy do_not_call_list (area_code, number) from $2 with delimiter > ... > tocr-# LANGUAGE 'sql' VOLATILE; > ERROR: parser: parse error at or near "$2" at character 178 COPY, like all the other utility commands in Postgres, doesn't support $n parameters. (Basically, you can only use these where an expression would be allowed, which is only in SELECT/INSERT/UPDATE/DELETE.) You can work around this by constructing the desired command as a string in plpgsql or one of the other PL languages, say CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar) ... execute ''copy do_not_call_list (area_code, number) from '' || quote_literal($2) || '' with delimiter '' ... LANGUAGE 'plpgsql' VOLATILE; regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] New PostgreSQL search resource
http://www.commandprompt.com/community/ I'd appreciate if you mention somewhere OpenFTS utilization, so people could recognize it. But I'm not insisting ;) Full intent to do so... want to get it finished up first. :) Sincerely, Joshua Drake Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Re: [GENERAL] New PostgreSQL search resource
On Fri, 16 Jan 2004, Joshua D. Drake wrote: > Hello, > > Took an hour today and made the 7.3.4, 7.4.1 and Practical PostgreSQL > documentation > all searchable using OpenFTS and Tsearch2. You can take a look at: > > http://www.commandprompt.com/community/ I'd appreciate if you mention somewhere OpenFTS utilization, so people could recognize it. But I'm not insisting ;) > > Sincerely, > > Joshua Drake > > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] error creating sql function
Matthew T. O'Connor wrote: I was trying to create a sql function today (see below) using postgresql 7.3.3. I don't see how to get around this error, anyone have any suggestions? Thanks much, Matthew tocr=# CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar) tocr-# RETURNS void AS tocr-# ' tocr'# begin; tocr'# update area_codes tocr'# set last_updated = now() tocr'# where code = $1; tocr'# DELETE from do_not_call_list tocr'# where area_code = $1; tocr'# copy do_not_call_list (area_code, number) from $2 with delimiter as \',\'; tocr'# commit; tocr'# ' tocr-# LANGUAGE 'sql' VOLATILE; ERROR: parser: parse error at or near "$2" at character 178 tocr=# COMMENT ON FUNCTION public.update_dncl(bpchar, bpchar) IS 'Will be used to update an area code in the DNCL tables.'; COMMENT Quick reply ... I haven't tested this, and it's only a theory, so treat it as such. The copy command should have '' around the filename, so possibly: copy do_not_call_list (area_code, number) from ''$2'' with delimiter as \',\'; ... would work? tocr=# SELECT version(); version - PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] error creating sql function
I was trying to create a sql function today (see below) using postgresql 7.3.3. I don't see how to get around this error, anyone have any suggestions? Thanks much, Matthew tocr=# CREATE OR REPLACE FUNCTION public.update_dncl(bpchar, bpchar) tocr-# RETURNS void AS tocr-# ' tocr'# begin; tocr'# update area_codes tocr'# set last_updated = now() tocr'# where code = $1; tocr'# DELETE from do_not_call_list tocr'# where area_code = $1; tocr'# copy do_not_call_list (area_code, number) from $2 with delimiter as \',\'; tocr'# commit; tocr'# ' tocr-# LANGUAGE 'sql' VOLATILE; ERROR: parser: parse error at or near "$2" at character 178 tocr=# COMMENT ON FUNCTION public.update_dncl(bpchar, bpchar) IS 'Will be used to update an area code in the DNCL tables.'; COMMENT tocr=# SELECT version(); version - PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) (1 row) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: embedded/"serverless" (Re: [GENERAL] serverless postgresql)
I still have to respectfully disagree. Postgresql is IMO just the wrong software for the job, and given that there are still a number of really important things that postgresql lacks, it should concentrate on those.I am not against it however for technical reasons, because those things can always be overcome. I just wouldn't want postgresql to start branching out in different directions at this point, it makes no sense if the project wants to keep focused and one day become comparable side by side to oracle. IMO that should be it's main goal, and embedded functionality would be a detour that has more chances of doing harm then good. Chris > > > > Frankly, I am _mighty glad_ that the developers are focussed on > > enterprise-level applications. > > Me too. But I think we can get it for small-scale stuff with no impact on > the rest. Just an additional function or 6 to make embedded developer's > lives easier. > > Jon > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] New PostgreSQL search resource
Hello, Took an hour today and made the 7.3.4, 7.4.1 and Practical PostgreSQL documentation all searchable using OpenFTS and Tsearch2. You can take a look at: http://www.commandprompt.com/community/ Sincerely, Joshua Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] YAGT (yet another GUID thread)
On 16. jan 2004, at 12:18, David Garamond wrote: David Helgason wrote: I'm switching right away. The notation doesn't really do anything for me, but that's fine. I've been using bit(128), but always suspected that of being unoptimal (for no particular reason). I think bit(128) is quite efficient (OCTET_LENGTH() function shows me it's using 16 bytes). Since I'm storing several big piles of data for each GUID, it's not ally an issue whether the storage is 16, 20, 22, 24, or 26 bytes, but thanks for the extensive guide. I've not gone over to using a GUID as PK+FK for the tables, and even if that should become interesting (for uniqueness across several databases for example), I would prefer a dual PK of (host-id, serial), where host-ids would be preassigned bit(8) values or some such. I was mostly wondering about index efficiency and such. A bit of testing confirms that this seems to be just fine. Not that I'm surprised. 4. Ease of incremental searching. Suppose we're creating a GUI app to let user type in an item by its ID. VARCHAR(22) is a winner here since it allows users to type in normal characters in the keyboard and still lets Pg uses index for searching using "WHERE col LIKE '...%'". However, most "sane" database design would use another unique code for most entities that need to be typed in. 128bit (22 characters as base64) are just too long anyway. In my case, only applications ever specify the GUIDs, so this is a non-issue. 5. The ease of migrating to future "real GUID" datatype. I think using INET/CIDR will be easiest, as I can just use some simple combination of builtin Pg string function. But this is a very minor issue since if we're using a "real GUID" in the future, we most probably can't use our old GUID anymore, due to different creation algorithm. I'm already using 'real' GUIDs, which in my case means that the database never generates them (since I don't have a generate_real_guid() function in the database (and don't need to). Neither GUID project on gborg (mentioned in another thread) seem to be Mac OSX compatible, which is my current platform (but I want to stay more-or-less free of platform dependance just yet). Howver, using INET/CIDR prevents me to use LIKE or ~. So I guess it's back to BYTEA for me. Seems useless to me, since the GUIDS are practically random so LIKEness has no relevance. So in short, for GUID I now tend to use BYTEA or INET/CIDR. Storing as base192/base64 feels a little wasteful for me, since I can use ENCODE(...) to display binary data as base64 anyway. I find BIT(n) awkward to work with/not properly supported in most languages. I think I'll be staying with BIT(128) here, since none of the other really make anything easier. The INET might have been a choice, but it seems to have to much 'magic' in its output routines for me to feel comfortable around it. However the client app uses a hex representation internally (don't tell me it's silly, it's already coded, due to intricacies of our project management), and my PL/PgSQL hex2bit() and bit2hex() functions are damn slow, so I'll be converting them to C any day not-so-soon (tried PL/Perl too, but even its simpler implementation was 5x slower yet !?) d. David Helgason Over the Edge Entertainments ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: embedded/"serverless" (Re: [GENERAL] serverless postgresql)
Nigel J. Andrews wrote: On Fri, 16 Jan 2004, Jeff Bowden wrote: So maybe this is a packaging issue. On Debian when I install postgres it is necessary to do root shit in order to enable non-priveledged users to create and destroy databases. My understanding has alwasy been that these operations are restricted because it can allow users to accidentally or intentionally interefere with each other's use of the server. If there is some way for this to be set up sanely by default to allow users to successfully use createdb and dropdb on only their own data, then I will file a wishlist bug against the Debian packages to add this. Or is this all a pipe dream? I don't see what the problem is. PostgreSQL is perfectly able to be installed and run as any old user. Even if one is distributing via a package system, such as RPM perhaps, which has problems installing as non-root user if one sees the package already installed then there's no need to try to install it again. Even with the package installations there's bugger all stopping any old user doing an initdb -D myownplace. The standard postgres user on a system is only special in that it is generally the user the postmaster is run as. If you have other non-priviledged users and there's no need for them to share a cluster why try to make them? I'm sorry, maybe I didn't state my ideas clearly enough. I was following on to Tom Lane's suggestion that the Debian and RPM packages make configuration "automatic". I was trying to explain that even after installation, some administrator configuration is required to make the server usable for non-privelidged users. Either in the form of creating and dropping databases or in giving the users rights to do it themselves. The way I'd like to make it work for my app is to run a seperate postmaster as the user to avoid involving the machine administrator or interfering with other users. If a shared install can be made to work in an equivalent way, then I would rather go with that. I'm not clear on whether that's possible though. My current understanding is that it is not and that my original notion is the only way for my app. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Tool to ease development of plpgsql
What's the best way to ease development of plpgsql. My largest issues with plpgsql pertain to the quoting of course. Bill McMilleon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: embedded/"serverless" (Re: [GENERAL] serverless postgresql)
On Fri, 16 Jan 2004, Jeff Bowden wrote: > Tom Lane wrote: > > >Jeff Bowden <[EMAIL PROTECTED]> writes: > > > > > >>Still, the main problem I, and I suspect others, would like to solve is > >>installation/configuration. For my app I don't want the user to have to > >>understand anything about how keeping data in a shared > >>system-administered database is different from keeping data in local > >>files. Everything should "just work". > >> > >> > > > >Sure, but most of the existing packagings of PG already try to make this > >automatic (at least Lamar's RPMs and Oliver's Debian package do). No > >doubt further work could be invested to make it even smoother, but that > >doesn't mean we need a client-started database. > > > > So maybe this is a packaging issue. On Debian when I install postgres > it is necessary to do root shit in order to enable non-priveledged users > to create and destroy databases. My understanding has alwasy been that > these operations are restricted because it can allow users to > accidentally or intentionally interefere with each other's use of the > server. If there is some way for this to be set up sanely by default to > allow users to successfully use createdb and dropdb on only their own > data, then I will file a wishlist bug against the Debian packages to add > this. Or is this all a pipe dream? I don't see what the problem is. PostgreSQL is perfectly able to be installed and run as any old user. Even if one is distributing via a package system, such as RPM perhaps, which has problems installing as non-root user if one sees the package already installed then there's no need to try to install it again. Even with the package installations there's bugger all stopping any old user doing an initdb -D myownplace. The standard postgres user on a system is only special in that it is generally the user the postmaster is run as. If you have other non-priviledged users and there's no need for them to share a cluster why try to make them? -- Nigel Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] Potential Problem with PostgeSQL performance on SuSE
Along similar lines - have generally obtained better server performance (and stability) from most Linux distros after replacing their supplied kernel with one from kernel.org . regards Mark Josh Berkus wrote: Folks, While debugging a wireless card, I came across this interesting bit: http://portal.suse.com/sdb/en/2003/10/pohletz_desktop_90.html What it indicates is that by default SuSE 9.0 plays with the timeslice values for the Linux kernel in order to provide a "smoother" user experience. In my experience, this can be very bad news for databases under heavy multi-user load. I would suggest that anyone installing a SuSE 9.0 PostgreSQL server remove the Desktop pararmeter in the bootloader configuration. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Potential Problem with PostgeSQL performance on SuSE Linux 9.0
Folks, While debugging a wireless card, I came across this interesting bit: http://portal.suse.com/sdb/en/2003/10/pohletz_desktop_90.html What it indicates is that by default SuSE 9.0 plays with the timeslice values for the Linux kernel in order to provide a "smoother" user experience. In my experience, this can be very bad news for databases under heavy multi-user load. I would suggest that anyone installing a SuSE 9.0 PostgreSQL server remove the Desktop pararmeter in the bootloader configuration. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Max registers in postgresql 7.4
OK boys, i've made a mistake by using the term "register". What i meant is "record". Could you please tell me if there's a limit in the amount of records that the database can handle? Thanks... Ruby Martin: why do you say i meant tuple? From: Martin Marques <[EMAIL PROTECTED]> To: Doug McNaught <[EMAIL PROTECTED]> CC: Ruby Deepdelver <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [GENERAL] Max registers in postgresql 7.4 Date: Wed, 14 Jan 2004 17:54:44 -0300 Mensaje citado por Doug McNaught <[EMAIL PROTECTED]>: > "Ruby Deepdelver" <[EMAIL PROTECTED]> writes: > > > Hello, I'm having trouble in find certain information, i've search > > over the web and through the documentation but i haven't had lucky. > > I need to know if there is some limit in the amount of registers that > > the database can manage, and if so, how much is that maximun. > > Ummm... What is a 'register'? That's not a standard database term > AFAIK and it does not have any meaning with respect to PostgreSQL. > That's probably why you couldn't find anything. :) Doug, you forgot to say what the term really is: TUPLE _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] serverless postgres, embedded firebird, etc.
Tom, Actually, I've never had a corrupted firebird database. There were two bugs in an older version of InterBase that produced income for me. The first was a failure to realize that a file had just gone over 4Gb and therefore the word holding the offset had rolled over and the system had begun writing at the beginning of the file. Nasty. The second was a case that allowed a server to open a single database twice, thinking it was two different databases. That lead to doubly allocated pages which lead to wrong page types, etc. Both problems were fixed before the first version of firebird shipped. On the larger question of embedded database code, yes, there is certainly the possibility of user data overwriting database data structures, accidentally or intentionally. The intentional part is a question for the builders and users of the application. Only trusted programs should ever run with an embedded engine. The accidental part rarely, if ever corrupts a database for two reasons. First, there's quite a lot of checking internally. A trashed data structure is recognized quickly and shuts down the connection without writing anything. Reestablishing the connection creates an entirely new set of internal structures, disposing of the corruption. Second, the database is always (in almost all circumstances) consistent, even if uncommitted data must be written. Our goal, from the beginning, was to allow the database to restart instantly from a crash caused by tripping over the power cord of the server. It works, except in one actual and one theoretic case. We offer an asynchronous write mode because flushing files on Unix systems is too expensive. (The system was originally written for VMS, which didn't have a page cache.) On Unix systems, the careful write is almost always successful enough even asynchronously because pages aren't kept in the system cache for long. That's the theoretic case. The actual case is on windows which, by default, keeps pages in cache until process shutdown. Pulling the plug on a windows system that's using asynchronous writes is the actual problem. Fortunately, people have learned not to run power cords from their servers across corridors and such, so the "tripped over the power cord" problem is much reduced from years past. However, in areas with frequent power failures, there are occasional broken databases caused by pages that were in the cache and not written. Generally, that happens in less developed countries where the cost of a UPS is significant. Needless to say, the cost of having me repair the database is also significant. Often, after the user pleads and grovels on the subject of not having a UPS or a database backup, I fix those for free. Harrison's campaign for world harmony. Don't tell my boss. Firebird and InterBase have been used in "embedded" mode since 1985 and don't seem to corrupt data except in the cases described above. The next version of Firebird also forces Windows to flush its cache regularly, which should be a boon for our Latin American users. Best regards, Ann ---(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] Returning large select results from stored procedures
Richard Huxton wrote: On Thursday 15 January 2004 15:02, Bill Moran wrote: I'm having a little trouble understanding how to do something. I assume I'm just missing it in the documentation, so a pointer to relevent docs would be as welcome as a direct answer. I have a project in which I'll need to create a number of stored procedures that are basically wrappers around complex SQL statements. Along the lines of: ... I keep getting these errors: ERROR: return type mismatch in function declared to return integer DETAIL: Final SELECT must return exactly one column. CONTEXT: SQL function "expired" during startup Try SETOF subscription for this example - you're not telling it the type of the result (which in your example is a row from subscription). Thanks to everyone who responded. For the sake of the archives: I solved the problem by creating a type (which I called 'expired_type') that contains all the fields that are returned by the join in the funtion. The function definition was then changed to: CREATE OR REPLACE FUNCTION expired(timestamp) RETURNS SETOF expired_type AS ' SELECT * FROM subscription INNER JOIN user ON subscription.userID = user.id WHERE subscription.expiredate>=$1; ' LANGUAGE SQL; The upshot being: 1) I can't use type 'subscription', becuase the join changes the type 2) I must define a type to return Overall, it seems as though postgre's stored procedures are very strongly typed (as opposed to MS-SQL, in which this application was prototyped by another, which allows you to return a type that is determined at run time, and actually supports the concept of "combined recordsets" where not all rows are even of the same type. Pretty crazy) So, if anyone every gets terribly bored (yeah, right!) and wants something to hack on: a new psuedo-type (perhaps called recordset) that would mimic the combined recordset functionality of MS-SQL would be a mighty cool feature ;) -- Bill Moran Potential Technologies http://www.potentialtech.com ---(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] check date validity
--- Harald Fuchs <[EMAIL PROTECTED]> wrote:
> In article
> <[EMAIL PROTECTED]>,
> "LitelWang" <[EMAIL PROTECTED]> writes:
>
> > I need this function :
> > CheckDate('2002-02-29') return false
> > CheckDate('2002-02-28') return true
>
> Why would you want to do that? Just try to insert
> '2002-02-29' into
> your DATE column, and PostgreSQL will complain.
That will cause the whole transaction to abort, which
is probably not what is wanted.
I don't know any way around this in Postgres. Best to
check this in application code.
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
---(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] Creating GUID
Seems that none of them is same as MS GUID. > Linux-only? Seems so, damn currently use windows for developement. "David Garamond" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Chris Gamache wrote: > > You want > > http://gborg.postgresql.org/project/uniqueidentifier/projdisplay.php > > Another alternative: > > http://gborg.postgresql.org/project/pguuid/projdisplay.php > > (How do the two compare, aside from uniqueidentifier seeming to be > Linux-only? Should I use one of the above for production purposes?) > > -- > dave > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Creating GUID
Chris Gamache wrote: You want http://gborg.postgresql.org/project/uniqueidentifier/projdisplay.php Another alternative: http://gborg.postgresql.org/project/pguuid/projdisplay.php (How do the two compare, aside from uniqueidentifier seeming to be Linux-only? Should I use one of the above for production purposes?) -- dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] how to catch the error in procedure ?
On Fri, 2004-01-16 at 04:41, Richard Huxton wrote:
> On Friday 16 January 2004 04:50, LitelWang wrote:
> > I need catch the error when my insert clause violate the
> > primary key .How to write in the server function ?
> > Thanks for any advice .
>
> You can't (at the moment). An error terminates a transaction immediately in
> PG's current model.
>
Didn't someone say this could be implemented in some of the other
pl's... plpython maybe? Or maybe they were using dblink to make a
separate connection/query... ?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Creating GUID
You want
http://gborg.postgresql.org/project/uniqueidentifier/projdisplay.php
CG
--- Ivar <[EMAIL PROTECTED]> wrote:
> Hi,
>
> How to generate GUID("f741d0ce-351c-4c8d-9625-d23765ca7f45") values in
> postgre ?
>
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Creating GUID
Hi,
How to generate GUID("f741d0ce-351c-4c8d-9625-d23765ca7f45") values in
postgre ?
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to become a Windows beta-tester?
On Friday 16 January 2004 10:35, Paul Ganainm wrote: > Hi all, > > > I would be interested in playing around with the beta version for the > Windows native port - where can I get it? I don't think there is a beta yet. There is a status page here http://momjian.postgresql.org/main/writings/pgsql/win32.html That describes a mailing list - check the archives on that to see what's happened so far. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie to Postgres - Urgent query
Hi, Oh ok..my conf didnt show the log_statement option. Thanks, should be helpful some other time. Regards, Ritu On Fri, 2004-01-16 at 17:39, Shridhar Daithankar wrote: On Friday 16 January 2004 16:43, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. In my local installation I have following relevant options. #log_connections = false #log_duration = false #log_pid = false #log_statement = false #log_timestamp = false #log_hostname = false #log_source_port = false Anyways, the problem is solved, as you pointed in other mail. Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org
Re: [GENERAL] Newbie to Postgres - Urgent query
On Friday 16 January 2004 16:43, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. In my local installation I have following relevant options. #log_connections = false #log_duration = false #log_pid = false #log_statement = false #log_timestamp = false #log_hostname = false #log_source_port = false Anyways, the problem is solved, as you pointed in other mail. Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie to Postgres - Urgent query
Hi all, I discovered the problem, AutoCommit is off by default and I did not commit the transaction..it works fine now. Thanks, Ritu On Fri, 2004-01-16 at 16:56, Csaba Nagy wrote: Have you read the documentation about configuring the postgres server: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=runtime.html Actually there is a very fine suite of documentation on that site. HTH, Csaba. On Fri, 2004-01-16 at 12:13, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. > > Regards, > Ritu > On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote: > > On Friday 16 January 2004 16:03, Ritu Khetan wrote: > > > Hello Richard, > > > > > >How do I switch on statement logging...? > > > > > >postmaster --help doesn't talk of any such switch. > > > > Check options in postgresql.conf. > > > > Shridhar > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 > > MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in > > Pragatee: Integrated Server-Software Suite: http://www.pragatee.com > > Emergic Freedom: Server-centric Computing: http://www.emergic.com > > BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com > > Deeshaa: Rural Development: http://www.deeshaa.com > > Rajesh Jain's Weblog on Technology: http://www.emergic.org > NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org
Re: [GENERAL] Newbie to Postgres - Urgent query
Have you read the documentation about configuring the postgres server: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=runtime.html Actually there is a very fine suite of documentation on that site. HTH, Csaba. On Fri, 2004-01-16 at 12:13, Ritu Khetan wrote: > Hi, > > No such option found. There is the "S" switch for silent mode which is > off by default anyways. > > Regards, > Ritu > On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote: > > On Friday 16 January 2004 16:03, Ritu Khetan wrote: > > > Hello Richard, > > > > > >How do I switch on statement logging...? > > > > > >postmaster --help doesn't talk of any such switch. > > > > Check options in postgresql.conf. > > > > Shridhar > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 > > MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in > > Pragatee: Integrated Server-Software Suite: http://www.pragatee.com > > Emergic Freedom: Server-centric Computing: http://www.emergic.com > > BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com > > Deeshaa: Rural Development: http://www.deeshaa.com > > Rajesh Jain's Weblog on Technology: http://www.emergic.org > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] YAGT (yet another GUID thread)
David Helgason wrote: I'm switching right away. The notation doesn't really do anything for me, but that's fine. I've been using bit(128), but always suspected that of being unoptimal (for no particular reason). I think bit(128) is quite efficient (OCTET_LENGTH() function shows me it's using 16 bytes). Btw, here are the data types and format I've tried/considered to store GUID in: - BYTEA (storing the raw bytes; storage = 4+16 = 20 bytes; attlen = -1) - CHAR/VARCHAR(18) (i'm using "base192" with character set containing ASCII 64-255. storage = 4+18 = 22 bytes?; attlen = -1) - CHAR/VARCHAR(22) (using base64, storage = 4+22 = 26 bytes?; attlen = -1) - INET/CIDR (storage = 24 bytes?; attlen = -1) - BIT(128) (storage = 16 bytes?; attlen = -1) PostgreSQL hasn't included a datatype with attlen of exactly 16 bytes, so all of the above are "variable-length field". My considerations in choosing the appropriate type for storing GUID are as follow (sorted from most important to least important): 1. The ease/naturalness of inserting. INET/CIDR is the slight winner here. For VARCHAR(18)/VARCHAR(22) I have to create a guidhex_to_base192()/guidhex_to_base64() function, which is not a big deal. Of course, I can always create/represent GUID as base192/base64 from the start, in which case using VARCHAR(18)/VARCHAR(22) is very easy too. For BYTEA you have to use "\\000" escape codes in psql. I'm still having difficulty on how to insert BIT fields using DBD::Pg and bind_param(). 2. "Ease to the eye", that is, they way PostgreSQL displays the data. For me, INET/CIDR wins here, though VARCHAR(22) looks equally nice too. VARCHAR(18) and BYTEA makes the display looks weird due to high ASCII characters and/or control characters. BIT(128) is just too long (and silly me, I can't seem to find an easy way to display BIT(128) columns as hex or normal strings). Of course, we can use ENCODE(col, 'base64') to display BYTEA GUID column, but it's kind of annoying to having to write that all the time. 3. The compactness/efficiency of storage. Well, none of the above are the most efficient anyway. We'll have to wait until PostgreSQL officially supports INT16/INT128/BIGBIGINT/GUID/fixed BYTEA. So either one is ok to me. 16 vs 22-24 bytes are not that big a deal either. Also, disk space is getting cheaper every day. 4. Ease of incremental searching. Suppose we're creating a GUI app to let user type in an item by its ID. VARCHAR(22) is a winner here since it allows users to type in normal characters in the keyboard and still lets Pg uses index for searching using "WHERE col LIKE '...%'". However, most "sane" database design would use another unique code for most entities that need to be typed in. 128bit (22 characters as base64) are just too long anyway. 5. The ease of migrating to future "real GUID" datatype. I think using INET/CIDR will be easiest, as I can just use some simple combination of builtin Pg string function. But this is a very minor issue since if we're using a "real GUID" in the future, we most probably can't use our old GUID anymore, due to different creation algorithm. So in short, for GUID I now tend to use BYTEA or INET/CIDR. Storing as base192/base64 feels a little wasteful for me, since I can use ENCODE(...) to display binary data as base64 anyway. I find BIT(n) awkward to work with/not properly supported in most languages. Howver, using INET/CIDR prevents me to use LIKE or ~. So I guess it's back to BYTEA for me. -- dave ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Newbie to Postgres - Urgent query
Hi, No such option found. There is the "S" switch for silent mode which is off by default anyways. Regards, Ritu On Fri, 2004-01-16 at 16:34, Shridhar Daithankar wrote: On Friday 16 January 2004 16:03, Ritu Khetan wrote: > Hello Richard, > >How do I switch on statement logging...? > >postmaster --help doesn't talk of any such switch. Check options in postgresql.conf. Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org
Re: [GENERAL] Newbie to Postgres - Urgent query
On Friday 16 January 2004 16:03, Ritu Khetan wrote: > Hello Richard, > >How do I switch on statement logging...? > >postmaster --help doesn't talk of any such switch. Check options in postgresql.conf. Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbie to Postgres - Urgent query
Hello Richard, How do I switch on statement logging...? postmaster --help doesn't talk of any such switch. Regards, Ritu On Fri, 2004-01-16 at 15:25, Richard Huxton wrote: On Friday 16 January 2004 09:18, Ritu Khetan wrote: > Hello all, > > I am trying to use DBI in perl to connect to my Postgres database. I > am able to connect to the database but when any further queries are made > using the database handle, I see the following message in my error logs > and no results are generated - > > "NOTICE: current transaction is aborted, queries ignored until end of > transaction block" 1. Check the return-values of your calls (e.g. connect, exectute...) 2. Turn on statement logging in PG to see what is happening. If the return values and the logs don't seem to help, post both back here and we'll see what we can do. NETCORE SOLUTIONS *** Ph: +91 22 5662 8000 Fax: +91 22 5662 8134 MailServ and FlexiMail: Messaging Solutions: http://netcore.co.in Pragatee: Integrated Server-Software Suite: http://www.pragatee.com Emergic Freedom: Server-centric Computing: http://www.emergic.com BlogStreet: Blog Profiles and RSS Ecosystem: http://blogstreet.com Deeshaa: Rural Development: http://www.deeshaa.com Rajesh Jain's Weblog on Technology: http://www.emergic.org
[GENERAL] How to become a Windows beta-tester?
Hi all, I would be interested in playing around with the beta version for the Windows native port - where can I get it? TIA. Paul... -- plinehan y_a_h_o_o and d_o_t com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. "XML avoids the fundamental question of what we should do, by focusing entirely on how we should do it." quote from http://www.metatorial.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] tinterval operators and functions
On Jan 16, 2004, at 6:46 PM, Richard Huxton wrote: On Friday 16 January 2004 05:14, Michael Glaesemann wrote: That said, I haven't been successful finding which functions accept tinterval as arguments, though this is probably because I don't know how to handle where clauses involving arrays, or perhaps its because the pg_proc.proargtypes attribute is type oid vector and haven't figured out how to handle that. Any insight appreciated. (Query details below.) If you know yourself and your enemy... richardh=# \o tinterval.txt richardh=# \df richardh=# \do richardh=# \q [EMAIL PROTECTED] tmp]$ grep tinterval tinterval.txt Works for friends, too, eh? :) Thanks, Richard! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Newbie to Postgres - Urgent query
On Friday 16 January 2004 09:18, Ritu Khetan wrote: > Hello all, > > I am trying to use DBI in perl to connect to my Postgres database. I > am able to connect to the database but when any further queries are made > using the database handle, I see the following message in my error logs > and no results are generated - > > "NOTICE: current transaction is aborted, queries ignored until end of > transaction block" 1. Check the return-values of your calls (e.g. connect, exectute...) 2. Turn on statement logging in PG to see what is happening. If the return values and the logs don't seem to help, post both back here and we'll see what we can do. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] tinterval operators and functions
On Friday 16 January 2004 05:14, Michael Glaesemann wrote: > > That said, I haven't been successful finding which functions accept > tinterval as arguments, though this is probably because I don't know > how to handle where clauses involving arrays, or perhaps its because > the pg_proc.proargtypes attribute is type oid vector and haven't > figured out how to handle that. Any insight appreciated. (Query details > below.) If you know yourself and your enemy... richardh=# \o tinterval.txt richardh=# \df richardh=# \do richardh=# \q [EMAIL PROTECTED] tmp]$ grep tinterval tinterval.txt -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
