Re: [HACKERS] can plpgsql returns more flexibe value ?
On Sun, 23 Jan 2005, Arnold.Zhu wrote: Can I use DataAdapter.Fill() with refcursor. :-( I have no idea what DataAdapter is, you will need to check your client interface for support (and this probably isn't the place to do that), but it's certainly possible. See for example The world's most advanced PostgreSQL client interface http://jdbc.postgresql.org/documentation/80/ch06.html#callproc-resultset-refcursor Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] French site with postgresql name
Hello Dennis, Some french guy on IRC showed the site http://www.postgresql.fr/ that does not contain anything about postgresql. I don't speak french so I can't tell what the page is about. It looks linux related. This doesn't really belong on the -hackers list but I don't know where to send the info or even if someone cares about it at all. The .fr names are now available for all. It changes from the previous situation where you had to be a company and could only register the name of that company. I sent a mail to the person that did it as told by whois, to ask about his motivation. I do not expect much. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Hackers in London/Oxford
I seem to remember that some of you guys were in London or Oxford? I'm in London at the moment and will be in Oxford on the weekend if anyone wants to catch up? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Hackers in London/Oxford
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Kings-Lynne Sent: 24 January 2005 09:41 To: pgsql-hackers@postgresql.org Subject: [HACKERS] Hackers in London/Oxford I seem to remember that some of you guys were in London or Oxford? I'm in London at the moment and will be in Oxford on the weekend if anyone wants to catch up? I'm about 20 miles from Oxford, and could certainly go for a beer and curry or similar if you like. If he's not busy, we might be able to persuade Simon Riggs to come along as well - he's not too far away. Regards, Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] can plpgsql returns more flexibe value ?
Hello, Kris Jurka Thank you for your reply, I will go to Npgsql development team for help. I have no idea what DataAdapter is, you will need to check your client interface for support (and this probably isn't the place to do that), but it's certainly possible. See for example The world's most advanced PostgreSQL client interface http://jdbc.postgresql.org/documentation/80/ch06.html#callproc-resultset-refcursor Kris Jurka Arnold.Zhu [EMAIL PROTECTED] 2005-01-24 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Concurrent free-lock
Hi, I read recently a paper Keir Fraser Tim Harris, Concurrent Programing without Locks, ACM Journal Name, vol V, n° N, M 20YY, Page 1-48 About algorithm to manage structure (exemple about red-black tree, skip list) with dead-lock free property, parallel read, etc. Does this have been studied for PostgreSQL ? There is surely some good idea in it. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autotuning Group Commit
On Fri, 21 Jan 2005 23:52:51 +, Simon Riggs [EMAIL PROTECTED] wrote: Currently, we have group commit functionality via GUC parameters commit_delay andcommit_siblings And since 7.3 we have ganged WAL writes (c.f. the thread starting at http://archives.postgresql.org/pgsql-hackers/2002-10/msg00331.php) which IMHO is a better solution to the same problem. Maybe the code dealing with commit_xxx parameters should just be removed. Are you or is anybody else aware of benchmarks showing that group commit via commit_xxx is still useful? Servus Manfred ---(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] Locale agnostic unicode text
On Sat, 22 Jan 2005 17:09:42 -0500, Tom Lane [EMAIL PROTECTED] wrote: This time setlocale() was needed to get the behaviour I needed (database initdb'ed to 'C', my order set to 'pl_PL', or whatever locale I need at given moment). I would imagine that the performance is spectacularly awful :-(. Have you benchmarked it? A large sort on a unitext column, for instance, would be revealing. Why do you persist in believing this? I sent timing results of doing a setlocale for every record here about a year ago. Sorting on the pg_strxfrm I posted (and Conway rewrote) was about twice as slow as sorting without using it. So it's slow but not spectacularly awful. This depends on having a good setlocale implementation, but glibc at least seems to be satisfactory. -- greg ---(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] Concurrent free-lock
Lock free data structures are cool... but not really applicable to databases. They have a high maintenance overhead, severe complexity, and will fail when there are many concurrent inserts/deletes to the structure. I messed with them a year or so ago, and that's what I found in every implementation. Pailloncy Jean-Gerard wrote: Hi, I read recently a paper Keir Fraser Tim Harris, Concurrent Programing without Locks, ACM Journal Name, vol V, n° N, M 20YY, Page 1-48 About algorithm to manage structure (exemple about red-black tree, skip list) with dead-lock free property, parallel read, etc. Does this have been studied for PostgreSQL ? There is surely some good idea in it. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not matc h ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Much Ado About COUNT(*)
Here's a possible solution... though I'm not sure about whether you find the pg_ prefix appropriate for this context. -- Create a Test Relation CREATE TABLE test_tbl ( test_id BIGINT NOT NULL, test_value VARCHAR(128) NOT NULL, PRIMARY KEY (test_id)); -- Create COUNT Collector Relation CREATE TABLE pg_user_table_counts ( schemaname VARCHAR(64) NOT NULL, tablenameVARCHAR(64) NOT NULL, rowcount BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (schemaname, tablename)); -- Populate Collector Relation INSERT INTO pg_user_table_counts (schemaname, tablename) (SELECT schemaname, tablename FROM pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' AND tablename != 'pg_user_table_counts' ) ; -- Create our Increment/Decrement Function CREATE OR REPLACE FUNCTION pg_user_table_count_func () RETURNS TRIGGER AS $pg_user_table_count_func$ DECLARE this_schemaname VARCHAR(64); BEGIN SELECT INTO this_schemaname nspname FROM pg_namespace WHERE oid = (SELECT relnamespace FROM pg_class WHERE oid = TG_RELID); -- Decrement Count IF (TG_OP = 'DELETE') THEN UPDATE pg_user_table_counts SET rowcount = rowcount - 1 WHERE schemaname = this_schemaname AND tablename = TG_RELNAME; ELSIF (TG_OP = 'INSERT') THEN UPDATE pg_user_table_counts SET rowcount = rowcount + 1 WHERE schemaname = this_schemaname AND tablename = TG_RELNAME; END IF; RETURN NULL; END; $pg_user_table_count_func$ LANGUAGE plpgsql; -- Create AFTER INSERT/UPDATE Trigger on our Test Table CREATE TRIGGER test_tbl_aidt AFTER INSERT OR DELETE ON test_tbl FOR EACH ROW EXECUTE PROCEDURE pg_user_table_count_func(); -- INSERT to Test Relation INSERT INTO test_tbl VALUES (1, 'Demo INSERT'); -- Query Collector demodb=# SELECT * FROM pg_user_table_counts; schemaname |tablename| rowcount +-+-- public | test_tbl|1 (1 row) -- DELETE from Test Relation DELETE FROM test_tbl; -- Query Collector emodb=# SELECT * FROM pg_user_table_counts; schemaname |tablename| rowcount +-+-- public | test_tbl|0 (1 row) Mark Kirkwood wrote: Jim C. Nasby wrote: Does anyone have working code they could contribute? It would be best to give at least an example in the docs. Even better would be something in pgfoundry that helps build a summary table and the rules/triggers you need to maintain it. http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html#PLPGSQL-TRIGGER-SUMMARY-EXAMPLE regards Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Shortcut for defining triggers
On Sun, Jan 23, 2005 at 03:49:22PM -0600, Jim C. Nasby wrote: Sorry if this is old, but I couldn't find it in the archives... How difficult would it be to provide a means to define a trigger in one statement? Something like a combination of CREATE TRIGGER and CREATE FUNCTION? Being able to define them seperately is awesome for generic cases where you can use one function for a bunch of different tables, but it's a pain in the cases where you need a unique trigger for one table. What would you want the function name to default to? What language, or would you want to specify that somehow? Here's a sketch of what such an API might look like: CREATE TRIGGER foo_trg BEFORE INSERT OR UPDATE ON foo_tab FOR EACH ROW EXECUTE PROCEDURE LANGUAGE PLPGSQL (/* params would go here if any */) $$ /* body here */ $$; This would cause a foo_tab_b4_iu_func (how to address namespace collisions?) to be created in the appropriate language with appropriate params, then the foo_trg on the table. Does SQL:2003 have anything to say about this? Also, what kind of development effort would be involved with an implementation, assuming SQL:2003 doesn't forbid? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(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] Extending System Views: proposal for 8.1/8.2
Marc G. Fournier wrote: I may be missing something here, but haven't we always stated that using 'SELECT *' should be frown'd upon for the most part? No, we have never stated that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Stephen Frost wrote: The other difference would seem to be that user identifiers can't be granted to users whereas role identifiers can be. Following this, rolmembers must be NULL if rolcanlogin is true, no? That breaks if roles can log in though. Or should we just allow granting of user identifiers to other users- but if we do should the user be permitted to do that? If he has admin option on his own role, sure. But I suppose by default we wouldn't. One use case I see is if someone goes on vacation he can temporarily grant the privileges held by his user account to others without actually giving out the login data. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Locale agnostic unicode text
Greg Stark [EMAIL PROTECTED] writes: On Sat, 22 Jan 2005 17:09:42 -0500, Tom Lane [EMAIL PROTECTED] wrote: I would imagine that the performance is spectacularly awful :-(. Have you benchmarked it? A large sort on a unitext column, for instance, would be revealing. Why do you persist in believing this? I sent timing results of doing a setlocale for every record here about a year ago. Sorting on the pg_strxfrm I posted (and Conway rewrote) was about twice as slow as sorting without using it. So it's slow but not spectacularly awful. glibc is not the world. I tried Dawid's functions on Mac OS X, being a random non-glibc platform that I happen to use. Using some text data I had handy (44500 lines, 1.9MB) I made a single-column text table and timed explain analyze select * from foo order by f1; The results were In C locale, SQL_ASCII encoding:820 ms In C locale, UNICODE encoding: 825 ms Using Dawid's functions:62010 ms Stripped-down functions:21010 ms The stripped down functions were the same functions without the locale overhead, eg CREATE OR REPLACE FUNCTION unitext_le(unitext,unitext) RETURNS boolean AS $$ my $ret = ($_[0] le $_[1]) ? 't' : 'f'; return $ret; $$ LANGUAGE plperlu STABLE; so we may conclude that about one-third of the overhead is plperl's fault and the other two-thirds is setlocale's fault. But it's still a factor of 50 slowdown to do it this way (actually worse, since not all of the EXPLAIN ANALYZE total runtime went into sorting). I'm not sure what your threshold of spectacularly awful is, but that meets mine. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Peter Eisentraut [EMAIL PROTECTED] writes: Marc G. Fournier wrote: I may be missing something here, but haven't we always stated that using 'SELECT *' should be frown'd upon for the most part? No, we have never stated that. We do however point out in the docs that SELECT * is vulnerable to addition of columns, eg footnote 1 here: http://www.postgresql.org/docs/8.0/static/tutorial-select.html We've always felt free to add columns to system catalogs at need, and I don't see that adding some to system views is worse. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Disallow LOAD to non-superusers.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Is it time for 7.4.7? I've seen many commits to 7.4 branch nowadays. Regards, Devrim On Mon, 24 Jan 2005, Tom Lane wrote: Log Message: --- Disallow LOAD to non-superusers. Per report from John Heasman. Tags: REL7_4_STABLE Modified Files: -- pgsql/src/backend/tcop: utility.c (r1.208 - r1.208.2.1) (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/utility.c.diff?r1=1.208r2=1.208.2.1) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match - -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFB9TYbtl86P3SPfQ4RArMOAJ4u9iPSiRnXKMIBIXIQCauJ1QaljACg3gjG 17d0mWM80ytwj5NxTpidMFM= =bomB -END PGP SIGNATURE- ---(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] [COMMITTERS] pgsql: Disallow LOAD to non-superusers.
Devrim GUNDUZ [EMAIL PROTECTED] writes: Is it time for 7.4.7? I've seen many commits to 7.4 branch nowadays. I just started a discussion about that on the core list ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Locale agnostic unicode text
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: So it's slow but not spectacularly awful. glibc is not the world. Sorry, I should have said It's not *necessarily* spectacularly awful I tried Dawid's functions on Mac OS X, being a random non-glibc platform that I happen to use. Using some text data I had handy (44500 lines, 1.9MB) I made a single-column text table and timed explain analyze select * from foo order by f1; The results were In C locale, SQL_ASCII encoding:820 ms In C locale, UNICODE encoding: 825 ms Using Dawid's functions:62010 ms Stripped-down functions:21010 ms I don't think these are fair comparisons though. The C locale probably short-circuits much of the work that strxfrm/strcoll have to do for other locales. I think the fair comparison is to compare a database initdb'd in a non-C locale like en_US using strcoll with no setlocale calls against one calling setlocale twice for every record. In any case it's true, some platforms have bad implementations of things. But if you have to do this (and I have to do this too) it doesn't really matter that some platforms don't handle it well. This just means those platforms aren't feasible and I'm forced to use glibc-based platforms. It doesn't mean I should dismiss Postgres for the project. Incidentally Dawid, if you are on a platform like OSX with a performance problem with this there is a possible optimization you can use. If you store and update the data rarely but sort it frequently you can store the output of strxfrm in a bytea column. Then you can sort on that column without having to call setlocale repeatedly. If you have few queries that can be optimized to always use indexes you can even store this information in a functional index instead of denormalizing the table. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Locale agnostic unicode text
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: The results were In C locale, SQL_ASCII encoding: 820 ms In C locale, UNICODE encoding: 825 ms Using Dawid's functions: 62010 ms Stripped-down functions: 21010 ms I don't think these are fair comparisons though. The C locale probably short-circuits much of the work that strxfrm/strcoll have to do for other locales. I think the fair comparison is to compare a database initdb'd in a non-C locale like en_US using strcoll with no setlocale calls against one calling setlocale twice for every record. OK, that's fair: In en_US locale:1900 ms which makes it only a factor of 20 slowdown to use setlocale every time... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] userlock changes for 8.1/8.2
Ok, With 8.0 out the door, I'd like to reopen discussion re: userlock contrib. module and propose to get it moved into the core database sources. This was discussed a bit a few months back but it never made it officially to the todo list. I'm not sure what the final consensus was on the shared locks discussion, but it might be a good idea to consider user locks at the same time (and vise versa), since they might invoke related changes to the documentation, etc. At the very least, I'd like to know that if properly documented, userlocks stand a chance of being upgraded to the core project. First, a little about the current userlock module: 1. It is GPL. However, the module is nothing more than a couple of wrappers to the backend functions LockAcquire, LockRelease, etc. and some documentation. I'm suggesting to recode the wrappers and redo the documentation as well in the BSD license. 2. userlocks provide a very powerful and high performance method of row level locking. With a little bit of clever coding, they can do other tricks... 3. The current system view, pg_locks, does not display enough information about user locks...I'd suggest either expanding the current view and/or adding a new view, pg_user_locks (along with new function in lockfuncs.c) 4. Current user locks implementation is missing (at least 2) important features...1. ability of superuser to kill a lock owned by another user (corollary: does lockrelease support this?), and 2. the ability to deal with the full 48 bit lock as a single datum (new system type?). Would also be nice to have a lockmode that waits for a lock for a period of time. 5. Need lots of documentation changes...would like to beef up section covering lock module, plus better description of runtime setting 'max_locks_per_transation' including a possible rename. Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Some things I like to pick from the TODO list ...
3) Allow GRANT/REVOKE permissions to be applied to all schema objects with one Maybe this is apply schema changes to several objects with one command. This seems reasonable. Well, I don't know. IMO, what I would really like to see is for various database objects to inherit permissions from the schema. The ability to GRANT to all tables in a schema, etc. is almost trivial to implement in plpgsql/dynamic sql currently. However the major annoyance with that approach is that I have to remember to re-grant permissions to tables in a schema after running that function. As worded, the TODO does not solve that problem. IOW, I would really like to see the TODO to be reworded 'allow schema objects to inherit permissions from the schema' or something like this... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Proposed TODO: fetch-INT8
I was browsing the TODO list and I noticed the todo to bump limit/offset to int8. IMO, the flavors of fetch that take a numeric parameter need this as well. FWIW, trying to pass integer 2^31 to fetch gives a syntax error, which is clearly wrong. Merlin ---(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] Proposed TODO: fetch-INT8
Is there a practical use for retrieving 2^31 records at once? (this is a serious question, I'm not arguing that it should cause a syntax error) Regards, Jeff Davis On Mon, 2005-01-24 at 14:13 -0500, Merlin Moncure wrote: I was browsing the TODO list and I noticed the todo to bump limit/offset to int8. IMO, the flavors of fetch that take a numeric parameter need this as well. FWIW, trying to pass integer 2^31 to fetch gives a syntax error, which is clearly wrong. Merlin ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed TODO: fetch-INT8
Jeff wrote: Is there a practical use for retrieving 2^31 records at once? (this is a serious question, I'm not arguing that it should cause a syntax error) Regards, Jeff Davis On Mon, 2005-01-24 at 14:13 -0500, Merlin Moncure wrote: I was browsing the TODO list and I noticed the todo to bump limit/offset to int8. IMO, the flavors of fetch that take a numeric parameter need this as well. FWIW, trying to pass integer 2^31 to fetch gives a syntax error, which is clearly wrong. No, but don't forget about relative positioning: fetch relative c from huge_cursor; Could make a reasonable case for that. Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] In NYC Monday to Wednesday
FYI, I am doing training for SRA in New York City today (Monday) until Wednesday. I might be behind in reading email and responding. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Goals for 8.1
On Sun, 23 Jan 2005, Benjamin Arai wrote: What are the goals for 8.1? Replace ARC ... anything else is a bonus ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Concurrent free-lock
On Mon, 2005-01-24 at 08:35 -0700, Jonah H. Harris wrote: Lock free data structures are cool... but not really applicable to databases. They have a high maintenance overhead, severe complexity, and will fail when there are many concurrent inserts/deletes to the structure. Can you elaborate on when they would fail, and why? It might be worth considering lock-free data structures for certain parts of the backend, but I'm skeptical they would be much of a win over locking most of the time. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Concurrent free-lock
Neil, Here is some pretty good info on lock-free structures... I'm pretty sure I tested their code in a multithreaded high-concurrency environment and experienced the problems I was discussing. http://www.cl.cam.ac.uk/Research/SRG/netos/lock-free/ Neil Conway wrote: On Mon, 2005-01-24 at 08:35 -0700, Jonah H. Harris wrote: Lock free data structures are cool... but not really applicable to databases. They have a high maintenance overhead, severe complexity, and will fail when there are many concurrent inserts/deletes to the structure. Can you elaborate on when they would fail, and why? It might be worth considering lock-free data structures for certain parts of the backend, but I'm skeptical they would be much of a win over locking most of the time. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Disallow LOAD to non-superusers.
On January 24, 2005 09:58 am, Tom Lane wrote: Devrim GUNDUZ [EMAIL PROTECTED] writes: Is it time for 7.4.7? I've seen many commits to 7.4 branch nowadays. I just started a discussion about that on the core list ... If we are going to roll a 7.4.7 any chance we can roll the icc compiler fixes -WI (if I recall right) into there as well? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [COMMITTERS] pgsql: Disallow LOAD to non-superusers.
Darcy Buskermolen [EMAIL PROTECTED] writes: If we are going to roll a 7.4.7 any chance we can roll the icc compiler fixes -WI (if I recall right) into there as well? You're going to need to be a lot more specific than that if you want something applied to 7.4.7. A patch against REL7_4_STABLE tip would be good ;-) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Concurrent free-lock
On Mon, 2005-01-24 at 16:50 -0700, Jonah H. Harris wrote: Here is some pretty good info on lock-free structures... I'm pretty sure I tested their code in a multithreaded high-concurrency environment and experienced the problems I was discussing. Fair enough, but my hope would be that those problems were the result of bugs in the implementation rather than some fundamental property of lock-free data structures. A concurrency control mechanism that falls over under concurrent access sounds a little broken, no? -Neil ---(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] Concurrent free-lock
Neil and others, It might be interesting to look at some of the papers by Michael Scott et al. I am not an expert on non-blocking data structures, but the following page seems interesting: http://www.cs.rochester.edu/u/scott/synchronization/ esp. (7) nonblocking dual data structures, which combine lock freedom with condition synchronization; and (8) contention management for software transactional memory In any case, I think only when contention is high the non-blocking algorithms are worth looking at. So can someone shine some light on where the contention might be? Cheers! -Min On Tue, 25 Jan 2005 Neil Conway wrote : On Mon, 2005-01-24 at 16:50 -0700, Jonah H. Harris wrote: Here is some pretty good info on lock-free structures... I'm pretty sure I tested their code in a multithreaded high-concurrency environment and experienced the problems I was discussing. Fair enough, but my hope would be that those problems were the result of bugs in the implementation rather than some fundamental property of lock-free data structures. A concurrency control mechanism that falls over under concurrent access sounds a little broken, no? -Neil ---(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] Shortcut for defining triggers
On Mon, Jan 24, 2005 at 08:12:49AM -0800, David Fetter wrote: On Sun, Jan 23, 2005 at 03:49:22PM -0600, Jim C. Nasby wrote: Sorry if this is old, but I couldn't find it in the archives... How difficult would it be to provide a means to define a trigger in one statement? Something like a combination of CREATE TRIGGER and CREATE FUNCTION? Being able to define them seperately is awesome for generic cases where you can use one function for a bunch of different tables, but it's a pain in the cases where you need a unique trigger for one table. What would you want the function name to default to? What language, or would you want to specify that somehow? Here's a sketch of what such an API might look like: CREATE TRIGGER foo_trg BEFORE INSERT OR UPDATE ON foo_tab FOR EACH ROW EXECUTE PROCEDURE LANGUAGE PLPGSQL (/* params would go here if any */) $$ /* body here */ $$; This would cause a foo_tab_b4_iu_func (how to address namespace collisions?) to be created in the appropriate language with appropriate params, then the foo_trg on the table. Yes, that's what I was thinking. If we wanted to get really clever, theoretically the function wouldn't even need to be named, but of course that would mean having to different sets of trigger code, which is probably a BadIdea(tm). As for the function name, it seems you'd want the trigger name in the function name somewhere. Does SQL:2003 have anything to say about this? Also, what kind of development effort would be involved with an implementation, assuming SQL:2003 doesn't forbid? Does the SQL standard even address triggers that only call a function? PostgreSQL is the only database I've used that does this (all the other ones just have you provide the procedural code you want run when the trigger fires). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(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] userlock changes for 8.1/8.2
Speaking of other tricks and things missing; I'd like to see support for named locks. If you're using locks for something other than row-level locking, it's awkward at best to have to come up with an OID to identify your lock with, and even that doesn't guarantee uniqueness. You're also out of luck if the table you're doing row level locking on doesn't have OIDs. You also can't do direct OID locking on more than one table in an application. I think two different naming facilities would be of use: 1) A namespace for single locks, where each lock has it's own name. 2) A namespace for a set of locks. Another way to look at this would be a namespace for group IDs. I realize that this conflicts with the existing userlock implementation, so it might be necessary to have a second set of locks to support this. On Mon, Jan 24, 2005 at 01:31:34PM -0500, Merlin Moncure wrote: Ok, With 8.0 out the door, I'd like to reopen discussion re: userlock contrib. module and propose to get it moved into the core database sources. This was discussed a bit a few months back but it never made it officially to the todo list. I'm not sure what the final consensus was on the shared locks discussion, but it might be a good idea to consider user locks at the same time (and vise versa), since they might invoke related changes to the documentation, etc. At the very least, I'd like to know that if properly documented, userlocks stand a chance of being upgraded to the core project. First, a little about the current userlock module: 1. It is GPL. However, the module is nothing more than a couple of wrappers to the backend functions LockAcquire, LockRelease, etc. and some documentation. I'm suggesting to recode the wrappers and redo the documentation as well in the BSD license. 2. userlocks provide a very powerful and high performance method of row level locking. With a little bit of clever coding, they can do other tricks... 3. The current system view, pg_locks, does not display enough information about user locks...I'd suggest either expanding the current view and/or adding a new view, pg_user_locks (along with new function in lockfuncs.c) 4. Current user locks implementation is missing (at least 2) important features...1. ability of superuser to kill a lock owned by another user (corollary: does lockrelease support this?), and 2. the ability to deal with the full 48 bit lock as a single datum (new system type?). Would also be nice to have a lockmode that waits for a lock for a period of time. 5. Need lots of documentation changes...would like to beef up section covering lock module, plus better description of runtime setting 'max_locks_per_transation' including a possible rename. Merlin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(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] Concurrent free-lock
On Mon, 2005-01-24 at 19:36 -0600, Min Xu (Hsu) wrote: In any case, I think only when contention is high the non-blocking algorithms are worth looking at. So can someone shine some light on where the contention might be? The major point of contention that has been identified in the past is over the BufMgrLock, which is an LWLock that protects (1) the buffer manager's lookup hash table (2) some aspects of the state of individual buffers themselves (e.g. a buffer's flags and shared refcount -- see the BufferDesc structure). Amazingly, there *are* lock-free hash table algorithms (e.g. [1]), but at first glance they seem pretty complex, and I'm not sure how much they would help (we'd still need some form of synchronization to protect access to buffer flags etc.) I think the better route to fixing this problem is just rethinking how we do locking in the bufmgr. There probably are other points of contention, but I think the BufMgrLock has been the one that has stood out in the past -- if/when that is resolved it will be easier to see what issues remain. -Neil [1] http://www.cs.rug.nl/~wim/mechver/hashtable/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] userlock changes for 8.1/8.2
Jim C. Nasby [EMAIL PROTECTED] writes: Speaking of other tricks and things missing; I'd like to see support for named locks. [ yawn... ] Create a table with a name column, put some rows in it, lock the rows. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Shortcut for defining triggers
On Mon, Jan 24, 2005 at 08:40:30PM -0600, Jim C. Nasby wrote: On Mon, Jan 24, 2005 at 08:12:49AM -0800, David Fetter wrote: On Sun, Jan 23, 2005 at 03:49:22PM -0600, Jim C. Nasby wrote: Sorry if this is old, but I couldn't find it in the archives... How difficult would it be to provide a means to define a trigger in one statement? Something like a combination of CREATE TRIGGER and CREATE FUNCTION? Being able to define them seperately is awesome for generic cases where you can use one function for a bunch of different tables, but it's a pain in the cases where you need a unique trigger for one table. What would you want the function name to default to? What language, or would you want to specify that somehow? Here's a sketch of what such an API might look like: CREATE TRIGGER foo_trg BEFORE INSERT OR UPDATE ON foo_tab FOR EACH ROW EXECUTE PROCEDURE ^ Maybe this should read EXECUTE DYNAMIC PROCEDURE or some such in order to make things easier on the parser. LANGUAGE PLPGSQL (/* params would go here if any */) $$ /* body here */ $$; This would cause a foo_tab_b4_iu_func (how to address namespace collisions?) to be created in the appropriate language with appropriate params, then the foo_trg on the table. Yes, that's what I was thinking. If we wanted to get really clever, theoretically the function wouldn't even need to be named, but of course that would mean having to different sets of trigger code, which is probably a BadIdea(tm). Yes™, It Is®[1]. As for the function name, it seems you'd want the trigger name in the function name somewhere. No matter what you do, there has to be some kind of fallback for namespace collision. How would this work? Does SQL:2003 have anything to say about this? Also, what kind of development effort would be involved with an implementation, assuming SQL:2003 doesn't forbid? Does the SQL standard even address triggers that only call a function? Dunno. SQL:2003 is written in what appears to be Klingon legalese, or possibly ceremonial Navajo. Maybe some of each. PostgreSQL is the only database I've used that does this (all the other ones just have you provide the procedural code you want run when the trigger fires). With Oracle, anyhow, there's a default language: PL/SQL. With PostgreSQL, things are a little more flexible, which takes away the tight integration. This is both good and bad. I'd like to see some way to CALL anonymous blocks of [your favorite PL/], and this might even have something to do with what you're describing. :) Cheers, D [1] It doesn't even depend on what your definition of 'is' is. ;) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] bug w/ cursors and savepoints
Someone at Fujitsu pointed out the following bug in 8.0: begin; savepoint x; create table abc (a int); insert into abc values (5); declare foo cursor for select * from abc; rollback to x; fetch from foo; -- hits an Assert() commit; The stacktrace is: #2 0x0826367b in ExceptionalCondition (conditionName=0x8316544 !(((bool)((relation)-rd_refcnt == 0))), errorType=0x8316004 FailedAssertion, fileName=0x8315f08 /home/neilc/pgsql/src/backend/utils/cache/relcache.c, lineNumber=2118) at /home/neilc/pgsql/src/backend/utils/error/assert.c:51 #3 0x0825cec0 in AtEOSubXact_RelationCache (isCommit=0 '\0', mySubid=2, parentSubid=1) at /home/neilc/pgsql/src/backend/utils/cache/relcache.c:2118 #4 0x080ade30 in AbortSubTransaction () at /home/neilc/pgsql/src/backend/access/transam/xact.c:3407 #5 0x080ac404 in CommitTransactionCommand () at /home/neilc/pgsql/src/backend/access/transam/xact.c:1982 #6 0x081de4ba in finish_xact_command () at /home/neilc/pgsql/src/backend/tcop/postgres.c:1843 #7 0x081dd102 in exec_simple_query (query_string=0x83b6ad4 rollback to x;) at /home/neilc/pgsql/src/backend/tcop/postgres.c:950 So what's happening is that the cursor still holds a reference to the newly-created table, so we can't just blow it away. I don't know the subtransaction code too well, so I'm not sure of the right fix. Comments? -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] userlock changes for 8.1/8.2
On Mon, Jan 24, 2005 at 10:43:40PM -0500, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Speaking of other tricks and things missing; I'd like to see support for named locks. [ yawn... ] Create a table with a name column, put some rows in it, lock the rows. What would guarantee that the OIDs of those rows don't conflict with some other OIDs in the system? BTW, this becomes a real issue if you're trying to write code that is meant to be incorporated into other PostgreSQL applications, which might also be using user_lock. Having a text-based means to identify locks greatly reduces the odds of conflicting with a userlock being used by an existing application. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Concurrent free-lock
On Tue, 25 Jan 2005 Neil Conway wrote : Amazingly, there *are* lock-free hash table algorithms (e.g. [1]), but at first glance they seem pretty complex, and It is a little scary when I read the lock-free hash table algorithm needs a theorem prover to prove its correctness. I'd guess maintaining such code is hard. I'm not sure how much they would help (we'd still need some form of synchronization to protect access to buffer flags etc.) I think the better route to fixing this problem is just rethinking how we do locking in the bufmgr. I completely agree. Ultimately, if a piece of code has true contention, i.e. the contention was not due to coarse-grain locking, then perhaps redesigning the algorithm is a better solution. I certainly have no idea what is the code of the bufmgr looks like. May the problem here be coarse-grain locking? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Built-in casts for ltree
contrib/ltree includes text2ltree and ltree2text functions, but no implicit casts. Is there any particular reason for this? --- ltree.sql.in.orig Mon Jan 24 22:47:01 2005 +++ ltree.sql.inMon Jan 24 22:58:40 2005 @@ -155,10 +155,14 @@ AS 'MODULE_PATHNAME' LANGUAGE 'C' WITH (isstrict,iscachable); +CREATE CAST (ltree AS text) WITH FUNCTION ltree2text(ltree) AS IMPLICIT; + CREATE FUNCTION text2ltree(text) RETURNS ltree AS 'MODULE_PATHNAME' LANGUAGE 'C' WITH (isstrict,iscachable); + +CREATE CAST (text AS ltree) WITH FUNCTION text2ltree(text) AS IMPLICIT; CREATE FUNCTION lca(_ltree) RETURNS ltree -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Much Ado About COUNT(*)
On Mon, 24 Jan 2005 08:28:09 -0700, Jonah H. Harris [EMAIL PROTECTED] wrote: UPDATE pg_user_table_counts SET rowcount = rowcount + 1 WHERE schemaname = this_schemaname AND tablename = TG_RELNAME; This might work for small single user applications. You'll have to keep an eye on dead tuples in pg_user_table_counts though. But as soon as there are several concurrent transactions doing both INSERTs and DELETEs, your solution will in the best case serialise access to test_tbl or it will break down because of deadlocks. Servus Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bug w/ cursors and savepoints
Neil Conway [EMAIL PROTECTED] writes: Someone at Fujitsu pointed out the following bug in 8.0: begin; savepoint x; create table abc (a int); insert into abc values (5); declare foo cursor for select * from abc; rollback to x; fetch from foo; -- hits an Assert() Offhand I'd say this should draw a no such cursor as foo error. I'm too tired to look into why foo still exists after the rollback... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Built-in casts for ltree
Jim C. Nasby [EMAIL PROTECTED] writes: contrib/ltree includes text2ltree and ltree2text functions, but no implicit casts. Is there any particular reason for this? Implicit casts to or from text are evil. 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
