Re: [GENERAL] [HACKERS] New PostgreSQL Committers
On Mon, Dec 07, 2009 at 10:49:13AM +, Dave Page wrote: On behalf of the core team, I'm pleased to announce that the Congratulations! +1 Congrats to you all, and thanks for the contributions, both past and future. As an aside, this sort of thing is one of the best signs to an external user of the health of the PostgreSQL project: the 'orderly transfer of power' as it were. I'm always cautious about adopting a project with a limited set of core developers (often one) no matter how good the software. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] Problems with outer joins in 7.1beta5
On Fri, Mar 16, 2001 at 10:17:33AM -0800, Barry Lind wrote:
My feeling is that postgres has misinterpreted the SQL92 spec in this
regards. But I am having problems finding an online copy of the SQL92
spec so that I can verify.
What I would expect the syntax to be is:
table as alias (columna as aliasa, columnb as aliasb,...)
This will allow the query to work regardless of what the table column
order is. Generally the SQL spec has tried not to tie query behaviour
to the table column order.
What you expect, and what's in the spec. can be very different. As
the following quote shows, the definition is in fact order dependent:
note that a derived column list is a simple comma delimited list of
column names.
Quote from SQL'92:
6.3 table reference
Function
Reference a table.
Format
table reference ::=
table name [ [ AS ] correlation name
[ left paren derived column list right paren ] ]
| derived table [ AS ] correlation name
[ left paren derived column list right paren ]
| joined table
derived table ::= table subquery
derived column list ::= column name list
column name list ::=
column name [ { comma column name }... ]
Syntax Rules
[...]
7) If a derived column list is specified in a table reference,
then the number of column names in the derived column list
shall be the same as the degree of the table specified by the
derived table or the table name of that table reference,
and the name of the i-th column of that derived table or the
effective name of the i-th column of that table name is the
i-th column name in that derived column list.
---(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: [GENERAL] pg_dump's over 2GB
On Fri, Sep 29, 2000 at 11:41:51AM -0500, Jeff Hoffmann wrote: Bryan White wrote: I am thinking that instead I will need to pipe pg_dumps output into gzip thus avoiding the creation of a file of that size. sure, i do it all the time. unfortunately, i've had it happen a few times where even gzipping a database dump goes over 2GB, which is a real PITA since i have to dump some tables individually. generally, i do something like pg_dump database | gzip database.pgz Hmm, how about: pg_dump database | gzip | split -b 1024m - database_ Which will give you 1GB files, named database_aa, database_ab, etc. to dump the database and gzip -dc database.pgz | psql database cat database_* | gunzip | psql database Ross Reedstrom -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
Re: FW: [GENERAL] Count Distinct
On Thu, Aug 24, 2000 at 10:35:49AM -0700, Ryan Williams wrote: I find that if I create the table described in the email, in psql I recieve 'ERROR: Attribute 'row' not found'... But this isn't exactly 'ERROR: parser: parse error at or near "distinct"'... Right, that's the error you get from doing this in 6.5.x. The original poster has already been advised to upgrade to 7.0.2. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Great Bridge benchmark results for Postgres, 4 others
On Tue, Aug 15, 2000 at 12:21:25PM -0400, Ned Lilly wrote: Oh, Dan, I'm not that clever... ;-) But I *can* tell you that the market leading proprietary RDBMS products we tested were not IBM, Informix, or Sybase. And in reply to the MySQL version comment/question, Ned said: "We only used the released versions of each database." I took that to mean they used the latest released version of each database. One thing I couldn't deduce: which operating system where the commercial RDBMs run on top of? NT for one of them, for sure, but the other can probably run on either of the quoted OSs. If it was run on NT, we might be seeing the linux vs. NT effect. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] +/- Inf for float8's
On Mon, Aug 14, 2000 at 02:33:55PM +1000, Tim Allen wrote: I'm just trying out PG7.0.2, with a view to upgrading from 6.5.3, and I've found one quirk a little troublesome. Not sure whether I'll get any sympathy, but I shall ask anyway :). We find it convenient to be able to store +/- infinity for float8 values in some database tables. With Postgres 6.5.3, we were able to get away with this by using the values -1.79769313486232e+308 for -Inf and 1.79769313486232e+308 for Inf. This is probably not very portable, but anyway, it worked fine for us, on both x86 Linux and SGI IRIX. One thing, though, to get these numbers past the interface we had to put them in quotes. It seemed as though there was one level of parsing that didn't like these particular numbers, and one level of parsing that coped OK, and using quotes got it past the first level. Now, however (unfortunately for us), this inconsistency in the interface has been "fixed", and now we can't get this past the interface, either quoted or not. Fixing inconsistencies is, of course, in general, a good thing, which is why I'm not confident of getting much sympathy :). Breaking working apps is never a good thing, but that's part of why it went from 6.X to 7.X. So, any suggestions as to how we can store +/- infinity as a valid float8 value in a database table? Right: the SQL standard doesn't say anything about what to do for these cases for floats (except by defining the syntax of an approximate numeric constant as basically a float), but the IEEE754 does: as you discovered below, they're NaN, -Infinity, and +Infinity. I notice, btw, that 'NaN' is accepted as a valid float8. Is there any particular reason why something similar for, eg '-Inf' and 'Inf' doesn't also exist? Just discovered, there is a special number 'Infinity', which seems to be recognised, except you can't insert it into a table because it reports an overflow error. Getting warm, it seems, but not there yet. And there doesn't seem to be a negative equivalent. And this is a bug. From looking at the source, I see that Thomas added code to accept 'NaN' and 'Infinity' (but not '-Infinity'), and Tom Lane tweaked it, but it's never been able to get an Infinity all the way to the table, as far as I can see: the value gets set to HUGE_VAL, but the call to CheckFloat8Val compares against FLOAT8_MAX (and FLOAT8_MIN), and complains, since HUGE_VAL is _defined_ to be larger than DBL_MAX. And, there's no test case in the regression tests for inserting NaN or Infinity. (Shame on Thomas ;-) I think the right thing to do is move the call to CheckFloat8Val into a branch of the test for NaN and Infinity, thereby not calling it if we've been passed those constants. I'm compiling up a test of this right now, and I'll submit a patch to Bruce if it passes regression. Looks like that function hasn't been touch in a while, so the patch should apply to 7.0.X as well as current CVS. some time later Looks like it works, and passes the regression tests as they are. I'm patching the tests to include the cases 'NaN', 'Infinity', and '-Infinity' as valid float8s, and 'not a float' as an invalid representation, and rerunning to get output to submit with the patch. This might be a bit hairy, since there are 5 different expected/float8* files. Should I try to hand patch them to deal with the new rows, or let them be regenerated by people with the appropriate platforms? later again Bigger problem with changing the float8 regression tests: a lot of our math functions seem to be guarded with CheckFloat8Val(result), so, if we allow these values in a float8 column, most of the math functions with elog(). It strikes me that there must have been a reason for this at one time. There's even a #define UNSAFE_FLOATS, to disable these checks. By reading the comments in old copies of float.c, it looks like this was added for an old, buggy linux/Alpha libc that would throw floating point exceptions, otherwise. Is there an intrinsic problem with allowing values outside the range FLOAT8_MAX = x =FLOAT8_MIN ? 'ORDER BY' seems to still work, with 'Infinity' and '-Infinity' sorting properly. Having a 'NaN' in there breaks sorting however. That's a current, live bug. Could be fixed by treating 'NaN' as a different flavor of NULL. Probably a fairly deep change, however. Hmm, NULL in a float8 sorts to the end, regardless of ASC or DESC, is that right? Anyway, here's the patch for just float.c , if anyone wants to look at it. As I said, it passes the existing float8 regression tests, but raises a lot of interesting questions. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 Index: backend/utils/adt/float.c === RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt
Re: [GENERAL] PostgresSQL drop table - drop serial
On Mon, Aug 14, 2000 at 02:56:05PM -0400, Jason Hihn wrote: /* please forgive me if you've seen this before. I've tried sending this several times, but I have not seen it show up on the list yet. I was having some subscribing trouble */ Hello. I was reading through the postgre docs, and saw that it was listed to 'fix' the non-autodrop of serials. If that is correct, then I'd like to pursuade you to not do that, or at least be able to disable it. I'd presume that the fix would involve only auto dropping sequences that where auto created. In order to get two tables using one sequence, you're going to have to hand code the DEFAULT for at least one of them, so it's (almost) no work for you to just do it twice. That has the benefit of allowing you to use a more descriptive name for this sequence, as well as being absolutely sure what the sequence name _is_. In short, don't worry about it: the developer's go to a lot of trouble to _not_ break backwards compatability, and if they must, make sure there's a simple upgrade path. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Re: 4 billion record limit?
On Fri, Jul 28, 2000 at 11:48:10AM -0500, Keith G. Murphy wrote: Mitch Vincent wrote: There is something else that many aren't considering. In every application I've ever written to use any database I use ID numbers of my own making, always they're integer. 4 billion is the limit on any integer field, not just the OID so there are limitations everyone should realize when using any integer for any kind of record identification purposes.. That's an excellent point, especially considering that *sequences* use an integer to hold their max_value, which is by default 2,147,483,647. You cannot go larger than that, either. I guess it's constrained to be positive. So OIDs give you more potential unique values than sequences, far as I can tell. However, in each case, you've got a per table (per field, really) limit, not a per database. Not to mention that there are work arounds: two int fields form a nice 64 bit compund key, without any need for a 64 bit int custom type. Admittedly cumbersome to use, but standard SQL. The sequence is a bit more or a problem, but since it's non standard SQL anyway, writing your own sequence that uses a numeric counter gives you potentially infinite serials. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] location of change list?
On Thu, Jul 13, 2000 at 04:58:54PM -0400, Tom Lane wrote: Ed Loehr [EMAIL PROTECTED] writes: Can anyone point me to a list of changes and bug-fixes *by release* for 7.0.1 and 7.0.2 over 7.0? The only really accurate info is in the CVS logs. Bruce usually prepares a summary for the release history, but if that's not good enough for you, get out your cvs client and look for yourself. The best way I've found so far is to cd to the top level of the area you are interested in (probably the top of your copy of the source tree) and do cvs log -rREL7_0_PATCHES -d '2000-05-10' -N | more (substitute appropriate branch name and date limit as needed; this would get you all log messages in the 7.0.* branch since 7.0 release). This is still pretty noisy --- it prints header info for all files including ones that haven't been modified in that branch, which tends to swamp out the stuff you're looking for :-(. Does anyone have a better recipe? Bruce has a shell script in src/tools for cleaning up the CVS log output, merging common log entries, but I can't get it to work (I get complaints from cat and awk: wallace$ ./pgcvslog -r '\.2\.[0-9]*$' ../log cat: invalid option -- r Try `cat --help' for more information. awk: line 6: regular expression compile failed (missing operand) * print blank line separating entries * ) If you've got a CVS tree handy, there's a nifty perl script at: http://www.red-bean.com/~kfogel/cvs2cl.shtml that generates a GNU style ChangeLog directly from the cvs logs. Note that we don't seem to get a log entry for the release itself: is one commited? Here's the top of running: cvs2cl -l "-d'2000-05-10'" -F REL7_0_PATCHES -r -b 2000-07-13 00:52 tgl * src/backend/optimizer/path/indxpath.c (REL7_0_PATCHES.1): Backpatch backwards-index-scan fix. 2000-07-07 16:29 tgl * src/backend/utils/adt/: like.c, regexp.c, varchar.c (REL7_0_PATCHES.[1,1,1]): Back-patch StrNCpy fix. 2000-07-07 10:41 momjian * doc/: FAQ_Linux_German (1.1), FAQ_Linux_Italian (1.1), src/FAQ/FAQ_hpux.html (1.2), src/FAQ/FAQ_irix.html (1.2), src/FAQ/FAQ_linux.html (1.2), src/FAQ/FAQ_solaris.html (1.2): Remove HTML FAQ files that are really just text files. [...] And here's the main trunk: 2000-07-14 11:04 thomas * doc/src/sgml/release.sgml (1.57): Fix munged markup from previous commit. 2000-07-14 10:43 thomas * src/: backend/parser/analyze.c (1.150), backend/parser/gram.y (2.178), backend/parser/keywords.c (1.79), backend/parser/scan.l (1.73), backend/utils/misc/guc.c (1.7), bin/psql/mainloop.c (1.33), include/nodes/nodes.h (1.71), include/nodes/parsenodes.h (1.109), test/regress/expected/comments.out (1.3), test/regress/sql/comments.sql (1.3): Implement nested block comments in the backend and in psql. Include updates for the comment.sql regression test. Implement SET SESSION CHARACTERISTICS and SET DefaultXactIsoLevel. Implement SET SESSION CHARACTERISTICS TRANSACTION COMMIT and SET AutoCommit in the parser only. Need to add code to actually do something. Implement WITHOUT TIME ZONE type qualifier. Define SCHEMA keyword, along with stubbed-out grammar. Implement "[IN|INOUT|OUT] [varname] type" function arguments in parser only; INOUT and OUT throws an elog(ERROR). Add PATH as a type-specific token, since PATH is in SQL99 to support schema resource search and resolution. [...]
Re: [GENERAL] sql question
On Wed, Jul 12, 2000 at 05:33:09PM -0500, Travis Bauer wrote: Let's say I have a table t1 with two fields, x and y. How do I write an sql statement like: select x if y1 else 0 from t1; SELECT CASE WHEN y1 THEN x ELSE 0 END FROM t1; From page 33 of Bruce's book, at: http://www.postgresql.org/docs/aw_pgsql_book/node52.html Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] boolean isn't boolean?
On Sat, Jun 24, 2000 at 12:29:14PM -0400, Bruce Momjian wrote: [ Charset ISO-8859-1 unsupported, converting... ] Thomas Lockhart writes: Do you have a reference for an SQL99 document? Preferably on-line or in a form similar to what we've found for SQL3-1999? ftp://jerry.ece.umassd.edu/isowg3/x3h2/Standards/ansi-iso-9075-[12345]-1999.txt This seems to be the real copyrighted deal, so get yours while it's there. Seems we need an ANSI SQL napster server. :-) Hmm, the README at that site says: This is the primary server for the /isowg directory. A mirror site is available at math0.math.ecu.edu. math0.math.ecu.edu is updated twice a day. So, it looks like it's the offical repository for (at least) the US part of the iso working groups. Traditionally, those docs have been freely available until the standard is actually accepted. Perhaps these are working drafts for the next version? OR someone slipped up, and forgot to remove them when the standard was voted on? Is the voting done? Has ANSI voted? Will Lassie find Jimmy in the well? Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Q: Truncated output
On Thu, Jun 01, 2000 at 08:51:13PM +, Elliot Finley wrote: I've just started using Postgres 6.5.2 and I'm trying to figure out a way to be able to see the complete 'type' for the 'employee_id' field. I can't remember which sequence I used in the 'nextval', so I need to be able to see which one is being used there. shift= \d employee Table= employee +--+--+---+ | Field | Type| Length| +--+--+---+ | employee_id | int4 not null default nextval ( | 4 | Yeah, this is ugly. If you start up psql with the -E switch, you'll see the queries the psql uses to get the info. Something like this will get what you want: select adsrc from pg_class c, pg_attribute, pg_attrdef where adrelid=c.oid and attrelid=c.oid and attnum=adnum and relname ='employee' and attname= 'employee_id'; Oh, a hint: if you used the 'serial' type to create the id, the sequence is named tablename_fieldname_seq, unless it's to long, then it gets truncated (fieldname first, then tablename) Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL]
Uh, I cut pasted the transcript in two pieces to get the selects in the same order, and messed up. The error happens _after_ connecting as anonymous, not before. Ross On Wed, May 24, 2000 at 01:09:58PM -0500, Ross J. Reedstrom wrote: idas= select count(*) from urls; ERROR: urls: Permission denied. idas= \c - anonymous connecting as new user: anonymous idas= select count(*) from urls_p; count - 23 (1 row) idas=
Re: [GENERAL] Migrating from mysql.
On Thu, May 25, 2000 at 04:58:48AM +1000, Giles Lean wrote: On Mon, 15 May 2000 23:04:48 +0100 Joe Karthauser wrote: And last but not least I'm used to using the 'desc tablename' sql command to show the structure of a table within MySQL. How do I do the same in PostgreSQL. In psql "\i tablename". Check out \? or the documentation for all the different backslash commands. You might want \z for access permissions as well. Actually, it's "\d tablename". The rest is right, though. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] simple C function
On Fri, May 19, 2000 at 05:54:55PM +0200, [EMAIL PROTECTED] wrote:
Hi,
I'm trying to write a simple C function:
char *pg_crypt (char *pass) {
char *salt="xyz";
char *res;
res = (char *) palloc(14);
res=crypt(pass,salt);
return res;
}
you can't pass char pointers around like that for pgsql functions.
Here's my version of the above function. It includes random salt
selection if you don't supply it. (Hmm, I suppose I should put
this is contrib, eh? I did start with someone elses boilerplate,
so I'm not sure about the #define at the top.)
I compile it on linux with gcc as so:
gcc -fPIC -shared -I /usr/local/pgsql/include -L /usr/local/pgsql/lib \
-o sqlcrypt.so sqlcrypt.c
And install it like so:
CREATE FUNCTION "sqlcrypt" (text,text ) RETURNS text AS '/usr/local/lib/sqlcrypt
.so' LANGUAGE 'C';
CREATE FUNCTION "sqlcrypt" (text ) RETURNS text AS 'select sqlcrypt($1,)' LA
NGUAGE 'SQL';
Ross
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
#define _XOPEN_SOURCE
#include postgres.h
#include unistd.h
#include string.h
#include stdlib.h
#include sys/time.h
text *sqlcrypt(text *key, text *salt);
/*sql create function sqlcrypt(text,text) returns text as 'DESTLIB' language 'c'*/
char *crypt(const char *key, const char *salt);
int rand(void);
void srand(unsigned int seed);
text *sqlcrypt(text *key, text *salt)
{
text *ret;
char pass[] = "123456789";
char s[] = "...";
char salts[] = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./";
int j,k;
struct timeval tv;
s[2]=0;
bzero(pass,9);
if ((VARSIZE(salt)-VARHDRSZ) 2)
{
gettimeofday(tv,0);
srand((unsigned int)(tv.tv_usec));
j=(rand() % 64);
k=(rand() % 64);
s[0]=salts[j];
s[1]=salts[k];
}
else
{
memcpy(s,VARDATA(salt),2);
}
ret = palloc(VARHDRSZ + 13);
bzero(ret,VARHDRSZ + 13);
VARSIZE(ret) = (VARHDRSZ + 13);
if ((VARSIZE(key)-VARHDRSZ) 8)
{
memcpy(pass,VARDATA(key),VARSIZE(key)-VARHDRSZ);
}
else
{
memcpy(pass,VARDATA(key),8) ;
}
memcpy(VARDATA(ret), crypt(pass,s),13);
return ret;
}
Re: [GENERAL] BLCKSZ
On Wed, May 17, 2000 at 02:53:17PM -0400, Robert B. Easter wrote: If I set the block size from 8k to 16k by editing /include/config.h, then all tuples will take up 16k on disk? If true, it just wastes lots of disk space if you are really not going to be storing more than 8k in most tuples? Currently, more than one tuple can be stored in a block, it's just that any one tuple cannot be stored in more than one block: i.e. tuples cannot span blocks, so the BLKSZ sets the maximum tuple size. Clear? Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Performance
On Tue, May 16, 2000 at 01:41:48AM -0700, Dustin Sallings wrote: On Mon, 15 May 2000, Charles Tassell wrote: I ran into this exact problem, and it was *very* significant on a 15M row table I have. :) It didn't seem to want to use the index, even freshly created, without a vacuum analyze. Hmm, if you drop the index, do a VACUUM ANALYZE, then create the index, it doesn't want to use it? That's be odd, since the statistics are only kept about the table relations, not the indices themselves. If you mean it won't use an fresh index on a fresh table, that's the expected behavior. VACUUM ANALYZE [tablename] fills in the statistics in pg_statistic that the optimizer uses when deciding between sequential and index scans. VACUUM is currently functionally overloaded: a simple VACUUM recovers storage space in the table files, VACUUM ANALYZE does that as well as collect statistics. It sometimes feels quicker to do a simple VACUUM, then a VACUUM ANALYZE. However, vacuuming a large table with indices on it can take a _long_ time: I've seen the recommendation given to drop indices, vacuum, then recreate the indices. This is mostly a problem for the space recovery aspect of vacuum, since each updated or deleted tuple causes a update/delete to the index, as space is compacted. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] COPY fails to read source file
On Tue, May 16, 2000 at 01:57:19PM -0400, Bill Barnes wrote: Hello all: Using 6.5.3, SuSE 6.4, logged in as 'billb'. Here are 2 instances of the COPY command; - copy custprofile from '/home/billb/custpr.txt' using delimiters ';'; The result is: ERROR: COPY command, running in backend with with effective uid 26, could not open '/home/billb/custpr.txt' for reading. Errno = Permission denied (13). The file looks like -rw-r--r-- 1 billb users 58902 May 16 11:45 /home/billb/custpr.txt What do permissions on home and billb look like? I'm guessing your missing an 'x' in there (probably on billb). copy custprofile from '/mnt/DOS_C/sybase/custpr.txt' using delimiters ';'; The result is: COPY The file looks like -rwxr-xr-x 1 rootroot 58902 May 16 11.56 /mnt/DOS_C/sybase/custpr.txt --- The second instance gave me the results I wanted, but why did the first instance fail? If anything, I would have expected permission denied on the root owner. I have had the same results on other ocassions. What am I missing? That the two examples have different paths through the filesystem tree. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] OID output problems
On Tue, May 02, 2000 at 10:13:14PM -0800, surfer girl wrote: --- "Robert B. Easter" [EMAIL PROTECTED] wrote: Try recompiling php 4.0RC1 or whatever is current with the latest Apache source (1.3.12). I'm thinking maybe you are not running php as compiled into the server. Thanks - I had PHP compiled into the server - though I may try the recompile as a last resort (before the major last resort of just keeping images as files and putting the filename and location into the db). Someone asked if my INPUT was correct. Here's what I've got: pg_Exec($conn, "BEGIN"); $oid = pg_locreate($conn); $handle = pg_loopen($conn, $oid, "w"); pg_lowrite($handle, $file); Hmm, based on my reading of the php4 docs, this will right the contents of the variable 'file' to the lo, expecting it to be a null terminated string. I'm not sure how you're supposed to get binary data in there. Is 'file' by any chance, the name of your file, not the contents? Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Functions in postgres
On Thu, Apr 06, 2000 at 04:48:02PM -0500, Jeff Gerhart wrote:
Ross,
Thats for the input. Have a couple of additional questions:
1- I have the code running and it appears that I get a different result each
time I sqlcrypt the same string e.g. select sqlcrypt('xyz') gives me a
different encryption each time I execute it. Pardon my ignorance, but how
does unix validate a password i.e. compare the results of crypt'd inout
password against the previously crypt'd password.
It's selecting a random 'salt' each time, since you're not passing it
one. The first two characters of the hash are the salt, so, for example:
reedstrm= select sqlcrypt('secret');
sqlcrypt
-
PfB9b6nH6QgbA
(1 row)
reedstrm= select sqlcrypt('secret');
sqlcrypt
-
acI.WsXmTid6k
(1 row)
reedstrm= select sqlcrypt('secret','Pf');
sqlcrypt
-
PfB9b6nH6QgbA
(1 row)
So, if we pass in the salt, we get the same hash back. That's what the
example select from my original post was for: it includes the salt:
SELECT * FROM "Personnel" WHERE "PerUsername" = 'RJReedstrom' AND
"PerPassword" = sqlcrypt('secret',substr("PerPassword",1,2));
This will return the row IFF the the submitted password 'secret' is
the same as the password that was originally hashed in. Note that any
encryption strategy that relys on the backend DB to do the encryption
is on the wrong end of the network link! The password still goes clear
text to the database, and is visible in the postgresql logs, if you
log queries. But it _does_ keep me from seeing all the bad passwords
people chose actually in my tables!
2- Is there something simple process I can use to encrypt text for storage
in the database and then de-encrypt it later. I would assume I would need to
maintain the key or seed I used for encryption of the string to de-encrypt
it later.
I'm not really an encryption expert, but what your looking for is a
reversible encryption algorithm. Some suggestions of the top of my head:
SHA, Blowfish, Hmm, can't think of any more. Note my caveat above,
about what's on the wire. You _really_ want the client app to do the
encrypting/decrypting, not the backend.
Ross
P.S. I copied GENERAL on this, just so there's a record with the
original post.
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Functions in postgres
Ah, I forget to mention how to compile the code I sent. I use: gcc -fPIC -shared -I /usr/local/pgsql/include -L /usr/local/pgsql/lib -o sqlcrypt.so sqlcrypt.c then move the sqlcrypt.so file into my pgsql storage space. This is on Linux, if it matters. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Wed, Apr 05, 2000 at 12:04:49PM -0500, Victor Manuel Jaquez Leal wrote:
Re: [GENERAL] Functions in postgres
On Wed, Apr 05, 2000 at 12:04:49PM -0500, Victor Manuel Jaquez Leal wrote:
Hi!
Hi back at ya.
I know that with \df you can see the functions available in postgres, but
there must be others not documented just like getpgusername().
My question is if are there a more complete list of postgres'
functions. To be more specific I'm looking for a crypt function.
Then you're in luck. Not as much luck as if there was a built in, but
I've attached my implementation below. I stole a general boiler plate
function from someone else, and modified it to call crypt. The trickiest
part was generating random salt. I use it with these SQL statements:
CREATE FUNCTION "sqlcrypt" (text,text ) RETURNS text AS
'/usr/local/pgsql/data/sqlcrypt.so' LANGUAGE 'C';
CREATE FUNCTION "sqlcrypt" (text ) RETURNS text AS 'select
sqlcrypt($1,)' LANGUAGE 'SQL';
That way, I can say sqlcrypt('somestring') and it'll return a crypted
version of the string, with a randomly selected salt. I use it for
storing passwords for a web based login: for that, we check logins as
so:
SELECT * FROM "Personnel" WHERE "PerUsername" = 'RJReedstrom' AND
"PerPassword" = sqlcrypt('password',substr("PerPassword",1,2))
That will only return results if the password hashes match. It does expose
the cleartext of the password between the web server and postgres db:
That's not a problem for us, since they're on the same machine.
Ross
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
#define _XOPEN_SOURCE
#include postgres.h
#include unistd.h
#include string.h
#include stdlib.h
#include sys/time.h
text *sqlcrypt(text *key, text *salt);
/*sql create function sqlcrypt(text,text) returns text as 'DESTLIB' language 'c'*/
char *crypt(const char *key, const char *salt);
int rand(void);
void srand(unsigned int seed);
text *sqlcrypt(text *key, text *salt)
{
text *ret;
char pass[] = "123456789";
char s[] = "...";
char salts[] = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./";
int j,k;
struct timeval tv;
s[2]=0;
bzero(pass,9);
if ((VARSIZE(salt)-VARHDRSZ) 2)
{
gettimeofday(tv,0);
srand((unsigned int)(tv.tv_usec));
j=(rand() % 64);
k=(rand() % 64);
s[0]=salts[j];
s[1]=salts[k];
}
else
{
memcpy(s,VARDATA(salt),2);
}
ret = palloc(VARHDRSZ + 13);
bzero(ret,VARHDRSZ + 13);
VARSIZE(ret) = (VARHDRSZ + 13);
if ((VARSIZE(key)-VARHDRSZ) 8)
{
memcpy(pass,VARDATA(key),VARSIZE(key)-VARHDRSZ);
}
else
{
memcpy(pass,VARDATA(key),8) ;
}
memcpy(VARDATA(ret), crypt(pass,s),13);
return ret;
}
Re: [GENERAL] Using aggregates in a select query
On Tue, Mar 21, 2000 at 04:07:20PM -0800, Arthur M. Kang wrote: Was wondering if there was a way to use an aggregate value in a single select query to make a calculation within that query... Example: Table has column bool of type boolean with various random boolean values. Want to see if it is possible (in a single select query) to find the percentage of entries in the table that are true. (SELECT count(*) FROM table WHERE bool='t') / (SELECT count(*) FROM table) Very close: here's with current CVS sources (should be released April 1 as 7.0) reedstrm=# select count(*) from test; count --- 10 (1 row) reedstrm=# select a,count(*) from test group by a; a | count ---+--- f | 5 t | 5 (2 rows) reedstrm=# select a,count(*),(count(*)*100.00)/(select count(*) from test) reedstrm-# from test group by a; a | count | ?column? ---+---+-- f | 5 | 50 t | 5 | 50 (2 rows) You an throw a WHERE clause in the outer query, if you want to restrict which results get returned. However, this _doesn't_ work in 6.5.X (or even 7.0beta2: but that was a bug) So, in current stable, no you can't do it in one query. In the coming stable, you sure can! Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Re: alter table
On Sun, Mar 12, 2000 at 05:57:50PM +0200, Raigo Lukk wrote: Hi alter table tmp add column last text; I had this same problem, turned out that PostgreSQL don't have this feature :-( Upgrade: ALTER TABLE tablename ADD COLUMN columnname columntype Has been a feature since version 6.5.X, at least. So only way is: DROP TABLE and then again CREATE TABLE with all the fields you need. This is still needed for DROP COLUMN but not ADD COLUMN. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: I can crash my database in less than 2 minutes...
On Tue, Mar 14, 2000 at 05:05:26PM +0100, Wim Aarts wrote: I'm getting futher in focussing on the problem. I can reproduce these messages I get when vacuuming a database. And by reproduce I mean on a different system a clean installed database It takes me less then a minute NOTICE: Rel pg_class: TID 294/3: InsertTransactionInProgress 20065 - can't shrink relation NOTICE: Rel pg_class: TID 294/4: InsertTransactionInProgress 20065 - can't shrink relation NOTICE: Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (1615) IS NOT THE SAME AS HEAP' (1587) NOTICE: Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (1615) IS NOT THE SAME AS HEAP' (1587) VACUUM O.K. - something is messing with pg_class (the system table that defines the system's tables ;-) and not updating the indices. Looks like tuples are disappearing. Are you losing tables? The message below is also one of the messages I get when restarting the database after a crash. Starting postgresql service: IpcMemoryCreate: shmget failed (Identifier removed) key=5432010, size=120, permission=700 IpcMemoryIdGet: shmget failed (Identifier removed) key=5432010, size=120, permission=0 IpcMemoryAttach: shmat failed (Invalid argument) id=-2 FATAL 1: AttachSLockMemory: could not attach segment This is a side effect of the crashed server not releasing it's shared memory, and you therefore running out. Try running ipcclean to remove them before restarting the server. I get these messages by doing many revokes ( 1000) Ah, I think I see where this is coming from: REVOKE changes the tuples in system relation pg_class by means direct access methods, bypassing the syscache, then manually updates the indices. Sounds like having several hundred pending during a transaction is confusing vacuum. Someone with more knowledge about the guts of vacuum than I may be able to figure out exactly why. psql -e template1 /tmp/test-revokes where test-revokes looks like: \connect foodb begin; alter user hr270 nocreateuser; REVOKE all on foo from pn407; REVOKE all on bar from pn407; another 1000 of revokes . . REVOKE all on foo2 from pn407; end; And while doing that I perform a vacuum. vacuumdb foodb Please tell me I'm doing something I really shouldn't be doing. O.K. - you're doing something you really shouldn't be doing. ;-) No, seriously, why do you need to simultaneously vacuum? can't you vacuum after committing the transaction? Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Date problem
On Sun, Mar 12, 2000 at 09:40:43PM -0500, Alex Pilosov wrote:
now() is a function, and you should use it as now()
-alex
also, the magic constant 'now' might work: it needs the tics as well.
Ross
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
On Sun, 12 Mar 2000, edNET System Admin wrote:
Hi, I'm trying to do something with dates which is proving to be a bit
tricky.
I'm trying to get the current "date" and add 3 days to this.
I've tried:
$date = "(now::date) + ('3 days'::interval)";
DBD::Pg::st execute failed: ERROR: parser: parse error at or near "3"
... and :
$date = "(now::date) + (\\'3 days\\'::timespan)";
DBD::Pg::st execute failed: ERROR: Bad date external representation
'(now::date) + ('3 days'::timespan)'
Needless to say I'm using this date creation string as a parameter of a
DBD::Pg CGI query, hence further complications with apostrophes.
I've found nothing concrete in the documentation about how to do this, and
the closest thing to an answer came from this newsgroup. No luck so far
tho'
does anyone know how to do this and possibly and source of good
documentation on this type of thing.
Regards
Scott McDaid
edNET
t: +44 131 625 5557 (direct dial)
t: +44 131 466 7003 (office)
Re: timestamp ?(RE: [GENERAL] scheduling table design)
On Fri, Feb 25, 2000 at 06:25:12PM -0600, [EMAIL PROTECTED] wrote: oops, it's "timestamp" now (just name change). BTW, I remember datetime is in sql92. "timestamp" is also in sql92? why "timestamp" is better than "datetime" ? sql99(96) ? Nope, DATETIME is not an SQL92 type, it's a class of types. Here's a snip from the standard: datetime type ::= DATE | TIME [ left paren time precision right paren ] [ WITH TIME ZONE ] | TIMESTAMP [ left paren timestamp precision right paren ] [ WITH TIME ZONE ] So the three SQL92 datetime types are DATE, TIME, and TIMESTAMP. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] using ID as a key
On Mon, Feb 07, 2000 at 01:37:21PM -0600, Ed Loehr wrote:
[EMAIL PROTECTED] wrote:
and, it seems not "programmatically at all.
What would make it "programmatic" in my view would be calling a
function, as in "$newID = GetNewID()", prior to INSERT and then using
the returned ID value in your INSERT, rather than using a 'default
nextval' to get the value. I don't see why one wouldn't want to use a
sequence object within GetNewID(), FWIW. But sounds like it is not
the kind of programmatic example/explanation you were looking for...
In fact, that's exactly how a number of core psql developers recommend
handling the problem of how to get the new value just assigned by
a default nextval() clause: don't use the default, do:
$newID = SELECT nextval('my_ID_seq')
INSERT INTO my_table (my_id,somethng,otherthng) VALUES ($newID, $some, $other)
or equivalent, so you've already got the ID in hand.
Personally, I use the SELECT curval('seq_name') construct.
Ross
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] How to handle non-postgres questions?
On Fri, Jan 28, 2000 at 01:41:52PM -0600, [EMAIL PROTECTED] wrote: Occasionally a question is posted to this list that is not a question about postgres, but to which I know the answer. When this happens, I usually reply only to the person who posted the question, rather than to the whole list. I do this to minimize non-postgres traffic on the list. However, this has the disadvantage of making it seem that questions that are posted to the list are ignored. Is responding only to the sender the correct thing to do, or should I include the whole list? I'd say, if it's a sort answer (like this one ;-), post it as well. If it's long and detailed, go ahead and send email, but if you want, post a brief reply to the list, usually just quoting the question, and saying "Answered in private email" Ross P.S. Watch your line lengths! -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] server hardware recommendations (the archives aredead)
On Wed, Dec 15, 1999 at 11:27:36AM -0400, The Hermit Hacker wrote: On Wed, 15 Dec 1999, Jeff Hoffmann wrote: my preference tends to be software raid...whatever I've ever seen as far as hardware raid is concerned has been quite slower then software raid...and this is with high-end servers... i kind of question this, and here's why: i just set up a linux dual P3/256MB with 4 software raid 5 volumes and even loading data into one of the databases slows it to a crawl. i've been looking around because snip Most of my RAID tests are on Solaris+Disksuite...with good drives in the machine, my writes are something like 18MB/s to the drive, stripe'd and mirrored...I think reads worked out to be 19MB/s...(bad drives, same Ah, this would be a RAID 0+1 setup, then? Very different from Jeff's RAID 5 configuration. I'd be willing to believe that software RAID 0+1 _could_ be faster than most hardware (it's just shuffling and dupping blocks around to different drives, which could be done with clever pointer twiddling) but calculating parity bits in hardware for RAID 5 had got to be a win, doesn't it? As it turns out, I'm speccing a similar machine right now, myself, and I've been running into statements like yours re: software RAID that surprised me. setup, same machine, same OS, were net'ng me something like 3MB/s...really killed performance *grin*) Hmm, bad drives as in broken, or slow? Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] How to stop implicit rollback on certain errors?
Hmm, sounds like a vote for nested transactions. The JDBC driver developer (Peter Mount) was musing that nested transaction would make large object support easier for him, as well. As to the other example of Oracle not forcing a rollback, I have a feeling that this may be specific to syntax errors in an interactive session. Implementing this sort of behavior has been discussed recently on the hackers list, in the context of making it easier to work interactively inside a transaction. I would be surprised if Oracle allows non-syntax errors inside a transaction to be ignored, or ignores anything in a non-interactive session. How about testing an example like links, where you provide data in a format the backend can't handle, (an out of range int or date or something) and see how Oracle handles that. Who's right? Well, as Peter Eisentraut said, what Postgres implements is the _definition_ of a transaction: all together, or nothing at all. This isn't just an arbitrary rule: the validity of the relational calculus depends on transactional semantics. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Thu, Dec 09, 1999 at 09:23:35AM +0800, Lincoln Yeoh wrote: At 01:18 AM 09-12-1999 +0100, Peter Eisentraut wrote: Seriously, why do you use a transaction, when you don't want any errors caught? Transactions are defined as everything succeeds or nothing goes. If you want update to succeed anyhow, put it in it's own transaction (i.e., commit before it). I want errors caught, most errors abort everything but some errors I want to try a different update instead, if that doesn't work then only rollback everything. I guess that's expected, and I should insert big years using another less ambiguous format. What is the recommended format? The safest way would be to set a date format with SET DATESTYLE TO and use that, possibly assisted by library formatting routines. OK. Link.
Re: [GENERAL] QUERY PLAN:
On Wed, Oct 27, 1999 at 11:03:45AM +0100, Tim Joyce wrote: can someone point me at documentation so that i can unserstand the results of an EXPLAIN, eg: Index Scan using words_id_idx on books_idx (cost=441.19 rows=7644 width=8) explain select * from books_idx where wrd_id=1; I am paticularly interested in what the rows= figure means. I've picked up a little info on this from following the hackers list, so I don't know what docs to point you at. My understanding is that the rows= represents the number of tuples the optimizer estimates will be returned by that step of the execution plan. In the example you've shown, it's an index scan of a field, and the estimate (based on last know number of tuples in the table (as of your last VACUUM ANALYZE) and an estimate of the selectivity of the operator being applied to this index (, =, , etc), and the approximate dispersion of the values in that field. For more detail, I'd suggest checking the archives of the pgsql-hackers list, and perhaps then asking on the list itself. There's some hints in the "PostgreSQL Programmer's Guide" in the "Extending SQL" sections on functions, operators, and interfacing them to indices. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] How to import data from MDB or pipe delimited file into PostgreSQL
Mike (and Doran) - Ah, that's the keyword I needed: grepping for 'upsiz' in my personal postgresql archives finds s tool called 'pgupt' at: http://dspace.dial.pipex.com/boylesa/pgupt/pgupt.shtml I haven't used it, but it seems to be targeted at exactly this task. Ross On Fri, Oct 01, 1999 at 10:15:23PM -0700, Mike Mascari wrote: --- "Doran L. Barton" [EMAIL PROTECTED] wrote: looking for help with MS-access - PostgreSQL migration Another way is to use a PostgreSQL upsizing tool. I have seen a post on this list several months ago regarding this tool, which looks promising. The search engine for the mailing list archives at www.postgresql.org appeared broken as of a couple of days ago and I don't have the URL to the page, but an upsizing tool does, indeed, exist. -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] encrypted field
On Fri, Sep 17, 1999 at 09:03:58AM -0300, Henrique Pantarotto wrote:
snipped Henrique's crypt function
PS: Note that all crypted passwords are created with salt "HP" (my name
initials..) You can change that, or if you know C, you can do in a way that it
will pick two random characters (the way it should really be).
I've got a similar function, just a generation later. Note that I don't
remember what trigger code I used the framework from. Apparently, (from
looking at Henrique's code) there are some text convenience functions
I don't know about: I did all the memory allocation explictly (i.e. the
hard way). I also ran into a 'gotcha': crypt expects zero terminated
strings, pg text type is a counted string. Took me too long to find the
problem, since from pgsql, I seemed to get a new (zeroed) buffer, most of
the time. So there might be lots of extra bzero()s and memcpy()s in the
following. If anyone has any suggestions for improvments, I'm all ears!
I compiled it as so:
cc -shared -I /usr/include/postgresql/ -o sqlcrypt.so sqlcrypt.c
And created the functions as described in the comments in the file.
This gives you two functions, sqlcrypt(text) and sqlcrypt(text,text)
The first form will pick a random salt, the second uses a given salt. I
use them from some web-based middleware, which has no crypt() function
(ColdFusion), as so:
with a table:
logins (userid serial, password char(13), username text)
SELECT userid FROM logins WHERE
username= '#name_entered#' and
password=sqlcrypt('#pass_entered#',substr(password,1,2))
--8
/* sqlcrypt functions: wrapper around standard unix crypt call.
* Copyright 1999, Ross J. Reedstrom ([EMAIL PROTECTED])
* I hereby place this code under the same copyright restrictions as
* PostgreSQL.
*/
#define _XOPEN_SOURCE
#include postgres.h
#include unistd.h
#include string.h
#include stdlib.h
#include sys/time.h
text *sqlcrypt(text *key, text *salt);
/*
* Create functions:
*
* sql create function sqlcrypt(text,text) returns text
* as 'DESTLIB' language 'c'*/
* sql create function sqlcrypt(text) returns text
* as 'select sqlcrypt($1,)' language 'SQL'
*
*/
char *crypt(const char *key, const char *salt);
int rand(void);
void srand(unsigned int seed);
text *sqlcrypt(text *key, text *salt)
{
text *ret;
char pass[] = "123456789";
char s[] = "...";
char salts[] = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./";
/* as per crypt(3): [a-zA-Z0-9./] */
int j,k;
struct timeval tv;
s[2]=0;
bzero(pass,9);
/* test for not-valid salt: if not, pick randomly. I'm only testing
size, should also make sure the first two characters are in the valid
set. Anyone have a better way to get a pseudo random number? I brought
in gettimeofday to seed rand*/
if ((VARSIZE(salt)-VARHDRSZ) 2)
{
gettimeofday(tv,0);
srand((unsigned int)(tv.tv_usec));
s[0]=salts[(rand() % 64)];
s[1]=salts[(rand() % 64)];
}
else
{
memcpy(s,VARDATA(salt),2);
}
ret = palloc(VARHDRSZ + 13);
bzero(ret,VARHDRSZ + 13);
VARSIZE(ret) = (VARHDRSZ + 13);
/* don't copy any garbage from the input, but only get the first eight */
if ((VARSIZE(key)-VARHDRSZ) 8)
{
memcpy(pass,VARDATA(key),VARSIZE(key)-VARHDRSZ);
}
else
{
memcpy(pass,VARDATA(key),8) ;
}
memcpy(VARDATA(ret), crypt(pass,s),13);
return ret;
}
--8
I'm no experience C programmer, nor an experienced PostgreSQL user, so maybe
there's a smarter way to do this same thing.. (there might be even a built in
function that I don't know).
Ditto for me: again, anyone have any improvements, let me know, my users will
thank you, if only they knew...
Ross
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] RV: Serial fields
On Fri, Aug 20, 1999 at 12:52:20PM +0200, Pablo Sentis wrote:
-Mensaje original-
De: Pablo Sentis [EMAIL PROTECTED]
Para: PostgreSQL mailing list pgsql-general@postgreSQL
Fecha: viernes, 20 de agosto de 1999 11:59
Asunto: Serial fields
Hi All!
Working with PostgreSQL 6.5.1 on Intel platf.
I´m trying to migrate an Acces database to postgress . First of all I´ve created the
database structure in the Postgres machine with SQL table creation statements and
this works properly . But the problem comes when I try to transfer the data via a
flat file : If I try to do (from a Windows program) the data transfer when the table
has a serial field , even though the original data is written in the postgres table
all subsequent INSERTS from psql get a 'Duplicate index' error . In the exported
flat file included the original serial values :
MDB TEXT FILEPOSTG table
===
1, NAME1 1, NAME1 1, NAME1
2, NAME2 2, NAME2 2, NAME2
3, NAME3 3, NAME3 3, NAME3
5, NAME5 5, NAME5 5, NAME5
After this if I try an INSERT : INSERT INTO table (NAME) values ('NAME6') I get
the error
Of course I know I should not write on a read-only field so as
I need to import the original serial values as they are referrenced in other tables
in the database
Ah, I think here lies the answer to solving your confusion. Serial
fields in PostgreSQL are different than 'automatic' fields in MS-Access:
they're _not_ readonly. Instead, they're just and int4 field with a
special default value that comes from a sequence.
For example, lets say you do this:
CREATE TABLE mynames ( nameid serial, name text);
insert into mynames (name) values ('Fred');
insert into mynames (name) values ('Angela');
if you dump the database with this table you'll see:
CREATE SEQUENCE "mynames_nameid_seq" start 2 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;
SELECT nextval ('mynames_nameid_seq');
These two lines create the sequence associated with your serial field,
and set it's start value to the maximum value already in use.
CREATE TABLE "mynames" (
"nameid" int4 DEFAULT nextval('mynames_nameid_seq') NOT NULL,
"name" text);
This creates the table.
COPY "mynames" FROM stdin;
1 Fred
2 Angela
\.
Note that the COPY reads in the serial values as well.
CREATE UNIQUE INDEX "mynames_nameid_key" on "mynames" using btree (
"nameid" "int4_ops" );
And this index makes sure you don't reuse a serial value.
When you _do_ insert and set a serial field to a particular value, it's good
to reset the sequence, to make sure you don't get errors, like so:
select setval('mynames_nameid_seq',max(nameid)) from mynames;
I need to do this sort of thing when I recreate a sequence, or sometimes
after deleting a lot of test records, I'll do it to not have big gaps
in my serials.
Hope this helps,
Ross
Regards from a sunny and almost boiling
Alicante , Spain
Ah, got you beat there, Houston _is_ boiling!
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: Fw: [GENERAL] uppercase of char16
Hmm, char16 is not a currently recognized type - what version of postgresql are you using? In 6.5, upper(foo) works for all the char and text types of foo I can find. Ross On Tue, Aug 10, 1999 at 02:30:54PM +0300, Safa Pilavcý wrote: Please help - Original Message - From: Safa Pilavc? [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 06 A?ustos 1999 Cuma 14:48 Subject: [GENERAL] uppercase of char16 hello , My problem is with SQL statement, I have a field like, my_fieldchar16 this field contains charecters all lowercase but I want to get them uppercase as a result of select statement.. I have tried ; select upper(my_field) from my_table; but the followwing error occured. function upper(char16) does not exist How can I get uppercase output of my_field Safa Pilavc? -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] group, by, problem, when, combined, with, insert, into
On Thu, Aug 05, 1999 at 01:19:10PM -0400, Brett W. McCoy wrote: On Wed, 4 Aug 1999, sam smith wrote: select loser,count(*) from moves group by loser; but when i combine it with an insert into - insert into losses select loser,count(*) from moves group by loser; I get ERROR: Illegal use of aggregates or non-group column in target list I think you want 'select loser, count(*) into losses from moves group by loser' This may in fact be the work around, but Sam probably has a preexisting table called losses, which the SELECT INTO syntax wont allow. I think this bug has been brought up recently, I'll check the TODO. Ah here it is, or at least, a couple of related entries: -INSERT ... SELECT ... GROUP BY groups by target columns not source columns redesign INSERT ... SELECT to have two levels of target list So Sam, I think it's a bug. The work around is to what Brett suggests, SELECT INTO temp_losses GROUP BY ..., then INSERT INTO losses SELECT * from temp_losses; Ross -- J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] pg-dump -- primary Key
On Sun, Jul 25, 1999 at 03:13:39PM +1000, Chris Bitmead wrote: Dan Wilson wrote: Yes, I am aware that the primary key does not really mean anything except implicitly making it a unique key, but it's supposed to be there for compatibility and it's not even in the dump. Someone mentioned recently that primary key enforces nulls as unique whereas unique index doesn't. Actually, I belive it enforces NOT NULL on primary keys, which it also dumps in the pg_dump output. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] JOIN exclusion problem
On Thu, Jul 01, 1999 at 03:43:27PM +0200, Anja Speerforck wrote: At 11:24 01.07.99 +0100, you wrote: I'm not sure that I understand exactly what you are trying to do. I'm guessing (and I mean guessing) that the tables are something like: ansprechpartner: private owner kunden: client agenturen: estate agent The tables represent: ansprechpartner = contact person kunden = clients agenturen = agencies What I'm trying to do is get a list of all contact people, whether they're associated with a specific client or a specific agency, so that the name of the client or agency shows up in the results. --- Ah, now it's clear. You're looking for the UNION operator, and two selects. Something like: SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, ap.email, ap.kunden_nr, k.name1 FROM ansprechpartner ap, kunden k WHERE ap.kunden_nr = k.kunden_nr UNION SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, ap.email, ap.agentur_nr, ag.name1 FROM ansprechpartner ap, agenturen ag WHERE ap.agentur_nr = ag.agentur_nr ORDER BY nachname Hope this helps, Ross
Re: [GENERAL] Full Text Searches
On Mon, 24 May 1999, Bruce Momjian wrote: What's it called? I only see some tcl frontend stuff. Despite my pessimism form the prior message, I am interested in a full text retrieval engine. It is called contrib/fulltextindex. Does someone want to suggest a better name? I didn't see it on the ftp site. I only saw pgv and tcldb in the contrib directory. Ah, here's the problem. Bruce means the contrib directory in the source distribution, which is at the top level, right beside src (were the core of postgresql lives). It's pgsql/contrib, if you do a CVS checkout. I'm not sure where it ends up in various binary packages. (/usr/lib/postgresql/contrib on my Debian Linux install, for example, has parts of it, but not the whole thing.) Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] select from into question
SQL has a few, limited, string manipulation functions. One of this is 'strpos' with return the position of a sub-string within the string, and another is 'substr' which return a substring based on positions. You'd think that these would make it easy, but there doesn't seem to be a way to get the _last_ occurance of a string. So, unless you know more about the format of this string than is given in the example (i.e., is it always the same number of catagories? Is the number always the same length?) it's not possible, within SQL. I assume you're just doing this one-off, for data importing or something? The following assumes three levels of categories, like in the example: test= select * from t; long|short +- categoryname/subcategoryname/someotherinformation/012345| (1 row) test= select long from t; long categoryname/subcategoryname/someotherinformation/012345 (1 row) test= select substr(long,strpos(long,'/')+1) from t; substr --- subcategoryname/someotherinformation/012345 (1 row) test= select substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1) from t; substr --- someotherinformation/012345 (1 row) test= select substr(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),strpos(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),'/')+1) from t; substr -- 012345 (1 row) update t set short=substr(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),strpos(substr(substr(long,strpos(long,'/')+1),strpos(substr(long,strpos(long,'/')+1),'/')+1),'/')+1); UPDATE 1 test= select * from t; long| short +-- categoryname/subcategoryname/someotherinformation/012345|012345 (1 row) test= select short from t; short -- 012345 (1 row) Kevin Heflin wrote: Just hoping some magic SQL can get me out of this one easily enough. I have a field of type varchar a sample would look something like this: 'categoryname/subcategoryname/someotherinformation/012345' all I want in this field is that last bit of information after the last '/' ie: '012345' I'd like to either replace this field with this number alone or insert into another newly created field. Any suggestions would be appreciated. - Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Problems with '||' concatenation operator.
Stuart -
I think this is a consequence of the internal representations of text
and varchar and char being identical, so that the cast finctions think
there's nothing to do.
I think this is fixed in 6.5. A work around for 6.4 is to apply
afunction that does nothing to the text: I've used btrim() in the past
(since I usually want to get rid of trailing whitespace anyway:
test= select btrim(chromosome) || btrim(arm) as locus from experiment;
locus
-
22q
17p
(2 rows)
Ross
Stuart Rison wrote:
Dear All,
I'm trying to get the concatenation operator (||) to work with different
character variables (i.e. varchar and bpchar)... and failing.
consider the following:
create table experiment (
chromosome varchar(2), // that for chromosomes 1-22 and X and Y
arm char(1) // can only be one of 'q' or 'p'
);
insert into experiment values ('22','q');
insert into experiment values ('17','p');
select * from experiment;
chromosome|arm
--+---
22|q
17|p
(2 rows)
I want to select a field as the concatenation of the chromosome and the arm...
cgh= select chromosome || arm as locus from experiment;
ERROR: There is more than one possible operator '||' for types 'varchar'
and 'b
pchar'
You will have to retype this query using an explicit cast
cgh=
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] How to get seq after insert
Hmm, Ross needs to break for lunch - his brain is runing out of glucose! Ross J. Reedstrom wrote: No, as Herouth pointed out, currval is multiuser-safe: it returns the last value given in the current session, and every user get's their own gets session. I just tried it out in two psql sessions to a test sequence - no matter how many calls to nextval I do in one window, the currval in each gives the write answer. right or correct Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Would this project be of interest to anyone?
James Thompson wrote: I've been working on a new project using Postgresql and wanted a little public input on it. OBE project You may want to take a look at Zope (www.zope.org) It's a Python based, GPLed web/object infrastructure thing. Think of it as a cross between PHP/ColdFusion and a persistent CGI/ASP application server. It plays well with Postgresql in my hands (so far) and has an active community (and company) behind it (Digital Creations: www.digicool.com). As to using PostgreSQL in a general office automation environment, the biggest snag I think you'll hit is the 8K limit on tuples, and the insufficiency of large objects (lo) that's seen so many postings to the lists lately. Although, apparently, the win32 ODBC driver handles lo's automatically reasonably well (I remember someone mentioning it being cool to drag-n-drop big old Word docs and animations into their PostgreSQL db via MS-Access) Perhaps for the limited case (not millions of records above 8k, just a few), the existing implementation is sufficent. Ross (parenthetically speaking this morning, for some reason) -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Are postgreSQL views UPDATEable?
Stuart Rison wrote: Hello, Found this snippet in a postgreSQL GENERAL posting: Ross J. Reedstrom [EMAIL PROTECTED] wrote [PostGreSQL does] support views, has for quite a while. They're even updateable. ^^^ What exactly does this mean? Does you can INSERT into a view with tuples being inserted into the base table(s) accordingly? Yes, but it's not automatic. To quote Jan Wieck from a mail on the HACKERS list: JW When will updateable views be supported? JW JW Since v6.4 :-) JW JW Look at the rule system documentation in the programmers JW manual for details. http://www.postgresql.org/docs/programmer/rules631.htm Stuart Does it mean you can UPDATE values in the views and values in the base table(s) will be accordingly modified? The problem is with the 'accordingly': since a view can be any select statement, the reverse mapping is not easily computed, automatically. So, you must define them yourself. Essentially, VIEWs in PostgreSQL are just tables with a ON SELECT rule that does a INSTEAD SELECT. With v6.4, the RULE system was expanded to allow for ON INSERT, UPDATE or DELETE, so you may write rules to handle updating VIEWs. The docs mentioned above are actually very good at describing all this. Ross -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
[GENERAL] Re: [SQL] sql 92 support in postgres
Eric - I've redirected your question to the general list, since it seems to fit in there better than the sql list. I think you're confusing mySQL's limitations with those of PostgreSQL (PG from here on) - PG does in fact support views, has for quite a while. They're even updateable. With each release, more and more of the SQL92 function set is implemented, and what's not can usually be worked around. I'm new to all this DB stuff, myself, but I haven't bumped up against limitations of the implementation yet - I hit the limits of my knowledge first! As to mySQL, I've never used it, but I understand it's a very fast, but limited, subset of SQL. The biggest drawback I see referenced is the lack of transaction support. My 2 cents, Ross Eric Enockson wrote: hi, I am going to be implementing an online database and was considering oracle on an ultra, until i checked the price and 2,000 dollars, no way. I have used mSQL and looked at mySQL and now am aware of postgres and have heard that it is the most robust and well used of the freeware databases. Is this correct? Also i am wondering about it's sql 92 support, i see that it doesn't have views and some other things, but my question is this. For those of you who are using it, what is your opinion of it's functionality sql wise? Is it sufficient, do wish that you had more? If you could afford it would you rather be using oracle? I can afford oracle as i'm not going to be paying, but 2000 just seems unresonable. I don't want to pay for suits and corporate planes, i just want to run software. Any help, comments, advice would be greatly appreciated. Eric Enockson -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] Comments on tables, functions, etc.
Michael - I was just wondering this myself! I even dug into the source code, to see if I could find how the comments get in there. Couldn't find any existing SQL or psql command syntax, so I'm doing this: template1= \d pg_description Table= pg_description +--+--+---+ | Field | Type| Length| +--+--+---+ | objoid | oid | 4 | | description | text | var | +--+--+---+ Index:pg_description_objoid_index template1= select oid from pg_class where relname = 'fred'; oid - 66464 (1 row) template1= insert into pg_description values (66464,'This is the table fred') ; INSERT 66473 1 template1= \dd fred description -- This is the table fred (1 row) template1= Michael Davis wrote: How can I add a comment to a table, function, etc. that will should up in a /dd comment in psql? -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] need help separating a field
Well, I once split up a bunch of multiline addresses with lines like
this:
select "PerIndex",substr("addr",1,(strpos("addr",'\n')- 1)) as addr,
substr("addr",(str
pos("addr",'\n')+1)) as addr2 into tmp2_addr from tmp_addr;
so, try something like (untested):
select substr(username_password,1,(strpos(username_password,'/')-1)) as
username, substr(username_password,(strpos(username_password,'/')+1)) as
password into new_table from table;
Kevin Heflin wrote:
I have a table with a field of 'username_password' where the data is in
the format of:
username/password
what I would really like to do, is create a new field of 'username' and
another of 'password' and with some magic SQL statement select all from
username_password, and spit up the pair, and insert them back into the
newly created 'username' and 'password' fields.
Is this even possible? and if so, would anyone be willing to give me a
hint?
Kevin
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #619| FAX:318.221.6612
[EMAIL PROTECTED]| Shreveport, LA 71101 | http://www.shreve.net
--------
--
Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED]
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] indexed regex select optimisation missing?
Ah, your description just tripped a memory for me from the hackers list: The behavior you describe has to do with the implementation of using an index for regex matching, in the presence of the USE_LOCALE configuration option. Internally, the condition: WHERE word~'^alongword' is converted in the parser(!) to: WHERE word = 'alongword' AND word 'alongword\377' since the index needs inequalities to be used, not matches. Now, the problem is the hack of tacking an octal \377 on the string to create the lexagraphically 'just bigger' value assumes ASCI sort order. If USE_LOCALE is defined, this is dropped, since we don't have a good fix yet, and slow correct behavior is better than fast, incorrect behavior. So, you have two options: if you don't need locale support, recompile without it. Otherwise, hand code your anchored matches as the pair of conditionals above Hmm, is there syntax for adding an arbitrary value to a string constant in the SQL? I suppose you could use: word 'alongwore', i.e. hand increment the last character, so it's larger than any match. Your point is correct, the developers are aware of it as a theoretical problem, at least. Always helps to hear a real world case, though. I believe it's on the TODO list as is, otherwise, pester Bruce. ;-) Reviewing my email logs from June, most of the work on this has to do with people who needs locales, and potentially multibyte character sets. Tom Lane is of the opinion that this particular optimization needs to be moved out of the parser, and deeper into the planner or optimizer/rewriter, so a good fix may be some ways out. Ross On Fri, Nov 05, 1999 at 10:12:06AM +1300, Stuart Woolford wrote: My point is that, while the index (in 6.5.1 and 6.5.2, anyway) is used to locate the start of the scan, the system is then index-scanning the *whole* rest of the table (which takes minutes for my 1.6 million entry table if it is from near the start), as opposed to using a better 'stop term' to stop scanning once the regex will no longer be able to match (ie: the static front of the regex is no longer matching), so the ordered scan is only being half utilised, this makes a MASSIVE difference in performance. For example, say one of the words in the table is 'alongword', and there is also 'alongwords', but no other words with the root of 'alongword' [...] If I do a 'select key from inv_word_i where word~'^alongword' it uses the index to find 'alongword', then does an index scan of the *whole* rest of the table check all the rest of the entries for regex matching, so it takes a long time, and returns the two entries detailed above, it will take almost as long as the previous query. What it should do is stop as soon as the leftmost part of the regex match no longer matches 'alongword' because, as it is scanning in indexed order, a match is no longer possible. The query will then run at nearly the speed of the first -- Ross J. Reedstrom, Ph.D., [EMAIL PROTECTED] NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
