Re: [GENERAL] Case sensitivity
Very good book. I have the .pdf file of it from before they took it off the site. John Clark Naldoza y Lopez wrote: Hello Scott, PostgreSQL also supports regular expressions, _VERY_ useful..;-) ~* regular expression, case-insensitive..;-) Try to get a copy of Bruce's book Introduction and Concepts by Bruce Momjian A sample from the book: SELECT * FROM friend WHERE firstname ~* '[bc]' read it online, or just read the docs online...;-) http://techdocs.postgresql.org Cheers, John Clark p.s. don't change PostgreSQL, change your SQL statements...;-) Scott Gilbert wrote: We intend to move from M$ Access to PostgreSQL, however, much of our legacy code operates under the assumption that string or text comparisons are case insensitive whereas postgres is case sensitive. This issue extends to SQL Select statements as well as straight comparison (i.e. in addition to the , and = operators, the WHERE and IN clauses in SQL statements are also issues) so we can't simply modify the function attached to the comparison operators. Is there some means of modifying the behaviour of PostgreSQL so that it is case insensitive? -- /) John Clark Naldoza y Lopez (\ / )Software Design Engineer III ( \ _( (__ Web-Application Development_) )_ (((\ \ /_Cable Modem Network Management System _\ / /))) ( \_/ / NEC Telecom Software Phils., Inc. \ \_/ ) \ / \ / \_/ phone: (+63 32) 233-9142 loc. 3113\_/ / / cellphone: (+63 919) 399-4742 \ \ / / email: [EMAIL PROTECTED]\ \ Intelligence is the ability to avoid doing work, yet getting the work done --Linus Torvalds ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Perfomance decreasing
Hello. I have a question about perfomance. I'm running PostgreSQL 7.1.2 at FreeBSD 4.3. For the first 1-2 days of running perfomance is excellent. But after that, speed began to decrease. And after a week of operation, perfomance falls 8-10 times, than at first day of using. I'm doing vacuum periodically (once a hour), but perfomance still falls down. After that I dump database as text file, make dropdb createdb and after that, restore database from dump - Perfomance is excellent again (for 1-2 days). Why this situation occures? May be I must use VACUUM ANALYSE instead of VACUUM? Best regards, Alexander mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] PostgreSQL library functions
A question has intrigued me ever since I started to learn about Berkeley DB (well after I had started using postgres): does PostgreSQL seperate the data management routines such that I could use them in a way similar to Berkeley DB? I find the concept useful for people who don't want to start extra processes for each connection. Also any comments on the data management abilities of Berkeley DB or Postgres would be appreciated. I read that MySQL wasn't great for concurrent access; is this because of Berkeley DB? Thanks, Jeff Davis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Perfomance decreasing
On Tue, Aug 14, 2001 at 02:06:40PM +0600, Alexander Loginov wrote: Hello. I have a question about perfomance. I'm running PostgreSQL 7.1.2 at FreeBSD 4.3. For the first 1-2 days of running perfomance is excellent. But after that, speed began to decrease. And after a week of operation, perfomance falls 8-10 times, than at first day of using. I'm doing vacuum periodically (once a hour), but perfomance still falls down. After that I dump database as text file, make dropdb createdb and after that, restore database from dump - Perfomance is excellent again (for 1-2 days). Why this situation occures? May be I must use VACUUM ANALYSE instead of VACUUM? I have actually the same problem, also FreeBSD 4.3, pgsql 7.1.2. I do use VACUUM ANALYSE quite often. The problem in the end gets that bad that perl-jobs cannot perform any SELECTs, or at least they stop returning results before dbi times out. So far I have tracked the problem down to the size of the database in the filesystem, where problems start occurring when it exceeds 1,4 Gb. A dump/drop/create/restore reduces files size to approx. 350 Mb. Any pointers would be helpful as a weekly dump/restore is not quite optimal :) /erwin -- Erwin Lansing -- http://droso.org You've got mail ---(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] Is the bug system active?
Is the bug system at: http://www.ca.postgresql.org/bugs/ actively in use? I recently reported the following bug: http://www.ca.postgresql.org/bugs/bugs.php?4~415 and after doing so browsed through a number of earlier bugs it appears that the vast majority are still 'unassigned' and not evaluated after a long period of time (like over a year)! Surely if this system is being ignored it should be removed from the website... Thanks, Lee Kindness. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] help on delete trigger.
I am developing a db application in postgresql and i need to write a delete trigger on one of the tables. the environment is table1 field1 varchar(64) other fields. table 2. field1 varchar(64) other fields I need a delete trigger on the table 1, so that if I delete a row from table 1 , the corresponding rows from table 2 should also be deleted. This is the code I have tried. DROP FUNCTION ApplicationsDeleteFn();CREATE FUNCTION ApplicationsDeleteFn()RETURNS OPAQUEAS 'BEGINdelete from ports where appName=OLD.appName;RETURN OLD; END;'LANGUAGE 'plpgsql'; Please help me with this, as my work is time bound. Thanks sundar
[GENERAL] PostgresQL equivalent of NOCOUNT
Does PostgresQL have some way to make update, insert and delete queries not return the number of affected rows? I know that in MS SQL one would use NOCOUNT for that. TIA, Jochem ---(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] Is the bug system active?
On Tue, 14 Aug 2001, Tom Lane wrote: Lee Kindness [EMAIL PROTECTED] writes: Is the bug system at: http://www.ca.postgresql.org/bugs/ actively in use? I recently reported the following bug: http://www.ca.postgresql.org/bugs/bugs.php?4~415 and after doing so browsed through a number of earlier bugs it appears that the vast majority are still 'unassigned' and not evaluated after a long period of time (like over a year)! Surely if this system is being ignored it should be removed from the website... It's not being ignored, exactly --- the part of it that is actually useful is that bug reports entered via the webform are spit out to the pgsql-bugs mailing list. Which the developers do read, and respond to. (I see your ecpg bug report on the list, BTW.) But no one is maintaining the bug database in the sense of marking stuff done. I hadn't realized that there was a publicly-visible database attached to the webform, actually. Perhaps we should not make the database visible, if we're not going to update it. Vince, what do you think? If we're not going to update it then I agree, although updating it is rather trivial. It's a function/feature that's already built in. So I guess it's your call, 'cuze it's just as trivial for me to disable that part. 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 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] Re: help on delete trigger.
This code looks fine to me, other than missing the actual trigger statement is missing. Assuming table 1 is named apps: DROP TRIGGER OnApplicationsDelete ON apps; DROP FUNCTION ApplicationsDeleteFn(); CREATE FUNCTION ApplicationsDeleteFn() RETURNS OPAQUE AS ' BEGIN delete from ports where appName=OLD.appName; RETURN OLD; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER OnApplicationsDelete BEFORE DELETE ON apps FOR EACH ROW EXECUTE PROCEDURE ApplicationsDeleteFn(); But the question is, would it be better to use a foreign key? If you put a foreign key on ports.appName that REFERENCES apps.appName, you could define it as an ON DELETE CASCADE relationship. Meaning that deleting the value from the apps table would cascade that delete to the ports table. Hope this helps, Greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] cmd-line interface
i have been using postgresql in preparation for supporting it with our product. one thing that i do a lot is run scripts from the cmd-line, and i have noticed a few things that seem to be lacking from the psql app. first off though, i must say how much it rocks. i absolutely hate having to go back to using the db2 cmd-line app. psql just does everything cool, and i WISH i could only ever use it. of all the interfaces i've used so far ( quite a few ) psql is by far the most capable. congratulations. but... one thing i do like about the db2 app, is that on the cmd-line i can specify two things: 1) stop on error this seems to be doable through the env-var ON_ERROR_STOP, but i do wish it was also parm i could pass on the cmd-line to psql. its just so much easier than setting the everytime i want the stop behaviour. i guess i could just set it and leave it. 2) wrap a file in an explicit transaction with the db2 app, i can request the stop-on-fail as well as disabling autocommit, all from the cmd-line. this is also doable by manually adding the sql at the beginning and end of the file, but that too kind of sucks. to me, the ideal behaviour would be to simply return to the bash prompt without commiting or rolling back when the psql app ends, then i can chose to do a commit or rollback manually. however, this is probably difficult as psql doesn't remember that much stuff about its connection. a very cool way of doing this would be to ask the user when the psql app ended if they wished to commit. only if this transaction behaviour was requested would the question be posed, but this would allow me to rollback something that had an error, without the hassle of modiying the sql script. is 2) possible right now? i can't see anything in the docs, so i think not. anyway, having this sort of control would really help. our scripts need to run on various servers, and some things use different syntax for transactions, db2 is 'rollback/commit work'. one final question. i presume that if a 'begin transaction' is used and the ON_ERROR_STOP is set, then the transaction is rolled back? cheers, CraigL-Thx(); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Case sensitivity
Hello Scott, PostgreSQL also supports regular expressions, _VERY_ useful..;-) ~* regular expression, case-insensitive..;-) Try to get a copy of Bruce's book Introduction and Concepts by Bruce Momjian A sample from the book: SELECT * FROM friend WHERE firstname ~* '[bc]' read it online, or just read the docs online...;-) http://techdocs.postgresql.org Cheers, John Clark p.s. don't change PostgreSQL, change your SQL statements...;-) Scott Gilbert wrote: We intend to move from M$ Access to PostgreSQL, however, much of our legacy code operates under the assumption that string or text comparisons are case insensitive whereas postgres is case sensitive. This issue extends to SQL Select statements as well as straight comparison (i.e. in addition to the , and = operators, the WHERE and IN clauses in SQL statements are also issues) so we can't simply modify the function attached to the comparison operators. Is there some means of modifying the behaviour of PostgreSQL so that it is case insensitive? -- /) John Clark Naldoza y Lopez (\ / )Software Design Engineer III ( \ _( (__ Web-Application Development_) )_ (((\ \ /_Cable Modem Network Management System _\ / /))) ( \_/ / NEC Telecom Software Phils., Inc. \ \_/ ) \ / \ / \_/ phone: (+63 32) 233-9142 loc. 3113\_/ / / cellphone: (+63 919) 399-4742 \ \ / / email: [EMAIL PROTECTED]\ \ "Intelligence is the ability to avoid doing work, yet getting the work done" --Linus Torvalds ---(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] delete columns from table!
Thanks, it helps! Tim Barnard wrote: Another way SELECT INTO a new table, omitting the column you want to delete. Tim - Original Message - From: Patrick Welche [EMAIL PROTECTED] To: Fariba Noorbakhsh [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, August 13, 2001 7:36 AM Subject: Re: [GENERAL] delete columns from table! On Mon, Aug 06, 2001 at 05:42:09PM +0200, Fariba Noorbakhsh wrote: Hi Patrick, Do you know how I can drop(delete) a column from a table?! Just a column. I don't think you can do it with Alter table Thanks in advance, Fariba According to the TODO list: COMMANDS ... * ALTER ... o Add ALTER TABLE DROP COLUMN feature [drop] so the way forward may be to pg_dump -t your_table -D your_database table.txt and have fun editing out all reference to said column from table.txt. Then drop the table and reload from table.txt. YMMV.. Cheers, Patrick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Perfomance decreasing
For the first 1-2 days of running perfomance is excellent. But after that, speed began to decrease. And after a week of operation, perfomance falls 8-10 times, than at first day of using. I'm doing vacuum periodically (once a hour), but perfomance still falls down. It sounds to me like you may be running into index growth problems. VACUUM is presently not good about shrinking indexes. If you drop and recreate the indexes used by your most important queries, does the performance go back to where it was? Why this situation occures? May be I must use VACUUM ANALYSE instead of VACUUM? Occasional VACUUM ANALYZEs are a good idea. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Is the bug system active?
Stephan Szabo writes: On Tue, 14 Aug 2001, Lee Kindness wrote: Is the bug system at: http://www.ca.postgresql.org/bugs/ actively in use? It's mostly a front end for the pgsql-bugs mailing list. I don't think the discussion and actions are generally copied back to the online system. Right, in which case I'd agree with Tom Lane that if the web side of things doesn't reflect reality then it would be better disabled - a note referring people to the bug list archives would be more useful. On a related note is comp.databases.postgresql.bugs on news.postgresql.org being gated from the pgsql-bugs mailing list? It doesn't appear to be so. Other groups (i.e general and interfaces) appear fine but bugs has no messages in it. Thanks, Lee. -- Lee Kindness Senior Software Engineer Concept Systems Limited [EMAIL PROTECTED] +44-(0)131-5575595 http://services.csl.co.uk/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Is the bug system active?
Lee Kindness [EMAIL PROTECTED] writes: Is the bug system at: http://www.ca.postgresql.org/bugs/ actively in use? I recently reported the following bug: http://www.ca.postgresql.org/bugs/bugs.php?4~415 and after doing so browsed through a number of earlier bugs it appears that the vast majority are still 'unassigned' and not evaluated after a long period of time (like over a year)! Surely if this system is being ignored it should be removed from the website... It's not being ignored, exactly --- the part of it that is actually useful is that bug reports entered via the webform are spit out to the pgsql-bugs mailing list. Which the developers do read, and respond to. (I see your ecpg bug report on the list, BTW.) But no one is maintaining the bug database in the sense of marking stuff done. I hadn't realized that there was a publicly-visible database attached to the webform, actually. Perhaps we should not make the database visible, if we're not going to update it. Vince, what do you think? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] LARGE db dump/restore for upgrade question
Hi I have a very large database of seismic data. It is about 27 Gb now, and growing at about the rate of 1 Gb every 3-4 days. I am running postgres 7.1.2. I might possibly try to upgrade to 7.2 when it comes out, but I don't know if it will be possible for me to do 7.3 due to the pg_dump/pg_restore problem. In a little over a year the database will probably pass the halfway point on my raid and so it will physically be impossible to upgrade. Most of the space is probably taken up by large objects, which I am hoping will make a solution at least a little bit easier. I am trying a pg_dump right now, and in the first 25 minutes it dumped 54Mb, which means that a full dump will take about 200 hours! I would guess the restore would take about the same amount of time, so I would be looking at 17 DAYS of downtime to upgrade! Maybe it will speed up later in the dump, I don't know. And in about 3 months or so it will take me twice that amout of time. Also, this is on a 4 processor sun E450 with a A1000 hardware raid, so it not that I am using old slow hardware. Just for comparison, a file system dump to tape took 6 hours, and that was back when I only had a software raid! So, my question is, is it likely that one day postgres will no longer require dump/restores for upgrades? I would assume that there will always be a need to tweak the internal layout of files, but I wonder if there isn't a way to do this in place or at least to allow a file system move of the large objects without requiring them to be dumped as well? Even better would be if each new version of postgres could read the immediatly previous version tables, and could convert them in the background. Maybe just dreaming here. :) Could something related to making the upgrade less painful for very large databases be added to the ToDo list even if it isn't a high priority? Not that I am complaining, postgres seems to handle this data volume quite well, and it is certainly worth very dollar I didn't pay for it. :) Any suggestion on how to prepare for the next upgrade would be appreciated. thanks, Philip ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: [BUGS] triggers
On Tue, 14 Aug 2001, Martin Kuria wrote: Hi, I have a problem I am using postgresql database and I want to enforce triggers between my related tables ( tables with relationship), one table has a foreign key and the other the a primary key, I would like to use triggers to update, delete, and insert in this tables but the table with the foreign key should first check in the other table before any action is taken, Which is the simplest sql or C language script can I use to enforce triggers within my tables, please demonstrate a simple scenerio thanks pgsql-bugs is definately not the place for this message (response to -general). Since I can't tell what you're precisely trying to do from the explanation above, the easiest starting point is to look at the documentation for triggers and then at techdocs.postgresql.org for a couple trigger examples. However, I don't believe you can guarantee that the foreign key trigger is going to happen before your trigger. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [Fwd: MySQL Benchmark page - Problem with vacuum() in PostgreSQL]
Hi! Tom == Tom Lane [EMAIL PROTECTED] writes: Tom Justin Clift [EMAIL PROTECTED] forwards: The problem was that when we run the benchmark with the --fast option, which basicly does a vacuum() between after each batch of updates, postmaster started to fill up disk with log files during one of the vacuum() runs and didn't stop until the disk was full. Tom See Tom http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html Tomregards, tom lane Thanks! I will look at this as soon as I am back home from my vacation and then do a new benchmark run and update the benchmark page. Regards, Monty PS: If anyone has any ideas of things that we have missed in our current benchmark pages, please email them to Anna so that she can add these to the next generation of the benchmark suite. Some things that I know we have missed in the single user benchmark are: - Sub select (all different forms of sub select, with a comparison to normal selects for those select that can be changed to normal selects) - Foreign keys (which should contain a comparison with multi-table-delete) - Transactions - Rollback With comparison I mean that there should be at least one test that makes it easy for the user to see which construct is better for this database. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] [ADMIN] initdb on postgresql 7.1.2 running under cygwin on win 2000
Hello ! I've got a little problem with launching initdb on postgresql 7.1.2 under cygwin : when i launch it, it says : $ initdb -d -n -D $PG_DATA Running with debug mode on. Running with noclean mode on. Mistakes will not be cleaned up. Initdb variables: PGDATA=/data/pgsql datadir=/usr/share/postgresql PGPATH=/usr/bin TEMPFILE=/tmp/initdb.412 MULTIBYTE=SQL_ASCII MULTIBYTEID=0 POSTGRES_SUPERUSERNAME=ssautetn POSTGRES_SUPERUSERID=1003 TEMPLATE1_BKI=/usr/share/postgresql/template1.bki GLOBAL_BKI=/usr/share/postgresql/global.bki TEMPLATE1_DESCR=/usr/share/postgresql/template1.description GLOBAL_DESCR=/usr/share/postgresql/global.description POSTGRESQL_CONF_SAMPLE=/usr/share/postgresql/postgresql.conf.sample PG_HBA_SAMPLE=/usr/share/postgresql/pg_hba.conf.sample PG_IDENT_SAMPLE=/usr/share/postgresql/pg_ident.conf.sample This database system will be initialized with username ssautetn. This user will own all the data files and must also own the server process. Creating directory /data/pgsql Creating directory /data/pgsql/base Creating directory /data/pgsql/global Creating directory /data/pgsql/pg_xlog Creating template1 database in /data/pgsql/base/1 Running: /usr/bin/postgres -boot -x1 -C -F -D/data/pgsql -d template1 and then the postgres process freezes and take 100 % of cpu, never creating the template1 files, so i must kill it. As anyone an idea on what's happening ? thank's. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Is the bug system active?
On Tue, 14 Aug 2001, Lee Kindness wrote: Is the bug system at: http://www.ca.postgresql.org/bugs/ actively in use? I recently reported the following bug: http://www.ca.postgresql.org/bugs/bugs.php?4~415 and after doing so browsed through a number of earlier bugs it appears that the vast majority are still 'unassigned' and not evaluated after a long period of time (like over a year)! Surely if this system is being ignored it should be removed from the website... It's mostly a front end for the pgsql-bugs mailing list. I don't think the discussion and actions are generally copied back to the online system. ---(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] do I have a reserved word here or something???
INSERT into env_info
(username,useremail,servicelevel,accountmanager,company) values
('$env_array{User}','$env_array{UserEmail}','$env_array{ServiceLevel}'
,'$env_array{AccountManager}','$env_array{Company}'
I sometime get a parse error near username, but for the life of me, can't
figure out what my issues is...mainly because it sometimes works.
Username example that failed is:
eds_bv3
Any clues as to why this insert would fail?
Thanks for all the help!
-jeremy
--
salad.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Is the bug system active?
On Tue, 14 Aug 2001, Lee Kindness wrote: Stephan Szabo writes: On Tue, 14 Aug 2001, Lee Kindness wrote: Is the bug system at: http://www.ca.postgresql.org/bugs/ actively in use? It's mostly a front end for the pgsql-bugs mailing list. I don't think the discussion and actions are generally copied back to the online system. Right, in which case I'd agree with Tom Lane that if the web side of things doesn't reflect reality then it would be better disabled - a note referring people to the bug list archives would be more useful. Actually we could probably keep up the form, but not the rest of the system and just say its a form that posts to the mailing list. (To be honest, I didn't even notice that it kept a list of the bugs and such until today). ---(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] nextval, sequences and sequencenames
Hi,
I'm pretty new to PostgreSQL so please bear with me if this is a newbie
question that has been answered before on this list.
I have created several tables containing a SERIAL column-type as primary
key.
What I would like to do is get the last value of these columns after Ive
inserted a new row.
I know I can do this either using nextval('sequence_name') or
curval('sequence_name').
My question:
is there anyway of retreiving the sequence_name corresponding to the
respective column,
knowing just the tablename and columnname?
The reason I need to do this, is because the application I write
dynamicly creates new tables, and I have no way of knowing the sequence
name before hand.
thanks in advance
Wieger
--
Wieger Uffink
tel: +31 20 428 6868
fax: +31 20 470 6905
web: http://www.usmedia.nl
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgresQL equivalent of NOCOUNT
Tom Lane wrote:
Jochem van Dieten [EMAIL PROTECTED] writes:
Does PostgresQL have some way to make update, insert and delete queries
not return the number of affected rows? I know that in MS SQL one would
use NOCOUNT for that.
Uh ... why? Seems like a useless anti-feature. Certainly suppressing
the count wouldn't save a noticeable number of cycles.
I am not in it for the cycles, just for the laziness ;)
Currently working with a ColdFusion frontend through ODBC, and
ColdFusion is unable to return 2 resultsets for one call to cfquery (the
ColdFusion query implementation). In MS SQL I would use the query below
to suppress one resultset and return the primary key of the recently
inserted record.
SET NOCOUNT ON
INSERT INTO ()
VALUES ()
SELECT @@IDENTITY AS 'Identity'
SET NOCOUNT OFF
I was wondering if something like that is possible in PostgresQL. I know
I can wrap it in a transaction and do a second query or build a
procedure to do it, but this would be much easier (presuming I can use
curval('primary_key_seq') instead of @@identity).
Any suggestions?
Jochem
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgresQL equivalent of NOCOUNT
Jochem van Dieten [EMAIL PROTECTED] writes: Does PostgresQL have some way to make update, insert and delete queries not return the number of affected rows? I know that in MS SQL one would use NOCOUNT for that. Uh ... why? Seems like a useless anti-feature. Certainly suppressing the count wouldn't save a noticeable number of cycles. 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] PostgresQL equivalent of NOCOUNT
Jochem van Dieten [EMAIL PROTECTED] writes: Uh ... why? Seems like a useless anti-feature. Certainly suppressing the count wouldn't save a noticeable number of cycles. I am not in it for the cycles, just for the laziness ;) Currently working with a ColdFusion frontend through ODBC, and ColdFusion is unable to return 2 resultsets for one call to cfquery (the ColdFusion query implementation). Oh. So your problem is not that you don't want to know the number of rows, it's that you don't want any result indication at all for the INSERT query. In libpq you could submit two queries as a single query string PQexec(INSERT ... ; SELECT ...); and PQexec would throw away the INSERT result indicator and only return the SELECT result. I am not sure if ODBC works similarly, but you could discuss that with the ODBC guys. In any case, I see inadequate reason here to justify breaking the FE/BE protocol (one response per query), which is what it would take to do what you're asking from the backend side. Even if we did, it's not at all clear that that would make ColdFusion work the way you're hoping. regards, tom lane ---(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] postgreSQL on windows2000
yes you can. go to http://sources.redhat.com/cygwin/lists.html and download the cygnus windows tool kit. the binary install comes with postgres pre-built so if you don't want to build it yourself you don't have to. after you install cygwin you will need to read the postgres config document found in c:\cygwin\usr\doc\cygwin\postgresql-7.1.2.readme. note you don't want the readme that is in usr\doc, you have to go to usr\doc\cygwin. for more support on postgres on win2k you should subscribe to the pgsql-cygwin mailing list. from what i have seen and read there aren't any really good win32 gui front ends. i have heard good things about pgaccess (a tcl/tk ms access replacement) but have not yet installed it. good luck and have fun! rjsjr -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of news.skynet.be Sent: Tuesday, August 07, 2001 6:28 AM To: [EMAIL PROTECTED] Subject: [GENERAL] postgreSQL on windows2000 Hi, I'm new to linux and postgresql. We use a windows2000 server and I'm wondering how I can run postgresql on it. Is there a front-end available too? Can anyone give me some explainations or give a site with all the info? Thanks, Eli ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] DeadLocks
This is my first posting to this site, but it's my last resort. We're running 7.0.3. I have 20 servlets that makeuse of our postgres database. All of the programs are able to perform their assigned SQL tasks, but not on a consistent basis. Often and unpredictably, different programs performing different functions (add, delete, update) will essentially hang. I will view the process (ps -ef) and postgres will be in an (commit, delete or update) state but does not finish. All others wishing to perform a task either via the browser or at command line (psql) will hang. The only way I can free up the process is having our sysadmin "kill" the process. The ten tables contain less than 400 rows. We are in test mode and will go into production after this "show stopper" is resolved. I have run "vacuum" which also hangs on one table that contain 382 rows. I am the sole tester, so I am not clashing with other persons. The servlets each make their own connection using the postgres' jdbc driver. The connections are closed at the end of the program. These programs are very simple insert, update, select statements. Has anybody else had this problem? What debugging methods/tools/logs will help. This is a very smalldevelopment shop, so I'm throwing this out to a larger community for the first time. Thanks, Gordon Campbell [EMAIL PROTECTED] Educational Technologist 212-854-1869
[GENERAL] Re: Perfomance decreasing
Tom Lane wrote: I'm doing vacuum periodically (once a hour), but perfomance still falls down. It sounds to me like you may be running into index growth problems. VACUUM is presently not good about shrinking indexes. I always enjoy Tom's comments - he is the master of understatement and always helpful. In this case, however, I think he may be understating too much. I read the original question as PostgreSQL is not useful for production systems. Call me melodramatic if you like: you are probably right. The point, I guess, is this: it would be really useful to have a document somewhere that honestly described the limitations of (the current version of) PostgreSQL. Don't use inheritance, don't use on 24x7 systems, whatever. It doesn't have to be fancy formatting, a brain-dump to a text file would be excellent (This is a hint, Tom et al!! :-)) If you drop and recreate the indexes used by your most important queries, does the performance go back to where it was? For what it's worth: I observed a similar issue and found that a dump and restore of all the databases helped. I haven't tried just recreating the index. I'll try it out and maybe post a test script to reproduce the issue. (where?) --- Allan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] cmd-line interface
Craig Longman writes: 1) stop on error this seems to be doable through the env-var ON_ERROR_STOP, but i do wish it was also parm i could pass on the cmd-line to psql. its just so much easier than setting the everytime i want the stop behaviour. i guess i could just set it and leave it. psql -V ON_ERROR_STOP= or check out ~/.psqlrc -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] A fourth PostgreSQL article
Here is a fourth article discussing MySQL and PostgreSQL: http://webtechniques.com/archives/2001/09/jepson/ -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] help on delete trigger.
Sundararajan wrote: I am developing a db application in postgresql and i need to write a delete trigger on one of the tables. the environment is table1 field1 varchar(64) other fields. table 2. field1 varchar(64) other fields I need a delete trigger on the table 1, so that if I delete a row from table 1 , the corresponding rows from table 2 should also be deleted. This is the code I have tried. DROP FUNCTION ApplicationsDeleteFn(); CREATE FUNCTION ApplicationsDeleteFn() RETURNS OPAQUE AS ' BEGIN delete from ports where appName=OLD.appName; RETURN OLD; END; ' LANGUAGE 'plpgsql'; Please help me with this, as my work is time bound. I don't see anything wrong with the trigger you have (except that you use appName instead of field1). But shouldn't that be a foreign key reference with ON DELETE CASCADE and ON UPDATE CASCADE in the first place? This would automatically delete the referencing rows, so no need for a custom trigger. In addition, it'll ensure that you can't get entries into ports for non-existent applications at all. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] explain, planner and more..
Hi, how has the following to be read.. (ie. what is the total cost of the query)... 4.05, 5.88 or ? Why does the planner choose not to use numberdomain_pkey as index on numberdomain ? The table layout is quite bad (due to a lot of last minute-changes) snip # explain select c.*, (select count(*) from numberservice ns where ns.customerid=c.customerid and (exists (select * from numbermail nm where nm.domainnr = ns.domainnr) or exists(select * from numberdomain nd where nd.domainnr=ns.domainnr))) as amount from customer c where c.status=0; NOTICE: QUERY PLAN: Seq Scan on customer c (cost=0.00..4.05 rows=46 width=200) SubPlan - Aggregate (cost=5.88..5.88 rows=1 width=0) - Index Scan using numberservice_customerid_ix on numberservice ns (cost=0.00..5.88 rows=1 width=0) SubPlan - Index Scan using numbermail_pkey on numbermail nm (cost=0.00..2.01 rows=1 width=47) - Seq Scan on numberdomain nd (cost=0.00..1.85 rows=1 width=31) EXPLAIN -- Mail [EMAIL PROTECTED] - [EMAIL PROTECTED] - PGP key id : 0xDF484022 ICQ: 5434480 - http://www.krap.dk - http://www.krap.net PGP Key http://keys.pgp.dk:11371/pks/lookup?op=getsearch=0xDF484022 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] LARGE db dump/restore for upgrade question
Philip Crotwell wrote: Hi I have a very large database of seismic data. It is about 27 Gb now, and growing at about the rate of 1 Gb every 3-4 days. I am running snip Out of curiosity, how long does it take you to vacuum that? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(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] Ugrading to 7.1 from 7.1
I am trying to upgrade from pg7.1 from 7.0 on RH7.0.
I do:
rpm -Fvh postgresql*7.1.2*rpm
but I get:
file /usr/bin/pg_id from install of postgresql-server-7.1.2-4PGDG conflicts
with file from package postgresql-7.0.2-17.
I've attached pg_id to this message.
I don't know what pg_id does (apart from the obvious that provides some
identification of postgres). Should I delete it (or rename it) and try
again?
Regards,
Paul.
begin 666 pg_id.dat
M?T5,1@$!`0(``P`!$($#0```H#0```#0`( `
M`@`0`8``8T-( $#2 ! C P 4$`P```/0`
M``#T@ 0(]( $!,3! $!`` ! @`@ 0(@ L`
M`( +```%`! ```$``` P``@)L$(;! A4`0``= $```8`$ ``
M`@```.P+``#LFP0([)L$.@```#H!@0$ $```B!! @(
M@00(( ``` $! ```]L:6(O;0M;EN=7@NV\N,@``! ```! `
M```!1TY5@41@`3% ``
M``` `,#P-#@`6
M%P```!D1`@`#`04`
M 0+`! `
M```)$@```!4*@``
M`.R;! @`$0#Q_TX!```LA@0(@0```(```#./(8$,X2
M\ ```$R! @M$@```!X!``!A@0(0@$``!(```#5;(8$)H!```2
M(P2! @`$@`+`/@```!\A@0(K ```(P`0``U)P$ 0`
M```1`!`%P$``(R! @S$@```L!``#4G 0(`!$`\?\\`0``G(8$
M,H2OP```*R! @R$@```.D```\A@0(= ```!(I
M,(H$ `2``X`V@```,R! B=(@```!``#A@0(0@$``!(W
M`0``[(8$ 4!```29 $``-2! @`$0#Q_R\```DFP0(`!$`
M\?]W`0``])P$ `1`/'_4P```2*! @$$0`/`! !``#8G 0(! ``
M`!$`%P#_(8$'02`0``( !?7V=M;VY?
MW1AG1?7P!L:6)Z+G-O+C$`7T193D%-24,`7VEN:70`7V9I;FD`7T=,3T)!
M3%]/1D93151?5$%3$5?`QI8F-R7!T+G-O+C$`7TE/7W-T9EN7W5S960`
M;EBF5S;VQV+G-O+C(`;EB;G-L+G-O+C$`;EB9PNV\N,@!L:6)M+G-O
M+C8`;EBF5A9QI;F4NV\N-XQ`QI8G1EFUC87 NV\N,@!L:6)C+G-O
M+C8`')I;G1F`=E=5U:60`5RF]R`'!U=',`7U]C%?9FEN86QIF4`
M9V5T=6ED`9PFEN=8`7U]D97)E9VES=5R7V9R86UE7VEN9F\`;W!T:6YD
M`=E=]P= !G971P=W5I9 !G971P=VYA;0!S=1EG(`97AI= !?7VQI8F-?
MW1AG1?;6%I;@!?7W)E9VES=5R7V9R86UE7VEN9F\`7V5D871A`%]?8G-S
M7W-T87)T`%]E;F0`+W5SB]L:6(`1TQ)0D-?,BXQ+C,`1TQ)0D-?,BXP
M``$``@```(``@```$``@```(``0```(``@`!``,``@```$``0`!``$`
M`@``0``+40`',?:0D```,`A0$``! 0:6D-```
M`)$!Z)L$ 89``#4G 0(!0D``-B! @%%P``L)L$ ``TFP0(
M!P,``+B;! @'! ``O)L$ %``# FP0(!P8``,2;! @' ``R)L$ *``#,
MFP0(!PP``-;! @'#0``U)L$ .``#8FP0(!Q ``-R;! @'$0``X)L$ 2
M``#DFP0(!Q@``%6)Y8/L.@E`0``D.B[`0``Z-8#``#)P_\UJ)L$/\EK)L$
M #_);;! AH`.G@_R6TFP0(: @```#IT/\EN)L$@0
MZ#_);R;! AH ```.FP_R7 FP0(: ```#IH/\EQ)L$
M@HZ9#_)B;! AH, ```.F _R7,FP0(:#@```#I/\E
MT)L$A Z6#_)=2;! AH2 ```.E0_R78FP0(:% ```#I0/__
M__\EW)L$A8Z3#_);! AH8 ```.D@_R7DFP0(:@```#I
M$/___P`Q[5Z)X8/D\%!44F@PB@0(: 2! A15F@0B 0(ZOTB?95
MB534.@`6X'#9A0``(N#1 ```(7 = +_T(M=_,G#B?:0D)0D)0D%6+
M%8R;! B)Y8/L(72=4F+%8B;! B+`H7 =!J-=8`C4($HXB;! C_$HL5B)L$
M(L*AEUZKA\A@0(A!T$(/L#B0FP0(Z-#^__^#Q!X`0```*.,FP0(BQ=
MPXUV`%6)Y8/L(GL7.-M@!5B6X+(8$(/L(7 =!6#[ AHW)P$B0
MFP0(Z#O^__^#Q!)[%W#D(VT)@!5B6#[ B)[%W#C;8`58GE5S'_
M5E.#[ R+70C'1? `BW4,QT7L`,=%Z #K6Y-M8`@_AR
M=#^')_#L=%\ $```#^YT/L8QT7H`0```(/X=70OZPK'1P!ZR20
MBPZ#[ 2+%=2! A1:**! A2Z,7]___'!0!Z%G^__^#[ 1H@(H$%93
MZ.K]__^#Q!#^/]UEJ'8G 0(*.%VWX#BSRBT7PA!T,8M=Z(7;=2J+#H/L
M!(L5U)P$%%HX(H$%+H;_W__\$) $```#H`_[__XVT)@%_W0LBT7L
MA!T)8L^@^P$BS74G 0(5V@@BP0(5N@Z_?__QP0D`0```.C._?__B?:%_W0Q
M@^P,5^BO_?__@\00A!U15+'HL-U)P$%-H48L$%'H`_W__\$) $```#H
ME_W__XM5[(72= J#[!#H6/W__^L(@^P0Z([]__^)!3HYOS__X/$$(7 =1+
M!H/L#%#HM/S__\$) $```#H6/W__XM]Z(7_=16+,(/L!%:+6 A3:+! CK
M)(UT)@+3?%R70-BQ#[ Q2Z*K\___K$8M (/L%!HXL$.C7_/__@\00
MC67T,!;7E]=PXVT)@-O`5:4FP0(B53@^P$@_C_NY2;! AT
M%HUV`(V\)P#ZP3_T(L#@_C_=?186UW#58GE@^P(BQ=PXVV`%6)
MY5-2Z !;@-J$0``C78`Z!?]__^+7?S)PP``
M``,!``(`;G)U
M``!5V%G93H@)7,@6RUN72!;+7)=(%LM=5T@
M6W5S97)N86UE70H`)7,Z(UN
M(UUW0@8F4@=7-E9!T;V=E=AEB!W:71H(UU@``
M`5S.B MB!C86YN;W0@8F4@=7-E9!W:5N($@=7-E
MB!N86UE(ES(=I=F5N@`ESH@)7,Z(YO('-U8V@@=7-E@H`=6ED/25D
M*5S*0H`)60*H)L$ ``_P``
M``#_`.R;! @``#*! AA@0(4H8$*! ARA@0(@H8$
M)*! BBA@0(LH8$,*! C2A@0(XH8$/*! @AP0(``$0
M`0```$4!8@$```!Q`0```'T!B $```2
M`0```*4!M0\```!\`0``# 2! @-,(H$ 0`
M```H@00(!0```'R#! @W($$ H```%`0``P```! 5
M``,```DFP0(`@```' 4$0```!```4A00($0```'R%! @2
M ```!,(_O__;TR%! C___]O`0```/#__V\8A00(
M+G-Y;71A8@`NW1R=%B`YS:'-TG1A8@`N:6YT97)P`YN;W1E+D%22UT
M86`+FAAV@`+F1Y;G-Y;0`N9'ENW1R`YG;G4N=F5RVEO;@`N9VYU+G9E
MG-I;VY?@`NF5L+F=O= `NF5L+F)SP`NF5L+G!L= `N:6YI= `NQT
M`YT97AT`YF:6YI`YR;V1A=$`+F1A=$`+F5H7V9R86UE`YC=]RP`N
M9'1OG,`+F=O=
[GENERAL] dump/restore failing in 7.1.2
In dumping from one 7.1.2 install and trying to restore in another fresh install: You are now connected to database template1. DELETE 0 psql:/local/dumpall-8-14:7: ERROR: CREATE USER: sysid 101 is already assigned CREATE USER DELETE 0 You are now connected to database template1 as user postgres. CREATE DATABASE You are now connected to database mydb as user postgres. psql:/local/dumpall-8-14:22: \connect: FATAL 1: user username-goes-here does not exist -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgresQL equivalent of NOCOUNT
Jochem van Dieten wrote:
Dwayne Miller wrote:
SELECT nextval('mysequence') AS PKEY FROM DUAL;
...
Your inserts and updates using #queryname.pkey#
I know, but it has 2 queries again, which is exactly the reason why I
don't want it (I am actually developing this to be used by others).
BTW, you can just call functions without a FROM, as in:
SELECT now() AS test
Not very portable... which was a requirement for me.
I generally have to use the same key in a series of 1 to 3 inserts, so
this solution seems to work well.
One solution of many
I think I will just replace cfquery with cf_query. Then have cf_query
replace every insert with a function call (so I don't have to double
all the quotes manually), where the function takes care about the rest
and returns #insertedrecord.id# or whatever. Would probably be the
easiest way to explain to others.
Jochem
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Null Conversion
Can anyone tell me how I might convert a null attribute value into a zero attribute value such that it can be multiplied in a query. In Oracle I could do: sal*12*NVL(COMM, 0) AS Annual Income where COMM is an attribute (a salesman commission, in an employes table) which has null values. This allows null commissions for non salesmen to give a zero calculated value. The NVL converts a null into zero. Thanks in anticipation Mike Withers University of Western Sydney Australia ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PGSQL Intro Concepts PDF
Me! :) Geoffeg This one time, at band camp, Digital Wokan wrote: Who besides Daniel asked me for this? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Geoffrey Gallaway || I dunno, I dream in Perl sometimes. [EMAIL PROTECTED] || D e v o r z h u n ||-- Larry Wall ---(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] Null Conversion
On Wed, 15 Aug 2001, Mike Withers wrote: Can anyone tell me how I might convert a null attribute value into a zero attribute value such that it can be multiplied in a query. In Oracle I could do: sal*12*NVL(COMM, 0) AS Annual Income where COMM is an attribute (a salesman commission, in an employes table) which has null values. This allows null commissions for non salesmen to give a zero calculated value. The NVL converts a null into zero. Try coalesce(COMM, 0) ---(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] Null Conversion
Can anyone tell me how I might convert a null attribute value into a zero attribute value such that it can be multiplied in a query. In Oracle I could do: sal*12*NVL(COMM, 0) AS Annual Income sal * 12 * CAST(COMM AS float8) AS Annual Income Cheers Jason -- Indigo Industrial Controls Ltd. 64-21-343-545 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: nextval, sequences and sequencenames
Hi, My question: is there anyway of retreiving the sequence_name corresponding to the respective column, knowing just the tablename and columnname? The reason I need to do this, is because the application I write dynamicly creates new tables, and I have no way of knowing the sequence name before hand. If it is very difficult finding that sequence name (I'm not sure) You may want to create the sequence yourself instead of using SERIAL. Then you would know the name. Not hard at all. csmith=# create table test (testid serial, name text); NOTICE: CREATE TABLE will create implicit sequence 'test_testid_seq' for SERIAL column 'test.testid' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_testid_key' for table 'test' CREATE Sequence name becomes tablename_columnname_seq so if we use the serial datatype to create a column called xyz in the table abc the sequence name is : abc_xyz_seq Make sense? - Chris Smith http://www.squiz.net/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [GENERAL] why no stored procedures?
Jan Wieck responded in an irritated manner thusly: What exactly do you mean with there are no stored procedures? i won't pretend to know what what the original poster had in mind when asking his question but i'm a newbie at postgres and i have some confusion as to how a function maps to a stored procedure. the big question in my mind is how i can treat the results from a function as a table. i can't. i posted a message to the pgsql-sql list which i have included below. this tells me that postgres _does not_ support stored procedures in the manner that those of us coming from microsoft sql server and/or oracle. thanks! rjsjr i'm reading the postgres documentation and i'm specifically interested in creating stored procedures so that i can keep as much of the business logic in the database as possible. while reading 13.1.3 (SQL Functions on Composite Types) in the Programmer's Guide i come across the phrase... When calling a function that returns a row, we cannot retrieve the entire row. We must either project an attribute out of the row or pass the entire row into another function. SELECT name(new_emp()) AS nobody; Unfortunately, PostgreSQL functions do not yet return result sets. This is on the todo list but unfortunately requires an overhaul of how postgresql functions work. -Josh Berkus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jan Wieck Sent: Tuesday, August 14, 2001 9:51 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] why no stored procedures? [EMAIL PROTECTED] wrote: Hey guys, Is there any reason why there are no stored procedures for postgresql or does this have to do with the ability to add your own procedural language? What exactly do you mean with there are no stored procedures? I mean, we have more procedural languages than any other database and with the upcoming v7.2 we will even have reference cursors for PL/pgSQL to pass them into and out of functions. So could you detail your question please? Jan -- #=== ===# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Null Conversion
At 06:30 PM 8/14/01 -0700, you wrote: On Wed, 15 Aug 2001, Mike Withers wrote: Can anyone tell me how I might convert a null attribute value into a zero attribute value such that it can be multiplied in a query. In Oracle I could do: sal*12*NVL(COMM, 0) AS Annual Income where COMM is an attribute (a salesman commission, in an employes table) which has null values. This allows null commissions for non salesmen to give a zero calculated value. The NVL converts a null into zero. Try coalesce(COMM, 0) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Thanks, this works. Tried the other suggestion: sal * 12 * CAST(COMM AS float8) AS Annual Income which unfortunately didn't work. ---(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] explain, planner and more..
Svenne Krap [EMAIL PROTECTED] writes: Why does the planner choose not to use numberdomain_pkey as index on numberdomain ? - Seq Scan on numberdomain nd (cost=0.00..1.85 rows=1 width=31) Evidently because it thinks numberdomain only has one disk block, and hence there's no possible savings from reading an index in addition to that one disk block. (If it were estimating more than one block read then the cost estimate would be 2 or more. 1.85 implies one block read = 1.0 cost unit, plus some per-tuple CPU effort.) If numberdomain is indeed big enough to warrant an index search, then you need to VACUUM it to update the planner's statistics. The plan you are getting is based on statistics that say numberdomain is tiny. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Null Conversion
On Wed, 15 Aug 2001, Mike Withers wrote: At 06:30 PM 8/14/01 -0700, you wrote: On Wed, 15 Aug 2001, Mike Withers wrote: Can anyone tell me how I might convert a null attribute value into a zero attribute value such that it can be multiplied in a query. In Oracle I could do: sal*12*NVL(COMM, 0) AS Annual Income where COMM is an attribute (a salesman commission, in an employes table) which has null values. This allows null commissions for non salesmen to give a zero calculated value. The NVL converts a null into zero. Try coalesce(COMM, 0) Thanks, this works. Tried the other suggestion: sal * 12 * CAST(COMM AS float8) AS Annual Income which unfortunately didn't work. Yeah, that'll still give you a null out. I guess oracle must have done nvl before the standards group decided on coalesce for the name of that. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] OpenBSD 2.9 - installation works fine but psql won't run
Ay caramba. I'm trying to get PostreSQL 7.1.2 up and running on my i386 OpenBSD 2.9 machine. It seems like all is going well -- ./configure, gmake, gmake install, initdb, createdb test all work fine, but then when I try to psql test I get the following: - - - - - - - - - bash-2.05$ /usr/local/pgsql/bin/psql test Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit /usr/libexec/ld.so: Undefined symbol _tgetent called from psql:/usr/lib/libreadline.so.0.0 at 0x40088964 - - - - - - - - - ...and it fails to connect. I'm using readline 4.2, in case that matters. Here's the output from ./configure: - - - - - - - - - bash-2.05# ./configure creating cache ./config.cache checking host system type... i386-unknown-openbsd2.9 checking which template to use... openbsd checking whether to build with locale support... no checking whether to build with recode support... no checking whether to build with multibyte character support... no checking whether to build with Unicode conversion support... no checking for default port number... 5432 checking for default soft limit on number of connections... 32 checking for gcc... gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... yes checking whether gcc accepts -g... yes using CFLAGS=-O2 -pipe checking whether the C compiler (gcc -O2 -pipe ) works... yes checking whether the C compiler (gcc -O2 -pipe ) is a cross-compiler... no checking for Cygwin environment... no checking for mingw32 environment... no checking for executable suffix... no checking how to run the C preprocessor... gcc -E checking whether gcc needs -traditional... no checking whether to build with Tcl... no checking whether to build with Tk... no checking whether to build Perl modules... no checking whether to build Python modules... no checking whether to build Java/JDBC tools... no checking whether to build the ODBC driver... no checking whether to build C++ modules... no using CPPFLAGS= using LDFLAGS= checking for mawk... no checking for gawk... gawk checking for flex... /usr/bin/flex checking whether ln -s works... yes checking for ld used by GCC... /usr/bin/ld checking if the linker (/usr/bin/ld) is GNU ld... no checking for ranlib... ranlib checking for lorder... lorder checking for tar... /bin/tar checking for perl... perl checking for bison... bison -y checking for readline... yes (-lreadline) checking for library containing using_history... none required checking for main in -lbsd... no checking for setproctitle in -lutil... yes checking for main in -lm... yes checking for main in -ldl... no checking for main in -lsocket... no checking for main in -lnsl... no checking for main in -lipc... no checking for main in -lIPC... no checking for main in -llc... no checking for main in -ldld... no checking for main in -lld... no checking for main in -lcompat... yes checking for main in -lBSD... no checking for main in -lgen... no checking for main in -lPW... no checking for main in -lresolv... yes checking for main in -lunix... no checking for library containing crypt... none required checking for __inet_ntoa in -lbind... no checking for inflate in -lz... yes checking for crypt.h... no checking for dld.h... no checking for endian.h... no checking for fp_class.h... no checking for getopt.h... no checking for ieeefp.h... yes checking for pwd.h... yes checking for sys/ipc.h... yes checking for sys/pstat.h... no checking for sys/select.h... yes checking for sys/sem.h... yes checking for sys/socket.h... yes checking for sys/shm.h... yes checking for sys/types.h... yes checking for sys/un.h... yes checking for termios.h... yes checking for kernel/OS.h... no checking for kernel/image.h... no checking for SupportDefs.h... no checking for netinet/in.h... yes checking for netinet/tcp.h... yes checking whether string.h and strings.h may both be included... yes checking for readline/readline.h... yes checking for readline/history.h... yes checking for working const... yes checking for inline... inline checking for preprocessor stringizing operator... yes checking for signed types... yes checking for volatile... yes checking whether struct tm is in sys/time.h or time.h... time.h checking for tm_zone in struct tm... yes checking for union semun... yes checking for struct sockaddr_un... yes checking for int timezone... no checking types of arguments for accept()... int, struct sockaddr *, size_t * checking whether gettimeofday takes only one argument... no checking for fcvt... no checking for getopt_long... no checking for memmove... yes checking for pstat... no checking for setproctitle... yes checking for setsid... yes checking for sigprocmask... yes checking for sysconf... yes checking for waitpid... yes checking
Re: [GENERAL] why no stored procedures?
[EMAIL PROTECTED] wrote: Hey guys, Is there any reason why there are no stored procedures for postgresql or does this have to do with the ability to add your own procedural language? What exactly do you mean with there are no stored procedures? I mean, we have more procedural languages than any other database and with the upcoming v7.2 we will even have reference cursors for PL/pgSQL to pass them into and out of functions. So could you detail your question please? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] LARGE db dump/restore for upgrade question
Any suggestion on how to prepare for the next upgrade would be appreciated. I think it has to be said that if you want decent performance on excessively large (50GB+) databases, you're going to need excessively good hardware to operate it on. Buy a 3ware IDE RAID controller (www.hypermicro.com) and a stripe a couple of seagate 80GB IDE drives (the new ones have an awesome transfer rate). Turn of fsync, and use softupdates or even async I/O. I am guessing though you've already done all of the above. You might have to go beefier hardware, for PCs that means DDR SDRAM/Rambus, and a 64 bit PCI 3ware card with 4 or 8 hard drives. Hope that helps Andrew ---(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] I am confused about PointerGetDatum among other things
If anyone cares I have figured out how to do this. I use SPI_getbinval and it works perfectly ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
