Re: [GENERAL] Backup issues
On 26/06/2010 2:07 AM, Joshua D. Drake wrote: On Fri, 2010-06-25 at 23:24 +0530, RP Khare wrote: I never used PostgreSQL in production environment. Now I got an opportunity to migrate a MySQL production database to PostgreSQL. Before migrating, I have few queries on data recovery: 1. Is there any feature of scheduled backups? You can schedule backups anyway you like :). If you are on Windows I believe you can use the job agent stuff in contrib. That might make your life easier. Otherwise you can write a batch file. 1. In case there is no backup and I want to shift my data files to a new PC, how to do that? You have to move the whole cluster. (Your data directory) ... and you can only do it to a new machine of the same CPU architecture and major version of PostgreSQL. You can't move from, say, a 32-bit to a 64-bit machine without a dump and reload, nor from PostgreSQL 8.3 to PostgreSQL 8.4. Keep good pg_dump backups, or maintain a PITR warm spare, because that's the best way to shift your data. You *can* move the cluster, but it's a pain. -- 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
Re: [GENERAL] pl-perl for 64 bits in Solaris 9
On 06/25/10 9:28 PM, Felipe de Jesús Molina Bravo wrote: original perl is 5.6. so i installed perl 5.8.9 for 64 bit and the error is the same... maybe my error is with the compiler, it is "gcc" (3.4.6). is it possible? where did this 64bit 5.8.9 come from? I try to downloaded SunStudio c/c++ compiler ... but it is not free... and the administration don't have it it was free last time I looked. you just have to have a sunsolve account to access it. -- 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] pl-perl for 64 bits in Solaris 9
2010/6/25 John R Pierce > On 06/25/10 2:21 PM, MOLINA BRAVO FELIPE DE JESUS wrote: > >> Hi >> >> I don't have experience with solaris. I know it is a 64 bits: >> >> bash-2.05# isainfo -v >> 64-bit sparcv9 applications >> 32-bit sparc applications >> >> >> >> I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for >> 64 bits. The options for the configure are: >> >> /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml >> --with-libxslt >> >> > > I would use the SunStudio c/c++ compiler for Sun Sparc, especially 64bit > sparc. All the sun stuff is built with Sun Studio (/opt/SUNWspro/bin/cc > etc). > > the perl thats built into solaris 9 is likely a little old, and may not be > 64bit, I'm not sure. k, on my sol9 sparc legacy test system(a V240), > /usr/bin/perl is 32bit. There is no /usr/bin/sparcv9/perl > original perl is 5.6. so i installed perl 5.8.9 for 64 bit and the error is the same... maybe my error is with the compiler, it is "gcc" (3.4.6). is it possible? I try to downloaded SunStudio c/c++ compiler ... but it is not free... and the administration don't have it thaks
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On 6/25/2010 4:22 AM, John Gage wrote:
There are features, are there not, that Postgres has that MySQL does not
have?
Yes, a big one would be data integrity. Most people would not consider
data integrity an optional feature in a DBMS, but apparently MySQL does.
Try this in MySQL:
create table t1 (f1 varchar(10))
insert into t1 values('this is a long string')
select * from t1
When I do this on a version 5.0.44 MySQL DB (the latest I have
conveniently available), MySQL processes the insert without complaint,
silently truncating the string to "this is a". Why people aren't
bothered by this is beyond me.
--
Guy Rouillier
--
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] PG dump and restore
On Friday 25 June 2010 4:59:17 pm Dennis C wrote: > OK well the gunzip seemed to "do the trick," but I don't recall before > having to do anything other than run the pg_restore command. Anyway, > thanks to everyone for all your help! My guess is that previously the dump command was actually: /opt/local/lib/postgresql84/bin/pg_dump -Fc -f ./Trading-Access -Z 5 Trading-Access -- 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] PG dump and restore
OK well the gunzip seemed to "do the trick," but I don't recall before having to do anything other than run the pg_restore command. Anyway, thanks to everyone for all your help! On Fri, Jun 25, 2010 at 11:29 AM, Adrian Klaver wrote: > On 06/25/2010 09:04 AM, Dennis C wrote: > >> It says "Trading-Access: gzip compressed data, from Unix" >> >> About the idea of not using pg_restore for these dumps, what I'm still >> missing is how it's worked for all these years before. Are there now more >> stringent standards being enforced? >> >> >> > You have restored from these dumps using pg_restore? > > The command below says create a plain text file that has commands to clean > database objects before recreating and store text in file ./Trading-Access > using gzip compression at level 5: > > > /opt/local/lib/postgresql84/bin/pg_dump -c -f ./Trading-Access -Z 5 > Trading-Access > > To restore I would think you need to gunzip ./Trading-Access and then feed > the file to psql. > > > -- > Adrian Klaver > adrian.kla...@gmail.com >
Re: [GENERAL] pl-perl for 64 bits in Solaris 9
On 06/25/10 2:21 PM, MOLINA BRAVO FELIPE DE JESUS wrote: Hi I don't have experience with solaris. I know it is a 64 bits: bash-2.05# isainfo -v 64-bit sparcv9 applications 32-bit sparc applications I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for 64 bits. The options for the configure are: /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml --with-libxslt I would use the SunStudio c/c++ compiler for Sun Sparc, especially 64bit sparc. All the sun stuff is built with Sun Studio (/opt/SUNWspro/bin/cc etc). the perl thats built into solaris 9 is likely a little old, and may not be 64bit, I'm not sure. k, on my sol9 sparc legacy test system(a V240), /usr/bin/perl is 32bit. There is no /usr/bin/sparcv9/perl so, you'll need to build your own 64bit perl if you want to link it to a 64bit postgres. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl-perl for 64 bits in Solaris 9
Hi I don't have experience with solaris. I know it is a 64 bits: bash-2.05# isainfo -v 64-bit sparcv9 applications 32-bit sparc applications I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for 64 bits. The options for the configure are: /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml --with-libxslt but when I do "make" get the next message: /port -L/usr/local/lib -o plpgsql.so make[4]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql/src' make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql' make[3]: Entering directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' /usr/local/bin/gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -shared plperl.o spi_internal.o SPI.o -L/usr/lib/sparcv9 -L/usr/local/lib -L../../../src/port -L/usr/local/lib -m64 -lsocket -lnsl -ldl -lm -lc -Wl,-R'/usr/local/lib/perl5/5.8.9/sun4-solaris-64/CORE' -o plperl.so ld: fatal: file plperl.o: wrong ELF class: ELFCLASS32 ld: fatal: File processing errors. No output written to plperl.so collect2: ld returned 1 exit status make[3]: *** [plperl.so] Error 1 make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src' make: *** [all] Error 2 then i do: bash-2.05# file src/pl/plperl/plperl.o src/pl/plperl/plperl.o: ELF 32-bit MSB relocatable SPARC Version 1 My questions are: is possible compile postgres with pl-perl for 64? where i can read more about this problem? thank's -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl-perl for 64 bits in Solaris 9
Hi I don't have experience with solaris. I know it is a 64 bits: bash-2.05# isainfo -v 64-bit sparcv9 applications 32-bit sparc applications I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for 64 bits. The options for the configure are: /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml --with-libxslt but when I do "make" get the next message: /port -L/usr/local/lib -o plpgsql.so make[4]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql/src' make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql' make[3]: Entering directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' /usr/local/bin/gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -shared plperl.o spi_internal.o SPI.o -L/usr/lib/sparcv9 -L/usr/local/lib -L../../../src/port -L/usr/local/lib -m64 -lsocket -lnsl -ldl -lm -lc -Wl,-R'/usr/local/lib/perl5/5.8.9/sun4-solaris-64/CORE' -o plperl.so ld: fatal: file plperl.o: wrong ELF class: ELFCLASS32 ld: fatal: File processing errors. No output written to plperl.so collect2: ld returned 1 exit status make[3]: *** [plperl.so] Error 1 make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src' make: *** [all] Error 2 then i do: bash-2.05# file src/pl/plperl/plperl.o src/pl/plperl/plperl.o: ELF 32-bit MSB relocatable SPARC Version 1 My questions are: is possible compile postgres with pl-perl for 64? where i can read more about this problem? thank's
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, 25 Jun 2010 08:48:11 -0700 Rob Wultsch wrote: > The freedom of the storage engine interface allows for much more > varied backend characteristics. Some examples: This is *really* fascinating but pg transactional engine is very mature and solid. Before any of the should-be-really-transactional-engine of MySQL will be as mature as pg's one, pg will have a lot more feature/speed/development sugar. If I didn't need a transactional engine I'd be happy to go (well not really happy[1]) with MySQL and any of its engines. It is nice to reuse and mix and match different tools. [1] I find some inconsistency of mysql a bit painful to live with... as I find some inconsistency in PHP equally irritating but they are there, quite diffused and still for certain domains the best compromise available. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help on extracting composite array element in ‘C’
I am having an issue with extracting data from the arguments within my ‘C’
function, inside my Stored Library.
Let’s put the pieces together first:
I have a type called rank_post which has two one character fields:
CREATE TYPE rank_post AS
(
rank character(1),
post character(1)
);
I am using the following select statement to call the stored library
function.
select
rotation(array[('5','A')::rank_post,('1','Z')::rank_post,('2','S')::rank_post,('3','D')::rank_post]);
So inside my stored library function I have
typedef struct {
char rank;
char post;
} TdbRank;
Datum rotation(PG_FUNCTION_ARGS)
{
ArrayType *attr_arr = PG_GETARG_ARRAYTYPE_P(0);
Oidattr_element_type = ARR_ELEMTYPE(attr_arr);
intattr_ndims= ARR_NDIM(attr_arr);
int *attr_dim_counts = ARR_DIMS(attr_arr);
int *attr_dim_lower_bounds = ARR_LBOUND(attr_arr);
intncols = attr_dim_counts[0];
int16 attr_len;
bool attr_byval;
char attr_align;
intindx[MAXDIM];
bool isNull;
TdbRankitem[ncols];
IntxItem;
Datum datumResult;
get_typlenbyvalalign(attr_element_type, &attr_len, &attr_byval,
&attr_align);
for(xItem = 0; xItem < ncols; xItem++) {
indx[0] = xItem + attr_dim_lower_bounds[0];
datumResult = array_ref(attr_arr, attr_ndims, indx, -1, attr_len,
attr_byval, attr_align, &isNull);
? /* how do I extract the data */
item[xItem].rank = ?;
item[xItem].post = ?;
}
...
}
After reading for an extended period of time, and trying what I thought was
right, I am still at a loss on how to extract the data out. When I print
the the ndims and ncols I do get the right results of 1 and 4, and when I
print the hex address each datum each is 32 bytes.
I am not sure what more someone needs to help, but this is the most
information I have and can release. So if you can help, it would be greatly
appreciated.
Re: [GENERAL] PG dump and restore
On 06/25/2010 09:04 AM, Dennis C wrote: It says "Trading-Access: gzip compressed data, from Unix" About the idea of not using pg_restore for these dumps, what I'm still missing is how it's worked for all these years before. Are there now more stringent standards being enforced? You have restored from these dumps using pg_restore? The command below says create a plain text file that has commands to clean database objects before recreating and store text in file ./Trading-Access using gzip compression at level 5: /opt/local/lib/postgresql84/bin/pg_dump -c -f ./Trading-Access -Z 5 Trading-Access To restore I would think you need to gunzip ./Trading-Access and then feed the file to psql. -- 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] Equivalent to "use database" in postgre
On 06/25/10 4:10 AM, javijava wrote: i need to know how to do a simple script that create a database,the y select it (in other languajes using USE) and after create tables with this database. How can I say "use name_database" on postgre sql? Thanks in advance 4 the help!! IF you are using the psql command line utility to execute these scripts, then \c dbname will connect to a new database. However, if you're processing these scripts some other way, then your app will have to disconnect from the one database and connect to the other itself (this is what the \c command tells psql to do) -- 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] Backup issues
On Fri, 2010-06-25 at 23:24 +0530, RP Khare wrote: > I never used PostgreSQL in production environment. Now I got an > opportunity to migrate a MySQL production database to PostgreSQL. > Before migrating, I have few queries on data recovery: > > 1. Is there any feature of scheduled backups? You can schedule backups anyway you like :). If you are on Windows I believe you can use the job agent stuff in contrib. That might make your life easier. Otherwise you can write a batch file. > 1. In case there is no backup and I want to shift my data files > to a new PC, how to do that? You have to move the whole cluster. (Your data directory) > 1. Are the Redo Logs enabled by default? Yes, its called WAL. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backup issues
I never used PostgreSQL in production environment. Now I got an opportunity to migrate a MySQL production database to PostgreSQL. Before migrating, I have few queries on data recovery: Is there any feature of scheduled backups?In case there is no backup and I want to shift my data files to a new PC, how to do that?Are the Redo Logs enabled by default? Regards, Rohit Prakash _ Bollywood, beauties and the latest flicks on MSN entertainment http://entertainment.in.msn.com/
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 09:44:04AM +0100, Dave Page wrote: > It could also be argued that having a storage engine API means that > the query planner/optimiser cannot have nearly as much knowledge > about how the data is stored and what access characteristics it may > have thus preventing it from being as well optimised as Postgres. Having it divided off at the place where it's divided in MySQL is certainly such a barrier. Having a storage API, as PostgreSQL used to have, and will have again with SQL/MED, doesn't necessarily present such a barrier. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Equivalent to "use database" in postgre
On Fri, Jun 25, 2010 at 10:00 AM, Tom Lane wrote: > Scott Marlowe writes: > > On Fri, Jun 25, 2010 at 7:10 AM, javijava > wrote: > >> How can I say "use name_database" on postgre sql? > > > You have to re-connect to use a different db in pgsql. > > But keep in mind that mysql databases are more nearly akin to what > postgres calls schemas. The best way to do what you want might be > to translate your mysql DBs into schemas, and then "set search_path" > would be a good approximation to "use". > The issue here may be data isolation. Suppose you have two independent groups, one with database A and one with database B. (Think of a company that hosts databases for its customers, for example.) In the ideal situation, the people with access only to database A should not even be able to learn that database B exists, much less access it in any fashion. I believe PG does not have the capability to completely isolate other databases from users running on the same server, I'm not sure if MySQL does or not. However, if there are any times when it might be necessary to query both database A and database B, then schemas are the way to go. -- Mike Nolan PG user by choice MySQL user by necessity
Re: [GENERAL] flatten pg_auth_members
On Jun 23, 2010, at 6:01 PM, A.M. wrote: > Hello, > > I am trying to make a query which will flatten pg_auth_members into a table > with two columns "user" and "group" which will recurse inherited roles so > that each login role is associated once with any inherited roles (assuming > all associated roles are inherited). > > This query does not do what I want, but I can't quite wrap my head around the > recursion part: > > WITH RECURSIVE usergroups(user_id,group_id) AS ( > SELECT am.member AS user_id,am.roleid AS group_id FROM pg_auth_members > AS am > UNION > SELECT am.member AS user_id,am.roleid AS group_id FROM usergroups AS > u,pg_auth_members AS am WHERE am.roleid=u.group_id > ) > SELECT r.user_id,r.group_id FROM usergroups AS r; > > For a role inheritance tree of "bob (1)"->"manager(2)"->"employee(3)", I > would like to see: > > user | group > 1 | 2 > 1 | 3 Hm- I wasn't able to figure out the WITH RECURSIVE construct, so I used a cartesian product instead: SELECT DISTINCT am1.member, (SELECT a2.rolname FROM pg_authid AS a2 WHERE a2.oid=am1.member), am2.roleid, (SELECT a3.rolname FROM pg_authid AS a3 WHERE a3.oid=am2.roleid) FROM pg_auth_members AS am1,pg_auth_members AS am2 WHERE pg_has_role(am1.member,am2.roleid,'MEMBER') UNION SELECT am1.member, (SELECT a2.rolname FROM pg_authid AS a2 WHERE a2.oid=am1.member), am1.member, (SELECT a2.rolname FROM pg_authid AS a2 WHERE a2.oid=am1.member) FROM pg_auth_members AS am1; Cheers, M -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 8:56 AM, Scott Marlowe wrote: > On Fri, Jun 25, 2010 at 11:48 AM, Rob Wultsch wrote: >> - Innodb : The primary transactional storage engine for MySQL. It does >> not have all the features of PG (like check contraints), but it has >> some features (like Compression!!!) which are *exceptionally* useful. > > You do know that pg has compression for text types built in already, > right? I'm sure there are subtle differences in the way compression > is done in each engine, just pointing that out. > I do, but TOAST is a very different animal than compression at the page level. The innodb buffer pool is also effectively compressed which allows for far greater use of memory. -- Rob Wultsch wult...@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] PG dump and restore
It says "Trading-Access: gzip compressed data, from Unix" About the idea of not using pg_restore for these dumps, what I'm still missing is how it's worked for all these years before. Are there now more stringent standards being enforced? On Fri, Jun 25, 2010 at 8:37 AM, Thom Brown wrote: > On 25 June 2010 16:28, Dennis C wrote: > > Greetings; > > As I've been doing for quite some time, backed up my database as > > such: /opt/local/lib/postgresql84/bin/pg_dump -c -f ./Trading-Access -Z 5 > > Trading-Access > > But then when I tried restoring it my usual way as > > such: /opt/local/lib/postgresql84/bin/pg_restore -d Trading-Access > > ./Trading-Access > > it causes the error: pg_restore: [archiver] input file does not appear to > be > > a valid archive > > So in trying: /opt/local/lib/postgresql84/bin/psql Trading-Access < > > ./Trading-Access > > it causes the error: invalid byte sequence for encoding "UTF8": 0x8b > > Thanks, > > If the output file is in custom format, that last step shouldn't work > since custom format isn't parsable by psql. That would only work if > it was dumped in plain format. > > Your original dump specifies a compression level, but you didn't > specify "-F c" for custom format, which would use the compression > level. > > Is your file human readable? Try running "file Trading-Access" on the > file. What does it say? > > Thom >
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 4:58 AM, Scott Marlowe wrote: > Next up: PostgreSQL stores its system catalogs in transaction safe > table types, like everything else it stores. MySQL stores its table > defs in myisam, even if the whole of the db you create is innodb and > innodb is the default. System crash in the middle of DDL? Might lose > a table or two. This is not true. MySQL stores users, acl, etc in MyISAM tables. In almost all setups users and acl do not change often so the crash sensitivity is not a big issue. I have dealt with (and still do deal with) horribly abused MySQL instances and I very very rarely run into issues with corruption on the system schema. MyISAM in not involved in the storage of data about Innodb. MySQL table definitions are stored in .frm files. Alterations to table definitions in MySQL (in general) are done by building a temporary table with the new definitions and the existing data and then shell gaming the files in. It is possible to have issues from a crash, but it is very rare. > > Next up: MySQL has optimizations made without proper testing. For > example, see this bug: > > http://bugs.mysql.com/bug.php?id=28591 > > This "optimization" made MySQL ignore the DESC keywork in innodb > tables. It was pushed into live, production ready MySQL code > midstream in version 5.0.28 on 2007-08-02. MySQL GA (i.e. production > ready) release had been made two years previously in March of 2005. > > Fix was pushed out on 2007-09-24, nearly 60 days later, to version > 5.0.48. Problem solved right? Well, not really, according to > http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed. The > actual fix gets pushed out on 2008-09-12. > > This shows several things about the MySQL release philosophy, at least > at the time. 1: Introducing performance enhancments without thorough > testing in a production release is A-OK. 2: The fix may or may not > actually work when it does get applied. and 3: It can take about a > year to get that fix in place. > > Things may be a LOT better by now. I'd certainly hope so. But I have > no real confidence or evidence of such an internal change. Sun/Oracle has improved things a lot. Long standing bugs are being closed and it feels like more care is being put into releases. -- Rob Wultsch wult...@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] PG dump and restore
On Fri, 2010-06-25 at 08:28 -0700, Dennis C wrote: > Greetings; > > > As I've been doing for quite some time, backed up my database as > such: /opt/local/lib/postgresql84/bin/pg_dump -c -f ./Trading-Access > -Z 5 Trading-Access > > > But then when I tried restoring it my usual way as > such: /opt/local/lib/postgresql84/bin/pg_restore -d > Trading-Access ./Trading-Access > it causes the error: pg_restore: [archiver] input file does not appear > to be a valid archive You don't use pg_restore for plain text dumps which is what you are doing above. You use psql. > > > So in trying: /opt/local/lib/postgresql84/bin/psql Trading-Access > < ./Trading-Access > it causes the error: invalid byte sequence for encoding "UTF8": 0x8b It means the database you has characters in it that are not UTF8 compliant. You can use iconv to clean it up. Joshua D. Drake > > > Thanks, -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 11:48 AM, Rob Wultsch wrote: > - Innodb : The primary transactional storage engine for MySQL. It does > not have all the features of PG (like check contraints), but it has > some features (like Compression!!!) which are *exceptionally* useful. You do know that pg has compression for text types built in already, right? I'm sure there are subtle differences in the way compression is done in each engine, just pointing that out. -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 1:44 AM, Dave Page wrote: > On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch wrote: >> MySQL has several full text search solutions. The built in MyISAM >> solution is the best known, but there is also an engine for using >> sphinx. >> >> ... >> >> And there are features that MySQL has that PG does not. Index only >> queries is a massive feature. Pluggable backend storage engines are >> another. > > Some might argue that is not a feature. Sure, it means you can have > different types of storage, but it means the feature set gets > fragmented - for example, if you want text search, you use MyISAM, but > if you want relational integrity you have to use InnoDB or some other > backend. You want both? Oh. Hmmm. > > It could also be argued that having a storage engine API means that > the query planner/optimiser cannot have nearly as much knowledge about > how the data is stored and what access characteristics it may have > thus preventing it from being as well optimised as Postgres. > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise Postgres Company > In many cases this criticism would be correct with the current interface. Drizzle has already changed the interface and is looking to allow more knowledge to be passed back to planner. In a few years MySQL like systems may have as much knowledge as PG does. The freedom of the storage engine interface allows for much more varied backend characteristics. Some examples: - NDB: A GPL'ed distributed highly redundant transactional storage engine for MySQL that can non-impactfully survive the lose of servers. I know of no PG equivilant. - TokuDB and Infobright: Data warehousing backends. The alternatives data warehousing forks based on PG that I know of are true forks which end up diverging significantly from the mainline. With MySQL the esoteric backends can stay current with mainline easily. IIRC both have incorporated new planner'ish features. - Sphinx : Fulltext indexing in MySQL done right. - CSV : A SQL interface to CRUD CSV. I know of no comparable in PG. - Blitzdb :An interesting new non-transactional engine that has recently been merged into Drizzle. - Innodb : The primary transactional storage engine for MySQL. It does not have all the features of PG (like check contraints), but it has some features (like Compression!!!) which are *exceptionally* useful. The backend being seperate from the core has in the last few years allowed significant features additions/changes (thank you Oracle). PostGIS is somewhat similar in how it is decoupled from core. - Blackhole : A storage engine that does not actually store data. I have used this for a variety of purposes including making ORM happy and allowing the dropping of parts of an application backend without breaking the application completely. To some in the MySQL community much of the most interesting development has happened outside of core. I guess the crux of my point is that storage engine interface allows for many features that are not found in PG. -- Rob Wultsch wult...@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] PG dump and restore
On 25 June 2010 16:28, Dennis C wrote: > Greetings; > As I've been doing for quite some time, backed up my database as > such: /opt/local/lib/postgresql84/bin/pg_dump -c -f ./Trading-Access -Z 5 > Trading-Access > But then when I tried restoring it my usual way as > such: /opt/local/lib/postgresql84/bin/pg_restore -d Trading-Access > ./Trading-Access > it causes the error: pg_restore: [archiver] input file does not appear to be > a valid archive > So in trying: /opt/local/lib/postgresql84/bin/psql Trading-Access < > ./Trading-Access > it causes the error: invalid byte sequence for encoding "UTF8": 0x8b > Thanks, If the output file is in custom format, that last step shouldn't work since custom format isn't parsable by psql. That would only work if it was dumped in plain format. Your original dump specifies a compression level, but you didn't specify "-F c" for custom format, which would use the compression level. Is your file human readable? Try running "file Trading-Access" on the file. What does it say? Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG dump and restore
Greetings; As I've been doing for quite some time, backed up my database as such: /opt/local/lib/postgresql84/bin/pg_dump -c -f ./Trading-Access -Z 5 Trading-Access But then when I tried restoring it my usual way as such: /opt/local/lib/postgresql84/bin/pg_restore -d Trading-Access ./Trading-Access it causes the error: pg_restore: [archiver] input file does not appear to be a valid archive So in trying: /opt/local/lib/postgresql84/bin/psql Trading-Access < ./Trading-Access it causes the error: invalid byte sequence for encoding "UTF8": 0x8b Thanks,
Re: [GENERAL] Waarschuwing na pg_dumpall en restore
Excerpts from Dick Kniep's message of vie jun 25 04:54:15 -0400 2010: > PROBLEM 2 > > > > Furthermore the following errors are shown: > > > > psql:db03.ak1.sql:251166: ERROR: column a.transaction does not exist > LINE 2: SELECT (a.relation)::regclass AS "table", a."transaction... > ^ > psql:db03.ak1.sql:251169: ERROR: relation "public.locks_tb_aux" does not > exist These are probably views constructed on top of system tables or views that changed between 8.1 and 8.3. You probably need to investigate the "column foo does not exist" ones; my guess is that fixing those would let subsequent commands that are failing with "relation foo does not exist" to be automatically fixed. The FK problems may be caused by problems 1 or 2, but my guess is that they are actually caused by a real problem in your data. Have a look at the data in the original database; you should be able to find missing rows in the referenced tables. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Need Some Recent Information on the Differences between Postgres and MySql
Jim Montgomery writes: > Remove me from your email chain. Jim, are you trying to win the Jerk of the Month award? There's an unsubscribe link on every PG list message. All you're accomplishing with this is to annoy other list members who cannot unsubscribe you. regards, tom lane -- 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] Equivalent to "use database" in postgre
On 25/06/10 19:10, javijava wrote: > > Hi, > > i'm newby in postgre sql world. > > i need to know how to do a simple script that create a database,the y > select it (in other languajes using USE) and after create tables with this > database. http://wiki.postgresql.org/wiki/FAQ http://www.coderholic.com/postgresql-for-mysql-users/ Most MySQL users misunderstand "databases" in postgresql. The closest equivalent in PostgreSQL to a MySQL "database" is a PostgreSQL "schema". If you expect to be able to run queries that use data from multiple "databases" you really want to use schema. See the help for the "psql" command for basic scripting, including the "\c" command to connect to another DB. For help on an SQL command, run "\h COMMANDNAME" in psql, or read the manual for that command. -- 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
Re: [GENERAL] Equivalent to "use database" in postgre
On Jun 25, 2010, at 10:48 AM, Scott Marlowe wrote: > On Fri, Jun 25, 2010 at 7:10 AM, javijava wrote: >> >> Hi, >> >> i'm newby in postgre sql world. >> >> i need to know how to do a simple script that create a database,the y >> select it (in other languajes using USE) and after create tables with this >> database. >> >> >> How can I say "use name_database" on postgre sql? > > You have to re-connect to use a different db in pgsql. And to elaborate a little more, the true need for "use database" for MySQL is not truly needed in most other RDBMS solutions. In MySQL this allows you to use different database engines for different databases. It also allowed you to keep your project independent of others. Where more robust engines use schema and tablespace concepts to achieve this for you. So, I would not implement a re-connect theory, I would suggest researching the database(s) you plan on supporting and seeing if there is a better way to leverage that engine for your needs. Which means, you need to know your needs - do you need data separation? Or, are you just used to sending the "use database" command? Ozz -- 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] Equivalent to "use database" in postgre
Scott Marlowe writes: > On Fri, Jun 25, 2010 at 7:10 AM, javijava wrote: >> How can I say "use name_database" on postgre sql? > You have to re-connect to use a different db in pgsql. But keep in mind that mysql databases are more nearly akin to what postgres calls schemas. The best way to do what you want might be to translate your mysql DBs into schemas, and then "set search_path" would be a good approximation to "use". regards, tom lane -- 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] Equivalent to "use database" in postgre
javijava wrote: > i'm newby in postgre sql world. > i need to know how to do a simple script that create a database,the y > select it (in other languajes using USE) and after create tables with this > database. > How can I say "use name_database" on postgre sql? You must specify the database to use on connect; if you want to use psql for your script, you can use "\c name_database" à la: | CREATE DATABASE testdatabase; | \c testdatabase | CREATE TABLE testtable (testcolumn int); Tim -- 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] Equivalent to "use database" in postgre
On Fri, Jun 25, 2010 at 7:10 AM, javijava wrote: > > Hi, > > i'm newby in postgre sql world. > > i need to know how to do a simple script that create a database,the y > select it (in other languajes using USE) and after create tables with this > database. > > > How can I say "use name_database" on postgre sql? You have to re-connect to use a different db in pgsql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Warnings after pg_dumpall > restore
Hi List, Thanks for this great product. We have a database with many schema's and we are trying to migrate the db from version 8.1.9 to 8.3.8. So we made a pg_dumpall. Now, when we try to restore with psql, we get loads of errors and warnings: PROBLEM 1 psql:db03.ak1.sql:10155: WARNING: column "plan_status" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "hplan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "stp_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_datum_start" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_datum_eind" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_inspanning" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "real_datum_start" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "real_datum_eind" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "med_uitvoering" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "conflict" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "conflict_text" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "handmatig_actief" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "mijlpaal" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "handmatig_groep" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "mijlpaaldatum" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_pro_noshow_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "doc_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "plan_status" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "plan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "hplan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "stp_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "plan_datum_start" has type "unknown" DETAIL: Proceeding with relation creation anyway. From earlier reports on the mailinglist, I assume it is a view that has no explicit cast. What are the consequences of these messages for the system? Can I ignore the warnings and simply correct the views later? PROBLEM 2 Furthermore the following errors are shown: psql:db03.ak1.sql:251166: ERROR: column a.transaction does not exist LINE 2: SELECT (a.relation)::regclass AS "table", a."transaction... ^ psql:db03.ak1.sql:251169: ERROR: relation "public.locks_tb_aux" does not exist psql:db03.ak1.sql:251176: ERROR: relation "locks_tb_aux" does not exist psql:db03.ak1.sql:251179: ERROR: relation "public.locks_tb" does not exist psql:db03.ak1.sql:251186: ERROR: column a.transaction does not exist LINE 2: SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi... ^ psql:db03.ak1.sql:251189: ERROR: relation "public.locks_tr_aux" does not exist psql:db03.ak1.sql:251196: ERROR: relation "locks_tr_aux" does not exist psql:db03.ak1.sql:251199: ERROR: relation "public.locks_tr" does not exist psql:db03.ak1.sql:251206: ERROR: column a.transaction does not exist LINE 2: SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi... ^ psql:db03.ak1.sql:251209: ERROR: relation "public.locks_trid_aux" does not exist psql:db03.ak1.sql:251216: ERROR: relation "locks_trid_aux" does not exist psql:db03.ak1.sql:251219: ERROR: relation "public.locks_trid" does not exist These look to me as system tables and are certainly not defined by us. Can I ignore these messages? PROBLEM 3 We get some messages that referential integrity rules (foreign keys) are violated. How can that be? This undermines my confidence in the system!! This would imply that t
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On Thu, Jun 24, 2010 at 10:03 PM, Jim Montgomery wrote: > Remove me from your email chain. > Remove yourself. -- Rob Wultsch wult...@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] Need Some Recent Information on the Differences between Postgres and MySql
Remove me from your email chain.
> Date: Thu, 24 Jun 2010 21:57:15 -0400
> Subject: Re: [GENERAL] Need Some Recent Information on the Differences
> between Postgres and MySql
> From: scott.marl...@gmail.com
> To: wult...@gmail.com
> CC: mary.y.w...@boeing.com; pgsql-general@postgresql.org
>
> On Thu, Jun 24, 2010 at 9:46 PM, Rob Wultsch wrote:
> > On Thu, Jun 24, 2010 at 6:13 PM, Scott Marlowe
> > wrote:
> >> For instant, by default, this will work in mysql:
> >>
> >> create table test (i int);
> >> insert into test (i) values ('');
> >>
> >> with a warning, but will produce an error in most modern versions of pgsql.
> >>
> >
> > However it is easy to get mostly sane behavior from MySQL:
> >
> > mysql> set sql_mode='strict_all_tables';
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql> create table test (i int);
> > Query OK, 0 rows affected (0.05 sec)
> >
> > mysql> insert into test (i) values ('');
> > ERROR 1366 (HY000): Incorrect integer value: '' for column 'i' at row 1
>
> Now if there were just a way to turn it on and not let the user turn it off...
>
> > If it were me I would generally work with whichever system I knew
> > better unless there was a specific reason to migrate. Both systems
> > will be a bit of a pain as they are both complicated. C'est la vie.
> >
> > All else being equal I would start a new project with PG.
>
> Agreed. I find that PostgreSQL tends to teach you fewer bad habits and
> MySQL does.
>
> > Full disclosure: I am a MySQL DBA.
>
> I'm a pgsql DBA...
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
_
http://clk.atdmt.com/UKM/go/195013117/direct/01/
We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now
[GENERAL] Equivalent to "use database" in postgre
Hi, i'm newby in postgre sql world. i need to know how to do a simple script that create a database,the y select it (in other languajes using USE) and after create tables with this database. How can I say "use name_database" on postgre sql? Thanks in advance 4 the help!! -- View this message in context: http://old.nabble.com/Equivalent-to-%22use-database%22-in-postgre-tp28990943p28990943.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 2:02 PM, Jim Montgomery wrote: > Remove me from your email chain. You need to unsubscribe from the mailing list. Until now your weren't explicitly included on any of the messages that I can see. http://www.postgresql.org/community/lists/ -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Need Some Recent Information on the Differences between Postgres and MySql
Remove me from your email chain! > Date: Fri, 25 Jun 2010 08:13:36 -0400 > Subject: Re: [GENERAL] Need Some Recent Information on the Differences > between Postgres and MySql > From: scott.marl...@gmail.com > To: jsmg...@numericable.fr > CC: wult...@gmail.com; mary.y.w...@boeing.com; pgsql-general@postgresql.org > > On Fri, Jun 25, 2010 at 7:58 AM, Scott Marlowe > wrote: > > This shows several things about the MySQL release philosophy, at least > > at the time. 1: Introducing performance enhancments without thorough > > testing in a production release is A-OK. 2: The fix may or may not > > actually work when it does get applied. and 3: It can take about a > > year to get that fix in place. > > > > Things may be a LOT better by now. I'd certainly hope so. But I have > > no real confidence or evidence of such an internal change. > > Note that I have a lot of respect for the Drizzle team and what > they're doing with MySQL's code base. It seems like they "get it" as > a team, while MySQL never did. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ http://clk.atdmt.com/UKM/go/19780/direct/01/ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
Remove me from your email chain. > Date: Fri, 25 Jun 2010 10:44:34 +0100 > Subject: Re: [GENERAL] Need Some Recent Information on the Differences > between Postgres and MySql > From: dp...@pgadmin.org > To: andreas.kretsch...@schollglas.com > CC: pgsql-general@postgresql.org > > On Fri, Jun 25, 2010 at 10:22 AM, A. Kretschmer > wrote: > > In response to Dave Page : > >> On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown wrote: > >> > >> > Didn't PostgreSQL used to have more than 1 storage engine in the past? > >> > I thought I read somewhere it did, but it was decided it was a > >> > compromise on stability and/or quality, so ended up using a single > >> > kick-ass engine? > >> > >> Yes, many, many moons ago. > > > > Really? Do you have a link? > > Hmm, I think I misread Thom's question. The smgr API used to be far > more rigidly designed as I understand it, to allow the possibility of > having different storage engines (for example, maybe one that used raw > devices). I don't know that any other storage engines were ever > actually written though. > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise Postgres Company > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ http://clk.atdmt.com/UKM/go/19780/direct/01/ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
Dave Page writes: > Hmm, I think I misread Thom's question. The smgr API used to be far > more rigidly designed as I understand it, to allow the possibility of > having different storage engines (for example, maybe one that used raw > devices). I don't know that any other storage engines were ever > actually written though. There actually were two smgr storage modules in the code we inherited from Berkeley, and I think there were probably more at one time. But the smgr interface is *way* lower level than mysql's storage engines; there is not that much that you can do to affect the behavior of the DB by replacing an smgr module. I believe what they had in mind originally was to be able to drive different physical storage devices, using raw access instead of going through the filesystem. That decision was taken before everything of interest got unified under the Unix filesystem API. These days, if you needed to do what they had in mind, you'd be writing a kernel device driver instead. So smgr is pretty vestigial, and we've largely broken its API abstraction anyway by doing filesystem access directly in so many other places. regards, tom lane -- 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] Hide the code from users postgres
Thank you all for the responses. I got a requirement from my boss that I need to give read only access to only tables , so that users can write some queries. At that time, he also mentioned that the users should not see the code. I read the document , but not able to figure out how to do that and then posted the question for the help. Regards On Thu, Jun 24, 2010 at 6:04 PM, Dave Page wrote: > On Thu, Jun 24, 2010 at 10:59 PM, Allan Kamau > wrote: > > > Perhaps (I could be wrong here), there may be a way (even though I > > don't really support the obfuscation, vendor lockup etc... idea). > > 1)Use a commercial DB (as mentioned previously), they seem to have > > provided for this. > > 2)Use PostgreSQL and write all code into C functions and complied to a > > given PostgreSQL installation. > > You could do that. It's not exactly productive to write/rewrite all > your functions in C if pl/pgsql will work though, plus it means you > need to compile separate binaries for each for each platform your > customers use, and upgrades can no longer be pure SQL scripts, as > you'll also need to ship new object files and install them on the > database server. > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise Postgres Company > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
Thomas Kellerer, 25.06.2010 14:32: Wang, Mary Y, 25.06.2010 01:04: Hi, I'm trying to find some write-ups about the differences between Postgres and MySql. A lot of stuff showed up on Google, but most of them are old. I saw this wiki over here http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by EnterpriseDB. Are there any other most recent summaries on the differences between Postgres and MySql? Thanks in advance Mary My favorite features in Postgres that MySQL doesn't have - deferrable constraints - sequences - check constraints - windowing functions - recursive common table expressions - the absence of a program to check the consistency of the data - the ability to use a subselect in a DML statement that references the table to be updated - generate_series() - array handling And another thing: The following works in Postgres (and Oracle, DB2, SQL Server, Derby) but not in MySQL (using InnoDB): create table fktest ( idinteger primary key not null, name varchar(20), parent_id integer ); alter table fktest add constraint fktest_parent foreign key (parent_id) references fktest(id); insert into fktest (id,name,parent_id) values (1,'Root', null); insert into fktest (id,name,parent_id) values (2,'Sub1', 1); insert into fktest (id,name,parent_id) values (3,'Subsub', 2); insert into fktest (id,name,parent_id) values (4,'Sub2', 1); commit; delete from fktest where id in (1,2,3,4); commit; MySQL complains that it cannot delete the rows"Cannot delete or update a parent row: a foreign key constraint fails" Regards Thomas -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 6:58 AM, Scott Marlowe wrote: > > Biggest difference between MySQL and PostgreSQL? The developers. > > I like that... It has a nice ring to it.
[GENERAL] Windows7 and user-defined procedure
Hello!
I have user-defined procedure (C procedure) realised in dll
('c_some_text_dll'). It is called in the following way:
1. CREATE OR REPLACE FUNCTION "app_text" () RETURNS text AS
'$libdir/c_some_text_dll', 'getTextValueFromApplication'
LANGUAGE 'c' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
2. CREATE TABLE "log" (
"id" SERIAL,
"value" TEXT DEFAULT app_text() NOT NULL
)
So when called from my application some stored procedure containing 'INSERT
INTO log...' there was the result of 'app_text()' in the field 'value'.
It works in XP and Win2000. I'm trying to test in Windows 7 now and get an
error when inserting data in table 'log': "Could not open relation
base\16123\16222: No such file or directory".
The file '16222' exists and represents the table 'log' itself. All the
imaginable rights for files and directories are set - as it seems to me.
One more detail - if I call this procedure from some other client application,
f.e., psql (SELECT * FROM app_text()), before this INSERT - it begins to work
in my application too.
Are there any ideas? I'll appreciate any help.
Thanks, Marina.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
Wang, Mary Y, 25.06.2010 01:04: Hi, I'm trying to find some write-ups about the differences between Postgres and MySql. A lot of stuff showed up on Google, but most of them are old. I saw this wiki over here http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by EnterpriseDB. Are there any other most recent summaries on the differences between Postgres and MySql? Thanks in advance Mary My favorite features in Postgres that MySQL doesn't have - deferrable constraints - sequences - check constraints - windowing functions - recursive common table expressions - the absence of a program to check the consistency of the data - the ability to use a subselect in a DML statement that references the table to be updated - generate_series() - array handling Thomas -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 7:58 AM, Scott Marlowe wrote: > This shows several things about the MySQL release philosophy, at least > at the time. 1: Introducing performance enhancments without thorough > testing in a production release is A-OK. 2: The fix may or may not > actually work when it does get applied. and 3: It can take about a > year to get that fix in place. > > Things may be a LOT better by now. I'd certainly hope so. But I have > no real confidence or evidence of such an internal change. Note that I have a lot of respect for the Drizzle team and what they're doing with MySQL's code base. It seems like they "get it" as a team, while MySQL never did. -- 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] Waarschuwing na pg_dumpall en restore
On 25 Jun 2010, at 10:54, Dick Kniep wrote: > Hi List, > > Thanks for this great product. > > We have a database with many schema's and we are trying to migrate the db > from version 8.1.9 to 8.3.8. > > So we made a pg_dumpall. Using which version? The one that came with 8.3? I'm suspecting that you used the one from 8.1.9, which would cause problems if you try to restore that in an 8.3 database. Also, 8.3.8 isn't the latest minor version, there's a (minor) security fix in 8.3.11 for example. Since you're still upgrading, now would be a good time to pick the latest minor release of 8.3. > Now, when we try to restore with psql, we get loads of errors and warnings: > > PROBLEM 1 > > psql:db03.ak1.sql:10155: WARNING: column "plan_status" has type "unknown" > DETAIL: Proceeding with relation creation anyway. > psql:db03.ak1.sql:10155: WARNING: column "plan_id" has type "unknown" > DETAIL: Proceeding with relation creation anyway. ... > From earlier reports on the mailinglist, I assume it is a view that has no > explicit cast. No need to assume anything, the error shows you at which line the problem is, so you can verify whether the problem is indeed caused by a view definition. > What are the consequences of these messages for the system? > > Can I ignore the warnings and simply correct the views later? If these are about a view, yeah - it won't be used to alter data during the restore. > PROBLEM 2 > > Furthermore the following errors are shown: > > psql:db03.ak1.sql:251166: ERROR: column a.transaction does not exist > LINE 2: SELECT (a.relation)::regclass AS "table", a."transaction... > ^ > psql:db03.ak1.sql:251169: ERROR: relation "public.locks_tb_aux" does not > exist > psql:db03.ak1.sql:251176: ERROR: relation "locks_tb_aux" does not exist > psql:db03.ak1.sql:251179: ERROR: relation "public.locks_tb" does not exist > psql:db03.ak1.sql:251186: ERROR: column a.transaction does not exist > LINE 2: SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi... >^ > psql:db03.ak1.sql:251189: ERROR: relation "public.locks_tr_aux" does not > exist > psql:db03.ak1.sql:251196: ERROR: relation "locks_tr_aux" does not exist > psql:db03.ak1.sql:251199: ERROR: relation "public.locks_tr" does not exist > psql:db03.ak1.sql:251206: ERROR: column a.transaction does not exist > LINE 2: SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi... >^ > psql:db03.ak1.sql:251209: ERROR: relation "public.locks_trid_aux" does not > exist > psql:db03.ak1.sql:251216: ERROR: relation "locks_trid_aux" does not exist > psql:db03.ak1.sql:251219: ERROR: relation "public.locks_trid" does not exist > > These look to me as system tables and are certainly not defined by us. > > Can I ignore these messages? These are probably real problems that you shouldn't ignore, especially if they're related to system tables. I think these are caused by using the "old" version of pg_dumpall or psql to create the dump. It's quite possible that the design of some system tables has changed between the two versions, and the "old" pg_dumpall/psql have no way of knowing about that. The newer versions have. > > PROBLEM 3 > > We get some messages that referential integrity rules (foreign keys) are > violated. How can that be? This undermines my confidence in the system!! This > would imply that the foreign key at one time did not exist, BUT WHEN IT WAS > CREATED IT DID NOT COMPLAIN THAT THE RULES WERE VIOLATED... These are probably the results of previous errors. For example, because of these errors some data wasn't inserted while later data depends on it, causing a constraint violation. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c249b98286211487176982! -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 4:22 AM, John Gage wrote: > There are features, are there not, that Postgres has that MySQL does not > have? My favorite pgsql feature is partial and functional indexes. For instance, let's say you have a work queue, and in it you have a boole called processed. it is 99.999% true, and you need an index for quick lookup on the ones that are false. create index yada on table blah (id) where processed is false. Now any query that does select id from table blah where processed is false can use that index for quick lookups. Now supposed you want to lookup a table based on the first two characters of some field. create index yada on table blah (substring(textfield,1,2)); Next up: PostgreSQL stores its system catalogs in transaction safe table types, like everything else it stores. MySQL stores its table defs in myisam, even if the whole of the db you create is innodb and innodb is the default. System crash in the middle of DDL? Might lose a table or two. Next up: MySQL has optimizations made without proper testing. For example, see this bug: http://bugs.mysql.com/bug.php?id=28591 This "optimization" made MySQL ignore the DESC keywork in innodb tables. It was pushed into live, production ready MySQL code midstream in version 5.0.28 on 2007-08-02. MySQL GA (i.e. production ready) release had been made two years previously in March of 2005. Fix was pushed out on 2007-09-24, nearly 60 days later, to version 5.0.48. Problem solved right? Well, not really, according to http://bugs.mysql.com/bug.php?id=31001 it wasn't quite fixed. The actual fix gets pushed out on 2008-09-12. This shows several things about the MySQL release philosophy, at least at the time. 1: Introducing performance enhancments without thorough testing in a production release is A-OK. 2: The fix may or may not actually work when it does get applied. and 3: It can take about a year to get that fix in place. Things may be a LOT better by now. I'd certainly hope so. But I have no real confidence or evidence of such an internal change. Now compare that to pgsql bugs introduced that may clobber your data in some way. It's easy to do, look for a production release, followed by another production release within a day or two. It's happened about two times I can remember off the top of my head with pgsql. The new version comes out, a horrific bug is found, fix is made, new version release, old one pulled. For 8.1 it looks like there was a two day window where overly restrictive type length in functions and constraints snuck out. Two. Days. There was another release that was never actually released, might have had a show stopper in it too. Biggest difference between MySQL and PostgreSQL? The developers. -- 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] Re: Need Some Recent Information on the Differences between Postgres and MySql
Disabused. On Jun 25, 2010, at 11:59 AM, Thomas Kellerer wrote: Getting really off-topic now: but MySQL does support Regex http://dev.mysql.com/doc/refman/5.1/en/regexp.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Need Some Recent Information on the Differences between Postgres and MySql
John Gage, 25.06.2010 11:50: Replying to my own post, and on further examination of the MySQL documentation, I am astonished to discover that MySQL does not support regular expressions much less something like tsvector. Please disabuse me of this idea if I am mistaken. Getting really off-topic now: but MySQL does support Regex http://dev.mysql.com/doc/refman/5.1/en/regexp.html -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On 25 June 2010 10:50, John Gage wrote: > In the words of Dwight Eisenhower, I couldn't fail to disagree with you > less. That said... > > Replying to my own post, and on further examination of the MySQL > documentation, I am astonished to discover that MySQL does not support > regular expressions much less something like tsvector. Please disabuse me > of this idea if I am mistaken. > > To me, this turns MySQL into a toy. Regular expressions are an > extraordinarily powerful tool rooted in science that make manipulating text > data infinitely easier. To leave them out of a system (recall that the > Macintosh is based on Unix and supports egrep, for example, out of the box) > is unbelievably backward. > I still find it frustrating that I can't use Perl-style regular expressions in PostgreSQL though... although it might be the case that it does, and that I just don't know how to use it. Thom -- 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] Need Some Recent Information on the Differences between Postgres and MySql
In the words of Dwight Eisenhower, I couldn't fail to disagree with you less. That said... Replying to my own post, and on further examination of the MySQL documentation, I am astonished to discover that MySQL does not support regular expressions much less something like tsvector. Please disabuse me of this idea if I am mistaken. To me, this turns MySQL into a toy. Regular expressions are an extraordinarily powerful tool rooted in science that make manipulating text data infinitely easier. To leave them out of a system (recall that the Macintosh is based on Unix and supports egrep, for example, out of the box) is unbelievably backward. Why extirpate part of your brain if you don't have to? MySQL thus becomes part of Gödel's inferred conspiracy to make men stupid. John P.S. I am aware that MySQL has its own, roll your own, text search capability...which adds insult to injury. P. P. S. I realize that there is an element of flame here. However, the facts are the facts and anyone wanting to judge between Postgres and MySQL has to deal in facts. On Jun 25, 2010, at 11:37 AM, A. Kretschmer wrote: I think, this is the wrong place to explain mysql-features... -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 10:22 AM, A. Kretschmer wrote: > In response to Dave Page : >> On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown wrote: >> >> > Didn't PostgreSQL used to have more than 1 storage engine in the past? >> > I thought I read somewhere it did, but it was decided it was a >> > compromise on stability and/or quality, so ended up using a single >> > kick-ass engine? >> >> Yes, many, many moons ago. > > Really? Do you have a link? Hmm, I think I misread Thom's question. The smgr API used to be far more rigidly designed as I understand it, to allow the possibility of having different storage engines (for example, maybe one that used raw devices). I don't know that any other storage engines were ever actually written though. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Need Some Recent Information on the Differences between Postgres and MySql
In response to John Gage : > Forgive me for being somewhat stupid, but is MyISAM a text search > engine? The Wikipedia article doesn't make it sound like one. MyISAM provides textsearch and other features, but no referential integrity. It's just one of many storage engines. > > Could you be more specific as to how, for example, MySQL implements > regular expressions or the tsvector funcitionality? I think, this is the wrong place to explain mysql-features... Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Waarschuwing na pg_dumpall en restore
Hi List, Thanks for this great product. We have a database with many schema's and we are trying to migrate the db from version 8.1.9 to 8.3.8. So we made a pg_dumpall. Now, when we try to restore with psql, we get loads of errors and warnings: PROBLEM 1 psql:db03.ak1.sql:10155: WARNING: column "plan_status" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "hplan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "stp_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_datum_start" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_datum_eind" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_inspanning" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "real_datum_start" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "real_datum_eind" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "med_uitvoering" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "conflict" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "conflict_text" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "handmatig_actief" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "mijlpaal" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "handmatig_groep" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "mijlpaaldatum" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "plan_pro_noshow_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10155: WARNING: column "doc_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "plan_status" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "plan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "hplan_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "stp_id" has type "unknown" DETAIL: Proceeding with relation creation anyway. psql:db03.ak1.sql:10185: WARNING: column "plan_datum_start" has type "unknown" DETAIL: Proceeding with relation creation anyway. From earlier reports on the mailinglist, I assume it is a view that has no explicit cast. What are the consequences of these messages for the system? Can I ignore the warnings and simply correct the views later? PROBLEM 2 Furthermore the following errors are shown: psql:db03.ak1.sql:251166: ERROR: column a.transaction does not exist LINE 2: SELECT (a.relation)::regclass AS "table", a."transaction... ^ psql:db03.ak1.sql:251169: ERROR: relation "public.locks_tb_aux" does not exist psql:db03.ak1.sql:251176: ERROR: relation "locks_tb_aux" does not exist psql:db03.ak1.sql:251179: ERROR: relation "public.locks_tb" does not exist psql:db03.ak1.sql:251186: ERROR: column a.transaction does not exist LINE 2: SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi... ^ psql:db03.ak1.sql:251189: ERROR: relation "public.locks_tr_aux" does not exist psql:db03.ak1.sql:251196: ERROR: relation "locks_tr_aux" does not exist psql:db03.ak1.sql:251199: ERROR: relation "public.locks_tr" does not exist psql:db03.ak1.sql:251206: ERROR: column a.transaction does not exist LINE 2: SELECT a."transaction", a.pid AS pid_locked, b.pid AS pi... ^ psql:db03.ak1.sql:251209: ERROR: relation "public.locks_trid_aux" does not exist psql:db03.ak1.sql:251216: ERROR: relation "locks_trid_aux" does not exist psql:db03.ak1.sql:251219: ERROR: relation "public.locks_trid" does not exist These look to me as system tables and are certainly not defined by us. Can I ignore these messages? PROBLEM 3 We get some messages that referential integrity rules (foreign keys) are violated. How can that be? This undermines my confidence in the system!! This would imply that the
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
In response to Dave Page : > On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown wrote: > > > Didn't PostgreSQL used to have more than 1 storage engine in the past? > > I thought I read somewhere it did, but it was decided it was a > > compromise on stability and/or quality, so ended up using a single > > kick-ass engine? > > Yes, many, many moons ago. Really? Do you have a link? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Need Some Recent Information on the Differences between Postgres and MySql
Forgive me for being somewhat stupid, but is MyISAM a text search engine? The Wikipedia article doesn't make it sound like one. Could you be more specific as to how, for example, MySQL implements regular expressions or the tsvector funcitionality? John On Jun 25, 2010, at 10:33 AM, Rob Wultsch wrote: The built in MyISAM solution is the best known -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 9:52 AM, Thom Brown wrote: > Didn't PostgreSQL used to have more than 1 storage engine in the past? > I thought I read somewhere it did, but it was decided it was a > compromise on stability and/or quality, so ended up using a single > kick-ass engine? Yes, many, many moons ago. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On 25 June 2010 09:44, Dave Page wrote: > On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch wrote: >> MySQL has several full text search solutions. The built in MyISAM >> solution is the best known, but there is also an engine for using >> sphinx. >> >> ... >> >> And there are features that MySQL has that PG does not. Index only >> queries is a massive feature. Pluggable backend storage engines are >> another. > > Some might argue that is not a feature. Sure, it means you can have > different types of storage, but it means the feature set gets > fragmented - for example, if you want text search, you use MyISAM, but > if you want relational integrity you have to use InnoDB or some other > backend. You want both? Oh. Hmmm. > > It could also be argued that having a storage engine API means that > the query planner/optimiser cannot have nearly as much knowledge about > how the data is stored and what access characteristics it may have > thus preventing it from being as well optimised as Postgres. > Didn't PostgreSQL used to have more than 1 storage engine in the past? I thought I read somewhere it did, but it was decided it was a compromise on stability and/or quality, so ended up using a single kick-ass engine? Thom -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 9:33 AM, Rob Wultsch wrote: > MySQL has several full text search solutions. The built in MyISAM > solution is the best known, but there is also an engine for using > sphinx. > > ... > > And there are features that MySQL has that PG does not. Index only > queries is a massive feature. Pluggable backend storage engines are > another. Some might argue that is not a feature. Sure, it means you can have different types of storage, but it means the feature set gets fragmented - for example, if you want text search, you use MyISAM, but if you want relational integrity you have to use InnoDB or some other backend. You want both? Oh. Hmmm. It could also be argued that having a storage engine API means that the query planner/optimiser cannot have nearly as much knowledge about how the data is stored and what access characteristics it may have thus preventing it from being as well optimised as Postgres. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 1:22 AM, John Gage wrote: > There are features, are there not, that Postgres has that MySQL does not > have? > > I refer in particular to things like tsvector. > > Am I mistaken in this? > > John > > > On Jun 25, 2010, at 3:46 AM, Rob Wultsch wrote: > >> unless there was a specific reason to migrate > > MySQL has several full text search solutions. The built in MyISAM solution is the best known, but there is also an engine for using sphinx. ... And there are features that MySQL has that PG does not. Index only queries is a massive feature. Pluggable backend storage engines are another. MySQL is also somewhat simpler to tune. Both systems can work well. Both have advantages. Both can suck. As has been just demonstrated, both have communities that suck at mingling with the other major open source rdms. -- Rob Wultsch wult...@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] Need Some Recent Information on the Differences between Postgres and MySql
There are features, are there not, that Postgres has that MySQL does not have? I refer in particular to things like tsvector. Am I mistaken in this? John On Jun 25, 2010, at 3:46 AM, Rob Wultsch wrote: unless there was a specific reason to migrate -- 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] Need Some Recent Information on the Differences between Postgres and MySql
On Fri, Jun 25, 2010 at 12:04 AM, Wang, Mary Y wrote: > Hi, > > I'm trying to find some write-ups about the differences between Postgres and > MySql. A lot of stuff showed up on Google, but most of them are old. > I saw this wiki over here > http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009 and > plan to watch a recent webcast on PostgreSQL vs. MySQL that was offered by > EnterpriseDB. If you mean the one from last week, then that would be a good choice. It's got our chief architect discussing the topic with our newest member of the EDB team, Robin Schumacher, who spent many years working in a senior product management position at MySQL. http://www.enterprisedb.com/learning/postgresql_vs_mysql.do -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] The case of PostgreSQL on NFS Server
On Friday 25 June 2010 02:23:17 Iwao Shikase wrote: > Hi Roeleveld-san, > > Thank you for your advice. But My purpose is to test PostgreSQL > which data cluster is in NFS server. > As your says, Cluster Filesystem is one of answer of sharing files. > But my company still want to use NFS server. So I want to know > how to use PostgreSQL using NFS. > > By the way,The manual says, "if possible, mount NFS file systems > synchronously (without caching) to avoid this". > > I want to know what means synchronously with out caching. > In other word, which NFS options we must use follwoing the manual. > sync ? or noac ? or both ? > > Thank you , > Iwao Shikase Hi Shikase-san, I am not fully familiar with all the NFS mount options. What the manual means is that when using NFS (Or any network-file-system) is that you need to make sure that all write-activities are done in the sequence they are sent and also that there is no write-caching done anywhere. In other words, both on the NFS-client and on the NFS-server side, you need to be certain that write-caching is disabled and that all write-actions are in sync. I myself only use NFS for document-storage. All the storage for server- software (Database, email,) are all on local disks. If I would need to store them on a different system (NAS/SAN) then I would be using a sharing-method that exposes the raw disk, like the methods mentioned by Craig Ringer. What is the actual reason why your company wants to use an NFS server to store the database-files? If it's for backups, why not schedule backups to be written to the NFS server? This has the added benefit of being able to easily restore and upgrade to newer versions without too much hassle. -- Joost Roeleveld -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
