Re: [HACKERS] Musings
... (1) Major version upgrade. This is a hard one, having to dump out and restore a database to go from 7.1 to 7.2 or 7.2 to 7.3 is really a hard sell. Hmm, maybe it would be more acceptable if we charged $40k per license, but refunded $40k if you *want* to dump/reload. Gets that motivation level up a bit... ;) - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] ECPG: FETCH ALL|n FROM cursor - Memory allocation?
Michael Meskes wrote: On Thu, Apr 25, 2002 at 12:42:00PM +0100, Lee Kindness wrote: Should the input pointers be NULL initialised? How should the memory be freed? A simple free() will do. You also can free all automatically allocated memory from the most recent executed statement by calling ECPGfree_auto_mem(). But this is not documented and will never be. The correct way is to free(array1) and free(array2) while libecpg will free the internal structures when the next statement is executed. Never, never mix these two! ECPGfree_auto_mem will free even memory which has already been free'd by the user, perhaps we should get rid of this method (any allocated memory regions are stored in a list, if you never call ECPGfree_auto_mem, this list grows and grows). Christof ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Marc G. Fournier wrote: hr ... do you have a working Windows development environment? I'm running WinXP at home, but don't have any of the compilers or anything yet, so all my work for the first part is going to be done under Unix ... but someone that knows something about building makefiles for Windows, and compiling under it, will definitely be a major asset ;) I think if you are familiar with make and gcc (and perhaps autoconf), MinGW and MSys are the development environment of choice on Windows. You even get /bin/sh. But the generated program does not depend on any custom library (like cygwin does). It's even possible to cross compile from a Linux box (actully powerpc in my case). Look at http://mingw.sourceforge.net (and there for msys). Christof ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Native Windows, Apache Portable Runtime
Cyril VELTER [EMAIL PROTECTED] writes: Also why not do the header fillup outside of PGSharedMemoryCreate ? Well, (a) I wasn't really concerned about defining an all-new API for shmem, and (b) I think the header is largely dependent on the semantics of SysV shmem anyway. A different shmem implementation might need different fields in there. Are the PGShmemHeader fields only used by PGSharedMemoryCreate ? What about using an API similar to the sema one : PGShmemHeader * PGSharedMemoryCreate(PGShmem shmem,uint32 size, bool makePrivate, int memKey); bool PGSharedMemoryIsInUse(PGShmem shmem); How does that solve the problem of determining whether a *previously* created shmem block is still in use? The point here is to be able to trace the connection from a data directory to active backends via their connections to a shared memory block --- take a look at RecordSharedMemoryInLockFile, which is the flip side of SharedMemoryIsInUse. Ok, I overlooked that, my proposal for PGSharedMemoryIsInUse doesn't make sense (and it doesn't matter on Beos because shared mem segments are automaticaly reaped at the end of the process). On a side note, after these API change, Beos will still need an Hack for shared memory, because all shared memory segments are in copy on write mode in the forked process. One solution could be to have an explicit attach call in the forked process : PGSharedMemoryAttach(PGShmem shmem); No strong feelings about this --- it looks like the same BeOS-specific hack under a different name ;-) This will allow the following calls for each fork to removed : beos_before_backend_startup beos_backend_startup_failed beos_backend_startup How so? If those calls were needed before, why won't all three still be needed? In the current hack, I've to iterate over all sharedmem segments (system wide) to find the original one by name. There is a race condition here if several backend are starting at the same time. beos_before_backend_startup beos_backend_startup_failed acquire / release a semaphore which prevent several fork at the same time. With the proposed API, I would be able to store some specific info about the shared mem segment (the beos handle of the original one created by postmaster) which will be accessible to the backend after the fork. This will remove the race condition and the need of the three calls. This will also improve mutliple backend startup time cause now forks are serialized. This was just a though, current implementation works fine. cyril ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Native Windows, Apache Portable Runtime
On Sat, 2002-05-04 at 21:56, Tom Lane wrote: mlw [EMAIL PROTECTED] writes: We could provide a PGSemaphore based on an APR mutex and a counter, but I'm not sure of the performance impact. We may want to implement a generic semaphore like this and one optimized for platforms which we have development resources. Once we have the internal API redone, it should be fairly easy to experiment with alternatives like that. I'm planning to work on this today (need a break from thinking about schemas ;-)). I'll run with the API I sketched yesterday, since no one objected. Although I'm not planning on doing anything to the API of the shared-mem routines, I'll break them out into a replaceable file as well, just in case anyone wants to try a non-SysV implementation. Would it be too hard to make them macros, so those which dont need shared mem at all (embedded single-user systems) could avoid the performance impact altogether. Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] ECPG: FETCH ALL|n FROM cursor - Memory allocation?
Okay, lets see if i've got this right... If I allocate the memory before the FETCH then I (naturally) free it. However If I NULL initialise the pointer then libecpg will allocate the memory and I must NOT free it - libecpg will free it automatically... Yeah? I think this highlights the need for some documentation on this aspect. Regards, Lee Kindness. Christof Petig writes: Michael Meskes wrote: On Thu, Apr 25, 2002 at 12:42:00PM +0100, Lee Kindness wrote: Should the input pointers be NULL initialised? How should the memory be freed? A simple free() will do. You also can free all automatically allocated memory from the most recent executed statement by calling ECPGfree_auto_mem(). But this is not documented and will never be. The correct way is to free(array1) and free(array2) while libecpg will free the internal structures when the next statement is executed. Never, never mix these two! ECPGfree_auto_mem will free even memory which has already been free'd by the user, perhaps we should get rid of this method (any allocated memory regions are stored in a list, if you never call ECPGfree_auto_mem, this list grows and grows). Christof ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [INTERFACES] ECPG: FETCH ALL|n FROM cursor - Memory allocation?
On Mon, May 06, 2002 at 09:37:18AM +0200, Christof Petig wrote: Never, never mix these two! ECPGfree_auto_mem will free even memory which has already been free'd by the user, perhaps we should get rid of That's why I discourage the usage of ECPGfree_auto_mem by the user. There is only one reason why the symbol is not static and that is that it is used by another module in libecpg. I never thought about this as an end user routine, it's just meant as a clean up method in case of an error during statement execution. BTW Christof, ECPGfree_auto_mem is used by testdynalloc.pgc. Maybe we should change that. this method (any allocated memory regions are stored in a list, if you never call ECPGfree_auto_mem, this list grows and grows). That is not true. Before a statement is executed libecpg calls ECPGclear_auto_mem which just frees ecpg's own structure but not the memory used for data. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Rather than propagating the SysV semaphore API still further, why don't we kill it now? (I'm willing to keep the shmem API, however.) Would this have the benefit of allow PostgreSQL to work properly in BSD jails, since lack of really working SysV IPC was the problem there? I have postgresql working quite happily in FreeBSD jails! (Just make sure you go sysctl jail.sysvipc_allowed=1). Yep, Alastair D'Silva helpfully pointed this out a month or two ago, and for many people, this would be a workable solution. Unfortunately, it appears that you have to run this command outside the jail, which I don't have access to. I forwarded the suggestion to my ISP (imeme, a Zope provider), who said that: This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. And therefore they refused to make the change. (More annoyingly, they kept trying to convince me that I should quit my whining and use MySQL since it's ACID compliant). So, I'm holding out hope that since this ISP seems unenlightened, one day PostgreSQL will simply run in BSD jails without a cooperating jailmaster, and it sounded like using the APR _might_ make this possible. (All of my other projects use PG; I'd sure love to get this one switched over!) Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
I forwarded the suggestion to my ISP (imeme, a Zope provider), who said that: This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. Not true. But I'll avoid digging up any more on that old issue... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
On Sat, 4 May 2002, Joel Burton wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tom Lane Sent: Friday, May 03, 2002 6:07 PM To: mlw Cc: Marc G. Fournier; [EMAIL PROTECTED] Subject: Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports Rather than propagating the SysV semaphore API still further, why don't we kill it now? (I'm willing to keep the shmem API, however.) Would this have the benefit of allow PostgreSQL to work properly in BSD jails, since lack of really working SysV IPC was the problem there? There is no problem with SysV IPC in the jail, per se ... jail's were just not coded to delimite/segregate such IPC from other jails ... its one of those caveat empor(sp?) situations ... you can do it, but at your own risk, as somoene in another jail has the ability to 'attach' to your segments ... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
-Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 7:36 AM To: Joel Burton; Tom Lane; mlw Cc: Marc G. Fournier; [EMAIL PROTECTED] Subject: Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports I forwarded the suggestion to my ISP (imeme, a Zope provider), who said that: This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. Not true. But I'll avoid digging up any more on that old issue... Oh, I'm sure it's not true. But sometimes things end up on the nyah, nyah, it's my server and I say so level. Sigh. So, I guess that's where it leaves me: waiting for some solution other than ISP cluefulness. :-) - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
On Sat, 4 May 2002, Tom Lane wrote: Matthew Kirkwood [EMAIL PROTECTED] writes: On Fri, 3 May 2002, Tom Lane wrote: The SysV API lets us detect that case, but I don't see any equally good way to do it if we are using anonymous shared memory. It's a hack (and has slight security implications), but you could just allow the postgres backends to keep the listening socket(s) open. Hmm. That might be workable, but it feels shaky to me. The problem is that you are using a lock based on port number to interlock a data directory --- and port number and data directory are independently variable parameters. Consider $ postmaster -D /my/dir -- dba thinks oops, forgot to specify port $ kill -9 pm-pid # bad idea $ postmaster -D /my/dir -p myport Any backends started by the first postmaster will not be noticed by the second one, if the interlock is based on port number. We could get around this, of course: record the port number in the data directory lockfile, and test for existence of the old socket independently of trying to create a new one. But it seems ugly. How about a second, data directory based socket simply named something like '.inuse', that is not port dependent? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
On Sun, 5 May 2002, Joel Burton wrote: Joel Burton [EMAIL PROTECTED] writes: Rather than propagating the SysV semaphore API still further, why don't we kill it now? (I'm willing to keep the shmem API, however.) Would this have the benefit of allow PostgreSQL to work properly in BSD jails, since lack of really working SysV IPC was the problem there? Was the problem just with semas, or was shmem an issue too? Not sure -- doesn't get far enough for me to tell. initdb dies with: creating template1 database in /usr/local/pgsql/data/base/1... IpcSemaphoreCreate: semget(key=1, num=17, 03600) failed: Function not implemented Read the jail manpage: jail.sysvipc_allowed This MIB entry determines whether or not processes within a jail have access to System V IPC primitives. In the current jail imple- mentation, System V primitives share a single namespace across the host and jail environments, meaning that processes within a jail would be able to communicate with (and potentially interfere with) processes outside of the jail, and in other jails. As such, this functionality is disabled by default, but can be enabled by setting this MIB entry to 1. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Native Windows, Apache Portable Runtime
On Mon, 6 May 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: Well, I guess that just saved *me* alot of work ... thanks ... Uh, not yet. Don't you still need a semaphore implementation that works on Windows? Yup ... next steps, but I believe that is what Mark is working on ... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Or changing ISPs to a place more enlightened ... On Mon, 6 May 2002, Joel Burton wrote: -Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] Sent: Monday, May 06, 2002 7:36 AM To: Joel Burton; Tom Lane; mlw Cc: Marc G. Fournier; [EMAIL PROTECTED] Subject: Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports I forwarded the suggestion to my ISP (imeme, a Zope provider), who said that: This will allow you to run a single postgres in a single jail only one user would have access to it. If you try to run more then one it will try to use the same shared memory and crash. Not true. But I'll avoid digging up any more on that old issue... Oh, I'm sure it's not true. But sometimes things end up on the nyah, nyah, it's my server and I say so level. Sigh. So, I guess that's where it leaves me: waiting for some solution other than ISP cluefulness. :-) - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Native Windows, Apache Portable Runtime
On Sat, 2002-05-04 at 21:56, Tom Lane wrote: mlw [EMAIL PROTECTED] writes: We could provide a PGSemaphore based on an APR mutex and a counter, but I'm not sure of the performance impact. We may want to implement a generic semaphore like this and one optimized for platforms which we have development resources. Once we have the internal API redone, it should be fairly easy to experiment with alternatives like that. I'm planning to work on this today (need a break from thinking about schemas ;-)). I'll run with the API I sketched yesterday, since no one objected. Although I'm not planning on doing anything to the API of the shared-mem routines, I'll break them out into a replaceable file as well, just in case anyone wants to try a non-SysV implementation. Would it be too hard to make them macros, so those which dont need shared mem at all (embedded single-user systems) could avoid the performance impact altogether. Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [INTERFACES] ECPG: FETCH ALL|n FROM cursor - Memory allocation?
Lee Kindness wrote: Okay, lets see if i've got this right... If I allocate the memory before the FETCH then I (naturally) free it. However If I NULL initialise the pointer then libecpg will allocate the memory and I must NOT free it - libecpg will free it automatically... Yeah? No, I only said: Never mix free and ECPGfree_auto_mem because ECPGfree_auto_mem will double free it if you free'd it already. And also: it might be a good idea to kill the undocumented function (and the list). And: You need to free it (by one of the two methods above). I think this highlights the need for some documentation on this aspect. Yes it does. Christof ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Native Windows, Apache Portable Runtime
Cyril VELTER [EMAIL PROTECTED] writes: Are the PGShmemHeader fields only used by PGSharedMemoryCreate ? Other than totalsize and freeoffset, I believe so. I see no reason that a particular port couldn't stick different fields in there if it had a mind to. How does that solve the problem of determining whether a *previously* created shmem block is still in use? Ok, I overlooked that, my proposal for PGSharedMemoryIsInUse doesn't make sense (and it doesn't matter on Beos because shared mem segments are automaticaly reaped at the end of the process). Well, SharedMemoryIsInUse is *not* just about ensuring that the shared memory gets reaped. The point is to ensure that you can't start a new postmaster until the last old backend is gone. (Consider situations where the parent postmaster process crashes, or perhaps is kill -9'd by a careless DBA, but there are still active backends. We want to detect that situation and ensure that a new postmaster will refuse to start.) How so? If those calls were needed before, why won't all three still be needed? In the current hack, I've to iterate over all sharedmem segments (system wide) to find the original one by name. There is a race condition here if several backend are starting at the same time. beos_before_backend_startup beos_backend_startup_failed acquire / release a semaphore which prevent several fork at the same time. Does keeping the shmem segment name around solve that? Seems like you don't need a PGShmemHeader field for that; just store it in a static variable. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Marc G. Fournier [EMAIL PROTECTED] writes: We could get around this, of course: record the port number in the data directory lockfile, and test for existence of the old socket independently of trying to create a new one. But it seems ugly. How about a second, data directory based socket simply named something like '.inuse', that is not port dependent? Hmm ... but how do you use that to tell if there are still backends around? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
On Mon, 6 May 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: We could get around this, of course: record the port number in the data directory lockfile, and test for existence of the old socket independently of trying to create a new one. But it seems ugly. How about a second, data directory based socket simply named something like '.inuse', that is not port dependent? Hmm ... but how do you use that to tell if there are still backends around? As a backend is started up, connect to that socket ... if socket is open when trying to start a new frontend, fail as there are currently other connections attached to it? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Marc G. Fournier [EMAIL PROTECTED] writes: Hmm ... but how do you use that to tell if there are still backends around? As a backend is started up, connect to that socket ... if socket is open when trying to start a new frontend, fail as there are currently other connections attached to it? But the backends would only have the socket open, they'd not be actively listening to it. So how could you tell whether anyone had the socket open or not? ISTM we gave up on exactly that technique for the main postmaster's socket; we now create a separate lockfile to protect the socket, and don't rely on the socket itself to give us any interlocking help at all. But the lockfile just contains the postmaster's PID, so it's no help in detecting the case where the old postmaster has gone away but there are still orphaned backends laying about. I'm not entirely thrilled with the lockfile technique; it'd be nice to find something better. (In particular, we've seen a couple cases now where people had trouble with PG refusing to start after a system reboot, because some other daemon process had been assigned the PID that the postmaster had in its previous incarnation; so the lockfile check code mistakenly thinks there's still an old postmaster.) But so far, the only thing worse than lockfiles is everything else :-( regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
I said: But the backends would only have the socket open, they'd not be actively listening to it. So how could you tell whether anyone had the socket open or not? Oh, I take that back, I see how you could do it: the postmaster opens the socket *for writing*, but never actually writes. All its child processes inherit that same open file descriptor and just keep it around. Then, to tell if anyone's home, you open the socket *for reading* and try to read in O_NONBLOCK mode. You get an EOF indication if and only if no one has the socket open for writing; otherwise you get an EAGAIN error. That would work ... but is it more portable than depending on SysV shmem connection counts? ISTR that some of the platforms we support don't have Unix-style sockets at all. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
On Mon, 6 May 2002, Tom Lane wrote: I said: But the backends would only have the socket open, they'd not be actively listening to it. So how could you tell whether anyone had the socket open or not? Oh, I take that back, I see how you could do it: the postmaster opens the socket *for writing*, but never actually writes. All its child processes inherit that same open file descriptor and just keep it around. Then, to tell if anyone's home, you open the socket *for reading* and try to read in O_NONBLOCK mode. You get an EOF indication if and only if no one has the socket open for writing; otherwise you get an EAGAIN error. That would work ... but is it more portable than depending on SysV shmem connection counts? ISTR that some of the platforms we support don't have Unix-style sockets at all. Wouldn't the same thing work with a simple file? Does it have to be a UnixDomainSocket? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Marc G. Fournier [EMAIL PROTECTED] writes: That would work ... but is it more portable than depending on SysV shmem connection counts? ISTR that some of the platforms we support don't have Unix-style sockets at all. Wouldn't the same thing work with a simple file? Does it have to be a UnixDomainSocket? No, and yes. If it's not a pipe/fifo then you don't get the EOF-only-when-no-possible-writers-remain behavior. TCP and UDP sockets don't show this sort of behavior either. So AFAICS we really need a named pipe, ie, socket. We could maybe do something approximately similar with TCP connection attempts (per the prior suggestion of letting backends hold the postmaster's listen socket open; then see if you get connection refused or a timeout from trying to connect) but I don't think it'd be as trustworthy. Simple mistakes like overly aggressive ipchains filters would confuse this kind of test. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Since our default behavior (at startup) is to have TCP sockets disabled, how many OSs are there that don't support UD sockets? Enough to really be worried about? On Mon, 6 May 2002, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: That would work ... but is it more portable than depending on SysV shmem connection counts? ISTR that some of the platforms we support don't have Unix-style sockets at all. Wouldn't the same thing work with a simple file? Does it have to be a UnixDomainSocket? No, and yes. If it's not a pipe/fifo then you don't get the EOF-only-when-no-possible-writers-remain behavior. TCP and UDP sockets don't show this sort of behavior either. So AFAICS we really need a named pipe, ie, socket. We could maybe do something approximately similar with TCP connection attempts (per the prior suggestion of letting backends hold the postmaster's listen socket open; then see if you get connection refused or a timeout from trying to connect) but I don't think it'd be as trustworthy. Simple mistakes like overly aggressive ipchains filters would confuse this kind of test. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Marc G. Fournier [EMAIL PROTECTED] writes: Since our default behavior (at startup) is to have TCP sockets disabled, how many OSs are there that don't support UD sockets? A quick look in the sources shows that we #undef HAVE_UNIX_SOCKETS for QNX, BeOS, and old cygwin versions ... which are exactly the platforms that don't have SysV shmem support, so those are exactly the guys who we're trying to fix the problem for. I do like the idea of using a Unix socket this way where available, though. It'd let us switch over the shmem code to using IPC_PRIVATE shmem key, which'd simplify that code tremendously; and we could make some progress against the dead-PID-in-lockfile problem. Could we get away with saying that the Unix-socket-less platforms have weaker protection against mistakenly restarting the postmaster? We could have a plain-vanilla lockfile instead of a socket lockfile on those platforms, which would not catch the dead-postmaster-live-backends case, but it'd be better than nothing. And I am not convinced that the shmem-connection-count check should be trusted on QNX or BeOS, anyway, so I'm not sure that they actually have a functioning check now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: Coming back to this subject if I may but only briefly, I hope. How about making a slight change to current_schemas() and including an optional argument such that something like: current_schemas(1) returns the complete list of schemas in the search path including the implicit temporary space and the pg_catalog (if not already listed obviously), while current_schemas() and current_schemas(0) behave as now. I don't really care for that syntax, but certainly we could talk about providing a version of current_schemas that tells the Whole Truth. Having something like this would enable client's like PgAccess to determine the complete list of visible objects. Well, no, it wouldn't. Say there are multiple tables named foo in different namespaces in your search path (eg, a temp table hiding a permanent table of the same name). A test like where current_schemas *= relnamespace won't reflect this correctly. I'm suspecting that what we really need is some kind of is_visible_table() test function, and then you'd do select * from pg_class where is_visible_table(oid); At least I've not been able to think of a better idea than that. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Set Returning Functions (SRF) - request for patch review and comment
I've been buried in the backend parser/planner/executor now for the last
2 weeks or so, and I now have a patch for a working implementation of
SRFs as RTEs (i.e. SELECT tbl.* FROM myfunc() AS tbl). I think I'm at
a good point to get review and comments. Not everything yet has been
implemented per my proposal (see:
http://fts.postgresql.org/db/mw/msg.html?mid=1077099 ) but most of the
support is in place.
How it currently works:
---
1. At this point, FROM clause SRFs are used as a row source in a manner
similar to the current API, i.e. one row at a time is produced without
materializing.
2. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.
3. The SRF may either return a base data type (e.g. TEXT) or a composite
data type (e.g. pg_class). If the function returns a base data type, the
single result column is named for the function. If the function returns
a composite type, the result columns get the same names as the
individual attributes of the type.
4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.
5. example:
test=# CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 16693 1
test=# INSERT INTO foo VALUES(1,2,'Ed');
INSERT 16694 1
test=# INSERT INTO foo VALUES(2,1,'Mary');
INSERT 16695 1
test=# CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
test=# SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
---+--+-
1 |1 | Joe
1 |2 | Ed
(2 rows)
test=# SELECT t1.fooname FROM getfoo(1) AS t1 WHERE t1.foosubid = 1;
fooname
-
Joe
(1 row)
test=# select * from dblink_get_pkey('foo') as t1;
dblink_get_pkey
-
fooid
foosubid
(2 rows)
What still needs to be done:
1. Add a new table_ref node type - DONE
2. Add support for three modes of operation to RangePortal:
a. Repeated calls -- DONE
b. Materialized results -- partially complete
c. Return query -- I'm starting to wonder how/if this is really
different than a.) above
3. Add support to allow the RangePortal to materialize modes a and c,
if needed for a re-read -- partially complete.
4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
specified -- not yet started.
Request for help:
-
So far I've tested with SQL and C functions. I will also do some testing
with PLpgSQL functions. I need testing and feedback from users of the
other function PLs.
Review, comments, feedback, etc. are appreciated.
Thanks,
Joe
srf.2002.05.05.2.patch.gz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] a couple of minor itches: RI Trigger Names, and additional Alterownerships commands.
Hello postgresql-hackers, Been a while sine I've particiapated on this list so I wanted to say thank you for the great product postgresql 7.2.1 is! I have been doing some testing in preperation of a database upgrade from 7.0.3 to 7.2.1 and I have a few small itches to scratch, so I thought I'd get opinions from the experts :) Itch #1: Referential Integrity trigger names in psql \d output. Currently the \d ouput from psql displays non-obvious names for what the RI trigger actually does. Reading through the source code and quering the mailing lists indicate this is easily changed (and I have already done this on a test database without any ill effects so far). Here is what the \d displays from 7.2.1: Before: test=# \d foo Table foo Column | Type | Modifiers -+-+--- blah_id | integer | not null foo | text| not null Primary key: foo_pkey Triggers: RI_ConstraintTrigger_30670 test=# \d blah Table blah Column | Type | Modifiers -+-+--- blah_id | integer | not null blah| text| not null Primary key: blah_pkey Triggers: RI_ConstraintTrigger_30672, RI_ConstraintTrigger_30674 After: test=# \d foo Table foo Column | Type | Modifiers -+-+--- blah_id | integer | not null foo | text| not null Primary key: foo_pkey Triggers: RI_blah_id (insert) test=# \d blah Table blah Column | Type | Modifiers -+-+--- blah_id | integer | not null blah| text| not null Primary key: blah_pkey Triggers: RI_blah_id (delete), RI_blah_id (update) This change was made with a simple update to the pg_trigger system table for the tgname column. Searching through the code and the mailing list, it looks like the only constraint to the tgname column is that it needs to be unique (although the database schema does not inforce this via a unique index) since the OID tacked on to the RI_ConstraintTrigger_* was designed to keep this uniqueness. What I would propose is to base the RI_* off the constrain name provided during the RI_trigger creation, if the constrain name is not provided, then to default to the current nameing scheme. Can anyone think of side-affects of changing the tgname column in the pg_trigger system table? Does this proposal seem like an acceptable solution? Would there be interest in this if I provided a patch to do this? Itch #2: Alter ownership on a sequence, etc. Alter table provides the functionality to change the ownership of a table, but ownership on other structures like sequences, etc can not be changed without dropping and recreating as the new owner. Would there be any interest if I worked on a patch to do this too? Thanks again for all the hard work and a great database! - Ryan Bradetich ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Native Windows, Apache Portable Runtime
Well, SharedMemoryIsInUse is *not* just about ensuring that the shared memory gets reaped. The point is to ensure that you can't start a new postmaster until the last old backend is gone. (Consider situations where the parent postmaster process crashes, or perhaps is kill -9'd by a careless DBA, but there are still active backends. We want to detect that situation and ensure that a new postmaster will refuse to start.) Yes I remember that now (the current code do that correctly). How so? If those calls were needed before, why won't all three still be needed? In the current hack, I've to iterate over all sharedmem segments (system wide) to find the original one by name. There is a race condition here if several backend are starting at the same time. beos_before_backend_startup beos_backend_startup_failed acquire / release a semaphore which prevent several fork at the same time. Does keeping the shmem segment name around solve that? Seems like you don't need a PGShmemHeader field for that; just store it in a static variable. No the name is not enough, I need the beos handle for each shared mem segment. I'll try to find a cleaner solution using existing API. cyril ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Tom Lane wrote: I said: But the backends would only have the socket open, they'd not be actively listening to it. So how could you tell whether anyone had the socket open or not? Oh, I take that back, I see how you could do it: the postmaster opens the socket *for writing*, but never actually writes. All its child processes inherit that same open file descriptor and just keep it around. Then, to tell if anyone's home, you open the socket *for reading* and try to read in O_NONBLOCK mode. You get an EOF indication if and only if no one has the socket open for writing; otherwise you get an EAGAIN error. That would work ... but is it more portable than depending on SysV shmem connection counts? ISTR that some of the platforms we support don't have Unix-style sockets at all. I think what you describe is a named pipe, not a socket. The underlying implementation might be a socketpair, but the behaviour of named pipes is exactly that since Version 7 at least. This worked under Minix already. regards, tom lane Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
On Mon, 6 May 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Coming back to this subject if I may but only briefly, I hope. How about making a slight change to current_schemas() and including an optional argument such that something like: current_schemas(1) returns the complete list of schemas in the search path including the implicit temporary space and the pg_catalog (if not already listed obviously), while current_schemas() and current_schemas(0) behave as now. I don't really care for that syntax, but certainly we could talk about providing a version of current_schemas that tells the Whole Truth. Having something like this would enable client's like PgAccess to determine the complete list of visible objects. Well, no, it wouldn't. Say there are multiple tables named foo in different namespaces in your search path (eg, a temp table hiding a permanent table of the same name). A test like where current_schemas *= relnamespace won't reflect this correctly. I'm suspecting that what we really need is some kind of is_visible_table() test function, and then you'd do select * from pg_class where is_visible_table(oid); At least I've not been able to think of a better idea than that. Ok, where I was coming from was the idea of the client, I'm most interested in PgAccess at the moment, retrieving the search path and cross referencing that against the results of the queries for tables etc. I seemed to remember mention of an is_visible() function earlier in the thread but that for some reason this would mean a performance hit across the board, or at least in many places. However, reviewing my emails I see not such comment about performance. Tom originally suggested relation_is_visible(oid) as the function. I also got it wrong about when the temporary space is emptied. I had been thinking it was when the connection terminated. However, I see from the same old message that this happens when the first temporary item is created in a session. Therefore, my way would be invalid anyway; or would it? -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: I also got it wrong about when the temporary space is emptied. I had been thinking it was when the connection terminated. However, I see from the same old message that this happens when the first temporary item is created in a session. Therefore, my way would be invalid anyway; or would it? It would work as long as the variant form of current_schemas() truly reflects the effective search path --- because until you create a temporary item, there is no temp schema in the effective path. Still, the issue of hiding seems to be a good reason not to code clients that way. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] a couple of minor itches: RI Trigger Names, and additional
Ryan Bradetich wrote: Can anyone think of side-affects of changing the tgname column in the pg_trigger system table? Does this proposal seem like an acceptable solution? Would there be interest in this if I provided a patch to do this? FWIW, not exactly what you are proposing, but ALTER TRIGGER RENAME is available in current CVS. See: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-altertrigger.html Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schemas: status report, call for developers
On Mon, 6 May 2002, Nigel J. Andrews wrote: On Mon, 6 May 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: Coming back to this subject if I may but only briefly, I hope. How about making a slight change to current_schemas() and including an optional argument such that something like: current_schemas(1) returns the complete list of schemas in the search path including the implicit temporary space and the pg_catalog (if not already listed obviously), while current_schemas() and current_schemas(0) behave as now. I don't really care for that syntax, but certainly we could talk about providing a version of current_schemas that tells the Whole Truth. Wouldn't such a function just be based on backend/catalog/namespace.c:RelnameGetRelid(const char *relname) ? I'm suspecting that what we really need is some kind of is_visible_table() test function, and then you'd do select * from pg_class where is_visible_table(oid); At least I've not been able to think of a better idea than that. [snip] For this if we look once again at RelnameGetRelid(relname) in backend/catalog/namespace.c wouldn't this is_visible() function simply be a wrapper around it? Obviously the parameter [probably] wouldn't be an OID but rather a name. If I knew which file would be most appropiate for this (utils/adt/name.c?) I'd have had a go at making a patch. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HEADS UP: Win32/OS2/BeOS native ports
Marc G. Fournier [EMAIL PROTECTED] writes: Since our default behavior (at startup) is to have TCP sockets disabled, how many OSs are there that don't support UD sockets? A quick look in the sources shows that we #undef HAVE_UNIX_SOCKETS for QNX, BeOS, and old cygwin versions ... which are exactly the platforms that don't have SysV shmem support, so those are exactly the guys who we're trying to fix the problem for. Next release of QNX (6.2) will add support for UDS, but they are still not quite portable. I do like the idea of using a Unix socket this way where available, though. It'd let us switch over the shmem code to using IPC_PRIVATE shmem key, which'd simplify that code tremendously; and we could make some progress against the dead-PID-in-lockfile problem. Could we get away with saying that the Unix-socket-less platforms have weaker protection against mistakenly restarting the postmaster? We could have a plain-vanilla lockfile instead of a socket lockfile on those platforms, which would not catch the dead-postmaster-live-backends case, but it'd be better than nothing. And I am not convinced that the shmem-connection-count check should be trusted on QNX or BeOS, anyway, so I'm not sure that they actually have a functioning check now. Why can't we use named pipe (aka FIFO file) instead of UDS? I think that is more portable... The socketpair() function also tends to be more portable than whole UDS in general... It works on QNX4 even, but not sure about BeOS. Another thought is, why can't we use bind() to the postmaster port to detect other postmasters? I might be missing something, so pardon by ignorance. But should not bind() to same port fail with EADDRINUSE unless SO_REUSEADDR is set? I don't really know if it is set in postgres or not ... -- igor ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Schemas: status report, call for developers
Nigel J. Andrews [EMAIL PROTECTED] writes: For this if we look once again at RelnameGetRelid(relname) in backend/catalog/namespace.c wouldn't this is_visible() function simply be a wrapper around it? Sort of. It's there already, see RelationIsVisible. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Native Windows, Apache Portable Runtime
On Mon, 6 May 2002, mlw wrote: Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: Well, I guess that just saved *me* alot of work ... thanks ... Uh, not yet. Don't you still need a semaphore implementation that works on Windows? I have a LOT of experience with Windows development. You tell me what you want and I'll write it. This is not an issue. appropriate sem* replacements in Windows? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] pgsql_data/base mapping
Are the numbers of the directories in the base diretory and the numbers of the directories under that, etc. traceable to a reference somewhere in the postgresql server using that data directory (such as the pg_database table or such)? If so, is there somewhere this is documented? Thanks, -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com Where's mybus? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pgsql_data/base mapping
The numbers are the same as the oids found in pg_database (as you expected). This question is better suited for the -general list, for future reference. Regards, Jeff On Monday 06 May 2002 04:31 pm, Laurette Cisneros wrote: Are the numbers of the directories in the base diretory and the numbers of the directories under that, etc. traceable to a reference somewhere in the postgresql server using that data directory (such as the pg_database table or such)? If so, is there somewhere this is documented? Thanks, ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Number of attributes in HeapTupleHeader
Neil Conway wrote: On Mon, 6 May 2002 08:44:27 +0900 "Hiroshi Inoue" [EMAIL PROTECTED] wrote: -Original Message- From: Manfred Koizar If there is interest in reducing on-disk tuple header size and I have not missed any strong arguments against dropping t_natts, I'll investigate further. Comments? If a dbms is proper, it prepares a mechanism from the first to handle ADD COLUMN without touching the tuples. If the machanism is lost(I believe so) by removing t_natts, I would say good bye to PostgreSQL. IMHO, the current ADD COLUMN mechanism is a hack. Besides requiring redundant on-disk data (t_natts), it isn't SQL compliant (because default values or NOT NULL can't be specified), and depends on a low-level kludge (that the storage system will return NULL for any attnums the # of the attributes stored in the tuple). I think it's neither a hack nor a kludge. The value of data which are non-existent at the appearance is basically unknown. So there could be an implementation of ALTER TABLE ADD COLUMN .. DEFAULT which doesn't touch existent tuples at all as Oracle does. Though I don't object to touch tuples to implement ADD COLUMN .. DEFAULT, please don't change the existent stuff together. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Number of attributes in HeapTupleHeader
I think the real trick is keeping track of the difference between: begin; ALTER TABLE tab ADD COLUMN col1 int4 DEFAULT 4; commit; and begin; ALTER TABLE tab ADD COLUMN col1; ALTER TABLE tab ALTER COLUMN col1 SET DEFAULT 4; commit; The first should populate the column with the value of '4', the second should populate the column with NULL and have new entries with default of 4. Not to mention begin; ALTER TABLE tab ADD COLUMN col1 DEFAULT 5; ALTER TABLE tab ALTER COLUMN col1 SET DEFAULT 4; commit; New tuples with default value of 4, but the column creation should have 5. -- Rod - Original Message - From: "Hiroshi Inoue" [EMAIL PROTECTED] To: "Neil Conway" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, May 06, 2002 9:08 PM Subject: Re: [HACKERS] Number of attributes in HeapTupleHeader Neil Conway wrote: On Mon, 6 May 2002 08:44:27 +0900 "Hiroshi Inoue" [EMAIL PROTECTED] wrote: -Original Message- From: Manfred Koizar If there is interest in reducing on-disk tuple header size and I have not missed any strong arguments against dropping t_natts, I'll investigate further. Comments? If a dbms is proper, it prepares a mechanism from the first to handle ADD COLUMN without touching the tuples. If the machanism is lost(I believe so) by removing t_natts, I would say good bye to PostgreSQL. IMHO, the current ADD COLUMN mechanism is a hack. Besides requiring redundant on-disk data (t_natts), it isn't SQL compliant (because default values or NOT NULL can't be specified), and depends on a low-level kludge (that the storage system will return NULL for any attnums the # of the attributes stored in the tuple). I think it's neither a hack nor a kludge. The value of data which are non-existent at the appearance is basically unknown. So there could be an implementation of ALTER TABLE ADD COLUMN .. DEFAULT which doesn't touch existent tuples at all as Oracle does. Though I don't object to touch tuples to implement ADD COLUMN .. DEFAULT, please don't change the existent stuff together. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Set Returning Functions (SRF) - request for patch review and comment
Feedback: you're a legend!
I'll try to patch my CVS and test it at some point...
Chris
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Joe Conway
Sent: Tuesday, 7 May 2002 12:51 AM
To: pgsql-hackers
Subject: [HACKERS] Set Returning Functions (SRF) - request for patch
review and comment
I've been buried in the backend parser/planner/executor now for the last
2 weeks or so, and I now have a patch for a working implementation of
SRFs as RTEs (i.e. SELECT tbl.* FROM myfunc() AS tbl). I think I'm at
a good point to get review and comments. Not everything yet has been
implemented per my proposal (see:
http://fts.postgresql.org/db/mw/msg.html?mid=1077099 ) but most of the
support is in place.
How it currently works:
---
1. At this point, FROM clause SRFs are used as a row source in a manner
similar to the current API, i.e. one row at a time is produced without
materializing.
2. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.
3. The SRF may either return a base data type (e.g. TEXT) or a composite
data type (e.g. pg_class). If the function returns a base data type, the
single result column is named for the function. If the function returns
a composite type, the result columns get the same names as the
individual attributes of the type.
4. The SRF *must* be aliased in the FROM clause. This is similar to the
requirement for a subselect used in the FROM clause.
5. example:
test=# CREATE TABLE foo (fooid int, foosubid int, fooname text, primary
key(fooid,foosubid));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
test=# INSERT INTO foo VALUES(1,1,'Joe');
INSERT 16693 1
test=# INSERT INTO foo VALUES(1,2,'Ed');
INSERT 16694 1
test=# INSERT INTO foo VALUES(2,1,'Mary');
INSERT 16695 1
test=# CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM
foo WHERE fooid = $1;' LANGUAGE SQL;
CREATE
test=# SELECT * FROM getfoo(1) AS t1;
fooid | foosubid | fooname
---+--+-
1 |1 | Joe
1 |2 | Ed
(2 rows)
test=# SELECT t1.fooname FROM getfoo(1) AS t1 WHERE t1.foosubid = 1;
fooname
-
Joe
(1 row)
test=# select * from dblink_get_pkey('foo') as t1;
dblink_get_pkey
-
fooid
foosubid
(2 rows)
What still needs to be done:
1. Add a new table_ref node type - DONE
2. Add support for three modes of operation to RangePortal:
a. Repeated calls -- DONE
b. Materialized results -- partially complete
c. Return query -- I'm starting to wonder how/if this is really
different than a.) above
3. Add support to allow the RangePortal to materialize modes a and c,
if needed for a re-read -- partially complete.
4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be
specified -- not yet started.
Request for help:
-
So far I've tested with SQL and C functions. I will also do some testing
with PLpgSQL functions. I need testing and feedback from users of the
other function PLs.
Review, comments, feedback, etc. are appreciated.
Thanks,
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Number of attributes in HeapTupleHeader
Rod Taylor wrote: I think the real trick is keeping track of the difference between: begin; ALTER TABLE tab ADD COLUMN col1 int4 DEFAULT 4; commit; and begin; ALTER TABLE tab ADD COLUMN col1; ALTER TABLE tab ALTER COLUMN col1 SET DEFAULT 4; commit; The first should populate the column with the value of '4', the second should populate the column with NULL and have new entries with default of 4. I know the difference. Though I don't love the standard spec of the first, I don't object to introduce it. My only anxiety is that the implementation of the first would replace the current implementaion of ADD COLUMN (without default) together to touch tuples. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
