Thread: PGXLOG variable worthwhile?
Hi everyone, Am just wondering if we've ever considered adding a PGXLOG environment variable that would point to the pg_xlog directory? In a Unix environment it's not real necessary as filesystem links can be created, but in other environments (i.e. the Native windows port) it's looking like it might be useful. :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
We dealt this this (painfully) during 7.3 development. Some wanted a -X flag to initdb/postgres/postmaster that would identify the pg_xlog directory while others wanted the flag only on initdb and have initdb create a symlink. Finally, we decided to do nothing. and continue to recommend manually moving pg_xlog using symlinks. Also, I have heard symlinks are available in native Windows but the interface to them isn't clearly visible. Can someone clarify that? --------------------------------------------------------------------------- Justin Clift wrote: > Hi everyone, > > Am just wondering if we've ever considered adding a PGXLOG environment > variable that would point to the pg_xlog directory? > > In a Unix environment it's not real necessary as filesystem links can be > created, but in other environments (i.e. the Native windows port) it's > looking like it might be useful. > > :-) > > Regards and best wishes, > > Justin Clift > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: 12 September 2002 06:27 > To: Justin Clift > Cc: PostgreSQL Hackers Mailing List > Subject: Re: [HACKERS] PGXLOG variable worthwhile? > > Also, I have heard symlinks are available in native Windows > but the interface to them isn't clearly visible. Can someone > clarify that? Well there are 'shortcuts' but I wouldn't want to trust my xlog directory to one. Even if I did, iirc, unless you are using the shell api, they just appear to be regular files anyway (for example, in Cygwin vi, I can edit a shortcut to a directory). Regards, Dave.
Dave Page wrote: > >>-----Original Message----- >>From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] >> >>Also, I have heard symlinks are available in native Windows >>but the interface to them isn't clearly visible. Can someone >>clarify that? > > > Well there are 'shortcuts' but I wouldn't want to trust my xlog > directory to one. These are Shell OLE links. As Dave points out, it requires the shell to interpret the shortcut. > > Even if I did, iirc, unless you are using the shell api, they just > appear to be regular files anyway (for example, in Cygwin vi, I can edit > a shortcut to a directory). > > Regards, Dave. In Windows 2000 and Windows XP with an NTFS filesystem, Microsoft has added Reparse Points, which allow for the implementation of symbolic links for directories. Microsoft calls them "Junctions". I *believe* the function used for creating reparse points is DeviceIoControl() with the FSCTL_SET_REPARSE_POINT I/O control code. I don't have quick access to 2K or XP, but it is clearly not supported by Win32 on 95/98/ME. Here's a link discussing the features of NTFS5 and Reparse Points: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnw2kmag00/html/NTFSPart1.asp Mike Mascari mascarm@mascari.com
Mike Mascari wrote: <snip> > In Windows 2000 and Windows XP with an NTFS filesystem, > Microsoft has added Reparse Points, which allow for the > implementation of symbolic links for directories. Microsoft > calls them "Junctions". I *believe* the function used for > creating reparse points is DeviceIoControl() with the > FSCTL_SET_REPARSE_POINT I/O control code. I don't have quick > access to 2K or XP, but it is clearly not supported by Win32 on > 95/98/ME. > > Here's a link discussing the features of NTFS5 and Reparse Points: > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnw2kmag00/html/NTFSPart1.asp That's really useful info. Reparse points under Win2k (mount points to the rest of us) are definitely something to try out in the future then. :) Seems like the NT4 users are left out in the cold though until we add some kind of ability for PostgreSQL to not look at the filesystem for info about where to put the xlog files. Regards and best wishes, Justin Clift > Mike Mascari > mascarm@mascari.com -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Thu, 12 Sep 2002, Justin Clift wrote: > Mike Mascari wrote: > <snip> > > In Windows 2000 and Windows XP with an NTFS filesystem, > > Microsoft has added Reparse Points, which allow for the > > implementation of symbolic links for directories. Microsoft > > calls them "Junctions". I *believe* the function used for > > creating reparse points is DeviceIoControl() with the > > FSCTL_SET_REPARSE_POINT I/O control code. I don't have quick > > access to 2K or XP, but it is clearly not supported by Win32 on > > 95/98/ME. > > > > Here's a link discussing the features of NTFS5 and Reparse Points: > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnw2kmag00/html/NTFSPart1.asp > > That's really useful info. Reparse points under Win2k (mount points to > the rest of us) are definitely something to try out in the future then. > :) > > Seems like the NT4 users are left out in the cold though until we add > some kind of ability for PostgreSQL to not look at the filesystem for > info about where to put the xlog files. This isn't true. With the resource kit, you get the gnu utils, and ln works a charm under NT4 with ntfs. And not just for directories, but files as well. Unless Microsoft somehow removed that functionality in the intervening years since I've used NT. (wouldn't put it past them, but I doubt they have.)
"scott.marlowe" wrote: <snip> > > Seems like the NT4 users are left out in the cold though until we add > > some kind of ability for PostgreSQL to not look at the filesystem for > > info about where to put the xlog files. > > This isn't true. With the resource kit, you get the gnu utils, and ln > works a charm under NT4 with ntfs. And not just for directories, but > files as well. Unless Microsoft somehow removed that functionality in the > intervening years since I've used NT. (wouldn't put it past them, but I > doubt they have.) The reference point that I'm working from is this: - Am testing out the third beta of the Native PostgreSQL port for Windows, on NT4 SP6 at present.- Have an internal RAID array of Seagate Cheetah 10kRPM drives. When installing the PGDATA directory on one drive it gives a certain kind of performance, and I'm interested in testing the performance of the Native PostgreSQL port for Windows with the xlog directory being located on another drive.- Have tried doing normal shortcuts, and have also tried using the cygwin "ln" command to create the appropriate soft link. Both approaches create a shortcut object of the correct name pointing to the correct place on the new drive, but the only thing that appears to follow this shortcut is when I click on them using Windows Explorer. The Native PostgreSQL port for Windows doesn't, and neither do a few other applications I tested. Would it be correct to say that the 'ln' command in the MS Resource Kit creates this kind of shortcut too, as the Reparse Points feature doesn't seem to be possible under NT4? Can only think of two real solutions at present, one being for us to add a PGXLOG environment variable or similar ability (GUC parameter perhaps?), and the other would be for the Native PostgreSQL for Windows port to follow these shortcuts. Not if any of these is all that easy, or maybe there is another solution that would work (apart from ignoring the problem). :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
scott.marlowe wrote: > > Seems like the NT4 users are left out in the cold though until we add > > some kind of ability for PostgreSQL to not look at the filesystem for > > info about where to put the xlog files. > > This isn't true. With the resource kit, you get the gnu utils, and ln > works a charm under NT4 with ntfs. And not just for directories, but > files as well. Unless Microsoft somehow removed that functionality in the > intervening years since I've used NT. (wouldn't put it past them, but I > doubt they have.) Yes, this is what I remember, that Cygwin had symlinks, and at that time that was the only Win32 OS we supported. Now, with native Win32 port coming, we have to figure out what is available. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Fri, 13 Sep 2002, Justin Clift wrote: > "scott.marlowe" wrote: > <snip> > > > Seems like the NT4 users are left out in the cold though until we add > > > some kind of ability for PostgreSQL to not look at the filesystem for > > > info about where to put the xlog files. > > > > This isn't true. With the resource kit, you get the gnu utils, and ln > > works a charm under NT4 with ntfs. And not just for directories, but > > files as well. Unless Microsoft somehow removed that functionality in the > > intervening years since I've used NT. (wouldn't put it past them, but I > > doubt they have.) > > The reference point that I'm working from is this: > > - Am testing out the third beta of the Native PostgreSQL port for > Windows, on NT4 SP6 at present. > - Have an internal RAID array of Seagate Cheetah 10kRPM drives. When > installing the PGDATA directory on one drive it gives a certain kind of > performance, and I'm interested in testing the performance of the Native > PostgreSQL port for Windows with the xlog directory being located on > another drive. > - Have tried doing normal shortcuts, and have also tried using the > cygwin "ln" command to create the appropriate soft link. Both > approaches create a shortcut object of the correct name pointing to the > correct place on the new drive, but the only thing that appears to > follow this shortcut is when I click on them using Windows Explorer. > The Native PostgreSQL port for Windows doesn't, and neither do a few > other applications I tested. > > Would it be correct to say that the 'ln' command in the MS Resource Kit > creates this kind of shortcut too, as the Reparse Points feature doesn't > seem to be possible under NT4? I wouldn't assume that. It's been years since I tested it, but back then, the command line and all program I used could see the link created by ln that came with the resource kit. They were distinctly different from the shortcut type of links, in that they seems transparent like short cuts in unix generally are. Do you have the resource kit or the gnu utils from it? Looking at this url: http://unxutils.sourceforge.net/ the part for ln.exe says it makes real hard links on ntfs (which means they would be on the same drive.) So I'm not sure if ntfs supports soft links across volumes transparently or not now.
On Thu, 12 Sep 2002, Justin Clift wrote: > Am just wondering if we've ever considered adding a PGXLOG environment > variable that would point to the pg_xlog directory? IMHO, a much better way to support this is to put this information into the config file. That way it can't easily change when you happen to, say, start postgres in the wrong window. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
scott.marlowe wrote: > On Fri, 13 Sep 2002, Justin Clift wrote:> >>Would it be correct to say that the 'ln' command in the MS Resource Kit >>creates this kind of shortcut too, as the Reparse Points feature doesn't >>seem to be possible under NT4? > > > I wouldn't assume that. It's been years since I tested it, but back then, > the command line and all program I used could see the link created by ln > that came with the resource kit. They were distinctly different from the > shortcut type of links, in that they seems transparent like short cuts in > unix generally are. > > Do you have the resource kit or the gnu utils from it? The situation appears to be this: 1. Soft links are available on NTFS 5 (2K/XP) as Reparse Points via the DeviceIoControl() function for any application using the standard C library routines. 2. Soft links are available on any filesystem under 95/98/ME/NT4/2K/XP as OLE streams (.lnk files) for Shell-aware applications. 3. Hard links are available on NTFS 5 (2K/XP) via the CreateHardLink() API. See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/createhardlink.asp 4. Hard links are available on NTFS (NT3.1/NT4) via the BackupWrite() API by writing a special stream to the NTFS. Example: http://www.mvps.org/win32/ntfs/lnw.cpp The cygwin implementation of link(): http://sources.redhat.com/cgi-bin/cvsweb.cgi/src/winsup/cygwin/syscalls.cc?rev=1.149.2.23&content-type=text/x-cvsweb-markup&cvsroot=src 1. Will use CreateHardLink() if on 2K/XP 2. Will try to use the BackupWrite() method 3. Failing #2 will just copy the file See how fun Microsoft makes things? Mike Mascari mascarm@mascari.com
I wrote: > scott.marlowe wrote:>> >> I wouldn't assume that. It's been years since I tested it, but back >> then, the command line and all program I used could see the link >> created by ln that came with the resource kit. They were distinctly >> different from the shortcut type of links, in that they seems >> transparent like short cuts in unix generally are. >> >> Do you have the resource kit or the gnu utils from it? > > > The situation appears to be this: > > 1. Soft links are available on NTFS 5 (2K/XP) as Reparse Points via the > DeviceIoControl() function for any application using the standard C > library routines. > > 2. Soft links are available on any filesystem under 95/98/ME/NT4/2K/XP > as OLE streams (.lnk files) for Shell-aware applications. > > 3. Hard links are available on NTFS 5 (2K/XP) via the CreateHardLink() API. <snip> > 4. Hard links are available on NTFS (NT3.1/NT4) via the BackupWrite() > API by writing a special stream to the NTFS. I also believe (I could be wrong) that for directories, the only two methods of links are the Soft link methods above. So PGXLOG cannot use soft links on a non-XP/2K machine unless it is "Shell-Aware". For example, in a cygwin bash command window: mkdir dir1 ln dir1 dir2 <- Error using Cygwin implementation ln -s dir1 dir2 <- Creates a Shell short-cut (NT4) echo "Hello" > dir1/test.txt cat dir2/test.txt "Hello" <- Cygwin's cat(bash?) is shell short-cut aware Now, in a Windows NT command prompt: notepad dir2\test.txt <- Notepad can't find file notepad dir2.lnk <- Displays link contents That means for a native port with a different PGXLOG directory running on NT4, the only choice *using links* is to make the native port shell short-cut aware. I could be wrong but I don't think so. Mike Mascari mascarm@mascari.com
Curt Sampson <cjs@cynic.net> writes:
> On Thu, 12 Sep 2002, Justin Clift wrote:
>> Am just wondering if we've ever considered adding a PGXLOG environment
>> variable that would point to the pg_xlog directory?
> IMHO, a much better way to support this is to put this information into
> the config file. That way it can't easily change when you happen to, say,
> start postgres in the wrong window.
Yes. We rejected environment-variable-based xlog location for reasons
that apply equally well to Windows. The xlog location *must* be stored
in a physical file in the data directory; anything else is too unsafe.
The current technology for that is a symlink.
While it doesn't have to be a symlink as opposed to some sort of config
file, I don't have the slightest problem with saying that we don't
support relocation of xlog on older Windoid platforms.
regards, tom lane
Tom Lane wrote: > Curt Sampson <cjs@cynic.net> writes: > > On Thu, 12 Sep 2002, Justin Clift wrote: > >> Am just wondering if we've ever considered adding a PGXLOG environment > >> variable that would point to the pg_xlog directory? > > > IMHO, a much better way to support this is to put this information into > > the config file. That way it can't easily change when you happen to, say, > > start postgres in the wrong window. > > Yes. We rejected environment-variable-based xlog location for reasons > that apply equally well to Windows. The xlog location *must* be stored > in a physical file in the data directory; anything else is too unsafe. > The current technology for that is a symlink. > > While it doesn't have to be a symlink as opposed to some sort of config > file, I don't have the slightest problem with saying that we don't > support relocation of xlog on older Windoid platforms. Agreed. Win 4.X is pretty dead. I added this thread to TODO.detail. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > > Tom Lane wrote: <snip> > > While it doesn't have to be a symlink as opposed to some sort of config > > file, I don't have the slightest problem with saying that we don't > > support relocation of xlog on older Windoid platforms. > > Agreed. Win 4.X is pretty dead. I added this thread to TODO.detail. Huh? You've got to be joking. Many of the *really large* enterprises around (i.e. with 40k+ PC's, etc) are still running WinNT 4, due to the migration issues with upgrading. Aka, Too Many Things Break when they move to Win2k, etc. Although MS no longer considers WinNT 4.0 to be a supported platform, there are *lots* of big places still running it. That's part of the reason some of the bigger corporates are looking for MS alternatives. Regards and best wishes, Justin Clift > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Justin Clift wrote: > Bruce Momjian wrote: > > > > Tom Lane wrote: > <snip> > > > While it doesn't have to be a symlink as opposed to some sort of config > > > file, I don't have the slightest problem with saying that we don't > > > support relocation of xlog on older Windoid platforms. > > > > Agreed. Win 4.X is pretty dead. I added this thread to TODO.detail. > > Huh? You've got to be joking. > > Many of the *really large* enterprises around (i.e. with 40k+ PC's, etc) > are still running WinNT 4, due to the migration issues with upgrading. > Aka, Too Many Things Break when they move to Win2k, etc. > > Although MS no longer considers WinNT 4.0 to be a supported platform, > there are *lots* of big places still running it. > > That's part of the reason some of the bigger corporates are looking for > MS alternatives. Oh, that is bad news. Well, can we accept they will not be moving XLOG around? The problem with the non-symlink solution is that it is error-prone/ugly on all the platforms, not just NT4.X. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: <snip> > Oh, that is bad news. Well, can we accept they will not be moving XLOG > around? > > The problem with the non-symlink solution is that it is error-prone/ugly > on all the platforms, not just NT4.X. What you guys are saying isn't necessarily wrong, in that it may not definitely be very pretty. However, moving the WAL files to another disk has a significant performance gain attached to it for loaded servers, so we how about we take the viewpoint that if WinNT/2k/XP are to be supported then we might as well let it do things properly instead of handicapping it? Does anyone care to estimate what the coding time+issues involved would be, for adding a parameter to the postgresql.conf file that allows PostgreSQL to directly use a different directory path for the WAL files? 'wal_path' or 'wal_directory' or similar. In the postgresql.conf it would probably be placed in the 'Write-ahead log (WAL)' or 'Misc' sections. No guarantees just yet but if it's not an extremely expensive thing to add, then there might be people willing to pay for it (have a group in mind already). :-) Regards and best wishes, Justin Clift > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Justin Clift <justin@postgresql.org> writes:
> However, moving the WAL files to another disk has a significant
> performance gain attached to it for loaded servers, so we how about we
> take the viewpoint that if WinNT/2k/XP are to be supported then we might
> as well let it do things properly instead of handicapping it?
Considering that we do not yet have support for WinAnything except via
cygwin, this thread strikes me as mighty premature.
And, to be blunt, I'm not likely to go out of my way to improve support
for WinAnything even when we do have a native port. In words of one
syllable: WinAnything is not, and never will be, a preferred platform
for Postgres. Accordingly, performance improvements for it are just a
distraction from our real business; a distraction which plays into the
hands of Gates & Co. No thank you. I'm okay with providing minimal
support for those who really want to run toy databases on a toy
platform. I will *not* buy into trying to make it a non-toy platform.
regards, tom lane
On Sun, 15 Sep 2002, Bruce Momjian wrote: > The problem with the non-symlink solution is that it is error-prone/ugly > on all the platforms, not just NT4.X. Actually, it's really just the environment variable solution that's error prone, I think. Putting it in the config file is fine. It's just a matter of someone coding it. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
Tom Lane wrote: <snip> > And, to be blunt, I'm not likely to go out of my way to improve support > for WinAnything even when we do have a native port. In words of one > syllable: WinAnything is not, and never will be, a preferred platform > for Postgres. Accordingly, performance improvements for it are just a > distraction from our real business; a distraction which plays into the > hands of Gates & Co. No thank you. I'm okay with providing minimal > support for those who really want to run toy databases on a toy > platform. I will *not* buy into trying to make it a non-toy platform. Understood, and that's ok. Allowing PostgreSQL to be productively used as best it can be, whereever it can be, makes sense doesn't it? Especially when the real target here would be to give existing MS places a lower cost of entry to the PostgreSQL world. Financial example : WinNT/2k/XP costs a few hundred dollars. MS SQL Server costs a few thousand dollars. Whenever we displace MS SQL Server, we divert more revenue away from MS than if we just say "Sorry but we're not happy with making the Windows port perform in ways that let it compete adequately with MS SQL Server". We both know the arguments for and against. You're in the "against" camp, and I'm in the "for" camp. Personally I'm hoping there are some other PostgreSQL coders around in the "for" camp too that can assist with this as we're beginning to gain some good public enterprise level of interest. :-) Regards and best wishes, Justin Clift > > regards, tom lane -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Justin Clift wrote: > > Bruce Momjian wrote: > <snip> > > Oh, that is bad news. Well, can we accept they will not be moving XLOG > > around? > > > > The problem with the non-symlink solution is that it is error-prone/ugly > > on all the platforms, not just NT4.X. > > What you guys are saying isn't necessarily wrong, in that it may not > definitely be very pretty. > > However, moving the WAL files to another disk has a significant > performance gain attached to it for loaded servers, so we how about we > take the viewpoint that if WinNT/2k/XP are to be supported then we might > as well let it do things properly instead of handicapping it? I just don't see why that all could become an issue. Someone running big stuff on NT4 today is not running a native PostgreSQL port on it. Why would someone want to do a new, big, PG installation on an old, unsupported NT4 server today? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: <snip> > > I just don't see why that all could become an issue. Someone > running big stuff on NT4 today is not running a native PostgreSQL > port on it. Why would someone want to do a new, big, PG > installation on an old, unsupported NT4 server today? Corporate Standards. Even if everyone *knows* that NT4 isn't the latest and greatest, many large companies still use NT4. Purely because so much stuff they use works with it that they haven't been able to generate sufficient business cases to migrate their base server OS to Win2K (or XP). If this would be a really huge and drastic modification then sure it's not necessarily an easy thing to decide. But the first thing to consider is "how much effort would be required?". :-) Regards and best wishes, Justin Clift > Jan > > -- > #======================================================================# > # It's easier to get forgiveness for being wrong than for being > right. # > # Let's break this rule - forgive > me. # > #================================================== > JanWieck@Yahoo.com # -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Justin Clift writes: > WinNT/2k/XP costs a few hundred dollars. > > MS SQL Server costs a few thousand dollars. The places that run Windows can be categorized into three camps: (1) Those that don't have a clue. They will never run PostgreSQL. (2) Those that are somehow afraid to switch to a different solution. They will be even more hesitant to switch to PostgreSQL. (3) Those that somehow like Windows. They will like MS SQL Server as well, no matter what we do. So where is the market? -- Peter Eisentraut peter_e@gmx.net
Justin Clift wrote: > > Jan Wieck wrote: > <snip> > > > > I just don't see why that all could become an issue. Someone > > running big stuff on NT4 today is not running a native PostgreSQL > > port on it. Why would someone want to do a new, big, PG > > installation on an old, unsupported NT4 server today? > > Corporate Standards. Even if everyone *knows* that NT4 isn't the latest > and greatest, many large companies still use NT4. Purely because so > much stuff they use works with it that they haven't been able to > generate sufficient business cases to migrate their base server OS to > Win2K (or XP). The word construct "corporate standard" is the most expensive and dangerous form of ignorance I've seen in the business. One of the best examples I've seen actually fit's very well. An SAP customer converting from R/2 to R/3 a couple years ago. They ran all their non-mainframe business on HP3000 MPE/IX systems. We strongly recommended using HP/UX for the SAP installation instead, but they followed their "corporate ignorance" anyway. Two weeks before going life SAP informed all their MPE customers that support for that operating system will be abandoned and strongly recommended converting to HP/UX soon because within a few months not even hotfixes will be provided any more. Outch! If corporate standard means similar letter heads, similar appearance of public offices or advertising, absolutely a good thing and I'm all for it. But if it causes to get stuck with old technology, then the corporate standard itself is the problem that needs to be fixed first. But ... let's put it into the damned config file and move on. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Peter Eisentraut wrote: > > Justin Clift writes: > > > WinNT/2k/XP costs a few hundred dollars. > > > > MS SQL Server costs a few thousand dollars. > > The places that run Windows can be categorized into three camps: <snip> How about this? The places that run Windows can be categorised a number of different ways, depending on what you're looking for. 1) Places that have in-house staff that can do or learn everything. Many of these places are really small, some are not. PostgreSQL fits well here, Windows or not, as these people are prepared to learn how to use it best. 2) Companies that hire external IT services. Often the software implemented here will be dependent on outside sources of advice such as consultants, executives who take an interest in IT mags, etc. Look at Windows NT on the server in the first place. Microsoft leveraged the marketplace through making itself available then promoting the heck out of itself into the IT press, industry mags, etc. These places will be receptive to PostgreSQL as our reputation further becomes known and they can see where PostgreSQL will be useful to them. PostgreSQL on Win NT/2K/XP will definitely be of use to a sizable number of these businesses. 3) Companies who depend on multiple external sources of IT support. i.e. One reasonable sized enterprise here in Australia has over 450 *development* companies presently working on applications for their environment. Because of the scope of standardisation needed, they standardised on WinNT many years ago. It still works for them. They don't even have SP6 installed on their desktops as it breaks too many of the desktop applications. etc. These people are not clueless. They make strategic decisions when they're necessary, and it all comes down to flexibility, reliability, and cost. For some things they run Unix, or Windows, or Novell, or OS/390, or any number of other stuff. Because of the years of experience some of their support companies have with WinNT, it works reliably enough for them. They don't have the "need to reboot once per week" thing with their servers. These guys will become receptive to PostgreSQL too, and it will be in our favour to be able to demonstrate very good performance across all platforms that we can, not just our own *personally preferred* platforms. By giving them options when it doesn't take a *whole bunch of effort* to do so, we open up ways for PostgreSQL to be used that we haven't even thought of before. We all know this already. It wouldn't really surprise me greatly if at some point this proved beneficial to a non-Windows platform for some reason too. :-) Regards and best wishes, Justin Clift > So where is the market? > > -- > Peter Eisentraut peter_e@gmx.net -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Mon, 16 Sep 2002, Peter Eisentraut wrote: > Justin Clift writes: > > > WinNT/2k/XP costs a few hundred dollars. > > > > MS SQL Server costs a few thousand dollars. > > The places that run Windows can be categorized into three camps: (1) > Those that don't have a clue. They will never run PostgreSQL. (2) Those > that are somehow afraid to switch to a different solution. They will be > even more hesitant to switch to PostgreSQL. (3) Those that somehow like > Windows. They will like MS SQL Server as well, no matter what we do. I would say the only real growth market is "Those who have a clue, and are looking at migrating off of Windows / MSSQL to a different database." In the case of my company, that's mostly resulted in Postgresql deployed on Linux and Solaris. But I can see a use for Postgresql on Windows. However, for us, all our serious Windows servers have long since been converted to Win2K. For all those situations, I can't imagine the database getting big enough and hit hard enough for pg_xlog to be a problem before it gets moved to a real OS. So, by the time someone is deciding to dedicate themselves to running Postgresql, they've probably already decided they should run it on some flavor of Unix, or the slower performance of Postgresql under Windows is no great detriment. Supporting a sane OS like Unix is hard enough, creating more work for the core developers in trying to work around a broken file system on Windows is not the best use of the resources available. If and when someone running postgresql on Windows decides they REALLY need to move the pg_xlog somewhere else, they can either code it, or move to Linux. I'd recommend moving to Linux.
> The places that run Windows can be categorized into three camps: (1) > Those that don't have a clue. They will never run PostgreSQL. (2) Those > that are somehow afraid to switch to a different solution. They will be > even more hesitant to switch to PostgreSQL. (3) Those that somehow like > Windows. They will like MS SQL Server as well, no matter what we do. > > So where is the market? Ask MySQL - they have many, many Windows users. Chris
Robert Treat wrote: > It seems all of this discussion misses the point. Either it has a large > amount of impact and the idea gets rejected because of implementation > issues, or it has little impact but it's nothing the core group wants to > implement. If the problem is finding someone to implement it, it sounds > like Justin has found such a person, so are we going to stand in his way > while we wax poetic about OS religion and corporate philosophies or can > he start submitting patches? Actually, the work is minimal. Look at the commit I used to remove PGXLOG, trim that to remove the changes to make the path name dynamic in size (added too much complexity for little benefit) and hang the path coding off a GUC variable rather than an environment variable. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> > It seems all of this discussion misses the point. Either it has a large > > amount of impact and the idea gets rejected because of implementation > > issues, or it has little impact but it's nothing the core group wants to > > implement. If the problem is finding someone to implement it, it sounds > > like Justin has found such a person, so are we going to stand in his way > > while we wax poetic about OS religion and corporate philosophies or can > > he start submitting patches? > > Actually, the work is minimal. Look at the commit I used to remove > PGXLOG, trim that to remove the changes to make the path name dynamic in > size (added too much complexity for little benefit) and hang the path > coding off a GUC variable rather than an environment variable. I personally don't see the problem with a GUC variable...that seems like the perfect solution to me... Chris
Christopher Kings-Lynne wrote: > > > It seems all of this discussion misses the point. Either it has a large > > > amount of impact and the idea gets rejected because of implementation > > > issues, or it has little impact but it's nothing the core group wants to > > > implement. If the problem is finding someone to implement it, it sounds > > > like Justin has found such a person, so are we going to stand in his way > > > while we wax poetic about OS religion and corporate philosophies or can > > > he start submitting patches? > > > > Actually, the work is minimal. Look at the commit I used to remove > > PGXLOG, trim that to remove the changes to make the path name dynamic in > > size (added too much complexity for little benefit) and hang the path > > coding off a GUC variable rather than an environment variable. > > I personally don't see the problem with a GUC variable...that seems like the > perfect solution to me... Well, let's see if we ever run on native NT4.X and we can decide then. Actually, don't our Cygnus folks have a problem with moving pg_xlog already? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: 17 September 2002 06:36 > To: Christopher Kings-Lynne > Cc: Robert Treat; Justin Clift; Peter Eisentraut; Tom Lane; > Curt Sampson; PostgreSQL Hackers Mailing List > Subject: Re: [HACKERS] PGXLOG variable worthwhile? > > > Well, let's see if we ever run on native NT4.X and we can > decide then. > Actually, don't our Cygnus folks have a problem with moving > pg_xlog already? No, because Cygwin knows about shell links. Whilst I'm here, I'll chuck my $0.02 in: I use PostgreSQL on Linux for production and XP for development, and am likely to continue that way. I've been beta testing the native Win32 port of PostgreSQL as Justin has and the latest version is fantastic - it runs as a service, osdb shows impressive results compared to Cygwin PostgreSQL on the same system and it's a breeze to install, despite there being no installer yet. What I can't understand is the attitude of some people here. Yes, Microsoft are evil, but the bottom line is, millions of people use Windows. Just look at the number of downloads for pgAdmin (shown at http://www.pgadmin.org/downloads/) - the last stable version has clocked up over 38,000 downloads, the preview I released just a couple of weeks ago, 2230 at the time of writing. I know from talking to some of the users that often people download copies for themselves and their colleagues, so we can probably assume there are actually 40,000+ PostgreSQL users that use Windows reguarly enough to want pgAdmin. What happens if you add in the pgAccess/Windows users, Tora, or pgExplorer? How many of these people would want to run PostgreSQL on Windows as well? What about the companies out there that have good sysadmins who want to use PostgreSQL, but manglement that insist on using Windows? What about situations where a single server is running SQL Server and other software (such as a middle tier server - as I have on one box here), and that other software cannot be changed, but SQL could? I think that ignoring the huge number of people that use windows because some of us consider it a Mickey Mouse OS is a particuarly bad strategy if we want to expand our userbase. Windows is not going anywhere soon, and like it or not, it *is* getting better and better. Our Windows 2000 (and our Beta3/RC1 .Net test Servers) are rock solid and haven't been rebooted in months) - we get more hardware faults these days, and those can occur on our Linux or HP-UX boxes just as easily. Anyway, enough of my rant :-) Regards, Dave.
> I use PostgreSQL on Linux for production and XP for development, and am > likely to continue that way. I've been beta testing the native Win32 > port of PostgreSQL as Justin has and the latest version is fantastic - > it runs as a service, osdb shows impressive results compared to Cygwin > PostgreSQL on the same system and it's a breeze to install, despite > there being no installer yet. >From where do we get this fabled Win32 port? Chris
> What I can't understand is the attitude of some people here. Yes, > Microsoft are evil, but the bottom line is, millions of people use > Windows. Just look at the number of downloads for pgAdmin (shown at > http://www.pgadmin.org/downloads/) - the last stable version has clocked > up over 38,000 downloads, the preview I released just a couple of weeks > ago, 2230 at the time of writing. I know from talking to some of the > users that often people download copies for themselves and their > colleagues, so we can probably assume there are actually 40,000+ > PostgreSQL users that use Windows reguarly enough to want pgAdmin. What > happens if you add in the pgAccess/Windows users, Tora, or pgExplorer? > How many of these people would want to run PostgreSQL on Windows as > well? I actually think that the long-term survival of Postgres DEPENDS on our Win32 support. Otherwise, we'll just get massacred by MySQL, MSSQL, Oracle and Firebird who do support Win32. Users of Postgres are our lifeblood. The more users we have the more developers we get, the more testing we get and the more likely we are to get money, corporate support, etc. Our ODBC driver will also be improved. Chris
On 17 Sep 2002 at 16:11, Christopher Kings-Lynne wrote: > > What I can't understand is the attitude of some people here. Yes, > > Microsoft are evil, but the bottom line is, millions of people use > > Windows. Just look at the number of downloads for pgAdmin (shown at > > http://www.pgadmin.org/downloads/) - the last stable version has clocked > > up over 38,000 downloads, the preview I released just a couple of weeks > > ago, 2230 at the time of writing. I know from talking to some of the > > users that often people download copies for themselves and their > > colleagues, so we can probably assume there are actually 40,000+ > > PostgreSQL users that use Windows reguarly enough to want pgAdmin. What > > happens if you add in the pgAccess/Windows users, Tora, or pgExplorer? > > How many of these people would want to run PostgreSQL on Windows as > > well? > I actually think that the long-term survival of Postgres DEPENDS on our > Win32 support. Otherwise, we'll just get massacred by MySQL, MSSQL, Oracle > and Firebird who do support Win32. Let's move this to general. But I disagree. History says that nobody can compete with microsoft on microsoft platform. Postgres will not be competing with either SQL Server or access. It would remain as toy database.. As far as people using mysql on windows, I have couple of colleages here who got things crowling for some heavy load, something like 60GB database with 512MB compq workstations.. Let's leave it. The main point to focus postgres on unix is not only because unix is proven/known as robust and scalable, but unix is much more standard to support across multiple OS. The amount with which windows differs from unices on API level, any serious efforts to make postgresql good enough on windows whould be a mammoth task. I haven't tried either port of postgres on windows but I would not bet on any of them. > Users of Postgres are our lifeblood. The more users we have the more I agree but even as of now, not even 1% users comes on any of postgres lists, in my estimate. So if users are not providing their feedback, what's the point in open source? (Actually all those people do help postgres by publicising it but still feedback remains an important phase of open source software engineering..) > developers we get, the more testing we get and the more likely we are to get > money, corporate support, etc. Our ODBC driver will also be improved. I agree for ODBC but that can be done without giving much to postgresql windows port as well. I understand windows port of postgresql remains very much important for people who want to evaluate it. But for some good evaluation, I would rather recommend them trying postgresql on linux rather than windows. There are limits as what postgresql can do on windows and probably postgresql development team can't do much about many of them.. No offense to anybody.. just some opinions.. Bye Shridhar -- Albrecht's Law: Social innovations tend to the level of minimum tolerable well- being.
> -----Original Message----- > From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au] > Sent: 17 September 2002 09:05 > To: Dave Page; Bruce Momjian > Cc: Robert Treat; Justin Clift; Peter Eisentraut; Tom Lane; > Curt Sampson; PostgreSQL Hackers Mailing List > Subject: RE: [HACKERS] PGXLOG variable worthwhile? > > > > I use PostgreSQL on Linux for production and XP for > development, and > > am likely to continue that way. I've been beta testing the native > > Win32 port of PostgreSQL as Justin has and the latest version is > > fantastic - it runs as a service, osdb shows impressive results > > compared to Cygwin PostgreSQL on the same system and it's a > breeze to > > install, despite there being no installer yet. > > >From where do we get this fabled Win32 port? > The call for testers (below) was originally posted to the Cygwin list. Regards, Dave. === My company is actively working on a Native Windows Port of Postgres based on 7.2.1. This is the same group that Jan Wieck and Katie Ward work with. We are now at the stage that we need community involvement to help work out the bugs. We plan on contributing the code to the Postgres base, but we want to make sure that most of the bugs have been worked out before doing so. We are looking for people who have an application that currently runs on Postgres 7.2 and who also have a Windows environment. If you would like to get involved, please send me email at mailto:mikef@multera.com Thanks... ...MikeF -- ------------------------------------------------------------------ Mike Furgal - mailto:mikef@multera.com - http://www.multera.com ------------------------------------------------------------------
> -----Original Message----- > From: Shridhar Daithankar > [mailto:shridhar_daithankar@persistent.co.in] > Sent: 17 September 2002 09:30 > To: Pgsql-hackers@postgresql.org > Cc: Pgsql-general@postgresql.org > Subject: Re: [HACKERS] PGXLOG variable worthwhile? > > > On 17 Sep 2002 at 16:11, Christopher Kings-Lynne wrote: > > But I disagree. History says that nobody can compete with > microsoft on > microsoft platform. Postgres will not be competing with > either SQL Server or > access. It would remain as toy database.. Like Oracle? > Let's leave it. The main point to focus postgres on unix is > not only because > unix is proven/known as robust and scalable, but unix is much > more standard to > support across multiple OS. The amount with which windows > differs from unices > on API level, any serious efforts to make postgresql good > enough on windows > whould be a mammoth task. Maybe, but it's pretty much there now. The beta Win32 native port has been performing excellently in the tests I've been able to throw at it, certainly better than the Cygwin port. > I haven't tried either port of postgres on windows but I > would not bet on any > of them. The thing I wouldn't bet on is not the quality of the code produced by the developers here, but Windows. Yes, it runs great here at the moment, and has done for a while now but there's no guarantee that a new release won't have a nasty bug. But that applies to the SQL user as well though. Or for that matter the user of *any* other OS... > There are limits as what postgresql can do on windows and > probably postgresql > development team can't do much about many of them.. The only real issue afaik with the current beta is that you can only run one instance on a single server. That is the case with SQL Server as well of course. > No offense to anybody.. just some opinions.. > Likewise. Regards, Dave.
> Let's leave it. The main point to focus postgres on unix is not > only because > unix is proven/known as robust and scalable, but unix is much > more standard to > support across multiple OS. The amount with which windows differs > from unices > on API level, any serious efforts to make postgresql good enough > on windows > whould be a mammoth task. It's already been done - that's the whole point. > So if users are not providing their feedback, what's the point in > open source? Users HAVE provided their feedback - they want Postgres on Windows. What's the point of open source if we can't accomodate them? There's no problems with economics, marketing, schedules, deadlines, nothing. The reason that people like Open Source is because they don't have to deal with some monolithic company refusing to port to their platform just because it's "too hard". Chris
> -----Original Message----- > From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au] > Sent: 17 September 2002 09:49 > To: shridhar_daithankar@persistent.co.in; Pgsql-hackers@postgresql.org > Cc: Pgsql-general@postgresql.org > Subject: Re: [HACKERS] PGXLOG variable worthwhile? > > > Users HAVE provided their feedback - they want Postgres on > Windows. What's the point of open source if we can't > accomodate them? There's no problems with economics, > marketing, schedules, deadlines, nothing. The reason that > people like Open Source is because they don't have to deal > with some monolithic company refusing to port to their > platform just because it's "too hard". Which in this case is what puzzles me. We are only talking about a simple GUC variable after all - I don't know for sure, but I'm guessing it's not a huge effort to add one? Regards, Dave.
Dave Page wrote: > Which in this case is what puzzles me. We are only talking about a > simple GUC variable after all - I don't know for sure, but I'm guessing > it's not a huge effort to add one? Can we get agreement on that? A GUC for pg_xlog location? Much cleaner than -X, doesn't have the problems of possible accidental use, and does allow pg_xlog moving without symlinks, which some people don't like? If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 17 Sep 2002, Bruce Momjian wrote: > Dave Page wrote: > > Which in this case is what puzzles me. We are only talking about a > > simple GUC variable after all - I don't know for sure, but I'm guessing > > it's not a huge effort to add one? > > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner > than -X, doesn't have the problems of possible accidental use, and does > allow pg_xlog moving without symlinks, which some people don't like? > > If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. GUC instead of -X or PGXLOG : yes. However, how is that going to work if tablespaces are introduced in 7.4. Surely the same mechanism for tablespaces would be used for pg_xlog. As the tablespace mechanism hasn't been determined yet, as far as I know, wouldn't it be best to see what happens there before creating the TODO item for the log? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
"Nigel J. Andrews" wrote: <snip> > However, how is that going to work if tablespaces are introduced in 7.4. Surely > the same mechanism for tablespaces would be used for pg_xlog. As the tablespace > mechanism hasn't been determined yet, as far as I know, wouldn't it be best to > see what happens there before creating the TODO item for the log? It's a Yes from me of course. Would a TODO list entry of something like "Add a GUC xlog_path variable" be broad enough that people keep it in mind when tablespaces are created, but it doesn't get forgotten about by not being on the list? :-) Regards and best wishes, Justin Clift > -- > Nigel J. Andrews > Director > > --- > Logictree Systems Limited > Computer Consultants > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Nigel J. Andrews wrote:
> On Tue, 17 Sep 2002, Bruce Momjian wrote:
>
> > Dave Page wrote:
> > > Which in this case is what puzzles me. We are only talking about a
> > > simple GUC variable after all - I don't know for sure, but I'm guessing
> > > it's not a huge effort to add one?
> >
> > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner
> > than -X, doesn't have the problems of possible accidental use, and does
> > allow pg_xlog moving without symlinks, which some people don't like?
> >
> > If I can get a few 'yes' votes I will add it to TODO and do it for 7.4.
>
> GUC instead of -X or PGXLOG : yes.
>
> However, how is that going to work if tablespaces are introduced in 7.4. Surely
> the same mechanism for tablespaces would be used for pg_xlog. As the tablespace
> mechanism hasn't been determined yet, as far as I know, wouldn't it be best to
> see what happens there before creating the TODO item for the log?
Good point. How about:
Allow pg_xlog to be moved without symlinks
That is vague enough. Added to TODO.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
I forget, is it possible to make a GUC that cannot be changed during runtime? If so, then I vote yes, otherwise, there is a problem if someone tries. On Tue, 2002-09-17 at 17:07, Bruce Momjian wrote: > Dave Page wrote: > > Which in this case is what puzzles me. We are only talking about a > > simple GUC variable after all - I don't know for sure, but I'm guessing > > it's not a huge effort to add one? > > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner > than -X, doesn't have the problems of possible accidental use, and does > allow pg_xlog moving without symlinks, which some people don't like? -- Rod Taylor
Rod Taylor wrote: > I forget, is it possible to make a GUC that cannot be changed during > runtime? Yes, you can set it to it only can be changed by the super-user and only takes effect on restart. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > > Dave Page wrote: > > Which in this case is what puzzles me. We are only talking about a > > simple GUC variable after all - I don't know for sure, but I'm guessing > > it's not a huge effort to add one? > > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner > than -X, doesn't have the problems of possible accidental use, and does > allow pg_xlog moving without symlinks, which some people don't like? > > If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. 'yes' - make it one more GUC and done Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
"Nigel J. Andrews" wrote: > However, how is that going to work if tablespaces are introduced in 7.4. Surely > the same mechanism for tablespaces would be used for pg_xlog. As the tablespace > mechanism hasn't been determined yet, as far as I know, wouldn't it be best to > see what happens there before creating the TODO item for the log? No, tablespaces would have to be something DB specific, while the Xlog is instance wide (instance == one postmaster == installation == whatever you name that level). My vision is that we start off with two tablespaces per database, "default" and "default_idx", which are subdirectories inside the database directory. All (non-index-)objects created without explicitly saying what tablespace they belong to automatically belong to default. Indexes ... bla. The tablespace catalog will have a column telling the physical location of that directory. Moving it around will not be *that* easy, I guess, because the UPDATE of that entry has to go hand in hand with the move of all files in that damned directory. But that's another thing to sort out later, IMHO. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > "Nigel J. Andrews" wrote: > > However, how is that going to work if tablespaces are introduced in 7.4. Surely > > the same mechanism for tablespaces would be used for pg_xlog. As the tablespace > > mechanism hasn't been determined yet, as far as I know, wouldn't it be best to > > see what happens there before creating the TODO item for the log? > > No, tablespaces would have to be something DB specific, while the Xlog > is instance wide (instance == one postmaster == installation == whatever > you name that level). > > My vision is that we start off with two tablespaces per database, > "default" and "default_idx", which are subdirectories inside the > database directory. All (non-index-)objects created without explicitly > saying what tablespace they belong to automatically belong to default. > Indexes ... bla. > > The tablespace catalog will have a column telling the physical location > of that directory. Moving it around will not be *that* easy, I guess, > because the UPDATE of that entry has to go hand in hand with the move of > all files in that damned directory. But that's another thing to sort out > later, IMHO. Yes, the nifty trick was to use a lstat() from pg_dump to learn if it is a symlink and if so, where it points to. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 17 Sep 2002, Bruce Momjian wrote: > Dave Page wrote: > > Which in this case is what puzzles me. We are only talking about a > > simple GUC variable after all - I don't know for sure, but I'm guessing > > it's not a huge effort to add one? > > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner > than -X, doesn't have the problems of possible accidental use, and does > allow pg_xlog moving without symlinks, which some people don't like? > > If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. Personally, I like the ability to define such at a command line level ... *especially* as it pertains to pointing to various directories ... I am against pulling the -X functionality out ... if you don't like it, don't use it ... add the GUC variable option to the mix, but don't take away functionality ... Hell, take a look at what you are saying above: because someone might forget to set -X, let's get rid of it in favor of a setting in a file that someone might forget to edit? Either format has the possibility of an error ... if you are so incompetent as to make that sort of mistake on a production server, it won't matter if its a GUC variable, environment variable or commnd line argument, you will still make that mistake ...
Marc G. Fournier wrote: > On Tue, 17 Sep 2002, Bruce Momjian wrote: > > > Dave Page wrote: > > > Which in this case is what puzzles me. We are only talking about a > > > simple GUC variable after all - I don't know for sure, but I'm guessing > > > it's not a huge effort to add one? > > > > Can we get agreement on that? A GUC for pg_xlog location? Much cleaner > > than -X, doesn't have the problems of possible accidental use, and does > > allow pg_xlog moving without symlinks, which some people don't like? > > > > If I can get a few 'yes' votes I will add it to TODO and do it for 7.4. > > Personally, I like the ability to define such at a command line level ... > *especially* as it pertains to pointing to various directories ... I am > against pulling the -X functionality out ... if you don't like it, don't > use it ... add the GUC variable option to the mix, but don't take away > functionality ... > > Hell, take a look at what you are saying above: because someone might > forget to set -X, let's get rid of it in favor of a setting in a file that > someone might forget to edit? > > Either format has the possibility of an error ... if you are so > incompetent as to make that sort of mistake on a production server, it > won't matter if its a GUC variable, environment variable or commnd line > argument, you will still make that mistake ... Sorry, I don't see the logic here. Using postgresql.conf, you set it once and it remains set until you change it again. With -X, you have to use it every time. I think that's where the votes came from. You argued that -X and GUC make sense, but why add -X when can get it done at once in postgresql.conf. Also, consider changing the location does require moving the WAL files, so you already have this extra step. Adding to postgresql.conf is easy. I don't think you can just point it at a random empty directory on startup. Our goal was to reduce params to postmaster/postgres in favor of GUC, not add to them. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Wed, 18 Sep 2002, Bruce Momjian wrote:
> Sorry, I don't see the logic here. Using postgresql.conf, you set it
> once and it remains set until you change it again. With -X, you have to
> use it every time. I think that's where the votes came from.
Ah, so you are saying that you type out your full command line each and
every time you start up the server? I know, in my case, I have a shell
script setup that I edit my changes in so that I don't have to remember
...
> You argued that -X and GUC make sense, but why add -X when can get it
> done at once in postgresql.conf. Also, consider changing the location
> does require moving the WAL files, so you already have this extra step.
> Adding to postgresql.conf is easy. I don't think you can just point it
> at a random empty directory on startup. Our goal was to reduce params
> to postmaster/postgres in favor of GUC, not add to them.
I don't disagree that editing postgresql.conf is easy, but its not
something that ppl would naturally thing of ... if I want to move a
directory with most servers I run, I will generally do a man to find out
what command options are required to do this change, and, if none are
provided, just create a god-forsaken symlink ...
The man page for postmaster should have something in it like:
-X <directory> Specifies an alternate location for WAL files. Superseded
by setting xlog_path in postmaster.conf
Hell, if you are going to remove -X because its 'easier to do it in
postmaster.conf', you should be looking at removing *all* command line
args that are better represented in the postmaster.conf file ...
The only time that *I* use the postmaster.conf file is when I'm playing
with the various scan'ng options ... why?
mars# ps aux | grep -- B
pgsql 133 0.0 0.0 77064 1512 con- S Mon10PM 3:21.15 /usr/local/bin/postmaster -B 8192 -N 512 -o -S 4096 -i
-p5432 -D/v1/pgsql (postgres)
pgsql 144 0.0 0.0 1097300 1372 ?? Is Mon10PM 0:06.04 /usr/local/pgsql/bin/postmaster -B 131072 -N 2048 -i
-p5433 -D/usr/local/pgsql/5433 -S (postgres)
its nice to be able to do a simple ps to find out which process is which,
and pointing where ... other then -D, I don't believe there is one option
in there that I couldn't have set in the postmaster.conf file, but, then,
to find out the various settings, I'd have to do ps to figure out where
the database files are stored, and then go look at the postmaster.conf
file to figure out what each are set to ...
I have one server that has 10 instances running right now:
jupiter# ps ax | grep -- -B
373 ?? Ss 0:55.31 /usr/local/pgsql721/bin/postmaster -B 10240 -N 512 -i -p 5432 -D/v1/pgsql/5432 -S (postgres)
383 ?? Ss 0:11.78 /usr/local/pgsql/bin/postmaster -B 64 -N 16 -i -p 5434 -D/v1/pgsql/5434 -S (postgres)
394 ?? Ss 0:17.82 /usr/local/pgsql/bin/postmaster -B 1024 -N 256 -i -p 5437 -D/v1/pgsql/5437 -S (postgres)
405 ?? Ss 0:16.46 /usr/local/pgsql/bin/postmaster -B 256 -N 128 -i -p 5440 -D/v1/pgsql/5440 -S (postgres)
416 ?? Ss 0:10.93 /usr/local/pgsql/bin/postmaster -B 256 -N 128 -i -p 5449 -D/v1/pgsql/5449 -S (postgres)
427 ?? Ss 0:16.30 /usr/local/pgsql/bin/postmaster -B 2048 -N 256 -i -p 5443 -D/v1/pgsql/5443 -S (postgres)
438 ?? Ss 0:10.60 /usr/local/pgsql721/bin/postmaster -B 1024 -N 512 -i -p 5446 -D/v1/pgsql/5446 -S (postgres)
88515 ?? Ss 0:10.05 /usr/local/pgsql/bin/postmaster -B 64 -N 16 -i -p 5433 -D/v1/pgsql/5433 -S (postgres)
13029 pi S+ 0:00.00 grep -- -B
445 con- S 0:10.59 /usr/local/pgsql/mb/bin/postmaster -B 256 -N 128 -i -p 5448 -D/v1/pgsql/openacs4 (postgres)
460 con- S 0:10.40 /usr/local/pgsql/bin/postmaster -B 64 -N 16 -i -p 5436 -D/v1/pgsql/electrichands (postgres)
All the information for each are right there in front of me ... I don't
have to go through 10 postmaster.conf files to figure out anything ...
the GUC value should override the command line option, agreed ... but the
ability to use the command line should not be removed just because some
ppl aren't competent enough to adjust their startup scripts if they change
their system ...
Marc G. Fournier wrote: > On Wed, 18 Sep 2002, Bruce Momjian wrote: > > > Sorry, I don't see the logic here. Using postgresql.conf, you set it > > once and it remains set until you change it again. With -X, you have to > > use it every time. I think that's where the votes came from. > > Ah, so you are saying that you type out your full command line each and > every time you start up the server? I know, in my case, I have a shell > script setup that I edit my changes in so that I don't have to remember > ... Yep, but your central place for changes should be postgresql.conf, not the command line. If we tried go get every GUC param on the command line it would be unusable. > > You argued that -X and GUC make sense, but why add -X when can get it > > done at once in postgresql.conf. Also, consider changing the location > > does require moving the WAL files, so you already have this extra step. > > Adding to postgresql.conf is easy. I don't think you can just point it > > at a random empty directory on startup. Our goal was to reduce params > > to postmaster/postgres in favor of GUC, not add to them. > > I don't disagree that editing postgresql.conf is easy, but its not > something that ppl would naturally thing of ... if I want to move a > directory with most servers I run, I will generally do a man to find out > what command options are required to do this change, and, if none are > provided, just create a god-forsaken symlink ... > > The man page for postmaster should have something in it like: > > -X <directory> Specifies an alternate location for WAL files. Superseded > by setting xlog_path in postmaster.conf > > Hell, if you are going to remove -X because its 'easier to do it in > postmaster.conf', you should be looking at removing *all* command line > args that are better represented in the postmaster.conf file ... Well, those other options are things you may want to change frequently. The xlog directory isn't going to be moving around, we hope. We have the flags there only so they can be easily adjusted for testing, I think, and in fact there has been discussion about removing more of them. > its nice to be able to do a simple ps to find out which process is which, > and pointing where ... other then -D, I don't believe there is one option > in there that I couldn't have set in the postmaster.conf file, but, then, > to find out the various settings, I'd have to do ps to figure out where > the database files are stored, and then go look at the postmaster.conf > file to figure out what each are set to ... Yea, but you aren't going to be needing to know the xlog directory that way, will you? Fact is, xlog is seldom moved, and symlinks do it fine now. The GUC was a compromise for people who didn't like symlinks. If we are getting pushback from GUC we may as well just drop the GUC idea and stick with symlinks. I think that's how the vote went last time and it seems to be heading in that direction again. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Marc G. Fournier" <scrappy@hub.org> writes:
> On Wed, 18 Sep 2002, Bruce Momjian wrote:
>> Sorry, I don't see the logic here. Using postgresql.conf, you set it
>> once and it remains set until you change it again. With -X, you have to
>> use it every time. I think that's where the votes came from.
> Ah, so you are saying that you type out your full command line each and
> every time you start up the server?
Let's put it this way: would you be in favor of adding a
--please-don't-wipe-my-database-directory
switch to the postmaster? And if you forget to specify that every time
you start the postmaster, we do an instant "rm -rf $PGDATA"?
Doesn't seem like a good idea, does it?
Well, specifying the XLOG location on the command line or as an
environment variable is just about as deadly as the above loaded-gun-
pointed-at-foot scenario. You start the postmaster with the wrong
context, even once, it's sayonara to your data integrity.
The point of insisting that the XLOG location be recorded *inside*
the data directory is to prevent simple admin errors from being
catastrophic. Do you remember when we regularly saw trouble reports
from people who'd corrupted their database indexes by starting the
postmaster with different LOCALE environments at different times? We
fixed that by forcing the locale collation order to be specified inside
the database directory (in pg_control, but the details are not important
here), rather than allowing it to be taken from postmaster environment.
If we allow XLOG location to be determined by a postmaster switch or
environment variable, then we *will* be opening the door for people
to shoot themselves in the foot just like they used to do with locale.
I learned something from those problems, and I do not intend to make
the same mistake again.
regards, tom lane
On Wed, 18 Sep 2002, Bruce Momjian wrote: > Yea, but you aren't going to be needing to know the xlog directory that > way, will you? Why not? Who are you to tell me how my scripts work, or how they get their information? I have a script that runs to tell me how much disk space each instance is using up, that parses the ps output for the -D argument ... having -X there would allow me to parse for that as well and, if it was in the ps output, add that appropriately into the calculations ... My point is, the functionality is there, and should be documented properly ... encourage ppl to use the GUC setting in postmaster.conf, but just because you can't grasp that some of us *like* to use command line args, don't remove such functionality ...
Marc G. Fournier wrote: > On Wed, 18 Sep 2002, Bruce Momjian wrote: > > > Yea, but you aren't going to be needing to know the xlog directory that > > way, will you? > > Why not? Who are you to tell me how my scripts work, or how they get > their information? I have a script that runs to tell me how much disk > space each instance is using up, that parses the ps output for the -D > argument ... having -X there would allow me to parse for that as well and, > if it was in the ps output, add that appropriately into the calculations > ... > > My point is, the functionality is there, and should be documented properly > ... encourage ppl to use the GUC setting in postmaster.conf, but just > because you can't grasp that some of us *like* to use command line args, > don't remove such functionality ... You ask for a vote and see if you can get votes to add -X. We had that vote once already. We do make decisions on what people should use. If not, we would be as hard to manage as Oracle. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, 18 Sep 2002, Tom Lane wrote: > "Marc G. Fournier" <scrappy@hub.org> writes: > > On Wed, 18 Sep 2002, Bruce Momjian wrote: > >> Sorry, I don't see the logic here. Using postgresql.conf, you set it > >> once and it remains set until you change it again. With -X, you have to > >> use it every time. I think that's where the votes came from. > > > Ah, so you are saying that you type out your full command line each and > > every time you start up the server? > > Let's put it this way: would you be in favor of adding a > --please-don't-wipe-my-database-directory > switch to the postmaster? And if you forget to specify that every time > you start the postmaster, we do an instant "rm -rf $PGDATA"? > > Doesn't seem like a good idea, does it? > > Well, specifying the XLOG location on the command line or as an > environment variable is just about as deadly as the above loaded-gun- > pointed-at-foot scenario. You start the postmaster with the wrong > context, even once, it's sayonara to your data integrity. > > The point of insisting that the XLOG location be recorded *inside* > the data directory is to prevent simple admin errors from being > catastrophic. Do you remember when we regularly saw trouble reports > from people who'd corrupted their database indexes by starting the > postmaster with different LOCALE environments at different times? We > fixed that by forcing the locale collation order to be specified inside > the database directory (in pg_control, but the details are not important > here), rather than allowing it to be taken from postmaster environment. > > If we allow XLOG location to be determined by a postmaster switch or > environment variable, then we *will* be opening the door for people > to shoot themselves in the foot just like they used to do with locale. > > I learned something from those problems, and I do not intend to make > the same mistake again. Except that you are ... you are assuming that someone is going to edit their postmaster.conf file correctly ... if you want to avoid making the same mistake again, there should be some sort of 'tag' that associates the files in the XLOG directory with the data directories themselves, regardless of *how* the XLOG directory is referenced ... something that links them at a level that an administrator *can't* make a mistake about ... all forcing the use of the postmaster.conf file is doing is reducing options, it isn't making sure that the XLOG directory pointed to is apporopraite for the data directory itself ...
I think Marc made a pretty good case about the use of command line arguments but I think I have to vote with Tom. Many of the command line arguments you seem to be using do sorta make sense to have for easy reference or to help validate your runtime environment for each instance. The other side of that is, I completely agree with Tom in the it's a very dangerous option. It would be begging for people to shoot themselves with it. Besides, just as you can easily parse the command line, you can also parse the config file to out that information. Plus, it really should be a very seldom used option. When it is used, it's doubtful that you'll need the same level of dynamic control that you get by using command line options. As a rule of thumb, if an option is rarely used or is very dangerous if improperly used, I do think it should be in a configuration file to discourage adhoc use. Let's face it, specify XLOG location is hardly something people need to be doing on the fly. My vote is config file it and no command line option! Greg On Wed, 2002-09-18 at 23:50, Bruce Momjian wrote: > Marc G. Fournier wrote: > > On Wed, 18 Sep 2002, Bruce Momjian wrote: > > > > > Yea, but you aren't going to be needing to know the xlog directory that > > > way, will you? > > > > Why not? Who are you to tell me how my scripts work, or how they get > > their information? I have a script that runs to tell me how much disk > > space each instance is using up, that parses the ps output for the -D > > argument ... having -X there would allow me to parse for that as well and, > > if it was in the ps output, add that appropriately into the calculations > > ... > > > > My point is, the functionality is there, and should be documented properly > > ... encourage ppl to use the GUC setting in postmaster.conf, but just > > because you can't grasp that some of us *like* to use command line args, > > don't remove such functionality ... > > You ask for a vote and see if you can get votes to add -X. We had that > vote once already. We do make decisions on what people should use. If > not, we would be as hard to manage as Oracle. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (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
It seems all of this discussion misses the point. Either it has a large amount of impact and the idea gets rejected because of implementation issues, or it has little impact but it's nothing the core group wants to implement. If the problem is finding someone to implement it, it sounds like Justin has found such a person, so are we going to stand in his way while we wax poetic about OS religion and corporate philosophies or can he start submitting patches? Robert Treat On Mon, 2002-09-16 at 14:11, Justin Clift wrote: > Peter Eisentraut wrote: > > > > Justin Clift writes: > > > > > WinNT/2k/XP costs a few hundred dollars. > > > > > > MS SQL Server costs a few thousand dollars. > > > > The places that run Windows can be categorized into three camps: > <snip> > > How about this? > > The places that run Windows can be categorised a number of different > ways, depending on what you're looking for. > > 1) Places that have in-house staff that can do or learn everything. > > Many of these places are really small, some are not. PostgreSQL fits > well here, Windows or not, as these people are prepared to learn how to > use it best. > > > 2) Companies that hire external IT services. > > Often the software implemented here will be dependent on outside sources > of advice such as consultants, executives who take an interest in IT > mags, etc. > > Look at Windows NT on the server in the first place. Microsoft > leveraged the marketplace through making itself available then promoting > the heck out of itself into the IT press, industry mags, etc. > > These places will be receptive to PostgreSQL as our reputation further > becomes known and they can see where PostgreSQL will be useful to them. > PostgreSQL on Win NT/2K/XP will definitely be of use to a sizable number > of these businesses. > > > 3) Companies who depend on multiple external sources of IT support. > i.e. One reasonable sized enterprise here in Australia has over 450 > *development* companies presently working on applications for their > environment. Because of the scope of standardisation needed, they > standardised on WinNT many years ago. It still works for them. They > don't even have SP6 installed on their desktops as it breaks too many of > the desktop applications. etc. > > These people are not clueless. They make strategic decisions when > they're necessary, and it all comes down to flexibility, reliability, > and cost. > > For some things they run Unix, or Windows, or Novell, or OS/390, or any > number of other stuff. > > Because of the years of experience some of their support companies have > with WinNT, it works reliably enough for them. They don't have the > "need to reboot once per week" thing with their servers. > > These guys will become receptive to PostgreSQL too, and it will be in > our favour to be able to demonstrate very good performance across all > platforms that we can, not just our own *personally preferred* > platforms. > > By giving them options when it doesn't take a *whole bunch of effort* to > do so, we open up ways for PostgreSQL to be used that we haven't even > thought of before. We all know this already. > > It wouldn't really surprise me greatly if at some point this proved > beneficial to a non-Windows platform for some reason too. > > :-) > > Regards and best wishes, > > Justin Clift > > > > So where is the market? > > > > -- > > Peter Eisentraut peter_e@gmx.net > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Robert Treat wrote: > It seems all of this discussion misses the point. Either it has a large > amount of impact and the idea gets rejected because of implementation > issues, or it has little impact but it's nothing the core group wants to > implement. If the problem is finding someone to implement it, it sounds > like Justin has found such a person, so are we going to stand in his way > while we wax poetic about OS religion and corporate philosophies or can > he start submitting patches? Well, I have Win32 patches here I am reviewing. I think I can say that the changes are minimal and probably will be accepted for addition into 7.4. I am actually surprised at how little is required. Right now, 7.4 is targeted with point-in-time recovery and Win32. And, in fact, both patches are almost ready for inclusion into CVS, so we may find that 7.4 has a very short release cycle. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Marc G. Fournier writes: > My point is, the functionality is there, and should be documented properly > ... encourage ppl to use the GUC setting in postmaster.conf, but just > because you can't grasp that some of us *like* to use command line args, > don't remove such functionality ... Top secret information: If it's made a GUC variable, it's automatically a command-line option. -- Peter Eisentraut peter_e@gmx.net
On Thu, 19 Sep 2002, Thomas Lockhart wrote: > Actually, a core member did implement this just a few weeks ago. The > same crew arguing this time rejected the changes and removed them from > the 7.3 feature set. The change to make a PG_XLOG environment variable was rejected. Is that really the change you were talking about? > So some folks have their heels dug in, and the vocal ones are not really > interested in understanding the issues which this feature is addressing. I was one of the vocal objectors, and I certainly understand the issues very well. Perhaps we should be saying the vocal supporters of the environment variable don't understand the issues. None of the objectors I saw have any problem with enabling Windows NT to have the log file somewhere else. In fact, I'm very strongly in support of this. But I object to doing it in a way that makes the system more fragile and susceptable to not starting properly, or even damage, when there's a simple and obvious way of doing it right: put this in the database configuration file rather than in an environment variable. Why you object to that, and insist it must be an environment variable instead (if that is indeed what you're doing), I'm not sure.... cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
Thomas Lockhart wrote: > ... > > Why you object to that, and insist it must be an environment variable > > instead (if that is indeed what you're doing), I'm not sure.... > > Well, what I was hoping for, but no longer expect, is that features > (store xlog in another area) can be implemented and applied without > rejection by the new gatekeepers. It is a feature that we do not have > now, and could have implemented for 7.3. > > No need to rehash the points which were not understood in the > "discussion". > > I have no fundamental objection to extending and replacing > implementation features as positive contributions to development. I do > have trouble with folks rejecting features without understanding the > issues, and sorry, there was a strong thread of "why would anyone want > to put storage on another device" to the discussion. I believe the discussion was "Why not use symlinks?" I think we have addressed that issue with the GUC variable solution. Certainly we all recognize the value of moving storage to another drive. It is mentioned in the SGML docs and other places. In fact, I tried to open a dialog with you on this issue several times, but when I got no reply, I had to remove PGXLOG. If we had continued discussion, we might have come up with the GUC compromise. > There has been a fundamental shift in the quality and civility of > discussions over issues over the last couple of years, and I was naively > hoping that we could work through that on this topic. Not happening, and > not likely too. My impression is that things have been getting better in the past six months. There is more open discussion, and more voting, meaning one group isn't making all the decisions. I have worked to limit the sway of any "new gatekeepers". People are encouraged to vote, and we normally accept that outcome. I think gatekeepers should sway only in the force of their arguments. Do you feel this was not followed on the PGXLOG case, or is the concept in error? I certainly have been frustrated when my features were not accepted, but I have to accept the vote of the group. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Fri, 20 Sep 2002, Thomas Lockhart wrote: > Well, what I was hoping for, but no longer expect, is that features > (store xlog in another area) can be implemented and applied without > rejection by the new gatekeepers. It can be, and very simply. So long as you do it in the way which is not error-prone, rather than the way which is. > I have no fundamental objection to extending and replacing > implementation features as positive contributions to development. I do > have trouble with folks rejecting features without understanding the > issues, and sorry, there was a strong thread of "why would anyone want > to put storage on another device" to the discussion. I doubt it. There was perhaps a strong thread of "windows users are loosers," but certainly Unix folks put storage on another device all the time, using symlinks. This was mentioned many, many times. > There has been a fundamental shift in the quality and civility of > discussions over issues over the last couple of years, and I was naively > hoping that we could work through that on this topic. Not happening, and > not likely too. Well, when you're going to bring in Windows in a pretty heavily open-source-oriented group, no, it's not likely you're going to bring everyone together. (This is not a value judgement, it's just a, "Hello, this is the usenet (or something similar)," observation. That said, again, I don't think anybody was objecting to what you wanted to do. It was simply a bad implementation that I, and probably all the others, were objecting to. So please don't go on like we didn't like the concept. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Fri, 20 Sep 2002, Bruce Momjian wrote: > In fact, I tried to open a dialog with you on this issue several times, > but when I got no reply, I had to remove PGXLOG. If we had continued > discussion, we might have come up with the GUC compromise. Ya know, I'm sitting back and reading this, and other threads, and assimilating what is being bantered about, and start to think that its time to cut back on the gatekeepers ... Thomas implemented an option that he felt was useful, and that doesn't break anything inside of the code ... he provided 2 methods of being able to move the xlog's to another location (through command line and environment variable, both of which are standard methods for doing such in server software) ... but, because a small number of ppl "voted" that it should go away, it went away ... You don't :vote: on stuff like this ... if you don't like it, you just don't use it ... nobody is forcing you to do so. If you think there are going to be idiots out here that aren't going to use it right, then you document it appropriately, with *strong* wording against using it ...
Marc G. Fournier wrote: > > On Fri, 20 Sep 2002, Bruce Momjian wrote: > > > In fact, I tried to open a dialog with you on this issue several times, > > but when I got no reply, I had to remove PGXLOG. If we had continued > > discussion, we might have come up with the GUC compromise. > > Ya know, I'm sitting back and reading this, and other threads, and > assimilating what is being bantered about, and start to think that its > time to cut back on the gatekeepers ... > > Thomas implemented an option that he felt was useful, and that doesn't > break anything inside of the code ... he provided 2 methods of being able > to move the xlog's to another location (through command line and > environment variable, both of which are standard methods for doing such in > server software) ... but, because a small number of ppl "voted" that it > should go away, it went away ... > > You don't :vote: on stuff like this ... if you don't like it, you just > don't use it ... nobody is forcing you to do so. If you think there are > going to be idiots out here that aren't going to use it right, then you > document it appropriately, with *strong* wording against using it ... I understand your thought of reevaluating how we decide things. However, if you don't accept voting as a valid way to determine if a patch is acceptible, what method do you suggest? I don't think we want to go down the road of saying that you can't vote "no" on a feature addition. We just rejected a patch today on LIMIT with UPDATE/DELETE via an informal vote, and I think it was a valid rejection. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Sun, 22 Sep 2002, Bruce Momjian wrote: > Marc G. Fournier wrote: > > > > On Fri, 20 Sep 2002, Bruce Momjian wrote: > > > > > In fact, I tried to open a dialog with you on this issue several times, > > > but when I got no reply, I had to remove PGXLOG. If we had continued > > > discussion, we might have come up with the GUC compromise. > > > > Ya know, I'm sitting back and reading this, and other threads, and > > assimilating what is being bantered about, and start to think that its > > time to cut back on the gatekeepers ... > > > > Thomas implemented an option that he felt was useful, and that doesn't > > break anything inside of the code ... he provided 2 methods of being able > > to move the xlog's to another location (through command line and > > environment variable, both of which are standard methods for doing such in > > server software) ... but, because a small number of ppl "voted" that it > > should go away, it went away ... > > > > You don't :vote: on stuff like this ... if you don't like it, you just > > don't use it ... nobody is forcing you to do so. If you think there are > > going to be idiots out here that aren't going to use it right, then you > > document it appropriately, with *strong* wording against using it ... > > I understand your thought of reevaluating how we decide things. > > However, if you don't accept voting as a valid way to determine if a > patch is acceptible, what method do you suggest? I don't think we want > to go down the road of saying that you can't vote "no" on a feature > addition. > > We just rejected a patch today on LIMIT with UPDATE/DELETE via an > informal vote, and I think it was a valid rejection. Its not the concept of 'the vote', its what is being voted on that I have a major problem with ... for instance, with the above LIMIT patch ... you are talking about functionality ... I haven't seen that thread yet, so am not sure why it was rejected, but did the submitter agree with the reasons? Assuming he did, is this something he's going to re-submit later after makign fixes? See, that is one thing I have enjoyed over the years ... someone submit's a patch and a few ppl jump on top of it, point out a few problems iwth it and the submitter re-submits with appropriate fixes ... Actually, I just went to my -patches folder and read the thread ... first off, the 'informal vote' appears to have consisted of Tom Lane and Alvaro Herrera, which isn't a vote ... second of all, in that case, the implementation of such, I believe, would go against SQL specs, no? Second of all, doesn't it just purely go against the point of a RDBMS if there are multiple rows in a table with nothing to identify them except for the ctid/oid? *scratch head* My point is, the use of an ENVIRONMENT variable for pointing ot a directory is nowhere near on the scale of implementing an SQL statement (or extension) that serves to take us steps backwards against the progress we've made to improve our compliance ... one has been removed due to personal preferences and nothign else ... the other rejected as it will break (unless I've misread things?) standard, accepted procedures ...
"Marc G. Fournier" <scrappy@hub.org> writes: > Ya know, I'm sitting back and reading this, and other threads, and > assimilating what is being bantered about, and start to think that > its time to cut back on the gatekeepers ... On the contrary, the quality of code accepted into a DBMS is really important. If you disagree with the definition of "code quality" that some developers are employing, then we can discuss that -- but I think that as the project matures, we should be more picky about the features we implement, not less. > Thomas implemented an option that he felt was useful, and that > doesn't break anything inside of the code The problem with this line of thinking is that "it doesn't break stuff" is not sufficient reason for adding a new feature. The burden of proof is on the person implementing the new feature. > ... he provided 2 methods of being able to move the xlog's to > another location Yes, but why do we need 2 different ways to do exactly the same thing? > but, because a small number of ppl "voted" that it should go away, > it went away ... They didn't just vote, they provided reasons why they thought the feature was brain-damaged -- reasons which have not be persuasively refuted, IMHO. If you'd like to see this feature in the code, might I suggest that you spend less time complaining about "gate keepers" (hint: it's called code review), and more time explaining exactly why the feature is worth having? Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Marc G. Fournier wrote: > > However, if you don't accept voting as a valid way to determine if a > > patch is acceptible, what method do you suggest? I don't think we want > > to go down the road of saying that you can't vote "no" on a feature > > addition. > > > > We just rejected a patch today on LIMIT with UPDATE/DELETE via an > > informal vote, and I think it was a valid rejection. > > Its not the concept of 'the vote', its what is being voted on that I have > a major problem with ... for instance, with the above LIMIT patch ... you > are talking about functionality ... I haven't seen that thread yet, so am > not sure why it was rejected, but did the submitter agree with the > reasons? Assuming he did, is this something he's going to re-submit later > after makign fixes? > > See, that is one thing I have enjoyed over the years ... someone submit's > a patch and a few ppl jump on top of it, point out a few problems iwth it > and the submitter re-submits with appropriate fixes ... > > Actually, I just went to my -patches folder and read the thread ... first > off, the 'informal vote' appears to have consisted of Tom Lane and Alvaro > Herrera, which isn't a vote ... second of all, in that case, the > implementation of such, I believe, would go against SQL specs, no? Second > of all, doesn't it just purely go against the point of a RDBMS if there > are multiple rows in a table with nothing to identify them except for the > ctid/oid? *scratch head* > > My point is, the use of an ENVIRONMENT variable for pointing ot a > directory is nowhere near on the scale of implementing an SQL statement > (or extension) that serves to take us steps backwards against the progress > we've made to improve our compliance ... The issue isn't really compliance because LIMIT in SELECT isn't compliant either, so adding it to UPDATE/DELETE is just as non-standard as in SELECT. The real question we vote on, I think, is, "Should this feature be given to our users? What value does it provide, and what confusion does it cause? Does the standard suggest anything?" I think that is the usual criteria. For LIMIT on UPDATE/DELETE, it provides little value, and adds confusion, i.e. an extra clause in those two commands that really doesn't add any functionality. Now, for the PG_XLOG environment variable/-X flag, it is almost the same result, i.e. it doesn't add much value (use a symlink) and does add confusion (oops, I forgot to set it). The idea of having the pg_xlog location in GUC I think was a good compromise, but too late to be discovered. If the patch author had continued discussion at the time, I think it would be in 7.3. > one has been removed due to personal preferences and nothign else ... the > other rejected as it will break (unless I've misread things?) standard, > accepted procedures ... PG_XLOG was remove for a few reasons: It didn't add much functionalityIt was ugly to add -X to all those commandsIt was error-prone Again, the same criteria. Are you saying the criteria I mentioned above is wrong? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"Marc G. Fournier" <scrappy@hub.org> writes:
> You don't :vote: on stuff like this ...
Why not, exactly?
I wasn't aware that any of core had a non-vetoable right to apply
any patch we liked regardless of the number and strength of the
objections. AFAIK, we resolve differences of opinion by discussion,
followed by a vote if the discussion doesn't produce a consensus.
It was pretty clear that Thomas' original patch lost the vote, or
would have lost if we'd bothered to hold a formal vote. I don't
see anyone arguing against the notion of making XLOG location more
easily configurable --- it was just the notion of making it depend
on environment variables that scared people.
regards, tom lane
On Sun, 22 Sep 2002, Tom Lane wrote: > > It was pretty clear that Thomas' original patch lost the vote, or > would have lost if we'd bothered to hold a formal vote. Hasn't there just been a formal vote on this? > I don't > see anyone arguing against the notion of making XLOG location more > easily configurable --- it was just the notion of making it depend > on environment variables that scared people. And it's obvious it was centred on the use of an environment variable from the subject line, it's still got PGXLOG in capitals in it. -- Nigel J. Andrews
"Nigel J. Andrews" wrote: <snip> > > And it's obvious it was centred on the use of an environment variable from the > subject line, it's still got PGXLOG in capitals in it. Actually, to be really precise, my original email asked for an environment variable. But only because I'd thought about it from the point of view of us already having a PGDATA environment variable and hadn't considered alternatives nor seen Thomas's stuff. Personally, I don't care if it's a -X, or an environment variable, or a GUC option. I'm just extremely positive that we should have an alternative to using symlinks for this (they don't work properly on NT). After following the discussion for a while I'm inclined to think that we should indeed have the GUC version, and *maybe* have the environment variable or the -X. The only thing bad about the -X is it's ability to trash your data if you forget it or get it wrong, and it's really easy to do in a decent scale environment with many servers. Marc has already suggested we might as well have something about a particular pg_xlog directory that PostgreSQL can use to check it's validity upon startup, so that could solve the data damaging issue. So, this thread has migrated away from a PGXLOG environment variable to discuss PGXLOG in general (good or bad) and also has implementation points too (about which people have been arguing). Regards and best wishes, Justin Clift > -- > Nigel J. Andrews > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Sun, 22 Sep 2002, Marc G. Fournier wrote: > Thomas implemented an option that he felt was useful, and that doesn't > break anything inside of the code ... he provided 2 methods of being able > to move the xlog's to another location (through command line and > environment variable, both of which are standard methods for doing such in > server software) ... but, because a small number of ppl "voted" that it > should go away, it went away ... The option as he implemented it did make the system more fragile. You can't back up an environment variable, it's separated from other configuration information, and it's more easily changed without realizing it. We should be building systems that are as resilient to human failure as possible, not opening up more possibilities of failure. We already have a place for configuration information: the configuration file. If I created a patch to move a variable out of the configuration file and make it an environment variable instead, everybody would (rightly) think I was nuts, and the patch certainly would not be accepted. So why should the situation be different for new configuration information? cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On 19 Sep 2002, Greg Copeland wrote: > I think Marc made a pretty good case about the use of command line > arguments but I think I have to vote with Tom. Many of the command line > arguments you seem to be using do sorta make sense to have for easy > reference or to help validate your runtime environment for each > instance. The other side of that is, I completely agree with Tom in the > it's a very dangerous option. It would be begging for people to shoot > themselves with it. Besides, just as you can easily parse the command > line, you can also parse the config file to out that information. Plus, > it really should be a very seldom used option. When it is used, it's > doubtful that you'll need the same level of dynamic control that you get > by using command line options. > > As a rule of thumb, if an option is rarely used or is very dangerous if > improperly used, I do think it should be in a configuration file to > discourage adhoc use. > > Let's face it, specify XLOG location is hardly something people need to > be doing on the fly. > > My vote is config file it and no command line option! I'd go one step further, and say that it should not be something a user should do by hand, but there should be a script to do it, and it would work this way: If there is a DIRECTORY called pg_xlog in $PGDATA, then use that. If there is a FILE called pg_xlog in $PGDATA, then that file will have the location of the directory stored in it. That file will be created when the move_pgxlog script is run, and that script will be have all the logic inside it to determine how to move the pg_xlog directory safely, i.e. making sure there's room on the destination, setting permissions, etc... that way, if you're dumb as a rock or smart as a rocket scientist, you do it the same way, and the script makes sure you don't scram your database in a not too bright moment. No postgresql.conf var, no command line switch, a file or directory, and a script. Seem workable? Or am I on crack?
Tom Lane wrote: > > "Marc G. Fournier" <scrappy@hub.org> writes: > > You don't :vote: on stuff like this ... > > Why not, exactly? > > I wasn't aware that any of core had a non-vetoable right to apply > any patch we liked regardless of the number and strength of the > objections. AFAIK, we resolve differences of opinion by discussion, > followed by a vote if the discussion doesn't produce a consensus. > > It was pretty clear that Thomas' original patch lost the vote, or > would have lost if we'd bothered to hold a formal vote. I don't > see anyone arguing against the notion of making XLOG location more > easily configurable --- it was just the notion of making it depend > on environment variables that scared people. And AFAICS it is scary only because screwing that up will simply corrupt your database. Thus, a simple random number (okay, and a timestamp of initdb) in two files, one in $PGDATA and one in $PGXLOG would be a totally sufficient safety mechanism to prevent starting with the wrong XLOG directory. Can we get that instead of ripping out anything? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote:
> Tom Lane wrote:
> >
> > "Marc G. Fournier" <scrappy@hub.org> writes:
> > > You don't :vote: on stuff like this ...
> >
> > Why not, exactly?
> >
> > I wasn't aware that any of core had a non-vetoable right to apply
> > any patch we liked regardless of the number and strength of the
> > objections. AFAIK, we resolve differences of opinion by discussion,
> > followed by a vote if the discussion doesn't produce a consensus.
> >
> > It was pretty clear that Thomas' original patch lost the vote, or
> > would have lost if we'd bothered to hold a formal vote. I don't
> > see anyone arguing against the notion of making XLOG location more
> > easily configurable --- it was just the notion of making it depend
> > on environment variables that scared people.
>
> And AFAICS it is scary only because screwing that up will simply corrupt
> your database. Thus, a simple random number (okay, and a timestamp of
> initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> totally sufficient safety mechanism to prevent starting with the wrong
> XLOG directory.
>
> Can we get that instead of ripping out anything?
Well, the problem is that Thomas stopped communicating, perhaps because
some were too aggressive in criticizing the patch. Once that happened,
there was no way to come up with a solution, and that's why it was
removed.
Also, we are in the process of removing args and moving them to GUC so I
don't see why we would make WAL an exception. It isn't changed that
often.
FYI, I am about to do the same removal for the SSL stuff too. Bear is
no longer responding. It is on the open items list now. If I can't
find someone who can review the good/bad parts of our SSL changes, it
might all be yanked out.
---------------------------------------------------------------------------
P O S T G R E S Q L
7 . 3 O P E N I T E M S
Current at ftp://momjian.postgresql.org/pub/postgresql/open_items.
Source Code Changes
-------------------
Schema handling - ready? interfaces? client apps?
Drop column handling - ready for all clients, apps?
Fix BeOS, QNX4 ports
Fix AIX large file compile failure of 2002-09-11 (Andreas)
Get bison upgrade on postgresql.org for ecpg only (Marc)
Fix vacuum btree bug (Tom)
Fix client apps for autocommit = off
Fix clusterdb to be schema-aware
Change log_min_error_statement to be off by default (Gavin)
Fix return tuple counts/oid/tag for rules
Loading 7.2 pg_dumpsfunctions no longer public executablelanguages no longer public usable
Add schema dump option to pg_dump
Make SET not start a transaction with autocommit off, document it
Add GRANT EXECUTE to all /contrib functions
Revert or fix SSL change
On Going
--------
Security audit
Documentation Changes
---------------------
Document need to add permissions to loaded functions and languages
Move documation to gborg for moved projects
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Jan Wieck <JanWieck@Yahoo.com> writes:
> And AFAICS it is scary only because screwing that up will simply corrupt
> your database. Thus, a simple random number (okay, and a timestamp of
> initdb) in two files, one in $PGDATA and one in $PGXLOG would be a
> totally sufficient safety mechanism to prevent starting with the wrong
> XLOG directory.
> Can we get that instead of ripping out anything?
Sure, if someone wants to do that it'd go a long way towards addressing
the safety issues.
But given that, I think a GUC variable is the most appropriate control
mechanism; as someone else pointed out, we've worked long and hard to
make GUC useful and feature-ful, so it seems silly to invent new
configuration items that bypass GUC. The safety concerns were the main
reason I liked a symlink or separate file, but if we attack the safety
problem directly then we might as well go for convenience in how you
actually set the configuration value.
regards, tom lane
On Tue, 24 Sep 2002, Jan Wieck wrote: > And AFAICS it is scary only because screwing that up will simply corrupt > your database. Thus, a simple random number (okay, and a timestamp of > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a > totally sufficient safety mechanism to prevent starting with the wrong > XLOG directory. But still, why set up a situation where your database might not start? Why not set it up so that if you get just *one* environment or command-line variable right, you can't set another inconsistently and screw up your start anyway? Why store configuration information outside of the database data directory in a form that's not easily backed up, and not easily found by other utilities? It's almost like people *don't* want to put this in the config file or something.... cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
On Wed, 25 Sep 2002, Curt Sampson wrote: > On Tue, 24 Sep 2002, Jan Wieck wrote: > > > And AFAICS it is scary only because screwing that up will simply corrupt > > your database. Thus, a simple random number (okay, and a timestamp of > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a > > totally sufficient safety mechanism to prevent starting with the wrong > > XLOG directory. > > But still, why set up a situation where your database might not > start? Why not set it up so that if you get just *one* environment > or command-line variable right, you can't set another inconsistently > and screw up your start anyway? Why store configuration information > outside of the database data directory in a form that's not easily > backed up, and not easily found by other utilities? > > It's almost like people *don't* want to put this in the config file > or something.... Curt, did you see my post about this earlier? I'll repeat it now, just in case anyone else missed it. Problem: - People need to move the pg_xlog directory around on heavily loaded systems to improve performance Constraints: - Windows can't reliably use links to do this. - If the pg_xlog directory is moved wrong or referenced incorrectly, data corruption may occur. This makes using a switch or environmental var dangerous I consider using a GUC in the postgresql.conf file to be better than any other option listed so far, but it is still a dangerous place for it to be. So, the way I think that would work best would be: If there's a directory called pg_xlog in the $PGDATA directory, then use that. If there's a file called pg_xlog in the $PGDATA directory, then it will contain the path to the real pg_xlog directory. If you want to move the pg_xlog directory, you called a custom script called "mvpgxlog" or something like it that: 1: Checks to make sure the database is shut down 2: Checks to make sure the destination path has enough free space for the xlogs 3: If these are both true (and whatever logic we need here for safety) then copy the current pg_xlog directory contents to the new pg_xlog (even if we are already using an alternative location, this should work), set proper permissions, rename / move the pg_xlog file / directorry, then edit/create the $PGDATA/pg_xlog file to point to the new directory. This method has several advantages, and no real disadvantages I can think of. The advantages are: - It makes it easy to move the pg_xlog directory. - It works equally well for Windows and Unix. - Gets rid of another GUC setting people can scram their database with. - It is easy to backup your pg_xlog setting. - If painted green it should not rust. How's that sound for a general theory of operation?
I don't see the gain of having a file called pg_xlog vs. using GUC. --------------------------------------------------------------------------- scott.marlowe wrote: > On Wed, 25 Sep 2002, Curt Sampson wrote: > > > On Tue, 24 Sep 2002, Jan Wieck wrote: > > > > > And AFAICS it is scary only because screwing that up will simply corrupt > > > your database. Thus, a simple random number (okay, and a timestamp of > > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a > > > totally sufficient safety mechanism to prevent starting with the wrong > > > XLOG directory. > > > > But still, why set up a situation where your database might not > > start? Why not set it up so that if you get just *one* environment > > or command-line variable right, you can't set another inconsistently > > and screw up your start anyway? Why store configuration information > > outside of the database data directory in a form that's not easily > > backed up, and not easily found by other utilities? > > > > It's almost like people *don't* want to put this in the config file > > or something.... > > Curt, did you see my post about this earlier? I'll repeat it now, just in > case anyone else missed it. > > Problem: > - People need to move the pg_xlog directory around on heavily > loaded systems to improve performance > > Constraints: > - Windows can't reliably use links to do this. > - If the pg_xlog directory is moved wrong or referenced incorrectly, data > corruption may occur. This makes using a switch or environmental var > dangerous > > I consider using a GUC in the postgresql.conf file to be better than any > other option listed so far, but it is still a dangerous place for it to > be. > > So, the way I think that would work best would be: > > If there's a directory called pg_xlog in the $PGDATA directory, then use > that. > > If there's a file called pg_xlog in the $PGDATA directory, then it will > contain the path to the real pg_xlog directory. > > If you want to move the pg_xlog directory, you called a custom script > called "mvpgxlog" or something like it that: > > 1: Checks to make sure the database is shut down > 2: Checks to make sure the destination path has enough free space for the > xlogs > 3: If these are both true (and whatever logic we need here for safety) > then copy the current pg_xlog directory contents to the new pg_xlog (even > if we are already using an alternative location, this should work), set > proper permissions, rename / move the pg_xlog file / directorry, then > edit/create the $PGDATA/pg_xlog file to point to the new directory. > > This method has several advantages, and no real disadvantages I can think > of. The advantages are: > > - It makes it easy to move the pg_xlog directory. > - It works equally well for Windows and Unix. > - Gets rid of another GUC setting people can scram their database with. > - It is easy to backup your pg_xlog setting. > - If painted green it should not rust. > > How's that sound for a general theory of operation? > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
I do. The problem is that if you change the location of pg_xlog and do one thing wrong, poof, your database is now corrupt. Like Tom said earlier, imagine a command like switch called "please-dont-scram-my-database" and if you ever forgot it then your data is gone. Is it better to move such a switch into the postgresql.conf file? Imagine a GUC setting called "butter-and-bread" that when set would delete all your data. That's what the equivalent here is, if you make a single mistake. Having a FILE called pg_xlog isn't the fix here, it's the result of the fix, which is to take all the steps of moving the pg_xlog directory and put them into one script file the user doesn't need to understand to do it right. I.e. idiot proof the system as much as possible. We could do it much simpler, if everyone was on Unix. We could just write a script that would do everything the same but instead of using a file called pg_xlog, would make a link. the reason for the file is to make it more transportable to brain damaged OSes like Windows. Do you really think the GUC variable is a safe way of referencing the pg_xlog directory all by itself? I can see MANY posts to the lists that will go like this: I just installed Postgresql 7.4 and it's been working fine. I needed more speed, so I looked up the GUC for the pg_xlog and set it to /vol/vol3/ on my machine. Now my database won't come up. I set it back but it still won't come up. What can I do to fix that? Here's the email we'd get from my solution: Hey, I just tried to move my pg_xlog directory with the mvpgxlog script, and it gave an error of "permission denied on destination". What does that mean? The choice is yours. On Wed, 25 Sep 2002, Bruce Momjian wrote: > > I don't see the gain of having a file called pg_xlog vs. using GUC. > > --------------------------------------------------------------------------- > > scott.marlowe wrote: > > On Wed, 25 Sep 2002, Curt Sampson wrote: > > > > > On Tue, 24 Sep 2002, Jan Wieck wrote: > > > > > > > And AFAICS it is scary only because screwing that up will simply corrupt > > > > your database. Thus, a simple random number (okay, and a timestamp of > > > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a > > > > totally sufficient safety mechanism to prevent starting with the wrong > > > > XLOG directory. > > > > > > But still, why set up a situation where your database might not > > > start? Why not set it up so that if you get just *one* environment > > > or command-line variable right, you can't set another inconsistently > > > and screw up your start anyway? Why store configuration information > > > outside of the database data directory in a form that's not easily > > > backed up, and not easily found by other utilities? > > > > > > It's almost like people *don't* want to put this in the config file > > > or something.... > > > > Curt, did you see my post about this earlier? I'll repeat it now, just in > > case anyone else missed it. > > > > Problem: > > - People need to move the pg_xlog directory around on heavily > > loaded systems to improve performance > > > > Constraints: > > - Windows can't reliably use links to do this. > > - If the pg_xlog directory is moved wrong or referenced incorrectly, data > > corruption may occur. This makes using a switch or environmental var > > dangerous > > > > I consider using a GUC in the postgresql.conf file to be better than any > > other option listed so far, but it is still a dangerous place for it to > > be. > > > > So, the way I think that would work best would be: > > > > If there's a directory called pg_xlog in the $PGDATA directory, then use > > that. > > > > If there's a file called pg_xlog in the $PGDATA directory, then it will > > contain the path to the real pg_xlog directory. > > > > If you want to move the pg_xlog directory, you called a custom script > > called "mvpgxlog" or something like it that: > > > > 1: Checks to make sure the database is shut down > > 2: Checks to make sure the destination path has enough free space for the > > xlogs > > 3: If these are both true (and whatever logic we need here for safety) > > then copy the current pg_xlog directory contents to the new pg_xlog (even > > if we are already using an alternative location, this should work), set > > proper permissions, rename / move the pg_xlog file / directorry, then > > edit/create the $PGDATA/pg_xlog file to point to the new directory. > > > > This method has several advantages, and no real disadvantages I can think > > of. The advantages are: > > > > - It makes it easy to move the pg_xlog directory. > > - It works equally well for Windows and Unix. > > - Gets rid of another GUC setting people can scram their database with. > > - It is easy to backup your pg_xlog setting. > > - If painted green it should not rust. > > > > How's that sound for a general theory of operation? > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > >
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't see the gain of having a file called pg_xlog vs. using GUC.
Well, the point is to have a safety interlock --- but I like Jan's
idea of using matching identification files in both directories.
With that, a GUC variable seems just fine.
regards, tom lane
On Wed, 25 Sep 2002, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I don't see the gain of having a file called pg_xlog vs. using GUC. > > Well, the point is to have a safety interlock --- but I like Jan's > idea of using matching identification files in both directories. > With that, a GUC variable seems just fine. Agreed, the interlock is a great idea. I hadn't seen that one go by.
Hi,
I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.
In a PL/pgSQL function I want to insert into a table and get the OID back.
That usually works with
GET DIAGNOSTICS last_oid = RESULT_OID;
right after the insert statement.
But if the table that I insert to has a rule (or perhaps a trigger?) that
updates another table, the RESULT_OID after the insert will be 0 (zero).
Can this be fixed (I have no such problem with JDBC and getLastOID())?
Testcase:
CREATE TABLE pltest ( id BIGINT default cs_nextval('invoice_invoice_id') NOT NULL, t TEXT, primary key (id)
);
CREATE TABLE plcounter ( counter INTEGER NOT NULL
);
CREATE FUNCTION pltestfunc(integer) RETURNS BOOLEAN AS'
DECLARE lastOID OID;
BEGIN FOR i IN 1..$1 LOOP INSERT INTO pltest (t) VALUES (\'test\'); GET DIAGNOSTICS lastOID = RESULT_OID; RAISE
NOTICE\'RESULT_OID: %\', lastOID; IF lastOID <= 0 THEN RAISE EXCEPTION \'RESULT_OID is zero\'; END IF; END
LOOP;RETURN true;
END;
' LANGUAGE 'plpgsql';
-- comment out the rule and the test will work
CREATE RULE pltest_insert AS ON INSERT TO pltest DO UPDATE plcounter SET counter=counter+1;
INSERT INTO plcounter VALUES (0);
SELECT pltestfunc(10);
SELECT * FROM pltest;
DROP FUNCTION pltestfunc(integer);
DROP TABLE pltest;
Regards,
Michael
Curt Sampson wrote: > > On Tue, 24 Sep 2002, Jan Wieck wrote: > > > And AFAICS it is scary only because screwing that up will simply corrupt > > your database. Thus, a simple random number (okay, and a timestamp of > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a > > totally sufficient safety mechanism to prevent starting with the wrong > > XLOG directory. > > But still, why set up a situation where your database might not > start? Why not set it up so that if you get just *one* environment > or command-line variable right, you can't set another inconsistently > and screw up your start anyway? Why store configuration information > outside of the database data directory in a form that's not easily > backed up, and not easily found by other utilities? With the number of screws our product has, there are so many possible combinations that don't work, why worry about one more or less? Seriously, if you move around files, make symlinks or adjust config variable to reflect that, there's allways the possibility that you fatfinger it and cannot startup. The point is not to make it pellethead-safe so that the damned thing will start allways, but to make it pellethead-safe so that an attempt to start with wrong settings doesn't blow away the whole server. > > It's almost like people *don't* want to put this in the config file > or something.... I want to have it it the config file. Just that that doesn't prevent anything. And if we have a "signature" file in the xlog and data directories, you can make it dummy-safe as you like ... if the config option is set wrong, first search for it on all drives before bailing out and if found, postmaster corrects the config setting. That way the admin can play hide and seek with our database ... ;-) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
"scott.marlowe" wrote: > Having a FILE called pg_xlog isn't the fix here, it's the result of the > fix, which is to take all the steps of moving the pg_xlog directory and > put them into one script file the user doesn't need to understand to do it > right. I.e. idiot proof the system as much as possible. And your script/program cannot modify postgresql.conf instead of creating a new file? Please remember: "A fool with a tool is still a fool". You can provide programs and scripts as many as you want. There have allways been these idiots who did stuff like truncating pg_log ... Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > > It's almost like people *don't* want to put this in the config file > > or something.... > > I want to have it it the config file. Just that that doesn't > prevent anything. And if we have a "signature" file in the xlog > and data directories, you can make it dummy-safe as you like ... > if the config option is set wrong, first search for it on all > drives before bailing out and if found, postmaster corrects the > config setting. That way the admin can play hide and seek with > our database ... ;-) Let's get it into GUC and see what problems people have. We may find out no one has difficulty. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, 25 Sep 2002, Jan Wieck wrote: > "scott.marlowe" wrote: > > > Having a FILE called pg_xlog isn't the fix here, it's the result of the > > fix, which is to take all the steps of moving the pg_xlog directory and > > put them into one script file the user doesn't need to understand to do it > > right. I.e. idiot proof the system as much as possible. > > And your script/program cannot modify postgresql.conf instead of > creating a new file? That's a minor point. It could be anywhere. It's just that much like a symlink is visible from the shell with a simple ls -l, so too is pg_xlog being a file an obvious sign that pg_xlog doesn't live here anymore. > Please remember: "A fool with a tool is still a fool". You can > provide programs and scripts as many as you want. There have > allways been these idiots who did stuff like truncating pg_log So, should we take out seatbelts from cars, safeties from guns, and have everyone run about with sharp sticks too? :-) I know that the second we make something more idiot proof, someone will make a better idiot, but that doesn't mean we shouldn't make things more idiot proof, we should just try to anticipate the majority of idiots (and let's face it, we can all be idiots at the right moments sometimes.) But, I have a few more questions about the signature file solution. Is the signature file going to be updated by date or something everytime the database is started up and shut down? If not, then it's quite possible that someone could copy the pg_xlog dir somewhere, run it for a while, then they change it back to the base pg_xlog will the database know that those xlogs are stale and not start up, or will it start up and corrupt the database with the old xlogs? As long as there's a time stamp in both places it should work fine.
scott.marlowe wrote: > On Wed, 25 Sep 2002, Jan Wieck wrote: > So, should we take out seatbelts from cars, safeties from guns, and have > everyone run about with sharp sticks too? :-) I know that the second we > make something more idiot proof, someone will make a better idiot, but > that doesn't mean we shouldn't make things more idiot proof, we should > just try to anticipate the majority of idiots (and let's face it, we can > all be idiots at the right moments sometimes.) Can we wait for someone to be injured in a car accident before putting in heavy seat belts? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"scott.marlowe" wrote: > [...] > But, I have a few more questions about the signature file solution. Is > the signature file going to be updated by date or something everytime the > database is started up and shut down? If not, then it's quite possible > that someone could copy the pg_xlog dir somewhere, run it for a while, > then they change it back to the base pg_xlog will the database know that > those xlogs are stale and not start up, or will it start up and corrupt > the database with the old xlogs? As long as there's a time stamp in both > places it should work fine. Good question. Actually, I think it'd be a perfect place and use for a copy of the controlfile. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Bruce Momjian wrote: > > scott.marlowe wrote: > > On Wed, 25 Sep 2002, Jan Wieck wrote: > > So, should we take out seatbelts from cars, safeties from guns, and have > > everyone run about with sharp sticks too? :-) I know that the second we > > make something more idiot proof, someone will make a better idiot, but > > that doesn't mean we shouldn't make things more idiot proof, we should > > just try to anticipate the majority of idiots (and let's face it, we can > > all be idiots at the right moments sometimes.) Sure, been there, done that ... > > Can we wait for someone to be injured in a car accident before putting > in heavy seat belts? About the car seatbelts I have a theory. If we would not have seatbelts, and instead of Airbags sharp sticks instantly killing the driver in the case of an accident, most of these wannabe Racing-Champs on our streets would either drive more reasonable or get removed by natural selection. Maybe the overall number of accidents would drop below the actual number of deaths in traffic (remember, we only kill the drivers on purpose, not anyone else in the car) ... and for sure the far lower number of *only* crippled or disabled victims will take a big burden off of the healthcare and wellfare system ... Okay, okay, enough proof of the first statement ... back to business. Jan B-) -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can we wait for someone to be injured in a car accident before putting
> in heavy seat belts?
Not the analogy you wanted to make ... if you knew there was a serious
risk, that's called negligence in most American courts. Ask Ford about
the Pinto ...
regards, tom lane
"Michael Paesold" <mpaesold@gmx.at> writes:
> I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS.
Hm. This seems to be SPI's version of the same definitional issue
we're contending with for status data returned from an interactive
query: SPI is currently set up to return the status of the last
querytree it executes, which is probably the wrong thing to do in the
presence of rule rewrites. But I'm hesitant to change SPI until we know
what we're going to do for interactive query status.
regards, tom lane
Tom Lane wrote: > "Michael Paesold" <mpaesold@gmx.at> writes: > > I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS. > > Hm. This seems to be SPI's version of the same definitional issue > we're contending with for status data returned from an interactive > query: SPI is currently set up to return the status of the last > querytree it executes, which is probably the wrong thing to do in the > presence of rule rewrites. But I'm hesitant to change SPI until we know > what we're going to do for interactive query status. > > regards, tom lane So this is not going to be fixed for 7.3 I suggest, no? Can you add the issue to the TODO list or can this thread be added to any appropriate TODO item? Regards, Michael Paesold
Michael Paesold wrote: > Tom Lane wrote: > > > "Michael Paesold" <mpaesold@gmx.at> writes: > > > I have come across a problem (bug?) with PL/pgSQL GET DIAGNOSTICS. > > > > Hm. This seems to be SPI's version of the same definitional issue > > we're contending with for status data returned from an interactive > > query: SPI is currently set up to return the status of the last > > querytree it executes, which is probably the wrong thing to do in the > > presence of rule rewrites. But I'm hesitant to change SPI until we know > > what we're going to do for interactive query status. > > > > regards, tom lane > > So this is not going to be fixed for 7.3 I suggest, no? Can you add the > issue to the TODO list or can this thread be added to any appropriate TODO > item? I already have a TODO item: * Return proper effected tuple count from complex commands [return] I am unsure if it will be fixed in 7.3 or not. It is still on the open items list, and I think we have a general plan to fix it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I already have a TODO item:
> * Return proper effected tuple count from complex commands [return]
> I am unsure if it will be fixed in 7.3 or not. It is still on the open
> items list, and I think we have a general plan to fix it.
I got distracted and wasn't following the thread a few days ago about
the topic. Did people come to a consensus about how it should work?
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I already have a TODO item: > > * Return proper effected tuple count from complex commands [return] > > I am unsure if it will be fixed in 7.3 or not. It is still on the open > > items list, and I think we have a general plan to fix it. > > I got distracted and wasn't following the thread a few days ago about > the topic. Did people come to a consensus about how it should work? Well, sort of. It was similar to your original proposal. See the TODO link for details. I am heading out for 2 hours and will summarize when I return. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, 25 Sep 2002, Jan Wieck wrote: > With the number of screws our product has, there are so many > possible combinations that don't work, why worry about one more > or less? That's just silly, so I won't even bother replying. > Seriously, if you move around files, make symlinks or adjust > config variable to reflect that, there's allways the possibility > that you fatfinger it and cannot startup. True. But once your symlink is in place, it is stored on disk in the postgres data directory. An environment variable is a transient setting in memory, which means that you have to have a program set it, and you have to make sure that program gets run before any startup, be it an automated startup from /etc/rc on boot or a manual startup. > I want to have it it the config file. Well, then we're agreed. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I already have a TODO item: > > * Return proper effected tuple count from complex commands [return] > > I am unsure if it will be fixed in 7.3 or not. It is still on the open > > items list, and I think we have a general plan to fix it. > > I got distracted and wasn't following the thread a few days ago about > the topic. Did people come to a consensus about how it should work? OK, I am back. I think the most promising proposal was from you, Tom: http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html It basically breaks down the three results (tag, oid, tuple count), and the INSTEAD/non-INSTEAD behavior. I actually got a big chuckle from this paragraph: Come on, guys, work with me a little here. I've thrown out severalalternative suggestions already, and all I've gotten fromeither ofyou is refusal to think about the problem. I liked the "work with me" phrase. To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of the original query. Everyone agrees on that. For non-INSTEAD, we have: 1) return original tag2) return oid if all inserts in the rule insert only one row3) return tuple count of all commands withthe same tag For item 2, it is possible to have multiple INSERTS in the rule and return an oid if the sum of the inserts is only one row. Item 3 is the most controversial. Some say sum all tuple counts, i.e. sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think summing only the matching tags has the highest probability of returning a meaningful number. Also, item 2 and 3 work well together with INSERT because a tuple count of 1 returns an oid, while > 1 does not, which is consistent with a non-rule insert. (FYI, I am still working SSL.) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > OK, I am back. I think the most promising proposal was from you, Tom: > http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html But that wasn't a specific proposal --- it was more or less an enumeration of the possibilities. What are we picking? regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > OK, I am back. I think the most promising proposal was from you, Tom: > > http://candle.pha.pa.us/mhonarc/todo.detail/return/msg00012.html > > But that wasn't a specific proposal --- it was more or less an > enumeration of the possibilities. What are we picking? The rest of my message explains your poposal while clarifying certain options you gave in the email. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, 25 Sep 2002 21:40:03 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us> wrote: >Item 3 is the most controversial. Some say sum all tuple counts, i.e. >sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think >summing only the matching tags has the highest probability of returning >a meaningful number. [Trying to keep it short this time] I still believe that there is more than one correct answer; it just depends on what the dba intends. So I proposed a syntax change for letting the dba explicitly mark the statements she/he wants to affect tuple count and oid. -> http://archives.postgresql.org/pgsql-hackers/2002-09/msg00720.php Unfortunately I tried to summarize all other proposals and the mail got so long that nobody read it to the end :-( ServusManfred
Curt Sampson wrote: > > On Wed, 25 Sep 2002, Jan Wieck wrote: > > > With the number of screws our product has, there are so many > > possible combinations that don't work, why worry about one more > > or less? > > That's just silly, so I won't even bother replying. Curt, it might sound silly on first sight and isolated. But it was in reply to: >>> But still, why set up a situation where your database might not >>> start? Why not set it up so that if you get just *one* environment >>> or command-line variable right, you can't set another inconsistently >>> and screw up your start anyway? Why store configuration information >>> outside of the database data directory in a form that's not easily >>> backed up, and not easily found by other utilities? Apply that argumentation to all of our commandline switches and config options and we end up with something that behaves like Microsoft products ... they know everything better, you cannot tune them, they work ... and you needed a bigger machine anyway. I am absolutely not in favour of the PGXLOG environment variable. But if someone else wants it, it doesn't bother me because I wouldn't use it and it cannot hurt me. I am simply against this "I think it's wrong so you have to change your behaviour" attitude. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Thu, 26 Sep 2002, Jan Wieck wrote: > >>> But still, why set up a situation where your database might not > >>> start? Why not set it up so that if you get just *one* environment > >>> or command-line variable right, you can't set another inconsistently > >>> and screw up your start anyway? Why store configuration information > >>> outside of the database data directory in a form that's not easily > >>> backed up, and not easily found by other utilities? > > Apply that argumentation to all of our commandline switches and config > options and we end up with something that behaves like Microsoft > products ... they know everything better, you cannot tune them, they > work ... and you needed a bigger machine anyway. Talk about a straw man! I have repeatedly said: I WANT THE FEATURE THAT LETS YOU TUNE THE LOCATION OF THE LOG FILE! Read it again, and again, until you understand that we both want that feature. Then realize, I just want it implemented in a way that makes it less likely that people will find themselves in a situation where the server doesn't start. > I am absolutely not in favour of the PGXLOG environment variable. But if > someone else wants it, it doesn't bother me because I wouldn't use it > and it cannot hurt me. Responsible programmers, when confronted with a more accident-prone and less accident-prone way of doing something, chose the less accident-prone way of doing things. That way people who are naive, or tired, or just having a bad day are less likely to come to harm. Using the config file is not only safer, it's actually more convenient. And since we're going to have the config file option anyway, removing the environment variable option means that others have less documentation to read, and will spend less time wondering why there's two different ways to do the same thing. And naive people won't chose the wrong way because they don't know any better. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
Manfred Koizar wrote: > On Wed, 25 Sep 2002 21:40:03 -0400 (EDT), Bruce Momjian > <pgman@candle.pha.pa.us> wrote: > >Item 3 is the most controversial. Some say sum all tuple counts, i.e. > >sum INSERT/UPDATE/DELETE. That just seems to messy to me. I think > >summing only the matching tags has the highest probability of returning > >a meaningful number. > > [Trying to keep it short this time] > > I still believe that there is more than one correct answer; it just > depends on what the dba intends. So I proposed a syntax change for > letting the dba explicitly mark the statements she/he wants to affect > tuple count and oid. > > -> http://archives.postgresql.org/pgsql-hackers/2002-09/msg00720.php > > Unfortunately I tried to summarize all other proposals and the mail > got so long that nobody read it to the end :-( That is an interesting idea; some syntax in the rule that marks the items. The one downside to that is the fact the rule writer has to make adjustments. Perhaps we could implement the behavoir I described and add such tagging later. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian writes: > To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of > the original query. Everyone agrees on that. > > For non-INSTEAD, we have: [I think this is the INSTEAD part.] > 1) return original tag > 2) return oid if all inserts in the rule insert only one row > 3) return tuple count of all commands with the same tag I think proper encapsulation would require us to simulate the original command, hiding the fact that something else happened internally. I know it's really hard to determine the "virtual" count of an update or delete if the command had acted on a permament base table, but I'd rather maintain the encapsulation of updateable views and return "unknown" in that case. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > Bruce Momjian writes: > > > To summarize, with non-INSTEAD, we get the tag, oid, and tuple count of > > the original query. Everyone agrees on that. > > > > For non-INSTEAD, we have: > > [I think this is the INSTEAD part.] Sorry, yes. > > 1) return original tag > > 2) return oid if all inserts in the rule insert only one row > > 3) return tuple count of all commands with the same tag > > I think proper encapsulation would require us to simulate the original > command, hiding the fact that something else happened internally. I know > it's really hard to determine the "virtual" count of an update or delete > if the command had acted on a permament base table, but I'd rather > maintain the encapsulation of updateable views and return "unknown" in > that case. Well, let's look at the common case. For proper view rules, these would all return the right values because the UPDATE in the rule would be returned. Is that what you mean? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian writes: > Well, let's look at the common case. For proper view rules, these would > all return the right values because the UPDATE in the rule would be > returned. Is that what you mean? I guess that really depends on whether the rules are written to properly constrain the writes to the view to the set of rows visible by the view. For example, if a view v1 selects from a single table t1 constrained by a search condition, and I do UPDATE v1 SET ...; without a condition, does that affect all rows in t1? If not, then both our proposals are equivalent, if yes, then the it's the user's fault, I suppose. -- Peter Eisentraut peter_e@gmx.net
Hi everyone, In order to clarify things, how about we do a formal vote with specific details like this: ******* Are you for... - pg_xlog directory changeable at all, not using symlinks? Yes/No - a PGXLOG environment variable to do this? Yes/No - a -X command line option to do this? Yes/No - a GUC (postgresql.conf) option to do this? Yes/No - altering the format of the pg_xlog directory so that it can't be used with the wrong database instance? Yes/No ******* Does this seem reasonable? :-) Regards and best wishes, Justin Clift -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
Peter Eisentraut wrote: > Bruce Momjian writes: > > > Well, let's look at the common case. For proper view rules, these would > > all return the right values because the UPDATE in the rule would be > > returned. Is that what you mean? > > I guess that really depends on whether the rules are written to properly > constrain the writes to the view to the set of rows visible by the view. > For example, if a view v1 selects from a single table t1 constrained by a > search condition, and I do UPDATE v1 SET ...; without a condition, does > that affect all rows in t1? If not, then both our proposals are > equivalent, if yes, then the it's the user's fault, I suppose. Well, since we found that we can't get a perfect solution, I started to think of the common cases. First, there is the "log changes" type of rule, but that isn't INSTEAD, so it doesn't even apply here. We already know we want to return the result of the main query.CREATE RULE service_request_update AS -- UPDATE ruleON UPDATE TOservice_request DO INSERT INTO service_request_log (customer_id, description, mod_type) VALUES (old.customer_id,old.description, 'U');CREATE RULE service_request_delete AS -- DELETE ruleON DELETE TO service_requestDO INSERT INTO service_request_log (customer_id, description, mod_type) VALUES (old.customer_id, old.description,'D'); Second, there is the updatable view rule, that is INSTEAD, and relies on the primary key of the table:CREATE RULE view_realtable_insert AS -- INSERT ruleON INSERT TO view_realtable DO INSTEAD INSERT INTO realtable VALUES (new.col);CREATE RULE view_realtable_update AS -- UPDATE ruleON UPDATE TOview_realtable DO INSTEAD UPDATE realtable SET col = new.col WHERE col = old.col;CREATE RULE view_realtable_deleteAS -- DELETE ruleON DELETE TO view_realtable DO INSTEAD DELETE FROM realtable WHERE col= old.col; It is my understanding that the proposed rule result improvements will return the proper values in these cases. That is why I like the current proposal. It also makes any extra non-tag matching queries in the rule not affect the result, which seems best. Does anyone else have a common rule that would return incorrect results using the proposed rules? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Sat, 28 Sep 2002 13:41:04 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us> wrote: >Does anyone else have a common rule that would return incorrect results >using the proposed rules? CREATE VIEW twotables ASSELECT ... FROM table1 INNER JOIN table2 ON ... ; CREATE RULE twotables_insert AS -- INSERT ruleON INSERT TO twotables DO INSTEAD ( INSERT INTO table1 VALUES (new.pk,new.col1); INSERT INTO table2 VALUES (new.pk, new.col2)); CREATE RULE twotables_update AS -- UPDATE ruleONUPDATE TO twotables DO INSTEAD ( UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk; UPDATE table2 SET col2= new.col2 WHERE pk = old.pk); CREATE RULE twotables_delete AS -- DELETE ruleON DELETE TO twotables DO INSTEAD ( DELETE FROM table1 WHERE pk = old.pk; DELETE FROM table2 WHERE pk = old.pk); CREATE VIEW visible ASSELECT ... FROM table3WHERE deleted = 0; CREATE RULE visible_delete AS -- DELETE ruleON DELETE TO visible DO INSTEAD UPDATE table3 SET deleted = 1 WHEREpk = old.pk; ServusManfred
OK, that is a good example. It would return the sum of the matching tags. You are suggesting here that it would be better to take the result of the last matching tag command, right? --------------------------------------------------------------------------- Manfred Koizar wrote: > On Sat, 28 Sep 2002 13:41:04 -0400 (EDT), Bruce Momjian > <pgman@candle.pha.pa.us> wrote: > >Does anyone else have a common rule that would return incorrect results > >using the proposed rules? > > CREATE VIEW twotables AS > SELECT ... FROM table1 INNER JOIN table2 ON ... ; > > CREATE RULE twotables_insert AS -- INSERT rule > ON INSERT TO twotables > DO INSTEAD ( > INSERT INTO table1 VALUES (new.pk, new.col1); > INSERT INTO table2 VALUES (new.pk, new.col2) > ); > > CREATE RULE twotables_update AS -- UPDATE rule > ON UPDATE TO twotables > DO INSTEAD ( > UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk; > UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk > ); > > CREATE RULE twotables_delete AS -- DELETE rule > ON DELETE TO twotables > DO INSTEAD ( > DELETE FROM table1 WHERE pk = old.pk; > DELETE FROM table2 WHERE pk = old.pk > ); > > CREATE VIEW visible AS > SELECT ... FROM table3 > WHERE deleted = 0; > > CREATE RULE visible_delete AS -- DELETE rule > ON DELETE TO visible > DO INSTEAD > UPDATE table3 > SET deleted = 1 > WHERE pk = old.pk; > > Servus > Manfred > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Sat, 28 Sep 2002 19:20:43 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us> wrote: >OK, that is a good example. It would return the sum of the matching >tags. You are suggesting here that it would be better to take the >result of the last matching tag command, right? The examples were meant to support my previous suggestion of explicitly marking the statement you want to be counted, something like: CREATE VIEW twotables ASSELECT ... FROM table1 INNER JOIN table2 ON ... ; CREATE RULE twotables_insert AS -- INSERT ruleON INSERT TO twotables DO INSTEAD ( COUNT INSERT INTO table1 VALUES(new.pk, new.col1); INSERT INTO table2 VALUES (new.pk, new.col2)); CREATE RULE twotables_update AS -- UPDATEruleON UPDATE TO twotables DO INSTEAD ( COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk; UPDATE table2SET col2 = new.col2 WHERE pk = old.pk); CREATE RULE twotables_delete AS -- DELETE ruleON DELETE TO twotables DOINSTEAD ( COUNT DELETE FROM table1 WHERE pk = old.pk; DELETE FROM table2 WHERE pk = old.pk); CREATE VIEW visible ASSELECT ... FROM table3WHERE deleted = 0; CREATE RULE visible_delete AS -- DELETE ruleON DELETE TO visible DO INSTEAD COUNT UPDATE table3 SET deleted =1 WHERE pk = old.pk; One argument against automatically "don't count non-INSTEAD rules and count the last statement in INSTEAD rules": sql-createrule.html says: | for view updates: there must be an unconditional INSTEAD rule [...] | If you want to handle all the useful cases in conditional rules, you | can; just add an unconditional DO INSTEAD NOTHING rule [...] | Then make the conditional rules non-INSTEAD CREATE RULE v_update AS -- UPDATE ruleON UPDATE TO v DO INSTEAD NOTHING; CREATE RULE v_update2 AS -- UPDATE ruleON UPDATE TO v WHERE <condition1>DO ( COUNT ...); CREATE RULE v_update3 AS -- UPDATE ruleON UPDATE TO v WHERE <condition2>DO ( COUNT ...); ServusManfred
We have talked about possible return values for RULES, particularly INSTEAD rule. Manfred has a nice example here, so I propose we handle INSTEAD rules this way: that we return the oid and tuple count of the last INSTEAD rule query with a tag matching the main query. The returned tag, of course, would be the tag of the main query. This works for Manfred's case, and it works for my case when there is only one action in the INSTEAD rule. If there is more than one matching tag in the INSTEAD rule, the user has the option to place the query he wants for the return at the end of the rule. This does give the user some control over what is returned. Comments? I think non-INSTEAD rules already return the tag, oid, and tuple count of the main query, right? --------------------------------------------------------------------------- Manfred Koizar wrote: > On Sat, 28 Sep 2002 19:20:43 -0400 (EDT), Bruce Momjian > <pgman@candle.pha.pa.us> wrote: > >OK, that is a good example. It would return the sum of the matching > >tags. You are suggesting here that it would be better to take the > >result of the last matching tag command, right? > > The examples were meant to support my previous suggestion of > explicitly marking the statement you want to be counted, something > like: > > CREATE VIEW twotables AS > SELECT ... FROM table1 INNER JOIN table2 ON ... ; > > CREATE RULE twotables_insert AS -- INSERT rule > ON INSERT TO twotables > DO INSTEAD ( > COUNT INSERT INTO table1 VALUES (new.pk, new.col1); > INSERT INTO table2 VALUES (new.pk, new.col2) > ); > > CREATE RULE twotables_update AS -- UPDATE rule > ON UPDATE TO twotables > DO INSTEAD ( > COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk; > UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk > ); > > CREATE RULE twotables_delete AS -- DELETE rule > ON DELETE TO twotables > DO INSTEAD ( > COUNT DELETE FROM table1 WHERE pk = old.pk; > DELETE FROM table2 WHERE pk = old.pk > ); > > CREATE VIEW visible AS > SELECT ... FROM table3 > WHERE deleted = 0; > > CREATE RULE visible_delete AS -- DELETE rule > ON DELETE TO visible > DO INSTEAD > COUNT UPDATE table3 > SET deleted = 1 > WHERE pk = old.pk; > > One argument against automatically "don't count non-INSTEAD rules and > count the last statement in INSTEAD rules": sql-createrule.html says: > | for view updates: there must be an unconditional INSTEAD rule [...] > | If you want to handle all the useful cases in conditional rules, you > | can; just add an unconditional DO INSTEAD NOTHING rule [...] > | Then make the conditional rules non-INSTEAD > > CREATE RULE v_update AS -- UPDATE rule > ON UPDATE TO v > DO INSTEAD NOTHING; > > CREATE RULE v_update2 AS -- UPDATE rule > ON UPDATE TO v WHERE <condition1> > DO ( > COUNT ... > ); > > CREATE RULE v_update3 AS -- UPDATE rule > ON UPDATE TO v WHERE <condition2> > DO ( > COUNT ... > ); > > Servus > Manfred > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We have talked about possible return values for RULES, particularly
> INSTEAD rule. Manfred has a nice example here, so I propose we handle
> INSTEAD rules this way: that we return the oid and tuple count of the
> last INSTEAD rule query with a tag matching the main query.
Hmm ... that's subtly different from what I'd seen discussed before.
I thought the idea was
1. If no INSTEAD rule: return tag, count, and OID of original query, regardless of what is added by non-INSTEAD
rules. (I think this part is not controversial.)2. If any INSTEAD rule: return tag, count, and OID of the last
executedquery that has the same tag as the original query. If no substituted query matches the original query's tag,
return original query's tag with zero count and OID. (This is where the going gets tough.)
I think you just modified the second part of that to restrict it to
queries that were added by INSTEAD rules. This is doable but it's
not a trivial change --- in particular, I think it implies adding
another field to Query data structure so we can mark INSTEAD-added
vs non-INSTEAD-added queries. Which means an initdb because it breaks
stored rules.
Offhand I think this might be worth doing, because I like that subtle
change in behavior. But we should understand exactly what we're doing
here...
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > We have talked about possible return values for RULES, particularly > > INSTEAD rule. Manfred has a nice example here, so I propose we handle > > INSTEAD rules this way: that we return the oid and tuple count of the > > last INSTEAD rule query with a tag matching the main query. > > Hmm ... that's subtly different from what I'd seen discussed before. > I thought the idea was > > 1. If no INSTEAD rule: return tag, count, and OID of original > query, regardless of what is added by non-INSTEAD rules. > (I think this part is not controversial.) > 2. If any INSTEAD rule: return tag, count, and OID of the last > executed query that has the same tag as the original query. > If no substituted query matches the original query's tag, > return original query's tag with zero count and OID. > (This is where the going gets tough.) > > I think you just modified the second part of that to restrict it to > queries that were added by INSTEAD rules. This is doable but it's > not a trivial change --- in particular, I think it implies adding > another field to Query data structure so we can mark INSTEAD-added > vs non-INSTEAD-added queries. Which means an initdb because it breaks > stored rules. I am confused how yours differs from mine. I don't see how the last matching tagged query would not be from an INSTEAD rule. Are you thinking multiple queries in the query string? > Offhand I think this might be worth doing, because I like that subtle > change in behavior. But we should understand exactly what we're doing > here... Seems we are adding up reasons for initdb. :-) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am confused how yours differs from mine. I don't see how the last
> matching tagged query would not be from an INSTEAD rule.
You could have both INSTEAD and non-INSTEAD rules firing for the same
original query. If the alphabetically-last rule is a non-INSTEAD rule,
then there's a difference.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I am confused how yours differs from mine. I don't see how the last > > matching tagged query would not be from an INSTEAD rule. > > You could have both INSTEAD and non-INSTEAD rules firing for the same > original query. If the alphabetically-last rule is a non-INSTEAD rule, > then there's a difference. How do we get multiple rules on a query? I thought it was mostly INSERT/UPDATE/DELETE, and those all operate on a single table. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am confused how yours differs from mine. I don't see how the last
> matching tagged query would not be from an INSTEAD rule.
>>
>> You could have both INSTEAD and non-INSTEAD rules firing for the same
>> original query. If the alphabetically-last rule is a non-INSTEAD rule,
>> then there's a difference.
> How do we get multiple rules on a query? I thought it was mostly
> INSERT/UPDATE/DELETE, and those all operate on a single table.
You can create as many rules as you want. One reasonably likely
scenario is that you have a view, you make an ON INSERT DO INSTEAD
rule to support insertions into the view (by inserting into some
underlying table(s) instead), and then you add some not-INSTEAD
rules to perform logging into other tables that aren't part of the
view but just keep track of activity.
You'd not want the logging activity to usurp the count result for this
setup, I think, even if it happened last. (Indeed, that might be
*necessary*, if for some reason it needed to access the rows inserted
into the view's base table.)
This approach would give us a general principle that applies in all
cases: not-INSTEAD rules don't affect the returned command result.
Perhaps that would answer Manfred's thought that we should be able
to label which rules affect the result. If you have any INSTEAD rules,
then it doesn't matter exactly how many you have, so you can mark them
INSTEAD or not to suit your fancy.
regards, tom lane
On Thu, 3 Oct 2002 22:21:27 -0400 (EDT), Bruce Momjian <pgman@candle.pha.pa.us> wrote: >so I propose we handle >INSTEAD rules this way: that we return the oid and tuple count of the >last INSTEAD rule query with a tag matching the main query. Bruce, this won't work for this example >> CREATE RULE visible_delete AS -- DELETE rule >> ON DELETE TO visible >> DO INSTEAD >> COUNT UPDATE table3 >> SET deleted = 1 >> WHERE pk = old.pk; because here we don't have a rule query with a matching tag. Same applies for >> CREATE RULE v_update AS -- UPDATE rule >> ON UPDATE TO v >> DO INSTEAD NOTHING; I wrote: >> One argument against automatically "don't count non-INSTEAD rules and >> count the last statement in INSTEAD rules" Seems I introduced a little bit of confusion here by argueing against something that has never been proposed before. Funny, that this non-existent proposal is now seriously discussed :-( Has the idea of extending the syntax to explicitly mark queries as COUNTed already been rejected? If yes, I cannot help here. If no, I keep telling you that this approach can emulate most of the other possible solutions still under discussion. Bruce wrote: >If there is more than one matching tag in >the INSTEAD rule, the user has the option to place the query he wants >for the return at the end of the rule. Are you sure this is always possible without unwanted side effects? ServusManfred
Tom Lane wrote: > You can create as many rules as you want. One reasonably likely > scenario is that you have a view, you make an ON INSERT DO INSTEAD > rule to support insertions into the view (by inserting into some > underlying table(s) instead), and then you add some not-INSTEAD > rules to perform logging into other tables that aren't part of the > view but just keep track of activity. > > You'd not want the logging activity to usurp the count result for this > setup, I think, even if it happened last. (Indeed, that might be > *necessary*, if for some reason it needed to access the rows inserted > into the view's base table.) > > This approach would give us a general principle that applies in all > cases: not-INSTEAD rules don't affect the returned command result. > Perhaps that would answer Manfred's thought that we should be able > to label which rules affect the result. If you have any INSTEAD rules, > then it doesn't matter exactly how many you have, so you can mark them > INSTEAD or not to suit your fancy. Oh, I like that, and rules fire alphabetically, right? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Manfred Koizar wrote: > On Thu, 3 Oct 2002 22:21:27 -0400 (EDT), Bruce Momjian > <pgman@candle.pha.pa.us> wrote: > >so I propose we handle > >INSTEAD rules this way: that we return the oid and tuple count of the > >last INSTEAD rule query with a tag matching the main query. > > Bruce, this won't work for this example > > >> CREATE RULE visible_delete AS -- DELETE rule > >> ON DELETE TO visible > >> DO INSTEAD > >> COUNT UPDATE table3 > >> SET deleted = 1 > >> WHERE pk = old.pk; > > because here we don't have a rule query with a matching tag. Same > applies for True, but because we have said we are going to return the tag of the original command, I don't think we have anything valid to return in this case to match the tag. > >> CREATE RULE v_update AS -- UPDATE rule > >> ON UPDATE TO v > >> DO INSTEAD NOTHING; This is OK because the default is return zeros. > I wrote: > >> One argument against automatically "don't count non-INSTEAD rules and > >> count the last statement in INSTEAD rules" > > Seems I introduced a little bit of confusion here by argueing against > something that has never been proposed before. Funny, that this > non-existent proposal is now seriously discussed :-( > > Has the idea of extending the syntax to explicitly mark queries as > COUNTed already been rejected? If yes, I cannot help here. If no, I Well, I am hoping to find something that was automatic. If we do our best, and we still get complains, we can add some syntax. I am concerned that adding syntax is just over-designing something that isn't necessary. > keep telling you that this approach can emulate most of the other > possible solutions still under discussion. > > Bruce wrote: > >If there is more than one matching tag in > >the INSTEAD rule, the user has the option to place the query he wants > >for the return at the end of the rule. > > Are you sure this is always possible without unwanted side effects? I am sure it isn't always possible, but let's do our best and see how people react. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"Michael Paesold" <mpaesold@gmx.at> writes:
> In a PL/pgSQL function I want to insert into a table and get the OID back.
> That usually works with
> GET DIAGNOSTICS last_oid = RESULT_OID;
> right after the insert statement.
> But if the table that I insert to has a rule (or perhaps a trigger?) that
> updates another table, the RESULT_OID after the insert will be 0 (zero).
As of CVS tip, this example produces the results I believe you want:
regression=# SELECT pltestfunc(10);
NOTICE: RESULT_OID: 282229
NOTICE: RESULT_OID: 282230
NOTICE: RESULT_OID: 282231
NOTICE: RESULT_OID: 282232
NOTICE: RESULT_OID: 282233
NOTICE: RESULT_OID: 282234
NOTICE: RESULT_OID: 282235
NOTICE: RESULT_OID: 282236
NOTICE: RESULT_OID: 282237
NOTICE: RESULT_OID: 282238pltestfunc
------------t
(1 row)
regression=# SELECT * FROM pltest;id | t
----+------ 1 | test 2 | test 3 | test 4 | test 5 | test 6 | test 7 | test 8 | test 9 | test10 | test
(10 rows)
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Michael Paesold" <mpaesold@gmx.at> writes: > > In a PL/pgSQL function I want to insert into a table and get the OID back. > > That usually works with > > GET DIAGNOSTICS last_oid = RESULT_OID; > > right after the insert statement. > > > But if the table that I insert to has a rule (or perhaps a trigger?) that > > updates another table, the RESULT_OID after the insert will be 0 (zero). > > As of CVS tip, this example produces the results I believe you want: > > regression=# SELECT pltestfunc(10); > NOTICE: RESULT_OID: 282229 > NOTICE: RESULT_OID: 282230 > NOTICE: RESULT_OID: 282231 ... [snip] That is very good news. I appreciate that you got it fixed for 7.3. I am sure I am only one of many who have use for that, but thanks anyway! Best Regards, Michael Paesold