[GENERAL] phppgadmin
Hi,I'm back on my problem with PHPpgadmin.. :-(when i log in for the first time, the server icon of servers list, is changing to Loadingand it is back on not connected server, but i've access to my database normally. each time that i click on some items (create database, accounts, create tables,...), PHPpgadmin asks me again username and pwd.i've checked if my browsers allow cookies and sessions.. Everything is fine... I also downgrade from apache 2.2.2 to apache 2.0.58 just in case of (based on my problem with php5apache2.dll issue in apache 2.2.2)Does anyone have some idea where could be located the problem ?I did the same configration at work that i have a home (from my network). thanks a lotAlain
[GENERAL] different sort order in windows and linux version
I have two postgres servers, one on linux (fedora core 5), one on windows, both are version 8.1.4. Both databases are initialized with locale Croatian and win1250 encoding. running pg_controldata on windows returns this LC_COLLATE: Croatian_Croatia.1250 LC_CTYPE:Croatian_Croatia.1250 the same command on linux returns this LC_COLLATE:hr_HR LC_CTYPE: hr_HR which is the same, I suppose. the sample databases are both initialized the same way CREATE DATABASE sample WITH OWNER = postgres ENCODING = 'WIN1250' TABLESPACE = pg_default; both databases have the same sample table CREATE TABLE sample ( some_text char(13) NOT NULL ); when I execute a query 'SELECT SOME_TEXT FROM SAMPLE ORDER BY SOME_TEXT' I get different sort order on these two servers. On the left side is windows server sort order, and on the right side is linux server sort order. all values are left padded with spaces. Postgres windows Postgres linux 0 0 1 000 2 0 3 1 4 11 5 12 6 123 7 125 8 13 9 14 a 15 A 2 b 2343255 b 234455 B 243 c 25 C 3 d 31TA001 e 32NU280 f 35 g 4 z 45 11 5 12 55 13 56455 14 6 15 65 25 7 35 75 45 8 55 85 65 9 75 a 85 A aa aa ab aaa aB aab Ab ab AB aB ba Ab bb AB cc aba 000 abA 123 aBa 125 AbA 243 ABA aaa abb aab abb aba abc abA abc aBa abC AbA Abc ABA ABC abb abcde abb acc abc b abc b abC B Abc ba ABC baa acc bab baa bb bab bba bba bbb bbb bca bca c zzz C zzz cc 56455 d abcde e 234455 f 2343255 g 31TA001 z 32NU280 zzz 0 zzz z z The only thing I can think of is that somehow these databases weren't initialized in the same way, if so what is the difference? Tnx in advance Dragan ---(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 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. Both databases are initialized with locale Croatian and win1250 encoding. running pg_controldata on windows returns this LC_COLLATE: Croatian_Croatia.1250 LC_CTYPE:Croatian_Croatia.1250 the same command on linux returns this LC_COLLATE:hr_HR LC_CTYPE: hr_HR which is the same, I suppose. Well, apparently not. Postgres makes no attempt to understand collations nor try to determine whether they make sense. If you want to have the same collation on Windows and Linux, I think you're going to have trouble. 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. Hope this helps, -- 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
[GENERAL] Installation problems
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' Anybody know how I fix this? I've Googled and came up with nothing. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installation problems
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] finding gps within polygon
On Thu, Jun 29, 2006 at 11:15:09PM -0400, blackwater dev wrote: Does anyone have a good tutorial on finding gps points within a polygon? I need to be able to pass in a list of gps coordinates and let postgres return to me matching cities from my cities table that are within that polygon. PostgreSQL has some basic geometry types and functions, but for more advanced spatial work consider using PostGIS (an add-on to PostgreSQL). The documentation has a Using PostGIS chapter with examples. http://www.postgis.org/ -- Michael Fuhr ---(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] pg_restore: [archiver] could not open input file
Tom Lane wrote: [EMAIL PROTECTED] writes: C:\www\foo.com\trunk\dbpg_restore -h localhost -U postgres -W mySecretPassword script.sql I don't think you're supposed to put the password on the command line like that. It's probably taking mySecretPassword as the file name to read. Hi Tom, it doesn't seem even that: I tried to exclude all other parameters and leaving just: c:\ pg_restore script.sql also specifying the absolute path it's still giving an error, though different: pg_restore: [archiver] input file does not appear to be a valid archive Dunno, it's just a regular db dump with function definitions and create table instructions Thanks anyway, Lorenzo ---(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
[GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4
On Converting from MySQL 5.0.x to PostgreSQL 8.1.x -- I am in the process of converting a couple of major sites from MySQL 5.0.22 to PostgreSQL 8.1.4, and I thought I would share some of my observations on this process and the two database systems in general. I feel I am in a good position to do this, as I am fairly familiar in-depth with MySQL's modern features, am relatively technology-agnostic, and having done this since the MySQL project has matured substantially over the last couple of years, I feel I can give insight into the features of both systems from a current perspective. I will attempt to do this from an objective standpoint, as I still see benefits and drawbacks to both systems (no software will ever meet the needs of every situation). If you are looking for a document that mocks one system or another, this is not it; there are countless results for X sucks on $your_favorite_search_engine. My reasons for making this switch are primarily due to having a bit of spare time, wanting to expand my horizons and familiarize myself with another well-respected open source project, some of the nice in-built procedural language features of PostgreSQL, and basically wanting to form my own opinion of the features of both systems. That being said, I have really been impressed so far with the features I am discovering, and becoming happier each day. It is by no means comprehensive; I have just highlighted some of the more notable and obvious differences as I discovered them. Most of the information in here is pretty basic for those of you very familiar with both systems. I also only highlight the differences in the Unix/POSIX versions of these programs. (I don't run Windows as a server, and I don't care to. My technology agnosticism stops at inadequate systems.) All comments are current as of the time of writing (Spring/Summer 2006). Error corrections are very welcome. Flames can go to /dev/null. Don't care. Oh, and since I wrote this in vim, it is best read with a monospace font. :^) Major differences I have noted: --- MySQL 5.0.x: * Multiple storage engines with different features. * Supports multi-insert syntax (INSERT INTO foo VALUES (1,2), (3,4) ...) * A few more access controls on features built-in to the GRANT tables. Many of these are still present, but implemented in other ways in PostgreSQL. * Single AUTO_INCREMENT column allowed per table. * Easy, built-in and extensive replication support. * Single datastore location per server. * ALL Stored Procedures are kept in the mysql system database. PostgreSQL 8.1.x: * Embedded procedures in multiple native languages (stored procedures and functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL) * Extensive and versatile procedural language functionality. * User-definable data types and operators. * Multiple sequence generators allowed per table. * Replication support still rudimentary. * Stored procedures are kept (somewhat more logically, imho) in the corresponding databases. * Multiple datastore locations possible using tablespaces concept. (For the record, MySQL will have tablespaces when 5.1.x is stabilized.) * Most system variables, built-in types and features configurable as they are just kept in a system catalog. * Allows deletions and subselects to specify the same table (e.g. DELETE FROM foo WHERE id IN (SELECT id FROM foo WHERE...) ). MySQL does not allow this as of 5.0.22. * Copious documentation on the database internals, for extending the database itself. Pointers, tips, quick facts and gotchas for other people converting: * Don't bother using an old version, just go for 8.1.4 (or whatever is new at the time of your conversion. This should be common sense.) * Since Pg uses a full transactional storage engine, the speed is roughly comparable to InnoDB, rather than the stock MyISAM format. * PostgreSQL's TCP port is 5432 by default. * The main server process on PostgreSQL is 'postmaster'. * 'postmaster' can be controlled via the 'pg_ctl' command. * The administrative user is called 'postgres' by default. * Like MySQL, Pg uses the system user as default, if no username is specified when connecting. * The command-line client is called 'psql'. * PostgreSQL by default comes configured to disallow network connections. To enable these, you must follow these steps: 1. Edit $DATADIR/pg_hba.conf and add access permissions. 2. Edit $DATADIR/postgresql.conf and uncomment the listen_addresses line, setting it to something reasonable. 3. Restart postmaster. * PostgreSQL relies extensively upon quick aliases for common features within the CLI shell. MySQL offers many similar features, but they aren't used as much from what I have observed. * MySQL combines the concepts of 'database' and 'schema' into one. PostgreSQL differentiates the two.
Re: [GENERAL] pg_restore: [archiver] could not open input file
On Thu, Jun 29, 2006 at 07:18:56AM -0700, [EMAIL PROTECTED] wrote: it doesn't seem even that: I tried to exclude all other parameters and leaving just: c:\ pg_restore script.sql If that's just a straight SQL script produced by pg_dump, then you don't need pg_restore, just psql -f script.sql will do. 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] User privileges in web database applications
On Jun 29, 2006, at 5:58 PM, Tim Hart wrote: I'm coming in a bit late to this conversation, so forgive me if I've missed something. Isn't this problem the reason that connection pools were created? In a connection pool, connections are only associated with a particular user for the duration of a transaction. Once the transaction is complete, the connection goes back to the pool. Right, this is standard operating procedure. But the original poster mentioned tying the connection to a particular web user/session. In other words, one connection per user. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4
Jason McManus [EMAIL PROTECTED] writes: I am in the process of converting a couple of major sites from MySQL 5.0.22 to PostgreSQL 8.1.4, and I thought I would share some of my observations on this process and the two database systems in general. Nice notes! I see only one small error: * Pg's default character set (in 8.1.4) is UTF8. I don't believe there is any fixed default character set. Each installation will have a default locale and encoding, but these depend on the locale under which initdb was run. From the above comment I surmise that you initdb'd under some UTF8-using locale ... Thank you, and I hope that these notes prove helpful to others! Perhaps they should go on the project website somewhere? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL and OpenVZ
Hello, Does anybody have experience in running PostgreSQL inside OpenVZ (http://openvz.org/) or any other virtual private servers solutions? I'm interested in both cases of running single PostgreSQL server and multiple PostgreSQLs on one physical machine under relatively high (though not IO-limited) load. Any caveats or limitations of this approach? Any known penalties or problems with this tandem? Best regards, Ivan Zolotukhin ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4
I don't believe there is any fixed default character set. Each installation will have a default locale and encoding, but these depend on the locale under which initdb was run. From the above comment I surmise that you initdb'd under some UTF8-using locale ... Ah, great. Thank you for the clarification. I did indeed run initdb on different systems, but they were all under UTF-8, so I guess I had drawn the wrong conclusion. Perhaps they should go on the project website somewhere? I was thinking so as well, but I'm not sure where, or who to submit to if there is no wiki or other external editing method. Any pointers for who to contact/where to submit? Thanks, Jason ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
Yes indeed if it would be possible, it should go to the website. Good comparison without saying such things like this dbms has xx features and this one has xx features. Some addtitional information for setcion Further information: phpPgAdmin http://phppgadmin.sourceforge.net/ Gborg http://gborg.postgresql.org/ Greetings, -Franz -Ursprüngliche Nachricht- Von: Tom Lane [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 30. Juni 2006 16:17 An: Jason McManus Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4 Jason McManus [EMAIL PROTECTED] writes: I am in the process of converting a couple of major sites from MySQL 5.0.22 to PostgreSQL 8.1.4, and I thought I would share some of my observations on this process and the two database systems in general. Nice notes! I see only one small error: * Pg's default character set (in 8.1.4) is UTF8. I don't believe there is any fixed default character set. Each installation will have a default locale and encoding, but these depend on the locale under which initdb was run. From the above comment I surmise that you initdb'd under some UTF8-using locale ... Thank you, and I hope that these notes prove helpful to others! Perhaps they should go on the project website somewhere? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4
Jason McManus [EMAIL PROTECTED] writes: Perhaps they should go on the project website somewhere? I was thinking so as well, but I'm not sure where, or who to submit to if there is no wiki or other external editing method. Any pointers for who to contact/where to submit? Not my department, but if none of the project's web people follow up in the next few hours, try inquiring on pgsql-docs or pgsql-www. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] User privileges in web database applications
Right, this is standard operating procedure. But the original poster mentioned tying the connection to a particular web user/session. In other words, one connection per user. Maybe I didn't phrase the question correctly, but I think that the answer to my question is, indeed, connection pooling, probably pgpool. Thanks all. -- Antonis Christofides +30-2107722840 (work) +30-2106521785 (home) +30-6979924665 (mobile) ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
I agree with Tom, nice notes. I noted a few minor issues that seem to derive from a familiarity with MySQL. I'll put my corrections below... On Fri, 2006-06-30 at 08:17, Jason McManus wrote: On Converting from MySQL 5.0.x to PostgreSQL 8.1.x -- Major differences I have noted: --- MySQL 5.0.x: * Easy, built-in and extensive replication support. Not sure how extensive it is. It's basically synchronous single master single slave, right? It is quite easy though. PostgreSQL 8.1.x: * Embedded procedures in multiple native languages (stored procedures and functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL) Note that there are a dozen or more other languages as well. Just FYI. Off the top of my head, plPHP, plJ (java there's two different java implementations, I think) and plR (R is the open source equivalent of the S statistics language) * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. Keep in mind, slony can be applied to a living database while it's running, and can run between different major versions of postgresql. That's a pretty advanced feature. Plus, if the replication daemons die (kill -9ed or whatever) you can restart replication and slony will come right back where it was and catch up. Pointers, tips, quick facts and gotchas for other people converting: * MySQL combines the concepts of 'database' and 'schema' into one. PostgreSQL differentiates the two. While the hierarchy in MySQL is database.table.field, PostgreSQL is roughly: database.schema.table.field. A schema is a 'logically grouped set of tables but still kept within a particular database.' This could allow separate applications to be built that still rely upon the same database, but can be kept somewhat logically separated. The default schema in each database is called 'public', and is the one referred to if no others are specified. This can be modified with 'SET search_path TO ...'. This is a VERY good analysis of the difference between the two databases. * Pg uses a 'template1' pseudo-database that can be tailored to provide default objects for new database creation, if you should desire. It obviously also offers a 'template0' database that is read-only and offers a barebones database, more equivalent to the empty db created with mysql's CREATE DATABASE statement. This isn't quite right. template0 is a locked and pure copy of the template database. It's there for break glass in case of emergency use. :) template1, when you first initdb, is exactly the same as template0, but you can connect to it, and alter it. Both of these are real postgresql databases. template1 is the database that gets copied by default when you do create database. Note that you can also define a different template database when running create database, which lets you easily clone any database on your machine. create database newdb with template olddb * Pg uses the 'serial' column type instead of AUTO_INCREMENT. This allows more than one independent sequence to be specified per table (though the utility of this may be of dubious value). These are closer to Oracle's concept of sequence generators, and they can be manipulated with the currval(), nextval(), setval(), and lastval() functions. Don't forget 64bit bigserials too. * Pg requires its tables and databases be 'vacuumed' regularly to remove completed transaction snapshots and optimize the tables on disk. It is necessary because the way that PostgreSQL implements true MVCC is by writing all temporary transactions to disk and setting a visibility flag for the record. Vacuuming can be performed automatically, and in a deferred manner by using vacuum_cost settings to limit it to low-load periods or based upon numerous other criteria. See the manual for more information. Interestingly enough, MySQL's innodb tables do almost the exact same thing, but their vacuum process is wholly automated. Generally, this means fewer issues pop up for the new dba, but when they do, they can be a little harder to deal with. It's about a wash. Of course, as you mentioned earlier, most mysql folks aren't using innodb. * While MySQL supports transactions with the InnoDB databases, many MySQL users generally do not use them extensively enough. With Pg, due to the behaviour of the server in attempting to ensure data integrity in a variety of situations (client disconnection, network trouble, server crashes, etc.), it is highly advisable to become familiar and utilize transactions a lot more, to
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: 30 June 2006 15:47 To: Jason McManus Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4 Jason McManus [EMAIL PROTECTED] writes: Perhaps they should go on the project website somewhere? I was thinking so as well, but I'm not sure where, or who to submit to if there is no wiki or other external editing method. Any pointers for who to contact/where to submit? Not my department, but if none of the project's web people follow up in the next few hours, try inquiring on pgsql-docs or pgsql-www. 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... Interesting notes BTW Jason - thanks for sharing. Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] limit results to one row per foreign object
hi all, I have the following schema: CREATE TABLE auctions ( id serial NOT NULL, user_id int4, title varchar(255), body varchar(255), CONSTRAINT auctions_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE bids ( id serial NOT NULL, auction_id int4, user_id int4, amount float8, created_at timestamp, CONSTRAINT bids_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE users ( id serial NOT NULL, username varchar(255), CONSTRAINT users_pkey PRIMARY KEY (id) ) WITHOUT OIDS; I'd like to return all the bids for a given auction, but limit it to only the *latest* bid from each user. so regardless of how many bids a user has placed, only their latest is returned. I dont have a clue where to even start with this and would appreciate some pointers thanks alan ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Scott Marlowe wrote: [snip] However, the more interesting thing here, is that every statement, including DDL is transactable, except for a couple of big odd ones, like create database. So, in postgresql, you can do: begin; create table xyz... alter table abc... insert into abc select * from iii update iii...; drop table iii; (oops, I messed up something) rollback; But isn't that what it means to be transactional? Or am I spoiled by my big, expensive enterprise database? - -- 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 iD8DBQFEpUGPS9HxQb37XmcRAiJwAJ9/A/N/OgmslveSsX3Xym2QnDQz1gCghPD0 YX882Kv81hzZ4AKjaIVKHg8= =Gsml -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: 30 June 2006 16:12 To: Jason McManus Cc: pgsql general Subject: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. I'm not sure that many people necessarily realise it, but you can also drive Slony directly from pgAdmin 1.4+ if slonik scripts give you a headache. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] limit results to one row per foreign object
Alan Bullock wrote: hi all, I have the following schema: CREATE TABLE auctions ( id serial NOT NULL, user_id int4, title varchar(255), body varchar(255), CONSTRAINT auctions_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE bids ( id serial NOT NULL, auction_id int4, user_id int4, amount float8, created_at timestamp, CONSTRAINT bids_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE users ( id serial NOT NULL, username varchar(255), CONSTRAINT users_pkey PRIMARY KEY (id) ) WITHOUT OIDS; I'd like to return all the bids for a given auction, but limit it to only the *latest* bid from each user. so regardless of how many bids a user has placed, only their latest is returned. I dont have a clue where to even start with this and would appreciate some pointers thanks Hmm... No foreign key constraints? I think you're looking for something like: SELECT * FROM bids WHERE auction_id = 1234 GROUP BY user_id HAVING created_at = MAX(created_at); You could also use a subselect with an order by created_at DESC limit 1 over each users bids. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
Ron Johnson wrote: begin; create table xyz... alter table abc... insert into abc select * from iii update iii...; drop table iii; (oops, I messed up something) rollback; But isn't that what it means to be transactional? Or am I spoiled by my big, expensive enterprise database? Well, according to my colleague here this wasn't possible until now (partially!) in Oracle 10. Meaning it's not common-place even among enterprise db's. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
Scott Marlowe [EMAIL PROTECTED] writes: On Fri, 2006-06-30 at 08:17, Jason McManus wrote: * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. Keep in mind, slony can be applied to a living database while it's running, and can run between different major versions of postgresql. That's a pretty advanced feature. Plus, if the replication daemons die (kill -9ed or whatever) you can restart replication and slony will come right back where it was and catch up. It might be worth pointing out that mysql's replication falls over if you so much as look at it crosseyed. I have not had to use it for production purposes, but I can tell you that the mysql replication regression tests fail ... irreproducibly of course ... almost one time in two in Red Hat's build environment. I've been able to trace a few of these failures to quirks of the build environment, like trying to build x86 and x86_64 at the same time in different chroots of the same machine (must take care not to use same TCP port numbers for tests), but it still seems flaky as hell. regards, tom lane ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
On Friday 30. June 2006 17:12, Scott Marlowe wrote: And, I hate the fact that CTRL-C in the mysql command line tool exits the tool instead of interrupting the current query. I agree, it's a nuisance. In PostgreSQL it interrupts the current query. CTRL-\ will kill the client if you need to. Or Ctrl-D, which also is a common way of terminating a command-line interface, like the Python interpreter. It's much easier to remember than the Ctrl-\ . -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
Out of curiosity, which big, expensive enterprise database are you spoiled by? Many that I support do not allow DDL within an transaction, or if they allow it, there are many caveats and rules. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson Sent: Friday, June 30, 2006 8:22 AM To: pgsql general Subject: Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Scott Marlowe wrote: [snip] However, the more interesting thing here, is that every statement, including DDL is transactable, except for a couple of big odd ones, like create database. So, in postgresql, you can do: begin; create table xyz... alter table abc... insert into abc select * from iii update iii...; drop table iii; (oops, I messed up something) rollback; But isn't that what it means to be transactional? Or am I spoiled by my big, expensive enterprise database? - -- 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 iD8DBQFEpUGPS9HxQb37XmcRAiJwAJ9/A/N/OgmslveSsX3Xym2QnDQz1gCghPD0 YX882Kv81hzZ4AKjaIVKHg8= =Gsml -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] limit results to one row per foreign object
Without trying it out, how about something like: select username, maxbid from users u, ( select user_id, max(amount) as maxbid from bids group by user_id where auction_id = XXX ) as b where u.id = b.user_id; John Alan Bullock wrote: hi all, I have the following schema: CREATE TABLE auctions ( id serial NOT NULL, user_id int4, title varchar(255), body varchar(255), CONSTRAINT auctions_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE bids ( id serial NOT NULL, auction_id int4, user_id int4, amount float8, created_at timestamp, CONSTRAINT bids_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE users ( id serial NOT NULL, username varchar(255), CONSTRAINT users_pkey PRIMARY KEY (id) ) WITHOUT OIDS; I'd like to return all the bids for a given auction, but limit it to only the *latest* bid from each user. so regardless of how many bids a user has placed, only their latest is returned. I dont have a clue where to even start with this and would appreciate some pointers thanks alan ---(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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
Ron Johnson [EMAIL PROTECTED] writes: Scott Marlowe wrote: However, the more interesting thing here, is that every statement, including DDL is transactable, except for a couple of big odd ones, like create database. So, in postgresql, you can do: But isn't that what it means to be transactional? Or am I spoiled by my big, expensive enterprise database? Being able to roll back DDL (table-schema modifications) isn't that common. Since PG keeps most of its schema information in tables, we have it easier than some other systems supporting DDL rollback, but it's still tricky. As an example, a long time ago we used to name table files after the table and database directories after the database, which made it easy to see what was what under $PGDATA, but prevented a lot of DDL from being transactional. For instance BEGIN; DROP TABLE foo; CREATE TABLE foo (some-new-definition); ROLLBACK; couldn't work because there would need to be two physical files named foo in the interim until you commit or roll back. ALTER TABLE RENAME had some related problems. Now we name all the filesystem objects using OIDs that can be chosen to never collide, even if they belong to database objects with similar names. Last I checked, mysql was still using table names for file names, so they're on the wrong side of this. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] limit results to one row per foreign object
If you don't mind using a (quite useful) postgres extension, this might work for you: select distinct on (auctions.id, users.id) * from auctions, bids, users where join clauses order by auctions.id, users.id, created_at desc; Hope this helps, On Fri, Jun 30, 2006 at 04:13:12PM +0100, Alan Bullock wrote: hi all, I have the following schema: CREATE TABLE auctions ( id serial NOT NULL, user_id int4, title varchar(255), body varchar(255), CONSTRAINT auctions_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE bids ( id serial NOT NULL, auction_id int4, user_id int4, amount float8, created_at timestamp, CONSTRAINT bids_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE users ( id serial NOT NULL, username varchar(255), CONSTRAINT users_pkey PRIMARY KEY (id) ) WITHOUT OIDS; I'd like to return all the bids for a given auction, but limit it to only the *latest* bid from each user. so regardless of how many bids a user has placed, only their latest is returned. I dont have a clue where to even start with this and would appreciate some pointers thanks alan ---(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 -- 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
[GENERAL] stored procedures
Hi,i'm migrating some SP from MySQL to PostgreSQL 8.1.xI would like to know if it is possible to return aresult of a select request and also a simple interger...thanks a lot,Alain
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 paul rivers wrote: Out of curiosity, which big, expensive enterprise database are you spoiled by? Many that I support do not allow DDL within an transaction, or if they allow it, there are many caveats and rules. Oracle Rdb. Built by DEC back in the early 1980s. It's had tablespaces (Storage Areas in Rdb parlance) since the late 80s. Tables (including the system catalog, which itself is a set of tables) all go in a Storage Area. If you don't specify one, it does in the default: RDB$SYSTEM. Creating a table is no more than inserting records into a few system tables, and allocating a few pages in the relevant Storage Area. Thus, rolling back most all DDL is built deep into the engine. http://www.oracle.com/technology/products/rdb/index.html - -- 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 iD8DBQFEpVAZS9HxQb37XmcRArUOAKDFNtLVqr9BeYi7k6nhp/GnVI7M6QCfV7hJ wNUUCx2sGUmRklxtwu6hoUA= =CLgH -END PGP SIGNATURE- ---(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] pgsql vs mysql
Hi, I am new to pgsqlI really appreciate the licensing terms of pgsql...mysql licensing is a little bit risky to use... But, I have one question... Q. when using pgsql as a very heavy and mission critical applications, what are the advantages of using pgsql instead of mysql? thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgsql vs mysql
On 30 Jun 2006 08:58:27 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I am new to pgsqlI really appreciate the licensing terms of pgsql...mysql licensing is a little bit risky to use... Q. when using pgsql as a very heavy and mission critical applications, what are the advantages of using pgsql instead of mysql? ok, this is pretty much a faq and you can get lots of information about this from the archives and from the internet itself. However, there is tons of disinformation about there so be careful Here are the major points you need to consider, based on my experience: * mysql performance advantage is greatly overstated, although postgresql requires you to use certain conventions (example: prepared statements) to get comparable performance * both databases (IMO) are very stable. in 6 years of workikng with both databases, I've never had either 'just crash' without external mitigating circumstances, a testimonial to both projects * mysql tends to encourage development in application code, while pg tends to encourage development in the database iteself. For various reasons, I greatly prefer the latter. * pg, in my opinion, has a better unicode handling, although there is a small learning curve to do it the best way * pg mvcc transactional engine is better than innodb (IMO), and faster when used properly * pg pl/pgsql is much better than (mysql 5.0) stored procedures. you also have a lot of other languages to use if you want * pg is generally much more flexible and extensible * mysql has decent out of the box replication that is easy to set up (one day I hope pg get hot PITR which is analagous feature) * pg has IMO much better shell and standardized syntax * pg query planer rivals top commercial databse engines * mysql has a few features here and there which are nice...just to name a few, flush tables with lock, multiple insert, etc there are lots of other things. if you want to use a mission critcal database engine for new project, I believe there to be only one choice, but you have to take a lot of things into consideration; support, development style, and a host of other factors. If you follow the pg lists I think you will find the support here to be unbelievably good. Merlin ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, Jun 30, 2006 at 04:22:28PM +0100, Dave Page wrote: * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. I'm not sure that many people necessarily realise it, but you can also drive Slony directly from pgAdmin 1.4+ if slonik scripts give you a headache. 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? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: On Fri, 2006-06-30 at 08:17, Jason McManus wrote: * Replication support still rudimentary. It might be worth pointing out that mysql's replication falls over if you so much as look at it crosseyed. I have not had to use it for production purposes, but I can tell you that the mysql replication regression tests fail ... irreproducibly of course ... almost one time in two in Red Hat's build environment. Are those tests, or at least descriptions of them, available? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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] limit results to one row per foreign object
Alan Bullock [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] hi all, I have the following schema: thanks all for such prompt replies! I'm going with Martijn's solution, I'm a total newbie and it appears simplest (though I realise it's pgsql only) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql vs mysql
Merlin Moncure wrote: * mysql performance advantage is greatly overstated, although postgresql requires you to use certain conventions (example: prepared statements) to get comparable performance * both databases (IMO) are very stable. in 6 years of workikng with both databases, I've never had either 'just crash' without external mitigating circumstances, a testimonial to both projects * mysql tends to encourage development in application code, while pg tends to encourage development in the database iteself. For various reasons, I greatly prefer the latter. * pg, in my opinion, has a better unicode handling, although there is a small learning curve to do it the best way * pg mvcc transactional engine is better than innodb (IMO), and faster when used properly * pg pl/pgsql is much better than (mysql 5.0) stored procedures. you also have a lot of other languages to use if you want * pg is generally much more flexible and extensible * mysql has decent out of the box replication that is easy to set up (one day I hope pg get hot PITR which is analagous feature) * pg has IMO much better shell and standardized syntax * pg query planer rivals top commercial databse engines * mysql has a few features here and there which are nice...just to name a few, flush tables with lock, multiple insert, etc This is a very good list of differences. I'm going to elaborate slightly on this one: pg is generally much more flexible and extensible. Mysql in general implements features as an afterthought. This causes it to be a bit less flexible than postgres. While it does contain what 99% of people want to use (probably because they only use mysql), it has huge gaps in it's feature set. Here is a great example of what I'm talking about: Mysql does not allow you to use now() as the default value of a column. From their docs: The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”. So they work around this major shortcoming by giving people the CURRENT_TIMESTAMP constant for the timestamp column so at least the people wanting an automatic timestamps are happy. Basically they add just enough support to do the most common thing. Postgres, allows all of the functions in a create table statement. This is legit in postgres: create table test_tab (data varchar(20), timestamp timestamp default now() - interval '32.56 minutes'); HTH, schu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
David Fetter [EMAIL PROTECTED] writes: On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote: It might be worth pointing out that mysql's replication falls over if you so much as look at it crosseyed. I have not had to use it for production purposes, but I can tell you that the mysql replication regression tests fail ... irreproducibly of course ... almost one time in two in Red Hat's build environment. Are those tests, or at least descriptions of them, available? Sure, it's just the standard make test sequence in mysql's source. If you want to do exactly what I'm talking about, grab the latest mysql SRPM off the Fedora download server and rpmbuild --rebuild it. There's a very long regression test suite (much larger than ours :-() and when it fails, it's invariably in one of the replication-related tests. regards, tom lane ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, Jun 30, 2006 at 01:41:53PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote: It might be worth pointing out that mysql's replication falls over if you so much as look at it crosseyed. I have not had to use it for production purposes, but I can tell you that the mysql replication regression tests fail ... irreproducibly of course ... almost one time in two in Red Hat's build environment. Are those tests, or at least descriptions of them, available? Sure, it's just the standard make test sequence in mysql's source. Uh oh. I'm a little worried about writing tests based on GPLed code for Slony-I or other replication systems. Might these need to be clean-roomed? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] User privileges in web database applications
Both connection pooling and using the superuser with SET SESSION AUTHORIZATION both have their uses. You might have an application that processes some type of transaction and inserts data into a users schema or table, but where there are no user credentials available. Then you might have a web interface for users to access that data where user credentials are available. We have this type of setup and we use a superuser with SET SESSION AUTHORIZATION for the incoming transactions.But we also have extensive security requirements that demand we do things most people don't do. Full security/code audits every quarter, peer review and full testing for any new code, hardware encryption for sensitive data and keys stored on tokens, client certificate authentication for all web access, restrictive firewall, etc.. Bottom line is that I'm paranoid about using SET SESSION AUTHORIZATION, but it does have it's uses and can be used safely. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL and OpenVZ
Hi, I use this approach both for development and backup servers (with PITR). Everything runs very smoothly. You should, of course, keep an eye on /proc/user_beancounters and diskquota to ensure that the engines have enough shared memory, network io (both sockets and buffer, tcp and other) and disk space. Stock values are too low. That__s not a real problem, though, because you can adjust all the values while running the server, but be careful to adjust shared memory before going in production, otherwise you might have to restart the database engine. For production I use several medium loaded OpenVZ machines with apache webservers, but one dedicated physical machine for the database engine, keeping one database for each webserver. I did not use one database engine per server, because I think, that it is more effective to use all its physical memory for one engine instead of dividing it into parts for several engines. This is an unproven theory of mine, I did not have enough time to evaluate it. In principle I found no problems other than giving the OpenVZ server enough ressources. Though I did not do any speed comparisons native vs. OpenVZ, I could do some benchmarking next week, if you need some values. For the PITR OpenVZ PostgreSQL backup server I even copy the WALs etc. using the base system into /vz/private/... while the OpenVZ database server is down. As soon as I fire up the OpenVZ database server, it uses the copied stuff while starting up. Because the OpenVZ server starts with the same IP like the main database server, there is no need to change anything else while switching from main server to backup server. Regards, Frank. On Fri, 30 Jun 2006 18:37:27 +0400 Ivan Zolotukhin [EMAIL PROTECTED] thought long, then sat down and wrote: Hello, Does anybody have experience in running PostgreSQL inside OpenVZ (http://openvz.org/) or any other virtual private servers solutions? I'm interested in both cases of running single PostgreSQL server and multiple PostgreSQLs on one physical machine under relatively high (though not IO-limited) load. Any caveats or limitations of this approach? Any known penalties or problems with this tandem? Best regards, Ivan Zolotukhin ---(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 -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606Mail: [EMAIL PROTECTED] Telefax: 0271 231 8608Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
David Fetter [EMAIL PROTECTED] writes: On Fri, Jun 30, 2006 at 01:41:53PM -0400, Tom Lane wrote: Sure, it's just the standard make test sequence in mysql's source. Uh oh. I'm a little worried about writing tests based on GPLed code for Slony-I or other replication systems. Might these need to be clean-roomed? Oh, is that what you wanted 'em for? Probably. My recollection from the few that I've really looked at is that they're pretty mysql-specific anyway (eg, some of them are actual regression tests to catch reappearance of old mysql bugs). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] join on next row
On 6/25/06, Sim Zacks [EMAIL PROTECTED] wrote: Merlin, Thank you for your input. My original question did specifically mention that the events had to be on the same day. I need to have a query that gives per employee each event and the event after it if it happened _on the same day_. whoop! :) oh well. heh ---(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 vs mysql
Mysql does not allow you to use now() as the default value of a column. From their docs: The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.1.1, TIMESTAMP Properties as of MySQL 4.1. So they work around this major shortcoming by giving people the CURRENT_TIMESTAMP constant for the timestamp column so at least the people wanting an automatic timestamps are happy. Basically they add just enough support to do the most common thing. Postgres, allows all of the functions in a create table statement. This is legit in postgres: create table test_tab (data varchar(20), timestamp timestamp default now() - interval '32.56 minutes'); right mysql implementation of defaults is a complete disaster, a big contributing factor as to why mysql tends to favor application code. In contrast, pg follows the principle of least suprise, becuase the internal structures are open and highly generic. the syntax is standardized and regular (as much as with possible somehow) wherever possible with carefully thought out exceptions. mysql syntax, otoh, is hacky and full of special cases. each basic sql command is highly nuanced and full of legacy workarounds to limitations of previous versions of mysql. some of the specific commands (insert, especially) are quite powerful but the whole adds up to less than the sum of its parts somehow. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
[EMAIL PROTECTED] (Ron Johnson) writes: Scott Marlowe wrote: [snip] However, the more interesting thing here, is that every statement, including DDL is transactable, except for a couple of big odd ones, like create database. So, in postgresql, you can do: begin; create table xyz... alter table abc... insert into abc select * from iii update iii...; drop table iii; (oops, I messed up something) rollback; But isn't that what it means to be transactional? Or am I spoiled by my big, expensive enterprise database? DDL commonly hasn't been able to be rolled back, even in big, expensive databases... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://www.ntlug.org/~cbbrowne/unix.html Rules of the Evil Overlord #180. If I ever build a device to transfer the hero's energy into me, I will make sure it cannot operate in reverse. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
[EMAIL PROTECTED] (Scott Marlowe) writes: I agree with Tom, nice notes. I noted a few minor issues that seem to derive from a familiarity with MySQL. I'll put my corrections below... On Fri, 2006-06-30 at 08:17, Jason McManus wrote: On Converting from MySQL 5.0.x to PostgreSQL 8.1.x -- Major differences I have noted: --- MySQL 5.0.x: * Easy, built-in and extensive replication support. Not sure how extensive it is. It's basically synchronous single master single slave, right? It is quite easy though. And it's statement-based, is it not? Indications are that MySQL replication is quite non-deterministic, as a result; if you use SYSDATE() in INSERT/UPDATE queries to set timestamps, replicas will get the wrong time. It looks like anything that is dynamically evaluated will be processed incorrectly on replicas, such as timezones. It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, left to the will of the query optimizer. It's multi-slave, mind you... * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. Keep in mind, slony can be applied to a living database while it's running, and can run between different major versions of postgresql. That's a pretty advanced feature. Plus, if the replication daemons die (kill -9ed or whatever) you can restart replication and slony will come right back where it was and catch up. And you can trust that the data that is replicated will actually be faithfully replicated, even in the presence of timestamps, triggers, and other things that challenge determinism... * Pg uses a 'template1' pseudo-database that can be tailored to provide default objects for new database creation, if you should desire. It obviously also offers a 'template0' database that is read-only and offers a barebones database, more equivalent to the empty db created with mysql's CREATE DATABASE statement. This isn't quite right. template0 is a locked and pure copy of the template database. It's there for break glass in case of emergency use. :) template1, when you first initdb, is exactly the same as template0, but you can connect to it, and alter it. Both of these are real postgresql databases. template1 is the database that gets copied by default when you do create database. Note that you can also define a different template database when running create database, which lets you easily clone any database on your machine. create database newdb with template olddb In the last few weeks, we've had fun using createdb --template= to create test copies of production databases (well, replicas thereof...). Creating a replica via Slony-I takes several hours, for large databases, as it has to load data into tables, then generate indexes. We've used createdb on such databases; the longest it took to set up an extra duplicate was something like 8 minutes, and that gave our sysadmins full copies of the production databases that could be used for testing... The speed was *stunning*... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://cbbrowne.com/info/multiplexor.html How much more helpful could I be than to provide you with the appropriate e-mail address? I could engrave it on a clue-by-four and deliver it to you in Chicago, I suppose. -- Seen on Slashdot... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, 2006-06-30 at 16:34, Chris Browne wrote: [EMAIL PROTECTED] (Scott Marlowe) writes: I agree with Tom, nice notes. I noted a few minor issues that seem to derive from a familiarity with MySQL. I'll put my corrections below... On Fri, 2006-06-30 at 08:17, Jason McManus wrote: On Converting from MySQL 5.0.x to PostgreSQL 8.1.x -- Major differences I have noted: --- MySQL 5.0.x: * Easy, built-in and extensive replication support. Not sure how extensive it is. It's basically synchronous single master single slave, right? It is quite easy though. And it's statement-based, is it not? Indications are that MySQL replication is quite non-deterministic, as a result; if you use SYSDATE() in INSERT/UPDATE queries to set timestamps, replicas will get the wrong time. It looks like anything that is dynamically evaluated will be processed incorrectly on replicas, such as timezones. It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, left to the will of the query optimizer. This is essentially correct. Note that I can use pgpool with postgresql and get about the same behaviour as mysql's replication, with the same basic draw backs, that it's best to copy the database between shutdown machines, and things that are dynamically evaluated can cause issues. With pgpool I get synchronous replication with automatic failover, and it's dead simple to build and install. Which kind of shows off the difference in philosophy between the two development camps. The postgresql folks are very very picky about what gets put into the main package, and let's face it, pgpool, while neat, is not really ready for integration into the backend. Meanwhile, a nearly identical replication system IS integrated into the backend of MySQL, warts and all for the sake of convenience of the users, and possibly marketing. It's not that one way is so much better than the other, it's just indicative of how the two camps operate. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is it possible to disable insert/update/delete triggers for one transaction and not another?
On Wed, Jun 28, 2006 at 11:59:36AM -0700, Karen Hill wrote: I have an insert/update/delete trigger on all my tables which add data to a log table. I would like to be able to disable them when the tables are called from one stored proceedure I have. Yet I would still like those triggers to fire on any other operation that is happening concurrently. Is this even possible? Best bet would be to have the procedure only execute as a given user (probably via security definer) and detect that in the trigger. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, Jun 30, 2006 at 11:01:19AM -0700, David Fetter wrote: On Fri, Jun 30, 2006 at 01:41:53PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote: It might be worth pointing out that mysql's replication falls over if you so much as look at it crosseyed. I have not had to use it for production purposes, but I can tell you that the mysql replication regression tests fail ... irreproducibly of course ... almost one time in two in Red Hat's build environment. Are those tests, or at least descriptions of them, available? Sure, it's just the standard make test sequence in mysql's source. Uh oh. I'm a little worried about writing tests based on GPLed code for Slony-I or other replication systems. Might these need to be clean-roomed? Is there actually a lack of ideas for our regression tests, or a lack of people/motivation to work on them? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, Jun 30, 2006 at 11:39:04AM -0400, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: On Fri, 2006-06-30 at 08:17, Jason McManus wrote: * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. Keep in mind, slony can be applied to a living database while it's running, and can run between different major versions of postgresql. That's a pretty advanced feature. Plus, if the replication daemons die (kill -9ed or whatever) you can restart replication and slony will come right back where it was and catch up. It might be worth pointing out that mysql's replication falls over if you so much as look at it crosseyed. I have not had to use it for production purposes, but I can tell you that the mysql replication regression tests fail ... irreproducibly of course ... almost one time in two in Red Hat's build environment. I've been able to trace a few of these failures to quirks of the build environment, like trying to build x86 and x86_64 at the same time in different chroots of the same machine (must take care not to use same TCP port numbers for tests), but it still seems flaky as hell. I attended a talk about MySQL and High Availability once and was pretty unimpressed. Lots of 'now you take the database down and copy files around' and the like. Nothing remotely close to the abilities of Slony. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
On Fri, Jun 30, 2006 at 05:16:46PM -0500, Scott Marlowe wrote: This is essentially correct. Note that I can use pgpool with postgresql and get about the same behaviour as mysql's replication, with the same basic draw backs, that it's best to copy the database between shutdown machines, and things that are dynamically evaluated can cause issues. With pgpool I get synchronous replication with automatic failover, and it's dead simple to build and install. Maybe we should be promoting pgpool's replication in that light. If you're looking something that works like MySQL's replication, use pgpool. If you want a much more sophisticated and complex mechanism, use Slony. Which kind of shows off the difference in philosophy between the two development camps. The postgresql folks are very very picky about what gets put into the main package, and let's face it, pgpool, while neat, is not really ready for integration into the backend. Meanwhile, a nearly identical replication system IS integrated into the backend of MySQL, warts and all for the sake of convenience of the users, and possibly marketing. It's not that one way is so much better than the other, it's just indicative of how the two camps operate. I'd say google:'mysql gotchas' is a pretty good indicator of that. ;) MySQL tries desperatly hard to make databases 'easy', but the reality is that unless it's a pretty trivial embedded database, databases (both RDBMSes and database design and use) aren't easy; they're probably one of the most complex pieces of IT in commmon use today. IMO, in trying to 'make it simple', a lot of people end up burned. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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 vs mysql
On Fri, Jun 30, 2006 at 01:07:32PM -0400, Merlin Moncure wrote: * mysql has decent out of the box replication that is easy to set up (one day I hope pg get hot PITR which is analagous feature) Actually, PITR is in no way analagous. Try replicating something like 'INSERT INTO table SELECT random();' on MySQL and note how all the data is different. pgpool replication is equivalent to MySQL's replication. Or if you want more sophisticated replication, use Slony. BTW, anyone curious about the differences should take a look at google:'mysql gotchas' (there's also a PostgreSQL section on that site). See also the MySQL/PostgreSQL thread that was on this list yesterday. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Notes on converting from MySQL 5.0.x to PostgreSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Chris Browne wrote: [EMAIL PROTECTED] (Ron Johnson) writes: Scott Marlowe wrote: [snip] However, the more interesting thing here, is that every statement, including DDL is transactable, except for a couple of big odd ones, like create database. So, in postgresql, you can do: begin; create table xyz... alter table abc... insert into abc select * from iii update iii...; drop table iii; (oops, I messed up something) rollback; But isn't that what it means to be transactional? Or am I spoiled by my big, expensive enterprise database? DDL commonly hasn't been able to be rolled back, even in big, expensive databases... I guess I'm just fortunate... - -- 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 iD8DBQFEpd6BS9HxQb37XmcRAgu8AKCqp6KxNYoa0tIcmbglG8XXSzgXpQCgjDLv vYkFNzwXF1K+b9ZNK6Svr64= =mW9L -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
Jim C. Nasby [EMAIL PROTECTED] writes: Is there actually a lack of ideas for our regression tests, or a lack of people/motivation to work on them? Certainly there are plenty of ideas in the archives ... but writing regression tests is so *boring* :-(. This is definitely a weak spot for a mostly-volunteer project --- it's hard to get anyone to do that kind of work. Something that would actually hold some intellectual interest is to improve the testing infrastructure. The current setup is pretty limited as to its ability to deal with varying outputs, and even more limited in its ability to test concurrent behavior. Again, see the archives. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] stored procedure which return a select result
Hi,I have some problems with a stored procedure.In this SP, i run several SELECT statements.1st one is to confirm that profile/account really exist into DB.if it's ok, the 2nd statement (SELECT) is executed and should return records. these records should be the result of my SP.i try the RETURNS setof record as ...but it seems not so great...at least i have problems with it...I've search in several books, but i did not find a real example which could help me. Could you give me some piece of code for such use ?Or, if this technique is not used, could you tell me what is used for returning a SELECT result as result of SP ?thanks a lotAlain
