Re: [HACKERS] What do you want me to do?
It's rumoured that David Fetter once said: I guess that's OK, but it shows up like a beacon to all kinds of hostile gear. Passive systems are usually a better bet. Are there many hostile forces in your hallway? :-) /D ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] What do you want me to do?
On Fri, Dec 26, 2003 at 10:48:20AM -, Dave Page wrote: It's rumoured that David Fetter once said: I guess that's OK, but it shows up like a beacon to all kinds of hostile gear. Passive systems are usually a better bet. Are there many hostile forces in your hallway? Most days not, but I've heard PA can be pretty rough ;) Cheers, 'nother D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] connections problem
hi i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1) what can be wrong ? ---(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: [HACKERS] feature request: \qf datatype
Alex J. Avriette [EMAIL PROTECTED] wrote: I'd like to request the following feature: Frequently when answering questions on IRC for people, questions fall into one of two categories, what function can I use to manipulate datatype xyz, and what datatype can i use for xyz. The latter is harder to answer than the former. For the former, I propose a macro in psql, \qf (query function). Obviously, the name implies a broader scope than simply querying the datatypes permissable. I know this is just syntactical nit-picking, but I'm wondering where this functionality belongs. A few possibilities: * Part of \dT (describing data types) * Part of \df (for functions) * A new \ command * Non-empty subsets of the above * And, of course, the all-important Stuff I Haven't Thought Of. Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Just my $0.02 :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 Who is wise? He who learns from all. Ben Zoma, Pirkei Avot 4:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] connections problem
hi i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? We need more info. What does your postgresql.conf say about max_connections? How much ram do you have? How much shared memory have you allocated? Sincerely, Joshua D. Drake im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1) what can be wrong ? ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] connections problem
max_connections=200 shared_buffers=2000 ram = 500M + 300M swap hdd = infinite On Fri, 26 Dec 2003, Joshua D. Drake wrote: hi i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? We need more info. What does your postgresql.conf say about max_connections? How much ram do you have? How much shared memory have you allocated? Sincerely, Joshua D. Drake im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1) what can be wrong ? ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] connections problem
ivan [EMAIL PROTECTED] writes: i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? First bet is that you are running into a maximum-number-of-processes- per-user limit imposed by ulimit -u. It's also possible that you need more RAM or swap space. 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: [HACKERS] connections problem
Hello, Perhaps you have too many open files? What else is running on this machine? Sincerely, Joshua D. Drake ivan wrote: max_connections=200 shared_buffers=2000 ram = 500M + 300M swap hdd = infinite On Fri, 26 Dec 2003, Joshua D. Drake wrote: hi i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? We need more info. What does your postgresql.conf say about max_connections? How much ram do you have? How much shared memory have you allocated? Sincerely, Joshua D. Drake im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1) what can be wrong ? ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] DBT-2 PostgreSQL on STP w/ LVM2
DBT-2 and PostgreSQL 7.4 is finally working with LVM2 at 100 and 200 warehouse scale factors on 8-processor system in STP with 40 drives (38 managed under LVM), but not without a few catches. I have written up some brief instructions on how to execute the test successfully with the Linux-2.6.0 stable release: http://developer.osdl.org/markw/stp_dbt2_howto.html The instructions also cover how to get oprofile annoted assembly source in the test results. Feel free to ask any questions. -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] fulltext searching via a custom index type
(I started with this addressed to the -general list, but it just
doesn't seem like a general usage topic. If -hackers is the wrong
place, please point me in the right direction).
I've been working on a custom index type (access method) that allows
postgres to do fulltext searching (including phrase and proximity) that
I believe is more flexible and natural than tsearch2 (no offense to
the tsearch2 guys... it's been a source of inspiration). I also plan
on providing term browsing and hit-position information.
I'm using Xapian (www.xapian.org) as the backend fulltext engine, and
I'm trying to learn more of the innards of postgres as I go. I've only
spent about 18-24 hours on this, so it's nowhere near complete, and I
really hesitate to even mention it in a public forum. But if in the
end it doesn't suck it might make a great contrib/ package. *shrug*
who knows?
In a nutshell, I've implemented all the necessary access method
functions to teach postgres about a new index type named xapian, and
so far, everything is really great. It works just like any other index
type would:
create table test (stuff varchar(255), more_stuff text);
create index idxstuff on test using xapian (stuff);
create index idxmore_stuff on test using xapian (more_stuff);
insert into test (stuff, more_stuff) values ('this is stuff', 'this is
more stuff');
insert into test (stuff, more_stuff) values ('my name is eric ridge',
'i like to drink beer');
select * from test where stuff = 'stuff' or more_stuff = 'drink
beer';
stuff | more_stuff
---+--
this is stuff | this is more stuff
my name is eric ridge | i like to drink beer
(2 rows)
All this aside, I've got some questions related to indexes and query
plans.
1) Is it possible for an access method to receive some kind of DROP
INDEX notification? As far as I can tell, the answer is no, but it
can't hurt to ask.
2) When does the query planner decide that it needs to Filter
results, and is there any way to turn this off or otherwise fool the
query planner into NOT doing Filters (even if only for certain
operators)?
For example, this query:
select * from test where stuff = 'stuff' AND NOT more_stuff =
'drink beer';
has this plan:
Index Scan using idxstuff on test (cost=0.00..-0.98 rows=250
width=177)
Index Cond: ((stuff)::text = 'stuff'::text)
Filter: (NOT (more_stuff = 'drink beer'::text))
In this case, postgres is forced to re-parse the contents of the
more_stuff field (via the defined procedure for the = operator) for
every row returned by the previous index scan, just so it can determine
if the field contains the phrase 'drink beer' or not. Since so much
overhead is involved here, it would be cool if postgres could somehow
do another index scan.
Maybe there's some way for the operator function to know not only the
Datum value, but the actual field (and ItemPointer)?
3) How does one get the $PGDATA directory? getenv() doesn't seem ideal
since PGDATA can be specified as a command-line argument.
4) Can a function be registered as part of a transaction, pre-commit --
so the function can have an opportunity to abort the transaction. I've
seen RegisterEOXactCallback, but it's not quite what I'm looking for.
5) are there discussions in the archives of this list (or other pgsql-
lists) that discuss fulltext searching that y'all think are worth
reading?
thanks in advance for your time and input!
eric
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Docu question
In manage-ag.sgml our docs says: [...] risk. To allow it, you must compile productnamePostgreSQL/ with the C preprocessor macro literalALLOW_ABSOLUTE_DBPATHS/ defined. One way to do this is to run the compilation step like this: programlisting gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all /programlisting [...] I wonder if there's a reason to suggest this gmake call. After all it makes gmake disregard the CPPFLAGS settings in the Makefile. This for instance breaks ecpg compilation on Linux as -D_GNU_SOURCE as defined in the Makefiles is needed for LONG_LONG_MIN. I think we should either change the docs or start using override in the Makefiles. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] connections problem
.. many others stuf ... the error is : could not fork new process for connection: Resource temporarily unavailable On Fri, 26 Dec 2003, Joshua D. Drake wrote: Hello, Perhaps you have too many open files? What else is running on this machine? Sincerely, Joshua D. Drake ivan wrote: max_connections=200 shared_buffers=2000 ram = 500M + 300M swap hdd = infinite On Fri, 26 Dec 2003, Joshua D. Drake wrote: hi i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? We need more info. What does your postgresql.conf say about max_connections? How much ram do you have? How much shared memory have you allocated? Sincerely, Joshua D. Drake im using debain with kernel 2.4.23-pre7, on P4 , (postgres 7.4.1) what can be wrong ? ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.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: [HACKERS] connections problem
ulimit -u = 256 , it's ok, ? On Fri, 26 Dec 2003, Tom Lane wrote: ivan [EMAIL PROTECTED] writes: i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? First bet is that you are running into a maximum-number-of-processes- per-user limit imposed by ulimit -u. It's also possible that you need more RAM or swap space. 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] fulltext searching via a custom index type
Eric Ridge [EMAIL PROTECTED] writes: 1) Is it possible for an access method to receive some kind of DROP INDEX notification? No. select * from test where stuff = 'stuff' AND NOT more_stuff = 'drink beer'; has this plan: To do better here, you'd need to invent a not-=' operator, so that the above could be simplified to, say, select * from test where stuff = 'stuff' AND more_stuff ~= 'drink beer'; and then define both = and ~= as members of your index opclass, and then build a two-column index on (stuff, more_stuff) ... whereupon the planner would pass your index AM both clauses and it would be up to you to do something intelligent. You might want to back off a little on how much of this you really want to do in a first cut. 3) How does one get the $PGDATA directory? DataDir. Why should you care? An index AM that wants to know this is probably broken IMHO; it's certainly trying to do something that's outside the charter of index AMs, and is likely to cause lots of headaches. 4) Can a function be registered as part of a transaction, pre-commit -- so the function can have an opportunity to abort the transaction. Why would that be a good idea? When exactly would you expect it to be called (relative to the other ninety-nine things that happen at commit)? How are you going to recover if something else aborts the transaction, either before or after your function runs? I get the impression from your questions that you are trying to make an end run around the transaction mechanism. This is almost certainly doomed to failure. You need to find a way of storing all your data within the ordinary index structure. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] connections problem
ivan [EMAIL PROTECTED] writes: i need to connect to by my database more then 100 connections, but after ~20-30 conn, postmaster says me Resource temporarily unavailable, what are this resource ? ulimit -u = 256 , it's ok, ? Mph. Well, that doesn't seem to be the problem, unless you are starting a whole lot of other processes besides the backends under the same UID. (However: are you sure the postmaster has been started with this same ulimit setting?) It sounds like some system call is failing with EAGAIN, but it's not real clear which one. If you're running 7.4, let's see the verbose form of the error message (see log_error_verbosity, or set psql's VERBOSITY variable). If not, updating to 7.4 might be the easiest way to get more info. 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: [HACKERS] connections problem
ivan [EMAIL PROTECTED] writes: the error is : could not fork new process for connection: Resource temporarily unavailable We could have saved some time if you'd spelled out the full error message to begin with. This is undoubtedly a kernel-imposed restriction you are running into. I'm not sure if the failure is related to sheer number of processes, number of open files, or RAM/swap space, but one way or another you are trying to create more processes than your kernel will support. It seems moderately unlikely that twenty or thirty Postgres processes would by themselves be able to run the kernel out of resources (although if the problem is related to number of open files, reducing max_files_per_process might help). What else do you have running on this machine? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] fulltext searching via a custom index type
On Dec 26, 2003, at 3:22 PM, Tom Lane wrote: 3) How does one get the $PGDATA directory? DataDir. Why should you care? An index AM that wants to know this is probably broken IMHO; it's certainly trying to do something that's outside the charter of index AMs, and is likely to cause lots of headaches. Xapian has it's own storage subsystem, and that's what I'm using to store the index... not using anything internal to postgres (although this could change). As such, Xapian needs to know *where* to save its indexes... $PGDATA seemed like a good place to start. 4) Can a function be registered as part of a transaction, pre-commit -- so the function can have an opportunity to abort the transaction. Why would that be a good idea? When exactly would you expect it to be called (relative to the other ninety-nine things that happen at commit)? How are you going to recover if something else aborts the transaction, either before or after your function runs? I don't really have an answer to this. :) I get the impression from your questions that you are trying to make an end run around the transaction mechanism. Perhaps. I'm actually fishing for ideas to bridge xapian's transaction facilities to postgres. Your comment confirms my suspicions that it just ain't gunna work out. This is almost certainly doomed to failure. You need to find a way of storing all your data within the ordinary index structure. You are probably right. :) I'm just playing around right now. I do appreciate your response, it's given me a lot to think about. eric ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] fulltext searching via a custom index type
Eric Ridge [EMAIL PROTECTED] writes: Xapian has it's own storage subsystem, and that's what I'm using to store the index... not using anything internal to postgres (although this could change). I would say you have absolutely zero chance of making it work that way. You will not be able to get it to interoperate reliably with transactions, checkpointing, or WAL replay; to say nothing of features we might add in the future, such as tablespaces and point-in-time recovery. You need to migrate all the data into the Postgres storage mechanism. It might be worth pointing out here than an index AM is not bound to use exactly the typical Postgres page layout. I think you probably do have to use the standard page header, but the page contents don't have to look like tuples if you don't want 'em to. For precedent see the hash index AM, which stores ordinary index tuples on some index pages but uses other pages for its own purposes. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL port to pure Java?
PostgreSQL runs beautifully together with JBoss (or any other J2EE platform) using its current JDBC driver so the idea of a co-bundle is not far fetched at all. I'm not an advocate for a backend rewrite to Java. I'm however a firm believer that Java and the J2EE platform will become (if it isn't already) the major development platform in the Open Source community. I think it's highly probable that PostgreSQL and Java will touch base in several areas in the future. I hope for a great future for PostgreSQL and I think that as it envolves it will support concepts that might be increasingly hard to write and maintain using plain C. A language with built-in support for OO semantics can be of great help. IMO it's better to keep an open mind regarding the future and the potential languages that might be used than to make remarks like the one Mr. Poure just made. Thomas Hallgren Jean-Michel POURE [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Le Mardi 09 Décembre 2003 16:15, Ivelin Ivanov a écrit : I think that a co-bundle between an open source J2EE container like JBoss and a scalable database like PostgreSQL will be a blast. Why not cut all trees on earth and replace them with plastic? Before that, we need to port mankind DNA to Windows 3.1 in order to improve speed. ---(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 8: explain analyze is your friend
Re: [HACKERS] fulltext searching via a custom index type
On Dec 26, 2003, at 4:04 PM, Tom Lane wrote: Eric Ridge [EMAIL PROTECTED] writes: Xapian has it's own storage subsystem, and that's what I'm using to store the index... not using anything internal to postgres (although this could change). I would say you have absolutely zero chance of making it work that way. thanks for the encouragement! :) You will not be able to get it to interoperate reliably with transactions, checkpointing, or WAL replay; to say nothing of features we might add in the future, such as tablespaces and point-in-time recovery. You need to migrate all the data into the Postgres storage mechanism. And these are the things I'm struggling with now. The basic indexing and searching currently works flawlessly, but the moment another user connects up, everything goes to hell. It might be worth pointing out here than an index AM is not bound to use exactly the typical Postgres page layout. I think you probably do have to use the standard page header, but the page contents don't have to look like tuples if you don't want 'em to. For precedent see the hash index AM, which stores ordinary index tuples on some index pages but uses other pages for its own purposes. That's useful information. Thanks. I've been using the hash AM as my guide b/c it's not as complex as the other index types (atleast on the public interface side). Obviously, I'm trying to save the time and energy of re-inventing the wheel when it comes full text indexing and searching. Xapian is an awesome standalone engine (and it's amazingly fast too!), so it seemed like a good place to start. It's backend storage subsystem is pluggable, and after our little exchange here today, I'm now considering writing a postgres backend for Xapian. I assume the doc chapter on Page Files and the various storage-related README files are good places for more information. Any other tips or pointers? eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] connections problem
from log : LOG: could not fork new process for connection: Resource temporarily unavailable LOG: could not fork checkpoint process: Resource temporarily unavailable ulimit -a : core file size(blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files(-n) 2048 pipe size (512 bytes, -p) 8 stack size(kbytes, -s) 15000 cpu time (seconds, -t) unlimited max user processes(-u) 400 virtual memory(kbytes, -v) unlimited On Fri, 26 Dec 2003, Tom Lane wrote: ivan [EMAIL PROTECTED] writes: the error is : could not fork new process for connection: Resource temporarily unavailable We could have saved some time if you'd spelled out the full error message to begin with. This is undoubtedly a kernel-imposed restriction you are running into. I'm not sure if the failure is related to sheer number of processes, number of open files, or RAM/swap space, but one way or another you are trying to create more processes than your kernel will support. It seems moderately unlikely that twenty or thirty Postgres processes would by themselves be able to run the kernel out of resources (although if the problem is related to number of open files, reducing max_files_per_process might help). What else do you have running on this machine? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] fulltext searching via a custom index type
Eric Ridge [EMAIL PROTECTED] writes: I assume the doc chapter on Page Files and the various storage-related README files are good places for more information. Any other tips or pointers? Not right offhand, but feel free to ask questions when you get stuck. Also don't forget that there's a wealth of info in source code comments; you should always try looking for existing code that does something similar to what you want to do. BTW, one problem with using the hash AM as a model is that it's not yet WAL-aware. The btree AM is the only one that's really WAL-ified yet, so you need to look at that if you want to think now about how to make your code safe for WAL. I think you'd probably be okay to postpone this consideration for later, but keep in mind that all your operations that change the contents of index files should be divisible into bite-size operations that can be logged as WAL entries. Each bite-size operation has to leave the index in a legal state, too, so there's a limit as to how small you can subdivide. 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
[HACKERS] REPOST from SQL List: Use of Setof Record Dynamically
Hi, I am attempting to use Setof Record dynamically. I am developing an application where I will not know what the end-user is trying to work with from a web front end. Thus I will not know before hand the structure of the table I am trying to pull the record from. My Code: CREATE FUNCTION retrievecollection(varchar, varchar, varchar) RETURNS record AS'Declaretablealias ALIAS FOR $1;crmid ALIAS FOR $2;username ALIAs FOR $3;allowed integer;objectdefinition record;realtablename char; beginselect into allowed secverf(username, tablealias);if allowed = 0 thenRAISE NOTICE ''User not authorized to perform retrieve.'', allowed;RETURN false;elseselect into realtablename tablename from applicationaliases where tablealias = qoute_literal(tablealias);if length(crmid) = 0 thenFOR objectdefinition IN SELECT * from qoute_ident(realtablename) LOOPRETURN NEXT objectdefinition;END LOOP;elseFOR objectdefinition IN SELECT * from qoute_ident(realtablename) where crmid = qoute_literal(crmid) LOOPRETURN NEXT objectdefinition;END LOOP;end if;end if;RETURN record;end;'LANGUAGE 'plpgsql' VOLATILE; I know that I would execute the procedure with select * from retrievecollection as x(x typex, y typey) but how do I do this if I don't know which table was passed to the function? TIA Alex Erwin
Re: [HACKERS] feature request: \qf datatype
On Fri, Dec 26, 2003 at 11:49:20AM -0600, David Fetter wrote: The latter is harder to answer than the former. For the former, I propose a macro in psql, \qf (query function). Obviously, the name implies a broader scope than simply querying the datatypes permissable. * Part of \df (for functions) This is my initial feeling. It really is just another \df. However, I don't see a good way to merge the functionality of the new function and the old function, as the parameter for df is the function name, not its arguments. * Part of \dT (describing data types) This, too would work, but again, I have a hard time figuring out where to put the arguments. * A new \ command The problem with this is that few people are going to notice it immediately whereas I would contend that many people already know aobut \df and \dT. The goal here is to get people to use the tools they have. If they're not already seeking out tools, it doesn't help to add new ones. Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Duplication of help systems that are never going to be used is a waste of everyone's time. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator Shut down some of the bullshit the government is spending money on and use it to buy all the Microsoft stock. If we did that, we could ... just bronze Gates, turn him into a statue, and stick him in front of the Commerce Department. - Scott McNealy ---(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: [HACKERS] feature request: \qf datatype
On Fri, Dec 26, 2003 at 06:34:47PM -0500, Alex J. Avriette wrote: On Fri, Dec 26, 2003 at 11:49:20AM -0600, David Fetter wrote: The latter is harder to answer than the former. For the former, I propose a macro in psql, \qf (query function). Obviously, the name implies a broader scope than simply querying the datatypes permissable. * Part of \df (for functions) This is my initial feeling. It really is just another \df. However, I don't see a good way to merge the functionality of the new function and the old function, as the parameter for df is the function name, not its arguments. True. * Part of \dT (describing data types) This, too would work, but again, I have a hard time figuring out where to put the arguments. Weelll, there's already a + operator, as in \df+. Perhaps there could be a T operator for data types and an f operator for functions, c. \dfT integer might bring back all the functions that operate on (or return) integers. * A new \ command The problem with this is that few people are going to notice it immediately whereas I would contend that many people already know aobut \df and \dT. The goal here is to get people to use the tools they have. If they're not already seeking out tools, it doesn't help to add new ones. Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Duplication of help systems that are never going to be used is a waste of everyone's time. Perhaps I didn't make clear what I was trying to say. :) We should continue to avoid the The docs for any given thing are findable in exactly one way. If you don't divine it, you are S.O.L. model of documentation. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 There is nothing more difficult to take in hand, more perilous to conduct, or more uncertain in its success, than to take the lead in the introduction of a new order of things. Because the innovator has for enemies all those who have done well under the old conditions, and lukewarm defenders in those who may do well under the new. Niccolo Machiavelli The Prince, 1513 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] REPOST from SQL List: Use of Setof Record Dynamically
A E wrote: I am attempting to use Setof Record dynamically. I am developing an application where I will not know what the end-user is trying to work with from a web front end. Thus I will not know before hand the structure of the table I am trying to pull the record from. [code] I know that I would execute the procedure with select * from retrievecollection as x(x typex, y typey) but how do I do this if I don't know which table was passed to the function? The short answer is there is no way to do what you seem to want to do, at least not directly. You might try storing a suitable column definition string (columndef) in the applicationaliases table for each referenced table. From your web page first do: SELECT columndef FROM applicationaliases WHERE tablealias = qoute_literal(tablealias); Alternatively you could dynamically build the columndef based on tablename. Then use the result to build the second query; something like: SELECT * FROM retrievecollection($tablealias, $crmid, $username) AS ($columndef); HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] feature request: \qf datatype
On Fri, Dec 26, 2003 at 04:07:50PM -0800, David Fetter wrote: \dfT integer might bring back all the functions that operate on (or return) integers. I like this the best so far. My only concern is that there is a whole lot of output generated by this (see thread parent). Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Duplication of help systems that are never going to be used is a waste of everyone's time. Perhaps I didn't make clear what I was trying to say. :) We should continue to avoid the The docs for any given thing are findable in exactly one way. If you don't divine it, you are S.O.L. model of documentation. Well, I'm perfectly happy to see it in psql. I'm just unable to actually do the C part myself. If somebody would step up to the plate, we could see this in the next release. I haven't heard anyone say they didn't like it. alex -- [EMAIL PROTECTED] Alex J. Avriette, Windows Systems Defenestrator Object-oriented programming is an exceptionally bad idea which could only have originated in California. - Edsger Dijkstra ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
