Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
More generally, it's worth making the point that a lot of MySQL's brand new in 5.0 features have been in Postgres for a *long* time, and are therefore likely to be both more stable and better-performing than MySQL's first cut at them. Some specific things could be: Their initial support for triggers ;) Also technically we do have updateable views via rules. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
I'm not sure what XA (distributed transactions) is - is that something that can be achieved with Slony? CSN --- Joshua D. Drake [EMAIL PROTECTED] wrote: On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Well IF they are being completely honest, we don't have XA and we don't have an instance manager but of course who really needs one? Sincerely, Joshua D. Drake Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] How to uninstall Postgres
On 10/6/05, Ly Lam Ngoc Bich [EMAIL PROTECTED] wrote: I have installed PostgresSQL with postgresql-8.0.3.tar.gz . My computer's OS is Linux Fedora 3 . Please show me to the way to uninstall PostgresSQL. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq hi, have you customized your installation as instructed in the postgresql manual page? http://www.postgresql.org/docs/8.0/interactive/install-procedure.html any way you have goto your source directory where you have started your installation and give the command # make clean # make dist clean and remove the directory. It should work in most of the cases. regards suresh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
No. Distributed transactions can cooperate in two phase commit. I think someone has done some two phase commit work already. IIRC. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of CSN Sent: Wednesday, October 05, 2005 11:11 PM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0? I'm not sure what XA (distributed transactions) is - is that something that can be achieved with Slony? CSN --- Joshua D. Drake [EMAIL PROTECTED] wrote: On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Well IF they are being completely honest, we don't have XA and we don't have an instance manager but of course who really needs one? Sincerely, Joshua D. Drake Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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 6: explain analyze is your friend
Re: [GENERAL] Cast to integer
A. Kretschmer wrote:
am 05.10.2005, um 15:08:33 +0200 mailte Robert Osowiecki folgendes:
Hello!
Anyone could explain to me, why cast(3.33 to int) works (from float, I
suppose) but cast('3.33' to int) (from text) does not? And what if I
create a cast for that: is it possibly dangerous?
test=# select '3.33'::float::int;
int4
--
3
(1 row)
Let me explain better. I encountered this problem when I tried to write
general unit-calculation (from kilograms to punds, for example) function
operating on any arithmetic datatype: with float and numeric it went all
ok, but failed with integer on return $result with error invalid
input syntax for integer. My function internally operated on numeric
type. I wrote a wrap-around for integer, because I've been afraid, that
changing text to integer cast (or input function, whatever it is)
could damage something important.
R.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Securing Postgres
On Wed, Oct 05, 2005 at 06:19:39PM -0700, Uwe C. Schroeder wrote: If any of my customers would ask me if they should buy a system where they can't access THEIR data in any other way than using the software that comes with the deal I'd tell them to back off. Most customers on the planet are not interested in your software - they make money from THEIR DATA. This is the bit that's been bugging me this whole thread. Who owns the data? I've had to help people out with programs where they could type data in but couldn't get the reports they wanted out. Furtunatly, Access's access control is, uh, simplistic and I created the reports they needed. If someone tried to sell me a system where I couldn't even get in table format the raw info I had entered, I'd tell them to go away. Like you say, the data is way more important that whatever program you're using. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpA7fdGhZjIO.pgp Description: PGP signature
[GENERAL] Dump all except some tables?
Is it possible to dump an entire database but to skip one or two tables? Or, conversely, to restore an entire dump except for one or two tables? (Although I'd prefer the first version.) The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround. Any idea, other than messing around with the dump file? I don't look forward to grepping a dump which is several tens of megabytes gzipped... I'm considering doing a dump with --table for each table except the one or two in question. But I wonder, if I simply concatenate the resulting SQL dumps, will I get a valid dump? There are all kinds of foreign key contraints in place, and if the table data is not fed back in the right order it's useless.
[GENERAL] Fedora Core 3 x86_64 compat libraries?
I have an AMD64 Fedora Core 3 server with Postgresql 8.0.1 that I want to upgrade to the latest version. Since 8.0.2 incremented the libpq version I can't install the official RPM packages. I found a message[1] to this list which had a compat-postgresql-libs[2] RPM, but that's an i686 package. Where can I find a x86_64 version or source for this package? Is that package compatible with 8.0.4? [1] http://archives.postgresql.org/pgsql-general/2005-05/msg01240.php [2] http://developer.postgresql.org/~devrim/compat-postgresql-libs-3-2PGDG.i686.rpm ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Dump all except some tables?
am 06.10.2005, um 13:59:44 +0300 mailte WireSpot folgendes: Is it possible to dump an entire database but to skip one or two tables? Or, conversely, to restore an entire dump except for one or two tables? (Although I'd prefer the first version.) The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround. You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 I'm considering doing a dump with --table for each table except the one or two in question. But I wonder, if I simply concatenate the resulting SQL dumps, will I get a valid dump? There are all kinds of foreign key I'm not sure, perhaps if you dump with --data-only every table. contraints in place, and if the table data is not fed back in the right order it's useless. Right. You can do a dump from all tables and after restore all the tables you can delete the one or two tables. Other way: pg_restore with '--use-list=list-file'. You can create a list of contents of the archive and edit this list. (pg_restore --list) Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dump all except some tables?
WireSpot wrote: Is it possible to dump an entire database but to skip one or two tables? Or, conversely, to restore an entire dump except for one or two tables? (Although I'd prefer the first version.) The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround. Any idea, other than messing around with the dump file? I don't look forward to grepping a dump which is several tens of megabytes gzipped... I'm considering doing a dump with --table for each table except the one or two in question. But I wonder, if I simply concatenate the resulting SQL dumps, will I get a valid dump? There are all kinds of foreign key contraints in place, and if the table data is not fed back in the right order it's useless. I don't think you can limit the dump output precisely as you ask, but you can get the equivalent by doing a custom format dump, then use pg_restore to produce a archive listing, which you then edit so as to select specific objects you want to include/exclude, and then run pg_restore against that edited list file. ---(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] Securing Postgres
On Thu, Oct 06, 2005 at 11:57:32AM +0200, Martijn van Oosterhout wrote: This is the bit that's been bugging me this whole thread. Who owns the data? I've had to help people out with programs where they could type data in but couldn't get the reports they wanted out. Furtunatly, Access's access control is, uh, simplistic and I created the reports they needed. If someone tried to sell me a system where I couldn't even get in table format the raw info I had entered, I'd tell them to go away. Like you say, the data is way more important that whatever program you're using. It is not that easy. I.e. most(if not all) world manufacturers of SDH or DWDM technologies know that the data is very important. They know that at certain time customers will want to have an access to *their* raw data, so they all have (mostly limited) interfaces to their EMSes. But they have to be purchased for incredible ammounts of money. In such cases TelCos cannot say: go away regards, Bohdan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dump all except some tables?
On 10/6/05, A. Kretschmer [EMAIL PROTECTED] wrote: am 06.10.2005, um 13:59:44 +0300 mailte WireSpot folgendes: Is it possible to dump an entire database but to skip one or two tables? Or, conversely, to restore an entire dump except for one or two tables? (Although I'd prefer the first version.) The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround. You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 Yes, pg_dump doesn't complain. But it only takes the first one into consideration. I only get one table in the dump. I'm considering doing a dump with --table for each table except the one or two in question. But I wonder, if I simply concatenate the resulting SQL dumps, will I get a valid dump? There are all kinds of foreign key I'm not sure, perhaps if you dump with --data-only every table. contraints in place, and if the table data is not fed back in the right order it's useless. Right. You can do a dump from all tables and after restore all the tables you can delete the one or two tables. That's exactly what I'm trying to avoid. If possible, I wanted to avoid having to dump them at all. We're talking a lot of logging data that is of no use where the dump is going. Other way: pg_restore with '--use-list=list-file'. You can create a list of contents of the archive and edit this list. (pg_restore --list) Interesting, I'll look into it. If mangling the restore list works, it would solve the problem halfway (at the restoring moment). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dump all except some tables?
am 06.10.2005, um 15:29:50 +0300 mailte WireSpot folgendes: The only related option for both pg_dump and pg_restore is --table, which only takes 1 (one) table name. If only it accepted more than one I could've found a workaround. You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 Yes, pg_dump doesn't complain. But it only takes the first one into consideration. I only get one table in the dump. Oops, sorry. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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 8.1 vs. MySQL 5.0?
[EMAIL PROTECTED] (CSN) writes: I'm not sure what XA (distributed transactions) is - is that something that can be achieved with Slony? No. XA is an interface to allow having updates take place across multiple databases. That would mean that you do some updates on one DB, others on another, and finally issue a distributed COMMIT which commits it all at once. That's not similar to what Slony-I does... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://cbbrowne.com/info/oses.html Have you noticed that, when we were young, we were told that `everybody else is doing it' was a really stupid reason to do something, but now it's the standard reason for picking a particular software package? -- Barry Gehm ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to uninstall Postgres
On Oct 6, 2005, at 1:14 AM, suresh ramasamy wrote: On 10/6/05, Ly Lam Ngoc Bich [EMAIL PROTECTED] wrote: I have installed PostgresSQL with postgresql-8.0.3.tar.gz . My computer's OS is Linux Fedora 3 . Please show me to the way to uninstall PostgresSQL. hi, have you customized your installation as instructed in the postgresql manual page? http://www.postgresql.org/docs/8.0/interactive/install-procedure.html any way you have goto your source directory where you have started your installation and give the command # make clean # make dist clean and remove the directory. It should work in most of the cases. regards suresh There's actually a make uninstall rule, too. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
They have collation and multiple characterset per table and etc. which actually is from 4.1 (not new in 5.0), and postgresql have only one collation per database cluster :-( Otherwise I think their features are all there, but cannot be used togather most of them (you can have foreign key, but not using fulltext ...) CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] text file import
Hi list, I am trying to transfer the following from MySQL to PostgreSQL: load data local infile 'D:/tmp/InterAcces- MySQL/03102005/bedrijven.txt' into table bedrijven fields terminated by ',' optionally enclosed by '^' lines terminated by ';\r\n'; Is there a way to do this without changing the file (this file is delivered by a 3rd party)? Btw, I am on the win32 platform. Here is an example from the file: 6603,^J.J. De Graaf^,^Achthovenerweg^,^1A^,^2351 AX^,^Leiderdorp^,^Revisievergunning (art. 8.4 Wm) (nieuw, de gehele inrichting omvattend)^,^2^,^50^,^1993-12-14^,^60^,^De Heer J.J. De Graaf^,^3^,^Veehouderijen (Niet Intensief)^,^3^,^96856^,^461861^,^^,^^,^2003-06-04^,^2005-09-28^,^16:22:00^,^^; 6605,^Neuteboom Tweewielers^,^Touwbaan^,^1E^,^2352 CZ^,^Leiderdorp^,^Meldingsplichtig ^,^1^,^50^,^1998-10-07^,^60^,^H. Neuteboom^,^1^,^Verkoop/Reparatie (Brom)Fietsen^,^10^,^95811^,^464378^,^M01^,^Detailhandel- En Ambachtsbedrijven^,^2004-01-06^,^2005-09-28^,^16:22:00^,^^; Thanks in advance. Best regards, Bart Bart van den Eijnden Syncera IT Solutions Postbus 270 2600 AG DELFT tel.nr.: 015-7512436 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How to uninstall Postgres on Iinux.
I am using Linux Fedora 3 . I've installed Postgres with postgresql-8.0.3.tar.gz package , so there is no rpm package when I check with rpm -qa|grep postgresql Please show me the way to uninstall PostgresSQL. Sincerly yours, ---(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] Dump all except some tables?
WireSpot [EMAIL PROTECTED] writes: On 10/6/05, A. Kretschmer [EMAIL PROTECTED] wrote: You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 Yes, pg_dump doesn't complain. But it only takes the first one into consideration. I only get one table in the dump. Right. That's on the TODO list but not done yet :-(. Berend gave you the right advice: dump the whole database using pg_dump -Fc, then use pg_restore's features for selective restore. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
They have collation and multiple characterset per table and etc. which actually is from 4.1 (not new in 5.0), and postgresql have only one collation per database cluster :-( Otherwise I think their features are all there, but cannot be used togather most of them (you can have foreign key, but not using fulltext ...) I heard that MySQL has tons of problems with its multibyte support (for example SELECT does not return correct data). I don't know if MySQL AB has fixed the problem or not though. -- SRA OSS, Inc. Japan Tatsuo Ishii ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to uninstall Postgres on Iinux.
Ly Lam Ngoc Bich [EMAIL PROTECTED] writes: I am using Linux Fedora 3 . I've installed Postgres with postgresql-8.0.3.tar.gz package , so there is no rpm package when I check with rpm -qa|grep postgresql Please show me the way to uninstall PostgresSQL. You should be able to go into the source directory where you compiled PG and do 'make uninstall'. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg_restore --disable-triggers does not stop triggers
Hi! 8.0.1 and 8.1 beta. Triggers are still fired although option --disable-triggers is applied to pg_restore. The fired triggers abort pg_restore because of the foreign keys violations. The following restore script used to be working but it suddently doesn't. I don't remember I ever changed this script since it had worked. #Backup command: #PGCLIENTENCODING=UNICODE pg_dump -Fc db1 db1 # #Restore commands: pg_restore -l db1 list createdb -E UNICODE db1 pg_restore -F c -L list -v -d db1 -s db1 log-schema 21 pg_restore -F c -L list -v -d db1 -a --disable-triggers db1 log-data 21 Any idea will be much appreciated. Regards, CN -- http://www.fastmail.fm - Email service worth paying for. Try it for free ---(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] [INTERFACES] [ODBC] Unbound text box, Text 255 characters, MSAccess/PostgreSQL
Hello, Greg and thanks for suggestions, but it didn't work with append query. I just couldn't pass more than 255 characters long text as parameter of DAO query. But, fortunately, I solved the problem by using AddNew method of DAO recordset to append new row. In this case I could pass directly the whole value of Me.TextBoxName. It seems that in this way Access can pass the whole text (as Memo Type) to new row of recordset. Otherwise if I want to pass the same value of the unbound text box by using an append query, it can pass just 255 characters long text. Fortunately, it works with recordset object... Thanks, Zlatko - Original Message - From: Greg Campbell [EMAIL PROTECTED] To: Zlatko Matić [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Tuesday, October 04, 2005 3:34 PM Subject: Re: [INTERFACES] [ODBC] Unbound text box, Text 255 characters, MSAccess/PostgreSQL What version of Access? Confirm that Access is interpreting the target field as MEMO, (either look at the linked table in design mode, or use Tools-Analyze-Documenter). For the query, determine the type of the parameter - Query menu-Parameters. Be sure you are using type MEMO. By the way, 3271 is a Jet error -- Invalid property value. Good luck. Zlatko Matić wrote: Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a DAO Append Query that has a parameter that is passed from the text box using parameter of DAO QueryDef object. I adjusted B7 parameter (Text as LongVarchar) in connection string (ODBC driver) to 1, so that Access links PostgreSQL Text fields as Memo fields in Access. The intention was to be able to enter unlimited length commentary into the field. But, although I can enter unlimited text by using bound text box in some other forms, in this particular form I can't use bound control, so I need to pass text from UNBOUND control to the linked table by using DAO or ADO code. It seems that DAO query can't accept Memo as parameter, but only text. If my text exceeds length of 255, I have an VBA error 3271. If text in unbound text box is shorter that 255 everything is OK. So, is there any way to pass text of length 255 from unbound text box to Memo field of linked PostgreSQL table, by using DAO or ADO ? Thanks in advance, Zlatko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Indexen on 8.0.3
Hi, After upgrading to 8.0.3 I see very poor performance on several indexes. Like this: (udps is a view on main) palga= explain analyze select rapnaam from udps where naamvrouw like 'vos%'; QUERY PLAN Index Scan using nv on main (cost=0.00..242.65 rows=60 width=14) (actual time=6.475..11598.502 rows=5692 loops=1) Index Cond: (((naamvrouw)::text = 'vos'::character varying) AND ((naamvrouw)::text 'vot'::character varying)) Filter: ((naamvrouw)::text ~~ 'vos%'::text) Total runtime: 11606.250 ms (4 rows) The index was created with: CREATE INDEX nv ON main USING btree (naamvrouw); Database was recently analyzed. Clearly, something has to be tuned that didn't need tuning on 7.4.3 ? (Main table has about 1.7 million records). Any suggestions welcome. Cheers, Han Holl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_restore --disable-triggers does not stop triggers
am 06.10.2005, um 22:33:52 +0800 mailte CN folgendes: Hi! 8.0.1 and 8.1 beta. Triggers are still fired although option --disable-triggers is applied to pg_restore. The fired triggers abort pg_restore because of the foreign keys violations. The following restore script used to be working but it suddently doesn't. I don't remember I ever changed this script since it had worked. #Backup command: #PGCLIENTENCODING=UNICODE pg_dump -Fc db1 db1 # #Restore commands: pg_restore -l db1 list createdb -E UNICODE db1 pg_restore -F c -L list -v -d db1 -s db1 log-schema 21 pg_restore -F c -L list -v -d db1 -a --disable-triggers db1 log-data You are DB-Superuser? Presently, the commands emitted for --disable-triggers must be done as superuser. So, you should also specify a supe- ruser name with -S, or preferably run pg_restore as a Post- greSQL superuser. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Wed, 2005-10-05 at 23:41, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Well IF they are being completely honest, we don't have XA and we don't have an instance manager but of course who really needs one? We don't have XA built into the backend, but if I've been following the jdbc list accurately, there's fairly complete XA support for the jdbc driver, which should be available in the 8.1 release. More generally, it's worth making the point that a lot of MySQL's brand new in 5.0 features have been in Postgres for a *long* time, and are therefore likely to be both more stable and better-performing than MySQL's first cut at them. (BTW, it sure seems like MySQL 5.0 has been a heckuva long time in getting to release status. Has anyone here been following that process? Why's it been so painful?) I've been beta testing 5.0.xx releases and reporting bugs. They're pretty fast at fixing individual bugs. Not sure why it's taken so long, really. Maybe they were trying to do too much at once in one release? But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] text file import
Bart van den Eijnden wrote: Hi list, I am trying to transfer the following from MySQL to PostgreSQL: load data local infile 'D:/tmp/InterAcces- MySQL/03102005/bedrijven.txt' into table bedrijven fields terminated by ',' optionally enclosed by '^' lines terminated by ';\r\n'; Is there a way to do this without changing the file (this file is delivered by a 3rd party)? Btw, I am on the win32 platform. Normally, I'd pipe it through a Perl filter into a COPY FROM STDIN command. Not sure what you'll have installed on Windows. Oh - and you might need to correct some of the data if MySQL isn't being strict enough. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Wed, 2005-10-05 at 20:37, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). Bit type: Postgresql supports binary string already. Cursors: PostgreSQL does everything up updatable cursors (unless this got added recently) MySQL's cursors are only available in a procedure or function, and can't be scolled. Information Schema: MySQL's support of this looks fairly extensive. Instance Manager: Uniquely MySQL. It allows things like starting and stopping the database remotely. Fixed point arithmetic: PostgreSQL has had good behaviour for arbitrarily long numeric math for quite some time. Archive Storage Engine: PostgreSQL does the same thing, on the fly, with no add on engine, and no limitations like this one has. I.e. you have fill transactions, and can use more than select and insert on your text types, which are automagically toasted if over a certain size. Federated Storage Engine: Allows MySQL to access tables in other servers like they are here. No real direct equivalent in PostgreSQL, but dblink provides similar functionality. Stored Routines: PostgreSQL's user defined functions have done the same thing as stored routines for quite some time now. And in many brightly colored languages. Strict Mode and Error handling: Not an option, but always on in PostgreSQL. There are still plenty of things that fall through the cracks on MySQL, like my previously mentioned problem with column level constraints (specificall fk but all column level constraints are ignored, no error, no warning, no notice.) Jeez, how hard would it be to just throw a danged notice? Triggers: PostgreSQL has been there, done that, and has a large collection of TShirts. Each with a name of a different language it can use to create triggers / user defined functions. varchar data type extended to 64k. PostgreSQL has a limit of 1 Meg on varchar (if you use a limit) and can make a text type of ~ 1 gig. Views: Similar functionality, but PostgreSQL has updatable views by the DBA writing simple rules that allow it. This means that for simple updatable views, MySQL wins for ease of use, and for complex updatable views, PostgreSQL wins because you can still do them, you just get to do it yourself. XA Transactions: MySQL's are pretty primitive, and PostgreSQL's XA may not be much further ahead there. XA transactions need some form of management for partial transactions. MySQL's answer here was to just refuse to commit on any member if any other member failed to be prepared for commit. This is possibly the least useful implementation of XA there could be, as the primary reason I've seen for it is to allow an application to have n db servers, and to kick one out if it starts misbehaving and run on the remaining n-1 servers. Note that right now, PostgreSQL's XA has, as far as I know, no real conflict management. But I'm guessing PostgreSQL will have a better fleshed out XA interface before MySQL. ---(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] text file import
On Thu, 2005-06-10 at 16:14 +0100, Richard Huxton wrote: Bart van den Eijnden wrote: Hi list, I am trying to transfer the following from MySQL to PostgreSQL: load data local infile 'D:/tmp/InterAcces- MySQL/03102005/bedrijven.txt' into table bedrijven fields terminated by ',' optionally enclosed by '^' lines terminated by ';\r\n'; Is there a way to do this without changing the file (this file is delivered by a 3rd party)? Btw, I am on the win32 platform. Normally, I'd pipe it through a Perl filter into a COPY FROM STDIN command. Not sure what you'll have installed on Windows. Oh - and you might need to correct some of the data if MySQL isn't being strict enough. I would have to second Richard on the last statement. MySQL formats many data types in ways that may not be possible to directly import into PostgreSQL, and has a bad habit of using non- standard representations for NULL; such as '00-00-' for date, and other similar invalid or wrong data values for NULL in other types. Good Luck ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a bug, now(), default CURRENT_TIME, WAL Logs.
Hi Folks, Well I think i had 'now' in there in someplaces and that got parsed. I've fixed it in all databases on my system now, so doing forward I'm safe. However there is a 2 week period in which the dates for some items are not what they should be. Of course they should be now() whenever now was at the time, but they are all 2005-09-21 21:09:59.180154-04 I was wondering if there was a way I could use the WAL logs to find what date certain oid's were inserted. Then query my table where date = 2005-09-21 21:09:59.180154-04, corelate the time in teh WAL log and update my table.. But I've no idea how to iteract with the wal log. Thanks again folks. Could you have done something like: CREATE TABLE ts_def_test2(a int4, b timestamp with time zone default 'now'); Which gives: CREATE TABLE ts_def_test2 ( a integer, b timestamp with time zone DEFAULT '2005-10-05 00:08:05.381034+01'::timestamp with time zone ); The difference being that 'now' is a literal timestamp (with timezone) that gets evaluated in your CREATE TABLE statement. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Indexen on 8.0.3
[EMAIL PROTECTED] writes: After upgrading to 8.0.3 I see very poor performance on several indexes. ... Database was recently analyzed. Clearly, something has to be tuned that didn't need tuning on 7.4.3 ? (Main table has about 1.7 million records). No, there's no reason for 8.0 to be slower at this than 7.4, if all else is equal. I'm betting that all else is not equal. Maybe you are using a different encoding or locale in the new installation than the old? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Wed, Oct 05, 2005 at 10:50:47PM -0700, Joshua D. Drake wrote: More generally, it's worth making the point that a lot of MySQL's brand new in 5.0 features have been in Postgres for a *long* time, and are therefore likely to be both more stable and better-performing than MySQL's first cut at them. Some specific things could be: Their initial support for triggers ;) Also technically we do have updateable views via rules. Actually, is that even a 'technically'? If memory serves, both Oracle and DB2 have ways to handle updates on views that are not automatically updateable. What we're missing are *automatically* updateable views. -- 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] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote: But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. Are you aware of the MySQL Gotchas website (just google it)? Any time you see MySQL being stupid about something you should probably check there first to see if it's a feature. -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Indexen on 8.0.3
On Thursday 06 October 2005 18:20, Tom Lane wrote: No, there's no reason for 8.0 to be slower at this than 7.4, if all else is equal. I'm betting that all else is not equal. Maybe you are using a different encoding or locale in the new installation than the old? Well, I suspect that something is not equal as well. The trouble is I can't seem to find it. We're going to replay what happened on a different machine, and hopefully will find something. Thanks, Han Holl ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Now this is rather useful in my opinion. I will be passing it on to some of my collegues. Aly. On Thu, 6 Oct 2005, Jim C. Nasby wrote: On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote: But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. Are you aware of the MySQL Gotchas website (just google it)? Any time you see MySQL being stupid about something you should probably check there first to see if it's a feature. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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] PostgreSQL Gotchas
http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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] PostgreSQL Gotchas
Compared to MySQL ditching referential integrity because of a typo, I would consider these 'gotchas' extremely minor, hence the reason I use Postgresql not MySQL. Postgresql does what you expect from an RDBMS system out of the box in 99.99% of cases. I don't have to toggle things on special like, or install additional modules, or check every time I create a foreign key that the system will actualy enforce it. In short Postgresql makes less work for a DBA than MySQL when you actualy care about your data. It's cleaner, faster and easier to deal with in all cases of real world database applications. I'm not even gonna talk about the query planner in MySQL trying to deal with an eight way join, let's just say that if you plan on using anything even near third normal, MySQL will dog. Oh - yeah - and the fact that it doesn't scale... If those 'gotchas' are all one has against Postgresql... I don't know why people are still even using Oracle or DB2 ;) MySQL you say, I thought we were talking about RDBMSes here. MySQL is to linux, what Jet is to Windows IMHO, oh wait - Jet has foreign keys by default... AlexOn 10/6/05, Aly S.P Dharshi [EMAIL PROTECTED] wrote: http://sql-info.de/postgresql/postgres-gotchas.htmlAny comments from folks on the list ?Cheers,Aly.--Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dressthat's short enough to be interestingand long enough to cover the subject ---(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] PostgreSQL Gotchas
This sure sounds like a flamewar bait email? On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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 Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). PostgreSQL does not run in Windows 98 There is a LOT of customers running Windows 98 . So I must switch to a Firebird, am I right ? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, 2005-10-06 at 12:23, Jim C. Nasby wrote: On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote: But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. Are you aware of the MySQL Gotchas website (just google it)? Any time you see MySQL being stupid about something you should probably check there first to see if it's a feature. Oh yeah, very aware. What's amazed me is how often I find something that's majorly wrong that isn't in that list. For instance, this particular problem isn't on the gotcha page, although lots of other constraint issues are. Sadly, after talking to the author of the innodb table handler, I get the feeling this one isn't going to change. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Gotchas
No flamewar here, I am just trying to see if opinions of others on this, as Jim had posted a MySQL one, and that there was a PostgreSQL one, so I wanted to see if these are valid, if they aren't then that site should be updated to reflect this. Cheers, Aly. On Thu, 6 Oct 2005, Gavin M. Roy wrote: This sure sounds like a flamewar bait email? On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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 Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). PostgreSQL does not run in Windows 98 There is a LOT of customers running Windows 98 . So I must switch to a Firebird, am I right ? You can run PostgreSQL on Cygwin on Win98, I think. But ifyou're running your database server on win98, you obviously don't care much about your data :) (PostgreSQL *client* tools and drivers run fine on Windows 98, btw) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, 2005-10-06 at 21:40 +0300, Andrus wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). PostgreSQL does not run in Windows 98 There is a LOT of customers running Windows 98 . So I must switch to a Firebird, am I right ? Over MySQL, yes. However since not even Microsoft supports Windows 98 anymore, it is better to update them. Sincerely, Joshua D. Drake Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.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 Gotchas
They're all valid, but most apply to versions 8.0 or 7.4 even, and the others are pretty esoteric issues that you don't see often. The missing from clause thing is likely to be the biggest surprise most folks run into. I find the supposed bad performance of aggregates is bunk. On my workstation (not even a server, just my personal workstation with all kinds of other crap running on it) I get a response time of about 1 second for a count(*) in version 7.4 of postgresql: select count(*) from locators; count - 100 (1 row) takes anywhere from .75 to 1.0 second. The same dataset, on oracle takes 0.75 seconds on a very fast oracle server, and anywhere from 5 to 20 seconds on a slower server, like one equivalent to my workstation. On Thu, 2005-10-06 at 14:01, Aly S.P Dharshi wrote: No flamewar here, I am just trying to see if opinions of others on this, as Jim had posted a MySQL one, and that there was a PostgreSQL one, so I wanted to see if these are valid, if they aren't then that site should be updated to reflect this. Cheers, Aly. On Thu, 6 Oct 2005, Gavin M. Roy wrote: This sure sounds like a flamewar bait email? On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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 Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
Then wouldn't it be more appropriate to ask: What's the status of the things listed on the PostgreSQL gotchas. Are they bugs? Are they valid? Have the been resolved? What does the community thing of those gotchas? I personally don't take an open ended question like URL: Any comments? as trying to be productive. But I could also just be burnt out on the whole PgSQL vs MySQL flame war or reading too much into what I appraised to be terse email. If so I apologize. Regards, Gavin On Oct 6, 2005, at 12:01 PM, Aly S.P Dharshi wrote: No flamewar here, I am just trying to see if opinions of others on this, as Jim had posted a MySQL one, and that there was a PostgreSQL one, so I wanted to see if these are valid, if they aren't then that site should be updated to reflect this. Cheers, Aly. On Thu, 6 Oct 2005, Gavin M. Roy wrote: This sure sounds like a flamewar bait email? On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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 Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
Okay that is a fair statement to make, hence restating: - What is the status of those items listed on the PostgreSQL gotchas - Are they bugs ? - Are they valid statements ? - If they are bugs are they resolved ? - What does the PG community thing of this list of gotchas ? http://sql-info.de/postgresql/postgres-gotchas.html On Thu, 6 Oct 2005, Gavin M. Roy wrote: Then wouldn't it be more appropriate to ask: What's the status of the things listed on the PostgreSQL gotchas. Are they bugs? Are they valid? Have the been resolved? What does the community thing of those gotchas? I personally don't take an open ended question like URL: Any comments? as trying to be productive. But I could also just be burnt out on the whole PgSQL vs MySQL flame war or reading too much into what I appraised to be terse email. If so I apologize. Regards, Gavin -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Andrus wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). PostgreSQL does not run in Windows 98 There is a LOT of customers running Windows 98 . So I must switch to a Firebird, am I right ? We run on Windoews 98 using Cygwin, I think. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 12:07:12PM -0600, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? The implicit from and mildly non-standard case folding are the only two that approach being real issues people may stumble over, IMO. If you look at the others that may be problematic in real use they're mostly for archaic versions of PG. The others that are still valid with 8.0 are... not so much problems with PG as differences in the way PG works that users should be aware of. Cheers, Steve ---(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 Gotchas
Don't think so. The author sounds like a PostgreSQL proponent to me. It also sounds like most of the issues have been addressed with recent builds. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Gavin M. Roy Sent: Thursday, October 06, 2005 11:40 AM To: Aly S.P Dharshi Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Gotchas This sure sounds like a flamewar bait email? On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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 Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
Sorry. AlexOn 10/6/05, Gavin M. Roy [EMAIL PROTECTED] wrote: This sure sounds like a flamewar bait email?On Oct 6, 2005, at 11:07 AM, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Cheers, Aly. -- Aly S.P Dharshi [EMAIL PROTECTED] A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject ---(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 Gavin M. Roy800 Pound Gorilla[EMAIL PROTECTED]---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Gotchas
On 10/6/05, Aly S.P Dharshi aly ( dot ) dharshi ( at ) telus ( dot ) net wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? - It's a lot shorter than MySQL's gotchas list. - 8 of the 13 are for versions of PostgreSQL = 8.1 - Of the remaining, I consider select as to be really trivial (and it appears a work-around can be hacked). - lowercase folding. I DO sometimes wish I could use fieldID, etc. without quoting it. - I've never found count(*) to be slow. - I don't know enough about the UNICODE means UTF-8 and RANDOM() failures to comment. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Support for windows 98 was infact extended to June 2006: http://support.microsoft.com/gp/lifean1 AlexOn 10/6/05, Joshua D. Drake [EMAIL PROTECTED] wrote: On Thu, 2005-10-06 at 21:40 +0300, Andrus wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). PostgreSQL does not run in Windows 98 There is a LOT of customers running Windows 98 . So I must switch to a Firebird, am I right ? Over MySQL, yes. However since not even Microsoft supports Windows 98anymore, it is better to update them.Sincerely,Joshua D. Drake Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives?http://archives.postgresql.org--Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240PostgreSQL Replication, Consulting, Custom Programming, 24x7 supportManaged Services, Shared and Dedicated HostingCo-Authors: plPHP, plPerlNG - http://www.commandprompt.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 8.1 vs. MySQL 5.0?
--- Scott Marlowe [EMAIL PROTECTED] wrote: Federated Storage Engine: Allows MySQL to access tables in other servers like they are here. No real direct equivalent in PostgreSQL, but dblink provides similar functionality. Would that be possible with table partitions? Or Slony? CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fw: [GENERAL] Connecting form Access or VB6 to PostgreSQL 8
Hello ! you have to configure : PostgreSQL 8 conf files on the server, Install psqlodbc on the client, and create an ODBC data system source for your data. In Access you have to link the PG tables. Luc - Original Message - From: Aman Tur To: pgsql-general@postgresql.org Sent: Monday, October 03, 2005 3:18 PM Subject: [GENERAL] Connecting form Access or VB6 to PostgreSQL 8 I have installed PostgreSQL 8 and latest pgODBC and ADO drivers but still I am not able to connect to it from access or VB 6. Kindly help me. Aman Tur
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote: But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. Are you aware of the MySQL Gotchas website (just google it)? Any time you see MySQL being stupid about something you should probably check there first to see if it's a feature. http://sql-info.de/mysql/gotchas.html Of course, one should probably also look at the PostgreSQL Gotchas page (same guy) just to be fair :-) http://sql-info.de/postgresql/postgres-gotchas.html Now whether or not those are still valid or not I have no idea... -philip ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dump all except some tables?
On Thu, Oct 06, 2005 at 09:51:22AM -0400, Tom Lane wrote: WireSpot [EMAIL PROTECTED] writes: On 10/6/05, A. Kretschmer [EMAIL PROTECTED] wrote: You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 Yes, pg_dump doesn't complain. But it only takes the first one into consideration. I only get one table in the dump. Right. That's on the TODO list but not done yet :-(. There have been several patches against pg_dump to do both multiple-table inclusion and multiple-table exclusion. These may get into 8.2, as they missed 8.1. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
--- Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, 2005-10-05 at 23:41, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Well IF they are being completely honest, we don't have XA and we don't have an instance manager but of course who really needs one? We don't have XA built into the backend, but if I've been following the jdbc list accurately, there's fairly complete XA support for the jdbc driver, which should be available in the 8.1 release. More generally, it's worth making the point that a lot of MySQL's brand new in 5.0 features have been in Postgres for a *long* time, and are therefore likely to be both more stable and better-performing than MySQL's first cut at them. (BTW, it sure seems like MySQL 5.0 has been a heckuva long time in getting to release status. Has anyone here been following that process? Why's it been so painful?) I've been beta testing 5.0.xx releases and reporting bugs. They're pretty fast at fixing individual bugs. Not sure why it's taken so long, really. Maybe they were trying to do too much at once in one release? But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. What's the difference between a fk at the table level vs. column level? The only fk's I've used are one column referencing another. CSN __ Yahoo! for Good Donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 12:07:12PM -0600, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? Several. First, it looks to me like this is a pretty transparent attempt to troll, so I'm not going to go there. Second, if you actually go through that list, you find: * Problems fixed in the last version or three of PostgreSQL. This is an incentive to upgrade, not a gotcha. * Problems that will be fixed in the next version of PostgreSQL. This means that problems get on developers' radar and get fixed. I suppose by some extremely un-generous method of assessment, this could be a gotcha. * Incomplete information, e.g. not mentioning Slony-I as an upgrade path. * One deviation from the standard that won't be changed: fold-to-lower. * Nits so minor as not to be worth addressing (non-optional AS in FROM clauses vs. SQL standard's making that AS optional there) Third, there are real gotchas, but those are generally reported as bugs on pgsql-bugs and acted upon as such. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 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] License question[VASCL:A1077160A86]
On Wed, Oct 05, 2005 at 05:34:25PM -0500, Jim C. Nasby wrote: Yes, because libpg.so is licensed under the BSD license. Note that you can do this in a COPYRIGHT file. It just has to be in all copies, whatever that means. AFAIK, this would only apply if he was actually distributing libpq.so, which would be a bad thing for technical reasons anyway. Well, yes, except I suppose I sort of thought it was going to be linked statically or something -- how do you rely on your users having the library installed? But now that I think about it, I suppose this is really a question prompted by TheirDB's decision to understand derivative program in a mighty extended way. I seem to be echoing Emily a lot these days. Never mind. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] License question
On Oct 4, 2005, at 4:38 PM, Aaron Smith wrote: I never imagined that I would get so many responses. Thanks for all the great information! depending on the nature of your DB you may wish to investigate SQLite as well. it is designed to be embedded into apps, not run as a separate server, which us better suited to some uses. SQLite is public domain, meaning there is not even a copyright holder, so you can literally do whatever you want with it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
--- CSN [EMAIL PROTECTED] wrote: - 8 of the 13 are for versions of PostgreSQL = 8.1 Doh! - 8 of the 13 are for versions of PostgreSQL 8.1! __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
On Thu, 2005-06-10 at 12:07 -0600, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? SELECT column alias, ...: this is a known issue. AFAIK it is not easy to solve. Unquoted object names fold to lower case: this is intentional, both because the developers prefer this behavior and because it is consistent with the behavior of prior PostgreSQL versions. Implicit FROM item and unintended cross joins: fixed in 8.1, as the gotcha notes. COUNT(*) very slow: this is a known issue -- see the -hackers archives for many prior discussions. MVCC makes this hard to solve effectively (whether applications should actually be using COUNT(*) on large tables with no WHERE clause is another matter...) -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
CSN wrote: - I don't know enough about the UNICODE means UTF-8 and RANDOM() failures to comment. I'm hardly an expert, but I've done enough with unicode to know that you can easily convert utf-8 to any other flavor of unicode you might want to use. Though, why you'd want to use something other than utf-8 in the first place I don't understand. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
* Aly S.P Dharshi ([EMAIL PROTECTED]) wrote: - What is the status of those items listed on the PostgreSQL gotchas - Are they bugs ? - Are they valid statements ? - If they are bugs are they resolved ? - What does the PG community thing of this list of gotchas ? http://sql-info.de/postgresql/postgres-gotchas.html There's only 6 things on there which apply to the current version of PostgreSQL, and one of those will be fixed in 8.1. The remaining items are: SELECT column alias,... This issue looks like it has probably been hashed over a few times and at the end of the day the answer is Best to use AS for clarity anyway, and it is documented to be necessary, and Postgres upstream has decided to require it instead of making alot of things reserved which wouldn't otherwise need to be. Unquoted object names fold to lower case I don't really see this as too much of an issue, personally, but I do know some people have run into it. The example they give seems a bit off tho, as I thought Oracle just folded to upper-case (in which case table != table in Oracle, but table = table in Postgres, kind of depends on how you look at it). I'm not sure the spec says one way or another. I've had problems with this when people were building things against a MySQL database w/o proper quoting and assuming it was case-sensitive when under Postgres it wasn't. RANDOM() failures This looks like an interesting problem, and might be a bug to be fixed. I think it depends on the what the SQL specification says for this case, if it says anything at all, as to if it should be considered a bug or not. On the surface it certainly does look like an oddity though. COUNT(*) very slow As someone else has pointed out, it's only slow if you've got a large dataset. There's certainly workarounds for this issue (generally involving a couple of functions for keeping track of the number of rows). In this way, Postgres actually gives the user the flexibility to choose the performance loss on insert/deletes which is necessary to track the number of rows seperately, or not to. UNICODE means UTF-8 This is an interesting point. To be perfectly honest, it seems like Postgres' UNICODE/UTF-8/etc support could stand to be somewhat better. I've not used it much myself but I do see comments about it on the lists from time to time. I think there was some work done on this for 8.1 and I expect support will only continue to improve in these areas. Just my 2c. Thanks, Stephen signature.asc Description: Digital signature
[GENERAL] MS Access / Postgres ODBC / Outer joins
We're having a problem with Access, Postgres, and outer joins. I'm
hoping this will ring a bell with someone and there'll be an easy answer.
Everything seems to work OK with inner joins and everything else we've
tried, but when switching to an outer join, Access screws the SQL all
up. I'm almost positive at this point that Access is the culprit, as
apposed to the ODBC driver.
Given this SQL statement typed into the Access query builder...
SELECT t1.field1, t2.field2 FROM t1 LEFT OUTER JOIN t2 on t1.field1 =
t2.field2 WHERE t1.field3 = 'some value';
Access will rewrite it to this...
SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on t1.field1
= t2.field2 WHERE t1.field3 = 'some value' };
Note the {oj after FROM, and the closing } at the end of the
query. What the heck is that all about? Has anybody seen this before?
Is there an SQL server that might actually recognize that?
(I didn't think version information would be too important for this, but
I can gather all that info if it's really needed...)
TIA
Glen Parker
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 12:35:38PM -0700, CSN wrote: Scott Marlowe [EMAIL PROTECTED] wrote: But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. What's the difference between a fk at the table level vs. column level? The only fk's I've used are one column referencing another. He means the way the foreign key constraint is defined. In MySQL, defining the constraint as part of column definition has no effect: CREATE TABLE bar ( fooid integer NOT NULL REFERENCES foo (id) ) TYPE innodb; The database accepts the above without warning but won't enforce the foreign key constraint. One must write this instead: CREATE TABLE bar ( fooid integer NOT NULL, FOREIGN KEY (fooid) REFERENCES foo (id) ) TYPE innodb; Also, notice the TYPE innodb clause of the CREATE TABLE statement. The default table type in MySQL is MyISAM, which doesn't support foreign key contraints at all, but which will silently allow you to declare them. If you haven't changed the default table type, then you must remember to specify that you want an InnoDB table, or else your REFERENCES clauses are nothing but documentation. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] License question
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Vivek Khera
Sent: Thursday, October 06, 2005 1:55 PM
To: Postgres General
Subject: Re: [GENERAL] License question
On Oct 4, 2005, at 4:38 PM, Aaron Smith wrote:
I never imagined that I would get so many responses. Thanks for all
the great information!
depending on the nature of your DB you may wish to investigate SQLite
as well. it is designed to be embedded into apps, not run as a
separate server, which us better suited to some uses.
SQLite is public domain, meaning there is not even a copyright
holder, so you can literally do whatever you want with it.
SQLite is also modeled after {a subset of} the PostgreSQL grammar. So
when you want to step up from a little baby database, you can step right
into PostgreSQL with minimal muss and fuss.
SQLite Grammar page:
http://www.sqlite.org/lang.html
---(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] Fedora Core 3 x86_64 compat libraries?
Hi again, On Thu, 6 Oct 2005, Jan Söderback wrote: Is that package compatible with 8.0.4? I forgot to reply this. Yes, but please do not download 8.0.4 RPMs now. New sets of 8.0.4 RPMs will be released this weekend (marked as 2PGDG). The current ones are a bit buggy and will be replaced with new ones. Regards, -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org ---(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] Fedora Core 3 x86_64 compat libraries?
hi, On Thu, 6 Oct 2005, Jan Söderback wrote: I have an AMD64 Fedora Core 3 server with Postgresql 8.0.1 that I want to upgrade to the latest version. Since 8.0.2 incremented the libpq version I can't install the official RPM packages. I found a message[1] to this list which had a compat-postgresql-libs[2] RPM, but that's an i686 package. Where can I find a x86_64 version or source for this package? Is that package compatible with 8.0.4? [1] http://archives.postgresql.org/pgsql-general/2005-05/msg01240.php [2] http://developer.postgresql.org/~devrim/compat-postgresql-libs-3-2PGDG.i686.rpm http://developer.postgresql.org/~devrim/rpms/compat/compat-postgresql-libs-3-3PGDG.src.rpm is the SRPM. Also I've just built the x86_64 RPM for you: http://developer.postgresql.org/~devrim/rpms/compat/compat-postgresql-libs-3-3PGDG.x86_64.rpm Regards, -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org ---(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 Gotchas
Stephen Frost wrote: UNICODE means UTF-8 This is an interesting point. To be perfectly honest, it seems like Postgres' UNICODE/UTF-8/etc support could stand to be somewhat better. I've not used it much myself but I do see comments about it on the lists from time to time. I think there was some work done on this for 8.1 and I expect support will only continue to improve in these areas. Yes, this is completely fixed in 8.1, except we allow UNICODE to be a synonym for UTF-8. We also added Win32 support for UTF8 in 8.1. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: 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] MS Access / Postgres ODBC / Outer joins
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Glen Parker
Sent: Thursday, October 06, 2005 2:41 PM
To: Postgres General
Subject: [GENERAL] MS Access / Postgres ODBC / Outer joins
We're having a problem with Access, Postgres, and outer joins. I'm
hoping this will ring a bell with someone and there'll be an easy
answer.
Everything seems to work OK with inner joins and everything else we've
tried, but when switching to an outer join, Access screws the SQL all
up. I'm almost positive at this point that Access is the culprit, as
apposed to the ODBC driver.
Given this SQL statement typed into the Access query builder...
SELECT t1.field1, t2.field2 FROM t1 LEFT OUTER JOIN t2 on t1.field1 =
t2.field2 WHERE t1.field3 = 'some value';
Access will rewrite it to this...
SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on
t1.field1
= t2.field2 WHERE t1.field3 = 'some value' };
Note the {oj after FROM, and the closing } at the end of the
query. What the heck is that all about? Has anybody seen this
before?
Is there an SQL server that might actually recognize that?
That's just ODBC syntax. The semicolon is not part of the statement,
but is some terminator for the tool that reads SQL commands, I guess.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht
m/odbcouter_join_escape_sequence.asp
(I didn't think version information would be too important for this,
but
I can gather all that info if it's really needed...)
Some more information about what is actually going wrong with the
queries and what tool set you are using would probably be good to
explain so that you can get the best possible help.
---(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] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, 2005-10-06 at 12:40 -0700, CSN wrote: --- Scott Marlowe [EMAIL PROTECTED] wrote: Federated Storage Engine: Allows MySQL to access tables in other servers like they are here. No real direct equivalent in PostgreSQL, but dblink provides similar functionality. Would that be possible with table partitions? Or Slony? No. This is a actual cross database kind of thing. That is why you need dblink. Table partitioning is same type of data multiple tables. Slony like Mammoth Replicator doesn't give you this either as we just mirror (replicate) the data. Sincerely, Joshua D. Drake CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 10:30:26AM -0500, Scott Marlowe wrote: Information Schema: MySQL's support of this looks fairly extensive. But PostgreSQL's is pretty good, too, last I looked. Instance Manager: Uniquely MySQL. It allows things like starting and stopping the database remotely. What does Instance Manager buy you that ssh doesn't? (For bonus points, what does ssh get you that Instance Manager doesn't? Hint: I have a Symbian UIQ phone. Google for PuTTY.) XA Transactions: MySQL's are pretty primitive, and PostgreSQL's XA may not be much further ahead there. XA transactions need some form of management for partial transactions. MySQL's answer here was to just refuse to commit on any member if any other member failed to be prepared for commit. This is possibly the least useful implementation of XA there could be, as the primary reason I've seen for it is to allow an application to have n db servers, and to kick one out if it starts misbehaving and run on the remaining n-1 servers. Note that right now, PostgreSQL's XA has, as far as I know, no real conflict management. But I'm guessing PostgreSQL will have a better fleshed out XA interface before MySQL. Well, to be fair, one of the Open Group's XA targets is actual distributed data sets, and not just reliability through redundancy. So MySQL's implementation appears to be enough to support the former in some ways. What seems more troublesome to me is that if a machine fails after the PREPARE step succeds, and then the client disconnects, the transaction is automatically rolled back and can't be recovered. I haven't figured out yet whether this is merely dodgy, or an outright violation of the spec. A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 01:19:33PM -0600, Aly S.P Dharshi wrote: Okay that is a fair statement to make, hence restating: - What is the status of those items listed on the PostgreSQL gotchas Some of them are for sure gotchas. For users of tranditionally-non-toy (as opposed to recently-non-toy) systems, I'd think that there are three serious issues: the column alias (because it strictly violates the SQL definition), the folding to lower case (same reason), and the UNICODE==UTF-8 issue (because it violates the UNICODE definition). - Are they valid statements ? - If they are bugs are they resolved ? Some of them are valid and resolved (the site actually notes as much in many cases). One thing I do note is that there's a workaround for the have-to-dump issue: you can use Slony to upgrade (by design). It won't work for everyone, but it's a big step up in many cases. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 12:40:49PM -0700, CSN wrote: --- Scott Marlowe [EMAIL PROTECTED] wrote: Federated Storage Engine: Allows MySQL to access tables in other servers like they are here. No real direct equivalent in PostgreSQL, but dblink provides similar functionality. Would that be possible with table partitions? Or Slony? Slony would give you a loose approximation. Table partitioning is unrelated. Better yet, I don't know of any reason why you can't define a view using dblink that would duplicate the features of a federated system. Of course it would be easier if it was in the back-end... -- 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 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 04:18:03PM -0400, Stephen Frost wrote: COUNT(*) very slow As someone else has pointed out, it's only slow if you've got a large dataset. There's certainly workarounds for this issue (generally involving a couple of functions for keeping track of the number of rows). In this way, Postgres actually gives the user the flexibility to choose the performance loss on insert/deletes which is necessary to track the number of rows seperately, or not to. Actually, I think the real issue here is that unlike every other database I've used, PostgreSQL can't do covering index scans, where only the index is read and not the base table. This is due to not having MVCC visibility info in the index. But there has been discussion on -hackers about a way to get close to this behavior; something along the lines of setting a bit in the index once a tuple is visible to all running transactions. This would be a win on larger indexes that don't have a lot of insert/update/delete activity. -- 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] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 12:29:51PM -0700, David Fetter wrote: * Problems that will be fixed in the next version of PostgreSQL. This means that problems get on developers' radar and get fixed. I suppose by some extremely un-generous method of assessment, this could be a gotcha. * Incomplete information, e.g. not mentioning Slony-I as an upgrade path. From the few times I've emailed the author he seems happy to accept better text. I'm sure he'd be happy to mention slony as an upgrade option (though it would be best if there was a good doc describing this we could point to). Likewise I believe he'd note things that will be fixed in a given version (such as 8.1 or 8.2). * One deviation from the standard that won't be changed: fold-to-lower. Does the standard even specify which case you fold to? I agree, this is only a gotcha if you're used to the very non-standard behavior in MySQL. * Nits so minor as not to be worth addressing (non-optional AS in FROM clauses vs. SQL standard's making that AS optional there) *shrug* I think it's useful to be able to see why something you might expect to work doesn't. -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 01:46:29PM -0500, Scott Marlowe wrote: On Thu, 2005-10-06 at 12:23, Jim C. Nasby wrote: On Thu, Oct 06, 2005 at 10:10:14AM -0500, Scott Marlowe wrote: But what really bugs me is that some things that ARE bugs simply aren't getting fixed and probably won't. Specifically, while mysql understands fk references made at a table level, it simply ignores, without error, warning, or notice, fk references made in a column. arg... Very frustrating. If they just didn't support that syntax it would be much less bothersome, since I'd try it, get an error, and try the other syntax. Instead, I spent an afternoon trying to figure out why it wasn't doing ANYTHING when I declared an FK reference at column level. Things like that are, sadly, kinda rampant in MySQL. Are you aware of the MySQL Gotchas website (just google it)? Any time you see MySQL being stupid about something you should probably check there first to see if it's a feature. Oh yeah, very aware. What's amazed me is how often I find something that's majorly wrong that isn't in that list. For instance, this particular problem isn't on the gotcha page, although lots of other constraint issues are. Sadly, after talking to the author of the innodb table handler, I get the feeling this one isn't going to change. Please submit any missing items to the author. If he refuses them send them to me and I'll start an addendum. -- 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] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 02:30:53PM -0400, Alex Turner wrote: MySQL is to linux, what Jet is to Windows IMHO, oh wait - Jet has foreign keys by default... MySQL is the WindowsME of databases - first hit searching for MySQL on CafePress. :) -- 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] Dump all except some tables?
On Thu, Oct 06, 2005 at 12:32:26PM -0700, David Fetter wrote: On Thu, Oct 06, 2005 at 09:51:22AM -0400, Tom Lane wrote: WireSpot [EMAIL PROTECTED] writes: On 10/6/05, A. Kretschmer [EMAIL PROTECTED] wrote: You can use the -t more than once. pg_dump -U foobar database -t foo -t foo1 Yes, pg_dump doesn't complain. But it only takes the first one into consideration. I only get one table in the dump. Right. That's on the TODO list but not done yet :-(. There have been several patches against pg_dump to do both multiple-table inclusion and multiple-table exclusion. These may get into 8.2, as they missed 8.1. Looking at the archived discussion from the TODO (http://momjian.postgresql.org/cgi-bin/pgtodo?pg_dump), I find myself wondering if it would be good to allow for specifying a set of rules for what to dump in a file, probably something like a set of regexes with a way to specify if it's an include or exclude rule. Seems like it would be a heck of a lot simpler to do that for complex cases than deal with a pile of spaghetti on the command-line, but I've never really worried about it. -- 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] MS Access / Postgres ODBC / Outer joins
Glen Parker [EMAIL PROTECTED] writes:
Access will rewrite it to this...
SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on t1.field1
= t2.field2 WHERE t1.field3 = 'some value' };
Note the {oj after FROM, and the closing } at the end of the
query. What the heck is that all about? Has anybody seen this before?
Yeah, I think this is actually in the ODBC standard: the ODBC driver is
supposed to recognize that and convert it to whatever outer join syntax
the database likes. Not sure if our ODBC driver does so --- you'd be
better off asking about this on pgsql-odbc.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Gotchas
Neil Conway wrote: COUNT(*) very slow: this is a known issue -- see the -hackers archives for many prior discussions. MVCC makes this hard to solve effectively (whether applications should actually be using COUNT(*) on large tables with no WHERE clause is another matter...) -Neil And it's not like a count(*) on an Oracle database of any decently-sized dataset is blazing fast, or even in blazing's ballpark. The only thing I could see actually being an issue is the random() one and add missing from. The rest are trivial. The random() thing is interesting, esoteric, and probably has never been a problem in a real situation. (Or has exactly once, when he wrote that gotcha) Jeff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Hi everyone, I've just got back from LinuxWorld in London and seeing this thread thought I would share my experience of the MySQL stand - if you are of a delicate dispostion, please look away now. I basically asked them straight up why I should use MySQL instead of PostgreSQL and was quite surprised by the result, mainly since it was not done on features but more on FUD. The basic message was this: - MySQL is the most popular open source database, with over 6m enterprise installs, with a large company supporting it. PostgreSQL is run by a very small community of developers. - MySQL can be clustered (This was later retracted when I mentioned I needed something that would work on large tables, as apparently their clustering only works in RAM and so will fail on large queries and queries that use a lot of joins). - All the companies that have tried to operate by selling PostgreSQL support services have gone bankrupt, except for EnterpriseDB. - PostgreSQL doesn't have row level locking. And this last comment really took the biscuit - I really hope that the none of the core team read this and decide to throw in the towel: MySQL has the biggest collection of database experts... Open source people don't know how to write databases So all in all, to say I was upset by some of these comments was an understatement. To all the people I spoke to on the PostgreSQL stand, I hope I did it in a way that made them feel empowered to go and try the PostgreSQL for their own applications by mentioning its benefits, and not by spreading FUD about its competition. Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 05:29:14PM -0500, Jim C. Nasby wrote: On Thu, Oct 06, 2005 at 12:29:51PM -0700, David Fetter wrote: * Problems that will be fixed in the next version of PostgreSQL. This means that problems get on developers' radar and get fixed. I suppose by some extremely un-generous method of assessment, this could be a gotcha. * Incomplete information, e.g. not mentioning Slony-I as an upgrade path. From the few times I've emailed the author he seems happy to accept better text. I'm sure he'd be happy to mention slony as an upgrade option (though it would be best if there was a good doc describing this we could point to). Likewise I believe he'd note things that will be fixed in a given version (such as 8.1 or 8.2). I'll draft something up :) * One deviation from the standard that won't be changed: fold-to-lower. Does the standard even specify which case you fold to? It specifies fold-to-upper, and that's just wrong. :P I agree, this is only a gotcha if you're used to the very non-standard behavior in MySQL. * Nits so minor as not to be worth addressing (non-optional AS in FROM clauses vs. SQL standard's making that AS optional there) *shrug* I think it's useful to be able to see why something you might expect to work doesn't. I suppose it's good to have those. There are other things I think of as bugs, and would like to have addressed internally before I send them off to gotchas land. PG's incomplete support for DOMAINs, for example, or the not-quite-it handling of composite types, or things that are missing (imho) important features... Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote: - lowercase folding. I DO sometimes wish I could use fieldID, etc. without quoting it. I believe that may be against ANSI SQL. In any case, the only databases I can think of that don't fold-case in some form are MySQL and Access. -- 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 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
Yep, I think the SQL spec says fold to uppercase. I'm not sure why PostgreSQL folds to lowercase instead, but if folding has to occur, I prefer lowercase. CSN --- Jim C. Nasby [EMAIL PROTECTED] wrote: On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote: - lowercase folding. I DO sometimes wish I could use fieldID, etc. without quoting it. I believe that may be against ANSI SQL. In any case, the only databases I can think of that don't fold-case in some form are MySQL and Access. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Gotchas
Stephen Frost [EMAIL PROTECTED] writes: Unquoted object names fold to lower case I don't really see this as too much of an issue, personally, but I do know some people have run into it. The example they give seems a bit off tho, as I thought Oracle just folded to upper-case (in which case table != table in Oracle, but table = table in Postgres, kind of depends on how you look at it). I'm not sure the spec says one way or another. Yeah, it does, and the spec sides with Oracle. This is explained exactly in the manual: : Quoting an identifier also makes it case-sensitive, whereas unquoted : names are always folded to lower case. For example, the identifiers FOO, : foo, and foo are considered the same by PostgreSQL, but Foo and : FOO are different from these three and each other. (The folding of : unquoted names to lower case in PostgreSQL is incompatible with the SQL : standard, which says that unquoted names should be folded to upper : case. Thus, foo should be equivalent to FOO not foo according to the : standard. If you want to write portable applications you are advised to : always quote a particular name or never quote it.) RANDOM() failures This looks like an interesting problem, and might be a bug to be fixed. I think it depends on the what the SQL specification says for this case, if it says anything at all, as to if it should be considered a bug or not. On the surface it certainly does look like an oddity though. Well, it's surely legitimate material as a gotcha. The example is taken from http://archives.postgresql.org/pgsql-general/2004-11/msg01375.php and the previous discussion referred to is this thread: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00082.php As you can see, there wasn't a lot of agreement that we ought to change it. Arguably, if we did change it we'd get ripped for the gotcha of poor optimization when the user forgets to mark nonvolatile functions properly. (Personally, though, I'm in favor of tightening it up.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dump all except some tables?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jim C. Nasby Sent: Thursday, October 06, 2005 3:34 PM Subject: Re: [GENERAL] Dump all except some tables? ... I find myself wondering if it would be good to allow for specifying a set of rules for what to dump in a file, probably something like a set of regexes with a way to specify if it's an include or exclude rule. Seems like it would be a heck of a lot simpler to do that for complex cases than deal with a pile of spaghetti on the command-line It may be useful to cut down on command line clutter if one could specify a file holding a list of table names to include/exclude. -Roger Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] vacuum
hi everybody , is it possible to schedule vacuum in the server ( postgres 8.0.3 on Windows XP SP2) ? I want to vacuum the DB everyday at 22:00 and I want to automate the process thanks in advance Hugo
Re: [GENERAL] PostgreSQL Gotchas
On Thu, Oct 06, 2005 at 05:47:36PM -0500, Jeffrey Melloy wrote: The only thing I could see actually being an issue is the random() one and add missing from. The rest are trivial. The random() thing is interesting, esoteric, and probably has never been a problem in a real situation. (Or has exactly once, when he wrote that gotcha) The random() issue has a workaround that the gotchas page doesn't mention: SELECT id, is_true FROM (SELECT id, RANDOM() 0.5 AS is_true FROM some_table OFFSET 0) AS t_tmp WHERE is_true; Tom Lane brought up the OFFSET 0 trick a couple of days ago in the Avoiding evaluating functions twice thread: http://archives.postgresql.org/pgsql-general/2005-10/msg00107.php -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
I had a similar experience speaking to the MySQL folks at (the last) COMDEX. After trying to get them to explain how their licenses work, I was even more confused (and two reps even gave conflicting info). CSN Hi everyone, I've just got back from LinuxWorld in London and seeing this thread thought I would share my experience of the MySQL stand - if you are of a delicate dispostion, please look away now. I basically asked them straight up why I should use MySQL instead of PostgreSQL and was quite surprised by the result, mainly since it was not done on features but more on FUD. The basic message was this: - MySQL is the most popular open source database, with over 6m enterprise installs, with a large company supporting it. PostgreSQL is run by a very small community of developers. - MySQL can be clustered (This was later retracted when I mentioned I needed something that would work on large tables, as apparently their clustering only works in RAM and so will fail on large queries and queries that use a lot of joins). - All the companies that have tried to operate by selling PostgreSQL support services have gone bankrupt, except for EnterpriseDB. - PostgreSQL doesn't have row level locking. And this last comment really took the biscuit - I really hope that the none of the core team read this and decide to throw in the towel: MySQL has the biggest collection of database experts... Open source people don't know how to write databases So all in all, to say I was upset by some of these comments was an understatement. To all the people I spoke to on the PostgreSQL stand, I hope I did it in a way that made them feel empowered to go and try the PostgreSQL for their own applications by mentioning its benefits, and not by spreading FUD about its competition. Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Dump all except some tables?
On Thu, Oct 06, 2005 at 04:31:14PM -0700, Roger Hand wrote: From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jim C. Nasby Sent: Thursday, October 06, 2005 3:34 PM Subject: Re: [GENERAL] Dump all except some tables? ... I find myself wondering if it would be good to allow for specifying a set of rules for what to dump in a file, probably something like a set of regexes with a way to specify if it's an include or exclude rule. Seems like it would be a heck of a lot simpler to do that for complex cases than deal with a pile of spaghetti on the command-line It may be useful to cut down on command line clutter if one could specify a file holding a list of table names to include/exclude. Here's my thoughts on a summary: [-t [table | glob]]...# 0 or more -t options [-T [table | glob]]...# 0 or more -T options [--include-tables-from-file f] [--exclude-tables-from-file f] where globs get expanded just the way they are in psql, and the exclude is evaluated after the include to remove any tables where they might conflict. I don't think regex matching is needed or good. Does this make sense? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Thu, Oct 06, 2005 at 11:42:57PM +0100, Mark Cave-Ayland wrote: Hi everyone, I've just got back from LinuxWorld in London and seeing this thread thought I would share my experience of the MySQL stand - if you are of a delicate dispostion, please look away now. I basically asked them straight up why I should use MySQL instead of PostgreSQL and was quite surprised by the result, mainly since it was not done on features but more on FUD. The basic message was this: [FUD elided] Did you happen to get names and quotes for any of these? As in, On October 1, 2005, at LinuxWorld London, Foo McBar said, ' ... ' One way to keep the FUD to a minimum is to hold the FUDster personally responsible for it. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Gotchas --- count()
On Informix however it is blindingly fast, and can also be instantly conjured with the dbaccess tool (Info/Table/Status). They might be stashing this count somewhere, but it is not available when the table is locked, as during a load. However they do it, performance does not seem to suffer, and having this rapidly available is certainly nice. Especially when people are used to it. Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Jeffrey Melloy Sent: Thu 10/6/2005 3:47 PM To: Neil Conway Cc: Aly S.P Dharshi; pgsql-general@postgresql.org Subject:Re: [GENERAL] PostgreSQL Gotchas Neil Conway wrote: COUNT(*) very slow: this is a known issue -- see the -hackers archives for many prior discussions. MVCC makes this hard to solve effectively (whether applications should actually be using COUNT(*) on large tables with no WHERE clause is another matter...) -Neil And it's not like a count(*) on an Oracle database of any decently-sized dataset is blazing fast, or even in blazing's ballpark. The only thing I could see actually being an issue is the random() one and add missing from. The rest are trivial. The random() thing is interesting, esoteric, and probably has never been a problem in a real situation. (Or has exactly once, when he wrote that gotcha) Jeff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings !DSPAM:4345aeea115747915089936! ---(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 Gotchas --- count()
Gregory S. Williamson wrote: On Informix however it is blindingly fast, and can also be instantly conjured with the dbaccess tool (Info/Table/Status). They might be stashing this count somewhere, but it is not available when the table is locked, as during a load. However they do it, performance does not seem to suffer, and having this rapidly available is certainly nice. Especially when people are used to it. Informix locks rows during modification so they don't have the MVCC visibility problem we have (some rows are visible to only some backends). -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
- All the companies that have tried to operate by selling PostgreSQL support services have gone bankrupt, except for EnterpriseDB. Oh the irony Command Prompt, Inc... Doing PostgreSQL since 1997. Profitable since 1997. No debt since 1997. Oh... and of course, no outside Vulture Capitalists either. Not to mention Pervasive although new to PostgreSQL has been around a LONG time. Stupid is as stupid does I guess. Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.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] pg_restore --disable-triggers does not stop triggers
Thank you for the reply! Triggers are still fired although option --disable-triggers is applied to pg_restore. The fired triggers abort pg_restore because of the foreign keys violations. [snip] pg_restore -l db1 list createdb -E UNICODE db1 pg_restore -F c -L list -v -d db1 -s db1 log-schema 21 pg_restore -F c -L list -v -d db1 -a --disable-triggers db1 log-data You are DB-Superuser? Presently, the commands emitted for --disable-triggers must be done as superuser. So, you should also specify a supe- ruser name with -S, or preferably run pg_restore as a Post- greSQL superuser. I am using PostgreSQL superuser doing this. I notice that PostgreSQL does disable triggers but it seems to not disable CHECK constraint: CREATE TABLE table1 ( CHECK(VerifyFunc(c2,c3)), c1 VARCHAR(20), c2 VARCHAR(20), c3 char NOT NULL )WITHOUT OIDS; pg_restore: disabling triggers pg_restore: restoring data for table table2 pg_restore: enabling triggers pg_restore: disabling triggers pg_restore: restoring data for table table1 pg_restore: ERROR: Exception raised by VerifyFunc() CONTEXT: COPY table1, line 1: 10 q1 X pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error -- http://www.fastmail.fm - IMAP accessible web-mail ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Joshua D. Drake [EMAIL PROTECTED] writes: - All the companies that have tried to operate by selling PostgreSQL support services have gone bankrupt, except for EnterpriseDB. Oh the irony Actually, AFAIR the *only* such company that's gone under was Great Bridge; and in their case it wasn't that there wasn't a viable business case, it was that the board of directors got cold feet during the 2001 dot-com bust, and refused to continue putting money into it according to the original business plan. Other longtime supporters such as SRA and PostgreSQL Inc are still around; and while Red Hat is not being as vocal about it as they once were, they are still paying me to work on Postgres. So, yeah, the above claim is just FUD. It'd be interesting to ask some hard questions about exactly how solid MySQL AB's finances are ... and how many other support options users will have if they go under. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL Gotchas --- count()
Bruce Momjian pgman@candle.pha.pa.us writes: Gregory S. Williamson wrote: [ re COUNT(*) ] On Informix however it is blindingly fast, and can also be instantly conjured with the dbaccess tool (Info/Table/Status). They might be stashing this count somewhere, but it is not available when the table is locked, as during a load. However they do it, performance does not seem to suffer, and having this rapidly available is certainly nice. Especially when people are used to it. Informix locks rows during modification so they don't have the MVCC visibility problem we have (some rows are visible to only some backends). More to the point: performance does not seem to suffer is an opinion based on no facts. You have no idea what it's costing Informix to maintain that count --- ie, how much faster might other things go if COUNT(*) didn't have to be instant? We know quite well what it would cost to make this happen in Postgres, and it's the general judgment that we don't want to pay those costs --- certainly not to force everyone to pay them. There's some discussion in the pgsql-hackers archives about possible add-on mechanisms to maintain COUNT(*) counts on tables for which the DBA thinks it's justified. It seems clearly doable, but no one's gotten excited enough to actually do it ... in the end, it seems that everyone who's looked closely at their application has decided that it wasn't so important after all. 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] pg_restore --disable-triggers does not stop triggers
CN [EMAIL PROTECTED] writes: I notice that PostgreSQL does disable triggers but it seems to not disable CHECK constraint: Why should it? (Hint: a check constraint that looks at anything but the row being checked is broken by definition.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
