Re: [HACKERS] SET NULL on NOT NULL field
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > I just noticed you can do this: > create table blah ( > a not null references test on delete set null > ) > Should that be prevented? It already does. Or did you mean disallow the declaration? I can't see anything in SQL92 that recommends disallowing the declaration. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] interactive docs error
Already fixed ... one of those things where per-db connection limits would have been helpful :( On Sat, 25 Jan 2003, Dave Cramer wrote: > > Warning: pg_connect() unable to connect to PostgreSQL server: FATAL 1: > Sorry, too many clients already in > /usr/local/www/www.postgresql.org/idocs/opendb.php on line 3 > Unable to access database > -- > Dave Cramer <[EMAIL PROTECTED]> > Cramer Consulting > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SET NULL on NOT NULL field
On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: > I just noticed you can do this: > > create table blah ( > a not null references test on delete set null > ) > > Should that be prevented? It shouldn't be too hard to test for really... Maybe, although I don't think the spec prevents it. In practice I'd guess it ends up being a more expensive way of saying no action. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Client failure allows backed to continue
Well, setting query_cancel then seems like a logical solution because it will exit at a reasonable point, hopefully. Right now we have statement_timeout and that exits at a give time, but I suppose it doesn't exit while data is transfering, so it may be different. --- Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Well, if we get an I/O error, I can't imagine why we would continue > > doing anything --- are any of those recoverable? > > Well, that's what's not clear --- it's hard to tell if a write failure > is a hard error or just transient. If we make like elog(ERROR), > returning to the main loop, and then a read from the client *doesn't* > fail, we'll try to continue ... but we've just screwed the pooch, > because we have not sent a complete message and therefore certainly have > messed up frontend/backend synchronization. I have no idea whether it's > really possible to recover from this situation or not, but that approach > surely won't work. > > If you want to take a kamikaze any-comm-error-means-we're-dead approach, > you might think about elog(FATAL). But that tries to send a message to > the client. Instant infinite loop, if the error is hard. > > Complaints to the postmaster log, and abort at the next safe place > (*not* partway through message output) seem like the way to go to me. > > > Do we need a separate error type for I/O messages? > > Uh ... see COMMERROR. > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Client failure allows backed to continue
Bruce Momjian <[EMAIL PROTECTED]> writes: > Well, if we get an I/O error, I can't imagine why we would continue > doing anything --- are any of those recoverable? Well, that's what's not clear --- it's hard to tell if a write failure is a hard error or just transient. If we make like elog(ERROR), returning to the main loop, and then a read from the client *doesn't* fail, we'll try to continue ... but we've just screwed the pooch, because we have not sent a complete message and therefore certainly have messed up frontend/backend synchronization. I have no idea whether it's really possible to recover from this situation or not, but that approach surely won't work. If you want to take a kamikaze any-comm-error-means-we're-dead approach, you might think about elog(FATAL). But that tries to send a message to the client. Instant infinite loop, if the error is hard. Complaints to the postmaster log, and abort at the next safe place (*not* partway through message output) seem like the way to go to me. > Do we need a separate error type for I/O messages? Uh ... see COMMERROR. 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
[HACKERS] SET NULL on NOT NULL field
I just noticed you can do this: create table blah ( a not null references test on delete set null ) Should that be prevented? It shouldn't be too hard to test for really... Chris ---(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] Win32 port patches submitted
Peter Eisentraut wrote: Justin Clift writes: The advantages to having the Win32 port be natively compatible with Visual Studio is that it already is (no toolset-porting work needed there), You're missing a couple of points here. First, the MS Visual whatever compiler can also be used with a makefile-driven build system. Second, the port as it stands isn't really compatible with anything except Jan's build instructions. There's a lot of work to be done before we get anything that builds out of the box in the 7.4 branch, and it's going to be a lot easier if we do it using the build system we already have and know. Thanks Peter. Really didn't know that MS Visual could work with makefile driven build systems, nor that the PeerDirect build process was so... unique. :) 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release Scheduales: 7.2.4 & 7.3.2
Josh Berkus <[EMAIL PROTECTED]> writes: > I've been following this discussion, but I still haven't seen a definitive > list of patches included in 7.2.4. Where can I find one? It ain't definitive till the tarball's wrapped, but at the moment there's http://archives.postgresql.org/pgsql-hackers/2003-01/msg00982.php (BTW, you could reproduce that list for yourself pretty easily. I use cvs2cl.pl, which I don't recall where I got it from, but certainly a bit of googling will find it for you.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Client failure allows backed to continue
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Why is COMMERROR not doing the longjump like ERROR? > > Because it's defined to be like LOG. > > A more useful reply might be that I'm not sure it's safe to abort in the > client I/O routines. Well, if we get an I/O error, I can't imagine why we would continue doing anything --- are any of those recoverable? Do we need a separate error type for I/O messages? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Client failure allows backed to continue
Bruce Momjian <[EMAIL PROTECTED]> writes: > Why is COMMERROR not doing the longjump like ERROR? Because it's defined to be like LOG. A more useful reply might be that I'm not sure it's safe to abort in the client I/O routines. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Client failure allows backed to continue
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > As part of the training class I did, some people tested what happens > > when the client allocates tons of memory to store a result and aborts. > > > What we found was that though elog was properly called: > > > elog(COMMERROR, "pq_recvbuf: recv() failed: %m"); > > > (I think that was the message.) the backend did not exit and kept > > eating CPU. I think the problem is that the elog code only exits on > > ERROR, not COMMERROR. Is there some way to fix this? > > There's been talk of setting the QueryCancel flag after detecting a > client communication failure ... but no one has ever done the legwork > to see if that works nicely, or what downsides it might have. Why is COMMERROR not doing the longjump like ERROR? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Client failure allows backed to continue
Bruce Momjian <[EMAIL PROTECTED]> writes: > As part of the training class I did, some people tested what happens > when the client allocates tons of memory to store a result and aborts. > What we found was that though elog was properly called: > elog(COMMERROR, "pq_recvbuf: recv() failed: %m"); > (I think that was the message.) the backend did not exit and kept > eating CPU. I think the problem is that the elog code only exits on > ERROR, not COMMERROR. Is there some way to fix this? There's been talk of setting the QueryCancel flag after detecting a client communication failure ... but no one has ever done the legwork to see if that works nicely, or what downsides it might have. 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])
Re: [HACKERS] IPv6 patch
Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > If we cleanly split the Postgres-specific code from the stuff that's > > been imported from BIND, shouldn't it be easy to import new versions, > > and thus get IPv6 support for free? > > IIRC, the issue was that we'd painfully hammered out a set of preferred > I/O behaviors for the inet and cidr datatypes, and then hacked up the > code we'd imported from BIND to make it happen. Paul Vixie sent in a > patch that replaced the imported code with v6-aware BIND code --- > thereby reverting those painfully-agreed-to patches. So it got > rejected. > > I have no problem with restructuring our I/O behavior as wrappers around > the pristine BIND routines; although privately I doubt it's worth the > trouble. The really interesting part of upgrading to v6 inet support is > going to be obtaining a consensus on how our current I/O behaviors should > translate to v6 addresses. Once we have that, I suspect that slash-and- > burn mods on the BIND code will again be the way to go ;-). It's not > like v6 is going to be replaced in the foreseeable future. Yes, we need to keep merge the changes we made to the original IPv4 code into IPv6. IPv6 seems to be maturing a little so if we can keep the bind stuff pretty similar and just call it, that would be nice too. Seems someone has to do the legwork and see what is involved. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] IPv6 patch
Neil Conway <[EMAIL PROTECTED]> writes: > If we cleanly split the Postgres-specific code from the stuff that's > been imported from BIND, shouldn't it be easy to import new versions, > and thus get IPv6 support for free? IIRC, the issue was that we'd painfully hammered out a set of preferred I/O behaviors for the inet and cidr datatypes, and then hacked up the code we'd imported from BIND to make it happen. Paul Vixie sent in a patch that replaced the imported code with v6-aware BIND code --- thereby reverting those painfully-agreed-to patches. So it got rejected. I have no problem with restructuring our I/O behavior as wrappers around the pristine BIND routines; although privately I doubt it's worth the trouble. The really interesting part of upgrading to v6 inet support is going to be obtaining a consensus on how our current I/O behaviors should translate to v6 addresses. Once we have that, I suspect that slash-and- burn mods on the BIND code will again be the way to go ;-). It's not like v6 is going to be replaced in the foreseeable future. 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])
Re: [HACKERS] IPv6 patch
That is _exactly_ how it has to be done. --- Neil Conway wrote: > On Mon, 2003-01-27 at 20:19, Bruce Momjian wrote: > > I had someone on the IPv6 IRC channel interested, but haven't seen any > > patch yet. It isn't that hard to do. > > If we cleanly split the Postgres-specific code from the stuff that's > been imported from BIND, shouldn't it be easy to import new versions, > and thus get IPv6 support for free? > > Or at least, that's what I vaguely recall Paul Vixie saying on > pgsql-patches a while ago: > > >http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=200202221828.g1MISX102055%40candle.pha.pa.us&rnum=2&prev=/groups%3Fq%3Dpaul%2Bvixie%2Bgroup:comp.databases.postgresql.patches%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D200202221828.g1MISX102055%2540candle.pha.pa.us%26rnum%3D2 > > Cheers, > > Neil > -- > Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC > > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] IPv6 patch
On Mon, 2003-01-27 at 20:19, Bruce Momjian wrote: > I had someone on the IPv6 IRC channel interested, but haven't seen any > patch yet. It isn't that hard to do. If we cleanly split the Postgres-specific code from the stuff that's been imported from BIND, shouldn't it be easy to import new versions, and thus get IPv6 support for free? Or at least, that's what I vaguely recall Paul Vixie saying on pgsql-patches a while ago: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=200202221828.g1MISX102055%40candle.pha.pa.us&rnum=2&prev=/groups%3Fq%3Dpaul%2Bvixie%2Bgroup:comp.databases.postgresql.patches%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D200202221828.g1MISX102055%2540candle.pha.pa.us%26rnum%3D2 Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] IPv6 patch
Christopher Kings-Lynne wrote: > > > > However, the server log messages stating an IPv6 socket was > > not made is > > > > only printed if the binary supports IPv6. The message seems to be a > > > > compromise between those who wanted a separate IPv6 GUC/flag and those > > > > who wanted it to silently fail on IPv6. > > > > > > I'm not sure. Those who wanted silence don't get any silence and those > > > who wanted a configurable hard failure get neither the > > configurability nor > > > any failure. > > > > That is the compromise. Neither gets what they want, but the final > > solution is closer to each. This is probably the best we can do. > > BTW guys, there was strong demand at Linux.conf.au asking for the 'inet' > type to support IPv6 addresses. I've been looking at it a bit, but I'll > have to interleave it with my work on phpPgAdmin 3 (which rocks BTW :) ). I had someone on the IPv6 IRC channel interested, but haven't seen any patch yet. It isn't that hard to do. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] IPv6 patch
> > > However, the server log messages stating an IPv6 socket was > not made is > > > only printed if the binary supports IPv6. The message seems to be a > > > compromise between those who wanted a separate IPv6 GUC/flag and those > > > who wanted it to silently fail on IPv6. > > > > I'm not sure. Those who wanted silence don't get any silence and those > > who wanted a configurable hard failure get neither the > configurability nor > > any failure. > > That is the compromise. Neither gets what they want, but the final > solution is closer to each. This is probably the best we can do. BTW guys, there was strong demand at Linux.conf.au asking for the 'inet' type to support IPv6 addresses. I've been looking at it a bit, but I'll have to interleave it with my work on phpPgAdmin 3 (which rocks BTW :) ). Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Fix for log_min_error_messages
Due to my error, log_min_error messages went into 7.3.X with the wrong default, and the wrong listed options. This fixes the bug in CVS current and 7.3.X. Patch attached. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/backend/utils/misc/guc.c === RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v retrieving revision 1.111 diff -c -c -r1.111 guc.c *** src/backend/utils/misc/guc.c25 Jan 2003 23:10:27 - 1.111 --- src/backend/utils/misc/guc.c27 Jan 2003 23:39:15 - *** *** 101,107 bool Password_encryption = true; ! int log_min_error_statement = ERROR; char *log_min_error_statement_str = NULL; const charlog_min_error_statement_str_default[] = "panic"; --- 101,107 bool Password_encryption = true; ! int log_min_error_statement = PANIC; char *log_min_error_statement_str = NULL; const charlog_min_error_statement_str_default[] = "panic"; Index: src/backend/utils/misc/postgresql.conf.sample === RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.66 diff -c -c -r1.66 postgresql.conf.sample *** src/backend/utils/misc/postgresql.conf.sample 25 Jan 2003 23:10:29 - 1.66 --- src/backend/utils/misc/postgresql.conf.sample 27 Jan 2003 23:39:15 - *** *** 134,140 #log_min_error_statement = error # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, !# info, notice, warning, error #debug_print_parse = false #debug_print_rewritten = false --- 134,140 #log_min_error_statement = error # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, !# info, notice, warning, error, panic(off) #debug_print_parse = false #debug_print_rewritten = false ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Release Scheduales: 7.2.4 & 7.3.2
Folks, I've been following this discussion, but I still haven't seen a definitive list of patches included in 7.2.4. Where can I find one? -- -Josh Berkus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Client failure allows backed to continue
As part of the training class I did, some people tested what happens when the client allocates tons of memory to store a result and aborts. What we found was that though elog was properly called: elog(COMMERROR, "pq_recvbuf: recv() failed: %m"); (I think that was the message.) the backend did not exit and kept eating CPU. I think the problem is that the elog code only exits on ERROR, not COMMERROR. Is there some way to fix this? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE
On Mon, 27 Jan 2003, Antti Haapala wrote: >For an INSERT command, the tag is INSERT oid rows, where rows > is the number of rows inserted, and oid is the object ID of the > inserted row if rows is 1, otherwise oid is 0. > > Wouldn't it be nice to add here > > If table doesn't contain row oids, in place of oid is the > primary key of the newly inserted record (if any) as column = > 'value' [ and column = 'value [ and ... ]] Well, as was pointed out, that's a lot of data to send back if your primary key is a huge text column, and you've still got a problem if you have a multi-column primary key. Since this sort of functionality is not all that frequently needed, I'd still say it would probably be cleaner to make a new query that selects the most recently inserted primary key. That means that a) you don't have to send back a potentially large amount of data unless the user asks for it, and b) multi-column primary keys work just fine. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_dump and inserts
I just checked and pg_dump -d _doesn't_ place the INSERT's in a transsaction. Seems it should, and perhaps add a: SET autocommit TO 'on' as well. Of course, that SET would fail when restoring to prior releases, but they don't have autocommit off anyway so it can be ignored. Comments? This would certainly speed up loads that use INSERT. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Caught up
I have read all my email while I was in Altanta. I don't think I will have time to apply any outstanding patches for a week or so from Japan. I will try tomorrow, but I have to polish my presentations too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] IPv6 patch
Peter Eisentraut wrote: > Bruce Momjian writes: > > > However, the server log messages stating an IPv6 socket was not made is > > only printed if the binary supports IPv6. The message seems to be a > > compromise between those who wanted a separate IPv6 GUC/flag and those > > who wanted it to silently fail on IPv6. > > I'm not sure. Those who wanted silence don't get any silence and those > who wanted a configurable hard failure get neither the configurability nor > any failure. That is the compromise. Neither gets what they want, but the final solution is closer to each. This is probably the best we can do. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] New hashed IN code ignores distinctiveness of subquery
Bradley Baetz <[EMAIL PROTECTED]> writes: > Hmm. OK, I poked through the code a bit more, and I think I now realise > why we were talking across each other. I've attached a 'patch' which > gets the mergejoin counts down to something reasonable. I've just committed a significant set of changes in the join cost estimation routines. On looking closer, they hadn't been upgraded for any of the recent changes --- they were still assuming that merge and hash join clauses could only be simple var = var, for instance. I did something about the mergejoin rescan issue, as well as modeling JOIN short-circuiting. All of the estimates are a bit crude, but certainly better than no model at all. I think this covers your concerns, though I'm still worried about whether it's okay to use the existing selectivity routines to compute selectivities in the JOIN_IN/JOIN_UNIQUE case. 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])
Re: [HACKERS] Request for qualified column names
On Mon, 2003-01-27 at 15:50, Larry Rosenman wrote: > --On Monday, January 27, 2003 15:49:06 -0500 Bruce Momjian > <[EMAIL PROTECTED]> wrote: > > > > > My idea on this after chat with Dave was to add a GUC option that puts > > the schema.table.column name as the default column label, rather than > > just the column name. (That's so easy, I think even I could do it.) If > > they over-ride it with AS, or if it is an aggregate or FROM subquery, we > > just return the default label as we do now --- we could return no label > > for those cases, but that seems too drastic. I am not overly excited > > about doing this at the protocol level unless there is major need for it. > DONT DEFAULT TO THE NEW ONE WITHOUT NOTICE! > > You will ***BREAK*** people. Agreed. This is the way we probably want to go -- but we'll need a guc for a release or 2 -- One release with default as current, one with default as new way, 7.6 can remove Guc. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Request for qualified column names
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > My idea on this after chat with Dave was to add a GUC option that puts > > the schema.table.column name as the default column label, rather than > > just the column name. > > And will you quotify things so that names containing dots, spaces, etc > are unambiguous? > > I think the above is a very poor substitute for doing it properly, > namely returning the values in separate fields. We should not allow > ourselves to get lured into a dead end just because we can do it without > obviously breaking the protocol. (I would argue that this breaks the > protocol anyway, though.) I don't see how it is worth modifying the client or protocol unless we have more demand for it. I would quote the labels, yes. > > I am not overly excited > > about doing this at the protocol level unless there is major need for it. > > I'm not excited about doing it at all, unless we do it right. We can > already have half-baked solutions on the client side ;-) It is easy on the server, quite hard on the client. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Request for qualified column names
Larry Rosenman wrote: > > > --On Monday, January 27, 2003 15:49:06 -0500 Bruce Momjian > <[EMAIL PROTECTED]> wrote: > > > > > My idea on this after chat with Dave was to add a GUC option that puts > > the schema.table.column name as the default column label, rather than > > just the column name. (That's so easy, I think even I could do it.) If > > they over-ride it with AS, or if it is an aggregate or FROM subquery, we > > just return the default label as we do now --- we could return no label > > for those cases, but that seems too drastic. I am not overly excited > > about doing this at the protocol level unless there is major need for it. > DONT DEFAULT TO THE NEW ONE WITHOUT NOTICE! > > You will ***BREAK*** people. Of course we are not going to default this to ON. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Request for qualified column names
Bruce Momjian <[EMAIL PROTECTED]> writes: > My idea on this after chat with Dave was to add a GUC option that puts > the schema.table.column name as the default column label, rather than > just the column name. And will you quotify things so that names containing dots, spaces, etc are unambiguous? I think the above is a very poor substitute for doing it properly, namely returning the values in separate fields. We should not allow ourselves to get lured into a dead end just because we can do it without obviously breaking the protocol. (I would argue that this breaks the protocol anyway, though.) > I am not overly excited > about doing this at the protocol level unless there is major need for it. I'm not excited about doing it at all, unless we do it right. We can already have half-baked solutions on the client side ;-) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 port patches submitted
Justin Clift writes: > The advantages to having the Win32 port be natively compatible with > Visual Studio is that it already is (no toolset-porting work needed > there), You're missing a couple of points here. First, the MS Visual whatever compiler can also be used with a makefile-driven build system. Second, the port as it stands isn't really compatible with anything except Jan's build instructions. There's a lot of work to be done before we get anything that builds out of the box in the 7.4 branch, and it's going to be a lot easier if we do it using the build system we already have and know. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Win32 port patches submitted
Bruce Momjian writes: > I do have a problem with MKS toolkit, which is a commerical purchase. > I would like to avoid reliance on that, though Jan said he needed their > bash. I don't believe that quite yet. Jan said the regression test script crashes Cygwin's bash, but how come it has never crashed anyone else's Cygwin bash? -- Peter Eisentraut [EMAIL PROTECTED] ---(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] IPv6 patch
Bruce Momjian writes: > However, the server log messages stating an IPv6 socket was not made is > only printed if the binary supports IPv6. The message seems to be a > compromise between those who wanted a separate IPv6 GUC/flag and those > who wanted it to silently fail on IPv6. I'm not sure. Those who wanted silence don't get any silence and those who wanted a configurable hard failure get neither the configurability nor any failure. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
--On Monday, January 27, 2003 15:49:06 -0500 Bruce Momjian <[EMAIL PROTECTED]> wrote: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. (That's so easy, I think even I could do it.) If they over-ride it with AS, or if it is an aggregate or FROM subquery, we just return the default label as we do now --- we could return no label for those cases, but that seems too drastic. I am not overly excited about doing this at the protocol level unless there is major need for it. DONT DEFAULT TO THE NEW ONE WITHOUT NOTICE! You will ***BREAK*** people. LER - -- Tom Lane wrote: "Reggie Burnett" <[EMAIL PROTECTED]> writes: > When talking about expressions,views, or any other construct that could > combine values from multiple tables I think it is reasonable to provide > null as the table name. Any one or any process requesting the table > name has to understand that not all SQL parameters have a base table > name. However, in the case where a single table is involved, table and > schema names should be available. That seems quite pointless. You hardly need the backend's help to determine which column belongs to which table in a single-table query. AFAICS this facility is only of interest if it does something useful in not-so-trivial cases. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. (That's so easy, I think even I could do it.) If they over-ride it with AS, or if it is an aggregate or FROM subquery, we just return the default label as we do now --- we could return no label for those cases, but that seems too drastic. I am not overly excited about doing this at the protocol level unless there is major need for it. --- Tom Lane wrote: > "Reggie Burnett" <[EMAIL PROTECTED]> writes: > > When talking about expressions,views, or any other construct that could > > combine values from multiple tables I think it is reasonable to provide > > null as the table name. Any one or any process requesting the table > > name has to understand that not all SQL parameters have a base table > > name. However, in the case where a single table is involved, table and > > schema names should be available. > > That seems quite pointless. You hardly need the backend's help to > determine which column belongs to which table in a single-table query. > AFAICS this facility is only of interest if it does something useful > in not-so-trivial cases. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL replay logic (was Re: [PERFORM] Mount options for Ext3?)
Is there a TODO here? I like the idea of not writing pg_controldata, or at least allowing it not to be read, perhaps with a pg_resetxlog flag so we can cleanly recover from a corrupt pg_controldata if the WAL files are OK. We don't want to get rid of the WAL file rename optimization because those are 16mb files and keeping them from checkpoint to checkpoint is probably a win. I also like the idea of allowing something between our "at the instant" recovery, and no recovery with fsync off. A "recover from last checkpooint time" option would be really valuable for some. --- Kevin Brown wrote: > Tom Lane wrote: > > Kevin Brown <[EMAIL PROTECTED]> writes: > > > One question I have is: in the event of a crash, why not simply replay > > > all the transactions found in the WAL? Is the startup time of the > > > database that badly affected if pg_control is ignored? > > > > Interesting thought, indeed. Since we truncate the WAL after each > > checkpoint, seems like this approach would no more than double the time > > for restart. > > Hmm...truncating the WAL after each checkpoint minimizes the amount of > disk space eaten by the WAL, but on the other hand keeping older > segments around buys you some safety in the event that things get > really hosed. But your later comments make it sound like the older > WAL segments are kept around anyway, just rotated. > > > The win is it'd eliminate pg_control as a single point of > > failure. It's always bothered me that we have to update pg_control on > > every checkpoint --- it should be a write-pretty-darn-seldom file, > > considering how critical it is. > > > > I think we'd have to make some changes in the code for deleting old > > WAL segments --- right now it's not careful to delete them in order. > > But surely that can be coped with. > > Even that might not be necessary. See below. > > > OTOH, this might just move the locus for fatal failures out of > > pg_control and into the OS' algorithms for writing directory updates. > > We would have no cross-check that the set of WAL file names visible in > > pg_xlog is sensible or aligned with the true state of the datafile > > area. > > Well, what we somehow need to guarantee is that there is always WAL > data that is older than the newest consistent data in the datafile > area, right? Meaning that if the datafile area gets scribbled on in > an inconsistent manner, you always have WAL data to fill in the gaps. > > Right now we do that by using fsync() and sync(). But I think it > would be highly desirable to be able to more or less guarantee > database consistency even if fsync were turned off. The price for > that might be too high, though. > > > We'd have to take it on faith that we should replay the visible files > > in their name order. This might mean we'd have to abandon the current > > hack of recycling xlog segments by renaming them --- which would be a > > nontrivial performance hit. > > It's probably a bad idea for the replay to be based on the filenames. > Instead, it should probably be based strictly on the contents of the > xlog segment files. Seems to me the beginning of each segment file > should have some kind of header information that makes it clear where > in the scheme of things it belongs. Additionally, writing some sort > of checksum, either at the beginning or the end, might not be a bad > idea either (doesn't have to be a strict checksum, but it needs to be > something that's reasonably likely to catch corruption within a > segment). > > Do that, and you don't have to worry about renaming xlog segments at > all: you simply move on to the next logical segment in the list (a > replay just reads the header info for all the segments and orders the > list as it sees fit, and discards all segments prior to any gap it > finds. It may be that you simply have to bail out if you find a gap, > though). As long as the xlog segment checksum information is > consistent with the contents of the segment and as long as its > transactions pick up where the previous segment's left off (assuming > it's not the first segment, of course), you can safely replay the > transactions it contains. > > I presume we're recycling xlog segments in order to avoid file > creation and unlink overhead? Otherwise you can simply create new > segments as needed and unlink old segments as policy dictates. > > > Comments anyone? > > > > > If there exists somewhere a reasonably succinct description of the > > > reasoning behind the current transaction management scheme (including > > > an analysis of the pros and cons), I'd love to read it and quit > > > bugging you. :-) > > > > Not that I know of. Would you care to prepare such a writeup? There > > is a lot of material in the source-code comments, but no coherent > > presentation. > > Be happy to. Just point me to any non-obvious source files. > > Thus far on my plate: > > 1.
Re: [HACKERS] urgent: db corruption - invalid TIDs?
We've done a bit more digging, and think we understand the problem. Looks like 7.3.1 is just now taking advantage of a performance flag that has been ignored up to now. The error message is a bit unclear. When we close this out internally, we'll document our findings to the list. So, in short, "never mind." Thanks, NL - Original Message - From: "Ned Lilly" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> Sent: Monday, January 27, 2003 11:06 AM Subject: Re: [HACKERS] urgent: db corruption - invalid TIDs? ps - this is on 7.3.1. It never happened on 7.2.3... - Original Message - From: "Ned Lilly" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> Sent: Monday, January 27, 2003 11:04 AM Subject: [HACKERS] urgent: db corruption - invalid TIDs? Has anyone seen this behavior? It's corrupted a production database. ERROR: heap_mark4update: (am)invalid tid WARNING: Error occurred while executing PL/pgSQL function issuewomaterial WARNING: line 40 at SQL statement ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] urgent: db corruption - invalid TIDs?
I don't know if this is terribly helpful, but the message was around in 7.2.x, look at src/backend/access/heap/heapam.c around line 1808 (or 1823 in the 7.3.x code) That said, I've not seen it before, perhaps you can expand upon what your function is doing? Robert Treat On Mon, 2003-01-27 at 11:06, Ned Lilly wrote: > ps - this is on 7.3.1. It never happened on 7.2.3... > > > > - Original Message - > From: "Ned Lilly" <[EMAIL PROTECTED]> > To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> > Sent: Monday, January 27, 2003 11:04 AM > Subject: [HACKERS] urgent: db corruption - invalid TIDs? > > > Has anyone seen this behavior? It's corrupted a production database. > > > ERROR: heap_mark4update: (am)invalid tid > WARNING: Error occurred while executing PL/pgSQL function issuewomaterial > WARNING: line 40 at SQL statement > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [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] help with I/O statistics in 7.3.1
Tom Lane wrote: > > Bogdan <[EMAIL PROTECTED]> writes: > > I can't make configuration to get "filesystem blocks in/out" > > to show valid data. > > Im only getting "0/0" all the time. > > Is that functionality available in postgres 7.3.1 ??? > > That is just printing what getrusage() tells it. Complain to your OS > authors. > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] I use RedHat 8.0 with all rpm.s installed. Is that posible that Linux (RedHat 8.0 distribution is incompatible) ? regards, Bogdan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] urgent: db corruption - invalid TIDs?
Ned Lilly wrote: Has anyone seen this behavior? It's corrupted a production database. Hi Ned, Just as information filler, which version of PostgreSQL, and which operating system? :-) Regards and best wishes, Justin Clift ERROR: heap_mark4update: (am)invalid tid WARNING: Error occurred while executing PL/pgSQL function issuewomaterial WARNING: line 40 at SQL statement -- "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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] urgent: db corruption - invalid TIDs?
ps - this is on 7.3.1. It never happened on 7.2.3... - Original Message - From: "Ned Lilly" <[EMAIL PROTECTED]> To: "PostgreSQL Hackers" <[EMAIL PROTECTED]> Sent: Monday, January 27, 2003 11:04 AM Subject: [HACKERS] urgent: db corruption - invalid TIDs? Has anyone seen this behavior? It's corrupted a production database. ERROR: heap_mark4update: (am)invalid tid WARNING: Error occurred while executing PL/pgSQL function issuewomaterial WARNING: line 40 at SQL statement ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] urgent: db corruption - invalid TIDs?
Has anyone seen this behavior? It's corrupted a production database. ERROR: heap_mark4update: (am)invalid tid WARNING: Error occurred while executing PL/pgSQL function issuewomaterial WARNING: line 40 at SQL statement ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
Well, certainly the driver could parse the sql and extract what it thinks is the table name. It just seems quite foreign to me to have a database engine go through the motions of determining column location and have ready access to all the metadata for all the columns in a resultset and then intentionally leave all that out of the FE/BE. Now, for us driver writers, if I have a select statement that has 20 columns I will need to extract the tablename myself (and hope I got it right) and then execute 20 separate queries to the database in order to implement any type of schema generation. I guess I don't understand this when just a few extra bytes in the RowDescriptor message would have fixed all this. Reggie > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED]] On Behalf Of Tom Lane > Sent: Monday, January 27, 2003 9:21 AM > To: Reggie Burnett > Cc: 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' > Subject: Re: [HACKERS] Request for qualified column names > > "Reggie Burnett" <[EMAIL PROTECTED]> writes: > > When talking about expressions,views, or any other construct that could > > combine values from multiple tables I think it is reasonable to provide > > null as the table name. Any one or any process requesting the table > > name has to understand that not all SQL parameters have a base table > > name. However, in the case where a single table is involved, table and > > schema names should be available. > > That seems quite pointless. You hardly need the backend's help to > determine which column belongs to which table in a single-table query. > AFAICS this facility is only of interest if it does something useful > in not-so-trivial cases. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(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] ECPG, threading and pooling
Michael Meskes <[EMAIL PROTECTED]> writes: >> Keep the thread hacking on the client side, please. Isn't there one of >> the ecpg/include/ files that would be suitable? > Yes, there is. Better make that there are. There is a file called > extern.h both in preproc as well as in lib and each file in the > directory does include the extern.h in its own directory. So we just > need to add it twice I guess. The preproc code doesn't need to be thread-safe does it? One issue we already fought with for large-file support is that that #define _REENTRANT probably needs to appear before you start to include any system header files. You may find that the best way to handle it is to make it a "-D_REENTRANT" added to CPPFLAGS (only within ecpg/lib), rather than trying to find a safe place to put it in the .h files. 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] Switching connection on the fly
On Mon, 27 Jan 2003, Antti Haapala wrote: > I need this feature also. The problem with set session authorization is > that you can always change back so it's not that secure. Actually I wanted > to have a function that could augment the privileges of user if supplied > the right password, which in turn has nothing to do with original > password. s/original/pg_shadow/ :) -- Antti Haapala +358 50 369 3535 ICQ: #177673735 ---(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] Request for qualified column names
"Reggie Burnett" <[EMAIL PROTECTED]> writes: > When talking about expressions,views, or any other construct that could > combine values from multiple tables I think it is reasonable to provide > null as the table name. Any one or any process requesting the table > name has to understand that not all SQL parameters have a base table > name. However, in the case where a single table is involved, table and > schema names should be available. That seems quite pointless. You hardly need the backend's help to determine which column belongs to which table in a single-table query. AFAICS this facility is only of interest if it does something useful in not-so-trivial cases. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Switching connection on the fly
On Mon, 27 Jan 2003, Shridhar Daithankar wrote: > I went thr http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-session- > authorization.html to get what it is. I didn't have an idea of such thing. > > Back to the topic, yes, pretty much except for few differences. > > 1) It says 'The session user identifier may be changed only if the initial > session user (the authenticated user) had the superuser privilege. Otherwise, > the command is accepted only if it specifies the authenticated user name.' > > That mean an ordinary user can not set session to any other authorised user. It > is like running setuid program with input accessible to any user. > > 2) Where do I specify password? I mean I take a password and start a connection > to database. But when it comes to switching connection, there is no password. > Probably because only superuser can switch connection? > > If there is a password clause there and if any user can switch to any user, > then it is the thing I am looking for. Probably even excluding switching to > superuser as a security measure. I need this feature also. The problem with set session authorization is that you can always change back so it's not that secure. Actually I wanted to have a function that could augment the privileges of user if supplied the right password, which in turn had nothing to do with original password. I believe it could be easy to implement such a function in C. But it could be better and easier to have pl/pgsql function that could set the session authorization. So, could it be made possible that pl/pgsql functions created by superuser could "set session authorization" even when not called by superuser (or user logged in as superuser)? -- Antti Haapala ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ECPG, threading and pooling
On Mon, Jan 27, 2003 at 09:30:50AM -0500, Tom Lane wrote: > Michael Meskes <[EMAIL PROTECTED]> writes: > > - Is it neccessary to patch c.h for ecpg? > > If you commit that part, it will be reverted forthwith (especially the > hardwired #define HAVE_PTHREAD_H ;-)). > > Keep the thread hacking on the client side, please. Isn't there one of > the ecpg/include/ files that would be suitable? Yes, there is. Better make that there are. There is a file called extern.h both in preproc as well as in lib and each file in the directory does include the extern.h in its own directory. So we just need to add it twice I guess. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ECPG, threading and pooling
On Mon, Jan 27, 2003 at 02:06:26PM +, Lee Kindness wrote: > > - Not sure with the version numbering but I can fix that later. > > As discussed on pgsql-hackers after the 7.3 release any "binary > incompatible" library change needs a major version number increase. Sorry, I was not precise enough. The major number has to be inceased, it's the minor numbers that need fixing. :-) Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] how do I get the table name from a query?
So unless I parse the SQL statement myself, I can't tell anything about the source table? To find out uniqueness, indexes, etc for fields in a source table, I need the table name and have to execute queries against pg_attribute and pg_indexes. But since the FE/BE doesn't return to me the source table name, I would have to parse this myself? Reggie > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED]] On Behalf Of Dave Cramer > Sent: Monday, January 27, 2003 8:42 AM > To: Reggie Burnett > Cc: 'Hackers' > Subject: Re: [HACKERS] how do I get the table name from a query? > > Reggie, > > At the moment, you can't, but please comment on my request for qualified > column names. This is the same issue and there are many collateral > issues which Tom has brought up. > > Dave > On Mon, 2003-01-27 at 09:30, Reggie Burnett wrote: > > When using the FE/BE, how do I retrieve the table name for a given > > column? Say I had the following SQL: > > > > > > > > Select c.id, c.name, d.phone from Members c, Addresses d where c.id < > > 200 > > > > > > > > From the docs of the FE/BE, I would see that the fields are named id, > > name, phone but how do I find out the source tables that each field > > came from? I need to know this to discover nullability of fields, > > uniqueness, etc. > > > > > > > > Thanks > > > > Reggie > -- > Dave Cramer <[EMAIL PROTECTED]> > Cramer Consulting > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Request for qualified column names
When talking about expressions,views, or any other construct that could combine values from multiple tables I think it is reasonable to provide null as the table name. Any one or any process requesting the table name has to understand that not all SQL parameters have a base table name. However, in the case where a single table is involved, table and schema names should be available. Reggie > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED]] On Behalf Of Tom Lane > Sent: Sunday, January 26, 2003 7:39 PM > To: Dave Cramer > Cc: PostgreSQL Hackers Mailing List > Subject: Re: [HACKERS] Request for qualified column names > > Dave Cramer <[EMAIL PROTECTED]> writes: > > So for a "select a, b, a+b as sum from c" returns c.a, c.b, ?table?.sum > > This might be something to consider as part of the planned protocol > overhaul. We cannot simply change the returned column names --- at > least not without breaking a lot of application code. But if we > return table name (and schema name too!) as separate fields of the > 'T' message, and make them accessible through new PQfoo accessor > functions, then no existing applications would break. > > But there are more than a few definitional issues to be settled before > you'll convince me this idea is fully baked. Some things that come to > mind immediately: > > What happens with views? Given > create view v as select col as vcol from tab; > select vcol from v; > are you expecting to get back "v.vcol"? Or "tab.col"? > > What happens with FROM-clause aliases? Supposing tab really has a > column "col", what do you expect to see from > select * from tab AS a(t1), tab AS b(t2) WHERE ... > You could make a case for either "tab.col, tab.col" or "a.t1, b.t2" > (in the latter case, you can't realistically return a schema name). > But you will probably break existing code if you do the former, since > currently the output columns are labeled t1, t2. > > What happens with join aliases (similar issues to above)? > > Do you think > select col as foo from tab > should return "tab.foo", or just "foo"? I'd lean to the latter; > "tab.foo" seems awfully misleading. Or maybe you're wanting it > to ignore the AS and return "tab.col"? Don't think that will fly. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] help with I/O statistics in 7.3.1
Bogdan <[EMAIL PROTECTED]> writes: > I can't make configuration to get "filesystem blocks in/out" > to show valid data. > Im only getting "0/0" all the time. > Is that functionality available in postgres 7.3.1 ??? That is just printing what getrusage() tells it. Complain to your OS authors. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] how do I get the table name from a query?
Reggie, At the moment, you can't, but please comment on my request for qualified column names. This is the same issue and there are many collateral issues which Tom has brought up. Dave On Mon, 2003-01-27 at 09:30, Reggie Burnett wrote: > When using the FE/BE, how do I retrieve the table name for a given > column? Say I had the following SQL: > > > > Select c.id, c.name, d.phone from Members c, Addresses d where c.id < > 200 > > > > From the docs of the FE/BE, I would see that the fields are named id, > name, phone but how do I find out the source tables that each field > came from? I need to know this to discover nullability of fields, > uniqueness, etc. > > > > Thanks > > Reggie -- Dave Cramer <[EMAIL PROTECTED]> Cramer Consulting ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ECPG, threading and pooling
Michael Meskes <[EMAIL PROTECTED]> writes: > - Is it neccessary to patch c.h for ecpg? If you commit that part, it will be reverted forthwith (especially the hardwired #define HAVE_PTHREAD_H ;-)). Keep the thread hacking on the client side, please. Isn't there one of the ecpg/include/ files that would be suitable? 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
[HACKERS] how do I get the table name from a query?
When using the FE/BE, how do I retrieve the table name for a given column? Say I had the following SQL: Select c.id, c.name, d.phone from Members c, Addresses d where c.id < 200 From the docs of the FE/BE, I would see that the fields are named id, name, phone but how do I find out the source tables that each field came from? I need to know this to discover nullability of fields, uniqueness, etc. Thanks Reggie
Re: [HACKERS] Switching connection on the fly
On 27 Jan 2003 at 9:16, Tom Lane wrote: > "Shridhar Daithankar" <[EMAIL PROTECTED]> writes: > > Is it possible for an established connection to backend, to switch user on the > > fly, if proper credentials are supplied? > > Are you looking for SET SESSION AUTHORIZATION? I went thr http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-session- authorization.html to get what it is. I didn't have an idea of such thing. Back to the topic, yes, pretty much except for few differences. 1) It says 'The session user identifier may be changed only if the initial session user (the authenticated user) had the superuser privilege. Otherwise, the command is accepted only if it specifies the authenticated user name.' That mean an ordinary user can not set session to any other authorised user. It is like running setuid program with input accessible to any user. 2) Where do I specify password? I mean I take a password and start a connection to database. But when it comes to switching connection, there is no password. Probably because only superuser can switch connection? If there is a password clause there and if any user can switch to any user, then it is the thing I am looking for. Probably even excluding switching to superuser as a security measure. But thanks for it. That is very close. Bye Shridhar -- And 1.1.81 is officially BugFree(tm), so if you receive any bug-reportson it, you know they are just evil lies."(By Linus Torvalds, [EMAIL PROTECTED]) ---(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] ECPG, threading and pooling
Shridhar Daithankar writes: > On 27 Jan 2003 at 14:06, Lee Kindness wrote: > > Michael Meskes writes: > > > Thanks a lot. I have no experience in multithreaded software development > > > so I cannot completely check your patch but it surely looks good. Shall > > > I commit it, or will you? I think we should get it into CVS for all to > > > test. > > Problem with it is it needs some changes to configure.in (and > > associated files) for it to be worthwhile. Checks would need to be > > added to determine is threads are supported on the build platform and > > the needed compile and link flags. I'm trying to get this together for > > libpq too. > Just out of curiosity, what happens when there are more than one competing > threading libraries? Like native threads and linuxthreads on freeBSD? It's all down to the checks in configure... Looking at many packages which have threads check in configure (e.g. openldap, mysql, mozilla, glib, ...) some let the user specify which theading library to use (e.g --with-threads=linuxthreads (or something)) while others just seem to pick up the first available... To be honest I am quite daunted by the checks needed (every other package seems to be doing the check differently!) and any/all help would be welcome! L. ---(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] Switching connection on the fly
"Shridhar Daithankar" <[EMAIL PROTECTED]> writes: > Is it possible for an established connection to backend, to switch user on the > fly, if proper credentials are supplied? Are you looking for SET SESSION AUTHORIZATION? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ECPG, threading and pooling
On 27 Jan 2003 at 14:06, Lee Kindness wrote: > Michael Meskes writes: > > On Mon, Jan 27, 2003 at 11:52:18AM +, Lee Kindness wrote: > > > I've spent a bit of time on making ecpg thread safe over Christmas, > > > while it's not finished i'm sure the attached patch is at least useful > > > and a step in the right direction. > > Thanks a lot. I have no experience in multithreaded software development > > so I cannot completely check your patch but it surely looks good. Shall > > I commit it, or will you? I think we should get it into CVS for all to > > test. > > Problem with it is it needs some changes to configure.in (and > associated files) for it to be worthwhile. Checks would need to be > added to determine is threads are supported on the build platform and > the needed compile and link flags. I'm trying to get this together for > libpq too. Just out of curiosity, what happens when there are more than one competing threading libraries? Like native threads and linuxthreads on freeBSD? Bye Shridhar -- Reliable source, n.:The guy you just met. ---(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] ECPG, threading and pooling
Michael Meskes writes: > On Mon, Jan 27, 2003 at 11:52:18AM +, Lee Kindness wrote: > > I've spent a bit of time on making ecpg thread safe over Christmas, > > while it's not finished i'm sure the attached patch is at least useful > > and a step in the right direction. > Thanks a lot. I have no experience in multithreaded software development > so I cannot completely check your patch but it surely looks good. Shall > I commit it, or will you? I think we should get it into CVS for all to > test. Problem with it is it needs some changes to configure.in (and associated files) for it to be worthwhile. Checks would need to be added to determine is threads are supported on the build platform and the needed compile and link flags. I'm trying to get this together for libpq too. > Just two questions: > - Is it neccessary to patch c.h for ecpg? No, but each ecpg source file would need something like: #ifdef HAVE_PTHREAD_H # include # include #endif It's in c.h just now for ease of testing. > - Not sure with the version numbering but I can fix that later. As discussed on pgsql-hackers after the 7.3 release any "binary incompatible" library change needs a major version number increase. Lee. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] help with I/O statistics in 7.3.1
From: [EMAIL PROTECTED] (Bogdan) Newsgroups: comp.databases.postgresql.questions Subject: help with I/O statistics in 7.3.1 NNTP-Posting-Host: 12.98.224.90 Message-ID: <[EMAIL PROTECTED]> I can't make configuration to get "filesystem blocks in/out" to show valid data. Im only getting "0/0" all the time. Is that functionality available in postgres 7.3.1 ??? Regards, Bogdan test4=# explain analyze select * from patients; LOG: query: explain analyze select * from patients; LOG: duration: 0.603887 sec LOG: QUERY STATISTICS ! system usage stats: ! 0.624269 elapsed 0.458985 user 0.123047 system sec ! [0.468750 user 0.125000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 7/4 [310/158] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 2742 read, 0 written, buffer hit rate = 3.59% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written QUERY PLAN - Seq Scan on patients (cost=0.00..4048.60 rows=131960 width=172) (actual time=0.04..562.97 rows=133886 loops=1) Total runtime: 602.42 msec (2 rows) test4=# test4=# show all; australian_timezones | off authentication_timeout | 60 autocommit | on checkpoint_segments| 3 checkpoint_timeout | 300 client_encoding| SQL_ASCII client_min_messages| notice commit_delay | 0 commit_siblings| 5 cpu_index_tuple_cost | 0.001 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 DateStyle | ISO with US (NonEuropean) conventions db_user_namespace | off deadlock_timeout | 1000 debug_assertions | on debug_pretty_print | on debug_print_parse | off debug_print_plan | off debug_print_rewritten | off default_statistics_target | 10 default_transaction_isolation | read committed dynamic_library_path | $libdir effective_cache_size | 1000 enable_hashjoin| on enable_indexscan | on enable_mergejoin | on enable_nestloop| on enable_seqscan | on enable_sort| on enable_tidscan | on explain_pretty_print | on fixbtree | on fsync | off geqo | on geqo_effort| 1 geqo_generations | 0 geqo_pool_size | 0 geqo_random_seed | -1 geqo_selection_bias| 2 geqo_threshold | 11 hostname_lookup| off krb_server_keyfile | unset lc_messages| C lc_monetary| C lc_numeric | C lc_time| C log_connections| off log_duration | on log_min_error_statement| panic log_pid| off log_statement | on log_timestamp | off max_connections| 32 max_expr_depth | 1 max_files_per_process | 1000 max_fsm_pages | 1 max_fsm_relations | 100 max_locks_per_transaction | 64 password_encryption| on port | 5432 pre_auth_delay | 0 random_page_cost | 4 search_path| $user,public server_encoding| SQL_ASCII server_min_messages| notice shared_buffers | 64 show_executor_stats| off show_parser_stats | off show_planner_stats | off show_source_port | off show_statement_stats | on silent_mode| off sort_mem | 1024 sql_inheritance| on ssl| off statement_timeout | 0 stats_block_level | off stats_command_string | off stats_reset_on_server_start| on stats_row_level| off stats_start_collector | on superuser_reserved_connections | 2 syslog | 0 syslog_facility| LOCAL0 syslog_ident | postgres tcpip_socket | on TimeZone | unknown trace_notify | off TRANSACTION ISOLATION LEVEL| READ COMMITTED transform_null_equals | off unix_socket_directory |
Re: [HACKERS] ECPG, threading and pooling
On Mon, Jan 27, 2003 at 11:52:18AM +, Lee Kindness wrote: Content-Description: message body text > Michael, > ... > I've spent a bit of time on making ecpg thread safe over Christmas, > while it's not finished i'm sure the attached patch is at least useful > and a step in the right direction. Thanks a lot. I have no experience in multithreaded software development so I cannot completely check your patch but it surely looks good. Shall I commit it, or will you? I think we should get it into CVS for all to test. Just two questions: - Is it neccessary to patch c.h for ecpg? - Not sure with the version numbering but I can fix that later. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy installer... now what to do with it?
> -Original Message- > From: Justin Clift [mailto:[EMAIL PROTECTED]] > Sent: 27 January 2003 11:51 > To: Dave Page > Cc: PostgreSQL Hackers Mailing List; PostgreSQL Cygwin Mailing List > Subject: Re: [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy > installer... now what to do with it? > > > Dave Page wrote: > > >>No real stress there, as I'm really sure the pgAdmin team and > >>yourself > >>will be able to give pointers on how to make that work properly. :) > > > > Step 1 is use an MSI compliant setup package. > > Ok, do you have any recommendations? Using M$ Visual > isn't > an option, but am willing to look at alternatives. I don't know of any freebies, other than the Microsoft Installer SDK which is a pain to use. I use Wise for Windows Installer for the ODBC driver, Installshield also does MSI format though. > Am curious as to whether packaging solutions other than MSI use merge > modules. Any idea? MSI is the underlying technology built into Windows and the packaging format, rather than the packaging software itself. They are pushing it's use because it allows centralised control of installations independently of the author of the tools (among other things). In other words, even if Fred Blogss (sorry Fred) writes a bad installer that will cause DLL hell, the installer service should sort out the mess. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msi/set up/about_windows_installer.asp Regards, Dave. ---(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] ECPG, threading and pooling
Michael, Michael Meskes writes: > On Thu, Jan 23, 2003 at 02:40:33PM +0530, Shridhar >Daithankar<[EMAIL PROTECTED]> wrote: > > I would like to use ECPG as it is relatively easy to code. However my > > application is multithreaded and also uses connecion pools. > > I'm afraid it needs some work to be thread-safe. sqlca is defined > statically. No big deal it seems to implement a thread safe version but > I haven't yet found the time. I've spent a bit of time on making ecpg thread safe over Christmas, while it's not finished i'm sure the attached patch is at least useful and a step in the right direction. Lee. ecpg-threadsafe.patch.gz Description: Binary data ---(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] [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy installer... now
Dave Page wrote: No real stress there, as I'm really sure the pgAdmin team and yourself will be able to give pointers on how to make that work properly. :) > Step 1 is use an MSI compliant setup package. Ok, do you have any recommendations? Using M$ Visual isn't an option, but am willing to look at alternatives. > Step 2 is then extremely easy. There are a number of advantages to this including: 1) DLL conflicts are handled properly by the installer service. 2) Installations can be properly rolled back in case they fail. 3) Installation patches can be created. 4) The base package can be built as a merge module which can then be included in any other setup program for seamless integration, and a guaranteed correct installation. These sound like worthwhile things to cater for. Point 4 here is very important. If people want to include PostgreSQL in their application (which is surely what we want?), all they need do is include the merge module in their own setup. This is how pgAdmin installs psqlODBC. The stup builder doesn't need to know how PostgreSQL installs and therefore doesn't have to re-write his own version of the installer, and risk getting it wrong. It also means that the installer service can correctly handle the installation of a PostgreSQL-included package onto a system that already has PostgreSQL installed. Am curious as to whether packaging solutions other than MSI use merge modules. Any idea? :-) Regards and best wishes, Justin Clift Regards, Dave. -- "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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE
> > Anyways, I've got an idea: what about having option that INSERTs return
> > "oid_status" in form...
>
> I don't understand exactly how an INSERT statement "returns" anything.
> An INSERT statement is not a function, is it?
I mean the backend message CompletedResponse (and
s/oid_status/PQoidStatus/ (as it's written in libpq-fe.h)) (ok, it's
deprecated now in favor of PQoidValue).
>From postgresql docs see section Frontend-Backend protocol:
Byte1('C')
Identifies the message as a completed response.
String
The command tag. This is usually a single word that
identifies which SQL command was completed.
For an INSERT command, the tag is INSERT oid rows,
where rows is the number of rows inserted, and oid
is the object ID of the inserted row if rows is 1,
otherwise oid is 0.
Wouldn't it be nice to add here
If table doesn't contain row oids, in place of oid
is the primary key of the newly inserted record (if any)
as column = 'value' [ and column = 'value [ and ... ]]
--
Antti Haapala
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] On Commit Triggers?
While waiting an application using the PostgreSQL database, I've come in the need of an On Commit Trigger. As you know such trigger doesn't not exist currently in the postgresql but I was wondering if it would be possible to add, maybe in a future version, or if I can obtain something similar with the actual code. Regards, Antonio Scotti ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Switching connection on the fly
Hi all, Recently solving a design problem for a friend, an idea crossed my mind. Is it possible for an established connection to backend, to switch user on the fly, if proper credentials are supplied? If this can be done, it would avoid initialization penalty of a new conenction and many applications which does their own user management, can deligate the task to backend. Many applications are written in such a way that application always connects and operates as one user and does necessary access control. There are situatons where such a design is best available choice. If it can switch connection on the fly, it will allow to have much finer control over database access. That would help immensely for any applications that use connection pooling. Right now, if an app uses connection pooling, it has to go via a single application user and do all the things on it's own. Besides I think this idea would be a smart implementation of what oracle called thin/virtual users. Any thoughts? Bye Shridhar -- The First Rule of Program Optimization: Don't do it.The Second Rule of Program Optimization (for experts only!): Don't do it yet.-- Michael Jackson ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy installer... now what to do with it?
> -Original Message- > From: Justin Clift [mailto:[EMAIL PROTECTED]] > Sent: 27 January 2003 02:38 > To: Dave Page > Cc: PostgreSQL Hackers Mailing List; PostgreSQL Cygwin Mailing List > Subject: Re: [CYGWIN] Have a PG 7.3.1 Windows (cygwin) easy > installer... now what to do with it? > > > The package here also has the ODBC drivers in it, but doesn't include > pgAdmin, nor Igor's WinMaster. It was originally assembled > with both of > them, but WinMaster didn't seem to really add anything (the package > auto-installs as a service), No, WinMaster shouldn't be required for a production system, though it can be handy for testing. > and with pgAdmin I was having trouble > getting it to register HighlightBox.ocx and use it once > installed. :( > No real stress there, as I'm really sure the pgAdmin team and > yourself > will be able to give pointers on how to make that work properly. :) Step 1 is use an MSI compliant setup package. Step 2 is then extremely easy. There are a number of advantages to this including: 1) DLL conflicts are handled properly by the installer service. 2) Installations can be properly rolled back in case they fail. 3) Installation patches can be created. 4) The base package can be built as a merge module which can then be included in any other setup program for seamless integration, and a guaranteed correct installation. Point 4 here is very important. If people want to include PostgreSQL in their application (which is surely what we want?), all they need do is include the merge module in their own setup. This is how pgAdmin installs psqlODBC. The stup builder doesn't need to know how PostgreSQL installs and therefore doesn't have to re-write his own version of the installer, and risk getting it wrong. It also means that the installer service can correctly handle the installation of a PostgreSQL-included package onto a system that already has PostgreSQL installed. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE
On Mon, 27 Jan 2003, Antti Haapala wrote:
> > I don't see why you need a unqiue identifier per row, nor do I see why,
> > if you are going to have one, it needs to be the same type across all
> > tables.
(Note here: it may not have been quite clear, but I'm not asking for
specific instances of where you might want to do this; I'm asking why it
should be forced upon every single table in the world, unless people a)
know that postgresql does this, and b) use special SQL extensions that
are not compatable with any other DMBS in the world.)
> If i had table with multi col primary key like...
>
> create table devices (
> major int4,
> minor int4,
> primary key (major, minor)
> );
>
> ... and do this:
>
> insert into devices (major, minor values (224, find_free_minor_for(224))
>
> should the database report something like
>
> INSERT '{<([\'224\', \'89\'])>}' 1
>
> which I could then parse in my client program and try to recover my
> fresh brand new primary key from it? No thanks...
It's up to you. It sounds like in this particular application, you want a
single integer as the primary key. So I have no objection to you changing
the table to be
create table devices (
id serial PRIMARY KEY,
major int4,
minor int4,
CONSTRAINT major_minor_unique UNIQUE (major, minor)
);
and then selecting currval('devices_id_seq') in order to find out what the
id of that record is.
But my first question here is, why do you want to do this with what is
effectively a hidden column, rather than explicitly showing that you need
this, as above? And why do you want to run the risk of OID wraparound when
you don't have to?
Next, other applications might not need to parse whatever the database
reports, or may know in advance what they've inserted. So why do you want
to, by default, impose the overhead of this special hidden column on these
other applications?
> Anyways, I've got an idea: what about having option that INSERTs return
> "oid_status" in form...
I don't understand exactly how an INSERT statement "returns" anything.
An INSERT statement is not a function, is it?
However, I have no objection to adding a function or other method to get
the primary key of the most recent insertion, assuming it exists, for
those folks with multi-column primary keys. Presumably it would generate
a result set just like a regular SELECT
cjs
--
Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
