Re: [GENERAL] FTS uses "tsquery" directly in the query
Xu,
FTS has nothing with your problem, it's general limitation/feature.
Oleg
On Sat, 23 Jan 2010, xu fei wrote:
Hi, everyone:
First I can successful run this query:select name, ts_rank_cd(vectors, query) as rank
from element, to_tsquery('item') query where query @@ vectors order by rank desc;But
actually I want to run this one:select name, ts_rank_cd(vectors, query) as rank from
element, 'item'::tsquery query where query @@ vectors order by rank desc;Looks like that
FTS does not support directly use "::tsquery " in such query. Do I
misunderstand something? Thanks!
Xu
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recursion in triggers?
Hi: I'm dealing with a hierarchical design where changes in one record can and should cause changes in other records lower inthe hierarchy. I'm trying to use update triggers to do this. And recursion would be a real nice way to do this. What I need to know is if, in the "after" update trigger I make the subsequent updates to other records in the same table, with the OLD/NEW record ponters be set properly in those subsequent update trigger invocations? Will the current and modified NEW.* values be passed down into the next update trigger "before" call as OLD.* values? Or is recursion like this not allowed? Thanks !
Re: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect
On 23/01/2010 11:52 PM, Alessandro Agosto wrote: Hi, i'm a python/c developer and i need an asynchronous (python) interface to communicate with a database. I like PostgreSQL so i'm trying to write a mock-up for a python extension module to use LibPQ in my asynchronous programs. What's wrong with psycopg2 for this purpose? Python's threading support (in the standard CPython interpreter) is awful due to the global interpreter lock. However, it works for waiting on blocking sockets as the GIL is released before entering most C-language routines. So you should be able to use psycopg2 in dedicated I/O worker threads just fine. If you're trying to use non-blocking sockets and select(...) with libpq, well, _then_ you'll have to go outside psycopg2. Be aware, though, that using SSL sockets in a non-blocking manner can be ... complicated ... so look into that in detail before deciding on this path. Multiple threads with blocking connections is likely to be a LOT easier. Frankly, though, you're never going to get wonderful results out of this. Twisted tries, but you've probably seen the issues it has working around the GIL and the limited success it has doing so. CPython's GIL dooms it to be a pretty shoddy language for high concurrency use, whether you use an async socket server model or a blocking threaded model. Can someone tell me what i wrong? And if you know some resource that explains with an example how i should to verify the connection during the select/poll, would be appreciated. You can't reliably verify that a connection is alive with select/poll. TCP/IP timeouts are very long and until the connection times out at the TCP/IP level, it might appear fine even though the peer died hours ago. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FTS uses "tsquery" directly in the query
Hi, everyone:
First I can successful run this query:select name, ts_rank_cd(vectors, query)
as rank from element, to_tsquery('item') query where query @@ vectors order by
rank desc;But actually I want to run this one:select name, ts_rank_cd(vectors,
query) as rank from element, 'item'::tsquery query where query @@ vectors order
by rank desc;Looks like that FTS does not support directly use "::tsquery " in
such query. Do I misunderstand something? Thanks!
Xu
Re: [GENERAL] Slow Query / Check Point Segments
Greg Smith wrote: John R Pierce wrote: I know the database has a lot of write volume overall, and its only one of several databases running in different zones on the server. I know nothing about the SAN, I suspect its a EMC Symmetrix of some sort. Probably a generation or two behind latest. The operations people are used to running large oracle databases. One thing you might try is making the PostgreSQL install act more like an Oracle one in terms of how it does writes. By default, PostgreSQL does its WAL writes by writing and then calling a sync method. On Solaris, you should be able to safely change this in the postgresql.conf file to be: iostat -x 5 or 15 shows a bunch of LUNs are as much as 10% busy, but the service time stays under 50ms... this is one of the busiest of the 15 second samples over a 10 min period I'm just showing a representive sampling of the busiest LUNs out of 40, the physical storage is all raid10's on a DMX4. extended device statistics devicer/sw/s kr/s kw/s wait actv svc_t %w %b sd1 0.00.00.00.0 0.0 0.00.0 0 0 ... ssd10 0.0 33.20.0 546.6 0.0 1.0 28.9 0 7 ssd11 0.0 27.70.0 573.9 0.0 0.9 34.0 0 6 ssd12 0.0 56.20.0 576.6 0.0 1.5 26.5 0 10 ssd13 0.0 30.80.0 505.5 0.0 0.9 28.7 0 7 ssd14 0.0 42.90.0 498.4 0.0 1.4 32.9 0 9 ssd15 0.0 38.30.0 557.6 0.0 1.7 44.0 0 8 ssd16 0.0 41.10.0 520.5 0.0 1.3 32.0 0 9 ssd17 0.0 52.40.0 528.6 0.0 1.3 24.8 0 10 ssd18 0.0 29.00.0 503.9 0.0 1.2 41.4 0 7 ... most show near 0% busy and 10/th that volume of writes or reads. zpool iostat 15 also shows this io peak for about a minute or two every few minutes but its nowhere near hardware capacity $ zpool iostat 15 capacity operationsbandwidth pool used avail read write read write -- - - - - - - data-p1 268G 101G109386 1.16M 6.98M data-p2 340M 33.2G 0 2491 36.8K -- - - - - - - .. -- - - - - - - data-p1 268G 101G 40 6 527K 164K data-p2 341M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 36 1.35K 306K 19.2M data-p2 341M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 45585 445K 2.01M data-p2 341M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 47 38 410K 1.05M data-p2 341M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 68145 745K 4.11M data-p2 341M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 38168 311K 4.60M data-p2 340M 33.2G 0 9 0 294K -- - - - - - - data-p1 268G 101G 55 65 504K 1.18M data-p2 340M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 5 1.07K 43.1K 9.71M data-p2 340M 33.2G 0 0 0 0 -- - - - - - - data-p1 268G 101G 46 7 549K 179K data-p2 340M 33.2G 0 0 0 0 -- - - - - - - ^C wal_sync_method=open_datasync Which I don't think is the default (you can confirm with "show wal_sync_method;" via psql on your database). That will use O_DSYNC writes, which are more like how Oracle approaches this and therefore potentially a better tuned path for your install. thats what its set to now. More on this subject, including idea for further tweaking http://www.postgresql.org/docs/8.4/static/runtime-config-wal.html http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm From what you've shown and described, I'm not sure what other PostgreSQL tuning you might do to improve the specific symptoms you're seeing. The particular issue you've got I'd normally start attacking on the filesystem and hardware side of things. There's a reason why many people avoid SANs in this context, there's a lot of ways you can screw up in this particular aspect of their performance relative to what you get with direct attached storage, and it's hard to tell which you've run into when a problem does pop up. The SAN is a dmx4, and it seems to be damn fast by all low level tests i can run. this database is really getting hammered on. a couple of the tables take 8-12 hours to vacuum. these are tables wit
Re: [GENERAL] more docs on extending postgres in C
Ivan Sergio Borgonovo writes: > - installing postgresql-server-dev-[version] > - apt-get source postgresql-server-dev-[version] > - copy from there a contrib dir in my ~ (or wherever you prefer) > - export USE_PGXS=1; make Don't forget apt-get build-dep postgresql-[version] > I didn't have postgresql server installed on this box but I have > stuff that got in to satisfy dependencies for php/python pg drivers > and psql. > I wasn't able to understand from where PGXS pick up the version > since I installed -dev-8.3 but everything else was for 8.4 and it > didn't work: See pg_config, there's the default one then one per major version. > Thanks... I'd publish a summary as soon as I've clearer ideas so > that the next poor guy will find easier to write contrib on Debian. > > I don't know if the version problem is worth a bug report to Debian > (or pg people that built the pgxs system). I' working on this, see the following email and thread. http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/2010-January/000546.html In short it's about doing a VPATH build and setting some variables, then building a PGXS extension is very easy. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mapping Java BigDecimal
Jakub Bednář wrote: Hi All, We decide add support PostgreSQL database (now supporting only Oracle database) to our product. In Oracle we mapping Java BigDecimal to number(19, 2), in PostgreSQL to numeric(19, 2). If I store to "BigDecimal column" number without decimal, e.g. "3", than Oracle JDBC driver return "3", but PostgreSQL JDBC driver return "3.00". Is there some way (mapping, server setup,...) how reach return number without trailing zeroes on decimal position? There's no difference between those two values in Java's BigDecimal. Are you saying that the 'scale' of the returned values differs between the JDBC for Oracle and that for PG? -- Lew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slow Query / Check Point Segments
John R Pierce wrote: I know the database has a lot of write volume overall, and its only one of several databases running in different zones on the server. I know nothing about the SAN, I suspect its a EMC Symmetrix of some sort. Probably a generation or two behind latest. The operations people are used to running large oracle databases. One thing you might try is making the PostgreSQL install act more like an Oracle one in terms of how it does writes. By default, PostgreSQL does its WAL writes by writing and then calling a sync method. On Solaris, you should be able to safely change this in the postgresql.conf file to be: wal_sync_method=open_datasync Which I don't think is the default (you can confirm with "show wal_sync_method;" via psql on your database). That will use O_DSYNC writes, which are more like how Oracle approaches this and therefore potentially a better tuned path for your install. More on this subject, including idea for further tweaking http://www.postgresql.org/docs/8.4/static/runtime-config-wal.html http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm From what you've shown and described, I'm not sure what other PostgreSQL tuning you might do to improve the specific symptoms you're seeing. The particular issue you've got I'd normally start attacking on the filesystem and hardware side of things. There's a reason why many people avoid SANs in this context, there's a lot of ways you can screw up in this particular aspect of their performance relative to what you get with direct attached storage, and it's hard to tell which you've run into when a problem does pop up. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Old/New
On Saturday 23 January 2010 7:51:28 am Bob Pawley wrote: > I havn't been able to find documentation on how to use \d. When I open the > psql interface (through either port ) it asks for a password but doesn't > allow any entry of a password. That would depend on the settings in pg_hba.conf, whether you have passwords enabled for the connection or not. I am not sure what you either port? Are you referring to the port address and the local socket? > > However, after my last e-mail to you, I came across something interesting - > at least to me. > > I use pg_admin scripts to modify triggers. > > Looking through pg_admin at all of the triggers on the p_id.processes table > I just happened to click on the trigger we have been discussing and then > clicked its refresh button. > > Using the same update statement and the same After Update trigger that > inserted two rows into p_id.devices it now inserted only one row and that > row was the correct row. > > I don't know enough about the interaction between the unrefreshed copy of a > trigger held by pg_admin and the updated trigger installed in the server to > comment - however there does seem to be a connection of which I wasn't > aware. Can't help you there I don't use pgAdmin. > > Bob > -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Referencing to system catalog problem
On Saturday 23 January 2010 6:15:36 am Davor J. wrote: > I am logged in as superuser. I am trying to create something similar to > this: > > > Code: > CREATE TABLE tbl_unit_convfunctions( > unit_from integer REFERENCES tbl_units (unit_id), > unit_to integer REFERENCES tbl_units (unit_id), > proc_id oid REFERENCES pg_proc (oid) > )but no matter what I refer to from pg_proc, i get the error message: > > ERROR: permission denied: "pg_proc" is a system catalog > SQL state: 42501 > > Has anyone any suggestions how to do something similar, or even better: how > to solve this error. I couldn't find any useful information on the net > about this issue. > > Thanks, > Davor > > Original post: > http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog- >problem-670063.html > > Note: OID's are unique in that table, and should be referable, and I > explicitely granted the REFERENCE priviledge to the superuser. You can't have FKs to system tables. See this post for explanation: http://archives.postgresql.org/pgsql-general/2004-12/msg00840.php -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL -> Postgres migration tools?
On Fri, 2010-01-22 at 11:15 -0800, Erik Jones wrote: > Does anyone know of any good, current migration tools out there? There is an open source tool developed by EnterpriseDB: MigrationWizard http://www.enterprisedb.com/openDownloads.do?productId=407&redirectReason=true&productVersion=otherDownload If you are using RHEL/CentOS 5 or Fedora 11+, you may find RPM version from: http://yum.pgsqlrpms.org/8.4/redhat/rhel-5Server-i386/repoview/migrationwizard.html http://yum.pgsqlrpms.org/8.4/fedora/fedora-11-i386/repoview/migrationwizard.html http://yum.pgsqlrpms.org/8.4/fedora/fedora-12-i386/repoview/migrationwizard.html Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Old/New
On 23/01/2010 15:51, Bob Pawley wrote: > I havn't been able to find documentation on how to use \d. When I open Hi Bob, In brief: \dt lists all the tables in the current schema \d gives the structure of the named table .. and loads of others. The docs are here: http://www.postgresql.org/docs/8.4/static/app-psql.html See the section entitled "meta-commands", a good distance down the page. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL -> Postgres migration tools?
2010/1/23 Craig Ringer : > > An InnoDB `AUTO_INCREMENT' column under MySQL 5.x is pretty similar to a > sequence. > > I increasingly think it's pretty safe to just > 's/AUTO_INCREMENT/SERIAL/g' > in DDL. Apps that use MyISAM aren't going to be fussy about how it works, > and apps that use InnoDB get Pg-like behaviour anyway. Yes, but I have seen it many times, where people needed a sequence that drives more than just one column/table. Can't do it with the simplistic mysql approach. > Modern (5.x-something-late) MySQL with the InnoDB storage engine is a very > different beast from MySQL 3.x/4.x or MySQL 5.x with MyISAM. It still has > some scary data-conversion habits and you need to turn on several different > kinds of strict modes and behave-standard-ways modes to get it to play nice, > but if you have and you're moving over to Pg you shouldn't actually have too > rough a time. > > ( Unless you use some of the MySQL features, like its upsert command REPLACE > or the INSERT ... ON DUPLICATE KEY UPDATE ..., that just have no equivalent > in Pg. Or you're relying on scary MySQL-isms like -00-00 dates. ). this is easily done with triggers, and again is more powerful. MySQL was designed to be easy to grasp for someone with limited time/skill in SQL. And as everything that is meant to be simple, it fails quickly if you need to use it for something more realistic/complete/insert your word here. Lets be honest, people use mysql with the default backend usually, because innodb is just too slow. I dumped mysql long time ago, but I still hear from people that it suffers from same problems. Hence, I still recommend people to switch to postgresql. Btw, if you want to help, please ask as many hosting places as you can about postgresql support, mentioning that you would probably buy it from them, if they had postgresql for the same price as mysql (read, for free, included in cheapest package usually). This is so far the biggest problem to overcome, and the major reason people choose mysql in first place. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect
Hi, i'm a python/c developer and i need an asynchronous (python) interface to communicate with a database. I like PostgreSQL so i'm trying to write a mock-up for a python extension module to use LibPQ in my asynchronous programs. I've started to study the docs about libpq just yesterday, so if i mistake something please correct me. I'm writing the connection function, using the api PQconnectStart but verifing the status of this call i get that the condition PQstatus(connection) != CONNECTION_OK (the connection must return CONNECTION_OK) is failed so my program exits. Using instead of it the PQconnectiondb all work as expected, but not using this api. Can someone tell me what i wrong? And if you know some resource that explains with an example how i should to verify the connection during the select/poll, would be appreciated. Thank you, Alex. -- Alessandro A.
Re: [GENERAL] Old/New
I havn't been able to find documentation on how to use \d. When I open the psql interface (through either port ) it asks for a password but doesn't allow any entry of a password. However, after my last e-mail to you, I came across something interesting - at least to me. I use pg_admin scripts to modify triggers. Looking through pg_admin at all of the triggers on the p_id.processes table I just happened to click on the trigger we have been discussing and then clicked its refresh button. Using the same update statement and the same After Update trigger that inserted two rows into p_id.devices it now inserted only one row and that row was the correct row. I don't know enough about the interaction between the unrefreshed copy of a trigger held by pg_admin and the updated trigger installed in the server to comment - however there does seem to be a connection of which I wasn't aware. Bob - Original Message - From: "Adrian Klaver" To: "Bob Pawley" Cc: "Tom Lane" ; "Postgresql" Sent: Friday, January 22, 2010 3:37 PM Subject: Re: [GENERAL] Old/New On Friday 22 January 2010 3:25:34 pm Bob Pawley wrote: No The table p_id.processes is the start of the fluid_id ident and that column is serial. Bob Per Tom's suggestion can we see \d for p_id.processes and for good measure p_id.devices ? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Referencing to system catalog problem
I am logged in as superuser. I am trying to create something similar to this: Code: CREATE TABLE tbl_unit_convfunctions( unit_from integer REFERENCES tbl_units (unit_id), unit_to integer REFERENCES tbl_units (unit_id), proc_id oid REFERENCES pg_proc (oid) )but no matter what I refer to from pg_proc, i get the error message: ERROR: permission denied: "pg_proc" is a system catalog SQL state: 42501 Has anyone any suggestions how to do something similar, or even better: how to solve this error. I couldn't find any useful information on the net about this issue. Thanks, Davor Original post: http://forums.devshed.com/postgresql-help-21/referencing-to-system-catalog-problem-670063.html Note: OID's are unique in that table, and should be referable, and I explicitely granted the REFERENCE priviledge to the superuser. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error "invalid byte sequence for encoding UTF8" on insert into BYTEA column
>You probably need to ask the mxODBC developers (who AFAIK don't hang out >on this list) what they are doing with that data. It sounds fairly >likely to me that the bytea value is just being sent as a string without >any special encoding. That would explain both the null sensitivity you >mention later in the thread, and the encoding validity complaints --- >PG 8.1 was much less picky about string encoding validity than recent >versions are. >There are basically two ways that you could make this work reliably: >arrange for the bytea value to be sent as an out-of-line binary >parameter, or encode it using backslash sequences (eg, '\000' for a >null). Whether the former is possible with mxODBC I dunno. The latter >might be something that mxODBC will do for you if it knows the value >is supposed to be bytea, but without that knowledge I don't see how it >could. You might end up having to do the encoding yourself. Preliminary notes: 1. I have now confirmed that at some point I upgraded from mxODBC 3.0 to 3.0.3. The statement in my original posting that my mxODBC installation had not changed was wrong. 2. The Python 'str' datatype is used for any sequence of single bytes, like C's array of char. One cannot tell from the datatype what these bytes are intended to represent: it could be ASCII characters, characters in any single-byte encoding, Unicode in any encoding, or binary data. I have discovered a workaround, which is to pass the data to mxODBC in a Python buffer object, which clearly identifies the data as binary. I wrote to eGenix about this as follows: For some years I have used a Postgres 8.1.4 database (UTF8) with the Postgres Unicode ODBC driver. At first I used mxODBC 2.0 as my interface with Python 2.4, then I upgraded to mxODBC 3.0 for Python 2.5. I am now on Python 2..6. With mxODBC 2.0 I had to use the Binary() function to convert binary data to a buffer object if I was to insert it successfully into a bytea column. With mxODBC 3.0 I found that that was no longer necessary, though now I had to check the “bytea as LO” option of the driver. At some point I upgraded to mxODBC 3.0.3, and recently I upgraded to Postgres 8.4.1. On trying to insert into a bytea column yesterday I found I was no longer able to do so. Either I received an "invalid byte sequence for encoding UTF8" error from Postgres, or the data was truncated at the first NULL, as though it was a C-style string. I find, however, that if I use Binary() again, I can insert the data correctly. It seems to make no difference whether I use BIND_USING_SQLTYPE or BIND_USING_PYTHONTYPE. Possibilities: i) Postgres has changed its processing of bytea input. ii) mxODBC has changed the way it handles such data. iii) Both have changed. Can you shed any light on this? Marc-Andre Lemburg replied as follows: mxODBC has not changed in this respect between 3.0.0 and 3.0.3, so it must be the PostgreSQL code that has changed. Note that changing the bind type doesn't help in this case, since the PostgreSQL ODBC driver does not provide the necessary APIs to do SQL type binding. As a result, mxODBC will always use Python type binding. By using the Binary() wrapper for binary data (which really is just an alias for buffer()), you tell mxODBC to send the data as binary data to PostgreSQL. Without the wrapper, mxODBC will send the data as text data and since it is not possible to store binary data as UTF-8 you get the errors you are seeing. What puzzles me is hinted at in the last sentence: why does Postgres 8.4.1 (though apparently not 8.1.4) try to interpret the bytes as UTF8 when they are being sent to a column that is typed as bytea? I apologise if this posting is excessively long, but I like to understand the reasons for things, and others may find the information useful.
Re: [GENERAL] Slow Query / Check Point Segments
Greg Smith wrote: My guess is that there's something wrong with your config such that writes followed by fsync are taking longer than they should. When I see "sync=0.640 s" into a SAN where that sync operation should be near instant, I'd be looking for issues in the ZFS intent log setup, how much data you've setup ZFS to write cache, and the write cache policy on the SAN hardware. There's something weird going on here, that sync should be near instant on your hardware with such a small write volume. Unfortunately, you may have to take the server down to find out exactly what's going on, which doesn't sound practical for your environment. (And people wonder why I have near religious zeal about testing disk hardware before systems go into production) there's a further complication. this system is deep in southeast asia on a rather slow and saturated network connection such that trying to figure out stuff about it is challenging at best. I know the database has a lot of write volume overall, and its only one of several databases running in different zones on the server. I know nothing about the SAN, I suspect its a EMC Symmetrix of some sort. Probably a generation or two behind latest. The operations people are used to running large oracle databases. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL -> Postgres migration tools?
On 23/01/2010 3:31 PM, Grzegorz Jaśkiewicz wrote: On Fri, Jan 22, 2010 at 7:15 PM, Erik Jones wrote: Hello, Given that the EU has approved Oracle's acquisition of Sun w/ MySQL it's fairly likely that there may be a number of people and companies looking to move from MySQL to Postgres in the coming months. Does anyone know of any good, current migration tools out there? A search for mysql on pgfoundry only turns up a few projects that haven't seen any recent updates... The problem there is not that there are no such tools, but that the database might need a bit of refinement when you move away from such simple database engine as MySQL. Most often, either due to lack of knowledge, or sometimes because you are forced to - the mysql database won't utilise sometimes even basic features of postgresql (sequences would be one grand example). An InnoDB `AUTO_INCREMENT' column under MySQL 5.x is pretty similar to a sequence. I increasingly think it's pretty safe to just 's/AUTO_INCREMENT/SERIAL/g' in DDL. Apps that use MyISAM aren't going to be fussy about how it works, and apps that use InnoDB get Pg-like behaviour anyway. Modern (5.x-something-late) MySQL with the InnoDB storage engine is a very different beast from MySQL 3.x/4.x or MySQL 5.x with MyISAM. It still has some scary data-conversion habits and you need to turn on several different kinds of strict modes and behave-standard-ways modes to get it to play nice, but if you have and you're moving over to Pg you shouldn't actually have too rough a time. ( Unless you use some of the MySQL features, like its upsert command REPLACE or the INSERT ... ON DUPLICATE KEY UPDATE ..., that just have no equivalent in Pg. Or you're relying on scary MySQL-isms like -00-00 dates. ). So to summarise, it is two stage process really. And the first stage - migration, should be fallowed by the database and code refinement before even the first 'after migration' release. And I believe that there is numerous sources out there to help people with that. Plus, we are always trying to be helpful on the list. That I tend to agree with. It's not just about converting your data and schema. You *will* need to adjust apps with any non-trivial queries. Even if you can hack it to work, you can often learn things as part of the porting process that can improve your results on *both* databases. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
