Techdocs error (WAS: RE: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4)
Hmm, not good - I've forwarded this to the techdocs author. Regards, Dave -Original Message- From: Jason McManus [EMAIL PROTECTED] To: Dave Page dpage@vale-housing.co.uk Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: 02/07/06 05:00 Subject: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4 Hi Dave, Documentation such as this can be added to the new techdocs area on the main site at http://www.postgresql.org/docs/techdocs under the relevant section (probably http://www.postgresql.org/docs/techdocs.3 in this case). Please note that the editting interface is still new and may still have a quirk or two... Great, thank you for the pointer to the page. However, I think I have been bitten by one of the two quirks ;) I managed to get through editing and formatting of the document, but upon submission, it fails to accept the document, stating that several properties are invalid (errors at the bottom of this message). I did not add any custom formatting, and only used the features and controls available within the editing interface.. So, it seems at this time, that I'll have to wait to post this until the form is fixed, or possibly submit it in some other fashion.. Thank you to everyone else who offered corrections, also! I knew there would be a few, and I will incorporate them into the revision before final submission. Cheers, -Jason error output upon choosing 'Save' from the techdocs editor: --- Element H2: Invalid attribute STYLE Element U: Invalid element Element U: Invalid element Element DIV: Invalid attribute STYLE Element DIV: Invalid attribute STYLE Element DIV: Invalid attribute STYLE Element U: Invalid element Element LI: Invalid attribute STYLE Element U: Invalid element Element U: Invalid element Element U: Invalid element Element U: Invalid element Element U: Invalid element ---(end of broadcast)--- TIP 1: 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] How to Backup like in mysql or ms sql server
Uwe C. Schroeder wrote: You can technically just copy paste the postgresql data directory IF YOU SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the same version of postgresql. Also: this is not a good way to do it and I'd encourage you not to use this as general means of backup (it's ok if you want to create a quick clone of an existing database on a second machine - provided that the platform and postgresql version on there is identical to the source). Well, err. thats not completely true with current postgres versions: http://www.postgresql.org/docs/current/static/backup-online.html ... Is there any way to back-up database like mysql or sql server we just copy and paste. Or maybe there is any tools to copy database when the service is shutdown. Where is postgresql put teh database files? Well, thats in the docs ;) (or see above) btw, just copy and paste w/o preparation is dangerous with the above databases too. Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] different sort order in windows and linux version
On Sat, Jul 01, 2006 at 06:23:07PM -0400, Tom Lane wrote: Tomi NA [EMAIL PROTECTED] writes: Basically, it comes down to three possibilities, doesn't it: 1.) use an existing library 2.) write a pgsql specific implementation 3.) forget about it and tend to other issues Personally, I don't really care if it's 1) or 2): I'm just afraid it's going to be 3). Is this a licencing issue (with regard to ICU beeing under the IBM public licence)? Licensing is a concern --- IBM's appears to be not quite BSD enough. Size and portability of the library are concerns. Performance is a concern. Whether the patch makes the library required or optional is a concern (if required, the portability issue becomes a whole lot more urgent). Loss of existing functionality is a concern --- for instance, if the patch is such that UTF8 becomes the only supported server encoding, it'll probably be rejected forthwith. Licence - It's the X/MIT licence, which is almost identical to the BSD licence. http://dev.icu-project.org/cgi-bin/viewcvs.cgi/*checkout*/icu/license.html http://en.wikipedia.org/wiki/MIT_License But I don't think anyone is actually considering importing ICU into the postgres source tree, are they? Size - I'm not sure this is relevent since I don't think we want to incorporate it into postgres itself, just let people use it if they have it. In any case though, the default dataset is 8MB. This includes support for every locale and charset it knows about. If you drop the conversion stuff (because postgres already has that) you're down to about 4MB. Since ICU supports userdefined tables, we could provide a single cross-platform dataset and get the user's ICU library implementation to use that. Portability - ICU runs on all the platforms postgres does, AFAICS. http://dev.icu-project.org/cgi-bin/viewcvs.cgi/icu/readme.html?rev=release-3-4#HowToBuildSupported Performance - ICU is approximatly four times faster than glibc for collation. Even once you include keygen time (including conversion) it comes out about 40% faster. http://icu.sourceforge.net/charts/collation_icu4c_glibc.html ICU is not slow. Well, the Japanese think that UTF8 is not the solution to all their worries, so they won't be happy with a UTF8-only solution. Likewise, those of us who only need single-byte character sets won't be very happy with being forced to accept multi-byte processing overhead. I've not quite understood the japenese problem with Unicode. My understanding is that it was primarily due to widespread use of broken converters. In any case, ICU appears to beat glibc with single byte encodings, even including the multi-byte conversion. However, the most important point is that people have said they'll take the speed hit if they could get consistant collation. For speed you can always throw more hardware. But no amount of hardware will fix your collation issues. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] different sort order in windows and linux version
Tomi NA wrote: On 6/30/06, Martijn van Oosterhout kleptog@svana.org wrote: On Fri, Jun 30, 2006 at 11:56:19AM +0200, Dragan Matic wrote: I have two postgres servers, one on linux (fedora core 5), one on windows, both are version 8.1.4. Not beeing able to depend on the engine to consistently collate strings as simple as the ones Dragan listed is closer to a serious bug (non-deterministic behaviour in otherwise deterministic functions) than a RFE, but is certainly nowhere near it's not our problem as it regularly seems made up to be. The OS(es) simply and obviously do(es)n't do a good enough job of it. I was about to say the same thing. I think that the whole point in having a portable database system is that the data inside the database should behave the _same way_ no matter what operating system database is running on - client shouldn't be aware of the server OS. This is clearly not the case here. Furthermore, the same thing happens even with en_US (on Linux) and English_United States (on windows) collations selected, so it is definitely a serious issue with US collation also and not with some exotic collation orders only. I think that the only case where it doesn't happen is when C collation is selected. It might be interesting to see how this issue behaves on other operating systems. In the past there have existed patches to allow postgres to use ICU for locale support. It's supposedly not quite as fast, but you will be able get consistant results across platforms. Personally, I'd be perfectly happy with pgsql if I could choose to make text operations up to 2-3x slower without the fuss of how it's going to work on a certain platform, in each pgsql version. Furthermore, compiling the server myself is not an option for live usage: on my current project, I'm not even the one installing the database servers...sending administrators a binary I configured and compiled (on Windows, in this case!) and noone but me tested...b...I get the shivers just thinking about it. Recompiling is not an option for me also, I mean I could do it for an in-house servers where I am in charge, but our application runs on many places and on many servers where recompiling postgres with some third-party patches is out of the question. I think the solution where postgres would be slower but behaved the same way on all supported operating systems would also be acceptable for most people. Dragan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] different sort order in windows and linux version
On Sun, Jul 02, 2006 at 12:13:02PM +0200, Martijn van Oosterhout wrote: However, the most important point is that people have said they'll take the speed hit if they could get consistant collation. I can second that. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pgsql user change to postgres
Hi, I started using PostgreSQL (8.0) about a year ago on Windows. Following the installation instructions, I created a 'postgres' user (BTW, this was based on the Short Version instructions for UNIX, i.e., adduser postgres --I'm not sure it this is made explicit elsewhere, even for UNIX). I'm now migrating to FreeBSD and was surprised to find that the port used 'pgsql' as the user. The maintainer said that was done to ensure backward compatibility because that *was* the original name. Since I didn't need to be backward compatible (and my Windows dbs already used 'postgres'), I tried to bypass that (sort of) requirement by renaming 'pgsql' to 'postgres' (in the passwd file) and changing the postgresql_user variable used in the rc startup file. That was OK until I tried to build 8.1.4_1. I figured out how to tweak the build files to stick with 'postgres' but then I realized I'd have to patch them every time I'd fetch a new build, so I went back to 'pgsql'. I'm curious about a few things. How long ago was the 'pgsql' to 'postgres' change (and maybe it would be helpful to know the rationale for the backward incompatible decision--I tried searching in the archives but 'pgsql' and 'postgres' are all too common)? Is there any problem with using 'pgsql' vs. 'postgres' (and are there any plans to deprecate or disallow the former at some point)? Are other UNIX/Linux ports in the same boat, or does any Linux port offer users a choice in this matter? Would any change to the build/install procs have to be done through the current port maintainer or are they somewhere in the PostgreSQL source tree (and subject to standard submission/review procedures)? Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgsql user change to postgres
On 7/2/06, Joe [EMAIL PROTECTED] wrote: Hi, I started using PostgreSQL (8.0) about a year ago on Windows. Following the installation instructions, I created a 'postgres' user (BTW, this was based on the Short Version instructions for UNIX, i.e., adduser postgres --I'm not sure it this is made explicit elsewhere, even for UNIX). I'm now migrating to FreeBSD and was surprised to find that the port used 'pgsql' as the user. The maintainer said that was done to ensure backward compatibility because that *was* the original name. Since I didn't need to be backward compatible (and my Windows dbs already used 'postgres'), I tried to bypass that (sort of) requirement by renaming 'pgsql' to 'postgres' (in the passwd file) and changing the postgresql_user variable used in the rc startup file. That was OK until I tried to build 8.1.4_1. I figured out how to tweak the build files to stick with 'postgres' but then I realized I'd have to patch them every time I'd fetch a new build, so I went back to 'pgsql'. I'm curious about a few things. How long ago was the 'pgsql' to 'postgres' change (and maybe it would be helpful to know the rationale for the backward incompatible decision--I tried searching in the archives but 'pgsql' and 'postgres' are all too common)? Is there any problem with using 'pgsql' vs. 'postgres' (and are there any plans to deprecate or disallow the former at some point)? Are other UNIX/Linux ports in the same boat, or does any Linux port offer users a choice in this matter? Would any change to the build/install procs have to be done through the current port maintainer or are they somewhere in the PostgreSQL source tree (and subject to standard submission/review procedures)? I *think* that's a bsd decision to change the name. All of the linux systems I have used for the last 5-6 years have used 'postgres' as the user. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql user change to postgres
Joe [EMAIL PROTECTED] writes: I'm now migrating to FreeBSD and was surprised to find that the port used 'pgsql' as the user. The maintainer said that was done to ensure backward compatibility because that *was* the original name. It's always been postgres, at least as far as the standard name of the initial database superuser goes. I see no recommendation of pgsql in Postgres 4.2 for instance: $ gzcat postgres-v4r2.tar.gz | grep -i pgsql | wc 0 0 0 $ Various ports have used pgsql in their preferred installation paths, eg the Linux RPMs use /var/lib/pgsql/data as the preferred $PGDATA, but this should generally be transparent to users of the database. Changing the superuser name isn't transparent, though. FreeBSD is out in left field here. However, I don't see why you need to alter the build to change this. Just create a new user postgres and run the initdb step as that user. initdb uses the OS user name it's run as to determine the initial superuser name. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
-Original Message- From: David Fetter [mailto:[EMAIL PROTECTED] Sent: 30 June 2006 18:30 To: Dave Page Cc: Scott Marlowe; Jason McManus; pgsql general Subject: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL Last I checked, pgAdmin 1.4 doesn't help setting up clusters, which is one of the major headaches of a Slony-I setup. I also noticed that pgAdmin 1.6-to-be has at least some of those hooks. Any ETA on that? It certainly can setup a new cluster - the only feature we don't support is failover because Andreas was never happy with the way it worked and such a critical operation needs to be flawless. That said, don't ask me how to setup a new cluster - I've never actually done it myself (in pgAdmin). Regards, Dave. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgsql user change to postgres
Tom Lane wrote: FreeBSD is out in left field here. However, I don't see why you need to alter the build to change this. Just create a new user postgres and run the initdb step as that user. initdb uses the OS user name it's run as to determine the initial superuser name. That's exactly what I did first, and as you say, it worked fine (at least I was able to create another user and another database). It was when I tried starting PostgreSQL from boot, which requires adding postgresql_enable in /etc/rc.conf, that it didn't work. I tracked that down to /usr/local/etc/rc.d/010.pgsql.sh which uses a postgresql_user variable defined as pgsql by the port. And this in turn is driven by the Makefile and other files provided by the FreeBSD port. As I mentioned, I could fix those references to 'pgsql' but then I'd have to patch them any time I fetched a port update. So I guess the port procedures are not controlled by the Development Group? Joe ---(end of broadcast)--- TIP 1: 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] pgsql user change to postgres
Joe [EMAIL PROTECTED] writes: It was when I tried starting PostgreSQL from boot, which requires adding postgresql_enable in /etc/rc.conf, that it didn't work. I tracked that down to /usr/local/etc/rc.d/010.pgsql.sh which uses a postgresql_user variable defined as pgsql by the port. And this in turn is driven by the Makefile and other files provided by the FreeBSD port. As I mentioned, I could fix those references to 'pgsql' but then I'd have to patch them any time I fetched a port update. So I guess the port procedures are not controlled by the Development Group? We can't dictate the contents of port-supplied files, if that's what you mean. Plan B is to leave the pgsql user alone and add another superuser named postgres. You can have more than one superuser ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] different sort order in windows and linux version
On Jul 2, 2006, at 6:13 AM, Martijn van Oosterhout wrote: But I don't think anyone is actually considering importing ICU into the postgres source tree, are they? Why not? Size - I'm not sure this is relevent since I don't think we want to incorporate it into postgres itself, just let people use it if they have it. In any case though, the default dataset is 8MB. This includes support for every locale and charset it knows about. If you drop the conversion stuff (because postgres already has that) you're down to about 4MB. Why would you drop the ICU transcoding support instead of the existing postgres functions? Why the duplicated effort? Well, the Japanese think that UTF8 is not the solution to all their worries, so they won't be happy with a UTF8-only solution. Likewise, those of us who only need single-byte character sets won't be very happy with being forced to accept multi-byte processing overhead. I've not quite understood the japenese problem with Unicode. My understanding is that it was primarily due to widespread use of broken converters. Certain Japanese characters cannot make a reliable round-trip through Unicode. ICU uses UTF-16 as its store, so the Japanese folks won't be happy with an ICU-only solution. However, it would still be of great benefit to allow ICU to handle as much as possible, leaving the string encodings to the encoding experts. At the very least, it would be great to have ICU to handle encoding on a per-column basis (perhaps extending the text datatype with encoding info). Perhaps this would be a decent stopgap solution? The backend protocol would also need a version bump- currently, it converts all strings to a single encoding. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Default directory for postgres user?
What should the default directory for the postgres user be? I'm using OSX 10.4. Right now, the default directory is set to /dev/null. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgsql user change to postgres
Tom Lane wrote: Plan B is to leave the pgsql user alone and add another superuser named postgres. You can have more than one superuser ... Yes, that's what my hosting provider (hub.org, which also hosts postgresql.org) seems to have done. This also avoids another minor problem: initdb on UNIX appears to create a 'postgres' database regardless of who runs the procedure (aside from template0 and template1; I don't have a 'postgres' database on Windows and since it's been a year I don't recall what the Windows initdb did). So when the 'pgsql' user invokes psql without specifying a database, there's no 'pgsql' database to be found. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Default directory for postgres user?
The shell is probably /bin/false right? That indicates that the postgres user won't log in to an active session. If that's an issue, then you should change that to whatever you like (probably /usr/local/pgsql/). On Jul 2, 2006, at 12:29 PM, Victor Escobar wrote: What should the default directory for the postgres user be? I'm using OSX 10.4. Right now, the default directory is set to /dev/null. ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to Backup like in mysql or ms sql server
On Sunday 02 July 2006 01:42, Tino Wildenhain wrote: Uwe C. Schroeder wrote: You can technically just copy paste the postgresql data directory IF YOU SHUT DOWN THE POSTMASTER FIRST! Be aware that this will only work for the same version of postgresql. Also: this is not a good way to do it and I'd encourage you not to use this as general means of backup (it's ok if you want to create a quick clone of an existing database on a second machine - provided that the platform and postgresql version on there is identical to the source). Well, err. thats not completely true with current postgres versions: http://www.postgresql.org/docs/current/static/backup-online.html ... Ok, you're correct on that one. However I'd rather not encourage someone to mess with WAL and filesystem based backups when s/he hasn't even heard of pg_dump yet, simply because I can already see the next question popping up ... like in I had a failure and wanted to restore my backup, but everything is messed up now and I can't get it running - help please! :-) With a standard pg_dump that won't happen, so it's IMHO the safest way to deal with the backup problem for a newbie. On a side-note: that piece of documentation is pretty heavy reading and assumes quite some knowledge about how a DB system like postgresql works internally. For me it's always the least sophisticated approach that solves a given problem. The good old KISS principle applies again :-) Uwe Is there any way to back-up database like mysql or sql server we just copy and paste. Or maybe there is any tools to copy database when the service is shutdown. Where is postgresql put teh database files? Well, thats in the docs ;) (or see above) btw, just copy and paste w/o preparation is dangerous with the above databases too. Regards Tino -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PostgreSQL or mySQL
Hello List: I hope my question does not start war of the posts. This question is genuine. Please answer objectively: I am test setting a Master Server (MS) with CentOS 4.3, freeRADIUS, DNS, Apache, (mySQL PostgreeSQL), PHP, Postfix, etc. This Master Server will have all the software I we need for our operation. We will copy MS to each server and setup a load balancing servers. I have very little knowledge of either PostgreeSQL or mySQL. Please advise me as to which of these two software package to use? I need some specific examples as to superiority of one package over the other. I prefer using the package which has a goof GUI database design. Please help. I am posting this message to both lists. I am specifically interested in opinion of system analysts who have used both of these packages. Thanks. Kirti
Re: [GENERAL] Default directory for postgres user?
On 2 jul 2006, at 18.29, Victor Escobar wrote: What should the default directory for the postgres user be? I'm using OSX 10.4. Right now, the default directory is set to /dev/null. I've set the home directory to /var/empty and the shell to /usr/bin/ false, like most of the other daemon users. /dev/null should be ok though. Sincerely, Niklas Johansson Phone: +46-322-108 18 Mobile: +46-708-55 86 90 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Permission denied: logfile
Hello, I'm getting the following error when I do this step: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data logfile 21 Exit 1: Permission denied: logfile 21 I did a chown on /usr/local/pgsql/data and am running this as postgres. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Permission denied: logfile
Check where you are executing this.the user postgres does not have permission to write logfile in your current directory Victor Escobar wrote: Hello, I'm getting the following error when I do this step: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data logfile 21 Exit 1: Permission denied: logfile 21 I did a chown on /usr/local/pgsql/data and am running this as postgres. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] different sort order in windows and linux version
On 7/2/06, Agent M [EMAIL PROTECTED] wrote: Certain Japanese characters cannot make a reliable round-trip through Unicode. ICU uses UTF-16 as its store, so the Japanese folks won't be happy with an ICU-only solution. However, it would still be of great Could you explain what you mean and what's special with those characters? benefit to allow ICU to handle as much as possible, leaving the string encodings to the encoding experts. At the very least, it would be great to have ICU to handle encoding on a per-column basis (perhaps extending the text datatype with encoding info). Perhaps this would be a decent stopgap solution? The backend protocol would also need a version bump- currently, it converts all strings to a single encoding. Could you give an example of what that would look like in your opinion? I was thinking more along the lines of a setting in pg_hba.conf where the server uses or does not use something like ICU...at least as an intermediate solution. Adding a LOCALE clause to a column definition (similar to the ENCODING clause of the CREATE DATABASE statement) would solve most (not all) problems with a default locale. There still might be some non-deterministic behaviour with operations between strings in different locales but it's far from a showstopper. t.n.a. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] libpq: bind message supplies 2 parameters, but prepared statement requires 1
Hello,
these 2 statements work fine for me on the psql-prompt:
punbb= select id, username, md5('deadbeef' || password) from users
where id = 7;
id | username | md5
+--+--
7 | Alex | b962415469222eeb31e739c3afbc8a4a
(1 row)
punbb= select username from users where id = 7 and md5('deadbeef' ||
password) = 'b962415469222eeb31e739c3afbc8a4a';
username
--
Alex
(1 row)
However when I try to execute the latter query by my C-program,
then it fails, saying that my bind command supplies 2 arguments
(yes, that's true), but the prepared statement requires 1 argument
(why 1? I don't understand). I have prepared a separate simple
test case, could someone please explain what am I doing wrong?
#include err.h
#include stdio.h
#include libpq-fe.h
#define DB_CONN_STR host=/var/www/tmp user=punbb dbname=punbb
#define SQL_FETCH_USERNAME select username from users \
where id = $1 and md5('deadbeef' || password) = '$2'
int
main(int argc, char *argv[])
{
PGconn* conn;
PGresult* res;
const char *args[2];
charusername[201];
if ((conn = PQconnectdb(DB_CONN_STR)) == NULL)
err(1, Connect to '%s' failed: out of memory, DB_CONN_STR);
if (PQstatus(conn) != CONNECTION_OK)
err(1, Connect to '%s' failed: %s,
DB_CONN_STR, PQerrorMessage(conn));
if ((res = PQprepare(conn, sql_fetch_username,
SQL_FETCH_USERNAME, 2, NULL)) == NULL)
err(1, Preparing statement '%s' failed: out of memory,
SQL_FETCH_USERNAME);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
err(1, Preparing statement '%s' failed: %s,
SQL_FETCH_USERNAME, PQerrorMessage(conn));
PQclear(res);
args[0] = 7;
args[1] = b962415469222eeb31e739c3afbc8a4a;
if ((res = PQexecPrepared(conn, sql_fetch_username,
2, args, NULL, NULL, 0)) == NULL)
err(1, Executing statement '%s' failed: out of memory,
SQL_FETCH_USERNAME);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
err(1, Executing statement '%s' failed: %s,
SQL_FETCH_USERNAME, PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return 0;
}
And here is the error message I get:
laptop72:src {541} ./fetch-user
fetch-user: Executing statement 'select username from users where id =
$1 and md5('deadbeef' || password) = '$2'' failed: ERROR: bind
message supplies 2 parameters, but prepared statement
sql_fetch_username requires 1
: No such file or directory
Thank you
Alex
PS: Using Postgresql 8.1.0 (from packages) on OpenBSD/386 -current
--
http://preferans.de
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] different sort order in windows and linux version
On Sun, Jul 02, 2006 at 12:25:43PM -0400, Agent M wrote: On Jul 2, 2006, at 6:13 AM, Martijn van Oosterhout wrote: But I don't think anyone is actually considering importing ICU into the postgres source tree, are they? Why not? Because it's a project of similar size to postgres and probably nearly as old and I don't think anyone here actually wants to maintain it. I mean, we could incorporate the source for readline, openssl, kerberos, the C library but why. That project has maintainers already and we only wan to use it, not fork it. If you drop the conversion stuff (because postgres already has that) you're down to about 4MB. Why would you drop the ICU transcoding support instead of the existing postgres functions? Why the duplicated effort? Because we would want to be bug-for-bug compatable to previous releases. I suppose it would be possible if someone checked that the end result is the same. Certain Japanese characters cannot make a reliable round-trip through Unicode. ICU uses UTF-16 as its store, so the Japanese folks won't be happy with an ICU-only solution. However, it would still be of great benefit to allow ICU to handle as much as possible, leaving the string encodings to the encoding experts. We don't need round-trip through unicode, since we're only doing one way conversions for the purpose of collation. BTW, this site seems to have a good discussion of Japanese characters and Unicode. http://www.jbrowse.com/text/unij.html At the very least, it would be great to have ICU to handle encoding on a per-column basis (perhaps extending the text datatype with encoding info). Perhaps this would be a decent stopgap solution? The backend protocol would also need a version bump- currently, it converts all strings to a single encoding. That's called SQL COLLATE support and that's an order of magnitude harder than adding support for ICU. See previous dicussion on -hackers. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] libpq: bind message supplies 2 parameters, but prepared statement requires 1
On Sun, Jul 02, 2006 at 11:17:12PM +0200, Alexander Farber wrote:
snip
However when I try to execute the latter query by my C-program,
then it fails, saying that my bind command supplies 2 arguments
(yes, that's true), but the prepared statement requires 1 argument
(why 1? I don't understand). I have prepared a separate simple
test case, could someone please explain what am I doing wrong?
#include err.h
#include stdio.h
#include libpq-fe.h
#define DB_CONN_STR host=/var/www/tmp user=punbb dbname=punbb
#define SQL_FETCH_USERNAME select username from users \
where id = $1 and md5('deadbeef' || password) = '$2'
You've got quotes around the $2, so it's seeing a string, not a
parameter.
Have a nice day,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
From each according to his ability. To each according to his ability to
litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Installation problems
Victor Escobar wrote: The directory is set to /dev/null. I also forgot to add that I'm using OS X 10.4. Since obviously /dev/null means the bit-bucket, what should I set the home directory to? Always CC the list, you'll get much faster/better responses. Set the home dir to the base of your postgres installation. If you installed postgres into /usr/local/pgsql, set it to that. chris smith wrote: On 6/30/06, Victor Escobar [EMAIL PROTECTED] wrote: Hello, I'm going through the elongated instructions of installing pgsql and am stuck at the point where one types: % su - postgres When I type this and type in the password I chose, I get the following error: 'su: no directory' What directory does /etc/passwd have for the postgres user? Does it exist? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 1: 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] PostgreSQL or mySQL
I hope my question does not start war of the posts. This question is genuine. Please answer objectively: I am test setting a Master Server (MS) with CentOS 4.3, freeRADIUS, DNS, Apache, (mySQL PostgreeSQL), PHP, Postfix, etc. This Master Server will have all the software I we need for our operation. We will copy MS to each server and setup a load balancing servers. I have very little knowledge of either PostgreeSQL or mySQL. Please advise me as to which of these two software package to use? I need some specific examples as to superiority of one package over the other. I prefer using the package which has a goof GUI database design. Please help. I am posting this message to both lists. I am specifically interested in opinion of system analysts who have used both of these packages. I am not the experienced analyst that you seek. But there are many threads generated by such ones on this very topic: Here is one such thread: (there are many others if you care to search for them :-) ) http://archives.postgresql.org/pgsql-general/2006-03/msg01004.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] db question - dynamic fields in db
you mean there are 3 tables the list of items purchased - this is what i am asking for the list of items purchased What is difference between T_SALES_CC_DETAIL and T_SALES_DETAIL thanks a lot ron - T_SALES_CC_DETAIL - - SALES_ID INTEGER PRIMARY KEY FOREIGN KEY (T_SALES_HEADER.SALES_ID), CC_NUMBER CHAR(16), EXPIRE_DATE CHAR(6) T_SALES_DETAIL - -- SALES_ID INTEGER FOREIGN KEY (T_SALES_HEADER.SALES_ID), TRAN_SRLNOSMALLINT, INVENTORY_ID INTEGER FOREIGN KEY (T_INVENTORY.INVENTORY_ID), QUANTITY SMALLINT, SALE_AMOUNT NUMERIC(10,2) PRIMARY KEY (SALES_ID, TRAN_SRLNO) Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 a wrote: hi i want to know how to add a list of items to a database it is basically text, but different fields but the problem is i dont know how many fields are there before hand so i m not sure how to store them in the db sometime i need to store 10 elements and some other times 5 thanks a lot MUMPS has repeating fields, I think. :) Pick definitely does... Seriously, though, what you need to do is to put that section of the database into 1st Normal Form. For example, a sales record has an sales id number, customer name, transaction date/time, store number, cash_credit flag, credit card number, reversal/adjustment flag, and the list of items purchased. So, this is how the tables would look: T_SALES_HEADER - -- SALES_ID INTEGER PRIMARY KEY, CUST_ID INTEGER FOREIGN KEY (T_CUSTOMER_NAME.CUST_ID), TRAN_DATE DATE, TRAN_TIME TIME, STORE_ID SMALLINT, EMPLOYEE_ID INTEGER, CASH_CREDIT_FLCHAR(1), IS_REVERSAL_FLCHAR(1), IS_REVERSED_FLCHAR(1), XREF_SALES_ID INTEGER T_SALES_CC_DETAIL - - SALES_ID INTEGER PRIMARY KEY FOREIGN KEY (T_SALES_HEADER.SALES_ID), CC_NUMBER CHAR(16), EXPIRE_DATE CHAR(6) T_SALES_DETAIL - -- SALES_ID INTEGER FOREIGN KEY (T_SALES_HEADER.SALES_ID), TRAN_SRLNOSMALLINT, INVENTORY_ID INTEGER FOREIGN KEY (T_INVENTORY.INVENTORY_ID), QUANTITY SMALLINT, SALE_AMOUNT NUMERIC(10,2) PRIMARY KEY (SALES_ID, TRAN_SRLNO) - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEppjZS9HxQb37XmcRApNQAJ9L3GZCxVj1pUuCioId5QkpOp7FlACeJQkY JDIoYyAdLvanH9g7JMyAZJM= =jufR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Is there a command like uf_purge
hi all, is there a command like uf_purge which purges the last x days of data. i noticed this command somewhere but i cant find a reference to it any documentation. is it a deprecated command and we just use delete now. whats its functionality? any ideas. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] db question - dynamic fields in db
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Correct, 3 tables. T_SALES_DETAIL records are line items, the stuff the customer bought. There is only a T_SALES_CC_DETAIL record if the transaction happens to use a credit card. a wrote: you mean there are 3 tables the list of items purchased - this is what i am asking for the list of items purchased What is difference between T_SALES_CC_DETAIL and T_SALES_DETAIL thanks a lot ron - T_SALES_CC_DETAIL - - SALES_ID INTEGER PRIMARY KEY FOREIGN KEY (T_SALES_HEADER.SALES_ID), CC_NUMBERCHAR(16), EXPIRE_DATE CHAR(6) T_SALES_DETAIL - -- SALES_ID INTEGER FOREIGN KEY (T_SALES_HEADER.SALES_ID), TRAN_SRLNO SMALLINT, INVENTORY_ID INTEGER FOREIGN KEY (T_INVENTORY.INVENTORY_ID), QUANTITY SMALLINT, SALE_AMOUNT NUMERIC(10,2) PRIMARY KEY (SALES_ID, TRAN_SRLNO) Ron Johnson wrote: a wrote: hi i want to know how to add a list of items to a database it is basically text, but different fields but the problem is i dont know how many fields are there before hand so i m not sure how to store them in the db sometime i need to store 10 elements and some other times 5 thanks a lot MUMPS has repeating fields, I think. :) Pick definitely does... Seriously, though, what you need to do is to put that section of the database into 1st Normal Form. For example, a sales record has an sales id number, customer name, transaction date/time, store number, cash_credit flag, credit card number, reversal/adjustment flag, and the list of items purchased. So, this is how the tables would look: T_SALES_HEADER -- SALES_ID INTEGER PRIMARY KEY, CUST_ID INTEGER FOREIGN KEY (T_CUSTOMER_NAME.CUST_ID), TRAN_DATE DATE, TRAN_TIME TIME, STORE_ID SMALLINT, EMPLOYEE_ID INTEGER, CASH_CREDIT_FLCHAR(1), IS_REVERSAL_FLCHAR(1), IS_REVERSED_FLCHAR(1), XREF_SALES_ID INTEGER T_SALES_CC_DETAIL - SALES_ID INTEGER PRIMARY KEY FOREIGN KEY (T_SALES_HEADER.SALES_ID), CC_NUMBER CHAR(16), EXPIRE_DATE CHAR(6) T_SALES_DETAIL -- SALES_ID INTEGER FOREIGN KEY (T_SALES_HEADER.SALES_ID), TRAN_SRLNOSMALLINT, INVENTORY_ID INTEGER FOREIGN KEY (T_INVENTORY.INVENTORY_ID), QUANTITY SMALLINT, SALE_AMOUNT NUMERIC(10,2) PRIMARY KEY (SALES_ID, TRAN_SRLNO) - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEqINCS9HxQb37XmcRAu2AAKDh7IqlCpIafZdZ+wDdujOyaAMXewCfQcYA 2+UKYtVQie2GMfoZ6JHs9p0= =STKu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is there a command like uf_purge
On Sun, Jul 02, 2006 at 06:40:30PM -0700, [EMAIL PROTECTED] wrote: is there a command like uf_purge which purges the last x days of data. i noticed this command somewhere but i cant find a reference to it any documentation. is it a deprecated command and we just use delete now. whats its functionality? Google, Google Groups, the PostgreSQL mailing list archives, and the PostgreSQL source code and documentation for 7.3 and later all contain zero instances of uf_purge. Where did you notice it? Might it have been locally written for your system? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL or mySQL
Kirti S. Bajwa wrote: Hello List: I hope my question does not start war of the posts. This question is genuine. Please answer objectively: I am test setting a Master Server (MS) with CentOS 4.3, freeRADIUS, DNS, Apache, (mySQL PostgreeSQL), PHP, Postfix, etc. This Master Server will have all the software I we need for our operation. We will copy MS to each server and setup a load balancing servers. I have very little knowledge of either PostgreeSQL or mySQL. Please advise me as to which of these two software package to use? I need some specific examples as to superiority of one package over the other. I prefer using the package which has a goof GUI database design. Please help. I am posting this message to both lists. I am specifically interested in opinion of system analysts who have used both of these packages. This topic arises on almost a weekly basis. Have you read the archives? Do you have specific questions after having read the archives? You say absolutely nothing about your application. Neither PostgreSQL nor MySQL is completely superior to the other in all circumstances. If such were the case, both projects being open source, the superior one would been universally adopted and the inferior one would have disappeared. Since that hasn't happened, you can probably safely assume that MySQL is good for some applications, and PostgreSQL is good for some applications. If your question truly is genuine, and you've done some reading, post back with some more specifics that people can comment on with some relevance. -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] different sort order in windows and linux version
Dragan Matic [EMAIL PROTECTED] writes: I was about to say the same thing. I think that the whole point in having a portable database system is that the data inside the database should behave the _same way_ no matter what operating system database is running on - client shouldn't be aware of the server OS. So on that argument, we need to eliminate datatypes float8 and float4 forthwith, because they don't behave quite the same on every machine. And int8 too, because it's not supported on every machine. And --enable-integer-datetimes has got to go; in fact configure should not have any options at all. Personally, I'd be perfectly happy with pgsql if I could choose to make text operations up to 2-3x slower without the fuss of how it's going to work on a certain platform, in each pgsql version. Fine for you, not so fine for other people with different concerns. I'm not unsympathetic to your general point, but black-and-white arguments won't get far in this discussion. It's all about tradeoffs ... it's most definitely not about one-size-fits-all. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
