Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
Problem is: to offer such a thing with a straight face, we'd have to confine ourselves to an Oracle-subset version of SQL. For instance, lose the ability to distinguish empty-string from NULL. I wasn't saying we write it - let Oracle do it :D Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
If there smart enough to be buying innobase these days, you can bet that by now they have this stuff all straightened out. No, that doesn't seem to follow ... if Oracle are spending their resources to attack MySQL rather than us, the conclusion would be that they are clearly still more informed by the buzz than technical merit. With no disrespect to PostgreSQL, MySQL has 100x our downloads and installations... Oracle is simply going after by far the biggest open source database player... Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
As Andrew noted, we've already heard plenty of FUD from Oracle. What we've not seen is a FUD campaign based on serious study of our weaknesses --- they've only bothered to muster transparent attacks on open source DBs in general. My prediction is that the next step will be FUD that's really designed specifically against Postgres. I admit I must have missed all this '.org FUD' - is it still around. I really don't know what you guys are referring to. Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
1) PostgreSQL to Oracle database conversion utilities released by Oracle (unlikely given extensible languages in PostgreSQL). Strangely a pgsql to oracle exporter is a good thing. It'd be a great feature of PostgreSQL. Imagine how many people would start on PostgreSQL if they KNEW that one day they could easily move to Oracle if they needed to. Risk management. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
Please don't make this assumption. PostgreSQL is *very* much on their radar, and probably represents the biggest long-term threat to their core database business at the moment. We got a hint of that during the .org bidding, but for now it is in Oracle's interest not to call attention to PostgreSQL. The last thing they want is publicity for the project. We may be a harder target to hurt than MySQL, but we are a target, make no mistake about it. I'm sure PostgreSQL is on the radar of Sybase, Microsoft, and IBM as well. And they probably read every word we write ;) Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Shell script to extract a table from a plain text dump
Argh! That's some sed coolness :) Chris Martijn van Oosterhout wrote: On Fri, Oct 07, 2005 at 04:46:12PM +0800, Christopher Kings-Lynne wrote: If you have huge plain text dumps, and just want to restore one table it's usually painful. Attached is a small shell script that can take a plain text dump and extract a single table's COPY data commands from it. If people think it's interesting and should be developed, I can pop it on pgfoundry or something. Hmm, what I usually use is: bzcat $file | sed -ne /^COPY \$table\ /,/^\\\.\$/p However, error checking and wrapping it into a script is a good idea. If it got given a couple of switches to control the output, maybe we can have a pg_restore for text dumps :) Have a nice day, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Shell script to extract a table from a plain text dump
If you have huge plain text dumps, and just want to restore one table
it's usually painful. Attached is a small shell script that can take a
plain text dump and extract a single table's COPY data commands from it.
If people think it's interesting and should be developed, I can pop it
on pgfoundry or something.
Chris
#!/bin/sh
# This script extracts a single table from a PostgreSQL pg_dumpall plain
# text dump. This is useful for restoring just one table.
#
# Usage: restore.sh backup bzip table name
# Check that arguments are given
if [ $1 = -o $2 = ]; then
echo Error: arguments not given
exit
fi
# Check that we're not going to clobber existing files
if [ -e working.sql -o -e working.sql-e -o -e $2.sql ]; then
echo Error: working files already exist
exit
fi
# Extract the backup to a working SQL script
bunzip2 $1 working.sql
# Find the line before the table's COPY output begins
START=`grep -n ^COPY $2 working.sql | sed -e 's/:.*//'`
START=$(($START-1))
# Remove all of the working file before the COPY
sed -i -e 1,${START}d working.sql
# Find line number at which COPY ends
END=`grep -n ^\\. working.sql | head -1 | sed -e 's/:.*//'`
END=$(($END+1))
# Remove all contents of the working file after the end of the COPY
sed -i -e $END,\$d working.sql
# Rename the working file to the table name
mv working.sql $2.sql
# Remove sed temporary file
rm working.sql-e
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] New MySQL to PostgreSQL Migration Guide
PDF not found? Chris Travers wrote: Hi; I have just posted a MySQL to PostgreSQL migration guide at http://www.metatrontech.com/wpapers and it is free for pretty much any use (I do have a somewhat toned-down advertising clause in the copyright license). It is a first draft and formatting and other stuff will be likely changed. I am also considering creating a set of wrapper functions which will provide compatibility with many of the non-standard functions in MySQL. Any feedback, etc. is appreciated. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type
So far, the checklist I can see includes: * Maintaining conversion scripts What I think we need is a C program that dumps directly from MySQL into PostgreSQL sql. ie. Take the mysqldump source code and just modify its output. Will inherit the MySQL license though :( Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type
I think one of the more difficult areas will be to convert unsigned fields from mysql into postgres. For smaller sizes it is possible to convert to postgres by moving one size up and using constraints to restrict numbers to be positive, and possibly within the mysql range too. But, the problem is unsigned bigint in mysql to postgresql. There's not another larger integer size that can be used that would allow the 18446744073709551615 (is that the max value?) max value available in mysql. Or am I missing something? You'd just issue a warning... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] INHERITS and planning
Well, it's not so much that I care about queries with 1000+ relations, as that this is a good way to stress-test the code and find out where the performance issues are. There are many thousand lines of code that can never be performance-sensitive, but to expose the ones that are it helps to push the envelope a bit. Once we have partitioning and people set up automated scripts to partition off stuff, we may well end up with 1000+ table queries... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [HACKERS] mirroring oracle database in pgsql
Check out EnterprisDB: www.enterprisedb.com Chris Edward Peschko wrote: hey all, I'm trying to convince some people here to adopt either mysql or postgresql as a relational database here.. However, we can't start from a clean slate; we have a very mature oracle database that applications point to right now, and so we need a migration path. I went to the mysql folks, and it looks like its going to be quite a while before mysql is up to the task, so I thought I'd try pgsql. Anyways, I was thinking of taking the following steps: a) finding a Java API that transparently supports both postgresql and Oracle data access and stored procedure calls. b) instrumenting the Oracle database so that all tables support timestamps on data rows. c) mirroring the Oracle database in MySQL. d) making interface code connecting the MySQL database to the Oracle database (and both applying updates to the database as well as data. In other words, I'm looking to make a postgresql - Oracle mirroring tool, and syncing the databases on a nightly basis, and I was wondering if anybody had experience with this sort of thing. As I see it, if we pull this off we could save quite a bit in licensing costs - we'd still have oracle around, but it would only be a datastore for talking to other oracle databases, and run by batch, not accessed by end users. However: a) I'm not sure how well stored procs, views, triggers and indexes transfer over from oracle to postgresql. b) I'm not sure how scalable postgresql is, and how well it handles multiprocessor support (we'd be using a six-processor box. As an aside, how much experience do people on the list have with enterprise db? I was thinking that they might alleviate the mirroring headaches quite a bit, but they don't seem to have a solaris port.. Anybody have a take on their db? Ed ( ps - if you subscribe to the mysql list, no you're not seeing double. I posted a very similar message on the mysql lists a couple of days ago.. ) ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [PHP] Any experiance with PostgreSQL and SQLRelay
I need a db connection pooling in PHP. As far I know persistent connections are not the best solution so I'm thinking about using SQLRelay. Does anyone have any experience using PostgreSQL + PHP + SQLRelay ? Maybe you know other connection pooling solutions for PHP ? Try pgpool. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] plPHP in core?
d) Bringing PL/Java into core will force a consistent documentation and, I imagine, a chapter of it's own in the main docs. I'm happy to write most of it but English is not my native language. Whatever I put into print will always benefit from a review. There is nothing stop'ng a chapter being added now, Actually there is: We don't ship documentation for software that we don't ship. How about we start a new standard extensions manual on pgFoundry, and give the documentors from all the main extension projects commits on it. That way it's all in one place :) Or at least a PL's manual... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low.
I find this tiny (9-line) patch useful to help my clients know when FSM settings may need updating. Some of the more frequently asked questions here are in regards to FSM settings. One hint I've seen is to run vacuum verbose;. At the end of thousands of lines of INFO and DETAIL messages vacuum verbose has 2 separate lines with some numbers to compare (total pages needed and FSM size...pages) that help indicate too low fsm settings. I've gotten into the habit of always installing the following patch (below) that automatically does this comparison for me, and if max_fsm_pages is too small, it logs a warning as shown here: patched=# vacuum; WARNING: max_fsm_pages(1601) is smaller than total pages needed(2832) VACUUM I think this patch is great. I can never figure out how to set those settings easily. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [PERFORM] HELP speed up my Postgres
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select mobile_num from LOADED_MOBILE_NUMBERS) Change to: update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where exists (select 1 from LOADED_MOBILE_NUMBERS lmn where lmn.mobile_num=SUBSCRIPTIONTABLE.mobile_num); That should run a lot faster. Make sure you have indexes on both mobile_num columns. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [PERFORM] Tuning queries on large database
sort_mem = 5 That is way, way too large. Try more like 5000 or lower. num_poste | numeric(9,0)| not null For starters numerics are really, really slow compared to integers. Why aren't you using an integer for this field since youhave '0' decimal places. schema | relfilenode | table | index| reltuples | size +-+--++-+-- public | 125615917 | data || 1.25113e+08 | 72312040 public | 251139049 | data | i_data_dat | 1.25113e+08 | 2744400 public | 250870177 | data | pk_data| 1.25113e+08 | 4395480 My first remark is that the table takes a lot of place on disk, about 70 Gb, instead of 35 Gb with oracle. Integers will take a lot less space than numerics. The different queries of the bench are simple queries (no join, sub-query, ...) and are using indexes (I explained each one to be sure) : Q1 select_court : access to about 700 rows : 1 num_poste and 1 month (using PK : num_poste=p1 and dat between p2 and p3) Q2 select_moy : access to about 7000 rows : 10 num_poste and 1 month (using PK : num_poste between p1 and p1+10 and dat between p2 and p3) Q3 select_long : about 250 000 rows: 2 num_poste (using PK : num_poste in (p1,p1+2)) Q4 select_tres_long : about 3 millions rows : 25 num_poste (using PK : num_poste between p1 and p1 + 25) The result is that for short queries (Q1 and Q2) it runs in a few seconds on both Oracle and PG. The difference becomes important with Q3 : 8 seconds with oracle 80 sec with PG and too much with Q4 : 28s with oracle 17m20s with PG ! Of course when I run 100 or 1000 parallel queries such as Q3 or Q4, it becomes a disaster ! Please reply with the EXPLAIN ANALYZE output of these queries so we can have some idea of how to help you. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [HACKERS] Remove MySQL Tools from Source?
But you would have to assign the copyright to them If someone is going to make money from my code, I prefer it to be me, or at least that everyone has a chance to do so rather than just one company. Well, then for the same reason we should write a Perl script that connects to MySQl and dumps in PGSql format. I think it's silly to try and read a MySQL dump and convert it - let's just dump straight from the source. Josh - I'm kind of keen to make this happen... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [HACKERS] Remove MySQL Tools from Source?
... on projects.postgresql.org, or similar.They really aren't doing any good in /contrib. I've already set up a category conversion tools on pgFoundry, and my idea was one project per target system. I reckon that by far the best way to do a mysql2pgsql converter is to just modify mysqldump C source code to output in postgresql format! Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] select statement against pg_stats returns
I don't think so --- we weren't trying to use it as an actual column datatype back then. 7.4 has a problem though :-( ... this is one of the damn I wish we'd caught that before release ones, since it can't easily be fixed without initdb. Reminds me that I need to get to work on making pg_upgrade viable again. Has anyone given any thought as to whether dumping and restoring pg_statistic is worthwhile? eg. some sort of ALTER TABLE..SET STATISTICS (1.0, 3.3, 'asdf',) command? Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] RE: [HACKERS] Enum type emulation: problem with opaque type in PL/pgSQL functions
I don't care how but I need to emulate ENUM type, just to convert
MySQL dumps to PostgreSQL. E.g. ENUM values
stored in MySQL dump should be restorable in Postgres without any
conversion.
In MySQL, ENUM is like this:
create table blah (
sex ENUM ('M', 'F')
);
This can be emulated in Postgres like this:
create table blah (
sex CHAR(1) CHECK (sex IN ('M', 'F'))
);
The _real_ trick is implementing MySQL sets in Postgres...
Chris
RE: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
Speaking of MySQL, has anyone looked at www.mysql.org recently? They have a big news article: MySQL wins Linux Journal Readers Choice Award again! For the third Year in a row MySQL won the Readers Choice Award in Linux Journal. Considering that MySQL earlier this fall won the Linux Magazine Editors Choice Award, reading magazines on the whole has been a very rewarding experience for MySQL fans lately. If you follow their link to www.linuxjournal.com, all I can find is an article about how _PostgreSQL_ won the Linux Magazine Editors Choice award! What's with that??? Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Robert D. Nelson Sent: Tuesday, November 14, 2000 12:22 AM To: [EMAIL PROTECTED]; Michael Fork; Poul L.Christiansen Cc: pgsql-general; pgsql-hackers Subject: [HACKERS] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL I made it all the way through the article. I'll summarize it for you: Postgres - hooray! MySQL - boo! Yeah, and that's about it. No analysis or anything. Disappointing, after waiting so long for the pages to load. Since this is an open source database article linked off of slashdot, I imagine they're getting pounded. Still...Regardless of what database they're running, either their abstraction layer is shit or their queries really need optimized. Is that perhaps why, even at 5 clients, the page views he shows never went significantly above 10/sec? Rob Nelson [EMAIL PROTECTED]
