Re: [GENERAL]
Matthew Peter wrote:
CREATE OR REPLACE FUNCTION getlist(text) RETURNS SETOF my_tbl as $$
SELECT * FROM my_tbl
WHERE u_id IN (0, $1);
$$ LANGUAGE SQL;
SELECT * from getlist('1,2,3');
(0 rows)
You're executing SELECT * FROM my_tbl WHERE u_id IN (0, '1,2,3').
Apparently there are no values 0 or '1,2,3' for u_id in that table.
I'm sure it's probably trival but I'm still learning how psql :) Thanks
You can split the string into values with string_to_array(). You'll
still be comparing ints with strings though.
Without casting the resulting strings to ints you run the risk that the
database needs to cast the int u_id value to text for every record
encountered. For 'small' data sets this shouldn't be a problem.
I suppose this is a simplified example, or you wouldn't have a reason to
use an SP; you'd just SELECT * FROM my_tbl WHERE u_id IN (0,1,2,3)
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] regarding triggers
Title: regarding triggers is there an advantage of using a trigger? when the same job can be performed by a stored procedure? one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) thanks, regards Surabhi
Re: [GENERAL] Best programming language / connectivity for best performance
I really wouldn't take relative DB performance into much consideration when choosing a programming language for your project. I have found that the actual Language/API overhead to be pretty inconsequential in most of my projects. When my DB access is slow, I have found it is almost always a problem with my query or the DB is just overloaded. There is one exception I can think of, and that's doing bound controls in VB through ODBC. I haven't done bound controls in VB for years and years, so I don't even know if it's still in the language, but they're crappy and you shouldn't use them. When I'm using a RAD tool like VB etc. I *never* use design-level tools to handle my DB IO. When I write a client side app, I manually handle my own DB IO and let the GUI simply handle the user interface. Nico Callewaert wrote: Hi, I was wondering what is the best database connectivity and programming language to get the best performance with PostgreSQL. I'm currently working with Delphi, but I don't know if that is the best way to go ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] regarding triggers
Foreign keys do not cascade deletions. If table y references table x on column a, the attempt to delete records in x with dependent records in y will yield an error. So the answer to that question is no, your trigger won't get called because a) y doesn't get touched because that's not what foreign keys do and b) an error is raised anyway Regarding the usefulness of triggers...I tend to stay away from them. I like to keep my data in my database and my logic in my application. I try to relegate triggers to very simple things like timestamping records. i.e. things that I won't later wonder What in the hell is going on??? surabhi.ahuja wrote: is there an advantage of using a trigger? when the same job can be performed by a stored procedure? one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) thanks, regards Surabhi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] regarding triggers
On 1/10/06, surabhi.ahuja [EMAIL PROTECTED] wrote: is there an advantage of using a trigger? when the same job can be performed by a stored procedure? a trigger is actually a stored procedure... the advantage is that it's called automagically when an event happens... one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. only if you specified ON DELETE CASCADE at FOREIGN KEY creation now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) if the DELETE will CASCADE, yes thanks, regards Surabhi -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Sequence Manipulation Functions
Hello, I use PostgreSQL 8.0.3. I want to get the information of the last value of a sequence. The function 'currval' only gives the value back, if before a nextval is executed. Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did. But that is not very helpful. I noticed that the phpPgAdmin has that information Name Last value Increment by Max value Min value Cache value Log count Is cycled? Is called? adr_dsnr 108 1 9223372036854775807 1 1 25 No Yes So how can I get that information? Thanks Michaela
Re: [GENERAL] regarding triggers
On 1/10/06, Jaime Casanova [EMAIL PROTECTED] wrote: On 1/10/06, surabhi.ahuja [EMAIL PROTECTED] wrote: is there an advantage of using a trigger? when the same job can be performed by a stored procedure? a trigger is actually a stored procedure... the advantage is that it's called automagically when an event happens... one more question is as follows: suppose i have a table x, with a primary attribute 'a' and i have a table y, with the primary attribute 'b', and a foreign key 'a'. suppose i say delete from x where a = '1', it means that not only the rows from x get deleted but also rows from y get deleted. only if you specified ON DELETE CASCADE at FOREIGN KEY creation now i have a trigger which is written for deletes taking place from the table y. however if i say delete from x where a = '1', will the trigger (mentioned above) still be called? (because delete are also taking place from the table y) if the DELETE will CASCADE, yes thanks, regards Surabhi -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Sequence Manipulation Functions
Select last_value from your_sequence_name; John MG wrote: Hello, I use PostgreSQL 8.0.3. I want to get the information of the last value of a sequence. The function 'currval' only gives the value back, if before a nextval is executed. Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did. But that is not very helpful. I noticed that the phpPgAdmin has that information Name Last value Increment by Max value Min value Cache value Log count Is cycled? Is called? adr_dsnr 108 1 9223372036854775807 1 1 25 No Yes So how can I get that information? Thanks Michaela ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] regarding triggers
John McCawley wrote: Foreign keys do not cascade deletions. They will if you specify on delete cascade. -- Guy Rouillier ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] regarding triggers
John McCawley [EMAIL PROTECTED] writes: Foreign keys do not cascade deletions. By default, no, but there is the CASCADE option ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Bulk] Re: [GENERAL] Best programming language / connectivity for best performance
Nico, I do not believe there is anyone who can tell you the best way to go as that is determined by far more than DB access. That said, I am inclined to agree with John. I would expand on that to say that if Delphi is the only language you know, then it is the best option for getting productive quickly. Otherwise, you have to resort to multiparadigm development and determine which of the languages you know best meets the requirements for your project. I use FORTRAN, C++, and JAVA, and I know VB, Perl, and PHP. If I require an outstanding object model, my choice reduces to C++ and JAVA, because the object model in FORTRAN is almost as seriously broken as that in VB. I generally don't use VB, except for trivial toys, and even there, it has been years since I used it. Perl and PHP are interesting, but I have yet to see an advantage they can offer over JAVA (including servlets and JSP) or C++. I use FORTRAN for specialty programs requiring extensive number crunching, but it is being displaced in my own work by C++ particularly because of the advantages provided by metaprogramming. The only languages I have used for commercial application development are C++ and Java. If there is significant networking and/or security, I lean towards JAVA because of the related libraries built into the SDK. OTOH, if I need real generic programming, I lean toward C++ because, while JAVA has made some progress in generics, it is still quite broken relative to the powerful support for generics in C++. And, if there is a need for metaprogramming based on generic programming, then C++ is the only choice I am aware of. You see, every language has it's strengths and weaknesses, and you generally need to choose based on what you know about each language you know how to use, and the availability of libraries to support your problem domain. In my experience, you become a more productive and better programmer as you make a point of learning more programming languages and give thought to their relative strengths and weaknesses. Almost invariably, when my use of a DB is slow, it is because I have a problem with my queries. This happens more often than it should, but then I have been programming using languages like FORTRAN, Pascal, Basic and C++ (all closely related Algol languages) for well over two dozen years and I started studying RDBMS and SQL only a few years ago. Like John, when I'm using a RAD tool (and I should say I like Delphi almost as much as I like Netbeans and Borland's C++ Builder), I handle DB access in my own code and let the GUI handle the interface only. My experience with bound controls is similar to John's. They are a convenience for novice programmers, but once you start adding more professional features, such as data validation and formatting, they leave just about everything to be desired. In my work, they served as a convenience during proof of concept work to show a client where their project was heading, but in virtually every case, I replaced them for one reason or another. They just didn't provide the feature set I required and it was just less work to use the basic controls than it was to fight with broken bound controls. Cheers, Ted - Original Message - From: John McCawley [EMAIL PROTECTED] To: Nico Callewaert [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Tuesday, January 10, 2006 9:06 AM Subject: [Bulk] Re: [GENERAL] Best programming language / connectivity for best performance I really wouldn't take relative DB performance into much consideration when choosing a programming language for your project. I have found that the actual Language/API overhead to be pretty inconsequential in most of my projects. When my DB access is slow, I have found it is almost always a problem with my query or the DB is just overloaded. There is one exception I can think of, and that's doing bound controls in VB through ODBC. I haven't done bound controls in VB for years and years, so I don't even know if it's still in the language, but they're crappy and you shouldn't use them. When I'm using a RAD tool like VB etc. I *never* use design-level tools to handle my DB IO. When I write a client side app, I manually handle my own DB IO and let the GUI simply handle the user interface. Nico Callewaert wrote: Hi, I was wondering what is the best database connectivity and programming language to get the best performance with PostgreSQL. I'm currently working with Delphi, but I don't know if that is the best way to go ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] q: explain analyze
Hello, When I run 'explain analyze' on a query, how do I know what index is used and is it used at all. What are specific words should I look for? Is Seq Scan indicates that index has been used? How do I know that it was Full Table Scan? Thanks, Mark. __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] q: explain analyze
On 1/10/06, Mark [EMAIL PROTECTED] wrote: Hello, When I run 'explain analyze' on a query, how do I know what index is used and is it used at all. What are specific words should I look for? Is Seq Scan indicates that index has been used? How do I know that it was Full Table Scan? Thanks, Mark. Seq Scan is short for Sequential Scan (Full Table Scan)... you have to look for the word index to see what indexes are you using if any (the name of the indexes are used too, so if you now indexe's names you can find them in the explain analyze quickly) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] regarding triggers
On 1/10/06, John McCawley [EMAIL PROTECTED] wrote: Regarding the usefulness of triggers...I tend to stay away from them. I like to keep my data in my database and my logic in my application. I try to relegate triggers to very simple things like timestamping records. i.e. things that I won't later wonder What in the hell is going on??? I always try to get all the relationships from the data into the database using whatever the database can do ie triggers, foriegn keys, check constraints etc. I find that leaving all the logic to the application is a disaster waiting to happen particularly when the application is being developed by lots of people. If you insist in having all the logic in the application then surely you could use triggers to make sure that if the application makes a cock up then the integrity of the data won't be compromised. I know you can use begin; commit; from the application but when working with other developers there are no guarantees that they will always be using them or more likely a mistake will be made by me or someone else and I want the database to handle it. -- Harry http://www.hjackson.org http://www.uklug.co.uk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] copy a postgres dbase to the same machine with different name?
Hi everyone, For a project here at work I look after a web app that communicates with a postgresql database. We need to clone/copy the existing database to run app upgrades on the clone database and then use the upgraded database in place of the original. I've read the pg_dump/pg_restore man pages, tried to google for a technique, but haven't found anything that adequate explains it i'd rather not screw up a live database grin Thanks for any help, Jon.
Re: [GENERAL] copy a postgres dbase to the same machine with different name?
Jonathan Roby [EMAIL PROTECTED] writes: For a project here at work I look after a web app that communicates with = a postgresql database. We need to clone/copy the existing database to = run app upgrades on the clone database and then use the upgraded = database in place of the original. Are you talking about cloning the whole installation to run under a separate postmaster? Or are you trying to create a new database with a different name under the same postmaster? If the latter, and you can idle the database while copying it, the TEMPLATE option to CREATE DATABASE will serve. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] q: explain analyze
This is great, Now here's my explain analyze: Seq Scan on balance (cost=0.00..54.51 rows=147 width=106) (actual time=0.026..0.767 rows=62 loops=1) Filter: (amount = 0::double precision) I do have an index on amount, but I guess it won't be used for = ... is there any way to force usage of index? another question: Can I defined index for _NOT_EQUAL_ ? I have a column that can have 5 values and my where is WHERE type 'A' OR type 'B' _or_ better to use: WHERE type ='C' OR type = 'D' OR type = 'E' Thank you, Mark. --- Jaime Casanova [EMAIL PROTECTED] wrote: On 1/10/06, Mark [EMAIL PROTECTED] wrote: Hello, When I run 'explain analyze' on a query, how do I know what index is used and is it used at all. What are specific words should I look for? Is Seq Scan indicates that index has been used? How do I know that it was Full Table Scan? Thanks, Mark. Seq Scan is short for Sequential Scan (Full Table Scan)... you have to look for the word index to see what indexes are you using if any (the name of the indexes are used too, so if you now indexe's names you can find them in the explain analyze quickly) -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] ANN: PgBrowser-1.6 and PgBrowser-1.7
PgBrowse ver 1.7 is a generic Postgresql database browser that works on Windows, Macintosh and Linux platforms that is written in Tcl/Tk. A Starpack is available for Linux/x86 and an application bundle is available for MacOSX. What is new in 1.6? o 1.6 Contains and can display the complete html Postgresql documentation set. What is new in 1.7? o Corrected error that prevented Postgresql Documentation from being opened more than once per session. (sigh...) o Added the ability to save the contents of the SQL window. By default the saved file will have an extension of .sql and will be written to the ~/SQLScripts directory. o Added the ability to refresh the scripts menu while the program is running. o Added MouseWheel support (each event scrolls 5 units). A some features of PgBrowser. 1) PgBrowser can display/extract graphical images stored in the database as bytea or large objects (via the Img package). 2) PgBrower supports SQL libraries of your favorite queries. At startup PgBrowser looks for ~/SQLScripts and builds a menu of all members of the directory that end in .sql. Subdirectories will generate the appropriate submenu. Selecting a menu item will load the SQL window with the query. 3) PgBrowser supports a history of queries/commands passed to the backend. Previous commands can be easily recalled from the keyboard. Gracefully exiting the program will cause the history to be stored in ~/SQLScripts as HiStOrY.tcl. This file will be sourced at program startup to recover the command history. 4) If PgBrowser is running on a Mac or Linux system that has psql located in a standard location, it is possible to execute psql commands from within PgBrowser. 5) Individual fields can be extracted and saved to user specified files. This includes bytea and large object fields. 6) A simple grid based table editor is now available to make changes in the database. The editor has features that facilitate the use of BYTEA and Large Object fields. For more information and download visit: http://homepage.mac.com/levanj/TclTk Suggestions for improvements and bug fixes gladly accepted. Thanks, Jerry ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] q: explain analyze
Mark [EMAIL PROTECTED] writes: I do have an index on amount, but I guess it won't be used for = ... The general rule is that an index is only helpful for extracting a fairly small subset of the table (small can mean as little as 1%). So a one-sided inequality is not usefully indexable unless the comparison constant is near the end of the data range. The planner does understand this and will do the right things as long as the ANALYZE statistics are reasonably accurate. is there any way to force usage of index? You can try setting enable_seqscan = off, but you'll likely find that the planner is making the right decision. (If it isn't, you may want to play with the value of random_page_cost ... but be wary of changing that based on a small number of test cases.) Can I defined index for _NOT_EQUAL_ ? No. See above. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] q: explain analyze
On 1/10/06, Mark [EMAIL PROTECTED] wrote: This is great, Now here's my explain analyze: Seq Scan on balance (cost=0.00..54.51 rows=147 width=106) (actual time=0.026..0.767 rows=62 loops=1) Filter: (amount = 0::double precision) I do have an index on amount, but I guess it won't be used for = ... look at the rows field... the first one is the estimated by the planner the second is the actual number of rows retrieved for that Seq Scan... so if 147 (the estimated) is about a 10% of the total records in your table an index will not be used because it will be loss performance... is there any way to force usage of index? you can try SET enable_seqscan=off; before executing your query... that will increase the cost of a seq scan and not be used unless there is no other way to do it or the other methods are incredible slower another question: Can I defined index for _NOT_EQUAL_ ? I have a column that can have 5 values and my where is WHERE type 'A' OR type 'B' _or_ better to use: WHERE type ='C' OR type = 'D' OR type = 'E' is not a good idea if you only will have 5 different values... although you can create a partial index... but this is good only if you create for the value that will be appear less (maybe 10% of total record or less)... and can only be used for that specific case... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Unsuscribe
Please Unsuscribe me. Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Reordering columns in a table
On Sat, Jan 07, 2006 at 02:45:44PM -0500, Robert Treat wrote: You should be able to do this now using pg_depend, it would just take a bit of leg-work. Pretty sure it would be easier than solving physical/logical attribute separation. Someone writing a pg_list_all_dependencies function would make for a really good head start... I wonder if the newsysviews provides anything like that. The closest it comes is pg_*_foreign_key*. Listing other dependancies would be damn cool to add, though. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] E-mail harvesting on PG lists?
On Sun, Jan 08, 2006 at 12:34:25AM +0100, Peter Eisentraut wrote: Magnus Hagander wrote: archives.postgresql.org properly hides the addresses. If you think that spammers are unable to do s/ (at) /@/ you're living in a dream world. Agreed. I'd honesly rather we drop that nonsense so I can at least cut and paste email addresses when needed. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] programming in pgsql
On Mon, Jan 09, 2006 at 10:13:12AM +, Richard Huxton wrote: Angshu Kar wrote: Hi Pgsql, Could anyone please advise whether the following program can be implemented using pgsql cursors/anythign else (or do we need some external scripts)?If yes, could you give please some function names etc? [snip series of queries] Any procedural language will be able to handle this. The only problem you'll have will be that the whole function will take place inside a single transaction, so you won't be able to spread the workload out over time. There are also other ways to do this. Celko presents one in SQL For Smarties based on setting up a graph (google:sql for smarties graph), and there is also contrib/ltree. Either of these is likely to perform much better than a heirarchy, unless you're doing a lot of inserts/updates/deletes (graphs) or need a lot of levels (ltree). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Arrays and Performance
On Sun, Jan 08, 2006 at 10:22:22AM +0100, Marc Philipp wrote: This sounds like it has more to do with inadequate freespace map settings than use of arrays. Every update creates a dead tuple, and if it is large (because the array is large) and leaked (because you have no room in your freespace map), that would explain a rapidly increasing database size. Can you tell me more about free-space map settings? What exactly is the free-space map? The information in the documentation is not very helpful. How can dead tuples leak? http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3 is an article I wrote that might clear things up. http://www.pervasive-postgres.com/lp/newsletters/2005/Insights_opensource_Dec.asp#2 might also be an interesting read, though it's just about MVCC in general. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] help with rules for system table
On Sun, Jan 08, 2006 at 06:35:06PM -0800, [EMAIL PROTECTED] wrote: Hi, I have to archieve functions like this: When users define a new index, I will do something (for instance, increase an counter in my table or do some other statistics). However, I defined rule for insert on pg_class and when the entry is actually an index, I do my thing. Obviously my rule is never executed. If I create a similar rule for a table I defined, it works. Does this mean that I can't create rules for system tables? If not, does anyone know how to do this? Many system operations completely bypass the 'normal' access methods for touching the system tables, so generally you can't do things like triggers or rules. Depending on what you need to do there may be other ways to accomplish it, though. For example, it's trivial to get a count of indexes on a table... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] E-mail harvesting on PG lists?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If you think that spammers are unable to do s/ (at) /@/ you're living in a dream world. Agreed. I'd honesly rather we drop that nonsense so I can at least cut and paste email addresses when needed. I'd rather not. While obfuscation is not a surefire solution, it does help. Quite a bit, as spammers generally go for the low hanging fruit. I've done tests on this, and the number of spams received is far higher for unobfuscated email addresses. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200601101538 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDxBv6vJuQZxSWSsgRAjHTAKCS3T2o1zPaZNESrUyrL9NZyuZgYgCg/GGW XxaU+C1A4Ol7ggUsTg9SMno= =zuf4 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sequence Manipulation Functions
MG schrieb: Hello, I use PostgreSQL 8.0.3. I want to get the information of the last value of a sequence. The function 'currval' only gives the value back, if before a nextval is executed. /Return the value most recently obtained by |nextval| for this sequence in the current session. (An error is reported if |nextval| has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed |nextval| since the current session did./ But that is not very helpful. I noticed that the phpPgAdmin has that information Name Last value Increment byMax value Min value Cache value Log count Is cycled? Is called? adr_dsnr 108 1 9223372036854775807 1 1 25 No Yes So how can I get that information? SELECT * FROM adr_dsnr; Otoh, for what do you need this information? Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] help with rules for system table
Adding -general back into this... No, I don't think there's any way to trigger on this programmatically, though there has been talk from time-to-time about adding support for triggers on DDL. In the meantime you'll just need to scan the system tables for new indexes. I'm also wondering if there's some other, better way to do what you ultimately are trying to do. Adding a function based on CREATE INDEX seems extremely odd, at least to me. From: zu zu [mailto:[EMAIL PROTECTED] Hi Jim, Thank you for the message. Actually what I wanted to do is: whenever a user defines a new index, I will automatically generate 2 functions (specific to that index) in the database. Of course, I can achieve this by, for instance, check if there's new index defined every night and if so, generate corresponding functions. Since most of the time, my database won't have drastic changes at all so this would be a waste of computational power. Originally I thought rule and trigger are the perfect solution for this. Now it seems that it's not possible to achieve this. Do you know other ways to achieve this? Thanks Ruey-Lung Jim C. Nasby wrote: On Sun, Jan 08, 2006 at 06:35:06PM -0800, [EMAIL PROTECTED] wrote: Hi, I have to archieve functions like this: When users define a new index, I will do something (for instance, increase an counter in my table or do some other statistics). However, I defined rule for insert on pg_class and when the entry is actually an index, I do my thing. Obviously my rule is never executed. If I create a similar rule for a table I defined, it works. Does this mean that I can't create rules for system tables? If not, does anyone know how to do this? Many system operations completely bypass the 'normal' access methods for touching the system tables, so generally you can't do things like triggers or rules. Depending on what you need to do there may be other ways to accomplish it, though. For example, it's trivial to get a count of indexes on a table... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Array as a parameter to stored procedure.
Is it possible to accept an array (or any data structure representing an ordered set ) as an input parameter in a stored procedure? The number of elements will vary from call to call. DB: PostgreSQL 8.0.0 on Linux
[GENERAL] psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3)
I'm doing a query that really should be too taxing. But when I execute it I get the following error(s): psql(18967) malloc: *** vm_allocate(size=8421376) failed (error code=3) psql(18967) malloc: *** error: can't allocate region psql(18967) malloc: *** set a breakpoint in szone_error to debug EXPLAIN ANALYZE SELECT a1.qname, a1.symbol, a1.num, a1.ge, a1.start, a1.stop, a1.cr, a1.str, a1.ex FROM singlehits a1, singlehit_ge a2 WHERE a2.cnt1 AND a2.symbol=a2.symbol; QUERY PLAN -- Nested Loop (cost=89.36..6086.42 rows=273312 width=88) (actual time=0.113..24456.508 rows=54952794 loops=1) - Seq Scan on singlehits a1 (cost=0.00..530.82 rows=17082 width=88) (actual time=0.043..71.127 rows=17082 loops=1) - Materialize (cost=89.36..89.52 rows=16 width=0) (actual time=0.000..0.418 rows=3217 loops=17082) - Seq Scan on singlehit_ge a2 (cost=0.00..89.34 rows=16 width=0) (actual time=0.049..5.167 rows=3217 loops=1) Filter: ((cnt 1) AND ((symbol)::text = (symbol)::text)) Total runtime: 30024.664 ms (6 rows) Given this post http://xy1.org/pgsql-general@postgresql.org/ msg01154.html I tried both VACUUM FULL and ANALYZE on the DB to no avail. Thanks, Ari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] function overloading
On Mon, 2006-01-09 at 20:28, Tom Lane wrote: Robert Greimel [EMAIL PROTECTED] writes: I have a question regarding function overloading: assume that you have a function that takes several numeric arguments - lets for example say 4 arguments: f(a,b,c,d) Now further assume that internally in the function the first thing you do is to convert the arguments to double precision, do your calculations and return the result always in double precision. In order to allow for all combinations of numeric (int2,int4,int8,float4,float8) inputs one would now have to define and write 5^4 = 625 functions !!! Apart from forcing the function user to use casts, is there any other way to avoid this madness of writing 625 functions ? I don't see why you need more than one function, taking all double-precision arguments ... regards, tom lane You are right. I got confused by the error message when I first tried to call the function and had made the mistake to define it with less parameters than it actually takes. The error for select f(1,1.5,a,b) from table; was ERROR: Function 'f(int4, float8, float4, float4)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts which made me think that I have to define a function for every possible combination of numeric types. By the time I realized that I had missed a parameter I already had added casts to all parameters in the query. So it works as I would expect it - one function definition with all arguments as double is sufficient as you note. Greetings Robert ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Connection specific information - Temporary table used in Sybase to store information
Hello everybody, Since I am new to PostgreSQL mailing lists, I am not sure which mailing list to use for my problem, I issue it to this group. If there is a more appropriate list, please let me know and I will happily post there. The situation: While porting an existing application from Sybase ASA to PostgreSQL 7.4 I need to access connection specific login data (user data). In the Sybase solution I used a temporary table (CREATE TEMPORARY TABLE). Each connection than had the table on connect; each client could then perform the INSERT statement without creating table. PostgreSQL differs from the behaviour as documented. A lot of (very) different clients rely upon this this standard behaviour. The question: Is there any feature/technique that a) can create temporary tables as in the SQL Standard defined b) something like ON CONNECT DO BEGIN END? So that the temporary table can be automatically created upon connect? Thanks in advance, Kind regards, Ralf ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Array as a parameter to stored procedure.
am 10.01.2006, um 11:19:32 -0500 mailte Sameer Nanda folgendes: Is it possible to accept an array (or any data structure representing an ordered set ) as an input parameter in a stored procedure? The number of elements will vary from call to call. Yes, of course. A simple example: http://a-kretschmer.de/tools/array_compare.sql HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
