Re: [HACKERS] Re: bugs - lets call an exterminator!
On Thu, 23 Aug 2001, Colin 't Hart wrote: Vince asks: Everybody keeps saying bugzilla. What EXACTLY will bugzilla do for us that would make me want to learn it and install it? BTW, the current wheel was invented a year ago 'cuze nothing really fit what we needed. The reasons I would choose Bugzilla: 1. It's *not* written by us so (in theory) we don't have to waste time developing yet another bug tracking solution. What we have is already developed and refining it isn't a problem. 2. It sends email to people involved with a bug whenever the detail associated with that bug is modified. This includes the reporter, who often will feedback that it now works, at which time the fixer or the reporter can mark the bug as fixed. What we have already does this, but noone was using it. 3. It complains when a NEW bug hasn't been looked at for /n/ days -- this means that any not-a-bug's will be closed, while any that are really bugs will be accepted. This would piss off the developers. 4. Good query facilities, if a little complex to use. Please elaborate. 5. I think Bugzilla's concepts of products, components and versions fit the way we work. I envisage that 'Postgres', 'Interfaces', 'Languages' might be products that we would have. Within 'Postgres' we would have the various subsystems that make up the core. Within 'Interfaces' we would have 'JDBC', 'ODBC' etc. Within 'Languages' we would have 'PL/pgSQL' etc. I can see a little benefit to this, but for the most part the same people that are working on the core pieces of PostgreSQL are also working on the interfaces and languages. 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
Re: [HACKERS] Re: bugs - lets call an exterminator!
Vince Vielhaber [EMAIL PROTECTED] writes: On Thu, 23 Aug 2001, Colin 't Hart wrote: 5. I think Bugzilla's concepts of products, components and versions fit the way we work. I envisage that 'Postgres', 'Interfaces', 'Languages' might be products that we would have. Within 'Postgres' we would have the various subsystems that make up the core. Within 'Interfaces' we would have 'JDBC', 'ODBC' etc. Within 'Languages' we would have 'PL/pgSQL' etc. I can see a little benefit to this, but for the most part the same people that are working on the core pieces of PostgreSQL are also working on the interfaces and languages. I would argue against subdividing a bug database at all. I don't think the project is large enough to require it (we are in no danger of becoming the size of Mozilla anytime soon). But more importantly, subdivision introduces the risk of misclassification of a bug --- and in my experience the initial reporter of a bug *very* frequently misidentifies where the problem is. So unless additional effort is expended to reclassify bugs (is that even possible in Bugzilla?), the classification will degenerate to the point of being a hindrance rather than a help in locating things. Overall I just don't see that much benefit from a classification system. 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] Reverse Engineering
Hannu Krosing [EMAIL PROTECTED] writes: Peter Harvey wrote: 2. How do I determine the AccessMethod specified when an index was created? you can parse it from pg_indexes.indexdef ... which relies on pg_get_indexdef(index OID). Or, look at pg_class.relam, which is zero for regular tables and a pg_am OID for indexes. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A couple items on TODO
I also noticed that this item has been there for a while: *Encrpyt passwords in pg_shadow table using MD5 (Bruce, Vince) While you are there do you think it's possible to make an mcrypt function? :) See contrib/pgcrypto. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A couple items on TODO
As I was browsing TODO, I noticed a couple unassigned items that I may be able to help with (I haven't worked with the source before): *Add use of 'const' for variables in source tree I would discuss this item with the hackers list and see exactly what people want done with it. *Convert remaining fprintf(stderr,...)/perror() to elog() The issue here is that some calls can't use elog() because the context is not properly set up yet so we need to identify the non-elog error calls and figure out if they should be elog(). Neither seemed to be active at all. I also noticed that this item has been there for a while: *Encrpyt passwords in pg_shadow table using MD5 (Bruce, Vince) This is done. I forgot to mark it. I just marked it now. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CURRENT OF cursor without OIDs
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: Hiroshi wrote: In addtion, xmin wouldn't be so reliable in the near future because it would be updated to FrozenXID (=2) by vacuum. I thought concurrent vacuum with an open cursor is not at all possible. If it were, it would not be allowed to change ctid (location of row) and could be made to not change xmin. New-style vacuum can certainly run concurrently with an open cursor (wouldn't be of much use if it couldn't). However, new-style vacuum never changes ctid, period. It could change the xmin of a tuple though, under my not-yet-implemented proposal for freezing tuples. AFAICS, if you are holding an open SQL cursor, it is sufficient to check that ctid hasn't changed to know that you have the same, un-updated tuple. Under MVCC rules, VACUUM will be unable to delete any tuple that is visible to your open transaction, and so new-style VACUUM cannot recycle the ctid. Old-style VACUUM might move the tuple and make the ctid available for reuse, but your open cursor will prevent old-style VACUUM from running on that table. So, there's no need to look at xmin. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] OLAP, Aggregates, and order of operations
I need to do some OLAP stuff, and I asked previously if there were a way to pass multiple parameters to an aggrigate function. i.e.: select mycube(value1, value2, value3) from table group by value1; I looked through the code and it is non-trivial to do, one would have to alter the grammar to include a number of parameters, I guess something like this: create aggregate (sfunc = myfunct, sfuncnargs=3, stype = int4, basetype1 = int4, basetype2 = int4, ); Then change the catalog, and the execution, arrg! (God I wish I could spend the time I want on PostgreSQL! ) Anyway, short of that If I do this: select mycube(value1) as d1, dimention(value2) as d2, dimention(value3) as d3 group by value1; Can I safely assume the following: (1) mycube() will be called first (2) Assuming dimention() has no final func, that final func of mycube() will be called last. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: [PATCHES] encoding names
Tatsuo Ishii writes: But getdbencoding isn't semantically different from the old getdatabaseencoding. encoding isn't the right term anyway, methinks, it should be character set. So maybe database_character_set()? (No get please.) I'm not a native English speaker, so please feel free to choose more appropriate name. BTW, what's wrong with encoding? I don't think, for example EUC-JP or utf-8, are character set names. Hmm, SQL talks of character sets, it has a CHARACTER_SETS view and such. It's slightly incorrect, I agree. Maybe we should not touch getdatabaseencoding() right now, given that the names we currently use are apparently almost correct anyway and considering the pain it creates to alter them, and instead implement the information schema views in the future? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] A couple items on TODO
Peter Eisentraut [EMAIL PROTECTED] writes: Jeff Davis writes: *Convert remaining fprintf(stderr,...)/perror() to elog() This isn't quite as easy as a mechanical conversion, mind you, because elog of course has rather complex side effects besides printing out a message. AFAIR, elog at NOTICE or DEBUG level isn't really supposed to have any side-effects. The bigger issue is that you have to be careful about using it in certain places, mainly during startup or for reporting communication errors. (send failure - elog - tries to send message to client - send failure - elog - trouble) Also, I believe most of the printf's in the backend are in debugging support code that's not even compiled by default. The return on investment from converting those routines to use elog is really nil. There may be a few remaining printf calls that should be converted to elog, but I don't think this is a big issue. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OLAP, Aggregates, and order of operations
mlw [EMAIL PROTECTED] writes: I need to do some OLAP stuff, and I asked previously if there were a way to pass multiple parameters to an aggrigate function. i.e.: I looked through the code and it is non-trivial to do, Offhand I don't know of any fundamental reason why it couldn't be done, but you're right that it'd take a fair amount of work. If I do this: select mycube(value1) as d1, dimention(value2) as d2, dimention(value3) as d3 group by value1; Can I safely assume the following: (1) mycube() will be called first (2) Assuming dimention() has no final func, that final func of mycube() will be called last. That might be true in the present code, but it strikes me as an awfully risky set of assumptions. Also, it sounds like what you have in mind is to have some hidden state that all the aggregate functions will access; how then will you work if there are more than one set of these aggregates being used in a query? If the needed parameters are all the same datatype, maybe you could put them into an array and pass the array as a single argument to the aggregate. 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] A couple items on TODO
AFAIR, elog at NOTICE or DEBUG level isn't really supposed to have any side-effects. The bigger issue is that you have to be careful about using it in certain places, mainly during startup or for reporting communication errors. (send failure - elog - tries to send message to client - send failure - elog - trouble) Also, I believe most of the printf's in the backend are in debugging support code that's not even compiled by default. The return on investment from converting those routines to use elog is really nil. There may be a few remaining printf calls that should be converted to elog, but I don't think this is a big issue. Clearly not a big issue, but something someone can poke around at to get started. -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Reverse Engineering
Peter Harvey wrote: Great progress today on my Reverse Engineering efforts. However; I have some comments. 1. How can I switch databases (where I would normally use USE)? You just open another connection . If you mean psql jou do \c otherdatabasename 2. How do I determine the AccessMethod specified when an index was created? you can parse it from pg_indexes.indexdef A great source for reverse engineering is source of pg_dump as it has to do all the reverse engineering in order to dump everything. 3. It would be cool if the catalog objects had comments on them in pg_description. Very few do. Yes it would :) - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: bugs - lets call an exterminator!
Vince asks: Everybody keeps saying bugzilla. What EXACTLY will bugzilla do for us that would make me want to learn it and install it? BTW, the current wheel was invented a year ago 'cuze nothing really fit what we needed. The reasons I would choose Bugzilla: 1. It's *not* written by us so (in theory) we don't have to waste time developing yet another bug tracking solution. 2. It sends email to people involved with a bug whenever the detail associated with that bug is modified. This includes the reporter, who often will feedback that it now works, at which time the fixer or the reporter can mark the bug as fixed. 3. It complains when a NEW bug hasn't been looked at for /n/ days -- this means that any not-a-bug's will be closed, while any that are really bugs will be accepted. 4. Good query facilities, if a little complex to use. 5. I think Bugzilla's concepts of products, components and versions fit the way we work. I envisage that 'Postgres', 'Interfaces', 'Languages' might be products that we would have. Within 'Postgres' we would have the various subsystems that make up the core. Within 'Interfaces' we would have 'JDBC', 'ODBC' etc. Within 'Languages' we would have 'PL/pgSQL' etc. Arguments accepted. There are other tools the Mozilla project uses that we could also use: Tinderbox -- continuous automated builds, including subsequent regression tests (useful for seeing who broke CVS). Bonsai -- CVS integration for Bugzilla Cheers, Colin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [HACKERS] CURRENT OF cursor without OIDs
Hiroshi wrote: There could be DELETE operations for the tuple from other backends also and the TID may disappear. Because FULL VACUUM couldn't run while the cursor is open, it could neither move nor remove the tuple but I'm not sure if the new VACUUM could remove the deleted tuple and other backends could re-use the space under such a situation. If you also save the tuple transaction info (xmin ?) during the select in addition to xtid, you could see whether the tupleslot was reused ? I think TID itself is available for the purpose as long as PostgreSQL uses no overwrite storage manager. If the tuple for a saved TID isn't found, the tuple may be update/deleted. If the tuple is found but the OID is different from the saved one, the space may be re-used. space *was* reused (not "may be") But I meant in lack of an OID (per not mandatory oid), that xmin might be a valid replacement for detecting, no ? Does *current (ctid, xmin) == saved (ctid, xmin)* mean that they are same ? Yes? but better ask Vadim ? Wraparound issue would be solved by FrozenXID and frequent vacuum. In addtion, xmin wouldn't be so reliable in the near future because it would be updated to FrozenXID (=2) by vacuum. I thought concurrent vacuum with an open cursor is not at all possible. If it were, it would not be allowed to change ctid (location of row) and could be made to not change xmin. If we switch to an overwriting smgr we have no item to detect the change of tuples. It may be one of the critical reasons why we shouldn't switch to an overwriting smgr:-). If we still want MVCC, we would still need something like xmin for overwrite smgr (to mark visibility). Andreas ---(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] Locale by default?
I don't understand why you object the idea giving PostgreSQL the ability to turn off the locale support in configuration/compile time. In that way, there's no inconveniences for many users. I don't mind at all the ability to turn it off. My point is that the compile time is the wrong time to do it. Many users use binary packages these days, many more users would like to use binary packages. But the creators of these packages have to make configuration choices to satisfy all of their users. So they turn on the locale support, because that way if you don't want it you can turn if off. The other way around doesn't work. Yup, imho we all understood that and the only (to be validated) concern is performance. The more appropriate way to handle this situation is to make it a runtime option. I agree that the LC_ALL/LC_COLLATE/LANG lattice is confusing and fragile. But there can be other ways, e.g., Yes, that was the (or at least my) main concern. initdb --locale=en_US initdb --locale-collate=C --locale-ctype=en_US initdb # defaults to --locale=C or in postgresql.conf locale=C locale_numeric=en_US etc. or SHOW locale; SHOW locale_numeric; That way you always know exactly what situation you're in. I think this was Hiroshi's main concern, the reliance on export LC_ALL, and I agree that this is bad. You say locale in Japan works, except for LC_COLLATE. This concern would be satisfied by the above approach. Comments? I think that's it :-) Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Toast, Text, blob bytea Huh?
I'm trying my best to convert from MySQL to PgSQL but I cant get a good clear answer about certian issures.Mainly TEXT, TOAST,BLOB , BYTEA etc. It was an easy task in mysql but everything in the archives about , text , toast and bytea is just confusing me with postgresql. I have Bruces's book and I've searched the archives years back with all the right keywords with not luck.Here is my situation-- WHAT I WAS DOING IN MYSQL Via the web my clients are uploading basic text/data files, sometimes than 30MB. In the past ,via CGI I have been parsing the file into one STL string, using mysql_escape_string to escape it and then using an INSERT to place the ,\'+stlstring+\' ,into a BLOB column. dont want to use a temp. file or files in general anywhere. The data will always be passed via the database and buffers for certian reasons.Thus no OID's THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL 1. I cant get a clear answer on what kind of data type to use for my large text string? TEXT, ???, ??? or something about TOAST I have seen in the e-mail archive but cant find any documentaion? 2. I've written my own escape method ,(cant find one for Pgsql) , BUT i don't know what to escape and not to escape. So it keeps failing. I cand find any docs. on what to escape either? SUMMARY What is the best datatype to use, for large raw text and/or binary if i choose? Once I know this,how Im a supposed to escape my string and get it through the parser correctly so i can retrieve it correctly? Thanks for your time. PS: Using RedHat. Jason H. Ory Medprint+ Software Developer [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: Escaping strings for inclusion into SQL queries
On Wed, Aug 22, 2001 at 05:16:44PM +, Florian Weimer wrote: We therefore suggest that a string escaping function is included in a future version of PostgreSQL and libpq. A sample implementation is provided below, along with documentation. I use Perl, which (through DBD::Pg) has a quote function available, but I think this is a very good idea to include in the library. I only have one issue - the SQL standard seems to support the use of '' to escape a single quote, but not \'. Though PostgreSQL has an extended notion of character string literals, I think that the usual policy of using the standard interface when possible should apply. -- Christopher Masto Senior Network Monkey NetMonger Communications [EMAIL PROTECTED][EMAIL PROTECTED]http://www.netmonger.net Free yourself, free your machine, free the daemon -- http://www.freebsd.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: Escaping strings for inclusion into SQL queries
Christopher Masto [EMAIL PROTECTED] writes: I only have one issue - the SQL standard seems to support the use of '' to escape a single quote, but not \'. Though PostgreSQL has an extended notion of character string literals, I think that the usual policy of using the standard interface when possible should apply. The first version escaped ' with ''. I changed it when I noticed that if \' is used instead, the same function can be used for strings ('...') and identifiers (...). In addition, you have to replace \ with \\, so you are forced to leave the grounds of the standard anyway. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Reverse Engineering
There seem to be several ways to get at just about anything in the Catalog Tables. The ODBC driver, psql, and pg_dump typically use slightly diff sql and you guys have suggested even better ways. Forgive me as I ask for more. How do I determine the foriegn keys in a table? I see pg_class.relfkeys and pg_class.relrefs. I am not sure what the diff is between the two. In anycase; where can I go to find the table/column(s) for each fk? Having this info will allow me to accurately connect the tables in the reverse engineered ERD. Very cool. Peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] [PATCH] Win32 errno a little bit safer
Hello, i just reviewed the win32 errno patch and i saw that maybe i didn't really played it totally safe in my last suggestion, the system table might pick up the msg but not the netmsg.dll, so better try both. I also added a hex printout of the errno appended to all messages, that's nicer. If anyone hate my coding style, or that i'm using goto constructs, just tell me, and i'll rework it into a nested if () thing. Patch attached. Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Index: fe-misc.c === RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/libpq/fe-misc.c,v retrieving revision 1.54 diff -u -r1.54 fe-misc.c --- fe-misc.c 2001/08/21 20:39:54 1.54 +++ fe-misc.c 2001/08/23 21:12:07 @@ -855,23 +855,46 @@ #ifdef WIN32 /* * strerror replacement for windows: + * + * We dont't know a fix for win9x yet, but this whould work for nt4 and win2k. + * If you can verify this working on win9x or have a solution, let us know, ok? + * */ const char* winsock_strerror(DWORD eno) { - if (!FormatMessage( - FORMAT_MESSAGE_IGNORE_INSERTS | -FORMAT_MESSAGE_FROM_SYSTEM | /* always consider system table */ -((netmsgModule != NULL) ? FORMAT_MESSAGE_FROM_HMODULE : 0), -netmsgModule, /* module to get message from (NULL == system) */ - eno, -MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), -winsock_strerror_buf,sizeof(winsock_strerror_buf)-1, -NULL - )){ - sprintf(winsock_strerror_buf,Unknown socket error(%u),eno); -} -winsock_strerror_buf[sizeof(winsock_strerror_buf)-1]='\0'; -return winsock_strerror_buf; + #define WSSE_MAXLEN (sizeof(winsock_strerror_buf)-1-12) /* 12 == (0x) */ + int length; + + /* First try the system table, this works on Win2k pro */ + + if (FormatMessage( + FORMAT_MESSAGE_IGNORE_INSERTS|FORMAT_MESSAGE_FROM_SYSTEM, + 0,eno,MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), + winsock_strerror_buf,WSSE_MAXLEN,NULL +)) +goto WSSE_GOODEXIT; + + /* That didn't work, let's try the netmsg.dll */ + + if (netmsgModule + FormatMessage( + FORMAT_MESSAGE_IGNORE_INSERTS|FORMAT_MESSAGE_FROM_HMODULE, + 0,eno,MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT), + winsock_strerror_buf,WSSE_MAXLEN,NULL + )) +goto WSSE_GOODEXIT; + + /* Everything failed, just tell the user that we don't know the desc */ + + strcat(winsock_strerror_buf,Socket error, no description available.); + +WSSE_GOODEXIT: + + length = strlen(winsock_strerror_buf); + sprintf(winsock_strerror_buf + lengthWSSE_MAXLEN?length:WSSE_MAXLEN, + (0x%08X),eno); + + return winsock_strerror_buf; } #endif ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] User locks code
Well, ability to lock only unlocked rows in select for update is useful, of course. But uniq features of user'locks are: 1. They don't interfere with normal locks hold by session/transaction. 2. Share lock is available. 3. User can lock *and unlock objects* inside transaction, which is not (and will not be) available with locks held by transactions. They are interesting too and proposed implementation will not impact lock manager (just additional 4 bytes in LOCKTAG = same size of LOCKTAG on machines with 8 bytes alignment). An interesting method would be to allow users to simply avoid locked rows: SELECT * FROM queue FOR UPDATE LIMIT 1 UNLOCKED; Unlocked, return immediately, whatever could be used as a keyword to avoid rows that are locked (skipping over them). For update locks the row of course. Currently for the above type of thing I issue an ORDER BY random() which avoids common rows enough, the queue agent dies if queries start taking too long (showing it's waiting for other things) and tosses up new copies if it goes a while without waiting at all (showing increased load). -- Rod Taylor This message represents the official view of the voices in my head - Original Message - From: Mikheev, Vadim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 17, 2001 2:48 PM Subject: [HACKERS] User locks code 1. Just noted this in contrib/userlock/README.user_locks: User locks, by Massimo Dal Zotto [EMAIL PROTECTED] Copyright (C) 1999, Massimo Dal Zotto [EMAIL PROTECTED] This software is distributed under the GNU General Public License either version 2, or (at your option) any later version. Well, anyone can put code into contrib with whatever license he/she want but user locks package includes interface functions in contrib *and* changes in our lock manager, ie changes in backend code. I wonder if backend' part of package is covered by the same license above? And is it good if yes? 2. Not good implementation, imho. It's too complex (separate lock method table, etc). Much cleaner would be implement this feature the same way as transactions wait other transaction commit/abort: by locking objects in pseudo table. We could get rid of offnum and lockmethod from LOCKTAG and add struct Oid RelId; Oid ObjId; } userObjId; to objId union of LOCKTAG. This way user could lock whatever object he/she want in specified table and note that we would be able to use table access rights to control if user allowed to lock objects in table - missed in 1. One could object that 1. is good because user locks never wait. I argue that never waiting for lock is same bad as always waiting. Someday we'll have time-wait etc features for general lock method and everybody will be happy -:) Comments? Vadim P.S. I could add 2. very fast, no matter if we'll keep 1. or not. 4. Most important: user locks are retained across transaction, which is not possible with ordinary locks. 5. User locks semantic is defined entirely by the application and is not related to rows in the database. I wrote the user locks code because I needed a method to mark items as `busy' for very long time to avoid more users modifying the same object and overwriting each one's changes. This requires two features: 1. they must survive transaction boundary. The typical use of user locks is: transaction 1: select object,user_lock(object); ... work on object for long time transaction 2: update object,user_unlock(object); 2. they must not block if the object is already locked, so that the program doesn't freeze and the user simply knows it can't use that object. When I wrote the code the only way to do this was to add a separate lock table and use the same machinery of ordinary locks. I agree that the code is complex and should probably be rewritten. If you think there is a better way to implement this feature go ahead, better code is always welcome. The only problem I have found with user locks is that if a backend crashes without releasing a lock there is no way to relase it except restarting the whole postgres (I don't remember exactly why, I forgot the details). Regarding the licencing of the code, I always release my code under GPL, which is the licence I prefer, but my code in the backend is obviously released under the original postgres licence. Since the module is loaded dynamically and not linked into the backend I don't see a problem here. If the licence becomes a problem I can easily change it, but I prefer the GPL if possible. -- Massimo Dal Zotto +--+ | Massimo Dal Zotto email: [EMAIL PROTECTED] | | Via Marconi, 141phone:
[HACKERS] Assessment on namespace clean include file names
Here is what we install by default and what we could do about it: c.h [1] config.hrename to pg_config.h ecpgerrno.h ok ecpglib.h ok ecpgtype.h ok iodbc/ [3] iodbc.h isql.h isqlext.h lib/[1] dllist.h libpgeasy.h ok libpgtcl.h ok libpq/ [1] libpq-fs.h pqcomm.h libpq++/ok pgconnection.h pgcursordb.h pgdatabase.h pglobject.h pgtransdb.h libpq++.h ok libpq-fe.h ok libpq-int.h [1] os.hrename to pg_config_os.h postgres_ext.h ok postgres_fe.h [1] pqexpbuffer.h [1] sql3types.h [2] sqlca.h [2] [1] -- The libpq-int.h draws in a lot of internal structure, true to the name. Something should be done about that, such as not installing it, or moving it to a hidden place. Ideas? [2] -- The ecpg preprocessor will actually include copies of these into the output file when seeing the commands 'exec sql include sql3types;' etc., thus not really making these include files in the traditional sense. The names are okay for the moment, but I will research this some more. [3] -- The names clash with the actual iodbc package. Not sure if this is intended, but I will evaluate with the odbc group. The idea I currently have for the installation layout including the server includes is this: --prefix=/usr/local/pgsql libpq-fe.h = /usr/local/pgsql/include/libpq-fe.h access/xlog.h = /usr/local/pgsql/include/server/access/xlog.h --prefix=/usr/local libpq-fe.h = /usr/local/include/libpq-fe.h access/xlog.h = /usr/local/include/postgresql/server/access/xlog.h pg_config will get an option --server-includedir to point to the files. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Toast, Text, blob bytea Huh?
I'm trying my best to convert from MySQL to PgSQL but I cant get a good clear answer about certian issures.Mainly TEXT, TOAST,BLOB , BYTEA etc. It was an easy task in mysql but everything in the archives about , text , toast and bytea is just confusing me with postgresql. I have Bruces's book and I've searched the Quick glossary: TEXT is a datatype which stores character data of unspecified length (up to the max value of a 4 byte integer in length, although I've seen comments indicating that the practical limit is closer to 1 GB -- not sure why). TEXT is not intended to hold arbitrary binary data. If you want to store binary in a text column, encode it to hex or base64 or something first. TOAST is an internal database concept. If I understand it correctly, it refers to a combination of compression and out-of-line storage for large length values of a charater datatype. This happens transparently to you. BLOB is otherwise known as LO or Large Object datatype in PostgreSQL. These are always stored out-of-line, I don't believe they are compressed, and they have their own special access methods (for dealing with data a chunk at a time). BYTEA is very similar to TEXT, except that it is intended for binary data. I recently posted a PHP function which escapes binary in order to allow inserting it into a bytea column (on the pgsql-general list). At a minimum there are 4 characters which must be escaped. They are ACSII 0, 10, 39, and 92. They must reach PostgreSQL looking like \\000, \\012, \\047, and \\134 respectively (actually 39 could be \' and 92 could be , but it is simpler to be consistent). THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL 1. I cant get a clear answer on what kind of data type to use for my large text string? TEXT, ???, ??? or something about TOAST I have seen in the e-mail archive but cant find any documentaion? So, you can use TEXT if you encode to hex or base64 in your app first, or you bytea if you escape as I described above in your app. Or you can use the LO functions to manipulate large objects (see http://www.postgresql.org/idocs/index.php?lo-interfaces.html). 2. I've written my own escape method ,(cant find one for Pgsql) , BUT i don't know what to escape and not to escape. So it keeps failing. I cand find any docs. on what to escape either? See above. SUMMARY What is the best datatype to use, for large raw text and/or binary if i choose? Once I know this,how Im a supposed to escape my string and get it through the parser correctly so i can retrieve it correctly? If you use TEXT, you will have to decode the hex/base64 back into binary in your app. Similarly, if you use bytea, although stored as binary, the data is returned with unprintable values escaped as octals*. Your app will have to decode the octals back into binary. *NOTE to hackers: is there a good reason for this? ISTM that the client should be responsible for any encoding needed when bytea is returned. The server should return bytea as straight varlena. If you use LO, you have to use the interface functions instead of standard SQL. Hope this helps, -- Joe ---(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] Remove --enable-syslog?
Originally, I added --enable-syslog because it used to be an option in config.h only. However, I wonder why we don't always compile it in, it's off by default anyway. The only reason I could think of is a portability problem. Is there any platform that does not supply the standard syslog interface? If we have configure to test for its existance, I can't see why we wouldn't want it enabled in the binary. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Remove --enable-syslog?
Peter Eisentraut [EMAIL PROTECTED] writes: Is there any platform that does not supply the standard syslog interface? Why worry? Do AC_CHECK_FUNC(syslog), or some such. 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])
[HACKERS] Re: OLAP, Aggregates, and order of operations
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: I need to do some OLAP stuff, and I asked previously if there were a way to pass multiple parameters to an aggrigate function. i.e.: I looked through the code and it is non-trivial to do, Offhand I don't know of any fundamental reason why it couldn't be done, but you're right that it'd take a fair amount of work. I understand the implications of the work, but it would be VERY cool to be able to do this for statistical stuff. If I do this: select mycube(value1) as d1, dimention(value2) as d2, dimention(value3) as d3 group by value1; Can I safely assume the following: (1) mycube() will be called first (2) Assuming dimention() has no final func, that final func of mycube() will be called last. That might be true in the present code, but it strikes me as an awfully risky set of assumptions. Also, it sounds like what you have in mind is to have some hidden state that all the aggregate functions will access; how then will you work if there are more than one set of these aggregates being used in a query? What I was thinking is that I could use the state to hold a pointer returned by palloc. I don't think I can handle multiple mycube() calls, but short of reworking aggregates, I don't see any other way. If the needed parameters are all the same datatype, maybe you could put them into an array and pass the array as a single argument to the aggregate. How would you do this without having to make multiple SQL calls? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] User locks code
If the licence becomes a problem I can easily change it, but I prefer the GPL if possible. We just wanted to make sure the backend changes were not under the GPL. No, Bruce - backend part of code is useless without interface functions and I wonder doesn't GPL-ed interface implementation prevent using of user-locks in *commercial* applications. For example, one could use user-locks for processing incoming orders by multiple operators: select * from orders where user_lock(orders.oid) = 1 LIMIT 1 - so each operator would lock one order for processing and operators wouldn't block each other. So, could such application be commercial with current licence of user_lock()? (Sorry, I'm not licence guru.) DISCLAIMER (to avoid ungrounded rumors -:)) I have no plans to use user-locks in applications of *any* kind (free/commercial). It's just matter of principle - anything in/from backend code maybe used for any purposes, - that's nature of our licence. DISCLAIMER II (to avoid ungrounded rumors in future -:)) I would be interested in using proposed key-locking in some particular commercial application but this feature is not must have for that application - for my purposes it's enough: -- LOCKTAG tag; tag.relId = XactLockTableId; tag.dbId = _tableId_; // tag.dbId = InvalidOid is used in XactLockTableInsert // and no way to use valid OID for XactLock purposes, // so no problem tag.objId.xid = _user_key_; -- - but I like standard solutions more -:) (BTW, key-locking was requested by others a long ago.) Vadim ---(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] User locks code
If the licence becomes a problem I can easily change it, but I prefer the GPL if possible. We just wanted to make sure the backend changes were not under the GPL. No, Bruce - backend part of code is useless without interface functions and I wonder doesn't GPL-ed interface implementation prevent using of user-locks in *commercial* applications. For example, one could use user-locks for processing incoming orders by multiple operators: select * from orders where user_lock(orders.oid) = 1 LIMIT 1 - so each operator would lock one order for processing and operators wouldn't block each other. So, could such application be commercial with current licence of user_lock()? (Sorry, I'm not licence guru.) I assume any code that uses contrib/userlock has to be GPL'ed, meaning it can be used for commercial purposes but can't be sold as binary-only, and actually can't be sold for much because you have to make the code available for near-zero cost. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] A couple items on TODO
Tom Lane writes: AFAIR, elog at NOTICE or DEBUG level isn't really supposed to have any side-effects. The bigger issue is that you have to be careful about using it in certain places, mainly during startup or for reporting communication errors. (send failure - elog - tries to send message to client - send failure - elog - trouble) It's especially postmaster.c and the related subroutines elsewhere that I'm not happy about. The postmaster would really need a way to report an error to the log and continue in its merry ways. This could probably be encapsulated in elog() by setting a flag variable I'm the postmaster -- might even exit already. Note: In my experience, the previous suggestion to return to the postmaster main loop on error would not really be useful. -- 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])
RE: [HACKERS] User locks code
For example, one could use user-locks for processing incoming orders by multiple operators: select * from orders where user_lock(orders.oid) = 1 LIMIT 1 - so each operator would lock one order for processing and operators wouldn't block each other. So, could such application be commercial with current licence of user_lock()? (Sorry, I'm not licence guru.) I assume any code that uses contrib/userlock has to be GPL'ed, meaning it can be used for commercial purposes but can't be sold as binary-only, and actually can't be sold for much because you have to make the code available for near-zero cost. I'm talking not about solding contrib/userlock separately, but about ability to sold applications which use contrib/userlock. Sorry, if it was not clear. Vadim ---(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] User locks code
For example, one could use user-locks for processing incoming orders by multiple operators: select * from orders where user_lock(orders.oid) = 1 LIMIT 1 - so each operator would lock one order for processing and operators wouldn't block each other. So, could such application be commercial with current licence of user_lock()? (Sorry, I'm not licence guru.) I assume any code that uses contrib/userlock has to be GPL'ed, meaning it can be used for commercial purposes but can't be sold as binary-only, and actually can't be sold for much because you have to make the code available for near-zero cost. I'm talking not about solding contrib/userlock separately, but about ability to sold applications which use contrib/userlock. Sorry, if it was not clear. No, you were clear. My assumption is that once you link that code into the backend, the entire backend is GPL'ed and any other application code you link into it is also (stored procedures, triggers, etc.) I don't think your client application will be GPL'ed, assuming you didn't link in libreadline. -- 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 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] User locks code
I assume any code that uses contrib/userlock has to be GPL'ed, meaning it can be used for commercial purposes but can't be sold as binary-only, and actually can't be sold for much because you have to make the code available for near-zero cost. I'm talking not about solding contrib/userlock separately, but about ability to sold applications which use contrib/userlock. Sorry, if it was not clear. No, you were clear. So I missed your near-zero cost sentence. My assumption is that once you link that code into the backend, the entire backend is GPL'ed and any other application code you link into it is also (stored procedures, triggers, etc.) I don't think your client application will be GPL'ed, assuming you didn't link in libreadline. Application would explicitly call user_lock() functions in queries, so issue is still not clear for me. And once again - compare complexities of contrib/userlock and backend' userlock code: what's reason to cover contrib/userlock by GPL? Vadim ---(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] Assessment on namespace clean include file names
Peter Eisentraut [EMAIL PROTECTED] writes: [1] -- The libpq-int.h draws in a lot of internal structure, true to the name. Something should be done about that, such as not installing it, or moving it to a hidden place. Ideas? libpq-int.h was always intended to be strictly internal. I made it part of the export fileset when it was created because I feared that there were probably applications out there that were using direct access to fields of PGresult, and I wanted to give them breathing room to update their code. But at this point they've had several releases worth of breathing room. I see no reason why we can't drop the other shoe and stop exporting libpq-int.h (or more accurately, move it out of the public namespace, same as you propose for backend headers). The idea I currently have for the installation layout including the server includes is this: --prefix=/usr/local/pgsql libpq-fe.h= /usr/local/pgsql/include/libpq-fe.h access/xlog.h = /usr/local/pgsql/include/server/access/xlog.h server may not be a great choice if we want to stick libpq-int.h into it too... 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] User locks code
No, you were clear. So I missed your near-zero cost sentence. OK. My assumption is that once you link that code into the backend, the entire backend is GPL'ed and any other application code you link into it is also (stored procedures, triggers, etc.) I don't think your client application will be GPL'ed, assuming you didn't link in libreadline. Application would explicitly call user_lock() functions in queries, so issue is still not clear for me. And once again - Well, yes, it calls user_lock(), but the communication is not OS-linked, it is linked over a network socket, so I don't think the GPL spreads over a socket. Just as telnet'ing somewhere an typing 'bash' doesn't make your telnet GPL'ed, so I think the client code is safe. To the client, the backend is just returning information. You don't really link to the query results. compare complexities of contrib/userlock and backend' userlock code: what's reason to cover contrib/userlock by GPL? Only because Massimo prefers it. I can think of no other reason. It clearly GPL-stamps any backend that links it in. -- 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 4: Don't 'kill -9' the postmaster
RE: [HACKERS] User locks code
Application would explicitly call user_lock() functions in queries, so issue is still not clear for me. And once again - Well, yes, it calls user_lock(), but the communication is not OS-linked, it is linked over a network socket, so I don't think the GPL spreads over a socket. Just as telnet'ing somewhere an typing 'bash' doesn't make your telnet GPL'ed, so I think the client code is safe. To the client, the backend is just returning information. You don't really link to the query results. Ah, ok. compare complexities of contrib/userlock and backend' userlock code: what's reason to cover contrib/userlock by GPL? Only because Massimo prefers it. I can think of no other reason. It clearly GPL-stamps any backend that links it in. Ok, let's do one step back - you wrote: My assumption is that once you link that code into the backend, the entire backend is GPL'ed and any other application code you link into it is also (stored procedures, triggers, etc.) So, one would have to open-source and GPL all procedures/triggers used by application just because of application uses user_lock() in queries?! Is it good? Vadim ---(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] User locks code
Well, yes, it calls user_lock(), but the communication is not OS-linked, it is linked over a network socket, so I don't think the GPL spreads over a socket. Just as telnet'ing somewhere an typing 'bash' doesn't make your telnet GPL'ed, so I think the client code is safe. To the client, the backend is just returning information. You don't really link to the query results. Ah, ok. Yes, kind of tricky. I am no expert in this but I have had the usual discussions. compare complexities of contrib/userlock and backend' userlock code: what's reason to cover contrib/userlock by GPL? Only because Massimo prefers it. I can think of no other reason. It clearly GPL-stamps any backend that links it in. Ok, let's do one step back - you wrote: My assumption is that once you link that code into the backend, the entire backend is GPL'ed and any other application code you link into it is also (stored procedures, triggers, etc.) So, one would have to open-source and GPL all procedures/triggers used by application just because of application uses user_lock() in queries?! Is it good? Yep. Is it good? Well, if you like the GPL, I guess so. If you don't, then it isn't good. Of course, if you want to try and make money selling your program, it isn't good whether you like the GPL or not. :-) -- 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: [HACKERS] User locks code
I definitely agree with Vadim here: it's fairly silly that the contrib userlock code is GPL'd, when it consists only of a few dozen lines of wrapper for the real functionality that's in the main backend. The only thing this licensing setup can accomplish is to discourage people from using the userlock code; what's the value of that? Besides, anyone who actually wanted to use the userlock code would need only to write their own wrapper functions to get around the GPL license. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User locks code
I definitely agree with Vadim here: it's fairly silly that the contrib userlock code is GPL'd, when it consists only of a few dozen lines of wrapper for the real functionality that's in the main backend. The only thing this licensing setup can accomplish is to discourage people from using the userlock code; what's the value of that? Besides, anyone who actually wanted to use the userlock code would need only to write their own wrapper functions to get around the GPL license. Hey, I agree with Vadim too. The GPL license is just a roadblock, but I can't tell Massimo what to do with his code if it is not in the backend proper. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] CURRENT OF cursor without OIDs
AFAICS, if you are holding an open SQL cursor, it is sufficient to check that ctid hasn't changed to know that you have the same, un-updated tuple. Under MVCC rules, VACUUM will be unable to delete any tuple that is visible to your open transaction, and so new-style VACUUM cannot recycle the ctid. ... As Tom mentiond once in this thread, I've referred to non-SQL cursors which could go across transaction boundaries. TIDs aren't that reliable across transactions. We could avoid reassignment of MyProc-xmin having cursors opened across tx boundaries and so new-style vacuum wouldn't remove old tuple versions... OIDs and xmin have already lost a part of its nature. Probably I have to guard myself beforehand and so would have to mention repeatedly from now on that if we switch to an overwriting smgr, there's no system item to detect the change of tuples. So, is tid ok to use for your purposes? I think we'll be able to restore old tid along with other tuple data from rollback segments, so I don't see any problem from osmgr... Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] CURRENT OF cursor without OIDs
"Mikheev, Vadim" wrote: AFAICS, if you are holding an open SQL cursor, it is sufficient to check that ctid hasn't changed to know that you have the same, un-updated tuple. Under MVCC rules, VACUUM will be unable to delete any tuple that is visible to your open transaction, and so new-style VACUUM cannot recycle the ctid. ... As Tom mentiond once in this thread, I've referred to non-SQL cursors which could go across transaction boundaries. TIDs aren't that reliable across transactions. We could avoid reassignment of MyProc-xmin having cursors opened across tx boundaries and so new-style vacuum wouldn't remove old tuple versions... Oops I'm referring to client side cursors in our ODBC driver. We have no cross-transaction cursors yet though I'd like to see a backend cross-transaction cursor also. OIDs and xmin have already lost a part of its nature. Probably I have to guard myself beforehand and so would have to mention repeatedly from now on that if we switch to an overwriting smgr, there's no system item to detect the change of tuples. So, is tid ok to use for your purposes? No. I need an OID-like column which is independent from the physical position of tuples other than TID. I think we'll be able to restore old tid along with other tuple data from rollback segments, so I don't see any problem from osmgr... How do we detect the change of tuples from clients ? TIDs are invariant under osmgr. xmin is about to be unreliable for the purpose. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] ERP Applications on Postgresql -- ERPTool
Hi Everyone, Just wanted to let you all know that I have been working on development of financial applications using,java, javascript, javabeans and of course PostgreSQL database for past one year. I was out of touch with the community for this time and it kinda feels like as if I am coming out trenches. I heard and read interviews by Geoff Davidson and Bruce Momijam. In Geoff Davidson's interview there is talk about need of Applications like Oracle or many other commercial vendors have. Well I cannot say that my Application , which I fondly call ERPTool , can fill the need but ,it definitly can provide a very good starting point. Right now the modules it has are, Order Entry, Purchasing, Receivables, Payables, GL (Basic) Inventory (Very Basic) There are three main points that set it apart from the commercial applications. 1.It is built using mostly free/open source software. 2.It is highly and very rapidly customizable. 3.It needs nothing more than a browser on client side to function. While it serves all the basic needs for a small company it can very rapidly expanded by adding new forms and functionality to suit the needs of an enterprise of any size. I would like to colloborate with postgresql and other open source communities to make one of the most versetile and dependable product using open source code. Please let me know if this can be done. Regards Amandeep Singh __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] A couple items on TODO
As I was browsing TODO, I noticed a couple unassigned items that I may be able to help with (I haven't worked with the source before): *Add use of 'const' for variables in source tree I would discuss this item with the hackers list and see exactly what people want done with it. I have noticed while working on command.c and heap.c that half the functions pass 'const char *' and the other half pass just 'char *'. This is a pain when you have a little helper function like 'is_relation(char *)' and you want to pass a 'const char *' to it and vice versa. ie. Compiler warnings - it's sort of annoying. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] A couple items on TODO
As I was browsing TODO, I noticed a couple unassigned items that I may be able to help with (I haven't worked with the source before): *Add use of 'const' for variables in source tree I would discuss this item with the hackers list and see exactly what people want done with it. I have noticed while working on command.c and heap.c that half the functions pass 'const char *' and the other half pass just 'char *'. This is a pain when you have a little helper function like 'is_relation(char *)' and you want to pass a 'const char *' to it and vice versa. ie. Compiler warnings - it's sort of annoying. Yes, it can be very annoying. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Permissions for large-object comments
Shane Wegner [EMAIL PROTECTED] writes: test= \lo_unlink 89803 ERROR: pg_description: Permission denied. Hmm. Maybe those client-side comment manipulations in psql aren't such a hot idea. I know I never tested them as non-superuser :-( Shane, try that from a superuser Postgres userid. Meanwhile, it's back to the drawing board for us. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] A couple items on TODO
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I have noticed while working on command.c and heap.c that half the functions pass 'const char *' and the other half pass just 'char *'. This is a pain Yeah, people have started to use 'const' in new code, but the older stuff doesn't use it, which means that the net effect is probably more annoyance than help. I'm afraid that if we attack this in an incremental way, we'll end up with code that may have a lot of const markers in the declarations, but the actual code is riddled with explicit casts to remove const because at one time or another that was necessary in a particular place. Can anyone think of a way to get from here to there without either a lot of leftover cruft, or a big bang massive changeover? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: OLAP, Aggregates, and order of operations
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: If the needed parameters are all the same datatype, maybe you could put them into an array and pass the array as a single argument to the aggregate. How would you do this without having to make multiple SQL calls? I was thinking something like select my_aggregate(my_array_constructor(foo, bar, baz)) from ... where my_array_constructor is a quick hack C routine to build a 3-element array from 3 input arguments (s/3/whatever you need/). Someday we ought to have SQL syntax to build an array value from a list of scalars, but in the meantime an auxiliary function is the only way to do it. Interesting. Kind of ugly, but interesting. So, what would the order of operation be? I assume my_array_constructor() would be called first, and the return value then be passed to my_aggregate() along with the state value being set to the initial state, then subsequent calls to my_array_constructor(), followed by my_aggregate() for each additional row in the group? I need to think about that. The overhead of constructing and then interpreting the temporary array value is slightly annoying, but I don't think it'll be horribly expensive. See the existing aggregate-related routines in numeric.c if you need some help with the C coding. postgres use story I can do the C stuff, I have tons of C and C++ functions written for Postgres already, when I get the time to make them clean enough to contribute to the Postgres project, I will. (Text manipulation, search engine, date manipulation, xmcd, analysis functions, decode, and others) If you are interested in seeing a half Oracle, half Postgres site, take a look at http://www.dotclick.com. (You will need a Windows box) It is pretty evenly split between postgres and oracle. All member related data is on Oracle. All music related data is in Postgres. It has saved us probably $50K to $100 in Oracle database licenses and hardware to do it this way. We have three postgres boxes. One master, and two slaves. The master gets updated with new information from various sites. The program which does the updating, on the master, creates a SQL log script of everything it does. The script is then run against the slaves to maintain consistency. A web farm is split evenly between the two slaves. It is pretty cool. (As a side note, we are using Oracle for session management across a bunch of servers. Sadly we can not use postgres for this (we would love too), sessions are mostly updates and deletes, maybe when 7.2 comes out, but I'm still not sure about that.) /postgres use story ---(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] A couple items on TODO
*Add use of 'const' for variables in source tree I would discuss this item with the hackers list and see exactly what people want done with it. I have noticed while working on command.c and heap.c that half the functions pass 'const char *' and the other half pass just 'char *'. This is a pain when you have a little helper function like 'is_relation(char *)' and you want to pass a 'const char *' to it and vice versa. ie. Compiler warnings - it's sort of annoying. That's good information, now I have a better idea what I am looking for. I am using Source Navigator (good recommendation I got reading this list). I am basically just trying to find either variables that can be declared const, or inconsistancies (as Chris mentions). If anyone else has a clearer idea of what the intention of that todo item is, let me know. Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: OLAP, Aggregates, and order of operations
mlw [EMAIL PROTECTED] writes: So, what would the order of operation be? I assume my_array_constructor() would be called first, and the return value then be passed to my_aggregate() along with the state value being set to the initial state, then subsequent calls to my_array_constructor(), followed by my_aggregate() for each additional row in the group? Check. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Link to bug webpage
Bruce Momjian wrote: How do you communicate that to people looking at the content? Do you put in big letters at the top, This list is not complete. The fact an items is missing from the list (new bug) is just as important as an item appearing on the list. How do you distinguish that from what we have now? I can't look at my pgsql email box and see how many and of what. A bugzilla is a more accurate representation of bugs and future features for the group. -d ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Link to bug webpage
David Ford wrote: Bruce Momjian wrote: That is the real question. Do we want to rely more heavily on a bug database rather than the email lists? I haven't heard many say they want that. I'd very much like a bugzilla because I can do research on bugs past or present now as well as know the status of them. Right now if I had a bug I would have to dig through web page after web page or use wget and grep. Using bugzilla seems the best option for me too. No need to roll our own bug tracking system when we could spend the same effort on making Bugzilla/PostgreSQL work better. --- Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [PATCHES] encoding names
BTW, what's wrong with encoding? I don't think, for example EUC-JP or utf-8, are character set names. Hmm, SQL talks of character sets, it has a CHARACTER_SETS view and such. It's slightly incorrect, I agree. Maybe we should not touch getdatabaseencoding() right now, given that the names we currently use are apparently almost correct anyway and considering the pain it creates to alter them, and instead implement the information schema views in the future? I thought schema stuffs would be introduced in 7.2 but apparently it would not happen... -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [HACKERS] A couple items on TODO
That's good information, now I have a better idea what I am looking for. I am using Source Navigator (good recommendation I got reading this list). I am basically just trying to find either variables that can be declared const, or inconsistancies (as Chris mentions). If anyone else has a clearer idea of what the intention of that todo item is, let me know. I assume that since most of the times char * is passed to a function, it is supposed to be unmodifiable. Putting the 'const' there enforces this - thereby making PostgreSQL more robust against poxy programming. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User locks code
Tom Lane wrote: I definitely agree with Vadim here: it's fairly silly that the contrib userlock code is GPL'd, when it consists only of a few dozen lines of wrapper for the real functionality that's in the main backend. As it seems a generally useful feature, it could at least be LGPL'd so that linking to it won't force the whole backend under GPL. The only thing this licensing setup can accomplish is to discourage people from using the userlock code; what's the value of that? Maybe it makes Massimo feel good ? It seems a worhty reason to me, as he has contributed a lot of useful stuff over the time. I really think that mixing licences inside one program is bad, if not for any other reason then for confusing people and making them have discussions like this. Besides, anyone who actually wanted to use the userlock code would need only to write their own wrapper functions to get around the GPL license. This is a part of copyright law that eludes me - can i write a replacement function for something so simple that it can essentially be done in one way only (like incrementing a value by one) ? - Hannu ---(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] Link to bug webpage
Honestly I wasn't aware postgres had any bugs... tongue in cheek. What I mean is PG works very nicely for me and I haven't had any problems with it, so that means no bugs. Yes there are bugs and things to be solved, but from my perspective it is already a pretty darn good piece of software. -d Philip Warner wrote: At 08:32 21/08/01 -0400, Vince Vielhaber wrote: Yes but noone was interested in it. It's still there but you're really the first to show interest in about a year. That's good (and depressing); where are they? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Link to bug webpage
Bruce Momjian wrote: That is the real question. Do we want to rely more heavily on a bug database rather than the email lists? I haven't heard many say they want that. I'd very much like a bugzilla because I can do research on bugs past or present now as well as know the status of them. Right now if I had a bug I would have to dig through web page after web page or use wget and grep. -d ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Remove --enable-syslog?
Originally, I added --enable-syslog because it used to be an option in config.h only. However, I wonder why we don't always compile it in, it's off by default anyway. The only reason I could think of is a portability problem. Is there any platform that does not supply the standard syslog interface? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
Hiroshi Inoue [EMAIL PROTECTED] writes: Hmm OIDs would be optional in 7.2. Is it known(announced) to pgsql-jdbc list ? Doesn't seem particularly relevant to this issue though. An application that's using OIDs to identify rows would certainly not choose to create its tables without OIDs. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: [JDBC] New backend functions? [was Re: JDBC ch
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 23-Aug-2001 Rene Pijlman wrote: What should the semantics be exactly? How about the multiple INSERT's i've been reading about on hackers? ... Only the OID of the last row inserted by the statement? How about an UPDATE statement that updates multiple rows? Well, here's my thoughts on this... The functionality would be that the very last inserted or updated OID would be stored in this static variable that is associated with the connection/session. So, in multiple inserts or updates, it is the last oid affect where this variable would be updated. How about triggers that insert/update extra rows? ... Only the OID of the row directly inserted by the client statement? It would be the last updated request caused by any insert or update, regardless of if its a trigger, preparedStatement, etc. How about Large Objects? Should inserting or updating a large object affect getLastInsertedOID()? Yes. I assume this OID would be associated with a client connection. Is this going to work with client side connection pooling? It must... that's the reason for this. Specifically, the JDBC driver has a method in it that is called getInsertedOID() which provides the last sucessfully inserted row's OID. This is specific to the driver, and JDBC pooling techniques do not allow access to this method. (It's not part of the JDBC spec) So, to make this data accessable to the users in a pooling condition, the call select getLastOID() needs to return the OID that is specific to the session. In Java, pooling techniques generally are aquired, then released, as dependant on the client or timeout procedures, and not randomly used for individual queries. (Mostly because of the need for the same driver during a transaction that takes multiple queries.) How about transaction semantics? INSERT row 1, Commit, INSERT row 2, Rollback... what should getLastInsertedOID() return? Can it, with a static variable? Good question. I'd start with rollback not affecting this value. Reason being that this function would be mostly used in a transaction anyways. I would not object to making this method only available during a transaction block if that helps. Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hWEwiysnOdCML0URAk3xAJ92nYoy22mP4Yk8xk53vojlF42w5gCfbnZf uexoQ9yqexctXvQM0yx+g2Y= =yK6n -END PGP SIGNATURE- ---(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] Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
On Thu, 23 Aug 2001 14:44:19 -0400, you wrote: Ned Wolpert [EMAIL PROTECTED] writes: Should the backend support the function getLastInsertedOID()? seems doable and reasonable to me: whenever an OID is returned to the client in an INSERT or UPDATE command result, also stash it in a static variable that can be picked up by this function. What should the semantics be exactly? How about the multiple INSERT's i've been reading about on hackers? ... Only the OID of the last row inserted by the statement? How about an UPDATE statement that updates multiple rows? How about JDBC batchExecute() when it performs multiple INSERT/UPDATE's? ... Only the OID of the last UPDATE or INSERT statement in the batch? How about triggers that insert/update extra rows? ... Only the OID of the row directly inserted by the client statement? How about Large Objects? Should inserting or updating a large object affect getLastInsertedOID()? I assume this OID would be associated with a client connection. Is this going to work with client side connection pooling? How about transaction semantics? INSERT row 1, Commit, INSERT row 2, Rollback... what should getLastInsertedOID() return? Can it, with a static variable? Regards, René Pijlman ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I like your function name, get_last_returned_oid(). That works for me. On 23-Aug-2001 Tom Lane wrote: Ned Wolpert [EMAIL PROTECTED] writes: Should the backend support the function getLastInsertedOID() or even getLastInsertedPrimaryKey() (or both)? I don't think you have any chance of doing the latter --- for one thing, how are you going to declare that function's return type? But the former seems doable and reasonable to me: whenever an OID is returned to the client in an INSERT or UPDATE command result, also stash it in a static variable that can be picked up by this function. Please pick a more SQL-friendly (ie, case insensitive) naming convention, though. And note that it'd apply to both INSERT and UPDATE. Maybe get_last_returned_oid() ? 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]) Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hWGbiysnOdCML0URAkqAAJ9Liv8VS+CPMYozG1q1tuy7vGLuEACfUJRM Hdbns8MxyOVgurx5ztV8YZU= =BbF3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
Rene Pijlman [EMAIL PROTECTED] writes: On Thu, 23 Aug 2001 14:44:19 -0400, you wrote: seems doable and reasonable to me: whenever an OID is returned to the client in an INSERT or UPDATE command result, also stash it in a static variable that can be picked up by this function. What should the semantics be exactly? Just the same as the command result string. How about the multiple INSERT's i've been reading about on hackers? ... Only the OID of the last row inserted by the statement? No OID is returned when multiple rows are inserted or updated. I'd say that should be the semantics of this function, too. How about JDBC batchExecute() when it performs multiple INSERT/UPDATE's? By definition, this is a backend function. It cannot know anything of JDBC. I assume this OID would be associated with a client connection. Is this going to work with client side connection pooling? Good point. Will this really get around the original poster's problem?? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: [JDBC] New backend functions? [was Re: JDBC ch
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 23-Aug-2001 Tom Lane wrote: I assume this OID would be associated with a client connection. Is this going to work with client side connection pooling? Good point. Will this really get around the original poster's problem?? It must. If transactions are on, any pooling mechanism needs to continue to use that connection for the client unti the transaction is done. (Most require the client to either tell the pool manager the connection is no longer need, via a close() call, or a pool-manager specific call, precisely because the client needs it to complete the transaction. My feeling is that if this is a problem, then this method call may need to be limited to the transaction context, but I hope that this is not the case. Most pool managers (and I'm only speaking about Java here) require some activity on the client to give up the connection, either directly or indirectly. Virtually, Ned Wolpert [EMAIL PROTECTED] D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7hYNRiysnOdCML0URAre3AJ94x/4mfeaJX3IQjRtyTWafeaR/BgCeIB4V liQyRjblBSuX38R0kq+NvVw= =ltfC -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
Ned Wolpert [EMAIL PROTECTED] writes: Should the backend support the function getLastInsertedOID() or even getLastInsertedPrimaryKey() (or both)? I don't think you have any chance of doing the latter --- for one thing, how are you going to declare that function's return type? But the former seems doable and reasonable to me: whenever an OID is returned to the client in an INSERT or UPDATE command result, also stash it in a static variable that can be picked up by this function. Please pick a more SQL-friendly (ie, case insensitive) naming convention, though. And note that it'd apply to both INSERT and UPDATE. Maybe get_last_returned_oid() ? 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] Re: [JDBC] New backend functions? [was Re: JDBC changes for 7.2... some questions...]
Tom Lane wrote: Ned Wolpert [EMAIL PROTECTED] writes: Should the backend support the function getLastInsertedOID() or even getLastInsertedPrimaryKey() (or both)? I don't think you have any chance of doing the latter --- for one thing, how are you going to declare that function's return type? But the former seems doable and reasonable to me: whenever an OID is returned Hmm OIDs would be optional in 7.2. Is it known(announced) to pgsql-jdbc list ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: [GENERAL] Postgresql log analyzer
Hi all, I have updated the drafts for pg log analyzer especially for EXPLAIN output. What do you want to see as statistics result. Currently I only output the following: - scan type - startup cost - total cost - number of rows returned - and the width There's certainly other usefull information but I don't know. Please let me know ! Note: This is a simple draft to show what can be done, as a general purpose it will include: - A configuration file (to choose what should be reported, paths, etc...) or/and command line args - An index page with resume of all reports - Incremental scan working on full or rotate log For other good requests it's done... Let me know any other requests otherwise I will publish the first release at least on monday if not tomorow ! http://www.samse.fr/GPL/log_report/ Regards, Gilles Darold Andrew McMillan wrote: Gilles DAROLD wrote: Hi all, Here is a first draft generated by a log analyzer for postgres I've wrote today: http://www.samse.fr/GPL/log_report/ In all this html report there is what I'm able to extract minus the statistics. I need to know what people want to see reported to have a powerfull log analyzer, I like what you have there so far. For my own use I would like to see the ability to turn some of these off, and also perhaps a summary page that you would click through to the more detailed reports. The 'query' page is kind of complicated too. Would it be possible to put that into a table layout as well? +---+ |select... | +++++---+ |stat|stat|stat|stat ...| | +++++---+ sort of layout. It would be nice to see an EXPLAIN on the query page, but you would want this to be an option, I guess. I imagine you could do this by getting the EXPLAIN at log analysis time if it isn't in the logs. Cheers, Andrew. -- _ Andrew McMillan, e-mail: Andrew @ catalyst . net . nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64(21)635-694, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl