Re: [HACKERS] Remaining VACUUM patches
Tom Lane [EMAIL PROTECTED] wrote: The other patch was ITAGAKI Takahiro's patch to fix n_dead_tuples in pgstats after VACUUM when there is concurrent update activity. I objected (and still object) to this patch because it allows n_dead_tuples to drift arbitrarily far away from reality There was some discussion about better ways to do it, IIRC, but no new patch has been submitted. I wrote the patch *after* the dicussion (and it is still valid with some hunks). It sets n_dead_tuples as the follows: | n_dead_tuples ---+--- (1) At the start of vacuum | N (2) At the end of vacuum | M (=N) (3) After updating stats | M - N So if we don't update the table during vacuum, n_dead_tuples will be definitely zero. Even if there are some updates with inaccurate stats in a vacuum, only the errors generated in the vacuum are left. Errors generated before the vacuum are completely cleared so that the formula does not enlarge the inaccuracy. I've waited for the completion of Recalculating OldestXmin in a long-running vacuum patch, because it changes the accuracy of (3). But without the recalculating patch, I have no plan to modify my n_dead_tuples patch any further. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] parser dilemma
Tom Lane írta:
...
If anyone seriously wants to propose removing postfix ops from b_expr,
we'd better take it up on someplace more widely read than -patches.
regards, tom lane
OK, I take the bullet and send it to -hackers.
For everyone who don't read -patches, let me reiterate the problem
During developing my GENERATED/IDENTITY patches,
a parser problem turned up.
Currently, DEFAULT is handled as a CONSTRAINT by the parser
to be able to write DEFAULT clause and CONSTRAINT clauses
in any order. Handling GENERATED { ALWAYS | BY DEFAULT}
AS { IDENTITY | ( expression ) } syntax in the same way causes
a conflict between DEFAULT and b_expr as discovered by Tom Lane.
He proposed two solutions, quote:
The problem comes from cases like
colname coltype DEFAULT 5! GENERATED ...
Since b_expr allows postfix operators, it takes one more token of
lookahead than we have to tell if the default expression is 5!
or 5!GENERATED
There are basically two ways to fix this:
1. Collapse GENERATED ALWAYS and GENERATED BY into single tokens
using filtered_base_yylex.
2. Stop allowing postfix operators in b_expr.
I find #1 a bit icky --- not only does every case added to
filtered_base_yylex slow down parsing a little more, but combined
tokens create rough spots in the parser's behavior. As an example,
both NULLS and FIRST are allegedly unreserved words, so this should
work:
regression=# create table nulls (x int);
CREATE TABLE
regression=# select first.* from nulls first;
ERROR: syntax error at or near first
LINE 1: select first.* from nulls first;
^
regression=#
#2 actually seems like a viable alternative: postfix operators aren't
really in common use, and doing this would not only fix GENERATED but
let us de-reserve a few keywords that are currently reserved. In a
non-exhaustive check I found that COLLATE, DEFERRABLE, and INITIALLY
could become unreserved_keyword if we take out this production:
*** 7429,7436
{ $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, $3, @2); }
| qual_Op b_expr%prec Op
{ $$ = (Node *) makeA_Expr(AEXPR_OP, $1, NULL, $2, @1); }
- | b_expr qual_Op%prec POSTFIXOP
- { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, NULL, @2); }
| b_expr IS DISTINCT FROM b_expr%prec IS
{
$$ = (Node *) makeSimpleA_Expr(AEXPR_DISTINCT, =, $1, $5,
@2);
--- 7550,7555
(Hmm, actually I'm wondering why COLLATE is a keyword at all right
now... but the other two trace directly to the what-comes-after-DEFAULT
issue.)
I proposed a third solution, that is actually standard-conforming
and still leaves the possibility of having postfix operators.
The solution was to admit that DEFAULT is not a CONSTRAINT,
hence not mixable with them. The standard has this syntax:
column definition ::=
column name [ data type or domain name ]
[ default clause | identity column specification | generation
clause ]
[ column constraint definition... ]
[ collate clause ]
This says that DEFAULT | GENERATED ... AS IDENTITY |
GENERATED ALWAYS AS ( expr ) must come after the data type
and before any CONSTRAINTs and the three forms are mutually exclusive.
This can be nicely handled by the parser and the analyzer phase
can save some cycles by not checking for conflicting DEFAULT clauses.
What do people think? Which would be the preferred solution?
Best regards,
Zoltán Böszörményi
--
--
Zoltán Böszörményi
Cybertec Geschwinde Schönig GmbH
http://www.postgresql.at/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Hacking on PostgreSQL via GIT
Hi, Alvaro Herrera wrote: Which is not always what happens in reality. Consider for example that we borrowed some files from NetBSD, OpenBSD, Tcl, zic and others. It would be nice to know exactly at what point we borrowed the file, so we can go to the upstream repo and check if there's any bug fix that we should also apply to our local copy. And we _also_ modify locally the file of course, so just digesting the file we have to get a SHA1 (or whatever) identifier is not an option. I consider such information (i.e. 'where is this file coming from') to be historical information. As such, this information clearly belongs to the VCS sphere and should be tracked and presented by the VCS. Advanced VCSes can import files from other projects and properly track those files or propagate on request. Even subversion can do that to some extent. My point here is: given a decent VCS, you don't need such historical information as often as you do with CVS. You can sit back and let the VCS do the job. (Like looking up, when the last 'import' of the file from the external project happened, what changed and merge those changes back into your (locally modified variant of the) file.) And if you really want to dig in the history of your project, you can ask the VCS, which you are going to need anyway for other historic information. Regards Markus ---(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] Hacking on PostgreSQL via GIT
Hi Jim C. Nasby wrote: I understand the argument about metadata and all, and largely agree with it. But on the other hand I think a version identifier is a critical piece of information; it's just as critical as the file name when it comes to identifying the information contained in the file. If you really want the files in your releases to carry a version identifier, you should let your release process handle that. But often enough, people can't even tell the exact PostgreSQL version they are running. How do you expect them to be able to tell you what version a single file has? For the developers: they have all the history the VCS offers them. There are tags to associate a release with a revision in your repository. And because a decent VCS can handle all the diff'ing, patching and merging you normally need, you shouldn't ever have to process files outside of your repository. So what exactly is the purpose of a version identifier within the file's contents? For whom could such a thing be good for? Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Allowing COPY into views
Hello, I would like to submit a patch so that the COPY statement can import data into a view. (Maybe if this works I'll see about copying out of a view.) Rather than spend a whole lot of time figuring out all the calls to use and all the detail, I'm going to go ahead and post now. That way if this is simply not going to fly I don't have to spend any more time on it. Otherwise, I'll post more as I work it out. Any and all help and/or comment is appreciated. The basic approach I'm thinking of is to: 1) Check to see if the view has an rule that allows INSERT, if not then exit with an error. I thought I would use a stored procedure for this but having read the FAQ_DEV perhaps I'll use SearchSysCache(). 2) Allocate memory for char* and construct an 'INSERT ...' statement corresponding to the COPY command that inserts into the view. (Just how much memory should be allocated? Is there a standard buffer pre-alocated somewhere or a standard buffer size?) 3) makeNode(T_PrepareStmt) (What should name be?) 4) Iterate over COPY statement's stmnt-options to produce prepared statement's argtypes. The DefElem arg values can be used without further ado. (I don't need to check to see that they are type T_TypeName because the parser would have taken care of that for the COPY statement, right? (I didn't look.) There's no point in doing a copyObject on the TypeName, right?) 5) Parse the (char*) 'INSERT...' statement. 6) Call PrepareQuery() on the parsed INSERT statement. I'm thinking this will result in a query plan with the rules expanded. 7) Execute the prepared statement for each row of data. Deallocate memory for (char*) 'INSERT...' statement. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(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] Allowing COPY into views
On 04/19/2007 08:41:55 AM, Karl O. Pinc wrote: I would like to submit a patch so that the COPY statement can import data into a view. 2) Allocate memory for char* and construct an 'INSERT ...' statement corresponding to the COPY command that inserts into the view. (Just how much memory should be allocated? Is there a standard buffer pre-alocated somewhere or a standard buffer size?) I'll use a StringInfo. And I forgot, the final step is to add regression tests. One for view with an INSERT rule and one to catch the error for a view without an INSERT rule. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Allowing COPY into views
Karl O. Pinc wrote: (Maybe if this works I'll see about copying out of a view.) You know we already have COPY (SELECT ...) TO ... don't you? cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Allowing COPY into views
Karl O. Pinc wrote: On 04/19/2007 08:41:55 AM, Karl O. Pinc wrote: I would like to submit a patch so that the COPY statement can import data into a view. 2) Allocate memory for char* and construct an 'INSERT ...' statement corresponding to the COPY command that inserts into the view. (Just how much memory should be allocated? Is there a standard buffer pre-alocated somewhere or a standard buffer size?) I'll use a StringInfo. And I forgot, the final step is to add regression tests. One for view with an INSERT rule and one to catch the error for a view without an INSERT rule. I'm not sure the plan is OK as stated. You wouldn't want to force to parse the query again for each row. Rather, create a prepared statement (already parsed, because you obtain it from the parsetree stored in the INSERT rule) to pass to the executor. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Allowing COPY into views
Karl O. Pinc [EMAIL PROTECTED] writes: Rather than spend a whole lot of time figuring out all the calls to use and all the detail, I'm going to go ahead and post now. That way if this is simply not going to fly I don't have to spend any more time on it. Otherwise, I'll post more as I work it out. It probably doesn't make sense to work on just this one case. There are folks looking at update-able views. If we had that then this would simplify to simply recognizing the case and invoking the rewriter to get you the insert statement to use. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Allowing COPY into views
On 04/19/2007 09:33:44 AM, Andrew Dunstan wrote: Karl O. Pinc wrote: (Maybe if this works I'll see about copying out of a view.) You know we already have COPY (SELECT ...) TO ... don't you? Sure. It'd just be syntatic suger for the COPY (SELECT ...) form, so end-users don't have to think about whether they're using a view or a relation. (Dunno if I'll ever get to it anyway) Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Allowing COPY into views
On 04/19/2007 09:39:18 AM, Alvaro Herrera wrote: I'm not sure the plan is OK as stated. You wouldn't want to force to parse the query again for each row. Rather, create a prepared statement (already parsed, because you obtain it from the parsetree stored in the INSERT rule) to pass to the executor. I wasn't going to parse and plan an INSERT for each row of data to COPY. Step 7 is the step that iterates over the rows of COPY data. I make my own PREPAREd insert and execute it in step 7 for each row of COPY data. It seems to me it'd be eaiser to write my own INSERT statement and call raw_parser on it than to try to modify the parsed INSERT statement stored in the INSERT rule. (I didn't know that was even there.) This is because the COPY statement could only insert into some columns of the view. It just seems easier to me to write an INSERT statement and parse it than to take stuff out of an already existing raw parse tree. Am I wrong? Thanks for the help. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Hacking on PostgreSQL via GIT
Jim C. Nasby wrote: Then how do you tell what version a file is if it's outside of a checkout? It's trivial for git to answer that - the file will either be pristine, and then we can just scan for the matching SHA1, or modified, and we can scan (taking a weee bit more time) which are the closest matches in your history, in what branches and commits. The actual scripting for this isn't written just yet -- Linus posted a proof-of-concept shell implementation along the lines of git rev-list --no-merges --full-history v0.5..v0.7 -- src/widget/widget.c rev-list best_commit=none best=100 while read commit do git cat-file blob $commit:src/widget/widget.c tmpfile lines=$(diff reference-file tmpfile | wc -l) if [ $lines -lt $best ] then echo Best so far: $commit $lines best=$lines fi done rev-list and it's fast. One of the good properties of this is that you can ask for a range of your history (v0.5 to v0.7 in the example) and an exact path (src/widget/widget.c) but you can also say --all (meaning in all branches) and a handwavy over there, like src. And git will take an extra second or two on a large repo, but tell you about all the good candidates across the branches. Metadata is metadata, and we can fish it out of the SCM easily - and data is data, and it's silly to pollute it with metadata that is mostly incidental. If I find time today I'll post to the git list a cleaned up version of Linus' shell script as git-findclosestmatch head or range or --all path/to/scan/ \ randomfile.c cheers, m -- --- Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St OFFICE: +64(4)916-7224 UK: 0845 868 5733 ext 7224 MOB: +64(21)364-017 Make things as simple as possible, but no simpler - Einstein --- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Hacking on PostgreSQL via GIT
Jim C. Nasby wrote: Not bad... took you 40 lines to answer my question. Let's see if I can beat that... Sure - it'll be 1 line when it's wrapped in a shell script. And then we'll be even. I understand the argument about metadata and all, and largely agree with it. But on the other hand I think a version identifier is a critical piece of information; it's just as critical as the file name when it comes to identifying the information contained in the file. Surely. It is important, but it's metadata and belongs elsewhere. That metadata _is_ important doesn't mean you corrupt _data_ with it. Just imagine that MySQL users were used to getting their SQL engine expand $Oid$ $Tablename$ $PrimayKey$ in TEXT fields. And that when INSERT/UPDATEing those were collapsed. And in comparisons too. Wouldn't you say that's metadata, can be queried in a thousand ways, does not belong in the middle of the data? And the _really_ interesting version identifier is usually the commit identifier, which gives you a SHA1 of the whole src directory and the history. Projects that use git usually include that SHA1 in their build script, so even if a user compiles off a daily snapshot or a checkout on a random branch of your SCM, you can just ask them what's the build identifier? and they'll give you a SHA1. Actually, git can spit a nicer build identifier that includes the latest tag, so if you see the identifier being v8.2.sha1 You know it's not 8.2 release but a commit soon after it, identified by that SHA1. GIT uses that during its build to insert the version identifier, so: $ git --version git version 1.5.1.gf8ce With that in your hand, you can say # show me what commits on top of the tagged 1.5.1 have I got: $ git log 1.5.1..gf8ce # file src/lib/foo.c at this exact commit git show gf8ce:src/lib/foo.c So if you use this identifier (just call `git version`) to - name your tarballs - create a build-id file at tarball creation time - tag your builds with a version id And then when you have code out there in the wild, and people report bugs or send you patches, there's a good identifier you can ask for that covers _all_ the files. If it happens that someone reports a bug and says they have 8.2.gg998 and you don't seem to have any gg998 commit after 8.2, you can say with confidence: you are running some a patched Pg - please repro with a pristine copy (or show us your code!) :-) cheers, m -- --- Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St OFFICE: +64(4)916-7224 UK: 0845 868 5733 ext 7224 MOB: +64(21)364-017 Make things as simple as possible, but no simpler - Einstein --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Allowing COPY into views
Gregory Stark wrote: Karl O. Pinc [EMAIL PROTECTED] writes: Rather than spend a whole lot of time figuring out all the calls to use and all the detail, I'm going to go ahead and post now. That way if this is simply not going to fly I don't have to spend any more time on it. Otherwise, I'll post more as I work it out. It probably doesn't make sense to work on just this one case. There are folks looking at update-able views. If we had that then this would simplify to simply recognizing the case and invoking the rewriter to get you the insert statement to use. The threads to updatable views have concluded rejecting the patches, and with ideas that require rather extensive rewriting of the rule manager, so I'd think that handling this one case (COPY view FROM foo) would be useful on its own, and would carry a lot less work than the whole thing. Updatable views are not going to handle COPY anyway ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [RFC] PostgreSQL Access Control Extension (PGACE)
Tom Lane wrote: KaiGai Kohei [EMAIL PROTECTED] writes: There are also some interesting questions about SQL spec compliance and whether a database that silently hides some rows from you will give semantically consistent results. Yeah -- that's a potentially serious issue; KaiGai, have you looked into it? Yes, I consider the policy to filter any violated tuple looks consistently. The policy enforces any tuple has to be filtered before using them, and it helps that computational processes don't get any effect from them. But proving innocence is generally hard task. At first, I want to know what points are you worried about the most. Unique constraints and foreign-key constraints seem the most pressing problems. What will you do to avoid having different viewers have different opinions about whether a constraint is violated? The behavior of unique constraints are kept as is. Thus, a client with some hidden tuples may not be able to insert a new tuple, though the tuple seems to him containing unique values. From strict security viewpoint, this behavior has a possibility to leak the existence of hidden tuples to clients without enough permissions. To resolve them, polyinstantiation table support will be required ultimately. When a client tries to insert a new tuple into a table in which foreign-key constraints are configured, the foreign-key values have to be included in his scope. If not so, the current transaction will be aborted. If the constraint has CASCADE rule, all the foreign-keys have to be updated when the value of primary key is changed. It is an exception for the policy to filter. If the client have any violated tuple, whole the process will be aborted. In normal cases, those tuples are merely excluded from the target of updating, although. As the conclusion, we intend to keep the consistency of any constrains. But some issues are remained from strict security viewpoint. Thanks, -- KaiGai Kohei [EMAIL PROTECTED] ---(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] Allowing COPY into views
Karl O. Pinc wrote: On 04/19/2007 09:39:18 AM, Alvaro Herrera wrote: I'm not sure the plan is OK as stated. You wouldn't want to force to parse the query again for each row. Rather, create a prepared statement (already parsed, because you obtain it from the parsetree stored in the INSERT rule) to pass to the executor. I wasn't going to parse and plan an INSERT for each row of data to COPY. Step 7 is the step that iterates over the rows of COPY data. I make my own PREPAREd insert and execute it in step 7 for each row of COPY data. It seems to me it'd be eaiser to write my own INSERT statement and call raw_parser on it than to try to modify the parsed INSERT statement stored in the INSERT rule. (I didn't know that was even there.) Based on past records, I would guess that that approach would be rejected or at least frowned upon. It happened to the COPY (query) TO patch. This is because the COPY statement could only insert into some columns of the view. Then you would fill the missing values with DEFAULT or NULL expressions. It just seems easier to me to write an INSERT statement and parse it than to take stuff out of an already existing raw parse tree. Am I wrong? Well, if it's easier for you to do it, go ahead; but if you want the patch accepted that's a whole'nother matter. I wrote several easy patches that were rejected. Maybe the easy patch serves as proof of concept to make sure it all works, which you then rework to do things properly. We don't care _how_ you arrived at a patch, if it's good. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Compiler warnings
I am seeing the following compiler warnings in CVS HEAD: analyze.c:471: warning: `diff' might be used uninitialized in this function vacuumlazy.c:220: warning: `diff' might be used uninitialized in this function -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Compiler warnings
Bruce Momjian wrote: I am seeing the following compiler warnings in CVS HEAD: analyze.c:471: warning: `diff' might be used uninitialized in this function vacuumlazy.c:220: warning: `diff' might be used uninitialized in this function Are you using too old a compiler? Mine does not complain gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) I'll fix them anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Compiler warnings
Alvaro Herrera wrote: Bruce Momjian wrote: I am seeing the following compiler warnings in CVS HEAD: analyze.c:471: warning: `diff' might be used uninitialized in this function vacuumlazy.c:220: warning: `diff' might be used uninitialized in this function Are you using too old a compiler? Mine does not complain gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) Probably: gcc version 2.95.3 20010315 (release) -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Compiler warnings
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: I am seeing the following compiler warnings in CVS HEAD: analyze.c:471: warning: `diff' might be used uninitialized in this function vacuumlazy.c:220: warning: `diff' might be used uninitialized in this function Are you using too old a compiler? Mine does not complain gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) Probably: gcc version 2.95.3 20010315 (release) The only thing older than that, I think, is Manfred Koizar's bike. http://archives.postgresql.org/pgsql-patches/2004-05/msg00326.php -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] unclean SPI_scroll_cursor_move documentation, is SPI_tuptable valid?
Hello, in doc there are for returned value: SPI_processed and SPI_tuptable are set as in SPI_execute if successful. But for move statement is SPI_tuptable undefined. Move statement only move cursor. Doesn't return anything. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pgsql crollable cursor doesn't support one form of postgresql's cursors
Hello, I found one unsupported form plpgsql's fetch statement which is supported by postgresql. PostgreSQL knows FETCH 3 FROM but plpgsql needs everytime direction's keyword. It's need small fix. I am sorry. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] [PATCH] A crash and subsequent recovery of the master can cause the slave to get out-of-sync
Hi
I believe I have discovered the following problem in pgsql 8.2 and HEAD,
concerning warm-standbys using WAL log shipping.
The problem is that after a crash, the master might complete incomplete
actions via rm_cleanup() - but since it won't wal-log those changes,
the slave won't know about this. This will at least prevent the creation
of any further restart points on the slave (because safe_restartpoint)
will never return true again - it it might even cause data corruption,
if subsequent wal records are interpreted wrongly by the slave because
it sees other data than the master did when it generated them.
Attached is a patch that lets RecoveryRestartPoint call all
rm_cleanup() methods and create a restart point whenever it encounters
a shutdown checkpoint in the wal (because those are generated after
recovery). This ought not cause a performance degradation, because
shutdown checkpoints will occur very infrequently.
The patch is per discussion with Simon Riggs.
I've not yet had a chance to test this patch, I only made sure
that it compiles. I'm sending this out now because I hope this
might make it into 8.2.4.
greetings, Florian Pflug
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 6c67821..93c86a1 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -5060,10 +5060,13 @@ #endif
* Perform a checkpoint to update all our recovery activity to disk.
*
* Note that we write a shutdown checkpoint rather than an on-line
- * one. This is not particularly critical, but since we may be
- * assigning a new TLI, using a shutdown checkpoint allows us to have
- * the rule that TLI only changes in shutdown checkpoints, which
- * allows some extra error checking in xlog_redo.
+ * one. A slave will always create a restart point if it sees a
+ * shutdown checkpoint, and will call all rm_cleanup() methods before
+ * it does so. This guarantees that any actions taken by the master
+ * in rm_cleanup will also be carried out on the slave.
+ * Additionally, we may be assigning a new TLI, so using a shutdow
+ * checkpoint allows us to have the rule that TLI only changes in shutdown
+ * checkpoints, which allows some extra error checking in xlog_redo.
*/
CreateCheckPoint(true, true);
@@ -5672,35 +5675,56 @@ CheckPointGuts(XLogRecPtr checkPointRedo
* restartpoint is needed or not.
*/
static void
-RecoveryRestartPoint(const CheckPoint *checkPoint)
+RecoveryRestartPoint(const CheckPoint *checkPoint, const bool shutdownCheckpoint)
{
int elapsed_secs;
int rmid;
/*
- * Do nothing if the elapsed time since the last restartpoint is less than
- * half of checkpoint_timeout. (We use a value less than
- * checkpoint_timeout so that variations in the timing of checkpoints on
- * the master, or speed of transmission of WAL segments to a slave, won't
- * make the slave skip a restartpoint once it's synced with the master.)
- * Checking true elapsed time keeps us from doing restartpoints too often
- * while rapidly scanning large amounts of WAL.
+ * If the checkpoint we saw in the wal was a shutdown checkpoint, it might
+ * have been written after the recovery following a crash of the master.
+ * In that case, the master will have completed any actions that were
+ * incomplete when it crashed *during recovery*, and these completions
+ * are therefor *not* logged in the wal.
+ * To prevent getting out of sync, we follow what the master did, and
+ * call the rm_cleanup() methods. To be on the safe side, we then create
+ * a RestartPoint, regardless of the time elapsed. Note that asking
+ * the resource managers if they have partial state would be redundant
+ * after calling rm_cleanup().
*/
- elapsed_secs = time(NULL) - ControlFile-time;
- if (elapsed_secs CheckPointTimeout / 2)
- return;
+ if (shutdownCheckpoint) {
+ for (rmid = 0; rmid = RM_MAX_ID; rmid++)
+ {
+ if (RmgrTable[rmid].rm_cleanup != NULL)
+RmgrTable[rmid].rm_cleanup();
+ }
+ }
+ else {
+ /*
+ * Do nothing if the elapsed time since the last restartpoint is less than
+ * half of checkpoint_timeout. (We use a value less than
+ * checkpoint_timeout so that variations in the timing of checkpoints on
+ * the master, or speed of transmission of WAL segments to a slave, won't
+ * make the slave skip a restartpoint once it's synced with the master.)
+ * Checking true elapsed time keeps us from doing restartpoints too often
+ * while rapidly scanning large amounts of WAL.
+ */
+ elapsed_secs = time(NULL) - ControlFile-time;
+ if (elapsed_secs CheckPointTimeout / 2)
+ return;
- /*
- * Is it safe to checkpoint? We must ask each of the resource managers
- * whether they have any partial state information that might prevent a
- * correct restart from this point. If so, we skip this opportunity, but
- * return at the next checkpoint record for another try.
- */
- for (rmid = 0; rmid =
Re: [HACKERS] parser dilemma
On Thu, Apr 19, 2007 at 11:19:40AM +0200, Zoltan Boszormenyi wrote: The problem comes from cases like colname coltype DEFAULT 5! GENERATED ... Since b_expr allows postfix operators, it takes one more token of lookahead than we have to tell if the default expression is 5! or 5!GENERATED ISTM that as long as: colname coltype DEFAULT (5!) GENERATED ... works I don't see why it would be a problem to require the parentheses in this case. Postfis operators are not going to be that common here I think. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] Fragmentation project
Hello people, I'm thinking in project and implement data fragmentation based on catalogs inside pgcluster as my university final project. I would like suggestions and would be happy if anyone help me to define it. Sorry if this is the incorrect list...I had no answer on pgcluster-general... Thanks, Gustavo. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Fixing insecure security definer functions
Stephen Frost wrote: While I agree that raising a warning makes sense I don't believe it should be forced. There may be cases where, even in security definer functions, the current search_path should be used (though, of course, care must be taken in writing such functions). I really wonder whether such a use case exists. What would it be? -- 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] Allowing COPY into views
Alvaro Herrera [EMAIL PROTECTED] writes: The threads to updatable views have concluded rejecting the patches, and with ideas that require rather extensive rewriting of the rule manager, I have some ideas on a different approach to this if anyone's thinking of starting fresh but I had the impression that the patches were rejected because they were unnecessarily complex, not because the overall approach was rejected. Updatable views are not going to handle COPY anyway ... Well I noticed this as I was writing it. Even once you have updateable views you would still have to have code in COPY supporting creating insert statements which isn't how it works now. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Allowing COPY into views
Gregory Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: The threads to updatable views have concluded rejecting the patches, and with ideas that require rather extensive rewriting of the rule manager, I have some ideas on a different approach to this if anyone's thinking of starting fresh What ideas? but I had the impression that the patches were rejected because they were unnecessarily complex, not because the overall approach was rejected. Well, there was the unneeded complexity as one argument, but the with check option stuff needed a big rework as well. http://groups.google.es/group/pgsql.hackers/browse_thread/thread/ed69543ea417a2f/d0b628881ead1e05 -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
On Thursday 05 April 2007 16:28, Larry Rosenman wrote: I might use that as the base then, since the hardware finishes getting here tomorrow. My question still stands on what OS's we need coverage for. One I see as missing right now is Solaris 10 X86 with gcc building 64bit binaries (we have more than one member building with SunStudio 11) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: [EMAIL PROTECTED] US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joshua D. Drake Sent: Thursday, April 05, 2007 6:18 PM To: Larry Rosenman Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] What X86/X64 OS's do we need coverage for? It will have CentOS 4.4 X86_64 as the base os with VMWare Server running on it. I am willing to run any X86 or X64 OS's in VM's as buildfarm clients. What OS's do we need coverage for? CentOS5 hits ina couple days. J LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: [EMAIL PROTECTED] US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Allowing COPY into views
Alvaro Herrera [EMAIL PROTECTED] writes: What ideas? Basically I think what we should do is extend SQL to support things like UPDATE (subquery) SET ... DELETE FROM (subquery) WHERE ... Ie, do the update-able view magic in the planner and executor rather than in the rewriter. Then the rewriter just has the same work to do that it does for SELECTs, namely substitute the view in the right place in the query. We don't have the rewriter try to reverse-engineer SELECTS and flatten the view into the outer query, it shouldn't be trying to do that for UPDATEs and DELETEs either. Oracle does actually support syntax like this, even for joins, and it's awfully convenient for UPDATE where it handles the same kind of cases we use our UPDATE ... FROM syntax. -- Gregory Stark 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] Fragmentation project
Gustavo Tonini wrote: I'm thinking in project and implement data fragmentation based on catalogs inside pgcluster as my university final project. I would like suggestions and would be happy if anyone help me to define it. Sorry if this is the incorrect list...I had no answer on pgcluster-general... This is the correct list. I'm not sure what you mean by data fragmentation, but we do support horizontal partitioning by table constraints. And toasting is like vertical partitioning. What exactly are you thinking of implementing? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] pgsql crollable cursor doesn't support one form of postgresql's cursors
Pavel Stehule [EMAIL PROTECTED] writes: I found one unsupported form plpgsql's fetch statement which is supported by postgresql. PostgreSQL knows FETCH 3 FROM but plpgsql needs everytime direction's keyword. No, I think that's OK, because that form specifies fetching 3 rows, which plpgsql's FETCH doesn't support. 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] pgsql crollable cursor doesn't support one form of postgresql's cu
Pavel Stehule [EMAIL PROTECTED] writes:
I found one unsupported form plpgsql's fetch statement which is
supported
by postgresql.
PostgreSQL knows
FETCH 3 FROM
but plpgsql needs everytime direction's keyword.
No, I think that's OK, because that form specifies fetching 3 rows,
which plpgsql's FETCH doesn't support.
it's true. There is same question for move statement too. Other difference
is unsupported keyword IN.
It can be fixed:
*** ./gram.y.orig 2007-04-19 20:27:17.0 +0200
--- ./gram.y2007-04-19 20:41:16.0 +0200
***
*** 2059,2071
else if (pg_strcasecmp(yytext, absolute) == 0)
{
fetch-direction = FETCH_ABSOLUTE;
! fetch-expr = plpgsql_read_expression(K_FROM, FROM);
check_FROM = false;
}
else if (pg_strcasecmp(yytext, relative) == 0)
{
fetch-direction = FETCH_RELATIVE;
! fetch-expr = plpgsql_read_expression(K_FROM, FROM);
check_FROM = false;
}
else if (pg_strcasecmp(yytext, forward) == 0)
--- 2059,2071
else if (pg_strcasecmp(yytext, absolute) == 0)
{
fetch-direction = FETCH_ABSOLUTE;
! fetch-expr = read_sql_construct(K_FROM, K_IN, FROM/IN, SELECT ,
true, true, NULL);
check_FROM = false;
}
else if (pg_strcasecmp(yytext, relative) == 0)
{
fetch-direction = FETCH_RELATIVE;
! fetch-expr = read_sql_construct(K_FROM, K_IN, FROM/IN, SELECT ,
true, true, NULL);
check_FROM = false;
}
else if (pg_strcasecmp(yytext, forward) == 0)
***
*** 2076,2081
--- 2076,2087
{
fetch-direction = FETCH_BACKWARD;
}
+ else if (tok != T_SCALAR)
+ {
+ plpgsql_push_back_token(tok);
+ fetch-expr = read_sql_construct(K_FROM, K_IN, FROM/IN, SELECT ,
true, true, NULL);
+ check_FROM = false;
+ }
else
{
/* Assume there's no direction clause */
***
*** 2083,2091
check_FROM = false;
}
! /* check FROM keyword after direction's specification */
! if (check_FROM yylex() != K_FROM)
! yyerror(expected \FROM\);
return fetch;
}
--- 2089,2097
check_FROM = false;
}
! /* check FROM or IN keyword after direction's specification */
! if (check_FROM (yylex() != K_FROM yylex() != K_IN))
! yyerror(expected \FROM/IN\);
return fetch;
}
Regards
Pavel Stehule
_
Najdete si svou lasku a nove pratele na Match.com. http://www.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
