Re: [HACKERS] Nested Transactions, Abort All
On Tue, 6 Jul 2004, Alvaro Herrera wrote: > We can later implement savepoints, which will have "SAVEPOINT foo" and > "ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly > different from a savepoint, so we can't use ROLLBACK TO in > subtransactions because that has a different meaning in savepoints). What is the semantic difference? In my eye the subtransactions and the savepoints are basically the same thing except the label that is used. If that is the only difference? why are we implementing our own extension for subtransactions instead of implementing this standard feature. Of course the label stuff is a little more complicated, but all the really hard parts should be the same as what have already been done. The most naive implementation of the labels is to have a mapping from a label to the number of subcommit (for RELEASE SAVEPOINT) or subrolllbacks (for ROLLBACK TO SAVEPOINT) to execute. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > Why not rollback all or commit all? > > I really really don't like subbegin and subcommit. I get the feeling > they'll cause more problems we haven't foreseen yet, but I can't put my > finger on it. Well I've already pointed out one problem. It makes it impossible to write generic code or reuse existing code and embed it within a transaction. Code meant to be a nested transaction within a larger transaction becomes non-interchangeable with code meant to be run on its own. I also have a different issue. The behaviour I'm expecting with most drivers will be to start a transaction immediately, and run every query within a subtransaction. This is what we've discussed previously with psql, but for the same reasons previously discussed I expect drivers to adopt the same approach, at least when not in autocommit mode. The goal would be to allow the application to control what happens when a given query returns an error and not force the application to roll the entire transaction back. This means the user can't use "BEGIN" or "END" at all himself. Since the driver would already have initiated a transaction itself. The *only* user-visible commands would become these awkward (and frankly, silly-sounding) "SUBBEGIN" and "SUBEND". I have an related question though. Will there be a out of band protocol method for controlling transaction status? If the v3 protocol allows the transaction status to be manipulated in binary messages that don't interact with user queries then a driver would still be able to reliably start and end transactions and nested transactions. If that were the case I guess I wouldn't care since a driver could then implement an external API that hid the irregularity of SUBfoo from the user and provided a consistent ->begin() ->end(). The driver could emulate this by inserting SUBfoo commands into the stream but then it would risk being subverted by user commands. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
On Tue, 2004-07-06 at 10:25, Alvaro Herrera wrote: > On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote: > > On Mon, 5 Jul 2004, Alvaro Herrera wrote: > > > > > > begin/end because they are already in an explicit/implicit transaction > > > > by default... How is the user/programmer to know when this is the case? > > > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > > you can't do is issue begin/end inside a function -- you always use > > > subbegin/subcommit in that case. > > > > I've not understood why we need new tokens for this case. Maybe you've > > explained it somewhere that I've missed. But surely the server know if you > > are in a transaction or not, and can differentiate on the first BEGIN and > > the next BEGIN. > > I think the best argument for this is that we need a command to abort > the whole transaction tree, and another to commit the whole transaction > tree. Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END), > because the spec says they work like that and it would be hell for an > interface like JDBC if they didn't. So it's out of the picture to use > those commands to end a subtransaction. Why not rollback all or commit all? I really really don't like subbegin and subcommit. I get the feeling they'll cause more problems we haven't foreseen yet, but I can't put my finger on it. They just don't feel like "postgresql" to me. I'd rather see extra syntax to handle exceptions, like rollback all or whatnot, than subbegin et. al. > > Now, it's clear we need new commands to end a subtransaction. Do we > also want a different command for begin? I think so, just to be > consistent. Sorry, but I respectfully disagree that it's clear. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] bug in DROP TABLESPACE
Tom already mentioned this just after committing tablespaces: 'Minor DROP TABLESPACE issue' http://www.mail-archive.com/[EMAIL PROTECTED]/msg46540.html In fact, I see that you contributed to the thread :-). I think the result of the thread was to make the error message a little more helpful and that adding empty files to represent schemas would be a pain (think WAL and name collision). Ah, I must have been in a dream state. The other thing we need are these two commands: ALTER DATABASE foo SET TABLESPACE spc; ALTER SCHEMA foo SET TABLESPACE spc; I think these should not be considered new features but essential functionality left out of the original patch. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [Re] Re: [HACKERS] PREPARE and transactions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Other exceptions I can think of are FETCH and DEALLOCATE. DEALLOCATE is > particularly fun -- don't most of the arguments for making PREPARE > transactional also apply to DEALLOCATE? Is it actually feasible to roll > back a DEALLOCATE? That's why PREPARE and DEALLOCATE are so perfect the way they are: outside of transactions. Although I think Tom mentioned that deallocating a statement that did not exist should raise a notice and not change the transaction status, which I would totally agree with. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200407062132 -BEGIN PGP SIGNATURE- iD8DBQFA61K/vJuQZxSWSsgRAi6oAKDruPbDxfk2uDydOAPoFLjJxyeaHACfaT3V LncDJ2/eFy8RMNLbmcG2Iwo= =Tin2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Postgresql on SAN
Simon Riggs <[EMAIL PROTECTED]> writes: > On Tue, 2004-07-06 at 18:22, Yannick Lecaillez wrote: > > > I would have the pgsql-hackers genius for do that :) . I think its the > > only feature which force company to buy 5$ Oracle licence ... Fwiw, I think you've underestimated the price on those Oracle licenses by an order of magnitude at least. If there are as many companies willing to pony up for some postgres developers I'm sure there would be people interested, but it's not the kind of project someone's going to be doing in their spare time. As Oracle found, it's *hard*. And moreover, it results in a system that's hard to use. Those companies that need are also ponying up much more than $50k/year just for the DBAs capable of running such beasts. Free Software runs on a very different operating model than commercial software. Instead of a sharp division between paying clients and profiting developers, most Free Software exists because the programmers themselves found they had a need and solved it for themselves. For that reason I would be skeptical about seeing huge clustered postgres systems a la Oracle OPS, simply because it's a very specialized need, and not one that any postgres developer is likely to run into on his own. They're more likely to run screaming when asked to provide such a monster than sit down and start coding... What most people need is some way to promise rapid recovery from failures. In my personal opinion the smoothest most reliable method of providing that is a PITR-based warm standby machine. I'm overjoyed that someone else saw the same need and has been working feverishly on that for 7.5. There does seem to be an awful lot of people on this list lobbying for some feature or another. It always seems a bit weird, like a basic misunderstanding is at play. The developers are working for their employers or for themselves. It doesn't really matter how many new users the Windows port will bring on, for example. This isn't some proselytising religion. It'll get done if a developer needs it either for him- or herself or for a client, not because you made some convincing argument about how there are lots of other people who would benefit. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Loadable Oracle Personality: WAS "LinuxTag wrapup" thread
Simon Riggs wrote: External tool is one thing, but the loadable personality seems like a very good idea and worth discussing further. Would an interesting, and maybe slightly different way of viewing a "loadable personality," be as a set of "rules" that can be applied to parser input before the parser actually gets it... and massages input SQL into something for the parser to understand. I'm hugely generalising here of course, but you know how we have a PostgreSQL "Rules" system that rewrites queries before handing them to the query planner... well, would it be possible/practical to potentially have a "Rules" system that rewrites incoming SQL before it gets given to the normal parser. Might get complicated though... we'd need a pre-parser or something. However, having a generalised system for doing this may make it far easier to provide "personalities". i.e. load a set of Oracle 8i rules, load a set of Oracle 9i rules, load a set of DB2 x, rules, etc. :) Regards and best wishes, Justin Clift ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] LinuxTag wrapup
> > > Andreas Pflug <[EMAIL PROTECTED]> writes: > > > > - what about Oracle portability. > > > > > > > IMHO we should rethink if we could make those people happy. How about a > > > > loadable personality (IIRC SAPDB has something like that), to exchange > > > > the parser in use with a custom one (by a SET command)? This way we have > > > > a pure ansi default, while enabling a way so someone could contribute an > > > > oracle style parser. > > > > > > How about an external tool that helps in translating apps to > > > SQL-standard syntax? Oracle does accept the standard syntax after all. > > > That way we are truly helping people liberate themselves: they can > > > switch to any SQL-compliant database, not only Postgres. > > > > I totally agree. After all, oracle provides such tools to their customers. External tool is one thing, but the loadable personality seems like a very good idea and worth discussing further. For ANSI standard, you need a checker that will reject non-ANSI right? How do you handle the same thing for Oracle and others. It would be very difficult to go through the parser and annotate everything as IsOracle or IsANSI etc.. IMHO the loadable personality would allow considerable further compatibility, but without effecting core behaviours. As we've seen, many of these products behave in exactly opposite ways, so we need a way that can cater for them all. Porting is such a pain...there has to be a better way. Best regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Postgresql on SAN
On Tue, 2004-07-06 at 18:22, Yannick Lecaillez wrote: > Le mar 06/07/2004 à 19:07, Alvaro Herrera a écrit : > > On Tue, Jul 06, 2004 at 06:17:16PM +0200, Yannick Lecaillez wrote: > > > > > What need to do (understand, to devel) to allow several > > > postgres instance running from several server to access to the > > > same data (no replication at all) hosted on a SAN ? > > > > Clustered shared memory, cluster-wide spinlocks. And with decent > > performance, while at it ... > Perhaps could be interesting to look at cluster file system which > seems to have same problems and find solution about locking (i.e > OpenGFS). > http://opengfs.sourceforge.net/showdoc.php?docpath=cvsmirror/opengfs/docs/ogfs-locking&doctitle=Locking&docauthor=ben.m.cahill(at)intel.com > > Found on google a clustered shared memory (openMosix project) > http://www.unixreview.com/documents/s=8989/ur0404l/ > > I would have the pgsql-hackers genius for do that :) . I think its the > only feature which force company to buy 5$ Oracle licence ... > I would note that Oracle first released OPS on UNIX at 7.0.13, in 1993. Major performance issues were not resolved until 9i emerged, almost 10 years later... It won't take PostgreSQL 10 years, but its impossible now, as Peter observes. Best regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Point in Time Recovery
On Tue, 2004-07-06 at 20:00, Richard Huxton wrote: > Simon Riggs wrote: > > On Mon, 2004-07-05 at 22:46, Tom Lane wrote: > > > >>Simon Riggs <[EMAIL PROTECTED]> writes: > >> > >>>Should we use a different datatype than time_t for the commit timestamp, > >>>one that offers more fine grained differentiation between checkpoints? > >> > >>Pretty much everybody supports gettimeofday() (time_t and separate > >>integer microseconds); you might as well use that. Note that the actual > >>resolution is not necessarily microseconds, and it'd still not be > >>certain that successive commits have distinct timestamps --- so maybe > >>this refinement would be pointless. You'll still have to design a user > >>interface that allows selection without the assumption of distinct > >>timestamps. > > > > > > Well, I agree, though without the desired-for UI now, I think some finer > > grained mechanism would be good. This means extending the xlog commit > > record by a couple of bytes...OK, lets live a little. > > At the risk of irritating people, I'll repeat what I suggested a few > weeks ago... > All feedback is good. Thanks. > Add a table: pg_pitr_checkpt (pitr_id SERIAL, pitr_ts timestamptz, > pitr_comment text) > Let the user insert rows in transactions as desired. Let them stop the > restore when a specific (pitr_ts,pitr_comment) gets inserted (or on > pitr_id if they record it). > It's a good plan, but the recovery is currently offline recovery and no SQL is possible. So no way to insert, no way to access tables until recovery completes. I like that plan and probably would have used it if it was viable. > IMHO time is seldom relevant, event boundaries are. > Agreed, but time is the universally agreed way of describing two events as being simultaneous. No other way to say "recover to the point when the message queue went wild". As of last post to Andreas, I've said I'll not bother changing the granularity of the timestamp. > If you want to add special syntax for this, fine. If not, an INSERT > statement is a convenient way to do this anyway. The special syntax isn't hugely important - I did suggest a kind of SQL-like syntax previously, but thats gone now. Invoking recovery via a command file IS, so we are able to tell the system its not in crash recovery AND that when you've finished I want you to respond to crashes without re-entering archive recovery. Thanks for your comments. I'm not making this more complex than needs be; in fact much of the code is very simple - its just the planning that's complex. Best regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Point in Time Recovery
On Mon, 2004-07-05 at 22:46, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > - when we stop, keep reading records until EOF, just don't apply them. > > When we write a checkpoint at end of recovery, the unapplied > > transactions are buried alive, never to return. > > - stop where we stop, then force zeros to EOF, so that no possible > > record remains of previous transactions. > > Go with plan B; it's best not to destroy data (what if you chose the > wrong restart point the first time)? > > Actually this now reminds me of a discussion I had with Patrick > Macdonald some time ago. The DB2 practice in this connection is that > you *never* overwrite existing logfile data when recovering. Instead > you start a brand new xlog segment file, which is given a new "branch > number" so it can be distinguished from the future-time xlog segments > that you chose not to apply. I don't recall what the DB2 terminology > was exactly --- not "branch number" I don't think --- but anyway the > idea is that when you restart the database after an incomplete recovery, > you are now in a sort of parallel universe that has its own history > after the branch point (PITR stop point). You need to be able to > distinguish archived log segments of this parallel universe from those > of previous and subsequent incarnations. I'm not sure whether Vadim > intended our StartUpID to serve this purpose, but it could perhaps be > used that way, if we reflected it in the WAL file names. > Some more thoughts...focusing on the what do we do after we've finished recovering. The objectives, as I see them, are to put the system into a state, that preserves these features: 1. we never overwrite files, in case we want to re-run recovery 2. we never write files that MIGHT have been written previously 3. we need to ensure that any xlog records skipped at admins request (in PITR mode) are never in a position to be re-applied to this timeline. 4. ensure we can re-recover, if we need to, without further problems Tom's concept above, I'm going to call timelines. A timeline is the sequence of logs created by the execution of a server. If you recover the database, you create a new timeline. [This is because, if you've invoked PITR you absolutely definitely want log records written to, say, xlog15 to be different to those that were written to xlog15 in a previous timeline that you have chosen not to reapply.] Objective (1) is complex. When we are restoring, we always start with archived copies of the xlog, to make sure we don't finish too soon. We roll forward until we either reach PITR stop point, or we hit end of archived logs. If we hit end of logs on archive, then we switch to a local copy, if one exists that is higher than those, we carry on rolling forward until either we reach PITR stop point, or we hit end of that log. (Hopefully, there isn't more than one local xlog higher than the archive, but its possible). If we are rolling forward on local copies, then they are our only copies. We'd really like to archive them ASAP, but the archiver's not running yet - we don't want to force that situation in case the archive device (say a tape) is the one being used to recover right now. So we write an archive_status of .ready for that file, ensuring that the checkpoint won't remove it until it gets copied to archive, whenever that starts working again. Objective (1) met. When we have finished recovering we: - create a new xlog at the start of a new ++timeline - copy the last applied xlog record to it as the first record - set the record pointer so that it matches That way, when we come up and begin running, we never overwrite files that might have been written previously. Objective (2) met. We do the other stuff because recovery finishes up by pointing to the last applied record...which is what was causing all of this extra work in the first place. At this point, we also reset the secondary checkpoint record, so that should recovery be required again before next checkpoint AND the shutdown checkpoint record written after recovery completes is wrong/damaged, the recovery will not autorewind back past the PITR stop point and attempt to recover the records we have just tried so hard to reverse/ignore. Objective (3) met. (Clearly, that situation seems unlikely, but I feel we must deal with it...a newly restored system is actually very fragile, so a crash again within 3 minutes or so is very commonplace, as far as these things go). Should we need to re-recover, we can do so because the new timeline xlogs are further forward than the old timeline, so never get seen by any processes (all of which look backwards). Re-recovery is possible without problems, if required. This means you're a lot safer from some of the mistakes you might of made, such as deciding you need to go into recovery, then realising it wasn't required (or some other painful flapping as goes on in computer rooms at 3am). How do we implement timelines? The main presumpt
Re: [HACKERS] LinuxTag wrapup
Marc G. Fournier wrote: On Tue, 6 Jul 2004, Andreas Pflug wrote: An external tool helping translating sql is fine, but nothing to be defined todo for core pgsql IMHO. I still believe some minor "oracle helper" behaviour (not to call it oracle compatibility, to avoid wrong expectations) should be added. Currently, pgsql appears a bit arrogant towards those oracle centric people (always a matter of point of view, of course). We could avoid this by offering some concessions. Actually, we had added awhile back a set of 'Oracle compability' stuff to the backend, to handle some of the non-standard functions that Oracle users had access to ... is there a reason why that can't be extended? Or are we talking about *really* core changes here? I don't think so. I'd like to say "we're support oracle style syntax as far as it's reasonable in the context of pgsql, and we're supplying best practice advice for some more stuff". Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Initdb error
Hi! I'm getting this error whenever I try to change the superuser of my database to anything != postgres. The same error shows up if I do "-U " on Unix and "someuser" does not exist in /etc/passwd. Probably the same reason - since win32 will not have any users in /etc/passwd.. It works on 7.4, so probably related to the initdb-in-C rewrite? //Magnus d:\msys\1.0\local\pgsql\bin>initdb -D d:\pgtemp -U pgsuper The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale Swedish_Sweden.1252. creating directory d:/pgtemp ... ok creating directory d:/pgtemp/global ... ok creating directory d:/pgtemp/pg_xlog ... ok creating directory d:/pgtemp/pg_clog ... ok creating directory d:/pgtemp/pg_subtrans ... ok creating directory d:/pgtemp/base ... ok creating directory d:/pgtemp/base/1 ... ok creating directory d:/pgtemp/pg_tblspc ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 1000 creating configuration files ... ok creating template1 database in d:/pgtemp/base/1 ... ok initializing pg_shadow ... ok enabling unlimited row size for system tables ... ok initializing pg_depend ... ok creating system views ... ok loading pg_description ... ok creating conversions ... ok setting privileges on built-in objects ... ERROR: user "pgsuper" does not exist child process was terminated by signal 1 initdb: failed initdb: removing data directory "d:/pgtemp" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] LinuxTag wrapup
On Tue, 6 Jul 2004, Andreas Pflug wrote: An external tool helping translating sql is fine, but nothing to be defined todo for core pgsql IMHO. I still believe some minor "oracle helper" behaviour (not to call it oracle compatibility, to avoid wrong expectations) should be added. Currently, pgsql appears a bit arrogant towards those oracle centric people (always a matter of point of view, of course). We could avoid this by offering some concessions. Actually, we had added awhile back a set of 'Oracle compability' stuff to the backend, to handle some of the non-standard functions that Oracle users had access to ... is there a reason why that can't be extended? Or are we talking about *really* core changes here? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Point in Time Recovery
On Tue, 2004-07-06 at 08:38, Zeugswetter Andreas SB SD wrote: > > - by time - but the time stamp on each xlog record only specifies to the > > second, which could easily be 10 or more commits (we hope) > > > > Should we use a different datatype than time_t for the commit timestamp, > > one that offers more fine grained differentiation between checkpoints? > > Imho seconds is really sufficient. If you know a more precise position > you will probably know it from backend log or an xlog sniffer. With those > you can easily use the TransactionId way. > OK, thanks. I'll just leave the time_t datatype just the way it is. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Error Codes
On Tue, Jul 06, 2004 at 01:22:35PM -0400, Bruce Momjian wrote:
> David Fetter wrote:
> > Kind people,
> >
> > So far, I have found two places where one can find the SQLSTATE
> > error codes: a header file, and the errcodes-appendix doc. Those
> > are excellent places.
> >
> > Did I miss how to get a list of them in SQL? If I missed it
> > because it isn't there, what would be a good way to have a current
> > list available?
>
> You know, it would be cool to have the codes and descriptions in a
> global SQL table.
I think so, too :)
So, I'm looking at src/include/utils/errcodes.h in CVS tip, and I see
what looks to me like two columns in a table:
sqlstate (e.g. 0100C)
warning (e.g. ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED)
this would make an excellent table to have handy. How to make sure
that it is, in fact, available, and that its contents match
errcodes.h? Here is a perl hack for parsing errcodes.h:
#!/usr/bin/perl -wl
use strict;
open F, ") {
chomp;
next unless (/^#define\s+ERRCODE_(\S+)\s+MAKE_SQLSTATE\('(.*)'\).*$/);
# print;
my ($warning, $sqlstate) = ($1, $2);
$warning =~ s/^ERRCODE_//;
$sqlstate =~ s/\W//g; # clean up
my $sql = "INSERT INTO sqlstates (sqlstate, warning) VALUES ($sqlstate,
$warning);";
print $sql;
# Now, do the inserts...but where?
}
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Point in Time Recovery
Simon Riggs wrote: On Mon, 2004-07-05 at 22:46, Tom Lane wrote: Simon Riggs <[EMAIL PROTECTED]> writes: Should we use a different datatype than time_t for the commit timestamp, one that offers more fine grained differentiation between checkpoints? Pretty much everybody supports gettimeofday() (time_t and separate integer microseconds); you might as well use that. Note that the actual resolution is not necessarily microseconds, and it'd still not be certain that successive commits have distinct timestamps --- so maybe this refinement would be pointless. You'll still have to design a user interface that allows selection without the assumption of distinct timestamps. Well, I agree, though without the desired-for UI now, I think some finer grained mechanism would be good. This means extending the xlog commit record by a couple of bytes...OK, lets live a little. At the risk of irritating people, I'll repeat what I suggested a few weeks ago... Add a table: pg_pitr_checkpt (pitr_id SERIAL, pitr_ts timestamptz, pitr_comment text) Let the user insert rows in transactions as desired. Let them stop the restore when a specific (pitr_ts,pitr_comment) gets inserted (or on pitr_id if they record it). IMHO time is seldom relevant, event boundaries are. If you want to add special syntax for this, fine. If not, an INSERT statement is a convenient way to do this anyway. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] dbt2-pgsql on OSDL
Hi Manfred, Oopsies, fixed that. I've removed the -r flag. Thanks for catching that. Mark On 6 Jul, Manfred Koizar wrote: > Mark, > > I've tried to run some performance tests on your Scalable Test Platform > but the tests failed at the build step. > > I guess the problem is near line 282 of > http://khack.osdl.org/stp/294734/logs/run-log.txt > > | + wget -nv -t 0 --waitretry=60 -r http://stp/data/dbt-2/postgresql-7.4.tar.gz > | 09:45:01 URL:http://stp/data/dbt-2/postgresql-7.4.tar.gz [12311256/12311256] -> > "stp/data/dbt-2/postgresql-7.4.tar.gz" [1] > | > | FINISHED --09:45:01-- > | Downloaded: 12,311,256 bytes in 1 files > | + tar zxf postgresql-7.4.tar.gz > | tar (child): postgresql-7.4.tar.gz: Cannot open: No such file or directory > | tar (child): Error is not recoverable: exiting now > | tar: Child returned status 2 > | tar: Error exit delayed from previous errors > > Obviously wget -r downloads into a newly created subdirectory structure. > http://khack.osdl.org/stp/294335/logs/run-log.txt which belongs to a > successful test run has > | + wget -nv -t 0 --waitretry=60 -nc http://stp/data/dbt-2/postgresql-7.4.tar.gz > | 21:00:55 URL:http://stp/data/dbt-2/postgresql-7.4.tar.gz [12311256/12311256] -> > "postgresql-7.4.tar.gz" [1] > > Servus > Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] dbt2-pgsql on OSDL
Mark, I've tried to run some performance tests on your Scalable Test Platform but the tests failed at the build step. I guess the problem is near line 282 of http://khack.osdl.org/stp/294734/logs/run-log.txt | + wget -nv -t 0 --waitretry=60 -r http://stp/data/dbt-2/postgresql-7.4.tar.gz | 09:45:01 URL:http://stp/data/dbt-2/postgresql-7.4.tar.gz [12311256/12311256] -> "stp/data/dbt-2/postgresql-7.4.tar.gz" [1] | | FINISHED --09:45:01-- | Downloaded: 12,311,256 bytes in 1 files | + tar zxf postgresql-7.4.tar.gz | tar (child): postgresql-7.4.tar.gz: Cannot open: No such file or directory | tar (child): Error is not recoverable: exiting now | tar: Child returned status 2 | tar: Error exit delayed from previous errors Obviously wget -r downloads into a newly created subdirectory structure. http://khack.osdl.org/stp/294335/logs/run-log.txt which belongs to a successful test run has | + wget -nv -t 0 --waitretry=60 -nc http://stp/data/dbt-2/postgresql-7.4.tar.gz | 21:00:55 URL:http://stp/data/dbt-2/postgresql-7.4.tar.gz [12311256/12311256] -> "postgresql-7.4.tar.gz" [1] Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Postgresql on SAN
Le mar 06/07/2004 à 19:07, Alvaro Herrera a écrit : > On Tue, Jul 06, 2004 at 06:17:16PM +0200, Yannick Lecaillez wrote: > > > What need to do (understand, to devel) to allow several > > postgres instance running from several server to access to the > > same data (no replication at all) hosted on a SAN ? > > Clustered shared memory, cluster-wide spinlocks. And with decent > performance, while at it ... Perhaps could be interesting to look at cluster file system which seems to have same problems and find solution about locking (i.e OpenGFS). http://opengfs.sourceforge.net/showdoc.php?docpath=cvsmirror/opengfs/docs/ogfs-locking&doctitle=Locking&docauthor=ben.m.cahill(at)intel.com Found on google a clustered shared memory (openMosix project) http://www.unixreview.com/documents/s=8989/ur0404l/ I would have the pgsql-hackers genius for do that :) . I think its the only feature which force company to buy 5$ Oracle licence ... Sincerely, Yannick. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] LinuxTag wrapup
Bruce Momjian wrote: Gavin Sherry wrote: On Sat, 3 Jul 2004, Tom Lane wrote: Andreas Pflug <[EMAIL PROTECTED]> writes: - what about Oracle portability. IMHO we should rethink if we could make those people happy. How about a loadable personality (IIRC SAPDB has something like that), to exchange the parser in use with a custom one (by a SET command)? This way we have a pure ansi default, while enabling a way so someone could contribute an oracle style parser. How about an external tool that helps in translating apps to SQL-standard syntax? Oracle does accept the standard syntax after all. That way we are truly helping people liberate themselves: they can switch to any SQL-compliant database, not only Postgres. I totally agree. After all, oracle provides such tools to their customers. Should this be a TODO? An external tool helping translating sql is fine, but nothing to be defined todo for core pgsql IMHO. I still believe some minor "oracle helper" behaviour (not to call it oracle compatibility, to avoid wrong expectations) should be added. Currently, pgsql appears a bit arrogant towards those oracle centric people (always a matter of point of view, of course). We could avoid this by offering some concessions. Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error Codes
David Fetter wrote: > Kind people, > > So far, I have found two places where one can find the SQLSTATE error > codes: a header file, and the errcodes-appendix doc. Those are > excellent places. > > Did I miss how to get a list of them in SQL? If I missed it because > it isn't there, what would be a good way to have a current list > available? You know, it would be cool to have the codes and descriptions in a global SQL table. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transaction TODO list
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Why does START have a different Node from BEGIN anyway? This seems to > > be a leftover from when people thought they should behave differently. > > They are the same now, so there's no point in distinguishing them, or is it? > > [shrug...] I'd counsel leaving this as-is. We've practically always > regretted it when we made the parser discard information about what > the user typed. For instance, I was just reminded yesterday that we > really ought to distinguish SortClauses created due to user ORDER BY > clauses from those created because the parser silently added 'em. What information are we loosing by having START and BEGIN use the same nodes? Knowing what keyword they used to start the transaction? Seems that would only be important if we wanted them to behave differently, which we don't, I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] LinuxTag wrapup
Gavin Sherry wrote: > On Sat, 3 Jul 2004, Tom Lane wrote: > > > Andreas Pflug <[EMAIL PROTECTED]> writes: > > > - what about Oracle portability. > > > > > IMHO we should rethink if we could make those people happy. How about a > > > loadable personality (IIRC SAPDB has something like that), to exchange > > > the parser in use with a custom one (by a SET command)? This way we have > > > a pure ansi default, while enabling a way so someone could contribute an > > > oracle style parser. > > > > How about an external tool that helps in translating apps to > > SQL-standard syntax? Oracle does accept the standard syntax after all. > > That way we are truly helping people liberate themselves: they can > > switch to any SQL-compliant database, not only Postgres. > > I totally agree. After all, oracle provides such tools to their customers. Should this be a TODO? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Postgresql on SAN
Yannick Lecaillez wrote: > What need to do (understand, to devel) to allow several > postgres instance running from several server to access to the > same data (no replication at all) hosted on a SAN ? This is impossible. You can use a SAN if only one node is active at a time, and that is indeed a good high-availability solution that is usually easier to set up and maintain than replication, but you can't have several instances running on the same data at once. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Postgresql on SAN
On Tue, Jul 06, 2004 at 06:17:16PM +0200, Yannick Lecaillez wrote: > What need to do (understand, to devel) to allow several > postgres instance running from several server to access to the > same data (no replication at all) hosted on a SAN ? Clustered shared memory, cluster-wide spinlocks. And with decent performance, while at it ... > Is this a planed feature ? No, I don't think so. -- Alvaro Herrera () "Es filósofo el que disfruta con los enigmas" (G. Coli) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] LinuxTag wrapup
On Sun, 2004-07-04 at 19:57, Tom Lane wrote: > Anyone who needs this has always been able to make it trivially > (though you once had to invent a random column name for the one > required column). In Oracle, DUAL is treated specially internally for performance reasons, since it is so heavily used. Making a table with the same name would probably be a serviceable but under-performing migration mechanism. > Does anyone have the foggiest idea why they named it DUAL? Doesn't > seem a very mnemonic choice to me... There is no real authoritative answer to this, and it has long been a mystery. One semi-official version of the story is that it was originally an internal table with two rows used for some operations. How that became a single row scratch pad table is a mystery, since even the Oracle old-timers I know have no recollection of it ever being anything but what it currently is. Others claim it is a reference to 1x1 matrix operations. There are a number of different stories that people have heard -- I've heard three or four completely unrelated explanations from long-time Oracle folks -- and most of them are plausible. It is one of those things we will probably never know. Whatever its historical purpose, DUAL has been so pervasively used in the Oracle universe for so long that giving it a better name would break virtually every Oracle application in existence. It is an institution unto itself. j. andrew rogers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql on SAN
Yannick Lecaillez wrote: Hi ppl, What need to do (understand, to devel) to allow several postgres instance running from several server to access to the same data (no replication at all) hosted on a SAN ? I'm probably wrong but i think this type of dev should be easier to realize than replication ? Because all node are always consistent since they use exactly the same data ? The problem is the PG uses shared memory to coordinate the various backend processes. Sharing the files is not the difficult bit, sharing info about who is doing what with which tuple/has which locks is. In fact i'm asking why opensource db go only to the replication solution than "true" clustering solution using SAN like Oracle RAC ? I don't see the big advantage of the replication method (of course, for application which need more than few nodes hosting "small" db). The price could be an answer but what about a db of several hundred giga byte ? When a node is added it must contain this capacity nearly for "nothing" (i thinks its more and more a waste when number of node grow ...). The advantage of replication is that you can just use standard server boxes. There is a company offering specialised high-bandwidth hardware that is supposed to offer PG clustering, but I'm afraid I know no more about it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Postgresql on SAN
On Tue, Jul 06, 2004 at 18:17:16 +0200, Yannick Lecaillez <[EMAIL PROTECTED]> wrote: > Hi ppl, > > What need to do (understand, to devel) to allow several > postgres instance running from several server to access to the > same data (no replication at all) hosted on a SAN ? Only once such instance can be running at once. You want to have some sort of interlock to make sure this doesn't happen or you will lose your data. You can use this system for rapid fail over if the primary server dies. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Nested Transactions, Abort All
On Tue, Jul 06, 2004 at 12:49:46PM -0400, Stephen Frost wrote: > * Alvaro Herrera ([EMAIL PROTECTED]) wrote: > > We could use BEGIN NESTED for starting a subtransaction, roll it back > > with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I > > like SUBBEGIN etc best, and no one had an opinion when I asked. So the > > current code has SUBBEGIN, SUBCOMMIT, SUBABORT. If people prefer > > Just to be pedantic and talking about consistency- > Why SUBABORT instead of SUBROLLBACK? Just because it's ugly and too long ... I think the standard spelling is ROLLBACK, and ABORT is a Postgres extension. Since nested xacts are a Postgres extension, we might as well extend our own syntax :-) -- Alvaro Herrera () La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
* Alvaro Herrera ([EMAIL PROTECTED]) wrote: > We could use BEGIN NESTED for starting a subtransaction, roll it back > with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I > like SUBBEGIN etc best, and no one had an opinion when I asked. So the > current code has SUBBEGIN, SUBCOMMIT, SUBABORT. If people prefer Just to be pedantic and talking about consistency- Why SUBABORT instead of SUBROLLBACK? Stephen signature.asc Description: Digital signature
Re: [HACKERS] Nested Transactions, Abort All
On Tue, Jul 06, 2004 at 08:15:14AM +0200, Dennis Bjorklund wrote: > On Mon, 5 Jul 2004, Alvaro Herrera wrote: > > > > begin/end because they are already in an explicit/implicit transaction > > > by default... How is the user/programmer to know when this is the case? > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > you can't do is issue begin/end inside a function -- you always use > > subbegin/subcommit in that case. > > I've not understood why we need new tokens for this case. Maybe you've > explained it somewhere that I've missed. But surely the server know if you > are in a transaction or not, and can differentiate on the first BEGIN and > the next BEGIN. I think the best argument for this is that we need a command to abort the whole transaction tree, and another to commit the whole transaction tree. Those _have_ to be ROLLBACK (or ABORT) and COMMIT (or END), because the spec says they work like that and it would be hell for an interface like JDBC if they didn't. So it's out of the picture to use those commands to end a subtransaction. Now, it's clear we need new commands to end a subtransaction. Do we also want a different command for begin? I think so, just to be consistent. Conclusion: we need a different syntax. So we invent an extension. We could use BEGIN NESTED for starting a subtransaction, roll it back with ROLLBACK NESTED or some such, and commit with COMMIT NESTED. But I like SUBBEGIN etc best, and no one had an opinion when I asked. So the current code has SUBBEGIN, SUBCOMMIT, SUBABORT. If people prefer another syntax, then we can have a vote or core hackers can choose -- I don't care what the syntax is, but it has to be different from BEGIN, COMMIT, ROLLBACK. We can later implement savepoints, which will have "SAVEPOINT foo" and "ROLLBACK TO foo" as interface. (Note that a subtransaction is slightly different from a savepoint, so we can't use ROLLBACK TO in subtransactions because that has a different meaning in savepoints). -- Alvaro Herrera () "La rebeldía es la virtud original del hombre" (Arthur Schopenhauer) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Postgresql on SAN
Hi ppl, What need to do (understand, to devel) to allow several postgres instance running from several server to access to the same data (no replication at all) hosted on a SAN ? I'm probably wrong but i think this type of dev should be easier to realize than replication ? Because all node are always consistent since they use exactly the same data ? In fact i'm asking why opensource db go only to the replication solution than "true" clustering solution using SAN like Oracle RAC ? I don't see the big advantage of the replication method (of course, for application which need more than few nodes hosting "small" db). The price could be an answer but what about a db of several hundred giga byte ? When a node is added it must contain this capacity nearly for "nothing" (i thinks its more and more a waste when number of node grow ...). Is this a planed feature ? Sincerely, Yannick. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
On Tue, Jul 06, 2004 at 11:37:18AM -0400, Bruce Momjian wrote: > Alvaro Herrera wrote: > > On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote: > > > Alvaro Herrera wrote: > > > > > >What I'd like to do is start the transaction block before the function > > > >is called if we are not in a transaction block. This would mean that > > > >when the function calls BEGIN it won't be the first one -- it will > > > >actually start a subtransaction and will be able to end it without harm. > > > >I think this can be done automatically at the SPI level. > > > > > > Please tell me there is some sanity in this. If I follow you > > > correctly, at no point should anyone be able to issue an explicit > > > begin/end because they are already in an explicit/implicit transaction > > > by default... How is the user/programmer to know when this is the case? > > > > I'm not sure I understand you. Of course you can issue begin/end. What > > you can't do is issue begin/end inside a function -- you always use > > subbegin/subcommit in that case. > > And if you use SUBBEGIN/SUBCOMMIT in a function that isn't already call > inside from an explicit transaction, it will work because the call > itself is its own implicit transaction, right? Right. Note that this doesn't work with the current code -- in fact you can cause a server crash easily. -- Alvaro Herrera () "Doing what he did amounts to sticking his fingers under the hood of the implementation; if he gets his fingers burnt, it's his problem." (Tom Lane) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] investigating deadlocks
Is there some way to determine the specific relation involved when a deadlock
occurs? For example in the following error message (with log level set to
verbose):
2004-07-03 20:30:44 [21347] ERROR: 40P01: deadlock detected
DETAIL: Process 21347 waits for ShareLock on transaction 104411804; blocked
by process 21315. Process 21315 waits for ShareLock on transaction 104411808;
blocked by process 21347.
LOCATION: DeadLockReport, deadlock.c:888
STATEMENT: INSERT INTO
It tells me the transaction id's and process id's involved, but I see no way
to determine which specific relations were involved (the table being inserted
into has several different rules/triggers that touch other tables) Since
the error happens infrequently and randomly, the above information is
outdated by the time I can investigate. I am also skeptical about the message
that it was trying to get ShareLock since afaik share lock level is only used
when creating indexes. I have thought about turning on some of the GUC's
like trace_locks or debug_deadlocks, but the docs are very ambiguous as to
what these items do specifically . Is there some other option I have missed
out on?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] A wanna be
The developer's FAQ is a good place to start. --- Jonathan Gardner wrote: > On Thursday 01 July 2004 01:10 pm, Jaime Casanova wrote: > > > > I'm a young developer with some knowledge in various programming > > languages including C. Nowadays, i'm not capable to contribute to any > > part of the postgresql project but i want seriously learn what i need in > > order to contribute. Can you guys tell me where can i start? > > Where can i find usefull information about Databases programming > > techniques? > > > > First, become intimately familiar with the tool from the user's perspective. > I use PostgreSQL for some major projects, and so I have grown familair with > it. I don't think I could've gotten this familiar without having to > actually use it in a production environment. > > Second, start coding extensions to it. I have written C code for both the > client and server side, and have learned tremendously from that. > > Finally, start reading code around the parts you are interested in. > > I have also found that watching the patches come in will give you a feel for > what is happening. Not only do you see the actual code, but you can watch > the experts talk about it. You can read through the code and then watch as > the server behaves differently. It's pretty amazing. > > -- > Jonathan Gardner > [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > > > !DSPAM:40e4b35d139131332413006! > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Nested Transactions, Abort All
Alvaro Herrera wrote: > On Sat, Jul 03, 2004 at 02:32:44AM -0500, Thomas Swan wrote: > > Alvaro Herrera wrote: > > > >What I'd like to do is start the transaction block before the function > > >is called if we are not in a transaction block. This would mean that > > >when the function calls BEGIN it won't be the first one -- it will > > >actually start a subtransaction and will be able to end it without harm. > > >I think this can be done automatically at the SPI level. > > > > Please tell me there is some sanity in this. If I follow you > > correctly, at no point should anyone be able to issue an explicit > > begin/end because they are already in an explicit/implicit transaction > > by default... How is the user/programmer to know when this is the case? > > I'm not sure I understand you. Of course you can issue begin/end. What > you can't do is issue begin/end inside a function -- you always use > subbegin/subcommit in that case. And if you use SUBBEGIN/SUBCOMMIT in a function that isn't already call inside from an explicit transaction, it will work because the call itself is its own implicit transaction, right? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] client_min_messages in dumps?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I agree changing some of those noiser notices would be good. I think > > the best idea would be to add a client_min_messages level of novice for > > them. > > Yes ... > > > In fact, looking at the code, I see that the INFO level is almost > > never used in our code. Perhaps we should just downgrade them to > > INFO-level messages. > > No! That is not a downgrade --- INFO messages are *not suppressable*. Uh, postgresql.conf has: #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error #log_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic I also don't see LOG used much in the code at all. It seems to be used mostly by VACUUM and ANALYZE. Seems something is wrong. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] client_min_messages in dumps?
Bruce Momjian <[EMAIL PROTECTED]> writes: > I agree changing some of those noiser notices would be good. I think > the best idea would be to add a client_min_messages level of novice for > them. Yes ... > In fact, looking at the code, I see that the INFO level is almost > never used in our code. Perhaps we should just downgrade them to > INFO-level messages. No! That is not a downgrade --- INFO messages are *not suppressable*. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] client_min_messages in dumps?
Tom Lane wrote: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > Would anyone else think that dumping: > > SET client_min_messages TO warning; > > In pg_dumps would be cool? > > > It would mean that while restoring a dump you can actually see the wood > > for the trees when trying to see ERRORs and WARNINGs the the massive > > spam of messages you get while restoring. > > Another answer is to get rid of some of our chattier NOTICEs, or at > least downgrade 'em to DEBUG1 or thereabouts. Does anyone really still > think that > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table > "foo" > is conveying any useful information? It's not like you couldn't find > out the name of the index from "\d foo". > > I think this is a better answer than having pg_dump try to force the > message level, since that would get in the way of seeing the messages > when you needed to do so for debugging reasons. > > (cc'ing to Bruce who has always been the most vocal defender of those > notices... now where did I put my flameproof longjohns?) I agree changing some of those noiser notices would be good. I think the best idea would be to add a client_min_messages level of novice for them. In fact, looking at the code, I see that the INFO level is almost never used in our code. Perhaps we should just downgrade them to INFO-level messages. FYI, I have never advocated those messages. Rather, I advocated allowing users to control how much detail they see in their client and in their logs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] More thoughts on drop tablespace
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > The same thing may also apply to the default tablespace of a database as > well... No, because it will always contain some files (at the very least, the db's pg_class *must* live there). See prior thread about exactly this issue. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Quick question regarding tablespaces
I would like to see some tool that reported an semi-accurate value for random page cost before adding the value per tablespace. --- Scott Marlowe wrote: > On Thu, 2004-07-01 at 18:54, Gavin Sherry wrote: > > On Thu, 1 Jul 2004, Mike Rylander wrote: > > > > > On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: > > > > Hi Mike, > > > > > > > > In this release, unfortunately not. > > > > > > That't too bad, but it's not that urgent I suppose. > > > > > > > > > > > I had some idea early on of putting rand_page_cost in pg_tablespace and > > > > having the planner have access to it for costing. I didn't actually get > > > > around to it but. :-( > > > > > > Well, I haven't looked at the PG source before, but if you have some specific > > > design ideas I would be glad to help out. I'm just not sure where (or when, > > > with the official release coming (sort of) soon) to start, but with some > > > pointers I'll do what I can! > > > > Well, it wont be in 7.5. Feel free to start looking at how > > random_page_cost in cost_index(). It might be worthwhile introducing a per > > tablespace performance factor so that we could could say that the cost of > > fetching an index tuple from tablespace A is half that of fetching an > > index tuple from tablespace B. That idea might not actually turn out to be > > a very good one once I look at it closely though. > > How about having a per cluster / database / tablespace / table type > setup that goes in a hierarchy, if they're there. I.e. if the database > doesn't have it's own random_page_cost, it inherits from cluster, if a > tablespace doesn't have one, it inherits from cluster->database, and so > on to individual tables / indexes. It may be that it's easier to > implement for them all now while doing it for tablespaces. Just > wondering. I'm a user, not a hacker, so I have no idea how much that > idea makes any sense, but I would certainly love to be able to set an > index to have a random_page_cost effect of 1.1 while the table it lives > in is 1.3, the tablespace 1.4, and so on. But not required, because it > always inherits from the parent if it doesn't have one, like stats > target. > > > > !DSPAM:40e4b98b142131356954127! > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [Plperlng-devel] strange bug in plperl
Thanks. I have 2 questions regarding this.
1. Is prodesc->fn_retistuple true if and only if this is a set returning
function? (what about setof int? what about a function returning a single
composite?)
2. I am suspicious about the use of these globals to stash data (and they
should all be marked static in any case so we don't pollute the namespace)
and already have it on my TODO list to examine them more closely. Won't they
get clobbered if our function makes an spi call which in turn calls this or
another perl function? If they will, we might need to use some sort of very
simple stack structure for this data, up to some reasonable level of
recursion (any bids?).
cheers
andrew
Sergej Sergeev said:
> Atached patch fix this bug
>
> Serg
>
>
> Andrew Dunstan wrote:
>
>>
>> Can anyone suggest why I might be seeing this effect (each notice
>> comes out once per row plus once per function call)
>>
>> thanks
>>
>> andrew
>>
>> andrew=# create function tstset() returns setof tst language plperl as
>> $$ andrew$# elog(NOTICE,"tstset called");
>> andrew$# return [{i=>1,v=>"one"},{i=>2,v=>"two"}];
>> andrew$# $$;
>> CREATE FUNCTION
>> andrew=# select * from tstset();
>> NOTICE: tstset called
>> NOTICE: tstset called
>> NOTICE: tstset called
>> i | v ---+-
>> 1 | one
>> 2 | two
>> (2 rows)
>>
>> ___
>> Plperlng-devel mailing list
>> [EMAIL PROTECTED]
>> http://pgfoundry.org/mailman/listinfo/plperlng-devel
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] bug in DROP TABLESPACE
On Tue, 6 Jul 2004, Christopher Kings-Lynne wrote: > There is bad breakage in the DROP TABLESPACE command if the only thing > "in" that tablespace is the default tablespaces for a schema: > > test=# create tablespace myspace location '/home/chriskl/loc'; > CREATE TABLESPACE > test=# create schema myschema tablespace myspace; > CREATE SCHEMA > test=# drop tablespace myspace; > DROP TABLESPACE > test=# select * from pg_namespace where nspname='myschema'; > nspname | nspowner | nsptablespace | nspacl > --+--+---+ > myschema |1 | 17228 | > (1 row) > > test=# select * from pg_tablespace where oid=17228; > spcname | spcowner | spclocation | spcacl > -+--+-+ > (0 rows) > > test=# create table myschema.blah (a int4); > ERROR: could not create directory > "/home/chriskl/local/data/pg_tblspc/17228/17227": No such file or directory Tom already mentioned this just after committing tablespaces: 'Minor DROP TABLESPACE issue' http://www.mail-archive.com/[EMAIL PROTECTED]/msg46540.html In fact, I see that you contributed to the thread :-). I think the result of the thread was to make the error message a little more helpful and that adding empty files to represent schemas would be a pain (think WAL and name collision). Thanks, Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] More thoughts on drop tablespace
Well at least the database can be queried easily for usage of that tablespace. Yes, that's the easy part to fix. You'd just set the dattablespace back to 0 when you dropped the tablespace. For the namespace issue, it would help if *some* object would be created with that namespace (e.g. an empty directory or file with the namespace oid), so that pg_tablespace_databases() could detect it. Seems like the only way. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] More thoughts on drop tablespace
Christopher Kings-Lynne wrote: The same thing may also apply to the default tablespace of a database as well... Well at least the database can be queried easily for usage of that tablespace. For the namespace issue, it would help if *some* object would be created with that namespace (e.g. an empty directory or file with the namespace oid), so that pg_tablespace_databases() could detect it. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] More thoughts on drop tablespace
The same thing may also apply to the default tablespace of a database as well... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bug with view definitions?
Andreas Pflug wrote: Christopher Kings-Lynne wrote: need_paren = (PRETTY_PAREN(context) ? !IsA(op->rarg, RangeTblRef) : true); In a quick glance this code seems close to completely brain dead :-( This probably was about catching expr_A UNION (expr_B INTERSECT expr_C) cases, falsely assuming left-to-right won't ever need parentheses. Apparently the current version already fixes this completely, suppressing parentheses also with non-pretty. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] bug in DROP TABLESPACE
There is bad breakage in the DROP TABLESPACE command if the only thing "in" that tablespace is the default tablespaces for a schema: test=# create tablespace myspace location '/home/chriskl/loc'; CREATE TABLESPACE test=# create schema myschema tablespace myspace; CREATE SCHEMA test=# drop tablespace myspace; DROP TABLESPACE test=# select * from pg_namespace where nspname='myschema'; nspname | nspowner | nsptablespace | nspacl --+--+---+ myschema |1 | 17228 | (1 row) test=# select * from pg_tablespace where oid=17228; spcname | spcowner | spclocation | spcacl -+--+-+ (0 rows) test=# create table myschema.blah (a int4); ERROR: could not create directory "/home/chriskl/local/data/pg_tblspc/17228/17227": No such file or directory This is probably pretty nasty because it means there's no way to check if dropping a tablespace is safe :( Maybe the only solution is if nsptablespace is invalid when creating an object in the schema, then fix it and ignore it? Or fix it when first connecting to the database? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: subtransactions and FETCH behaviour (was Re: [HACKERS] PREPARE and transactions)
> Well, the proposal of implementing it like holdable cursors means using > a Materialize node which, if I understand correctly, means taking the > whole result set and storing it on memory (or disk). Would it help to hold the lock for a record that is the current cursor position, iff this record was updated (and subsequently rolled back) by this subtxn, and release that lock as soon as you fetch next ? Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug with view definitions?
Christopher Kings-Lynne wrote: need_paren = (PRETTY_PAREN(context) ? !IsA(op->rarg, RangeTblRef) : true); In a quick glance this code seems close to completely brain dead :-( For one thing, why isn't it making separate determinations about whether the left and right inputs of the UNION (resp INTERSECT or EXCEPT) operator need to be parenthesized? After that maybe we could figure out what the individual decisions need to be. So what are we going to do about it? Was it one of the pgAdmin guys who wrote it in the first place? Yep, me. It was still on my radar to fix; not surprising, Tom was faster. I'll have a look at the "braindead" issue. Regards, ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Point in Time Recovery
> - by time - but the time stamp on each xlog record only specifies to the > second, which could easily be 10 or more commits (we hope) > > Should we use a different datatype than time_t for the commit timestamp, > one that offers more fine grained differentiation between checkpoints? Imho seconds is really sufficient. If you know a more precise position you will probably know it from backend log or an xlog sniffer. With those you can easily use the TransactionId way. > - when we stop, keep reading records until EOF, just don't apply them. > When we write a checkpoint at end of recovery, the unapplied > transactions are buried alive, never to return. > - stop where we stop, then force zeros to EOF, so that no possible > record remains of previous transactions. > I'm tempted by the first plan, because it is more straightforward and > stands much less chance of me introducing 50 wierd bugs just before > close. But what if you restore because after that PIT everything went haywire including the log ? Did you mean to apply the remaining changes but not commit those xids ? I think it is essential to only leave xlogs around that allow a subsequent rollforward from the same old full backup. Or is an instant new full backup required after restore ? Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Recovery Features
On Tue, 2004-07-06 at 00:30, Mike Mascari wrote: > Simon Riggs wrote: > > > On Mon, 2004-07-05 at 23:40, Mike Mascari wrote: > > > > hmmm...not sure I know what you mean. > > > > It is very-very-close-to-impossible to edit the transaction logs > > manually, unless some form of special-format editor were written for the > > purpose. > > > > Is it clear that the PITR features are completely different from > > pg_dump? (Which would allow a manual edit and recover). The xlogs are > > binary files that refer to all changes to all tables in a cluster > > ordered by time, rather than by table. > > What I meant by hand-restore was > > 1. A total backup occurrs on Monday morning > > 2. Transactions occur on Monday, Tuesday and Wednesday, with PITR > archiving enabled > > 3. Intern deletes everyting from obscure_table on Thursday afternoon > and wants to restore it as it was on Thursday morning > > 4. On some other machine, the total backup is restored into a new > cluster, the transaction logs replayed to that point-in-time where > intern deleted everything from obscure_table > > 5. The table is dumped manually and restored in the production > database, because it is known that this table has no logicial > implications for the consisetency of other tables. > > That's what I meant by hand-restore. > Excellent. Thanks very much for the clarification. I was worried that I had misrepresented the functionality, but you have it spot on. The scenario you describe is going to be possible now... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
