Re: [PATCHES] [HACKERS] Point in Time Recovery
On Mon, 2004-07-19 at 04:03, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Latest version, pitr_v5_2.patch... > > Reviewed and committed with some adjustments. > Wow! Thanks very much - you work fast. I'll be re-testing later today. > I see the following significant loose ends: > > * Documentation is, um, lacking. (One point in particular is that I > inserted the recovery.conf.sample file into CVS, but did not fill in > the patch's lack of attempt to install it anywhere.) > Yes...wasn't sure what to do with that. Is everybody happy to install it as a sample into the main Data Directory? (i.e. as recovery.conf.sample rather than recovery.conf which would be a bad thing). > * As Bruce has pointed out already, the process of making a backup > needs some improvements for more safety: the starting and ending WAL > offsets have got to be recorded somehow. > Haven't got to that yet, but will do. > * As I have pointed out already, we need to invent "timelines" to > allow incompatible WAL segments to exist side-by-side. I will volunteer > to look into this. Yes, discussing on the other thread. > > * I think creating a .ready file during XLogFileOpen is completely bogus, > for reasons mentioned in committed comments (look for XXX). Possibly > this can go away with timelines. Yes, to some extent it would go away with timelines. If you have a local copy at the end of a timeline that isn't archived, then it seems a good idea to archive it, or at least copy it somewhere safe. If you don't then you will not be able to revert to a full recovery of that timeline in the future should you choose to do so. The code and its location may be somewhat more suspect :) > > * I am wondering if it wouldn't be a good idea to remove the local copy > of any segment we successfully obtain from archive. The existing > comments note that we might get a wrong or corrupted file from archive, > but aren't we in at least as much risk of using an obsolete segment > restored from backup if we leave the local segment in place? (The > archive recovery run itself will know not to do this, but if we crash > shortly thereafter, the ensuing recovery run would NOT know not to > trust such files.) > I agree they're a loose end that needs some thought. I avoided that decision by going around the files. We originally agreed that we would keep that datareason was you can't tell whether the files have been restored by a backup that forgot to exclude pg_xlog, or that we are choosing to do a PITR recovery on an otherwise healthy system (or as the comments explain maybe we lost everything except pg_xlog). If we crash during recovery it doesn't crash recover and restart. If we crash after recovery, then the checkpoint record will have moved forward and we so we don't then accidentally re-use those local copies. Timelines will solve this... > > Perhaps the last point is really a backup-process issue. AFAICS there > is no good reason for a backup tarfile to include $PGDATA/pg_xlog at > all, and some good reasons for it not to. Can we redesign either the > backup process or the disk layout so that that will not happen? Then > we could stop worrying about stale local pg_xlog files. > Thats the way I saw it. Seems fairly easy to say "don't backup pg_xlog", but you can't guarantee they won't, even if you tell them not to... What is stale today maybe considered to be actually your best option when testing to see whether a recovery has achieved your objectives. I'll read the who patch, your comments and test before I respond further. Thanks for working so hard on this, so quickly. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] logfile subprocess and Fancy File Functions
Magnus Hagander wrote: > Super-minor nitpicking from just eyeing over the patch, not actually > checking how it works. Reviewing the own code the most obvious things are overlooked. > > This patch changes the error message for pg_signal_backend() to "only > superuser may access generic file functions". > > I'm sure that was not intended.. You probably need to pass a parameter > to requireSuperuser() about what should go in the err msg. Yes, seems I was a bit overenthusiastic... > > Also, I think you forgot to attach syslogger.h. Indeed, attached is include/postmaster/syslogger.h Regards, Andreas /*- * * syslogger.h * Exports from postmaster/syslogger.c. * * Portions Copyright (c) 2004, PostgreSQL Global Development Group * * $PostgreSQL: $ * *- */ #ifndef _SYSLOGGER_H #define _SYSLOGGER_H #include "pgtime.h" /* GUC options */ extern int Log_RotationAge; extern int Log_RotationSize; extern char * Log_directory; int SysLogger_Start(void); void SysLoggerMain(int argc, char *argv[]); extern bool LogFileRotate(void); #endif /* _SYSLOGGER_H */ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Point in Time Recovery
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> * Documentation is, um, lacking. (One point in particular is that I > >> inserted the recovery.conf.sample file into CVS, but did not fill in > >> the patch's lack of attempt to install it anywhere.) > > > I figure it should go in share like the other sample files, and tell > > people to copy it to /data and modify it for recovery. > > It should certainly go to /share as a .sample file. I was thinking that > initdb should perhaps copy it into $PGDATA (still as .sample, not as > .conf!) so it'd be right there when you need it. I think /share is best. I see other *.share file that aren't used until you rename them and move them to the right directory, and recovery.conf.sample seems the same. I think having the sample at the top of data when for most people it will be unused is strange. > >> Perhaps the last point is really a backup-process issue. AFAICS there > >> is no good reason for a backup tarfile to include $PGDATA/pg_xlog at > >> all, and some good reasons for it not to. > > > Seems we should just clear out the /pg_xlog directory before we start > > recovery. > > No, that's a horrid idea, because it loses the ability to combine > archival xlog files with recent files in /pg_xlog that are not yet > archived. We need to distinguish old files that were accidentally > captured by backup from very-recent files. I think the cleanest way to > do that is for backup not to capture them in the first place. I am confused. Aren't we always doing a restore from a backup? Are you saying there are cases where we aren't and need the stuff in pg_xlog? Are you saying we might have some new WAL files that we want to add to pg_xlog before we do the restore, like the most recent WAL that wasn't archived because it wasn't finished? Why would we be doing a recover if we had such files? I see your point that we wouldn't know which file to use, the archive version or the pg_xlog version, but actually wouldn't the archive version always be preferred because we would know it to be complete. I don't see any reliable way to prevent people from having pg_xlog in their backups seeing they might use snapshots, tar, etc. > > We are going to rename recovery.conf to recovery.in-progress > > or something to prevent us from clearing out the directory after a > > crash, right? > > I had second thoughts about that and didn't do it in the committed > patch, though it's certainly still open for debate. How are we handling a crash during recovery? > > (I see you rename recovery.conf to recovery.done. Is > > that wise? > > Yes. Once you've done with a PITR recovery you definitely do *not* want > a subsequent crash recovery to think it should obey your recovery_target > limit. But if you fail before you've finished the recovery run it > should theoretically be okay to retry, so I didn't add code to rename to > "recovery.inprogress". We can certainly add it later if we decide it's > a good idea. Ah, OK, so it just keeps going. However, we don't know if what is in pg_xlog was in the process of being copied from the archive at the time of the crash, no? In fact I am wondering if we should be transfering the archive files into temporary names than doing an 'mv' to make them current so we don't get partial files in pg_xlog. However, we can't do that because we are using a user-supplied command line. Should we pass a fake name to the command string then do the 'mv' ourselves. With WAL now, we do an fsync so we know the contents are crash-proof, but I am not sure how to do that during recovery. I guess this gets back to how to handle the contents of pg_xlog during recovery. -- 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 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Point in Time Recovery
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> It should certainly go to /share as a .sample file. I was thinking that >> initdb should perhaps copy it into $PGDATA (still as .sample, not as >> .conf!) so it'd be right there when you need it. > I think /share is best. Okay, we agree on that part at least; I'll take care of it. If anyone wants to argue for further copying during initdb, that can be added later. > I am confused. Aren't we always doing a restore from a backup? No. This code serves two purposes: recovery from archived WAL and point-in-time recovery. You might want to do a PITR run at a time where not all your WAL segments have been pushed to archive. Indeed the latest one can never be so pushed, since it's unfinished. Suppose you are trying to do PITR recovery to a time just a few minutes ago that is still in the latest WAL segment --- there is simply not any legal way to have that come from the archive. So we can't simply zero out pg_xlog at the start of a PITR run, even if there weren't a don't-destroy-data argument against it. >> I had second thoughts about that and didn't do it in the committed >> patch, though it's certainly still open for debate. > How are we handling a crash during recovery? Retry, perhaps. It doesn't seem any different from crash-during-recovery in the non-archived scenario ... > Ah, OK, so it just keeps going. However, we don't know if what is in > pg_xlog was in the process of being copied from the archive at the time > of the crash, no? Nonissue. It goes into RECOVERYXLOG and we never assume that that's initially good. See RestoreArchivedXLog(). regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] Point in Time Recovery
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> It should certainly go to /share as a .sample file. I was thinking that > >> initdb should perhaps copy it into $PGDATA (still as .sample, not as > >> .conf!) so it'd be right there when you need it. > > > I think /share is best. > > Okay, we agree on that part at least; I'll take care of it. If anyone > wants to argue for further copying during initdb, that can be added > later. > > > I am confused. Aren't we always doing a restore from a backup? > > No. This code serves two purposes: recovery from archived WAL and > point-in-time recovery. You might want to do a PITR run at a time > where not all your WAL segments have been pushed to archive. Indeed > the latest one can never be so pushed, since it's unfinished. Suppose > you are trying to do PITR recovery to a time just a few minutes ago > that is still in the latest WAL segment --- there is simply not any > legal way to have that come from the archive. > > So we can't simply zero out pg_xlog at the start of a PITR run, even > if there weren't a don't-destroy-data argument against it. If we had some code that checks pg_xlog on recovery startup, it could rename each pg_xlog file and then recover the file from the archive. If it doesn't exist or is truncated, discard it. If it is the right size, we need to check to see which one has a WAL eof-of-segment marker (we have on of those, right?). This would seem to catch all the cases: o file brought back by tar, but complete file in archive o archive in process of writing during crash o partially full file in pg_xlog What it doesn't cover are cases where tar gets a partial copy of a pg_xlog file but the file never made it to archive yet, and a new pg_xlog file was created and we get some of that file too. In fact, the backup could get holes in the pg_xlog file where the backup has zeros but the real file had data added to it after the zeros: in tar X 0 X realX X X This could happen when file has this: X 0 0 backup reads this: X 0 database writes this: X X X backup reads the remainder of the file: X 0 X In this case the end-of-segment marker doesn't even help us, and their might not be an archive copy of this because it didn't happen yet. I think I see a solution. We are going to create a file during backup so we know the wal offsets and xids. If we see that file, we know either we have a restore of a backup or they currently running a backup. If we tell them not to restore while a backup is running (seems pretty obvious) we can then delete pg_xlog when the backup wal offset file exists. In other cases, we know the WAL files are valid to use. -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Point in Time Recovery
On Mon, 2004-07-19 at 05:54, Tom Lane wrote: > code in Simon's original patch that would start bleating Code that bleats? LOL :) (is that a new log level?) Some of it was perhaps a little woolly You've made my day, Simon Riggs (still laughing) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] Point in Time Recovery
Bruce Momjian <[EMAIL PROTECTED]> writes: > we need to check to see which one has a WAL eof-of-segment marker (we > have on of those, right?). No, we don't. > I think I see a solution. We are going to create a file during backup so > we know the wal offsets and xids. If we see that file, we know either > we have a restore of a backup or they currently running a backup. ... or the last backup attempt failed, but they forgot to remove the file it left. Or we are doing crash recovery after the system lost power while a backup was running. Or half a dozen other obvious scenarios. > If we tell them not to restore while a backup is running (seems pretty > obvious) we can then delete pg_xlog when the backup wal offset file > exists. In other cases, we know the WAL files are valid to use. We're not deleting pg_xlog, period. IMHO it's too dangerous even to have such a function in the code. My original suggestion was to *replace* individual xlog files with data extracted from archive, and only after determining that the archive indeed has a copy of that particular file (and we can fetch it). This at least has a fighting chance of not losing information. Wiping pg_xlog in toto on the basis of a guess about the system status is just a form of russian roulette. Sooner or later you will wipe some xlog files that you can't get back from archive. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] Point in Time Recovery
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > we need to check to see which one has a WAL eof-of-segment marker (we > > have on of those, right?). > > No, we don't. > > > I think I see a solution. We are going to create a file during backup so > > we know the wal offsets and xids. If we see that file, we know either > > we have a restore of a backup or they currently running a backup. > > ... or the last backup attempt failed, but they forgot to remove the > file it left. Or we are doing crash recovery after the system lost > power while a backup was running. Or half a dozen other obvious scenarios. > > > If we tell them not to restore while a backup is running (seems pretty > > obvious) we can then delete pg_xlog when the backup wal offset file > > exists. In other cases, we know the WAL files are valid to use. > > We're not deleting pg_xlog, period. IMHO it's too dangerous even to > have such a function in the code. > > My original suggestion was to *replace* individual xlog files with data > extracted from archive, and only after determining that the archive > indeed has a copy of that particular file (and we can fetch it). > This at least has a fighting chance of not losing information. Wiping > pg_xlog in toto on the basis of a guess about the system status is just > a form of russian roulette. Sooner or later you will wipe some xlog > files that you can't get back from archive. OK, if you don't want to place restrictions on recovery, fine, but how do you handle the situation where you backup but the WAL file has holes in the tar backup but you don't have an archive file to use because it didn't make it to the archive before the drive died? Can we detect holes in the WAL file recovered from backup? We might, but I am afraid we might not. -- 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 8: explain analyze is your friend
Re: [PATCHES] add missing options to pg_dumpall
Patch applied. Thanks.
---
Christopher Kings-Lynne wrote:
> OK,
>
> Here is another patch that fixes a stack of pg_dump bugs:
>
> * Fix help text ordering
>
> * Add back --set-session-authorization to pg_dumpall. Updated the docs
> for that. Updated help for that.
>
> * Dump ALTER USER commands for the cluster owner ("pgsql"). These are
> dumped AFTER the create user and create database commands in case the
> permissions to do these have been revoked.
>
> * Dump ALTER OWNER for public schema (because it's possible to change
> it). This was done by adding TOC entries for the public schema, and
> filtering them out at archiver time. I also save the owner in the TOC
> entry just for the public schema.
>
> * Suppress dumping single quotes around schema_path and DateStyle
> options when they are set using ALTER USER or ALTER DATABASE. Added a
> comment to the steps in guc.c to remind people to update that list.
>
> * Fix dumping in --clean mode against a pre-7.3 server. It just sets
> all drop statements to assume the public schema, allowing it to restore
> without error.
>
> * Cleaned up text output. eg. Don't output -- Tablespaces comment if
> there are none. Same for groups and users.
>
> * Make the commands to DELETE FROM pg_shadow and DELETE FROM pg_group
> only be output when -c mode is enabled. I'm not sure why that hasn't
> been done before?!?!
>
> This should be good for application asap, after which I will start on
> regression dumping 7.0-7.4 databases.
>
> Chris
>
[ application/x-gzip is not supported, skipping... ]
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
--
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: [PATCHES] fix schema ownership on first connection preliminary patch
Tom is reviewing this. --- Bruce Momjian wrote: > > I have added the v2 version of this patch to the patch queue (attached). > I agree with Tom that there is no need for regression tests for this > feature and have removed that part of the patch. > > http://momjian.postgresql.org/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. > > --- > > Fabien COELHO wrote: > > > > Dear hackers, > > > > Please find attached a preliminary patch to fix schema ownership on first > > connection. It is for comments and advices as I still have doubts about > > various how-and-where issues, thus it is not submitted to the patch list. > > > > (1) It adds a new "datisinit" attribute to pg_database, which tells > > whether the database initialization was performed or not. > > The documentation is updated accordingly. > > > > (2) This boolean is tested in postinit.c:ReverifyMyDatabase, > > and InitializeDatabase is called if necessary. > > > > (3) The routine updates pg_database datisinit, as well as pg_namespace > > ownership and acl stuff. The acl item update part is not yet > > appropriate: I'd like some answers before going on. > > > > (4) Some validation is added. It passes for me. > > > > > > My questions: > > > > - is the place for the first connection housekeeping updates appropriate? > > it seems so from ReverifyMyDatabase comments, but these are only comments. > > > > - it is quite convenient to use SPI_* stuff for this very rare updates, > > but I'm not that confident about the issue... On the other hand, the > > all-C solution would result in a much longer and less obvious code. > > > > - it is unclear to me whether it should be allowed to skip this under > > some condition, when the database is accessed in "debug" mode from > > a standalone postgres for instance? > > > > - also I'm wondering how to react (what to do and how to do it) on > > errors within or under these initialization. For instance, how to > > be sure that the CurrentUser is reset as expected? > > > > Thanks in advance for your answers and comments. > > > > Have a nice day. > > > > -- > > Fabien Coelho - [EMAIL PROTECTED] > > Content-Description: > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > -- > 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 > Dear hackers, > > Please find attached a patch to fix schema ownership on first > connection, so that non system schemas reflect the database owner. > > > This revised patch includes fixes after Tom comments on names used in > the validation. However, the validation is still there. It is easy to > edit it out if required, but I still think that such a test is a good thing. > > > (1) It adds a new "datisinit" attribute to pg_database, which tells > whether the database initialization was performed or not. > The documentation is updated accordingly. > > > (2) The datisnit boolean is tested in postinit.c:ReverifyMyDatabase, > and InitializeDatabase is called if necessary. > > > (3) The routine updates pg_database datisinit, as well as pg_namespace > ownership and acl stuff. > > > I think that the race condition if two backends connect for > the first time to the same database is correctly taken care of, > as only one backend will update the datisinit flag and then will fix the > schema ownership. > > > (4) Some validation is added. > > > Some questions: > > - is the place for the first connection housekeeping updates appropriate? > it seems so from ReverifyMyDatabase comments, but these are only comments. > > > - it is quite convenient to use SPI_* stuff for this very rare updates, > but I'm not that confident about the issue... On the other hand, the > all-C solution would result in a much longer and less obvious code:-( > > > - it is unclear to me whether it should be allowed to skip this under > some condition, when the database is accessed in "debug" mode from > a standalone postgres for instance? > > > - also I'm wondering how to react (what to do and how to do it) on > errors within or under these initialization. For instance, how to > be sure that the CurrentUser is reset as expected? > > > Thanks in advance for any comments. > > Have a nice day. > > -- > Fabien. > > *** ./doc/src/sgml/catalogs.sgml.orig Mon Jun 7 09:08:11 2004 > --- ./doc/src/sgml/catalogs.sgml Wed Jun 9 10:26:39 2004 > *** > *** 1536,1541 > --- 1536,1552 >
Re: [PATCHES] pgxs: build infrastructure for extensions v4
I have Peter reviewing this. --- Fabien COELHO wrote: > > Dear patchers, > > > Please find attached another new version of a patch which provides a > working infrastructure for pg extensions. I hope it addresses all of > Peter's comments. I'll be away for the next 3 weeks, so if minor changes > are required it would be best if you could proceed without me... > > The infrastructure is a simple reworking of the already available internal > infrastructure for contrib, so that it can be used outside of the > postgresql source tree after installation, without gory details being in > sight of the user. The documentation is added as a new section in > "xfunc.sgml". > > I updated all contrib makefiles so that they can be used either the > standard way after a configure, or the new way without needing a configure > but with an already installed postgreSQL. Just try them with > > "cd contrib/foo ; make USE_PGXS=1 install" > > *AFTER* postgresql has been configure, compiled and installed. It should > be compiled and installed wrt to the first "pg_config" which is found in > the path. > > > How it works: > > - necessary files (includes, scripts, makefiles...) are copied under >$(pkglibdir)/pgxs on the initial "make install". > >due to gnu-make restriction on how its includes work, these files must >be copied with the *same* directory structure as the pg source tree. >The fact does not appear at all in the actual infrastructure from the >user point of view, but it explains why subdirectories are necessary >under pgxs, if you care to have a look. > > - the makefile of any extension is expected to set macro PGXS to >"pg_config --pgxs", to include a special makefile, and to >set some macros depending on what is to be built, just like in >current contrib. > > - I've added two PGXS-triggered conditionnals in Makefile.global, >so that includes and libraries are taken where needed. > > > Notes: > > - there is still a "light-install" target that matches the previous >"install" behavior, as new "install" matches previous "server-install". > > - I'm not sure the sgml is ok. It looks ok, but is it enough. > > - It validates and works for me. > > > Have a nice day, > > -- > Fabien Coelho - [EMAIL PROTECTED] Content-Description: [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html -- 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Point in Time Recovery
On Mon, 2004-07-19 at 17:56, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> I had second thoughts about that and didn't do it in the committed > >> patch, though it's certainly still open for debate. > > > How are we handling a crash during recovery? > > Retry, perhaps. It doesn't seem any different from crash-during-recovery > in the non-archived scenario ... > Well, a recovery is just re-applying already written logs at super speed. We don't need to write WAL because we already wrote it once (and that would really confuse the timeline issue). I think if this was an issue, the solution would be to speed up recovery since that would benefit us more than putting recovery-squared code in. Just start over... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Point in Time Recovery
Okay, we agree on that part at least; I'll take care of it. If anyone wants to argue for further copying during initdb, that can be added later. I reckon it should be copied into $PGDATA :) Otherwise, when I'm in a panic at recovery time, I'd have to figure out where the heck my package has installed the share conf file to, conf files usually aren't in share, etc., etc. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PATCHES] Docs for PL/Perl
Kind people,
Please find enclosed a patch that matches the PL/Perl documentation
(fairly closely, I hope) to the current PL/Perl implementation.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plperl.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/plperl.sgml,v
retrieving revision 2.23
diff -u -r2.23 plperl.sgml
--- doc/src/sgml/plperl.sgml16 May 2004 23:22:06 - 2.23
+++ doc/src/sgml/plperl.sgml20 Jul 2004 03:04:05 -
@@ -34,9 +34,10 @@
Users of source packages must specially enable the build of
-PL/Perl during the installation process. (Refer to the installation
-instructions for more information.) Users of binary packages
-might find PL/Perl in a separate subpackage.
+PL/Perl during the installation process. (Refer to for more information.) Users of
+binary packages might find PL/Perl in a separate subpackage.
+
@@ -54,7 +55,7 @@
The body of the function is ordinary Perl code. Since the body of
the function is treated as a string by
PostgreSQL, it can be specified using
- dollar quoting (as shown above), or via the usual single quote
+ dollar quoting (as shown above), or via the legacy single quote
syntax (see for more
information).
@@ -79,19 +80,22 @@
- If an SQL null valuenull valuein PL/Perl is passed to a function,
- the argument value will appear as undefined in Perl. The
- above function definition will not behave very nicely with null
- inputs (in fact, it will act as though they are zeroes). We could
- add STRICT to the function definition to make
- PostgreSQL do something more reasonable:
- if a null value is passed, the function will not be called at all,
- but will just return a null result automatically. Alternatively,
- we could check for undefined inputs in the function body. For
- example, suppose that we wanted perl_max with
- one null and one non-null argument to return the non-null argument,
- rather than a null value:
+ If an SQL NULL valuenull
+valuein PL/Perl is
+passed to a function, the argument value will appear as
+undefined in Perl. The above function definition will not
+behave very nicely with NULL inputs (in fact, it
+will act as though they are zeroes). We could add STRICT
+to the function definition to make
+PostgreSQL do something more reasonable: if
+a NULL value is passed, the function will not be
+called at all, but will just return a NULL result
+automatically. Alternatively, we could check for undefined inputs in
+the function body. For example, suppose that we wanted
+perl_max with one NULL and one
+non-NULL argument to return the
+non-NULL argument, rather than a
+NULL value:
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
@@ -108,9 +112,9 @@
- As shown above, to return an SQL null value from a PL/Perl
- function, return an undefined value. This can be done whether the
- function is strict or not.
+As shown above, to return an SQL NULL value from
+a PL/Perl function, return an undefined value. This can be done
+whether the function is strict or not.
@@ -127,7 +131,7 @@
CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
my ($emp) = @_;
-return $emp->{'basesalary'} + $emp->{'bonus'};
+return $emp->{basesalary} + $emp->{bonus};
$$ LANGUAGE plperl;
SELECT name, empcomp(employee) FROM employee;
@@ -135,35 +139,9 @@
- There is currently no support for returning a composite-type result
- value.
+ There is now support for returning a composite-type result value.
-
-
-Because the function body is passed as an SQL string literal to
-CREATE FUNCTION, you have to use dollar quoting
-or escape single quotes and backslashes within your Perl source,
-typically by doubling them. Another possible approach is to avoid
-writing single quotes by using Perl's extended quoting operators
-(q[], qq[],
-qw[]).
-
-
-
-
-
- Data Values in PL/Perl
-
-
- The argument values supplied to a PL/Perl function's code are
- simply the input arguments converted to text form (just as if they
- had been displayed by a SELECT statement).
- Conversely, the return command will accept any string
- that is acceptable input format for the function's declared return
- type. So, the PL/Perl programmer can manipulate data values as if
- they were just text.
-
@@ -171,25 +149,77 @@
Access to the database itself from your Perl function can be done via
- an experimental module http://www.cpan.org/modules/by-module/DBD/APILOS/";>DBD::PgSPI
(also available at http://www.cpan.org/SITES.html";>CPAN
- mirror sites). This module makes available a
+
Re: [PATCHES] logfile subprocess and Fancy File Functions
Andreas Pflug wrote: > Updated patch which leaves postmaster runnable after the syslogger > terminated due to pipe problems. Very nice. You did a nice trick of reading the log filenames into a timestamp field: count = sscanf(de->d_name, "%04d-%02d-%02d_%02d%02d%02d_%05d.log", &yea$ You only process files that match that pattern for pg_logfiles_ls() (perhaps this should be pg_logdir_ls for consistency). And you can then process the timestamp field in queries. Good idea. What happens if a filename matches the above pattern but isn't a valid timestamp? Does the function fail? My only question is whether we need to allow a custom prefix for the log filenames so they can be distinguished from other file names in a user-supplied log directory, like /var/log, or would they always go into a separate directory under there. I think a prefix would be nice. Of course this needs docs but I assume you are waiting to see it applied first. -- 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: [PATCHES] Show tablespace name in pg_tables and pg_indexes
[ Previous patch discarded.] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Klaus Naumann wrote: > On Mon, 12 Jul 2004, Klaus Naumann wrote: > > Hi, > > sorry, the last patch is buggy which didn't show up in the tests :( > Two LEFTs were missing - new patch is attached. > > Greetings, Klaus > > > > > > Hi, > > > > the attached patch shows the new column "tablespace" in the mentioned > > views. > > Apply with > > > > ~/pgsql$ patch -p1 < 03_showtblspc.diff > > > > Greetings, Klaus > > > > > > -- > Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany) > Phone / FAX : ++49/177/7862964 | E-Mail: ([EMAIL PROTECTED]) Content-Description: [ Attachment, skipping... ] > > ---(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 -- 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 8: explain analyze is your friend
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
Does anyone have opinions on including this in 7.5? I see it first appeared on July 6, six days after feature freeze. --- David F. Skoll wrote: > Attached is a patch against pg_dump version 7.4.3 that permits > multiple "-t" switches so that you can select more than one table (but > less than all) to dump. > > It also adds a "-T" switch (long name "--exclude-table") that says > *not* to dump a specific table. So: > > pg_dump -t table1 -t table2 db > > will dump table1 and table2 only, whereas: > > pg_dump -T table1 -T table2 db > > will dump all the tables *except* table1 and table2. > > Tested briefly on my system; doesn't seem to break anything. > > Regards, > > David. > > -- > David F. Skoll <[EMAIL PROTECTED]>Roaring Penguin Software Inc. > +1 (613) 231-6599 ext. 100 http://www.roaringpenguin.com/ > For CanIt technical support, please mail: [EMAIL PROTECTED] Content-Description: [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- 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: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
I see one vote in favor of its inclusion on the grounds it is a bug not to support multiple -t parameters. However, is someone objects I will have to hold it for 7.6. It needs SGML doc additions which I will do myself. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- David F. Skoll wrote: > Attached is a patch against pg_dump version 7.4.3 that permits > multiple "-t" switches so that you can select more than one table (but > less than all) to dump. > > It also adds a "-T" switch (long name "--exclude-table") that says > *not* to dump a specific table. So: > > pg_dump -t table1 -t table2 db > > will dump table1 and table2 only, whereas: > > pg_dump -T table1 -T table2 db > > will dump all the tables *except* table1 and table2. > > Tested briefly on my system; doesn't seem to break anything. > > Regards, > > David. > > -- > David F. Skoll <[EMAIL PROTECTED]>Roaring Penguin Software Inc. > +1 (613) 231-6599 ext. 100 http://www.roaringpenguin.com/ > For CanIt technical support, please mail: [EMAIL PROTECTED] Content-Description: [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org -- 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 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
I see one vote in favor of its inclusion on the grounds it is a bug not to support multiple -t parameters. However, is someone objects I will have to hold it for 7.6. It needs SGML doc additions which I will do myself. Well, I guess I'm against it based on the rules of feature freeze, even though it would be really useful for me :( I don't see how it's a "bug" to not support multiple parameters thought - that's really scraping the bottom of the barrel... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Well, I guess I'm against it based on the rules of feature freeze, > even though it would be really useful for me :( It would have been a lot easier to approve it if it'd arrived on June 30 rather than July 6 :-(. However, I do believe that David originally submitted a slightly-too-late version of this in the previous release cycle, so maybe we could cut him a little slack and pretend this is a mistakenly-forgotten patch that we held over from 7.4. Note I haven't actually *read* the patch and so take no position on whether it does what it claims to. But if someone else will read/test it and give it a favorable report, then I'm inclined to approve it. I'm quite sure we'd agreed in principle to allow multiple -t values. (A negative -T switch is another matter --- that part maybe needs more discussion.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
Well, I guess I'm against it based on the rules of feature freeze, even though it would be really useful for me :( It would have been a lot easier to approve it if it'd arrived on June 30 rather than July 6 :-(. However, I do believe that David originally submitted a slightly-too-late version of this in the previous release cycle, so maybe we could cut him a little slack and pretend this is a mistakenly-forgotten patch that we held over from 7.4. Yes, the reason it would be nice for me is that currently if you want to dump two specific, related tables from your db, there's no way to do it with pg_dump within the one transactions (ie. maintaining integrity). I guess I'm in favour of -t -t but not -T depending on the complexity of it. I'll review the patch if you like. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Patch for pg_dump: Multiple -t options and new -T option
Yes, the reason it would be nice for me is that currently if you want to dump two specific, related tables from your db, there's no way to do it with pg_dump within the one transactions (ie. maintaining integrity). I guess I'm in favour of -t -t but not -T depending on the complexity of it. I'll review the patch if you like. One problem with this patch is that there's no way to dump multiple tables in different schemas. Does this matter? It's a bit non-orthogonal... Chris ---(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
