Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, 24 Jan 2007, Neil Conway wrote: On Wed, 2007-01-24 at 13:49 -0500, Tom Lane wrote: 2) once we put this in core we are going to be stuck with supporting its SQL API forever. Are we convinced that this API is the one we want? I don't recall even having seen any proposal or discussion. There has been some prior discussion: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00919.php But I agree that we need considerably more discussion before committing the patch. I'm personally not sold on the need for modifications to the SQL grammar, for example, as opposed to just using a set of SQL-callable functions and some new system catalogs. Another question that would be easier to resolve before the patch is committed is naming: the patch currently uses a mix of "full text" and "tsearch[2]" as the name of the full-text search feature. If we're going to bless this as "the" integrated full-text search in PG, it might make more sense to use "full text search" and "FTS" exclusively. We tried to use full-text search (FTS) in the documentation http://mira.sai.msu.su/~megera/pgsql/ftsdoc/index.html. Tsearch[2] used just for historical notes, which may not go to the official documentation. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch in core patch, for inclusion
Hi there, sorry, if I will a bit verbose - just tried to answer to several postings. On Wed, 24 Jan 2007, Tom Lane wrote: Teodor Sigaev wrote: If there aren't objections then we plan commit patch tomorrow or after tomorrow. This is a fairly large patch and I would like the chance to review it before it goes in --- "we'll commit tomorrow" is not exactly a decent review window. I see your argument, no problem with that. We intentionally announced its availability several weeks ago. Peter Eisentraut <[EMAIL PROTECTED]> writes: I still haven't heard any argument for why this would be necessary or desirable at all, other than that it looks better for marketing reasons, One possible argument for this over the contrib version is a saner approach to dumping and restoring configurations. However, as against that: 1) what's the upgrade path for getting an existing tsearch2 configuration into this implementation? this is a real question and we will prepare UPGRADE notes. 2) once we put this in core we are going to be stuck with supporting its SQL API forever. Are we convinced that this API is the one we want? I don't recall even having seen any proposal or discussion. It was OK for tsearch2's API to change every release while it was in contrib, but the expectation of stability is a whole lot higher for core features. If you're talking about SQL and psql commands, than they are new and we tried to be consistent with existing approach to manage system objects. Any inconsistence we'd be happy to discuss and improve. I don't remember we changed operators and function for a long time, so users of tsearch2 should not be confused. After all, our intention is to meet user's wish to have FTS in PostgreSQL and nothing more. We several times wrote in mailing list that it's too early to move tsearch2 to the pg core, since we consider (that time) it has some scalability problem. GiN was specially developed to solve this problem and it did it. It's de facto standard to have FTS in modern database and it has no difference how you call it - plugin, extension, contrib module or built-in. It's infair to compare approach of commercial DB with postgres, since they have their own marketing police - they charge separately for every extension ! Our usual peer - MySQL has built-in FTS, for example, and I don't see any objections to not have an additional argument for our PR people, since our FTS is a way better. I agree, that requirements for core features should be stronger that for contrib module, especially, for the stability of API. So, let us discuss it. We are open for suggestions for about 6 years :) I Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Recursive Queries
The only code that is usable (and performant) is the CONNECT BY patch made by Evgen Potemkin, It works on production servers on the 8.1.5 I hope that a WITH RECURSIVE will be in the 8.3... but I don't see anybody working on this... (what a shame...) Le mercredi 24 janvier 2007 à 17:27 +, Gregory Stark a écrit : > I'm looking into recursive queries and what it would take to support them in > Postgres. Is anyone else looking at this already? > > Aside from the Oracle-ish syntax were there other objections to the patch as > posted a while back for 7.3 by Evgen Potemkin? > > I have some ideas myself for how to go about this but I'm going to review the > existing patch first. If anyone else has ideas I would like to hear them. > ___ Ce message et les �ventuels documents joints peuvent contenir des informations confidentielles. Au cas o� il ne vous serait pas destin�, nous vous remercions de bien vouloir le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et la soci�t� �mettrice ne peut �tre tenue pour responsable de son contenu.
Re: [HACKERS] [COMMITTERS] pgsql: Fix for plpython functions; return true/false for boolean,
[EMAIL PROTECTED] (Bruce Momjian) writes: > Fix for plpython functions; return true/false for boolean, This patch has broken a majority of the buildfarm. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-patches] [HACKERS] unprivileged contrib and pl install
Jeremy Drake <[EMAIL PROTECTED]> writes: >> I am digging through the code looking at this, and I have a question. As >> far as I can tell, there is currently no owner for a pg_language entry. Er, doh. > Sort of answered my own question, found this comment: > * Note: for now, languages are treated as owned by the bootstrap > * user. We should add an owner column to pg_language instead. > So in the course of implementing this, an owner column would probably need > to be added to pg_language, I guess. If you believe my idea that the DB owner ought to have special privilege in this regard, then probably yes. Alternatively, we could hard-wire the treatment of the DB owner. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_trigger.tgargs needs detoast
Patch applied. Thanks.
Backpached to 8.2.X. If it needs to be backpatched to older releases,
someone needs to research that.
---
Kenji Kawamura wrote:
>Hello,
>
>This patch fixes a bug of case of extraction of pg_trigger.tgargs.
>There was a problem when we used a long argument in defining trigger,
> possibly resulting in a server crash.
>
> Example:
>
>We defined a CREATE TRIGGER such as follows and registered trigger.
>In this case, the argument value which we received in the trigger
> procedure was not right.
>
> CREATE TRIGGER trigger_test BEFORE INSERT OR UPDATE ON sample FOR EACH
> ROW EXECUTE PROCEDURE sample_trig('XXX...(more than 1823 characters)');
>
>The trigger procedure which receives the argument:
>
> Datum sample_trig(PG_FUNCTION_ARGS)
> {
> TriggerData* trigdata = (TriggerData*)fcinfo->context;
> char** args = trigdata->tg_trigger->tgargs;
> int nargs = trigdata->tg_trigger->tgnargs;
>
> int i;
> for (i = 0; i < nargs; i++) {
> elog(LOG, "%s", args[i]);
> }
> ...
> }
>
> Result:
>
>Before: LOG: (the character that is not right, for example '%')
>After : LOG: XXX...(more than 1823 characters)
>
> Regards,
>
> ---
> Kenji Kawamura
> NTT Open Source Center, Japan
>
> Index: src/backend/commands/tablecmds.c
> ===
> --- src/backend/commands/tablecmds.c (HEAD)
> +++ src/backend/commands/tablecmds.c (modified)
> @@ -1800,8 +1800,7 @@
>* line; so does trigger.c ...
>*/
> tgnargs = pg_trigger->tgnargs;
> - val = (bytea *)
> - DatumGetPointer(fastgetattr(tuple,
> + val = DatumGetByteaP(fastgetattr(tuple,
>
> Anum_pg_trigger_tgargs,
>
> tgrel->rd_att, &isnull));
> if (isnull || tgnargs < RI_FIRST_ATTNAME_ARGNO ||
> Index: src/backend/commands/trigger.c
> ===
> --- src/backend/commands/trigger.c(HEAD)
> +++ src/backend/commands/trigger.c(modified)
> @@ -906,8 +906,7 @@
> char *p;
> int i;
>
> - val = (bytea *)
> - DatumGetPointer(fastgetattr(htup,
> + val = DatumGetByteaP(fastgetattr(htup,
>
> Anum_pg_trigger_tgargs,
>
> tgrel->rd_att, &isnull));
> if (isnull)
> Index: src/backend/utils/adt/ruleutils.c
> ===
> --- src/backend/utils/adt/ruleutils.c (HEAD)
> +++ src/backend/utils/adt/ruleutils.c (modified)
> @@ -521,8 +521,7 @@
> char *p;
> int i;
>
> - val = (bytea *)
> - DatumGetPointer(fastgetattr(ht_trig,
> + val = DatumGetByteaP(fastgetattr(ht_trig,
>
> Anum_pg_trigger_tgargs,
>
> tgrel->rd_att, &isnull));
> if (isnull)
>
> ---(end of broadcast)---
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fix for bug in plpython bool type conversion
Patch applied. Thanks.
---
Guido Goldstein wrote:
> Hi!
>
> The attached patch fixes a bug in plpython.
>
> This bug was found while creating sql from trigger functions
> written in plpython and later running the generated sql.
> The problem was that boolean was was silently converted to
> integer, which is ok for python but fails when the created
> sql is used.
>
> The patch uses the Py_RETURN_xxx macros shown at
> http://docs.python.org/api/boolObjects.html .
>
> It would be nice if someone could test and comment
> on the patch.
>
> Cheers
> Guido
> --- postgresql-8.2.1.orig/src/pl/plpython/plpython.c 2006-11-21
> 22:51:05.0 +0100
> +++ postgresql-8.2.1/src/pl/plpython/plpython.c 2007-01-17
> 18:06:58.185497734 +0100
> @@ -1580,8 +1580,8 @@
> PLyBool_FromString(const char *src)
> {
> if (src[0] == 't')
> - return PyInt_FromLong(1);
> - return PyInt_FromLong(0);
> + Py_RETURN_TRUE;
> + Py_RETURN_FALSE;
> }
>
> static PyObject *
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [PERFORM] how to plan for vacuum?
On Thu, Jan 25, 2007 at 12:52:02AM -0300, Alvaro Herrera wrote: > Jim C. Nasby wrote: > > > I'll generally start with a cost delay of 20ms and adjust based on IO > > utilization. > > I've been considering set a default autovacuum cost delay to 10ms; does > this sound reasonable? For a lightly loaded system, sure. For a heavier load that might be too much, but of course that's very dependent on not only your hardware, but how much you want vacuum to interfere with normal operations. Though, I'd say as a default it's probably better to be more aggressive rather than less. Also, it might be better to only set autovac_cost_delay by default; presumably if someone's running vacuum by hand they want it done pronto. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PERFORM] how to plan for vacuum?
Jim C. Nasby wrote: > I'll generally start with a cost delay of 20ms and adjust based on IO > utilization. I've been considering set a default autovacuum cost delay to 10ms; does this sound reasonable? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-patches] [HACKERS] unprivileged contrib and pl install
On Wed, 24 Jan 2007, Jeremy Drake wrote: > I am digging through the code looking at this, and I have a question. As > far as I can tell, there is currently no owner for a pg_language entry. > Is this correct or is ownership information stored somewhere other than > the pg_language relation? Are you suggesting that a lanowner column would > need to be added? > Sort of answered my own question, found this comment: * Note: for now, languages are treated as owned by the bootstrap * user. We should add an owner column to pg_language instead. So in the course of implementing this, an owner column would probably need to be added to pg_language, I guess. -- If a 6600 used paper tape instead of core memory, it would use up tape at about 30 miles/second. -- Grishman, Assembly Language Programming ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-patches] [HACKERS] unprivileged contrib and pl install
On Wed, 24 Jan 2007, Tom Lane wrote: > [ redirecting thread from -patches to -hackers for wider comment ] > > Jeremy Drake <[EMAIL PROTECTED]> writes: > > On Wed, 24 Jan 2007, Tom Lane wrote: > >> Note I'm not arguing against allowing it to be "on" by default, I just > >> want to be sure there is a way for paranoid DBAs to turn it off. Maybe > >> it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate" > >> was the only way to manipulate it --- we've gotten along with treating > >> datistemplate and datallowconn that way. > > > That sounds reasonable to me. I'll try to put together a patch like this > > (adding a boolean column to pg_pltemplate) and see if this is acceptable. > > I assume that only superusers can modify pg_pltemplate already ;) > > I had a further thought about this: if we allow random users to create > languages, then without any further tweaking the instance of the > language in their DB would be owned by them and they could grant or deny > USAGE on it to others in their DB. This is probably not good. Given > the current structure of pg_language, a language is effectively a > one-time-per-DB resource and so random users could obstruct others from > using a language. > > Perhaps it'd make sense to limit this to the DB owner, who would then be > able to grant or deny language usage to the other users in his database. > I am digging through the code looking at this, and I have a question. As far as I can tell, there is currently no owner for a pg_language entry. Is this correct or is ownership information stored somewhere other than the pg_language relation? Are you suggesting that a lanowner column would need to be added? As far as the column name referred to below as "pg_pltemplate.something", for now I am calling it tmpldbaallowed. I am not particularly attached to nor fond of that name, however, and am open to naming suggestions. > In detail, it'd look something like: > > * For an untrusted language: must be superuser to either create or use > the language (no change from current rules). Ownership of the > pg_language entry is really irrelevant, as is its ACL. > > * For a trusted language: > > * if pg_pltemplate.something is ON: either a superuser or the current > DB's owner can CREATE the language. In either case the pg_language > entry will be marked as owned by the DB owner (pg_database.datdba), > which means that subsequently he (or a superuser) can grant or deny > USAGE within his DB. > > * if pg_pltemplate.something is OFF: must be superuser to CREATE the > language; subsequently it will be owned by you, so only you or another > superuser can grant or deny USAGE (same behavior as currently). > > Comments? The bit about assigning the datdba as the owner might seem > a bit odd, but I'm worried about the case where someone has the DBA > privilege as a role but issues the create under his own ID. If it's > owned directly by him, you'd end up in a situation where other holders > of the DBA role couldn't manipulate the language, which seems > undesirable. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > > -- Save the Whales -- Harpoon a Honda. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Help with Degree Work
You should take a look at http://pgfoundry.org/projects/qbe, which deals with querying data by providing sample data that matches what you're looking for. On Wed, Jan 24, 2007 at 01:40:04PM -0400, Werner Echezuria wrote: > Hello, > > I've suscribed to this mailing list for help, I will work on a > Specialization Degree Thesis, this will be a PostgreSQL implementation of > fsql, or fuzzy querys. > > http://www.lcc.uma.es/~ppgg/FSQL.html, this is a link to a webpage who made > this in Oracle, but it's not inside of course, because it was made for other > people. > > My work will be make this in the PostgreSQL Kernell, so i'll appreciate all > help you can bring. > > Thanks. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] TODO list has removed developer names
I have removed the developer names from the bottom of the TODO list now that URLs are used to reference discussions. The URLs are much more accurate than putting names on items. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-patches] [HACKERS] unprivileged contrib and pl install (formerly tsearch
[ redirecting thread from -patches to -hackers for wider comment ] Jeremy Drake <[EMAIL PROTECTED]> writes: > On Wed, 24 Jan 2007, Tom Lane wrote: >> Note I'm not arguing against allowing it to be "on" by default, I just >> want to be sure there is a way for paranoid DBAs to turn it off. Maybe >> it'd be sufficient if the flag bit was there but "UPDATE pg_pltemplate" >> was the only way to manipulate it --- we've gotten along with treating >> datistemplate and datallowconn that way. > That sounds reasonable to me. I'll try to put together a patch like this > (adding a boolean column to pg_pltemplate) and see if this is acceptable. > I assume that only superusers can modify pg_pltemplate already ;) I had a further thought about this: if we allow random users to create languages, then without any further tweaking the instance of the language in their DB would be owned by them and they could grant or deny USAGE on it to others in their DB. This is probably not good. Given the current structure of pg_language, a language is effectively a one-time-per-DB resource and so random users could obstruct others from using a language. Perhaps it'd make sense to limit this to the DB owner, who would then be able to grant or deny language usage to the other users in his database. In detail, it'd look something like: * For an untrusted language: must be superuser to either create or use the language (no change from current rules). Ownership of the pg_language entry is really irrelevant, as is its ACL. * For a trusted language: * if pg_pltemplate.something is ON: either a superuser or the current DB's owner can CREATE the language. In either case the pg_language entry will be marked as owned by the DB owner (pg_database.datdba), which means that subsequently he (or a superuser) can grant or deny USAGE within his DB. * if pg_pltemplate.something is OFF: must be superuser to CREATE the language; subsequently it will be owned by you, so only you or another superuser can grant or deny USAGE (same behavior as currently). Comments? The bit about assigning the datdba as the owner might seem a bit odd, but I'm worried about the case where someone has the DBA privilege as a role but issues the create under his own ID. If it's owned directly by him, you'd end up in a situation where other holders of the DBA role couldn't manipulate the language, which seems undesirable. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [GENERAL] Autovacuum Improvements
Kenneth Marshall <[EMAIL PROTECTED]> writes: > Not that I am aware of. Even extending the relation by one additional > block can make a big difference in performance Do you have any evidence to back up that assertion? It seems a bit nontrivial to me --- not the extension part exactly, but making sure that the space will get used promptly. With the current code the backend extending a relation will do subsequent inserts into the block it just got, which is fine, but there's no mechanism for remembering that any other newly-added blocks are available --- unless you wanted to push them into the FSM, which could work but the current FSM code doesn't support piecemeal addition of space, and in any case there's some question in my mind about the concurrency cost of increasing FSM traffic even more. In short, it's hardly an unquestionable improvement, so we need some evidence. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] New feature proposal
Sorin Schwimmer <[EMAIL PROTECTED]> writes: > My suggestion is to allow INSERT to do it REPEAT x. You can do that today. INSERT INTO foo SELECT const1,const2,... FROM generate_series(1,1000); regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New feature proposal
On Wed, 24 Jan 2007, Sorin Schwimmer wrote: > Dear Developers, > > I would like to suggest the inclusion of an extension > in PostgreSQL. There are instances, I found, when one > needs to INSERT several times the same record in a > table. The front-end application can do it easy in a > loop of a sort, but on remote servers (and that's the > norm these days) it creates unnecessary network > traffic. > > My suggestion is to allow INSERT to do it REPEAT x. > This should allow, in my view, the followings: > a) INSERT INTO my_table (field1, field2, field3) >VALUES (value1, value2, value3) REPEAT 5; postgres=# create table baz (i int, j text); CREATE TABLE postgres=# insert into baz (i, j) select 1, 'hello' from generate_series(1, 5); INSERT 0 5 postgres=# select * from baz; i | j ---+--- 1 | hello 1 | hello 1 | hello 1 | hello 1 | hello (5 rows) > b) INSERT INTO my_table (field1, field2, field3) >VALUES (x, value2/x, value3) REPEAT (x=3); > should insert the followings: > 1, value2, value3 > 2, value2/2, value3 > 3, value2/3, value3 Yuk! Besides, it can be done similarly to the above. > This suggestion comes for a practical project that I > have. Well, the good thing is, you can use generate_series() now! :-) Thanks, Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] New feature proposal
On Wed, 2007-01-24 at 08:26 -0800, Sorin Schwimmer wrote: > The front-end application can do it easy in a > loop of a sort, but on remote servers (and that's the > norm these days) it creates unnecessary network > traffic. You can avoid this easily via a stored procedure. > My suggestion is to allow INSERT to do it REPEAT x. We generally try to avoid non-standard extensions to SQL to accomplish things that can be comfortably expressed in standard SQL, or via the existing Postgres constructs (e.g. PL/PgSQL stored procedures). -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Autovacuum Improvements
On Mon, Jan 22, 2007 at 05:11:03PM -0600, Jim C. Nasby wrote: > On Mon, Jan 22, 2007 at 12:17:39PM -0800, Ron Mayer wrote: > > Gregory Stark wrote: > > > > > > Actually no. A while back I did experiments to see how fast reading a file > > > sequentially was compared to reading the same file sequentially but > > > skipping > > > x% of the blocks randomly. The results were surprising (to me) and > > > depressing. > > > The breakeven point was about 7%. [...] > > > > > > The theory online was that as long as you're reading one page from each > > > disk > > > track you're going to pay the same seek overhead as reading the entire > > > track. > > > > Could one take advantage of this observation in designing the DSM? > > > > Instead of a separate bit representing every page, having each bit > > represent 20 or so pages might be a more useful unit. It sounds > > like the time spent reading would be similar; while the bitmap > > would be significantly smaller. > > If we extended relations by more than one page at a time we'd probably > have a better shot at the blocks on disk being contiguous and all read > at the same time by the OS. > -- > Jim Nasby[EMAIL PROTECTED] > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > Yes, most OS have some read-ahead when reading a file from disk. Any increment over 1 would be an improvement. If you used a counter with a time-based decrement function, you could increase the amount that the relation is extended based on temporal proximity. If you have extended it several times recently, increase the size of the new extension to reduce the overhead even further. The default should be approximately the OS standard read-ahead amount. Ken ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] New feature proposal
Dear Developers, I would like to suggest the inclusion of an extension in PostgreSQL. There are instances, I found, when one needs to INSERT several times the same record in a table. The front-end application can do it easy in a loop of a sort, but on remote servers (and that's the norm these days) it creates unnecessary network traffic. My suggestion is to allow INSERT to do it REPEAT x. This should allow, in my view, the followings: a) INSERT INTO my_table (field1, field2, field3) VALUES (value1, value2, value3) REPEAT 5; should insert 5 identical rows b) INSERT INTO my_table (field1, field2, field3) VALUES (x, value2/x, value3) REPEAT (x=3); should insert the followings: 1, value2, value3 2, value2/2, value3 3, value2/3, value3 In other words, this form of INSERT shold instruct the SQL engine to perform a for loop. Maybe instead of REPEAT there should be another word, maybe the for loop should allow going with a different step than the default one and go backward as well (easy to do with a negative step). This suggestion comes for a practical project that I have. I haven't feel the need for something like that in an UPDATE, but I can imagine that it may happen: UPDATE my_table SET field1=value1*x WHERE condition REPEAT (x=(SELECT repetition FROM table2 WHERE condition2) STEP -1.5); Best regards, Sorin Schwimmer Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Autovacuum Improvements
On Tue, Jan 23, 2007 at 09:01:41PM -0600, Jim Nasby wrote: > On Jan 22, 2007, at 6:53 PM, Kenneth Marshall wrote: > >The default should > >be approximately the OS standard read-ahead amount. > > Is there anything resembling a standard across the OSes we support? > Better yet, is there a standard call that allows you to find out what > the read-ahead setting is? > -- > Jim Nasby[EMAIL PROTECTED] > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > Not that I am aware of. Even extending the relation by one additional block can make a big difference in performance and should easily fall within every read-ahead in use today. Or a GUC variable, that defaults to a small power of 2 number of PostgreSQL blocks, with a default arrived at by testing. Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, 24 Jan 2007, Martijn van Oosterhout wrote: > On Wed, Jan 24, 2007 at 09:38:06PM +0100, Stefan Kaltenbrunner wrote: > > sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 in > > the current version is actually imposing some additional(often > > non-trivial) complexity for things like database restores and upgrades > > so I can see an ISP wanting to avoid that altogether. > > Something I've wondered about before is the concept of having installed > Modules in the system. Let's say for example that while compiling > postgres it compiled the modules in contrib also and installed them in > a modules directory. > > Once installed there, unpriviledged users could say "INSTALL foo" and > it would install the module, even if they do not have the permissions > to create them themselves. That would be great, and also it would be great to be able to CREATE LANGUAGE as a regular user for a trusted pl that is already compiled/installed. > > That way you don't clutter the catalogs with external projects, and > there is some indication from the postgres team of some trust in these > modules. After all, if the installation made it easy to use for users, > it must be safe, right? Essentially, I think they are just pretty reluctant to run commands as a superuser on behalf of a user... -- It is better never to have been born. But who among us has such luck? One in a million, perhaps. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] tsearch in core patch, for inclusion
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: IIRC Tom's main objection to the previous proposal was that it involved large grammar changes, which I understand is not now proposed. No, they're already in there --- the patch seems to have been written according to that proposal despite the objections. Oh. ouch. That seems strange given this query from Oleg back on 18 Nov: So, if we'll not touch grammar, are there any issues with tsearch2 in core ? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, Jan 24, 2007 at 09:38:06PM +0100, Stefan Kaltenbrunner wrote: > sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 in > the current version is actually imposing some additional(often > non-trivial) complexity for things like database restores and upgrades > so I can see an ISP wanting to avoid that altogether. Something I've wondered about before is the concept of having installed Modules in the system. Let's say for example that while compiling postgres it compiled the modules in contrib also and installed them in a modules directory. Once installed there, unpriviledged users could say "INSTALL foo" and it would install the module, even if they do not have the permissions to create them themselves. That way you don't clutter the catalogs with external projects, and there is some indication from the postgres team of some trust in these modules. After all, if the installation made it easy to use for users, it must be safe, right? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] tsearch in core patch, for inclusion
Andrew Dunstan <[EMAIL PROTECTED]> writes: > IIRC Tom's main objection to the previous proposal was that it involved > large grammar changes, which I understand is not now proposed. No, they're already in there --- the patch seems to have been written according to that proposal despite the objections. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tsearch in core patch, for inclusion
Neil Conway wrote: If people had a problem with integrating tsearch2 in core they should have said so much earlier. Peter, Tom and others raised essentially identical objections when this design was initially proposed. For example: http://archives.postgresql.org/pgsql-hackers/2006-11/msg00392.php http://archives.postgresql.org/pgsql-hackers/2006-11/msg00405.php http://archives.postgresql.org/pgsql-hackers/2006-11/msg00437.php http://archives.postgresql.org/pgsql-hackers/2006-11/msg00397.php Was a consensus reached in that thread? (I didn't see one, but perhaps I've overlooked a mail.) IIRC Tom's main objection to the previous proposal was that it involved large grammar changes, which I understand is not now proposed. The way I read that thread was that there was no strenuous objection apart from the grammar parts. Certainly I think we can still argue about details, such as the functional API. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] "tupdesc reference is not owned by resource owner Portal"
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> I think the proper fix is probably to establish a new eval_context
>> when we enter an EXCEPTION block, and destroy it again on the way out.
>> Slightly annoying, but probably small next to the other overhead of
>> a subtransaction. Comments?
> we use exception blocks heavily here so anything that makes them slower
> is not nice but if it fixes the issue at hand I'm all for it ...
This turned out a bit uglier than I thought --- the real problem is that
plpgsql's "simple eval econtext" management is much too stupid to
survive in a subtransaction world. There was a comment in the code
worrying about this, but I guess we never investigated closely.
The attached patch (against 8.2) appears to fix the reported problem,
but it could use some more testing before I push it into the stable
branches. Can you try it in the production situation that exposed the
problem? Aside from not failing, do you see any performance loss?
regards, tom lane
Index: pl_exec.c
===
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.180
diff -c -r1.180 pl_exec.c
*** pl_exec.c 4 Oct 2006 00:30:13 - 1.180
--- pl_exec.c 24 Jan 2007 21:46:33 -
***
*** 37,51
static const char *const raise_skip_msg = "RAISE";
-
/*
! * All plpgsql function executions within a single transaction share
! * the same executor EState for evaluating "simple" expressions. Each
! * function call creates its own "eval_econtext" ExprContext within this
! * estate.We destroy the estate at transaction shutdown to ensure there
! * is no permanent leakage of memory (especially for xact abort case).
! */
! static EState *simple_eval_estate = NULL;
/
* Local function forward declarations
--- 37,69
static const char *const raise_skip_msg = "RAISE";
/*
! * All plpgsql function executions within a single transaction share the same
! * executor EState for evaluating "simple" expressions. Each function call
! * creates its own "eval_econtext" ExprContext within this estate for
! * per-evaluation workspace. eval_econtext is freed at normal function exit,
! * and the EState is freed at transaction end (in case of error, we assume
! * that the abort mechanisms clean it all up). In order to be sure
! * ExprContext callbacks are handled properly, each subtransaction has to have
! * its own such EState; hence we need a stack. We use a simple counter to
! * distinguish different instantiations of the EState, so that we can tell
! * whether we have a current copy of a prepared expression.
! *
! * This arrangement is a bit tedious to maintain, but it's worth the trouble
! * so that we don't have to re-prepare simple expressions on each trip through
! * a function. (We assume the case to optimize is many repetitions of a
! * function within a transaction.)
! */
! typedef struct SimpleEstateStackEntry
! {
! EState *xact_eval_estate; /* EState for
current xact level */
! long intxact_estate_simple_id; /* ID for
xact_eval_estate */
! SubTransactionId xact_subxid; /* ID for current
subxact */
! struct SimpleEstateStackEntry *next;/* next stack entry up */
! } SimpleEstateStackEntry;
!
! static SimpleEstateStackEntry *simple_estate_stack = NULL;
! static long int simple_estate_id_counter = 0;
/
* Local function forward declarations
***
*** 154,159
--- 172,178
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
+ static void plpgsql_create_econtext(PLpgSQL_execstate *estate);
static void free_var(PLpgSQL_var *var);
***
*** 892,897
--- 911,919
*/
MemoryContext oldcontext = CurrentMemoryContext;
ResourceOwner oldowner = CurrentResourceOwner;
+ ExprContext *old_eval_econtext = estate->eval_econtext;
+ EState *old_eval_estate = estate->eval_estate;
+ long intold_eval_estate_simple_id =
estate->eval_estate_simple_id;
BeginInternalSubTransaction(NULL);
/* Want to run statements inside function's memory context */
***
*** 899,904
--- 921,935
PG_TRY();
{
+ /*
+* We need to run the block's statements with a new
eval_econtext
+* that belongs to the current subtransaction; if we
try to use
+* the outer econtext then E
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, 2007-01-24 at 18:38 -0300, Alvaro Herrera wrote: > In any case, I agree with Andrew that it would be pretty dumb to reject > a funded, already written patch. Well, there are two separate issues: should we include tsearch2 in core, and what syntax should it use? Changing the syntax would not require rejecting the entire patch. > If people had a problem with integrating tsearch2 in core they should > have said so much earlier. Peter, Tom and others raised essentially identical objections when this design was initially proposed. For example: http://archives.postgresql.org/pgsql-hackers/2006-11/msg00392.php http://archives.postgresql.org/pgsql-hackers/2006-11/msg00405.php http://archives.postgresql.org/pgsql-hackers/2006-11/msg00437.php http://archives.postgresql.org/pgsql-hackers/2006-11/msg00397.php Was a consensus reached in that thread? (I didn't see one, but perhaps I've overlooked a mail.) -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch in core patch, for inclusion
Joshua D. Drake wrote: > Peter Eisentraut wrote: > > I wrote: > >> The closest I could find is Oracle Text, the full-text search for > >> Oracle. > > > > Oh, and note that Oracle Text is an "extension" and not included in the > > Oracle database product proper. > > Cool. Then we will have yet another reason to claim we are superior. It's probably separate just so they can charge extra for it ;-) In our case it's going to be free either way. In any case, I agree with Andrew that it would be pretty dumb to reject a funded, already written patch. If people had a problem with integrating tsearch2 in core they should have said so much earlier. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch in core patch, for inclusion
Peter Eisentraut wrote: > I wrote: >> The closest I could find is Oracle Text, the full-text search for >> Oracle. > > Oh, and note that Oracle Text is an "extension" and not included in the > Oracle database product proper. Cool. Then we will have yet another reason to claim we are superior. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch in core patch, for inclusion
I wrote: > The closest I could find is Oracle Text, the full-text search for > Oracle. Oh, and note that Oracle Text is an "extension" and not included in the Oracle database product proper. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
Stefan Kaltenbrunner wrote:
> I think one can find arguments for both variants - one of the
> question might even be how other databases are doing that and if the
> proposed syntax is resembling one of those or not.
The closest I could find is Oracle Text, the full-text search for
Oracle. Browsing the documentation I see things like
exec ctx_ddl.create_preference('myjlexer','japanese_lexer');
exec ctx_ddl.add_stopword('globallist','the','French');
which look pretty similar to what a procedure-based interface to
tsearch2 could look like.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch in core patch, for inclusion
Peter Eisentraut wrote: > Stefan Kaltenbrunner wrote: >> sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 >> in the current version is actually imposing some additional(often >> non-trivial) complexity for things like database restores and >> upgrades so I can see an ISP wanting to avoid that altogether. > > I have never used tsearch2 across an upgrade, so what exactly are those > problems and why would they be specific to tsearch2? Tsearch2 changes things occasionally from release to release which make upgrades impossible with a standard pg_dump/pg_restore. I would have to double check (because I always work around the problem now) but IIRC there have been function call changes that are different from one release to the next. Sincerely, Joshua D. Drake > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch in core patch, for inclusion
Stefan Kaltenbrunner wrote: > sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 > in the current version is actually imposing some additional(often > non-trivial) complexity for things like database restores and > upgrades so I can see an ISP wanting to avoid that altogether. I have never used tsearch2 across an upgrade, so what exactly are those problems and why would they be specific to tsearch2? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch in core patch, for inclusion
Andrew Dunstan wrote: > Joshua D. Drake wrote: >> Where on the website can I see what "plugins" are included with >> PostgreSQL? YES! That's IMHO a more fundamental problem. The specific question about Text Search seems more like a symptom. While I don't mind Text Search in core, it seems an even bigger deal that it's hard to find information on extensions (whether from contrib or from gborg or from external places like postgis). A web page with a table easily visible on the postgresql web site that had Extension (i.e. tsearch2, postgis) Project Maturity (i.e. alpha/beta/stable) Compatability (i.e. extension 1.0 works with postgresql 8.2) Description (i.e. "full text search") URL would be a partial fix. > contrib is a horrible misnomer. Can we maybe bite the bullet and call it > something else? +1 How about "plugins" or "extensions" or "optional libraries". ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch in core patch, for inclusion
Peter Eisentraut wrote: > Jeremy Drake wrote: >> I for one am greatly looking forward to tsearch2 being in core. I >> was very fond of the plugin mechanism, until I signed up with a >> hosting provider. > > Yes, you have told us about your hosting provider before. Just make > sure your next hosting provider does not refuse to install database > objects whose OID is a multiple of 13 because of bad luck, or you might > miss out on full-text indexing again. sure that ISP is a bit stupid(especially wrt plpgsql) - but tsearch2 in the current version is actually imposing some additional(often non-trivial) complexity for things like database restores and upgrades so I can see an ISP wanting to avoid that altogether. A fully integrated fulltext search could make that much easier(in a few years when most distributions have picked up 8.3) and just telling people they should switch their hosting ISP is not always an immediatly workable solution (think contracts,migration costs,legacy apps). Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > Neil Conway wrote: >> Another question that would be easier to resolve before the patch is >> committed is naming: the patch currently uses a mix of "full text" and >> "tsearch[2]" as the name of the full-text search feature. If we're going >> to bless this as "the" integrated full-text search in PG, it might make >> more sense to use "full text search" and "FTS" exclusively. > making this consistent makes a lot of sense and I agree that it might be > a good idea to just call it FTS (or similiar). > But on the other side would have to go as far as renaming > TSVECTOR/TSQUERY to FTSVECTOR/FTSQUERY or similiar which might pose some > considerable headache for people upgrading from the contrib/ version. If we use "text search" (abbrev TS) as the key phrase we can avoid that. But this reiterates my point that the upgrade path for existing tsearch2 users is an important thing to consider. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch in core patch, for inclusion
Peter Eisentraut wrote: Andrew Dunstan wrote: contrib is a horrible misnomer. Can we maybe bite the bullet and call it something else? plugins? standard-plugins might be more informative. I think of them as being like perl's standard modules, things that are part of the standard perl distribution as opposed to all the other stuff on CPAN. Maybe it needs to split into two - things that are genuine plugins and other stuff (e.g. start-scripts). cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tsearch in core patch, for inclusion
Neil Conway wrote: > On Wed, 2007-01-24 at 13:49 -0500, Tom Lane wrote: >> 2) once we put this in core we are going to be stuck with supporting its >> SQL API forever. Are we convinced that this API is the one we want? >> I don't recall even having seen any proposal or discussion. > > There has been some prior discussion: > > http://archives.postgresql.org/pgsql-hackers/2006-12/msg00919.php > > But I agree that we need considerably more discussion before committing > the patch. I'm personally not sold on the need for modifications to the > SQL grammar, for example, as opposed to just using a set of SQL-callable > functions and some new system catalogs. I think one can find arguments for both variants - one of the question might even be how other databases are doing that and if the proposed syntax is resembling one of those or not. > > Another question that would be easier to resolve before the patch is > committed is naming: the patch currently uses a mix of "full text" and > "tsearch[2]" as the name of the full-text search feature. If we're going > to bless this as "the" integrated full-text search in PG, it might make > more sense to use "full text search" and "FTS" exclusively. making this consistent makes a lot of sense and I agree that it might be a good idea to just call it FTS (or similiar). But on the other side would have to go as far as renaming TSVECTOR/TSQUERY to FTSVECTOR/FTSQUERY or similiar which might pose some considerable headache for people upgrading from the contrib/ version. Stefan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] tsearch in core patch, for inclusion
Neil Conway wrote: > But I agree that we need considerably more discussion before > committing the patch. I'm personally not sold on the need for > modifications to the SQL grammar, for example, as opposed to just > using a set of SQL-callable functions and some new system catalogs. In particular, I would think that unless one is affiliated with The New COBOL World Order, one would *prefer* a set of functions over new SQL statements. And using functions to manage extensions seems to be the established way in Oracle land, if that matters at all. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] tsearch in core patch, for inclusion
Peter Eisentraut wrote: > Jeremy Drake wrote: >> I for one am greatly looking forward to tsearch2 being in core. I >> was very fond of the plugin mechanism, until I signed up with a >> hosting provider. > > Yes, you have told us about your hosting provider before. Just make > sure your next hosting provider does not refuse to install database > objects whose OID is a multiple of 13 because of bad luck, or you might > miss out on full-text indexing again. Well we just turn off OIDs to help prevent that possible curse. Sincerely, Joshua D. Drake > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] tsearch in core patch, for inclusion
Jeremy Drake wrote: > I for one am greatly looking forward to tsearch2 being in core. I > was very fond of the plugin mechanism, until I signed up with a > hosting provider. Yes, you have told us about your hosting provider before. Just make sure your next hosting provider does not refuse to install database objects whose OID is a multiple of 13 because of bad luck, or you might miss out on full-text indexing again. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] tsearch in core patch, for inclusion
Jeff Davis wrote: > On that point, why do we have /contrib? It's for "plugins" that are > so version-dependent that they can't exist as a separate project, as > I understand it. No. (I don't know a better and succinct answer, but that is not it.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch in core patch, for inclusion
Andrew Dunstan wrote: > contrib is a horrible misnomer. Can we maybe bite the bullet and call > it something else? plugins? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
Jeremy Drake wrote: On Wed, 24 Jan 2007, Peter Eisentraut wrote: I still haven't heard any argument for why this would be necessary or desirable at all, other than that it looks better for marketing reasons, which I will counter by saying that it looks worse for marketing reasons because our hailed plugin mechanism is apparently so poor that it can't support some practical extension module such as this. I for one am greatly looking forward to tsearch2 being in core. For goodness' sake! This is work that's been sponsored! Are we going to turn around now and reject it? We'd be a laughing stock. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, 2007-01-24 at 13:49 -0500, Tom Lane wrote: > 2) once we put this in core we are going to be stuck with supporting its > SQL API forever. Are we convinced that this API is the one we want? > I don't recall even having seen any proposal or discussion. There has been some prior discussion: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00919.php But I agree that we need considerably more discussion before committing the patch. I'm personally not sold on the need for modifications to the SQL grammar, for example, as opposed to just using a set of SQL-callable functions and some new system catalogs. Another question that would be easier to resolve before the patch is committed is naming: the patch currently uses a mix of "full text" and "tsearch[2]" as the name of the full-text search feature. If we're going to bless this as "the" integrated full-text search in PG, it might make more sense to use "full text search" and "FTS" exclusively. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch in core patch, for inclusion
Jeremy Drake wrote: > On Wed, 24 Jan 2007, Peter Eisentraut wrote: > >> Teodor Sigaev wrote: >>> If there aren't objections then we plan commit patch tomorrow or >>> after tomorrow. >> I still haven't heard any argument for why this would be necessary or >> desirable at all, other than that it looks better for marketing >> reasons, which I will counter by saying that it looks worse for >> marketing reasons because our hailed plugin mechanism is apparently so >> poor that it can't support some practical extension module such as >> this. > > I for one am greatly looking forward to tsearch2 being in core. I was > very fond of the plugin mechanism, until I signed up with a hosting > provider. I do not have superuser privileges on the database cluster, and > they will not install any plugins due to unspecified "security concerns". You could move to Hub or Command Prompt ;) Joshua D. Drake > So ATM if I want full text indexing, my only choice would be to avail > myself of their mysql instance which has it built in. So I have been > jaded, and my opinion of optional plugins has gone from "wow, this is > neat" to "man, this is a pain". They do not install plpgsql so I cannot > write any triggers, they don't install tsearch2 so I don't get full text > indexing, so all of the great features of postgres I have come to enjoy on > my own box are suddenly taken away :( > > Sorry for the rant, I am just looking forward to 8.3 so I could get full > text indexing... > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, 24 Jan 2007, Peter Eisentraut wrote: > Teodor Sigaev wrote: > > If there aren't objections then we plan commit patch tomorrow or > > after tomorrow. > > I still haven't heard any argument for why this would be necessary or > desirable at all, other than that it looks better for marketing > reasons, which I will counter by saying that it looks worse for > marketing reasons because our hailed plugin mechanism is apparently so > poor that it can't support some practical extension module such as > this. I for one am greatly looking forward to tsearch2 being in core. I was very fond of the plugin mechanism, until I signed up with a hosting provider. I do not have superuser privileges on the database cluster, and they will not install any plugins due to unspecified "security concerns". So ATM if I want full text indexing, my only choice would be to avail myself of their mysql instance which has it built in. So I have been jaded, and my opinion of optional plugins has gone from "wow, this is neat" to "man, this is a pain". They do not install plpgsql so I cannot write any triggers, they don't install tsearch2 so I don't get full text indexing, so all of the great features of postgres I have come to enjoy on my own box are suddenly taken away :( Sorry for the rant, I am just looking forward to 8.3 so I could get full text indexing... -- ARCHDUKE FERDINAND FOUND ALIVE -- FIRST WORLD WAR A MISTAKE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, Jan 24, 2007 at 01:53:54PM -0500, Andrew Dunstan wrote: > Joshua D. Drake wrote: > >Peter Eisentraut wrote: > > > >>Teodor Sigaev wrote: > >> > >>>If there aren't objections then we plan commit patch tomorrow or > >>>after tomorrow. > >>> > >>I still haven't heard any argument for why this would be necessary or > >>desirable at all, other than that it looks better for marketing > >>reasons, which I will counter by saying that it looks worse for > >>marketing reasons because our hailed plugin mechanism is apparently so > >>poor that it can't support some practical extension module such as > >>this. > > > >Of which I will counter that we don't have a hailed plugin mechanism. We > >have a contrib which professionals generally consider untested and not > >part of PostgreSQL. > > > >I am constantly running into this: > > > >Q. Does PostgreSQL have full text indexing? > >A. Yes it is in contrib. > >Q. But that isn't part of core. > >A. *sigh* > > > >Where on the website can I see what "plugins" are included with > >PostgreSQL? > > > >Where on the website can I see the Official PostgreSQL > >Documentation for Full Text Indexing? > > > >With TSearch2 in core will that fix the many upgrade problems > >associated with using TSearch2? > > contrib is a horrible misnomer. Can we maybe bite the bullet and > call it something else? Some version of "version-dependent plugins?" Cheers, D (who hasn't come up with anything shorter just yet) -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] weird buildfarm failures on arm/mipsel and --with-tcl
Tom Lane wrote: > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: >> FWIW - I removed --with-tcl from quagga's configuration about two weeks >> ago and it has not failed(for that reason) again. So the issue most >> definitly looks like plptcl related ... > > It sorta looks like Tcl might be installing an atexit() callback that is > doing the Wrong Thing somehow. What Tcl version do you have installed > exactly, and with what configure options? (The contents of tclConfig.sh > should be reasonably complete info.) version is 8.4.12-1.1(current debian etch package) and tclConfig.sh is at: http://www.kaltenbrunner.cc/files/tclConfig.sh.txt Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
On Wed, 2007-01-24 at 19:15 +0100, Peter Eisentraut wrote: > Teodor Sigaev wrote: > > If there aren't objections then we plan commit patch tomorrow or > > after tomorrow. > > I still haven't heard any argument for why this would be necessary or > desirable at all, other than that it looks better for marketing > reasons, which I will counter by saying that it looks worse for > marketing reasons because our hailed plugin mechanism is apparently so > poor that it can't support some practical extension module such as > this. > On that point, why do we have /contrib? It's for "plugins" that are so version-dependent that they can't exist as a separate project, as I understand it. But what we want when we say we have a plugin mechanism is something more like CPAN, where software is developed on it's own timeline and can be added seamlessly into any version of PostgreSQL that supports the needs of the project. PostGIS is a good example of this. You don't have to wait for a PostgreSQL release to upgrade PostGIS, and they don't have to discuss the intricacies of spatial queries and data on -hackers. If tsearch2 really does need to be in lockstep with the PostgreSQL releases (although I don't see why it does), I don't see a problem putting it in core. It's an important feature, and we're already giving up a lot of the benefits of plugins anyway by distributing it with the project. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Access last inserted tuple info...
On Wed, Jan 24, 2007 at 12:56:14PM -0400, Luis D. Garc?a wrote: > >Hi, I'm working on a modification of PostgreSQL 8.1.4 and I need to access >the >information stored in the last tuple inserted in a table (All this from the >backend >code). >Could anyone please help me on this? >Greetings and Thanks... 8.2 has (INSERT/UPDATE/DELETE) ... RETURNING. Perhaps you could use the 8.2 series instead :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Default permissisons from schemas
* Merlin Moncure ([EMAIL PROTECTED]) wrote: > On 1/24/07, Stephen Frost <[EMAIL PROTECTED]> wrote: > >err, what proposal wasn't touching the GRANT syntax at all but rather > > right, but the original proposal did: > # %Allow GRANT/REVOKE permissions to be applied to all schema objects > with one command > > which was more or less (with the NEW TABLES flavor of the command) > duplicated by: > > # Allow GRANT/REVOKE permissions to be inherited by objects based on > schema permissions These are pretty different things actually, imv.. I don't think it makes sense to use GRANT on something which is clearly a schema property. Would you still track the information in pg_namespace? Nothing else makes sense to me and if it's there I think it's perfectly reasonable to modify a schema property using ALTER SCHEMA. Hacking up GRANT to do it sounds very, very ugly and not intuitive... > and your proposal would make alter schema (and presumably create > schema) the only command(s) that deal with privileges excluding The proposal didn't involve CREATE SCHEMA. I don't really have a strong opinion on that but I'm at least disinclined towards it as being unnecessary. > grant/revoke. That, IMO is actually a bad thing...a surprising > behavior. I think the 'new tables' form is better but has the same > problems as your proposal in that it does not disambiguate sequences > from tables, etc. It would however solve (I think!) your problem > without resorting to ownership delegation. It doesn't seem unsuprising at all to me, especially with appropriate documentation... Having the syntax in GRANT or in ALTER SCHEMA would work for me for the ACLs. I don't see how that distincation does anything to solve the concerns or provide a solution for ownership delegation. Especially considering you can't change ownership with GRANT today... > >I don't think it makes sense to have this syntax be part of the GRANT > syntax since it's really about a schema.. > > So, basically I disagree with the above, and agree with the others wrt > ownership change, but very much agree if it is pratical that having > some mechanism of applying permissions to objects when they are > created depending on which schema they are in is a good thing. Ok. The issue that I have is that some permissions are exclusivly available only to the owner of an object, and it's not possible to grant them. I feel that it should be possible to have those permissions applied to objects when they are created as well... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Recursive Queries
Bruce Momjian wrote: Wasn't somebody else working on this? Jonah? (Maybe you EDB guys need to talk more ...) He is taking it over for Jonah. Oh, good. That was the piece of missing info. I had a case just yesterday where this feature would have saved us hours of writing client code to compute the same thing. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Recursive Queries
Andrew Dunstan wrote: > Gregory Stark wrote: > > I'm looking into recursive queries and what it would take to support them in > > Postgres. Is anyone else looking at this already? > > > > Aside from the Oracle-ish syntax were there other objections to the patch as > > posted a while back for 7.3 by Evgen Potemkin? > > > > I have some ideas myself for how to go about this but I'm going to review > > the > > existing patch first. If anyone else has ideas I would like to hear them. > > > > > > My recollection is that the verdict was that it was clode to 100% > unusable - you might want to review the past discussions. Yes, the old patch is unusasble. The change has to be done in a different part of the code. > Wasn't somebody else working on this? Jonah? (Maybe you EDB guys need to > talk more ...) He is taking it over for Jonah. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] weird buildfarm failures on arm/mipsel and --with-tcl
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > FWIW - I removed --with-tcl from quagga's configuration about two weeks > ago and it has not failed(for that reason) again. So the issue most > definitly looks like plptcl related ... It sorta looks like Tcl might be installing an atexit() callback that is doing the Wrong Thing somehow. What Tcl version do you have installed exactly, and with what configure options? (The contents of tclConfig.sh should be reasonably complete info.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch in core patch, for inclusion
Joshua D. Drake wrote: Peter Eisentraut wrote: Teodor Sigaev wrote: If there aren't objections then we plan commit patch tomorrow or after tomorrow. I still haven't heard any argument for why this would be necessary or desirable at all, other than that it looks better for marketing reasons, which I will counter by saying that it looks worse for marketing reasons because our hailed plugin mechanism is apparently so poor that it can't support some practical extension module such as this. Of which I will counter that we don't have a hailed plugin mechanism. We have a contrib which professionals generally consider untested and not part of PostgreSQL. I am constantly running into this: Q. Does PostgreSQL have full text indexing? A. Yes it is in contrib. Q. But that isn't part of core. A. *sigh* Where on the website can I see what "plugins" are included with PostgreSQL? Where on the website can I see the Official PostgreSQL Documentation for Full Text Indexing? With TSearch2 in core will that fix the many upgrade problems associated with using TSearch2? contrib is a horrible misnomer. Can we maybe bite the bullet and call it something else? cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tsearch in core patch, for inclusion
> Teodor Sigaev wrote: >> If there aren't objections then we plan commit patch tomorrow or >> after tomorrow. This is a fairly large patch and I would like the chance to review it before it goes in --- "we'll commit tomorrow" is not exactly a decent review window. Peter Eisentraut <[EMAIL PROTECTED]> writes: > I still haven't heard any argument for why this would be necessary or > desirable at all, other than that it looks better for marketing > reasons, One possible argument for this over the contrib version is a saner approach to dumping and restoring configurations. However, as against that: 1) what's the upgrade path for getting an existing tsearch2 configuration into this implementation? 2) once we put this in core we are going to be stuck with supporting its SQL API forever. Are we convinced that this API is the one we want? I don't recall even having seen any proposal or discussion. It was OK for tsearch2's API to change every release while it was in contrib, but the expectation of stability is a whole lot higher for core features. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch in core patch, for inclusion
Peter Eisentraut wrote: > Teodor Sigaev wrote: >> If there aren't objections then we plan commit patch tomorrow or >> after tomorrow. > > I still haven't heard any argument for why this would be necessary or > desirable at all, other than that it looks better for marketing > reasons, which I will counter by saying that it looks worse for > marketing reasons because our hailed plugin mechanism is apparently so > poor that it can't support some practical extension module such as > this. Of which I will counter that we don't have a hailed plugin mechanism. We have a contrib which professionals generally consider untested and not part of PostgreSQL. I am constantly running into this: Q. Does PostgreSQL have full text indexing? A. Yes it is in contrib. Q. But that isn't part of core. A. *sigh* Where on the website can I see what "plugins" are included with PostgreSQL? Where on the website can I see the Official PostgreSQL Documentation for Full Text Indexing? With TSearch2 in core will that fix the many upgrade problems associated with using TSearch2? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] About PostgreSQL certification
>> Also comparing Postgres to MYSQL is also pretty funny, since there are >> instances of MYSQL LOSING databases due to corruption because they do >> not have PITR and their transaction rollback feature did not work >> properly last time I checked. This is really a issue of people being >> close minded to great database software and not being able to sell it >> to their superiors. > > It's not funny at all. Just like comparing PostgreSQL to Apache isn't > funny (Covalent did spectacular things legitimizing the use of Apache in > the global 2000). The fact that MySQL has lost data is not germane to This is the point of this thread that I think people are severely missing. (Covalent did spectacular things legitimizing the use of Apache in the global 2000) It is also about my point that Theo and I share different markets. In Theo's world his arguments are 100% correct, imo. I would garner that less than 1% of the PostgreSQL experts out there can speak to the global 2000 requirements. The global 2000 includes people like GM, Wal-Mart and Sony. http://www.forbes.com/lists/2006/18/06f2000_The-Forbes-2000_Rank.html These organizations have diverse and extreme requirements that only some of us have ever even been exposed to. Case in point, one of my customers recently spoke to me about moving a critical system to PostgreSQL. This system, if down will cost the customer several million (that is 7 digits) an hour. How many on this thread can honestly say that they have a clue what type of business volume that is? Sincerely, Johsua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] weird buildfarm failures on arm/mipsel and --with-tcl
Stefan Kaltenbrunner wrote: > Tom Lane wrote: >> Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: >>> one of my new buildfarm boxes (an Debian/Etch based ARM box) is >>> sometimes failing to stop the database during the regression tests: >>> http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=quagga&dt=2007-01-08%2003:03:03 >>> this only seems to happen sometimes and only if --with-tcl is enabled on >>> quagga. >>> lionfish (my mipsel box) is able to trigger that on every build if I >>> enable --with-tcl but it is nearly impossible to debug it there because >>> of the low amount of memory and diskspace it has. >> Hm, could pl/tcl somehow be preventing the backend from exiting once >> it's run any pl/tcl stuff? I have no idea why though, and even less >> why it wouldn't be repeatable. >> >>> After the stopdb failure we still have those processes running: >>> pgbuild 3488 0.0 2.4 43640 6300 ?Ss 06:15 0:01 >>> postgres: pgbuild pl_regression [local] idle >> Can you get a stack trace from this process? > > (gdb) bt > #0 0x406b9d80 in __pthread_sigsuspend () from /lib/libpthread.so.0 > #1 0x406b8a7c in __pthread_wait_for_restart_signal () from > /lib/libpthread.so.0 > #2 0x406b91f8 in pthread_onexit_process () from /lib/libpthread.so.0 > #3 0x40438658 in exit () from /lib/libc.so.6 > #4 0x40438658 in exit () from /lib/libc.so.6 > Previous frame identical to this frame (corrupt stack?) > > > >>> pgbuild 3489 0.0 0.0 0 0 ?Z06:15 0:00 >>> [postgres] >> This is a bit odd ... if that process is a direct child of the >> postmaster it should have been reaped promptly. Could it be a child >> of the other backend? If so, why was it started? Please try the >> ps again with whatever switch it needs to list parent process ID. > > looks you are right - the defunct 3489 seems to be a child of 3488: > > PPID PID PGID SID TTY TPGID STAT UID TIME COMMAND > 1 3389 18341 18341 ? -1 S 1001 0:03 > /home/pgbuild/pgbuildfarm/HEAD/inst/bin/postgres -D data > 3389 3391 3391 3391 ? -1 Ss1001 0:00 postgres: > writer process > 3389 3392 3392 3392 ? -1 Ss1001 0:00 postgres: stats > collector process > 3389 3488 3488 3488 ? -1 Ss1001 0:01 postgres: > pgbuild pl_regression [local] idle > 3488 3489 3488 3488 ? -1 Z 1001 0:00 [postgres] > FWIW - I removed --with-tcl from quagga's configuration about two weeks ago and it has not failed(for that reason) again. So the issue most definitly looks like plptcl related ... Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Default permissisons from schemas
On 1/24/07, Stephen Frost <[EMAIL PROTECTED]> wrote: err, what proposal wasn't touching the GRANT syntax at all but rather right, but the original proposal did: # %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command which was more or less (with the NEW TABLES flavor of the command) duplicated by: # Allow GRANT/REVOKE permissions to be inherited by objects based on schema permissions and your proposal would make alter schema (and presumably create schema) the only command(s) that deal with privileges excluding grant/revoke. That, IMO is actually a bad thing...a surprising behavior. I think the 'new tables' form is better but has the same problems as your proposal in that it does not disambiguate sequences from tables, etc. It would however solve (I think!) your problem without resorting to ownership delegation. I don't think it makes sense to have this syntax be part of the GRANT syntax since it's really about a schema.. So, basically I disagree with the above, and agree with the others wrt ownership change, but very much agree if it is pratical that having some mechanism of applying permissions to objects when they are created depending on which schema they are in is a good thing. merlin merlin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] tsearch in core patch, for inclusion
Teodor Sigaev wrote: > If there aren't objections then we plan commit patch tomorrow or > after tomorrow. I still haven't heard any argument for why this would be necessary or desirable at all, other than that it looks better for marketing reasons, which I will counter by saying that it looks worse for marketing reasons because our hailed plugin mechanism is apparently so poor that it can't support some practical extension module such as this. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Recursive Queries
Gregory Stark wrote: I'm looking into recursive queries and what it would take to support them in Postgres. Is anyone else looking at this already? Aside from the Oracle-ish syntax were there other objections to the patch as posted a while back for 7.3 by Evgen Potemkin? I have some ideas myself for how to go about this but I'm going to review the existing patch first. If anyone else has ideas I would like to hear them. My recollection is that the verdict was that it was clode to 100% unusable - you might want to review the past discussions. Wasn't somebody else working on this? Jonah? (Maybe you EDB guys need to talk more ...) cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Help with Degree Work
Hello, I've suscribed to this mailing list for help, I will work on a Specialization Degree Thesis, this will be a PostgreSQL implementation of fsql, or fuzzy querys. http://www.lcc.uma.es/~ppgg/FSQL.html, this is a link to a webpage who made this in Oracle, but it's not inside of course, because it was made for other people. My work will be make this in the PostgreSQL Kernell, so i'll appreciate all help you can bring. Thanks.
Re: [HACKERS] Recursive Queries
Gregory Stark wrote: > I'm looking into recursive queries and what it would take to support them in > Postgres. Is anyone else looking at this already? Yes your co-employee Jonah. http://archives.postgresql.org/pgsql-hackers/2007-01/msg00989.php Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Recursive Queries
I'm looking into recursive queries and what it would take to support them in Postgres. Is anyone else looking at this already? Aside from the Oracle-ish syntax were there other objections to the patch as posted a while back for 7.3 by Evgen Potemkin? I have some ideas myself for how to go about this but I'm going to review the existing patch first. If anyone else has ideas I would like to hear them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] STOP all user access except for admin for a few minutes?
Hi, [EMAIL PROTECTED] wrote: Ha ha... thx Tino Yes, I think this is way to go, strange how my mind climbs the wrong tree sometimes :) I actually need to aquire a transaction across several dB's, check if the conditions are right, and then modify some tables, write and remove some triggers. Transactions in postgres are 2 sophisticated, I dont think they will hold the locks at the level I need them. You want to read about explicit locking: http://www.postgresql.org/docs/8.2/static/explicit-locking.html But I was thinking (climbing out of the wrong tree;)... I can just aquire exclusive locks on the tables, and hey presto, users are on hold while the software checks the dB's. I'm sure, that's possible. However, I remember you were talking about replication, thus I have to add a warning: please keep in mind that this does not scale. You're most probably better using two phase commit, aren't you? Regards Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Access last inserted tuple info...
Hi, I'm working on a modification of PostgreSQL 8.1.4 and I need to access the information stored in the last tuple inserted in a table (All this from the backend code). Could anyone please help me on this? Greetings and Thanks... -- Luis D. García M. Telf: (+58) 2418662663 Cel.: (+58) 4143482018 - FACYT - UC - - Computación -
[HACKERS] tsearch in core patch, for inclusion
We (Oleg and me) are glad to present tsearch in core of pgsql patch. In basic, layout, functions, methods, types etc are the same as in current tsearch2 with a lot of improvements: - pg_ts_* tables now are in pg_catalog - parsers, dictionaries, configurations now have owner and namespace similar to other pgsql's objects like tables, operator classes etc - current tsearch configuration is managed with a help of GUC variable tsearch_conf_name. - choosing of tsearch cfg by locale may be done for each schema separately - managing of tsearch configuration with a help of SQL commands, not with insert/update/delete statements. This allows to drive dependencies, correct dumping and dropping. - psql support with a help of \dF* commands - add all available Snowball stemmers and corresponding configuration - correct memory freeing by any dictionary Work is sponsored by EnterpriseDB's PostgreSQL Development Fund. patch: http://www.sigaev.ru/misc/tsearch_core-0.33.gz docs: http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ (not yet completed and it's not yet a patch, just a SGML source) Implementation details: - directory layout src/backend/utils/adt/tsearch - all IO function and simple operations src/backend/utils/tsearch - complex processing functions, including language processing and dictionaries - most of snowball dictionaries are placed in separate .so library and they plug in into data base by similar way as character conversation library does. If there aren't objections then we plan commit patch tomorrow or after tomorrow. Before committing, I'll changes oids from 5000+ to lower values to prevent holes in oids. And after that, I'll remove tsearch2 contrib module. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] STOP all user access except for admin for a few minutes?
Ha ha... thx Tino Yes, I think this is way to go, strange how my mind climbs the wrong tree sometimes :) I actually need to aquire a transaction across several dB's, check if the conditions are right, and then modify some tables, write and remove some triggers. Transactions in postgres are 2 sophisticated, I dont think they will hold the locks at the level I need them. But I was thinking (climbing out of the wrong tree;)... I can just aquire exclusive locks on the tables, and hey presto, users are on hold while the software checks the dB's. Effectively creating a very rough transaction, with the lock scope needed? ... ie it will keep users out long enough to align several dB's... I'm hoping? From: "Tino Wildenhain" <[EMAIL PROTECTED]> maybe I'm in the wrong tree Yes I'm thinking that too: Is it possible to make quick structural changes to postgres, with user activety? of course. Maybe start a transaction that changes structure... wonder if that will stop or hold user activity??? Usually not - all your DDL is done in a transaction just like any other access users would make. So it only fails (but as a whole) if you want to modify locked tables and such. But you would not end up w/ a partly changed database in any case. Just make sure you do everything in a transaction. No need to suspend user accounts for that. Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Default permissisons from schemas
* Merlin Moncure ([EMAIL PROTECTED]) wrote: > On 1/24/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > >when you create them. Table rights almost always follow broad rules > >so it only natural to integrate that with schemas somehow...but > >admittedly it is awkward to put it into GRANT (and I've thought alot a > >bout. > > oops :( what I meant to say here is that I don't think it's possible > to this in the way that Stephen wants because it would hack up GRANT > to much. Tom was at least half right, this proposal was not discarded > out of hand but it was on pretty shaky ground...I was one of the big > supporters of extending grant this way in the original discussion but > I think it might be the wrong approach. err, what proposal wasn't touching the GRANT syntax at all but rather adding some options to ALTER SCHEMA which I didn't think was all that bad (and wasn't commented on except to point out that I needed to handle different object types seperately). The current opposition, aiui, is against having a 'default owner' for new objects in a schema and not the default ACLs per schema. I don't think it makes sense to have this syntax be part of the GRANT syntax since it's really about a schema... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [pgsql-patches] pg_get_domaindef
[ redirecting discussion to -hackers, where it seems more appropriate ] Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: FAST PostgreSQL wrote: Please find attached the patch with modifications are you proposing to implement the other functions in this TODO item (pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_functiondef() ) ? I haven't entirely understood the use case for any of these. It's not pg_dump, for a number of reasons: one being that pg_dump still has to support older backend versions, and another being that every time we let backend SnapshotNow functions get involved, we take another hit to pg_dump's claim to produce a consistent MVCC snapshot. But my real objection is: do we really want to support duplicative code in both pg_dump and the backend? Updating pg_dump is already a major PITA whenever one adds a new feature; doubling that work isn't attractive. (And it'd be double, not just a copy-and-paste, because of the large difference in the operating environment.) So I want to hear a seriously convincing use-case that will justify the maintenance load we are setting up for ourselves. "Somebody might want this" is not adequate. Perhaps a better area of work would be the often-proposed refactoring of pg_dump into a library and driver program, wherein the library could expose individual functions such as "fetch the SQL definition of this object". Unfortunately, that'll be a huge project with no payoff until the end... I agree entirely. I'm not sure how big the refactoring would be, but I do think it's a good goal. Neil mentioned something about it the other day. It is a worry though that we have an item on the TODO list that has been worked on and now we might say "Thanks, but no thanks". That's not a good way to make friends for PostgreSQL. This is why I think we need the TODO list to be somewhat authoritative, i.e. a list of things that we have some sort of consensus about doing and commitment to accepting, at least in principle. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] About PostgreSQL certification
On Jan 24, 2007, at 9:50 AM, John Zubac wrote: I find your statements about Postgres being a huge business risk pretty laughable. First of all, Postgres is based on SQL92 and SQL99 standards which means that most scripts are pretty much the same compared to MSSQL and Oracle. The only thing I have seen to learn are the postgres datatypes. Big deal! PGAdmin III will write most scripts for you and that too is pretty much free. I dealt with it when we started learning and using postgres. I only had experience in Oracle and MSSQL. If that's the only thing you had to learn, we aren't talking about the same risks. Datatypes are developer level differences. Tuning, sizing, disaster recovery planning, backups, differences in or lack of enterprise features and integration -- these are all very different between databases and fundamental to operating it in an enterprise environment. You can laugh if you like. I don't laugh about these things, neither do our clients. Many have decided to run postgres and that decision was a good one. Many do not and their decisions were also wise. Several people at the dayjob, including me, travel and speak on postgres, database replication, large architecture management, open source, etc. We promote postgres in many venues. I said: "If you only want to hire people with extensive experience, you're exposing yourself to an enormous business risk by adopting postgres." There simple aren't that many people that have extensive experience. So you you are hinging the success of your business of one of those people being available, it _is_ an enormous business risk. My arguments here are not against postgres, they are for training and certification -- both help dramatically increase the pool of people with sufficient experience. Also comparing Postgres to MYSQL is also pretty funny, since there are instances of MYSQL LOSING databases due to corruption because they do not have PITR and their transaction rollback feature did not work properly last time I checked. This is really a issue of people being close minded to great database software and not being able to sell it to their superiors. It's not funny at all. Just like comparing PostgreSQL to Apache isn't funny (Covalent did spectacular things legitimizing the use of Apache in the global 2000). The fact that MySQL has lost data is not germane to the discussion. There have been bugs in PostgreSQL as well. And there has been data loss with PostgreSQL and Oracle and MSSQL. We're talking about business risks due to resource availability in the job market capable of managing postgresql in an enterprise environment. And was stating that solid certification programs can and will increase the availability of those resources and reduce the risks in adopting postgres as a solution. I, along with most of the people in the community, believe in PostgreSQL, believe in the direction development is going in and want to see adoption increase. This is the way I sold postgres to my boss. It is opensource (low cost), all the features of MSSQL and then some, WAY FASTER than MSSQL on a BSD platform, very good recovery when the database gets corrupted (this happens to all databases from user error usually), and lastly you can always migrate the data to another database if you don't like postgres in the end. I have no problem representing the positive aspects of postgres. I am also not blind to its shortcomings. We manage one of the larger postgres instances out there -- I know its pros and cons well. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] About PostgreSQL certification
Hi Theo I find your statements about Postgres being a huge business risk pretty laughable. First of all, Postgres is based on SQL92 and SQL99 standards which means that most scripts are pretty much the same compared to MSSQL and Oracle. The only thing I have seen to learn are the postgres datatypes. Big deal! PGAdmin III will write most scripts for you and that too is pretty much free. I dealt with it when we started learning and using postgres. I only had experience in Oracle and MSSQL. Also comparing Postgres to MYSQL is also pretty funny, since there are instances of MYSQL LOSING databases due to corruption because they do not have PITR and their transaction rollback feature did not work properly last time I checked. This is really a issue of people being close minded to great database software and not being able to sell it to their superiors. This is the way I sold postgres to my boss. It is opensource (low cost), all the features of MSSQL and then some, WAY FASTER than MSSQL on a BSD platform, very good recovery when the database gets corrupted (this happens to all databases from user error usually), and lastly you can always migrate the data to another database if you don't like postgres in the end. John Zubac -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Theo Schlossnagle Sent: Tuesday, January 23, 2007 5:20 PM To: Joshua D. Drake Cc: Theo Schlossnagle; Mark Kirkwood; David Fetter; Iannsp; PostgreSQL-development Subject: Re: [HACKERS] About PostgreSQL certification On Jan 23, 2007, at 5:14 PM, Joshua D. Drake wrote: > >> Get a CCIE and tell me that again :-) When you are handed a >> complicated >> network of routers and switches running all sorts of version of >> IOS and >> CatOS and you go to lunch, they break it and you have a certain time >> allotment to fix it all. >> >> Most certifications are not simple multiple choice quizes. Just the >> ones you hear about -- the ones that suck. >> >>> I think seeing relevant training courses + experience on a CV trumps >>> certification anytime - unfortunately a lot of folks out there are >>> mesmerized by shiny certificates >> >> Sure. But experience is very hard to get. And since people with >> PostgreSQL experience are limited, companies adopting it need a good >> second option -- certified people. > > They aren't limited, just all employed ;) I can't find 500, let alone 1000, people with extensive postgresql experience in an enterprise environment. Oracle has an order of magnitude more. MySQL even has better numbers than postgres in this arena. If you only want to hire people with extensive experience, you're exposing yourself to an enormous business risk by adopting postgres. You'd have to hire out to a consulting company and if too many do that, the consulting company will have scaling issues (as all do). The upside of Oracle is that I can hire out to a consulting company for some things (particularly challenging scale or recovery issues) and get someone who knows their way around Oracle reasonably well (has performed _real_ disaster recovery in a hands on fashion, performed hands-on query tuning, database sizing exercises, etc.) by simply finding someone who is Oracle certified (all of those things are part of the Oracle certification process). Granted, just because someone is certified doesn't mean they "fit" or will excel at the problems you give them -- it's just a nice lower bar. Granted you can make a name for yourself as an expert without getting a certification, but if you've made a name for yourself, you aren't likely to be on the job market -- which is really my point. Oracle's certification programs have helped Oracle considerably in gaining the number of Oracle professionals in the job market. PostgreSQL certification has the opportunity to do the same and in doing so increase overall PostgreSQL adoption. That's a good thing. -- Theo // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Updateable cursors
On Wed, 2007-01-24 at 14:27 +0100, Zeugswetter Andreas ADI SD wrote: > > That is also the safe thing to do, since PostgreSQL's implementation > of > > WITH HOLD cursors doesn't leave the rows locked. That can lead to the > > rows being deleted from under the cursor, for which the standard is > > unclear as to whether that is acceptable, or not. > > Um, the default use case is to "intent exclusive" lock the current row, > so you can do some calculations on columns inside the application > without > them changing in the meantime. > So, imho that lock is a substantial feature of FOR UPDATE cursors. > The lock is usually freed as soon as you fetch the next row. > In MVCC db's it is also a method to read a guaranteed up to date > version. Completely agree. The standard doesn't say it, but it might be taken to imply that locks continue to be held, as with 2PC, and released when the cursor is closed. But I'm not really sure I'd want that either, IMHO. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Default permissisons from schemas
On 1/24/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: when you create them. Table rights almost always follow broad rules so it only natural to integrate that with schemas somehow...but admittedly it is awkward to put it into GRANT (and I've thought alot a bout. oops :( what I meant to say here is that I don't think it's possible to this in the way that Stephen wants because it would hack up GRANT to much. Tom was at least half right, this proposal was not discarded out of hand but it was on pretty shaky ground...I was one of the big supporters of extending grant this way in the original discussion but I think it might be the wrong approach. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Default permissisons from schemas
On 1/24/07, Stephen Frost <[EMAIL PROTECTED]> wrote: Sure, all the objects in a given schema should be owned by a role which all the admins of that schema are members of. I really see this as a sensible step from ACLs since ownership implies additional permissions (which can't otherwise be granted, otherwise it wouldn't matter so much). We do this quite a bit and it's annoying when someone forgets to change the ownership of something they created. Since we do this largely on a per-schmea basis (and different schemas have different admin groups, which can overlap) getting people to remember to 'set role' doesn't seem likely to practically improve things much. I've considered writing a cron job to periodically fix all the ownerships and permissions but then having actual exceptions becomes a pain. In every place I've worked, table permissions/ownership has been a problem...it's tedious and error-prone to catch permission errors...even with regression testing. My solution has always been to write pl/pgsql functions to do exactly that. It would be very nice not to have to do that however and have things auto-magically be set when you create them. Table rights almost always follow broad rules so it only natural to integrate that with schemas somehow...but admittedly it is awkward to put it into GRANT (and I've thought alot a bout. It seems like an alternate solution to this problem is to be able to hook triggers to pg_class and pg_namepace, so you can fire grant statements of your choosing when tables gets added/removed based on your own logic. Or, since triggers are broadly not allowed to system catalogs, maybe a trigger-ish sql callback could be added to the schema so that when objects inside are added/removed, you have the ability to inject your own sql. This gets you to the same place without hacking up grant or adding acl. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Free space management within heap page
On 1/24/07, Gregory Stark <[EMAIL PROTECTED]> wrote: "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > On 1/24/07, Martijn van Oosterhout wrote: >> >> I thought the classical example was a transaction that updated the same >> tuple multiple times before committing. Then the version prior to the >> transaction start isn't dead yet, but all but one of the versions >> created by the transaction will be dead (they were never visible by >> anybody else anyway). > > I believe that calculation of oldestXmin would consider the running > transaction, if any, which can still see the original tuple. So the > intermediate tuples won't be declared DEAD (they will be declared > RECENTLY_DEAD) as long as the other transaction is running. Any newer > transactions would always see the committed copy and hence need not follow > ctid through the dead tuples. Martijn is correct that HeapTupleSatisfiesVacuum considers tuples dead if there were created and deleted by the same transaction even if that transaction isn't past the oldestxmin horizon. I agree. Here the tuple must had been created as an effect of INSERT and not UPDATE. Since if its created because of UPDATE, then HEAP_UPDATED bit is set on the tuple and tuple is not considered dead by HeapTupleSatisfiesVacuum, even if its xmin and xmax are same. So it must have been created by INSERT. In that case there can not be a parent linking this tuple via t_ctid. There's already been one bug in that area when it broke update chains, and to fix it vacuum ignores tuples that were deleted by the same transaction in an UPDATE statement. Sounds logical. This seems like such an unusual case, especially now that it's been narrowed by that exception, that it's silly to optimize for it. Just treat these tuples as live and they'll be vacuumed when their transaction commits and passes the oldestxmin like normal. I agree. Nevertheless, I don't see any problem with having that optimization. Now that I think more about it, there are places where xmin of the next tuple in the t_ctid chain is matched with the xmax of the previous tuple to detect cases where one of the intermediate DEAD tuples has been vacuumed away and the slot has been reused by a completely unrelated tuple. So doesn't than mean we have already made provision for scenarios where intermediate DEAD tuples are vacuumed away ? Thanks, Pavan EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Default permissisons from schemas
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > * Tom Lane ([EMAIL PROTECTED]) wrote: > >> Before discussing "limitations" you should first justify why we need any > >> such concept at all. It was no part of the original TODO item and I > >> cannot see any good use for it. > > > There are permissions which are not grantable but exist as implicitly > > granted to the owner of object. These include drop, truncate, alter. > > Practically, I find myself having to change the owner of objects which I > > create almost as often as I'm defining the ACL for those objects. In > > many of our schemas all the objects should be owned by the same 'admin' > > role so that those who are in that role can perform the actions which > > are only available to object owners, much the same as those objects > > having a certain set of minimum ACLs. > > I don't see any argument here for not creating the object as owned by > the creator -- as you note, SET ROLE is the way to cause something to be > owned by a role you have permission to become. The important difference > is that SET ROLE actually checks that you have that permission, whereas > a magical catalog entry saying "create objects as somebody else instead" > wouldn't. Maybe you could make it do so, but that would just be a > surprising behavior IMHO; and surprising security-related behaviors are > seldom a good idea. One of the specific suggestions I made in the previous email was to have the permissions be checked at object creation time. There's no reason that couldn't be done and I believe it's quite straight-forward to do. > BTW, I believe a schema owner can DROP any contained object whether he > owns it or not; without that the owner's ability to DROP the schema > would of course be worthless... Yes, the schema owner can drop contained objects but that doesn't extend to those who have only create rights on the schema. There is no 'drop' right which can be granted, you have to be the schema owner or the owner of the table. > > I had thought it was going to be possible to set up roles/permissions > > such that a newly created object would be owned by the role through > > which the CREATE permission is given but that doesn't seem to be the > > case (or perhaps I'm doing something wrong with it). > > Hm, I have some vague recollection that we considered that and rejected > it. Probably because it's ill-defined: what if there is more than one > path through which you've been granted CREATE permission? Sure, in that case it's ill-defined. This would resolve that though to a specific role, by schema. Permissions checks could then still be done to ensure that the user attempting the creation is a member of the default owner role and that role has create rights on the schema. If either of those fail, fall back to the default case. Also, only run down this path *if asked for* by the schema owner by having set the default owner to begin with. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Default permissisons from schemas
* Jim Nasby ([EMAIL PROTECTED]) wrote: > On Jan 23, 2007, at 12:07 PM, Stephen Frost wrote: > >Hmm. While I agree with the sentiment, Unix does provide for setgid > >such that objects inherit a specific group on creation. Using > >roles we > >don't get that distinction so I don't think comparing it to Unix is a > >slam-dunk. There do need to be limitations here though, certainly. A > >couple options, in order of my preference: > > Is there a use-case for per-schema default ownership? I can't really > think of one... Sure, all the objects in a given schema should be owned by a role which all the admins of that schema are members of. I really see this as a sensible step from ACLs since ownership implies additional permissions (which can't otherwise be granted, otherwise it wouldn't matter so much). We do this quite a bit and it's annoying when someone forgets to change the ownership of something they created. Since we do this largely on a per-schmea basis (and different schemas have different admin groups, which can overlap) getting people to remember to 'set role' doesn't seem likely to practically improve things much. I've considered writing a cron job to periodically fix all the ownerships and permissions but then having actual exceptions becomes a pain. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Free space management within heap page
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > On 1/24/07, Martijn van Oosterhout wrote: >> >> I thought the classical example was a transaction that updated the same >> tuple multiple times before committing. Then the version prior to the >> transaction start isn't dead yet, but all but one of the versions >> created by the transaction will be dead (they were never visible by >> anybody else anyway). > > I believe that calculation of oldestXmin would consider the running > transaction, if any, which can still see the original tuple. So the > intermediate tuples won't be declared DEAD (they will be declared > RECENTLY_DEAD) as long as the other transaction is running. Any newer > transactions would always see the committed copy and hence need not follow > ctid through the dead tuples. Martijn is correct that HeapTupleSatisfiesVacuum considers tuples dead if there were created and deleted by the same transaction even if that transaction isn't past the oldestxmin horizon. There's already been one bug in that area when it broke update chains, and to fix it vacuum ignores tuples that were deleted by the same transaction in an UPDATE statement. This seems like such an unusual case, especially now that it's been narrowed by that exception, that it's silly to optimize for it. Just treat these tuples as live and they'll be vacuumed when their transaction commits and passes the oldestxmin like normal. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Updateable cursors
> That is also the safe thing to do, since PostgreSQL's implementation of > WITH HOLD cursors doesn't leave the rows locked. That can lead to the > rows being deleted from under the cursor, for which the standard is > unclear as to whether that is acceptable, or not. Um, the default use case is to "intent exclusive" lock the current row, so you can do some calculations on columns inside the application without them changing in the meantime. So, imho that lock is a substantial feature of FOR UPDATE cursors. The lock is usually freed as soon as you fetch the next row. In MVCC db's it is also a method to read a guaranteed up to date version. Andreas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Free space management within heap page
On 1/24/07, Martijn van Oosterhout wrote: On Wed, Jan 24, 2007 at 12:45:53PM +0530, Pavan Deolasee wrote: > My apologies if this has been discussed before. I went through the earlier > discussions, but its still very fuzzy to me. I am not able to construct a > case > where a tuple is DEAD (not RECENTLY_DEAD) and still there could be > a transaction need to follow the ctid pointer chain from its parent. Can > somebody help me to construct this scenario ? I thought the classical example was a transaction that updated the same tuple multiple times before committing. Then the version prior to the transaction start isn't dead yet, but all but one of the versions created by the transaction will be dead (they were never visible by anybody else anyway). I believe that calculation of oldestXmin would consider the running transaction, if any, which can still see the original tuple. So the intermediate tuples won't be declared DEAD (they will be declared RECENTLY_DEAD) as long as the other transaction is running. Any newer transactions would always see the committed copy and hence need not follow ctid through the dead tuples. I might be missing something very obvious, but thats what I am trying to understand. Thanks, Pavan EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Searching some sites explaing about PosgtreSQL source codes
Hi, I'm working with Postgres Source Code too, and there's a site that could be helpfull for you as it has been for me. The site is: http://www.mcknight.de/pgsql-doxygen/cvshead/html/ Greetings... 2007/1/24, re-plore <[EMAIL PROTECTED]>: Hi, I am now reading PostgreSQL source codes, but i am not familiar to this codes. So i am now seraching some sites which explaing about PostgreSQL source codes, or it's structure. If you know a good site explaing PostgreSQL's source codes. Please teach me. Thanks a lot of your conservation! -- Luis D. García M. Telf: (+58) 2418662663 Cel.: (+58) 4143482018 - FACYT - UC - - Computación -
Re: [HACKERS] Updateable cursors
On Wed, 2007-01-24 at 14:54 +1100, John Bartlett wrote: > The reason for those 5 options is to consider different means to cover the > Prepared Stmt requirement where the different stages of processing are > actually in different transactions. John, Thanks for explaining. Wow! I've never come across such a requirement before, personally and hadn't even imagined anybody would want to do this. ISTM the main use for positioned UPDATE/DELETE is for a single transaction to first open a cursor and then loop around doing FETCH and then positioned UPDATE/DELETE on that cursor. It would make the implementation considerably easier to limit the initial implementation to only work using WITHOUT HOLD cursors (the default). This will allow you to cache the ctid, rather than re-seeking via the index, so will offer considerably better performance also. That is also the safe thing to do, since PostgreSQL's implementation of WITH HOLD cursors doesn't leave the rows locked. That can lead to the rows being deleted from under the cursor, for which the standard is unclear as to whether that is acceptable, or not. AFAICS the SQL Standard also requires that the positioned Update/Delete also effect only a single row. When using WITH HOLD cursors the desired row's ctid may have changed. Re-executing the original WHERE condition might easily reveal more than one row where previously there was only one. The cursor itself provides no mechanism for telling rows apart in that circumstance when no Primary Key is defined on the table. We can surround that with various checks, maybe. ISTM that even allowing this using WITH HOLD cursors seems likely to be both a poor-performing and fragile application programming technique. I'd suggest we add the combination of WITH HOLD cursors and positioned updates to the small pile of SQL standard items we don't really want to support for practical reasons. At very least, I'd suggest we do the straightforward part of this for 8.3 and see whether we want a more full implementation in later releases. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Free space management within heap page
On Wed, Jan 24, 2007 at 12:45:53PM +0530, Pavan Deolasee wrote: > My apologies if this has been discussed before. I went through the earlier > discussions, but its still very fuzzy to me. I am not able to construct a > case > where a tuple is DEAD (not RECENTLY_DEAD) and still there could be > a transaction need to follow the ctid pointer chain from its parent. Can > somebody help me to construct this scenario ? I thought the classical example was a transaction that updated the same tuple multiple times before committing. Then the version prior to the transaction start isn't dead yet, but all but one of the versions created by the transaction will be dead (they were never visible by anybody else anyway). I beleive other such corner cases are transactions that have subtransactions that aborted after updating. But I'm not that knowledgable on MVCC to be sure about that. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Piggybacking vacuum I/O
On Wed, 2007-01-24 at 09:32 +0530, Pavan Deolasee wrote: > On a typical desktop class 2 CPU Dell machine, we have seen pgbench > clocking more than 1500 tps. That implies CLOG would get filled up in > less > than 262144/1500=174 seconds. VACUUM on accounts table takes much > longer to trigger. You assume that all of the top level transactions have no subtransactions. On that test, subtransactions are in use because of the EXCEPTION clause in the PL/pgSQL used. That should at least double the number of Xids. > So > most of the 636528 reads in the next 55 minutes can be attributed to > VACUUM. A similar argument might also be applied to subtrans, so a similar investigation seems worthwhile. Subtrans has space for less Xids than clog, BTW. OTOH, I do think that 99% of that will not cause I/O. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] msvc failure in largeobject regression test
On Tue, Jan 23, 2007 at 11:39:23AM -0800, Jeremy Drake wrote: > On Tue, 23 Jan 2007, Magnus Hagander wrote: > > > On Tue, Jan 23, 2007 at 09:31:40AM -0500, Andrew Dunstan wrote: > > > Magnus Hagander wrote: > > > >Hi! > > > > > > > >I get failures for the largeobject regression tests on my vc++ build. I > > > >don't think this has ever worked, given that those tests are fairly new. > > > >Any quick ideas on what's wrong before I dig deeper? > > > > > > > > > > > [snip] > > > > > > I wonder if this is a line-end issue? Assuming you are working from CVS, > > > does your client turn \n into \r\n ? I see that other windows boxes are > > > happily passing this test on the buildfarm, and of course the mingw cvs > > > doesn't adjust line endings. > > > > Bingo! > > > > That's it. I copeid the file in binary mode from a linux box and now it > > passes. > > I thought about that when I wrote it, and thus tried it under mingw and > cygwin without issue ;) I don't think the regression tests were in a > position of running on the msvc build at the time... My thought for what > to do if this did run into a problem would be an alternate output file > that is also acceptable (I don't know what they're called but other tests > have them IIRC). Either that, or we require a checkout using Unix style linefeeds. I've confirmed that removing the file and checking it back out with "cvs --lf update tenk.data" works - tests pass fine. Yet another option might be to flag that file as binary in cvs, in which case I think cvsnt shouldn't go mess with it. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Searching some sites explaing about PosgtreSQL source codes
Hi, I am now reading PostgreSQL source codes, but i am not familiar to this codes. So i am now seraching some sites which explaing about PostgreSQL source codes, or it's structure. If you know a good site explaing PostgreSQL's source codes. Please teach me. Thanks a lot of your conservation!
Re: [HACKERS] Piggybacking vacuum I/O
Pavan Deolasee wrote: I have just counted the number of read/write calls on the CLOG blocks. As you can see the total number of CLOG reads jumped from 545323 to 1181851 i.e. 1181851 - 545323 = 636528 CLOG block reads for 1554697 pages of stock table. Hmm. So there is some activity there. Could you modify the patch to count how many of those reads came from OS cache? I'm thinking of doing a gettimeofday() call before and after read, and counting how many calls finished in less than say < 1 ms. Also, summing up the total time spent in reads would be interesting. Or, would it be possible to put the clog to a different drive, and use iostat to get the numbers? This figure is only indicative since it also includes the CLOG block reads which would have happened as part of other backend operations (VACUUM took almost 55 minutes to complete). Still in the first 210 minutes of the run, the total reads were only 545323. So most of the 636528 reads in the next 55 minutes can be attributed to VACUUM. Actually, clog reads during normal activity is even worse. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
