Re: [HACKERS] More schema queries
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 18 May 2002 00:01 To: Dave Page Cc: [EMAIL PROTECTED] Subject: Re: More schema queries There was already some discussion about making a variant version of current_schemas() that would tell you the Whole Truth, including the implicitly searched schemas. Seems like we'd better do that; otherwise we'll find people hardwiring knowledge of these implicit search rules into their apps, which is probably a bad idea. Anyone have a preference about what to call it? I could see making a version of current_schemas() that takes a boolean parameter, or we could choose another function name for the implicit-schemas-too version. Use of a parameter seems fine to me. Save having Yet Another Function :-) and trying to figure out a sensible name for it! Curious. I have not noticed much of any change in postmaster startup time on Unix. Can you run a profile or something to see where the time is going? Probably, but I'd need hand-holding as I don't have a clue how to do that. I'm not sure how to do it on Cygwin, either. On Unix you'd build a profilable backend executable using cd pgsql/src/backend gmake clean gmake PROFILE=-pg all install same, run it, and then use gprof on the gmon.out file dumped at postmaster termination. Dunno if it has to be done differently on Cygwin. Well, I have gcc gprof so I assume it'll be pretty much the same. I'll have a play tonight. Thanks Tom, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Poster(s) needed
How about the postgresql logo - is there a source vector/postscript of it so that he can blow it up without res loss and print it? The logo designer may still have the source files. Cheerio, Link. At 02:56 AM 5/18/02 -0300, Marc G. Fournier wrote: Not that I'm aware of anyone making ... On Fri, 17 May 2002, Michael Meskes wrote: month, I'd like to get some PostgreSQL posters for the booth. But I have no idea where to find some. Do we have that kind of stuff? Or where could I get it? Preferable of course as file so I can print it myself. ---(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] WIN32 native ... lets start?!?
On Friday 17 May 2002 22:16, you wrote: Marc G. Fournier [EMAIL PROTECTED] writes: Might be an idea to create a pgsql-hackers-win32 list also? Or just pgsql-win32? Actually, I think that'd be a bad idea. The very last thing we need is for these discussions to get fragmented. The issues affect the whole backend AFAICS. regards, tom lane Yes, indeed. I would also like to discuss matters on this list, as one get's a 'heads up' from people in the know much easier. BTW, I'm in the process of doing the 'really only what is necessary for pg' ipc-stuff, and was wondering if anybody already did some configuration of the source tree towards MinGW?? How should we go about that? I would rather like not using cygwin's sh for that ;-), and we have no 'ln' !! Greetings, Joerg -- Leading SW developer - S.E.A GmbH Mail: [EMAIL PROTECTED] WWW: http://www.sea-gmbh.com ---(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] Updated CREATE FUNCTION syntax
Tom Lane [EMAIL PROTECTED] said: Seems like the only way to do that in the backend would be to find a way of slipping the function text past the lexer/parser entirely. While I can imagine ways of doing that, I think it'd be a *whole* lot cleaner to fix things on the client side. How do you feel about a psql hack that provides a function definition mode? More generally it could be a mode to enter random text and have it be converted to an SQL literal string. Perhaps psql= create function foo (int) returns int as psql- \beginliteral psql-LIT begin psql-LIT x := $1; psql-LIT ... psql-LIT end; psql-LIT \endliteral psql- language plpgsql; Essentially, \beginliteral and \endliteral each convert to a quote mark, and everywhere in between quotes and backslashes get doubled. We might want to specify that the leading and trailing newlines get dropped, too, though for function-definition applications that would not matter. Tom -- Given that 98% of my function defining is done is psql, this would be fine for me and solve my frustrations. It wouldn't help people that build functions in scripting languages or non-psql environments, however, but I don't know how common this is. What do others think? Thanks! -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updated CREATE FUNCTION syntax
Joel Burton [EMAIL PROTECTED] writes: Given that 98% of my function defining is done is psql, this would be fine for me and solve my frustrations. It wouldn't help people that build functions in scripting languages or non-psql environments, however, but I don't know how common this is. True, but I'm thinking that other development environments could provide equivalent features. (I seem to recall that pgAdmin already does, for example.) ISTM the reason we've not addressed this for so long is that no one could think of a reasonable way to solve it on the backend side. Maybe we just have to shift our focus. Another point worth considering is that because psql has its own smarts about locating query boundaries, it'd be very difficult to build a function-definition mode without making psql changes, anyway. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Sequence privileges
The documentation of the sequence privileges on the GRANT reference page doesn't match the code. Documented: currval:UPDATE nextval:UPDATE setval: UPDATE Actual: currval:SELECT nextval:UPDATE setval: UPDATE But shouldn't it more ideally be currval:SELECT nextval:SELECT + UPDATE setval: UPDATE because nextval allows you to infer the content of the sequence? (Cf. UPDATE tab1 SET a = b requires SELECT + UPDATE on tab1.) -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Poster(s) needed
Lincoln Yeoh [EMAIL PROTECTED] writes: How about the postgresql logo - is there a source vector/postscript of it so that he can blow it up without res loss and print it? I have EPS versions of both the elephant-in-crystal and cartoon-elephant logos. I'm pretty sure both are up on the website someplace, 'cause I didn't make either one. BTW, Marc will correct me if I'm wrong, but I think officially the crystal one is the PG project logo while the cartoon is more associated with PostgreSQL Inc. I tend to ignore this distinction though, since the crystal logo renders beautifully on screen but is nearly unusable for black-and-white printouts. So I like to use whichever fits the need at hand. 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] Trouble with pg_encoding_to_char
It means you are running a jdbc driver from 7.2 (perhaps 7.1, but I think 7.2) against a 6.5 database. While we try to make the jdbc driver backwardly compatable, we don't go back that far. You really should consider upgrading your database to something remotely current. thanks, --Barry [EMAIL PROTECTED] wrote: Hi, I've been developing a program with the postgres jdbc 2 driver, jdk-1.3.0 and postgres 6.5. When I start my program up it bombs like so: Something unusual has occured to cause the driver to fail. Please report this exception: Exception: java.sql.SQLException: ERROR: No such function 'pg_encoding_to_char' with the specified attributes Stack Trace: java.sql.SQLException: ERROR: No such function 'pg_encoding_to_char' with the specified attributes at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:94) at org.postgresql.Connection.ExecSQL(Connection.java:398) at org.postgresql.Connection.ExecSQL(Connection.java:381) at org.postgresql.Connection.openConnection(Connection.java:314) at org.postgresql.Driver.connect(Driver.java:149) Does anyone know what any of this means...? Regards, Youenn Université de Bretagne sud http://www.univ-ubs.fr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [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] Sequence privileges
Peter Eisentraut [EMAIL PROTECTED] writes: But shouldn't it more ideally be currval: SELECT nextval: SELECT + UPDATE setval: UPDATE because nextval allows you to infer the content of the sequence? (Cf. UPDATE tab1 SET a = b requires SELECT + UPDATE on tab1.) One objection is that testing for both privs will require two aclcheck calls (since aclcheck(SELECT|UPDATE) will check for the OR not the AND of the privileges). Not sure it's worth the overhead. Given that nextval() is really the only interesting operation on sequences (you cannot do a real UPDATE), I don't see a problem with interpreting UPDATE as the right to do nextval() for sequences. Since currval only returns to you the result of your own prior nextval, there is no real point in giving it a different privilege bit. Accordingly I think it *should* be testing UPDATE --- the docs are right and the code is wrong. (If it weren't for your recent addition of setuid functions, I'd question why currval bothers to make a privilege test at all.) SELECT still means what it says: the ability to do a select from the sequence, which lets you see the sequence parameters. So what we really have is: SELECT: read sequence as a table UPDATE: all sequence-specific operations. You could maybe make an argument that setval() should have a different privilege than nextval(), but otherwise this seems sufficient to me. There is now room in ACL to invent a couple of sequence-specific privilege bits if it bothers you to use UPDATE for the can-invoke- sequence-functions privilege, but I'm not sure it's worth creating a compatibility issue just to do 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] UTF-8 safe ascii() function
Dear all,
I would like to transform UTF-8 strings into Java-Unicode. Example :
- Latin1 : 'é'
- UTF-8 : 'é'
- Java Unicode = '\u00233'
Basically, a Unicode compatible ascii() function would be fine.
ascii('é') should return 233.
1) Has anyone written an ascii UTF-8 safe wrapper to ascii() function? If yes,
would you be so kind to publish this function on the list.
2) Are there plans to add an ascii() UTF-8 safe function to PostrgeSQL?
Best regards,
Jean-Michel POURE
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] SASL, compression?
I've been looking at the authentication and networking code and would like to float a trial balloon. 1) add SASL. This is a new standards-track protocol that is often described as PAM for network authentication. PostgreSQL could remove *all* protocol-specific authentication code and use standard plug-in libraries instead. (It's worth noting that SSL/TLS operates at a lower level than SASL. This has some interesting consequences, see below.) After the black-box authentication finishes, the postmaster will have up to pieces of information: the peer's client cert (SSL) and a string containing the Kerberos principal, user name verified with password/one-time-password/CRAM, etc. PostgreSQL authentication would be reduced to specifying which authentication methods are acceptable for each database, then mapping that authenticated user string and/or cert to a pguser. 2) add ZLIB compression. The last point needs a bit of explanation. With SASL, the buffers may be modified due to the authentication protocol selected, so the low-level routines in pqcomm.c and fe-connect.c must be modified. But since this is happening anyway, it would be easy to wrap sasl_encode with ZLIB compression and sasl_decode with ZLIB decompression, with pq_flush() (and client's equivalent) doing a sync flush of the compression buffer. You obviously don't need compression on the Unix socket or a fast network connection, but if you're on a T1 or slower the reduced transmission time should more than offset the time spent in compression/decompression. Drawbacks The biggest drawback, at least initially, is that the initial exchange will need to be totally rewritten. One possibility could use something like this: S: 220 example.com PostgreSQL 8.1 C: HELO client.com S: 250-example.com S: 250-AUTH ANONYMOUS KERBEROS4 list of authentication methods S: 250-STARTTLS server accepts SSL/TLS S: 250-COMPRESSION compress datastream S: 250 HELP C: STARTTLS pq.virtual.com allows virtual domains SSL/TLS negotation occurs *here* S: 250-pq.virtual.com S: 250-AUTH ANONYMOUS PLAIN KERBEROS4 note extra method S: 250-COMPRESSION S: 250-some extract functions only available with TLS/SSL sessions S: 250 HELP C: AUTH PLAIN user password use simple username/password S: 220 OK C: COMPRESSION ON S: 220 OK C: OPEN database S: 220 OK and then the system drops back to the existing data exchange format. Or it could look like something entirely different - the most important thing is that the server needs to provide a list of authentication methods, the client chooses one, and it either succeeds or the client can retry. However a protocol something like this has the strong advantage of being well-tested in the existing protocols. Bear ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [INTERFACES] libpgtcl - backend version information patch
[My apolgies if this turns up in the lists twice (now three times) but my mailer claims it's been in the queue for them too long. Not sure why it thinks that since it's only a few minutes since I sent it.] On Fri, 17 May 2002, Peter Eisentraut wrote: Nigel J. Andrews writes: I've attached a patch for libpgtcl which adds access to backend version numbers. This is via a new command: pg_version db channel major varname ?minor varname? ?patch varname? This doesn't truly reflect the way PostgreSQL version numbers are handled. Say for 7.2.1, the major is really 7.2 and the minor is 1. With the interface you proposed, the information major == 7 doesn't really convey any useful information. Ah, oops. I'll change it. I withdraw the patch submission I made yesterday (now two days back). I envisage this patch applied to 7.3 tip and to 7.2 for the 7.2.2 release mentioned a couple of days ago. The only problem with doing this for 7.2 that I can see is where people doing the 'package -exact require Pgtcl 1.x' thing, and how many of those are there? Even PgAccess doesn't use that. Normally we only put bug fixes in minor releases. PgAccess may get an exception, but bumping the version number of a library is stretching it a little. If you're intending to use the function for PgAccess, why not make it internal to PgAccess? That way you can tune the major/minor thing exactly how you need it. It did occur to me this morning that having it applied for 7.2.2 was perhaps silly as it was introducing a new feature and not a bug fix. This feature could be added to PgAccess but I felt it was general enough to be placed in the interface library. I think someone else suggested such a place a couple of weeks ago also. If there is a concensus that this should be done in the application layer I'll happily drop this patch completely. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pq_eof() broken with SSL
I came across another bug in the SSL code. backend/libpq/pqcomm.c:pq_eof() calls recv() to read a single byte of data to check for EOF. The character is then stuffed into the read buffer. This will not work with SSL. Besides the data being encrypted, you could end up reading a byte from an SSL control message instead of a data message, or messing up counts. Fortunately this procedure only seems to be called in some password code - if you use 'trust' or 'ident' then the SSL should work fine. The quick fix is to add another USE_SSL block, a better fix is to explicitly create a new abstraction layer. Bear ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] *new* libpgtcl - backend version information patch
This is similar to the same patch as I submitted Thursday, and hopefully withdrew in time after a response was made. I have repeated the description with appropiate changes for ease of reference. I've attached a patch for libpgtcl which adds access to backend version numbers. This is via a new command: pg_version db channel major varname ?minor varname? Using readonly variables rather than a command was my first choice but I decided that it was inappropiate for the library to start assigning global variable(s) when that's really the applications job and the command interface is consistent with the rest of the interface. Obviously, backend version numbers are specific to a particular connection. So I've created a new data structure, to keep the information as a distinct unit, and added an instance of the new structure to the Pg_ConnectionId type. The version information is retrieved from the given connection on first use of pg_version and cached in the new data structure for subsequent accesses. In addition to filling the named variables in the callers scope with version numbers/strings the command returns the complete string as returned by version(). It's not possible to turn this return off at the moment but I don't see it as a problem since normal methods of stopping unwanted values returned from procedures can be applied in the application if required. Perhaps the most significant change is that I've increased the package's version number from 1.3 to 1.4. This will adversly effect anyone using an application that requires a specific version of the package where their postgres installation is updated but their application has not been. I can't imagine there are many applications out there using the package management features of TCL though. This isn't a bug fix and is therefore for 7.3 not 7.2.2 -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updated CREATE FUNCTION syntax
Tom Lane [EMAIL PROTECTED] said: Joel Burton [EMAIL PROTECTED] writes: Given that 98% of my function defining is done is psql, this would be fine for me and solve my frustrations. It wouldn't help people that build functions in scripting languages or non-psql environments, however, but I don't know how common this is. True, but I'm thinking that other development environments could provide equivalent features. (I seem to recall that pgAdmin already does, for example.) ISTM the reason we've not addressed this for so long is that no one could think of a reasonable way to solve it on the backend side. Maybe we just have to shift our focus. Out of curiosity, Tom, why the preference for a solution like this rather than allowing for a much-less-common-than-' delimiter for the create function syntax? (Such as the [[ and ]] I suggested a few posts ago?) This would seem like something that wouldn't seem too difficult to do, and would work in all environments. That would have the advantage of being consistent as users switched from writing functions in psql to writing function-writing functions, to writing functions in other environments, etc. Thanks, - 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]
[HACKERS] Set-returning function syntax
For those who want to play on the bleeding edge of CVS, can someone provide the syntax for the recently-checked-in set-returning functions? I've got it figured out when I'm returning a many rows of single column, but not for many rows of several columns. If someone can do this, and no one has put together docs on this feature, I'll volunteer to write this up. Thanks! - J. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management Technology Consultant ---(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] SASL, compression?
On Sat, 18 May 2002 11:39:51 -0600 (MDT) Bear Giles [EMAIL PROTECTED] wrote: 1) add SASL. This is a new standards-track protocol that is often described as PAM for network authentication. PostgreSQL could remove *all* protocol-specific authentication code and use standard plug-in libraries instead. I'm not that clueful about SASL -- would this mean that we could get rid of the PostgreSQL code that does SSL connections, plus MD5, crypt, ident, etc. based authentication, and instead just use the SASL stuff? Or would SSL/TLS support need to co-exist with SASL? 2) add ZLIB compression. This was discussed before, and the conclusion was that compression is of fairly limited utility, and can be accomplished by using ssh -- so it's not worth the bloat. But there were some dissenting opinions at the time, so this might merit further discussion... The biggest drawback, at least initially, is that the initial exchange will need to be totally rewritten. I'd like to see a FE/BE protocol change in 7.4, so this might be a possibility at that point. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SASL, compression?
Bear Giles [EMAIL PROTECTED] writes: 1) add SASL. This is a new standards-track protocol that is often described as PAM for network authentication. PostgreSQL could remove *all* protocol-specific authentication code and use standard plug-in libraries instead. To me, new standards-track protocol translates as pie in the sky. When will there be tested, portable, BSD-license libraries that we could *actually* use? I'm afraid this really would end up meaning writing and/or supporting our own SASL code ... and I think there are more important things for the project to be doing. IMHO we've got more than enough poorly-supported authentication options already. Unless you can make a credible case that using SASL would allow us to rip out PAM, Kerberos, MD5, etc *now* (not in a few releases when everyone's switched to SASL), I think this will end up just being another one :-(. (It doesn't help any that PAM support was sold to us just one release cycle back on the same grounds that it'd be the last authentication method we'd need to add. I'm more than a tad wary now...) 2) add ZLIB compression. Why do people keep wanting to reinvent SSH tunneling? 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] [INTERFACES] libpgtcl - backend version information patch
Nigel J. Andrews [EMAIL PROTECTED] writes: This feature could be added to PgAccess but I felt it was general enough to be placed in the interface library. I think someone else suggested such a place a couple of weeks ago also. If there is a concensus that this should be done in the application layer I'll happily drop this patch completely. I guess I don't quite see the point of doing this in libpgtcl, as opposed to doing a select version() at the application level. It would take only a line or two of Tcl code to do that and parse the result of version(), so why write many lines of C to accomplish the same thing? 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] SASL, compression?
I'm not that clueful about SASL -- would this mean that we could get rid of the PostgreSQL code that does SSL connections, plus MD5, crypt, ident, etc. based authentication, and instead just use the SASL stuff? We would still need the ability to map user identities - pgusers for those methods where the client can't specify an arbitrary user name (e.g., Kerberos and GSSAPI), but strictly speaking that's an authorization problem, not an authentication problem, and it can be handled entirely within the backend. [W]ould SSL/TLS support need to co-exist with SASL? Yes. SASL effectively works at the application layer. It's now common practice for one of the application commands to be STARTTLS (perhaps by another name) that both sides use as a signal to negotiate a TLS/SSL session. The benefit of this approach is that it's easily migrated to Unix sockets, IPv6, etc. 2) add ZLIB compression. This was discussed before, and the conclusion was that compression is of fairly limited utility, and can be accomplished by using ssh -- so it's not worth the bloat. But there were some dissenting opinions at the time, so this might merit further discussion... I agree, it wasn't worth the effort with the existing code. But if we rewrite the lowest level routines then the amount of bloat can be minimized. Bear ---(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] SASL, compression?
Bear Giles [EMAIL PROTECTED] writes: 1) add SASL. This is a new standards-track protocol that is often described as PAM for network authentication. To me, new standards-track protocol translates as pie in the sky. When will there be tested, portable, BSD-license libraries that we could *actually* use? http://asg.web.cmu.edu/sasl/sasl-implementations.html Unless you can make a credible case that using SASL would allow us to rip out PAM, Kerberos, MD5, etc *now* (not in a few releases when everyone's switched to SASL), I think this will end up just being another one :-(. http://asg.web.cmu.edu/sasl/sasl-projects.html If it's being used in Sendmail, Cyrus IMAP and OpenLDAP, with preliminary work (sponsored by Carnegie Mellon University) in supporting it for CVS and LPRng and possibly SSH I think it's safe to say it's beyond vaporware at this point. The only reason I was waving my hands a bit is that I'm not sure if SASL 2.x is considered production-ready yet. We could support SASL 1.x, but if 2.x is coming out within 6-12 months then it may make more sense to target 2.x instead of releasing 1.x today, then switching to 2.x in the next release. If there's a concensus that we should proceed, I would also be the first to argue that we should contact CMU for assistance in the conversion. Hopefully they have enough experience with their cyrus package that we can really put this issue to bed. (Meanwhile PostgreSQL would get more free advertising as another major project using their SASL library.) (It doesn't help any that PAM support was sold to us just one release cycle back on the same grounds that it'd be the last authentication method we'd need to add. I'm more than a tad wary now...) Umm... I don't know what to say. This is a common misunderstanding of PAM (and one reason I *really* hate those PAM Kerberos modules) but people keep repeating it. PAM was only designed for local use, but people keep trying to use it for network authentication even though us security freaks keep pointing out that using some of those modules on a network will leave your system wide open. In contrast SASL was designed from the start to work over an untrusted network. This isn't to say that PAM support is totally useless - it may be a clean way to handle the ongoing Kerberos principal - pguser issue, but it's a nonstarter for authentication purposes unless you know you're on the Unix socket. 2) add ZLIB compression. Why do people keep wanting to reinvent SSH tunneling? One good reason is that secure sites will prohibit them. SSH tunnels require that clients have shell accounts on the remote system, and on a dedicated database server you may have no accounts other than the sysadmins who administer the box. I'm aware of the various tricks you can do - setting the shell to /bin/false, requiring RSA authentication and setting the no-tty flag in the 'known_keys' file, etc., but at the end of the day there are still extra shell accounts on that system. SSH tunnels are a good stopgap measure while you add true TLS/SSL support, but they can't be considered a replacement for that support. Bear ---(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] [INTERFACES] libpgtcl - backend version information
On Sat, 18 May 2002, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: This feature could be added to PgAccess but I felt it was general enough to be placed in the interface library. I think someone else suggested such a place a couple of weeks ago also. If there is a concensus that this should be done in the application layer I'll happily drop this patch completely. I guess I don't quite see the point of doing this in libpgtcl, as opposed to doing a select version() at the application level. It would take only a line or two of Tcl code to do that and parse the result of version(), so why write many lines of C to accomplish the same thing? Yes, you're right. It is only a couple of lines to do the exec, error checking and parsing. Someone mentioned how it might be worth considering putting version testing into the library. I thought it a reasonable idea, something that would be reasonably be expected to reused across applications and as I'm not putting forward anything for pgaccess until it's decided what the heck is going on with it thought I'd do the libpgtcl version of it. I see the pros as: version information is accessable to all TCL applications without each having to worry about getting it, it comes ready to support multiple DB connections per application. The cons: well I don't see anything similar in the perl interface and it's not in libpq so as the other interfaces are essentially wrappers for libpq it shouldn't be in libpqtcl either, there's more C code than TCL code would take (still, I could change it to use a Tcl_eval if it's lines of code that count) -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Set-returning function syntax
Joel Burton wrote:
For those who want to play on the bleeding edge of CVS, can someone
provide the syntax for the recently-checked-in set-returning
functions? I've got it figured out when I'm returning a many rows of
single column, but not for many rows of several columns.
For multiple columns, you need a composite data type defined --
basically you need to create a table, even if it is an unused shell,
which has the column names and data types of the returned tuple. See
below for more.
If someone can do this, and no one has put together docs on this
feature, I'll volunteer to write this up.
I hadn't gotten to the docs yet, but if you wanted to write something up
that would be great! :) I'll certainly help too.
Attached is the script I've been using to test as I go. It shows the
usage of SRFs in a variety of situations (note that the C function tests
require contrib/dblink installed). There's also a description in one of
my earlier posts. Here is a recap, edited to the latest reality:
How it currently works:
---
1. The SRF may be either marked as returning a set or not. A function
not marked as returning a set simply produces one row.
2. 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.
3. The SRF may be aliased in the FROM clause, but it also be left
unaliased. If a function is used in the FROM clause with no alias, the
function name is used as the relation name.
Hope that's a start.
Thanks,
Joe
DROP TABLE foo2;
CREATE TABLE foo2(fooid int, f2 int);
INSERT INTO foo2 VALUES(1, 11);
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
DROP FUNCTION foot(int);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;'
LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(fooid) z where foo2.f2 = z.f2;
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid =
foo2.fooid);
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid);
-- nested functions
select * from foot(sin(pi()/2)::int);
DROP TABLE foo;
CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid =
$1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = b
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid
= $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
-- sql, proretset = f, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;'
LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
-- sql, proretset = t, prorettype = c
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;'
LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
DROP VIEW vw_getfoo;
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
-- C, proretset = f, prorettype = b
SELECT * FROM dblink_replace('123456789987654321', '99', 'HelloWorld');
DROP VIEW vw_dblink_replace;
CREATE VIEW vw_dblink_replace AS SELECT * FROM dblink_replace('123456789987654321',
'99', 'HelloWorld');
SELECT * FROM vw_dblink_replace;
-- C, proretset = t, prorettype = b
SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
DROP VIEW vw_dblink_get_pkey;
CREATE VIEW vw_dblink_get_pkey AS SELECT dblink_get_pkey FROM dblink_get_pkey('foo');
SELECT * FROM vw_dblink_get_pkey;
-- plpgsql
--DROP FUNCTION getfoo(int);
--CREATE OR REPLACE FUNCTION testplpgsql() RETURNS setof int AS 'DECLARE fooint int;
BEGIN SELECT fooid into fooint FROM foo; RETURN fooint; END;' LANGUAGE 'plpgsql';
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Sequence privileges
Tom Lane wrote: SELECT still means what it says: the ability to do a select from the sequence, which lets you see the sequence parameters. So what we really have is: SELECT: read sequence as a table UPDATE: all sequence-specific operations. Since the sequence-specific operations are really just function calls, maybe it should be: SELECT: read sequence as a table EXECUTE: all sequence-specific operations. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Sequence privileges
Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: what we really have is: SELECT: read sequence as a table UPDATE: all sequence-specific operations. Since the sequence-specific operations are really just function calls, maybe it should be: SELECT: read sequence as a table EXECUTE: all sequence-specific operations. But is it worth creating a compatibility problem for? Existing pg_dump scripts are likely to GRANT UPDATE. They certainly won't say GRANT EXECUTE since that doesn't even exist in current releases. I agree that EXECUTE (or some sequence-specific permission name we might think of instead) would be logically cleaner, but I don't think it's worth the trouble of coming up with a compatibility workaround. UPDATE doesn't seem unreasonably far off the mark. 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] Set-returning function syntax
Tatsuo Ishii [EMAIL PROTECTED] writes: Does your SRF function allow to return a setof composite data type using C function? If so, how can I write such that C function? The setof part is documented in src/backend/utils/fmgr/README. There's no good documentation for returning tuples at the moment, but basically you return a pointer to a TupleTableSlot. (Re-use the same slot on each call to avoid memory leakage.) There's an example in src/backend/executor/functions.c --- look at the uses of funcSlot. One reason this isn't documented is that it's really ugly. It might be a good idea to change it before we start having lots of user-written code that depends on it ... 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] Set-returning function syntax
Tatsuo Ishii wrote: Does your SRF function allow to return a setof composite data type using C function? If so, how can I write such that C function? I couldn't find any example or explanation so far. You referred dblink, but in my understanding it does not have any function that returns a setof composite data type. I haven't written a C function yet that returns a composite type. You are correct that dblink does not have an example which returns composite type, because that wasn't even possible when I wrote the dblink code ;-) At least initially, a C function returning a composite type will have to do alot of dirty work -- i.e. something like: - manually form a tuple based on the return type relation attributes - save the tuple in a tuple table slot - return a pointer to the slot as a datum I don't know what other complications may be lurking, but I will try to get a working example sometime this coming week and post it to HACKERS. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Set-returning function syntax
Tatsuo Ishii wrote: Does your SRF function allow to return a setof composite data type using C function? If so, how can I write such that C function? I Just to follow-up, here's a quick look at what works and what doesn't, at least using my test script. SELECT * FROM myfunc(); Language RetSet RetType Status --- --- --- - C t b OK C t c Not tested C f b OK C f c Not tested SQL t b OK SQL t c OK SQL f b OK SQL f c OK PL/pgSQL t b No retset support PL/pgSQL t c No retset support PL/pgSQL f b OK PL/pgSQL f c OK - RetSet: t = function declared to return setof something RetType: b = base type; c = composite type Same cases work when a view is defined as SELECT * FROM myfunc(). Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Set-returning function syntax
The setof part is documented in src/backend/utils/fmgr/README. There's no good documentation for returning tuples at the moment, but basically you return a pointer to a TupleTableSlot. (Re-use the same slot on each call to avoid memory leakage.) There's an example in src/backend/executor/functions.c --- look at the uses of funcSlot. That was almost same as I guessed:-) One reason this isn't documented is that it's really ugly. It might be a good idea to change it before we start having lots of user-written code that depends on it ... Sounds like a good idea. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Unbounded (Possibly) Database Size Increase - Toasting
On Sat, 2002-05-11 at 11:24, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Was it not the case that lazy vacuum had problems freeing tuples that have toasted fields ? News to me if so. regards, tom lane It looks like this may in fact be the case. I performed a number of tests using the previous setup, but shortening the row length and using pg_attribute.attstorage to untoast the text field for some of the tests. The difference is striking. The behaviour of the untoasted case is pretty much as expected : the database grows a bit and then stabilizes at some size. However I could not get any size stabilization in the toasted case. Here are (some) of my test results : Fsm Siz |Threads|Toast |Init(M)|End (M)|Stable |Stable Time(h) |Run Time(h) 2 | 2 |Y | 166 | 380 | N | - |17 6 | 2 |Y | 166 | 430 | N | - |20 1 | 2 |N | 162 | 235 | Y | 0.5 |1 2 | 2 |N | 166 | 235 | Y | 0.5 |13 6 | 2 |N | 166 | 235 | Y | 0.5 |13 legend : Fsm Siz = max_fsm_pages Threads = no. update threads Toast = whether body field was toasted Init= initial database size End = final database size Stable = whether database growth had stopped Stable Time = when stable size was achieved Run Time= length of test run (excluding initial database population) Average vacuum time = 300s Typical (1 thread) entire table update time = 2000s Row length = 7.5K The scripts I used are here : http://homepages.slingshot.co.nz/~markir/tar/test/spin.tar.gz At this point I am wondering about sending this in as a bug report - what do you think ? regards, Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
