Re: [HACKERS] proposal: catch warnings
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> What is problem? ANSI SQL has different model of handling exception than
> postgresql. It doesn't distinguishes between warnings and exception.
Simply
> some sqlstate clases are reservated for warnings and other's for
exception.
> But all sqlstate's (without '0') can be handled via any CONTINUE,
EXIT
> or UNDO handler. Exceptions are not problem.
Really? If an EXIT handler fires on a warning, does that mean the
statement giving the warning is aborted midstream, instead of being
allowed to complete?
Propably I have too bad english. I wrote it. I am sorry. Any warning
handlers are executed after statements. With exit handler it executes
handler statement and leave block. That's all.
I think that the model the SQL spec has in mind is that a warning
condition is raised only after the statement has run to completion
(which implies only one such condition per statement BTW). This is
quite at variance with our notion of WARNING. AFAICS you are not going
to be able to implement anything that works sanely if you try to take
control away at the instant of elog(WARNING). You would need to create
some infrastructure for making this happen after the statement giving
the warning is otherwise done --- which will take *much* more extensive
revisions than just hooking into elog.
I understand. Warning infrastructure is implemented in plpgpsm code now.
There is one interest of hook - compatibility. I can simply set if warning
is processed on server side or is sended to client. This decision depends on
application (not only application, it depend on used language).
Do you thing API like?
ErrorData *edata;
push_catch_warning_state(); // new handler and set catching to on
EXEC statement
if ((edata = catched_warning() != NULL)
{
if (I know warning)
process it;
clean_warning();
else
distribute_warning(); // doesn't mean directly sending to client, maybe
others handlers wait for it
}
pop_catch_warning_state(); // prev. handler and set catch to prev. value
Pavel
_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] SGML index build fix
Peter Eisentraut wrote:
> Tom Lane wrote:
> > Perhaps even more to the point, what makes you think that someone
> > will notice the warning? If the docs build is one step in an
> > automated build process, this seems unlikely.
>
> Taking a closer look, it's pretty much guaranteed that no one will see
> them, because the targets they are attached to are intermediate,
> normally followed by latex runs.
Here is a patch that runs the build twice when HTML.index does not
exist, and once every time after that. This is not ideal, but it is a
start.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/Makefile
===
RCS file: /cvsroot/pgsql/doc/src/sgml/Makefile,v
retrieving revision 1.87
diff -c -c -r1.87 Makefile
*** doc/src/sgml/Makefile 7 Jan 2007 08:49:31 - 1.87
--- doc/src/sgml/Makefile 8 Jan 2007 04:02:45 -
***
*** 99,114
COLLATEINDEX := LC_ALL=C $(PERL) $(COLLATEINDEX) -f -g
- # If HTML.index does not exist, create a dummy bookindex.sgml. During the
- # next build, create bookindex.sgml with the proper index contents. A proper
- # bookindex.sgml is required to have an index in the output.
- ifeq (,$(wildcard HTML.index))
- bookindex.sgml:
- $(COLLATEINDEX) -o $@ -N
- else
bookindex.sgml: HTML.index
! $(COLLATEINDEX) -i 'bookindex' -o $@ $<
! endif
version.sgml: $(top_builddir)/src/Makefile.global
{ \
--- 99,115
COLLATEINDEX := LC_ALL=C $(PERL) $(COLLATEINDEX) -f -g
bookindex.sgml: HTML.index
! # If HTML.index is zero length, create a dummy bookindex.sgml
! test -s HTML.index || $(COLLATEINDEX) -o $@ -N
! # If HTML.index is valid, create valid bookindex.sgml. This
! # is required so the output has a proper index.
! test ! -s HTML.index || $(COLLATEINDEX) -i 'bookindex' -o $@ $<
!
! # If HTML.index does not exist, create an empty file and recusively call
! # our own Makefile to create a valid bookindex.sgml.
! HTML.index:
! test -f HTML.index || (touch HTML.index && $(MAKE) $(MAKECMDGOALS))
version.sgml: $(top_builddir)/src/Makefile.global
{ \
***
*** 285,291
clean distclean maintainer-clean:
# HTML
! rm -f HTML.manifest *.html *.gif
# man
rm -rf *.1 *.$(DEFAULTSECTION) man1 man$(DEFAULTSECTION) manpage.refs manpage.links manpage.log
# print
--- 286,292
clean distclean maintainer-clean:
# HTML
! rm -f HTML.manifest *.html *.gif bookindex.skip
# man
rm -rf *.1 *.$(DEFAULTSECTION) man1 man$(DEFAULTSECTION) manpage.refs manpage.links manpage.log
# print
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] SGML index build fix
On Sun, 7 Jan 2007, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Perhaps even more to the point, what makes you think that someone > >> will notice the warning? If the docs build is one step in an > >> automated build process, this seems unlikely. > > > Taking a closer look, it's pretty much guaranteed that no one will see > > them, because the targets they are attached to are intermediate, > > normally followed by latex runs. > > If we think this is a problem, ISTM the correct answer is to just force > a repeat jade run when doing "make all". The only objection to that > AFAICS is that when you're doing docs work and only need a draft to > look at, you'd rather it not run twice. But perhaps we could address > that by providing a separate target, "make draft" say, that runs jade > but once. That's a nice approach. Those working on the docs will know about the draft target and those just wanting to build the docs for publication will get the result. Gavin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Full page writes
Gregory Stark <[EMAIL PROTECTED]> writes: > It occurs to me that we don't need to write full page writes on newly extended > pages. Is that optimization in the code already? I would check except I'm not > even sure where to look for it. Look for XLOG_HEAP_INIT_PAGE ... I suspect there are other cases where the idea could be applied, but at least the basic first-heap_insert scenario is covered. 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] [PATCHES] SGML index build fix
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Perhaps even more to the point, what makes you think that someone >> will notice the warning? If the docs build is one step in an >> automated build process, this seems unlikely. > Taking a closer look, it's pretty much guaranteed that no one will see > them, because the targets they are attached to are intermediate, > normally followed by latex runs. If we think this is a problem, ISTM the correct answer is to just force a repeat jade run when doing "make all". The only objection to that AFAICS is that when you're doing docs work and only need a draft to look at, you'd rather it not run twice. But perhaps we could address that by providing a separate target, "make draft" say, that runs jade but once. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] proposal: catch warnings
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > What is problem? ANSI SQL has different model of handling exception than > postgresql. It doesn't distinguishes between warnings and exception. Simply > some sqlstate clases are reservated for warnings and other's for exception. > But all sqlstate's (without '0') can be handled via any CONTINUE, EXIT > or UNDO handler. Exceptions are not problem. Really? If an EXIT handler fires on a warning, does that mean the statement giving the warning is aborted midstream, instead of being allowed to complete? I think that the model the SQL spec has in mind is that a warning condition is raised only after the statement has run to completion (which implies only one such condition per statement BTW). This is quite at variance with our notion of WARNING. AFAICS you are not going to be able to implement anything that works sanely if you try to take control away at the instant of elog(WARNING). You would need to create some infrastructure for making this happen after the statement giving the warning is otherwise done --- which will take *much* more extensive revisions than just hooking into elog. 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] Erronous sort used in query plan
Shane Ambler <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> It seems clear to me that we ought not impose a bias unless the join >> type is such that both directions of hashing are feasible. > I think that the selected sort (or at least the merge join) is incorrect > - the column sorted (or both actually) is linking the current record in > pg_operator with the oid in the pg_proc - it will only return one row. That hasn't got anything to do with it AFAICS. The plan is not wrong, it's just inefficient. I dug around in old files and found out that the notion of discriminating against hash joins with the larger rel on the inside is ancient: in Postgres v4r2, the oldest tarball I have, cost_hashjoin contains int outerpages = page_size (outersize,outerwidth); int innerpages = page_size (innersize,innerwidth); if (outerpages < innerpages) return _disable_cost_; and while the code's been rewritten several times, the lineage is clear. It seems to me now that I've thought about it that we should just get rid of this bias entirely: it is demonstrably wrong if the join is not an inner join, or if the larger relation has significantly better dispersion across hash buckets. To the extent that it's accomplishing anything good at all, the behavior ought to be emergent from the rest of the cost model. I experimented a bit with the idea and found out that without the bias, it was willing to flip over to larger-relation-on-the-inside in cases where that actually made it a bit slower. I interpret this as meaning that we're undercharging for loading the hash table in comparison to using it. The cost model as it stands (but minus bias) effectively says that for two relations that both fit into work_mem and have equally well-dispersed hash keys, it's actually better to have the larger rel on the inside! The number of hash-function calculations is the same either way (one for each tuple in the two rels), and the executor tries to hold the number-of-tuples-per-hash-bucket constant at about 10 regardless of relation size, so when you trace through the calculations you find the only differential between the total-cost estimates for the two mirror-image hash joins is the number of outer tuples for which hashtable probes must be made; hence the fewer of them the better. Experimentation shows this isn't so, however, which suggests that the cost of inserting a tuple into the hashtable is a non-ignorable cost. I got results that seemed to track reality better after I added cpu_tuple_cost per hashtable entry and discounted the per-outer-tuple cost a bit to compensate. I also noticed that the cost estimate for having to do I/O in a batched hashjoin had missed getting updated for the 8.2 seq_page_cost changes; this is a plain ol' oversight on my part. In short, then, I'm proposing the attached patch for HEAD and 8.2; I'm not sure whether to change things further back. Comments? regards, tom lane Index: costsize.c === RCS file: /cvsroot/pgsql/src/backend/optimizer/path/costsize.c,v retrieving revision 1.172 diff -c -r1.172 costsize.c *** costsize.c 5 Jan 2007 22:19:31 - 1.172 --- costsize.c 8 Jan 2007 01:00:56 - *** *** 1498,1507 double hashjointuples; double outer_path_rows = PATH_ROWS(outer_path); double inner_path_rows = PATH_ROWS(inner_path); - double outerbytes = relation_byte_size(outer_path_rows, - outer_path->parent->width); - double innerbytes = relation_byte_size(inner_path_rows, - inner_path->parent->width); int num_hashclauses = list_length(hashclauses); int numbuckets; int numbatches; --- 1498,1503 *** *** 1538,1550 /* * Cost of computing hash function: must do it once per input tuple. We !* charge one cpu_operator_cost for each column's hash function. * * XXX when a hashclause is more complex than a single operator, we really * should charge the extra eval costs of the left or right side, as * appropriate, here. This seems more work than it's worth at the moment. */ ! startup_cost += cpu_operator_cost * num_hashclauses * inner_path_rows; run_cost += cpu_operator_cost * num_hashclauses * outer_path_rows; /* Get hash table size that executor would use for inner relation */ --- 1534,1549 /* * Cost of computing hash function: must do it once per input tuple. We !* charge one cpu_operator_cost for each column's hash function. Also, !* tack on one cpu_tuple_cost pe
Re: [HACKERS] --with-libxml build failures on OS X
Seneca Cunningham wrote: > jackal's builds have been failing since xml.c started using > xmlSaveToBuffer. It turns out that xmlSaveToBuffer is not yet > implemented in the version of libxml2 that comes with OS X (10.4.8), > but it is being implicitly defined during the build. I have changed it so that it checks for xmlSaveToBuffer in configure and mentions the minimum required version. -- 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] Erronous sort used in query plan
Tom Lane wrote: Shane Ambler <[EMAIL PROTECTED]> writes: I am putting together searches on the catalog info and came up with a select that was rather slow and I noticed that in the explain analyze there is a sort step on one of the left joins which I don't think belongs there. Well, it's certainly necessary in context because it's preparing the data for the merge join immediately above it. The correct question is why is the thing using a merge join here, when a hash join would be cheaper? I dug through this and found out that the hash join is estimated as cheaper, right up till the last step of cost_hashjoin: /* * Bias against putting larger relation on inside. We don't want an * absolute prohibition, though, since larger relation might have better * bucketsize --- and we can't trust the size estimates unreservedly, * anyway. Instead, inflate the run cost by the square root of the size * ratio. (Why square root? No real good reason, but it seems * reasonable...) * * Note: before 7.4 we implemented this by inflating startup cost; but if * there's a disable_cost component in the input paths' startup cost, that * unfairly penalizes the hash. Probably it'd be better to keep track of * disable penalty separately from cost. */ if (innerbytes > outerbytes && outerbytes > 0) run_cost *= sqrt(innerbytes / outerbytes); In this example, the data volume from the join of everything else is estimated as less than what needs to be fetched from pg_proc, and so this bias kicks in, and the cost estimate roughly doubles. Unfortunately, because it's a LEFT JOIN, we'll never consider hashjoin in the other direction and so the hash loses out to the mergejoin. It seems clear to me that we ought not impose a bias unless the join type is such that both directions of hashing are feasible. I wonder also if the bias is too large ... but there's not really evidence for or against that in this example. The point is that this code implicitly assumes both directions will be tried, and they won't. I think that the selected sort (or at least the merge join) is incorrect - the column sorted (or both actually) is linking the current record in pg_operator with the oid in the pg_proc - it will only return one row. If one of the pg_type joins is changed, it then sorts on the oid of pg_operator as the foreign table - again this will only return one row. I would think that the foreign oid would indicate to the planner that it will only find one foreign row to link with. I can see that the error I made created a funny (probably useless actually) link that would throw things out, but I would expect it to create bad planning for the two joins that are in error not a non-related one to one join. If a sort/merge join was created from this and used to satisfy this join I would accept that as part of what I unintentionally requested but instead it generates a sort/merge join on a join that links one current record to one foreign record and has nothing in common with the joins in error. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(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
[HACKERS] Full page writes
It occurs to me that we don't need to write full page writes on newly extended pages. Is that optimization in the code already? I would check except I'm not even sure where to look for it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] SGML index build fix
Tom Lane wrote: > Perhaps even more to the point, what makes you think that someone > will notice the warning? If the docs build is one step in an > automated build process, this seems unlikely. Taking a closer look, it's pretty much guaranteed that no one will see them, because the targets they are attached to are intermediate, normally followed by latex runs. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Erronous sort used in query plan
Shane Ambler <[EMAIL PROTECTED]> writes: > I am putting together searches on the catalog info and came up with a > select that was rather slow and I noticed that in the explain analyze > there is a sort step on one of the left joins which I don't think > belongs there. Well, it's certainly necessary in context because it's preparing the data for the merge join immediately above it. The correct question is why is the thing using a merge join here, when a hash join would be cheaper? I dug through this and found out that the hash join is estimated as cheaper, right up till the last step of cost_hashjoin: /* * Bias against putting larger relation on inside. We don't want an * absolute prohibition, though, since larger relation might have better * bucketsize --- and we can't trust the size estimates unreservedly, * anyway. Instead, inflate the run cost by the square root of the size * ratio. (Why square root? No real good reason, but it seems * reasonable...) * * Note: before 7.4 we implemented this by inflating startup cost; but if * there's a disable_cost component in the input paths' startup cost, that * unfairly penalizes the hash. Probably it'd be better to keep track of * disable penalty separately from cost. */ if (innerbytes > outerbytes && outerbytes > 0) run_cost *= sqrt(innerbytes / outerbytes); In this example, the data volume from the join of everything else is estimated as less than what needs to be fetched from pg_proc, and so this bias kicks in, and the cost estimate roughly doubles. Unfortunately, because it's a LEFT JOIN, we'll never consider hashjoin in the other direction and so the hash loses out to the mergejoin. It seems clear to me that we ought not impose a bias unless the join type is such that both directions of hashing are feasible. I wonder also if the bias is too large ... but there's not really evidence for or against that in this example. The point is that this code implicitly assumes both directions will be tried, and they won't. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] security definer default for some PL languages (SQL/PSM)?
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), but > PostgreSQL default is SECURITY CALLLER. Is acceptable to define security > flag in dependency to used language? I'd vote no, even if Peter is wrong and you're right about what the spec says. A PL gets to set the rules within its function body, not outside. I prefare security invoker too. It's secure. This question is again here over some years. Spec knows both, but doesn't speak clearly which is default. From my view SECURITY DEFINER is more natural and consistent in SQL framework (like views) and maybe a little bit simpler for some beginers. My view isn't too important. I checked this topic again and I thing so default depends on implementation now. Next you'll be telling us that the standard requires that the CREATE FUNCTION not use a dollar-quoted function body ... to which the answer will be "too bad". I think the principle of least surprise dictates that security properties shouldn't be inconsistent across PLs. I am finding solution and concensus. Propably CREATE FUNCTION without dollar-quoted function body is more elegant, but it means big enhancing of main parser. It hasn't sence with external parser like plpgsql or plpgpsm, and I don't wont to do it now. Our syntax simply allow more languages and I really haven't problem with it. last note: SQL/PSM doesn't specify syntax for CREATE FUNCTION or CREATE PROCEDURE statement. So it is possible be compatible with SQL/PSM with dollar-quoted function body. With any IDEs this topic isnt too important. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
On Sun, 2007-01-07 at 11:29 -0500, Tom Lane wrote: > I wrote: > > ... The active-portal kluge that you've just > > mentioned is nothing but a kluge, proving that you thought of some cases > > where it would fail. But I doubt you thought of everything. > > BTW, a sufficient counterexample for that kluge is that neither SPI or > SQL-function execution use a separate portal for invoked commands. Thus > testing whether there's only one active portal isn't sufficient to prove > that you're not inside a function executing in serializable mode, and > thus it could have a transaction snapshot predating the COPY. Chewing the last pieces of my Bowler hat while reading. I don't have many left ;-( > It's conceivable that it's safe anyway, or could be made so with some > rejiggering of the tests in tqual.c, but counting active portals doesn't > do anything to help. I'll rethink, but as you say, with separate proposal and patch. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
On Sun, 2007-01-07 at 11:14 -0500, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: > >> I think you just talked yourself out of getting this patch applied. > > > Maybe; what would be your explanation? > > The main reason is that you were guilty of false advertising. It was not my intention to do that, but I see that is how it has come out. I am at fault and will withdraw that part of the patch. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] --with-libxml build failures on OS X
jackal's builds have been failing since xml.c started using xmlSaveToBuffer. It turns out that xmlSaveToBuffer is not yet implemented in the version of libxml2 that comes with OS X (10.4.8), but it is being implicitly defined during the build. The version of libxml2 on jackal is 2.6.21, but the requisite function was implemented in 2.6.23. The version of libxml used can be found in libxml/xmlversion.h in numerous forms. -- Seneca [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] proposal: catch warnings
What is problem? ANSI SQL has different model of handling exception than postgresql. It doesn't distinguishes between warnings and exception. Simply some sqlstate clases are reservated for warnings and other's for exception. But all sqlstate's (without '0') can be handled via any CONTINUE, EXIT or UNDO handler. Exceptions are not problem. But I cannot map sqlstates '01xxx' and '02xxx' to exception because they internally do rollback. I have to use different elevel. I cannot trap warnings on plpgpsm level, because in this moment I will lost an possibility of traping warnings from others PL. I can use warning for signaling any safe event (don't need rollaback) from this PL. Currently I save only last warning, but it is possible safe warnings info to any queue. What in the world does it mean to "catch" a warning? If your intention is to process arbitrary user-defined code while inside the error subsystem, I can tell you right now that it's unlikely to work. No, it means, it process user-defined code related to some compound statement immediate after some statement which signals sqlstate '01xxx' or '02xxx'. Exceptions are diffrent, they do rollback before call exception's handle. I use callback function only for decision if there is any possible warning handler and for saving struct edata. After excecuton any statement I check this struct and call handler statement. > Simply solution's is adding one callback to > error's processing of errors on level WARNING. I can't get excited about hooks that are defined in such a way that there can be only one user of the hook ... if it's useful to you, it's probably useful to someone else too. Propably we can use it for faster catch no_data in plpgsql or for enhancing of statement RAISE in future. Any SQL/PSM implementation have to solve this task. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
"Simon Riggs" <[EMAIL PROTECTED]> writes: > There is no failure condition where the rows continue to exist > on disk && the table relfilenode shows a committed transaction pointing > to the file containing the marked-valid-but-actually-not rows. What of BEGIN; CREATE TABLE foo ...; SAVEPOINT x; COPY foo FROM ...; ROLLBACK TO x; COMMIT; regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] security definer default for some PL languages (SQL/PSM)?
Pavel Stehule wrote: > SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), I can't find this in the standard. Where did you get this information? -- Jim Melton, SQL's stored procedures, page 43, next: blom98sqlpsm: 3.3.1 Access rights to data By default, the stored routine can access the same data as the creator of the routine. Hence all tables that the creator of a routine can access and/or change can also be accessed and changed by the routine but I can't find any better info now, so it's can be propably implementation-defined. _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] security definer default for some PL languages (SQL/PSM)?
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), but > PostgreSQL default is SECURITY CALLLER. Is acceptable to define security > flag in dependency to used language? I'd vote no, even if Peter is wrong and you're right about what the spec says. A PL gets to set the rules within its function body, not outside. Next you'll be telling us that the standard requires that the CREATE FUNCTION not use a dollar-quoted function body ... to which the answer will be "too bad". I think the principle of least surprise dictates that security properties shouldn't be inconsistent across PLs. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] proposal: catch warnings
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that > warnings are catchable too. What in the world does it mean to "catch" a warning? If your intention is to process arbitrary user-defined code while inside the error subsystem, I can tell you right now that it's unlikely to work. > Simply solution's is adding one callback to > error's processing of errors on level WARNING. I can't get excited about hooks that are defined in such a way that there can be only one user of the hook ... if it's useful to you, it's probably useful to someone else too. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] SGML index build fix
Bruce Momjian <[EMAIL PROTECTED]> writes: > Martijn van Oosterhout wrote: >> I don't know enough about the relevent tool to know if they actually >> generate a warning about whether they need to be rerun. In any case it >> seems a much better approach to simply run it again when needed rather >> than printing a warning. > The problem is that there is no indication from the make (no warning) > that you have to rerun, and it isn't something people are used to doing > like with latex. If the objective is to make it safe against people who do not understand how the tools work, then I still complain that this method is insufficient. All you are testing is whether an index was generated, not whether it was correct (ie, up to date). A valid test would be along the lines of comparing the pre-run and post-run copies of the index data to see if they're the same. Perhaps even more to the point, what makes you think that someone will notice the warning? If the docs build is one step in an automated build process, this seems unlikely. 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] [PATCHES] COPY with no WAL, in certain circumstances
I wrote: > ... The active-portal kluge that you've just > mentioned is nothing but a kluge, proving that you thought of some cases > where it would fail. But I doubt you thought of everything. BTW, a sufficient counterexample for that kluge is that neither SPI or SQL-function execution use a separate portal for invoked commands. Thus testing whether there's only one active portal isn't sufficient to prove that you're not inside a function executing in serializable mode, and thus it could have a transaction snapshot predating the COPY. It's conceivable that it's safe anyway, or could be made so with some rejiggering of the tests in tqual.c, but counting active portals doesn't do anything to help. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
"Simon Riggs" <[EMAIL PROTECTED]> writes: > On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: >> I think you just talked yourself out of getting this patch applied. > Maybe; what would be your explanation? The main reason is that you were guilty of false advertising. This patch was described as being an application of a known-and-agreed-safe optimization to a new case, viz letting COPY into a new table use a whole-file fsync instead of WAL-logging individual records. I suspect most people didn't look at it closely because it sounded like nothing very new; I certainly didn't. Now we find out that you've also decided you can subvert the MVCC system in the name of speed. This is NOT something the hackers community has discussed and agreed to, and I for one doubt that it's safe. The active-portal kluge that you've just mentioned is nothing but a kluge, proving that you thought of some cases where it would fail. But I doubt you thought of everything. In any case the correct method for dealing with a new optimization of questionable safety or value is to submit it as a separate patch, not to hope that the committer will fail to notice that the patch doesn't do what you said it did. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] security definer default for some PL languages (SQL/PSM)?
Pavel Stehule wrote: > SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), I can't find this in the standard. Where did you get this information? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] proposal: catch warnings
> > > >Does the PSM warning error handler run in the same transaction or a > >separate subtransaction? Can transaction execution continue afterwards? > > > > It's depend. Continue and exit warning handlers run in the same transaction, > undo handler has separate subtransaction. It works well. For patterns used > in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not > found). Hmmm. SQLSTATE 02000 NO_DATA doesn't seem to be raised anywhere by the backend, though it is listed by ECPG. Are you thinking of the special variable FOUND, which doesn't raise an exception in PL/pgSQL, or the PostgreSQL PL/pgSQL exception: NO_DATA_FOUND (SQLSTATE P0002) which isn't actually an SQL ERROR at all. (Definitely an exception in PL/SQL?) ISTM that if we have an exception defined like this in PL/pgSQL EXCEPTION WHEN NO_DATA_FOUND THEN block END; that we wouldn't need to wrap it in a sub-transaction, because the earlier statements need not be rolled back when it occurs. Perhaps you can scan for this condition in the PSM code, rather than getting the backend to throw a different kind of error? FOUND is only one value, but I have to detect all values from sqlstate classes '02' and '01'. Without backend change I am not able catch warnings from other PL. Hook is general solution. Pavel _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] SGML index build fix
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Sun, Jan 07, 2007 at 12:42:06AM -0500, Bruce Momjian wrote: > > Joshua D. Drake wrote: > > > On Sat, 2007-01-06 at 23:38 -0500, Tom Lane wrote: > > > > Everyone using these tools knows about the two-pass behavior. > > > > > > No, not everyone knows. In fact I would argue that most do not know. It > > > isn't intuitive to the process. You *expect* that an index will be made. > > > > The idea for the warning message actually came from Peter. > > FWIW, I have this problem with LaTeX also, which needs multiple passes > occasionally to fix cross-references and idexes and stuff. The solution > I have in the makefile is a fragment like the following: > > while egrep -q "^LaTeX Warning:.*Rerun to" logfile ; do > rm logfile > latex taxfile > done Our Makefile has: %.dvi: %.tex-ps @rm -f $*.aux $*.log # multiple runs are necessary to create proper intra-document links jadetex $< jadetex $< jadetex $< so there should be no reason for you to have to rerun. > > I don't know enough about the relevent tool to know if they actually > generate a warning about whether they need to be rerun. In any case it > seems a much better approach to simply run it again when needed rather > than printing a warning. The problem is that there is no indication from the make (no warning) that you have to rerun, and it isn't something people are used to doing like with latex. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] [Fwd: Index Advisor]
On 1/7/07, Gurjeet Singh <[EMAIL PROTECTED]> wrote: contrib/pg_advise_index/sample_error_messages.txt also shows an interesting usage, where advise_index is actually a VIEW with a RULE that redirects INSERTs into another advise_index_data table. Also, the DDL for the advise_index table can be found in advise_index.create.sql script in the contrib module. Regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [PATCHES] [HACKERS] [Fwd: Index Advisor]
On 1/7/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: I have looked over this patch, Thanks I think we have to decide if we want this, and whether it should be in /contrib or fully integrated into the backend. Well, as already said, the plugin architecture gives others a way to develop and deploy their own index advisers, or even something else that does nifty things with the generated plan! I am thinking the API needs to be simpified, perhaps by removing the system table and having the recommendations just logged to the server logs. The advise_index table not required to be a system table anymore, as required by the original patch. It can be any table/view on which the executing user has INSERT permissions. The Adviser internally builds an 'INSERT INTO advise_index ...' statement and executes it through SPI. So, it actually behaves as if the user is doing and INSERT. As a side effect, if the EXPLAIN is done in a transaction, which is later rolled back, the recommendations inserted in the advise_index will also be lost! contrib/pg_advise_index/sample_error_messages.txt also shows an interesting usage, where advise_index is actually a VIEW with a RULE that redirects INSERTs into another advise_index_data table. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
On Sun, 2007-01-07 at 12:59 +0100, Martijn van Oosterhout wrote: > On Sun, Jan 07, 2007 at 11:46:29AM +, Simon Riggs wrote: > > On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: > > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > > The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as > > > > well. > > > > > > I think you just talked yourself out of getting this patch applied. > > > > Maybe; what would be your explanation? Do you have a failure case you > > know of? Perhaps if one exists, there is another route. > > One thing I pondered while looking at this: how do you know the user is > going to commit the transaction after the COPY is complete. Could they > run analyze or vacuum or some other DDL command on the table that would > get confused by the disparity between the hint bits and the xlog. If it crashes, we'll clean up the file. At end of statement it is synced to disk. There is no failure condition where the rows continue to exist on disk && the table relfilenode shows a committed transaction pointing to the file containing the marked-valid-but-actually-not rows. There is a failure condition where the new relfilenode is on disk, but the version of the table that points to that will not be visible. (You can't run a VACUUM inside a transaction block.) Everybody else is locked out because the CREATE or TRUNCATE has taken an AccessExclusiveLock. I've just re-checked the conditions from tqual.c and they all check, AFAICS. There would be a problem *if* it was possible to issue a self-referential COPY, like this: COPY foo FROM (select * from foo) which would exhibit the Halloween problem. But this is not yet possible, and if it were we would be able to check for that and avoid it. I'm not saying I haven't made a mistake, but I've done lots of thinking and checking to confirm that this is a valid thing to do. That in itself is never enough, which is why I/we talk together. If somebody does find a problem, its a small thing to remove that from the patch, since it is an additional enhancement on top of the basic WAL removal. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] proposal: catch warnings
On Sun, 2007-01-07 at 11:20 +0100, Pavel Stehule wrote: > > > >On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote: > > > > > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires > >that > > > warnings are catchable too. Simply solution's is adding one callback to > > > error's processing of errors on level WARNING. > > > >Exceptions are run within their own subtransaction, so the exception > >handling code runs separately. > > > >Does the PSM warning error handler run in the same transaction or a > >separate subtransaction? Can transaction execution continue afterwards? > > > > It's depend. Continue and exit warning handlers run in the same transaction, > undo handler has separate subtransaction. It works well. For patterns used > in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not > found). Hmmm. SQLSTATE 02000 NO_DATA doesn't seem to be raised anywhere by the backend, though it is listed by ECPG. Are you thinking of the special variable FOUND, which doesn't raise an exception in PL/pgSQL, or the PostgreSQL PL/pgSQL exception: NO_DATA_FOUND (SQLSTATE P0002) which isn't actually an SQL ERROR at all. (Definitely an exception in PL/SQL?) ISTM that if we have an exception defined like this in PL/pgSQL EXCEPTION WHEN NO_DATA_FOUND THEN block END; that we wouldn't need to wrap it in a sub-transaction, because the earlier statements need not be rolled back when it occurs. Perhaps you can scan for this condition in the PSM code, rather than getting the backend to throw a different kind of error? -- 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] [PATCHES] COPY with no WAL, in certain circumstances
On Sun, Jan 07, 2007 at 11:46:29AM +, Simon Riggs wrote: > On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as > > > well. > > > > I think you just talked yourself out of getting this patch applied. > > Maybe; what would be your explanation? Do you have a failure case you > know of? Perhaps if one exists, there is another route. One thing I pondered while looking at this: how do you know the user is going to commit the transaction after the COPY is complete. Could they run analyze or vacuum or some other DDL command on the table that would get confused by the disparity between the hint bits and the xlog. 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] [PATCHES] COPY with no WAL, in certain circumstances
On Sun, 2007-01-07 at 03:53 -0500, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: > >> Uh ... what in the world has an active portal got to do with it? > >> I think you've confused snapshot considerations with crash recovery. > > > The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as > > well. > > I think you just talked yourself out of getting this patch applied. Maybe; what would be your explanation? Do you have a failure case you know of? Perhaps if one exists, there is another route. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] SGML index build fix
On Sun, Jan 07, 2007 at 12:42:06AM -0500, Bruce Momjian wrote: > Joshua D. Drake wrote: > > On Sat, 2007-01-06 at 23:38 -0500, Tom Lane wrote: > > > Everyone using these tools knows about the two-pass behavior. > > > > No, not everyone knows. In fact I would argue that most do not know. It > > isn't intuitive to the process. You *expect* that an index will be made. > > The idea for the warning message actually came from Peter. FWIW, I have this problem with LaTeX also, which needs multiple passes occasionally to fix cross-references and idexes and stuff. The solution I have in the makefile is a fragment like the following: while egrep -q "^LaTeX Warning:.*Rerun to" logfile ; do rm logfile latex taxfile done I don't know enough about the relevent tool to know if they actually generate a warning about whether they need to be rerun. In any case it seems a much better approach to simply run it again when needed rather than printing a warning. 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
[HACKERS] ECPG regression test failures on Solaris 10/x86_64 with Sun studio 11
On solaris 10U2 using the Sun compiler we get segmentation faults in nearly all of the ECPG regression tests on a 64bit built. the resulting cores look similiar to: Current function is ECPGget_variable 91 var->pointer = va_arg(APREF, char *); (dbx) where =>[1] ECPGget_variable(ap = 0xfd7fffdff6d0, type = ECPGt_char, var = 0x41d9d0, indicator = '\001'), line 91 in "execute.c" [2] create_statement(lineno = 28, compat = 0, force_indicator = 1, connection = 0x413210, stmt = 0xfd7fffdff818, query = 0x4013e8 "select current_database () ", ap = 0xfd7fffdff828), line 192 in "execute.c" [3] ECPGdo(lineno = 28, compat = 0, force_indicator = 1, connection_name = (nil), query = 0x4013e8 "select current_database () ", ... = 0x1, ...), line 1548 in "execute.c" [4] main(), line 30 in "test2.pgc" 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
[HACKERS] Erronous sort used in query plan
I am putting together searches on the catalog info and came up with a select that was rather slow and I noticed that in the explain analyze there is a sort step on one of the left joins which I don't think belongs there. I found the small error in my query (using tl.oid instead of tr.oid and tres.oid) that caused the query to slow down and generate the sort in the plan but am not sure that the given condition should even generate a sort step and if it does then I believe it should be a (more?) stable decision. Removing one of the left join's that is in error (tr or tres) changes the column that is sorted, neither of which is related to the join/s that appear to generate the step. With tl, tr and tres in place the sort is performed on pjoin.oid. Removing or correcting either tr or tres the sort is changed to perform on olsort.oid. Removing or correcting both tr and tres removes the sort from the plan. Also - removing all the pg_operator joins the sort is still there (on pjoin.oid) but if I remove one of the erroneous joins as well the sort goes. (correcting one of the joins leaves the sort there but removing it removes the sort) Using postgres 8.2.0 on Mac OSX 10.4.8 The full query is - explain analyze SELECT o.oid as "OID" , n.nspname as "Schema" , o.oprname as "Name" , r.rolname as "Owner" , CASE WHEN o.oprkind='b' THEN 'infix(left and right)' WHEN o.oprkind='l' THEN 'prefix (left)' WHEN o.oprkind='r' THEN 'postfix (right)' END as "Kind" , CASE WHEN o.oprcanhash='t' THEN 'Yes' WHEN o.oprcanhash='f' THEN 'No' END as "Supports Hash Joins" , tl.typname as "Left Operand" , tr.typname as "Right Operand" , tres.typname as "Result Type" , ocom.oprname as "Commutator Operator" , onegate.oprname as "Negator Operator" , olsort.oprname as "Left Sort Operator" , orsort.oprname as "Right Sort Operator" , oltcm.oprname as "Less Than Operator" , ogtcm.oprname as "Greater Than Operator" , pcode.proname as "Operator Function" , prest.proname as "Restriction Selectivity Function" , pjoin.proname as "Join Selectivity Function" FROM pg_catalog.pg_operator o left join pg_catalog.pg_namespace n ON n.oid = o.oprnamespace left join pg_catalog.pg_roles r on r.oid=o.oprowner left join pg_catalog.pg_type tl on tl.oid=o.oprleft left join pg_catalog.pg_type tr on tl.oid=o.oprright left join pg_catalog.pg_type tres on tl.oid=o.oprresult left join pg_catalog.pg_operator ocom on ocom.oid=o.oprcom left join pg_catalog.pg_operator onegate on onegate.oid=o.oprnegate left join pg_catalog.pg_operator oneg on oneg.oid=o.oprnegate left join pg_catalog.pg_operator olsort on olsort.oid=o.oprlsortop left join pg_catalog.pg_operator orsort on orsort.oid=o.oprrsortop left join pg_catalog.pg_operator oltcm on oltcm.oid=o.oprltcmpop left join pg_catalog.pg_operator ogtcm on ogtcm.oid=o.oprgtcmpop left join pg_catalog.pg_proc pcode on pcode.oid=o.oprcode left join pg_catalog.pg_proc prest on prest.oid=o.oprrest left join pg_catalog.pg_proc pjoin on pjoin.oid=o.oprjoin WHERE n.nspname like 'public' I have attached a copy of the query and plan. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz explain analyze SELECT o.oid as "OID" , n.nspname as "Schema" , o.oprname as "Name" , r.rolname as "Owner" , CASE WHEN o.oprkind='b' THEN 'infix(left and right)' WHEN o.oprkind='l' THEN 'prefix (left)' WHEN o.oprkind='r' THEN 'postfix (right)' END as "Kind" , CASE WHEN o.oprcanhash='t' THEN 'Yes' WHEN o.oprcanhash='f' THEN 'No' END as "Supports Hash Joins" , tl.typname as "Left Operand" , tr.typname as "Right Operand" , tres.typname as "Result Type" , ocom.oprname as "Commutator Operator" , onegate.oprname as "Negator Operator" , olsort.oprname as "Left Sort Operator" , orsort.oprname as "Right Sort Operator" , oltcm.oprname as "Less Than Operator" , ogtcm.oprname as "Greater Than Operator" , pcode.proname as "Operator Function" , prest.proname as "Restriction Selectivity Function" , pjoin.proname as "Join Selectivity Function" FROM pg_catalog.pg_operator o left join pg_catalog.pg_namespace n ON n.oid = o.oprnamespace left join pg_catalog.pg_roles r on r.oid=o.oprowner left join pg_catalog.pg_type tl on tl.oid=o.oprleft left join pg_catalog.pg_type tr on tl.oid=o.oprright left join pg_catalog.pg_type tres on tl.oid=o.oprresult left join pg_catalog.pg_operator ocom on ocom.oid=o.oprcom left join pg_catalog.pg_operator onegate on onegate.oid=o.oprnegate left join pg_catalog.pg_operator oneg on oneg.oid=o.oprnegate left join pg_catalog.pg_operator olsort on olsort.oid=o.oprlsortop left join pg_catalog.pg_operator orsort on orsort.oid=o.oprrsortop left join pg_catalog.pg_operator oltcm on oltcm.oid=o.oprltcmpop left join pg_catalog.pg_operator ogtcm on ogtcm.oid=o.oprgtcmpop left join pg_catalog.pg_proc pcode on pcode.oid=o.oprcode left join pg_catalog.pg_proc prest on prest.oid=o.oprrest left join pg_catalog.pg_proc pjoin on pjoin.oid=o.oprjoin WHERE n.nspname like 'public' ORDER BY lower(n.nspname)
Re: [HACKERS] proposal: catch warnings
On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote: > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that > warnings are catchable too. Simply solution's is adding one callback to > error's processing of errors on level WARNING. Exceptions are run within their own subtransaction, so the exception handling code runs separately. Does the PSM warning error handler run in the same transaction or a separate subtransaction? Can transaction execution continue afterwards? It's depend. Continue and exit warning handlers run in the same transaction, undo handler has separate subtransaction. It works well. For patterns used in SQL/PSM is important fast continue handler for SQLSTATE '02000' (not found). Transactions are controlled in plpgpsm code (like plpgsql). This patch allows detecting signalled warning (after processing and finishing any statement). Pavel Stehule _ Find sales, coupons, and free shipping, all in one place! MSN Shopping Sales & Deals http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] proposal: catch warnings
On Sun, 2007-01-07 at 09:59 +0100, Pavel Stehule wrote: > PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that > warnings are catchable too. Simply solution's is adding one callback to > error's processing of errors on level WARNING. Exceptions are run within their own subtransaction, so the exception handling code runs separately. Does the PSM warning error handler run in the same transaction or a separate subtransaction? Can transaction execution continue afterwards? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 8.3 pending patch queue
Bruce Momjian wrote: The problem there is that the web site references these, so changing the URL for every release is odd, Not a problem though - it's trivial for us to update whatever webpages link to it. > plus right now both queues are for 8.3. Well, yeah - that's why it's confusing! Regards, Dave ---(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
[HACKERS] security definer default for some PL languages (SQL/PSM)?
Hello, SQL/PSM default for SQL procedures are SECURITY DEFINER (like views), but PostgreSQL default is SECURITY CALLLER. Is acceptable to define security flag in dependency to used language? Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] [Fwd: Index Advisor]
On Sat, 2007-01-06 at 16:08 -0500, Bruce Momjian wrote: > I have looked over this patch, and it completes part of this TODO item: > > o Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM > ANALYZE, and CLUSTER > It involves a patch to the backend, and a /contrib module to access it. > > I think we have to decide if we want this, and whether it should be in > /contrib or fully integrated into the backend. I am thinking the API > needs to be simpified, perhaps by removing the system table and having > the recommendations just logged to the server logs. The patch to the backend is in the form of a plugin API, which does nothing when there is no plugin. IMHO there is a significant amount of code there and it is too early to try to get all of that into the backend, especially when more tested things like Tsearch2 haven't. Plugins are cool because we can update them without needing to bounce a production server, which means the code can evolve faster than it would do if it was directly in the backend. (You do need to reconnect to allow local_preload_libraries to be re-read). Tuning out the wierd recommendations will take some time/effort - I don't know there are any, but then my gut tells me there very likely are some. The output isn't a system table, its a user space table. The reason for having an output table is that we can use multiple invocations of the adviser to build up a set of new indexes for a complete workload. Reading things back out of the log would make that more difficult, since we really want this to be automated by pgAdmin et al. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] proposal: catch warnings
Hello,
PostgreSQL allow only catch exception (elevel ERROR). SQL/PSM requires that
warnings are catchable too. Simply solution's is adding one callback to
error's processing of errors on level WARNING.
typedef struct WarningHandlerCallback
{
bool(*callback) (void *arg, ErrorData *edata);
void *arg;
} WarningHandlerCallback;
extern DLLIMPORT WarningHandlerCallback *warning_handler;
Callback function returns true if accept warning and process it. This
function is called from errfinish()
/*
* Emit the message to the right places. If warning_handler is
defined,
* try use warning_handler. Emit message only if handler don't
accept
* message (returns false). Warning handlers are used in PL/pgPSM
language.
*/
if (elevel == WARNING)
{
bool handled = false;
if (warning_handler)
handled =
(*warning_handler->callback)(warning_handler->arg,edata);
if (!handled)
EmitErrorReport();
}
else
EmitErrorReport();
It's propably usable only for SQL/PSM implementation, and it's one from two
necessery hacks to core for this PL (second is scrollable cursor's support).
But without this hook I cannot simply distribute plpgpsm.
Any comments?
Best regards
Pavel Stehule
_
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
---(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] 8.3 pending patch queue
On Sat, 2007-01-06 at 16:29 -0500, Bruce Momjian wrote: > The issue is that the _hold_ patches are for patches that arrived after > feature freeze. The patches that arrived after 8.2 was released don't > go in there because it might cause confusion. Right, which is why I'm pointing it out; they did all arrive before 8.2 > I also have to control > how quickly I push out patches from the queue so as not to overwhelm > folks. Yes, I see the challenge. I'm not hassling you, just asking for stuff to be added appropriately to the queue. I just used too many/wrong words in the request; again, sorry. Just found another one, which was issued after 8.2. pg_standby, latest version: http://archives.postgresql.org/pgsql-patches/2006-12/msg00179.php -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
"Simon Riggs" <[EMAIL PROTECTED]> writes: > On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: >> Uh ... what in the world has an active portal got to do with it? >> I think you've confused snapshot considerations with crash recovery. > The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as > well. I think you just talked yourself out of getting this patch applied. 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] Parsing ambiguity for ORDER BY ... NULLS FIRST/LAST
On Sat, 2007-01-06 at 13:05 -0500, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > Presumably you could put extra grammar rules in to throw errors when you > > see FROM NULLS FIRST? > > It'll throw an error just fine without any extra rules, because there > won't be any production allowing the NULLS_FIRST pseudo-token there. > You already see this in 8.2: > > regression=# select * from with cascaded; > ERROR: syntax error at or near "cascaded" > LINE 1: select * from with cascaded; >^ OK, Sounds good. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > The rule is: if the relfilenode for a table is new in this transaction > > (and therefore the whole things will be dropped at end-of-transaction) > > then *all* COPY commands are able to avoid writing WAL safely, if: > > - PITR is not enabled > > - there is no active portal (which could have been opened on an earlier > > commandid and could therefore see data prior to the switch to the new > > relfilenode). In those cases, *not* using WAL causes no problems at all, > > so sleep well without it. > > Uh ... what in the world has an active portal got to do with it? > I think you've confused snapshot considerations with crash recovery. The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as well. So the active portal consideration does apply in this case. (We discussed about a year ago the idea of setting FrozenTransactionId, which I now agree wouldn't work, but setting the hint bits does work.). That is important, because otherwise the first person to read the newly loaded table has to re-write the whole table again; right now we ignore that cost as being associated with the original COPY, but from most users perspective it is. Its common practice to issue a select count(*) from table after its been loaded, so that later readers of the table don't suffer. Which makes me think we can still use the no-WAL optimisation, but just without setting HEAP_XMIN_COMMITTED when there is an active portal. (I should also mention that the creation of the relfilenode can happen in earlier committed subtransactions also. There is also a great big list of commands that throw implicit transactions, all of which cannot therefore be used with this optimisation either.) -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
On Sat, 2007-01-06 at 21:32 -0500, Tom Lane wrote: > Robert Treat <[EMAIL PROTECTED]> writes: > > On Saturday 06 January 2007 16:36, Simon Riggs wrote: > > > >> BEGIN; > >> CREATE TABLE foo... > >> INSERT INTO foo--uses WAL > >> COPY foo.. --no WAL > >> INSERT INTO foo--uses WAL > >> COPY foo.. --no WAL > >> INSERT INTO foo--uses WAL > >> COPY foo...--no WAL > >> COMMIT; > > > Is there some technical reason that the INSERT statements need to use WAL > > in > > these scenarios? > > First, there's enough other overhead to an INSERT that you'd not save > much percentagewise. Second, not using WAL doesn't come for free: the > cost is having to fsync the whole table afterwards. So it really only > makes sense for commands that one can expect are writing pretty much > all of the table. I could easily see it being a net loss for individual > INSERTs. Agreed. We agreed that before, on the original design thread. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Mark/Restore and avoiding RandomAccess sorts
On Sat, 2007-01-06 at 17:06 -0500, Bruce Momjian wrote: > I saw no replies to this. Me neither. I take it that means its a very good idea and we should add a TODO -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
