[GENERAL] Mysql to postgres tools -reviews?
I am looking for feedback from anyone that has moved their data from Mysql to Postgres using the tools in contrib/mysql or any other data porting tools on the net. Did any data get lost in the transfer? Recommendations? Amount of time for transfers? Any feedback would be greatly appreciated. Thanks in advance. GW ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Re: quick question: index optimisations on small tables
On Fri, 31 Aug 2001, Andrew Snow wrote: > Hrmm... I have 26 rows in mine at the moment, and after vacuum > analyzing, it uses a seq. scan. How come yours used the index? I > thought mine wasn't using an index because postgres won't use an index > until the table is "big enough". > > But if an index page is already in cache.. surely it'd be faster using > it than doing a seq. scan. > > (Yes, I know its a small table, but I think the worst case for seq. scan > would be a fair bit worse than for the index, and every little bit > counts, right?) If the table is small enough to fit in one page, a sequence scan across those rows may be faster than the index scan since the index scan will need to read two pages (one for the index, one for the heap -- the visibility info is only in the heap so that must be consulted for each index match) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] HELP WANTED: Open Source Consultant Programmer wanted for quick project
HELP WANTED: Open Source Consultant Programmer wanted for quick project We are in need of someone to quickly develop a new web project. You will need to be an expert on setting up Apache Web Server on a Linux system and in designing an online data base using Postgresql or MySQL. HTML required, PHP probably required, Java & Java Script may or may not be required. You will be totally responsible for taking this rough idea and implementing a working, fully scalable website. Initial design will be two load shared Linux boxes running Apache on the front end and one Linux box running Postgresql or MySQL on the back end. The site will be co-located at a broadband ISP's facility so complete remote admin will be required, including a daily data dump from the web server to the admin system. This is not a giant bucks project but we have some cash to pay for this work. We are located in central Florida and would prefer someone in this area but it is not an absolute requirement. All code will be our property meaning we can not pay for code to be developed and licensed to us. Development cycle is 8 to 12 weeks, so you need to know your stuff. No time for learning curve on this one. This is a very interesting project. If you are a serious candidate with the required skill set, please send your complete resume and contact information to [EMAIL PROTECTED] tmb PS, If you are a shop and your rates are salary + overhead + margin you are probably not what we are looking for. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] no Relation no 'id' in psql and PGAccess
I install PostgreSQL 7.1.3 first time and have problem. I create table Klient with PGAccess and - from it when I do "table open" a see every data - when I tried filter ID=32 I see error no column 'id' in psql I can't do any select on table Klient error : no Relation command \dt show this table then I create second table in psql klienci when query looks like select * from klienci, select count(*) from klienci everything is ok but when I tried select Id, Nazwa from klienci the error apear no 'id' what is wrong or what I'm doing incorrect? maybe there is problem with lower and upper case I tried also copy klienci from '..' DELIMITER '|' was incorrect error parse error near 'delimiter' copy klienci from '..' delimiter '|' was correct any ideas? columns in table: Id int4 Nazwa text NIP text ... Arek Malinowski ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Run scripts
Working with oracle I used the command @/home/lange/textfile.sql to run scripts. Does anyone happen to know how this works with postgres? Thanks for any help in advance, Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Is that pgsql support the database partitioning?
Hi all expert, I am a newbie on pgsql. It is so many thing that I don't know. Is that pgsql supports the database partitioning just like oracle do? For the current project, I need to store data into the same database. Can I split the data into different storage device by using database partitioning? Best regards, Corn ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: A903-35A9-76AF : CONFIRM from pgsql-general (subscribe)
accept A903-35A9-76AF ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Appology : MySQL threads belong else where.
Sorry if I insulted the wrong people. Like I said in the message, "The odd comparison is OK" was poorly stated, I was tired of having to go through all the posts from people who appear to be close to anti PostgreSQL. I was trying to indicate that suggesting feature enhancements that are SQL92 and in other databases are appropriate. I am not saying in any way that MySQL's features are bad, but I would rather see PostgreSQL specification compliant than a MySQL knock off. Of course the management programs may not be required to meet any specifications. I the suggestions are aimed at making the management system better suggestions should be welcome. I am not against discussing comparisons between PostgreSQL and other RDBMS systems. Discussions are often a good way of sowing the seeds of ideas for the developers. When discussions appear to become arguments it seems appropriate that somebody steps in to calm down the thread. Again, I am sorry for being harsh, I was upset, but that is no excuse. Guy PS: I hope this thread can stop soon. -- There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] mx is needed by postgresql-python-7.1.3-1PGDG
Hi I had to "rpm --rebuild postgresql-7.1.3-1PGDG.src.rpm" on a RedHat 6.2 machine. After I rebuilt the software I attempted to install all the compiled packages but got the error message : error: failed dependencies: mx is needed by postgresql-python-7.1.3-1PGDG This "/root/.cpan/build/Net-DNS-0.12/demo/mx" is the only mx I could find on my system. Being a simple program name, I have no idea where to get the required package. If the file is supposed to be a program to find DNS MX RR's then this program may be OK. Below is taken from the output from the build process. Begin output from build Requires: python >= 1.5 mx ld-linux.so.2 libcrypt.so.1 libc.so.6 libpq.so.2 /usr/bin/env libc.so.6(GLIBC_2.0) Conflicts: python >= 1.6 End Can anyone tell me what it is and where I can find it? Guy -- There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Majordomo being upgraded ...
Its been much much too long since I've upgraded Majordomo2 on the server, so this is the last email I'm sending out prior to upgrading her today ... if anyone notices the lists go suddenly quiet, or the way it works changing, please let me know ... My main worry is that in the past 6+ months, some of the defaults might have been reversed, so that they default to off instead of on, or vice versa ... just a heads up so that ppl are watching for it ... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Re: Fw: [JDBC] Regarding vacuumdb
> I tried with the command you gave. But, if I try to delete the table 'test1' > it comes with the following error: > ERROR: mdopen: couldn't open test1: Permission denied > > How to solve it?Any help. Make sure the file is owned and writable by the user that the database runs as. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] glacial deletes (long)
Looking for some hints on how to speed up deletes Thanks in advance Using PostgreSQL 7.1.2 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3 (2 processors) Have a "file" record wx_grib_file, with data stored as large object. The rule "wx_grib_file_delete" does the lo_unlink on grib_file_id. Table "wx_grib_file" Attribute | Type| Modifier --+---+-- grib_file_id | oid | not null name | char(40) | parse_time | timestamp | Index: wx_grib_file_pkey Rule: wx_grib_file_delete Triggers: RI_ConstraintTrigger_180325, RI_ConstraintTrigger_180327 Have a catalog entry which uses the wx_grib_file key as a foreign key. For referential integrity, the catalog entry must be deleted before the file. For refTable "wx_grib_catalog" Attribute | Type| Modifier ---+---+-- edition | integer | not null discipline| integer | not null generating_center | integer | not null sub_center| integer | not null scale_factor | integer | grib_product_id | integer | not null prod_category | integer | grib_model_id | integer | not null run_time | timestamp | not null fcst_time | integer | not null grib_region_id| integer | not null level | integer | not null level_units | char(8) | not null projection| char(16) | not null bmp_usage | boolean | not null wx_usage | boolean | not null gds_usage | boolean | not null grib_file_id | oid | gds_offset| integer | pds_offset| integer | not null drs_offset| integer | ds_offset | integer | not null bms_offset| integer | Index: wx_grib_catalog_pkey Constraints: ((prod_category >= 0) AND (prod_category <= 19)) (fcst_time >= 0) (gds_offset >= 0) (pds_offset >= 0) (drs_offset >= 0) (ds_offset >= 0) (bms_offset >= 0) ((edition = 1) OR (edition = 2)) ((generating_center >= 7) AND (generating_center <= 99)) Inserts to these (including the lo_import) are very fast (5 to 10 sets per second), but deletes are very slow (1 or 2 secs per item). Here are the plans: explain verbose delete from wx_grib_catalog where grib_file_id = 3347194; NOTICE: QUERY DUMP: { SEQSCAN :startup_cost 0.00 :total_cost 2787.20 :rows 1 :width 6 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27 :restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk true } :expr { VAR :varno 1 :varattno -1 :vartype 27 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno -1}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1137 :opid 184 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 18 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 18} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 51 18 -6 ] })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 } NOTICE: QUERY PLAN: explain verbose delete from wx_grib_file where grib_file_id = 3347194; NOTICE: QUERY DUMP: { INDEXSCAN :startup_cost 0.00 :total_cost 3.10 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname lo_unlink :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 23 :opType func :oper { FUNC :funcid 964 :functype 23 } :args ({ VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 180258) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 607 :opid 184 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 51 18 -6 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1137 :opid 184 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 51 18 -6 ] })})) :indxorderdir 1 } NOTICE: QUERY PLAN: Index Scan using wx_grib_file_pkey on wx_grib_file (cost=0.00..3.10 rows=1 width=4) NOTICE: QUERY DUMP: { NESTLOOP :startup_cost 0.00 :total_cost 3698.46 :rows 1 :width 14 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27 :restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk true } :expr { VAR :varno 65000 :varattno 1 :vartype 27 :vartypmod -1 :varlevelsup 0 :varnoold 6 :varoattno -1}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0
Re: [GENERAL] tuning
On Thu, 30 Aug 2001, Daniel Lundqvist wrote: > I have a pgsql database containing DHCP lease informtation,one table for > each month. > As we get more and more customers there will be more rows in the tables. The > table for august > now contains about 2.7 million rows. There is about 5 inserts per second. > > Table definition: > > start | boolean > ts| timestamp > mac | macaddr > port | character varying(50) > ip| inet > > There are indexes on ts,mac,port and ip. > Database is running under Linux 2.2 on a Compaq with a P3 800 and 256MB > memory. > > Now for the problem :) I get what I think is very poor response times,about > 90-140s to get a result when searching on ip. > > The only options I have set in postgresql.conf is: > wal_files = 8 > wal_sync_method = fdatasync > > What can I do to tune this database besides memory upgrade and adding a > additional CPU? Have you been running vacuum analyze? What are the queries you're running and what does explain show for them? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] MySQL treads belong else where.
On Wed, 29 Aug 2001, Guy Fraser wrote: > I would appreciate it if the MySQL zealots with troglodytical > mentalities would confine themselves to there own mailing list. Er? Man, everybody just has to chime in with their $0.02. Let the moderators handle it, oh flaming sword o' justice. > The odd comparison is OK but the flame wars are a waste of storage. Which, while an amusing read, also point out actual flaws in Postgres that we could stand fixing. People do bitch without a reason, but the do it more often when they have something to bitch about. Me, I was just being devil's advocate. Looks like not everyone caught that. Oh well. > PS If you don't understand any of these words use a dictionary to find > out what they mean. Don't just presume they are insults. Now, now. You had me going for a while, but you have just instantly turned what may have been a mildly informative post into a pathetic flame. I mean, really. What was that about troglodytical mentalities? For the most part, I agree with you. For everyone else who doesn't get it. Upgrade. Upgrade now, upgrade quickly, and upgrade until you can't upgrade anymore. Postgres 7.1 is not the postgres of yesteryear. Postgres has evolved past the horrid thing MySQL compared itself to. The best part is that 7.2 will be even better. The point here is to know postgres's flaws. Ignore the flames, and upgrade. The more people using the newer versions, the better the next one will be. C'mon, kiss a developer today! -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] RFC: PostgreSQL and MySQL comparison.
On Wed, 29 Aug 2001, Robert J. Sanford, Jr. wrote: > http://www.phpbuilder.com/columns/tim20001112.php3 Now *that* was very informative, thank you. The best benefit to this, is that the optimization engine is supposedly vastly improved in the 7.2 tree, so that'll just increase the lead. If they clean up vacuum to actually get indexes, the planner will have a better chance at picking more optimal execution plans, too. I'm glad that development has picked up. It seemed like 6.5x would be around forever. Thanks for knocking down the walls guys. ^_^ -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] --enable-syslog and Solaris 7
Hi, I'm having a strange problem with syslog and Solaris 7, and I've run out of ideas about what might be wrong. I configure postgres with --enable-syslog, and everything works fine. Configure finds syslog, so that's not a problem. In postgres.conf, I have the following: # # Syslog # #ifdef ENABLE_SYSLOG syslog = 2 # range 0-2 syslog_facility = 'LOCAL1' syslog_ident = 'postgres' #endif And, in /etc/syslog.conf, I have #Postgres logging local1.*/opt/OXRS/logs/postgres/pg.log (yes, the whitespace is tabs). Now, the strange thing is that nothing ever seems to go to the syslog daemon. If I start up syslogd with -d, it never shows anything coming from postgres, even though I have logging levels turned up. If I redirect the log to both console and logfile, I get lots of noise on the console, bit nothing in the file, so I know it's not from want of data. Any suggestion is much appreciated, because I'm stumped. I've never seen anything like this before. Thanks, A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] sub select
On Thu, 30 Aug 2001, Ben-Nes Michael wrote: > Can I: > > select *, sum_rows as ( select count(*) from table2; ) from table1; I think you need to say: select *, (select count(*) from table2) as sum_rows from table1; But this won't be a very interesting query because the sum_rows column will be the same in every row. Where subselects get interesting is when you do a correlated subquery like this: select *, (select count(*) from table2 where table2.x = table1.y) as sum_rows from table1; -- Tod McQuillin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [GENERAL] MySQL treads belong else where.
> So far I haven't seen any real flames or insults thrown about > yet - no MYsql will kill your puppy threads at all. There > can be rigourous discussion without insults being thrown and > so far that's all I've seen. No, but it WILL kill your data, if the DB is big enough and has been in use long enough.. speaking from personal experience *weep* ;-) I used to think mysql was great while I was learning SQL, because it was free, unbloated, and easy to use. But I think one grows out of it for larger and more complex projects... Postgres' larger set of features and sophisticated locking become invaluable. I know (from reading this list) that people sometimes have trouble with PGSQL, but quite honestly I found that after I had read the Postgresql docs in their entirety, everything "fit" and seemed intuitive. (I found it not unlike the feeling of becoming a FreeBSD user after having used linux...) For the project as a whole I guess its a matter of killing off the genuine FUD with one hand, and fixing the areas in Postgres that MySQL does have genuinely have an advantage, with the other hand. - Andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] [Revoke] cannot revoke the permission of "some User"....
Hello all, when i use the "\dp" to view the permission of "mytable": mytable | {"=","28=arw","34=arw","36=arw","40=arw","42=arw","cgi=arw"} i guess those numeric thingy is kinda object id of a user, and i've deleted the user b4 i revoke the permission of that user from "mytable". is it possible for me to remove those 28, 34 & 36? thanks in advance. --- matthew
[GENERAL] sub select
Can I: select *, sum_rows as ( select count(*) from table2; ) from table1; -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] weird behaviour
sorry, i forgot some version number :) [mat@biniac ~]$ psql --version psql (PostgreSQL) 7.1.2 contains readline, history support Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. [mat@biniac ~]$uname -rs FreeBSD 4.3-RELEASE[mat@biniac ~]$ mat ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] weird behaviour
hi, i'm notice a weird behaviour while running a query on the same table, the query take a long time to be executed when this table is empty , and when this table isn't empty, it take about 1/2 second the two tables are freshly vacuumed ... here the same tables, one is empty, not the other : Table "classement_jeu" Attribute | Type | Modifier ---+--+-- semaine | smallint | annee | integer | id_turf | integer | points| integer | tag | smallint | Table "classement_jeumat" Attribute | Type | Modifier ---+--+-- semaine | smallint | annee | integer | id_turf | integer | points| integer | tag | smallint | psql -c "select count(*) from classement_jeu" count --- 21318 (1 row) psql "select count(*) from classement_jeumat" count --- 0 (1 row) now the query i want to run : SELECT id_turf FROM classement_jeumat WHERE semaine = 35 AND annee = 2001 AND id_turf IN (5247,5425, ) the IN clause contain about 600 entries run the query on table classement_jeumat (empty table) time psql < laquery id_turf - (0 rows) real1m18.308s user0m0.000s sys 0m0.015s run the query on table classement_jeu (not empty table) time psql < laquery id_turf - [... snip results ... ] (592 rows) real0m0.472s user0m0.009s sys 0m0.014s somebody understand what happened ? it's not really important because i will not work on an empty table ... but i'm surprised ... thanxs mat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Fw: [JDBC] Regarding vacuumdb
Shanmugasundaram Doraisamy wrote: > - Original Message - > From: Peter Eisentraut <[EMAIL PROTECTED]> > To: Shanmugasundaram <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Wednesday, August 29, 2001 5:11 PM > Subject: Re: [JDBC] Regarding vacuumdb > > > Shanmugasundaram writes: > > > > > I am getting the following error while doing vacuumdb, > > > > > > ERROR: mdopen: couldn't open test1: No such file or directory > > > vacuumdb: database vacuum failed on db1. > > > > > > Here 'db1' is the database and 'test1' is a table. When, displaying the > > > structure of the > > > table 'test1', it comes correctly. But I can't drop the table. What > > > could be wrong? > > > > You probably rolled back a DROP TABLE operation, so the underlying table > > file on disk is gone but the catalog structures have been preserved. > > You could probably try > > > >touch $PGDATA/base/db1/test1 > > > > and then drop the table. > > > > Consider upgrading to the latest stable release (7.1.3) when you get a > > chance to eliminate this class of problems. > > > > -- > > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter > > I tried with the command you gave. But, if I try to delete the table 'test1' it comes with the following error: ERROR: mdopen: couldn't open test1: Permission denied How to solve it?Any help. shan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] tuning
Hi, Sorry for my last empty message. I have a pgsql database containing DHCP lease informtation,one table for each month. As we get more and more customers there will be more rows in the tables. The table for august now contains about 2.7 million rows. There is about 5 inserts per second. Table definition: start | boolean ts| timestamp mac | macaddr port | character varying(50) ip| inet There are indexes on ts,mac,port and ip. Database is running under Linux 2.2 on a Compaq with a P3 800 and 256MB memory. Now for the problem :) I get what I think is very poor response times,about 90-140s to get a result when searching on ip. The only options I have set in postgresql.conf is: wal_files = 8 wal_sync_method = fdatasync What can I do to tune this database besides memory upgrade and adding a additional CPU? Kind regards, Daniel Lundqvist ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Default encoding in database
On Thu, Aug 30, 2001 at 11:04:39AM +0200, Jacob Vennervald Madsen wrote: > If I do rebuild it with initdb will I still have all my data? No of course, initdb create everything again... but if you have only templates DB you probably haven't some data, else you must dump all your DB and backup it and rebuild templates. > > > List of databases > > > Database | Owner | Encoding > > > +--+--- > > > template0 | postgres | SQL_ASCII > > > template1 | postgres | SQL_ASCII > > > Jacob BTW, I don't understand why you don't create new databases by CREATE DATABASE bar WITH ENCODING = 'foo' or by "createdb -E". Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Default encoding in database
On Thu, Aug 30, 2001 at 05:28:16PM +0900, Tatsuo Ishii wrote: > > > How does the output from "psql -l" look like? > > List of databases > > Database | Owner | Encoding > > +--+--- > > template0 | postgres | SQL_ASCII > > template1 | postgres | SQL_ASCII > > Jacob > > I don't know why your template1 DB's encoding is SQL_ASCII, but you > need to change the encoding of the DB anyway. > > try: > > psql -c "update pg_database set encoding = 7 where datname = > 'template1'" template1 IMHO more careful is re-build it by initdb with option -E LATIN1. KArel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] MySQL treads belong else where.
Guy Fraser wrote: > > Hi > > I would appreciate it if the MySQL zealots with troglodytical > mentalities would confine themselves to there own mailing list. > I've yet to see any MYsql zealots on this list. > The odd comparison is OK but the flame wars are a waste of storage. > > MySQL and PostgreSQL both have loyal followers for there own reasons. > Both of these open source database engines have pros and cons. Each > database project has it's own unique set of requirements which are more > suitable to one or the other, but could probably be don in either. > > Marketing a database by claiming your features are better and the > feature of the other are useless is immature. > No it's not useless - if you don't offer alternative view (like MYsql's information on foreign keys) people do believe everything they read. > I use both, but I look to this list for PostgreSQL questions and > answers. The bantering of MySQL zealots does not help anyone on this > list with PostgreSQL questions or answers. > Detailed and open discussion of MYsql vs Postgresql does belong on the general list. A lot of people do believe everything MYsql says (like I used to) like to get both sides of the story and then be able to pass the information to others. It helps in making decisions on which database to use. So far I haven't seen any real flames or insults thrown about yet - no MYsql will kill your puppy threads at all. There can be rigourous discussion without insults being thrown and so far that's all I've seen. > Guy Fraser > > PS If you don't understand any of these words use a dictionary to find > out what they mean. Don't just presume they are insults. > > -- > There is a fine line between genius and lunacy, fear not, walk the > line with pride. Not all things will end up as you wanted, but you > will certainly discover things the meek and timid will miss out on. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Re: [SQL] getting the oid for a new tuple in a BEFORE trigger
On Wed, Aug 29, 2001 at 11:15:08AM +0200, Markus Wagner wrote: > Hi, > > we need to control database changes within BEFORE triggers. > There is no problem with triggers called by update, but there is > a problem with triggers called by insert. > > We strongly need to know the oid of a newly inserted tuple. In this case, we > use tg_newtuple of the TriggerData structure passed to thetrigger function, > and its t_data -> t_oid will have the value '0'. > > Using BEFORE and AFTER triggers would make our lives much harder. > > Is there any way (even hack) to get the oid the newly inserted tuple will > receive? > > Thank you very much, > > Markus > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > > end of the original message Read section 24.2.5.4 'Obtaining other results status' of the Programmer's Guide. This is for the PL/pgSQL language, though. Francesco Casadei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] RE: Default encoding in database
If I do rebuild it with initdb will I still have all my data? Jacob -Original Message- From: Karel Zak [mailto:[EMAIL PROTECTED]] Sent: 30. august 2001 11:03 To: Tatsuo Ishii Cc: Jacob Vennervald Madsen; [EMAIL PROTECTED] Subject: Re: Default encoding in database On Thu, Aug 30, 2001 at 05:28:16PM +0900, Tatsuo Ishii wrote: > > > How does the output from "psql -l" look like? > > List of databases > > Database | Owner | Encoding > > +--+--- > > template0 | postgres | SQL_ASCII > > template1 | postgres | SQL_ASCII > > Jacob > > I don't know why your template1 DB's encoding is SQL_ASCII, but you > need to change the encoding of the DB anyway. > > try: > > psql -c "update pg_database set encoding = 7 where datname = > 'template1'" template1 IMHO more careful is re-build it by initdb with option -E LATIN1. KArel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] Default encoding in database
> > How does the output from "psql -l" look like? > List of databases > Database | Owner | Encoding > +--+--- > template0 | postgres | SQL_ASCII > template1 | postgres | SQL_ASCII > Jacob I don't know why your template1 DB's encoding is SQL_ASCII, but you need to change the encoding of the DB anyway. try: psql -c "update pg_database set encoding = 7 where datname = 'template1'" template1 -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] pg7.1 release date
Greetings, pgsql-general! While researching material for a certain upcoming article, I found out that online docs still claim "2001-??-??" as a release date for PostgreSQL 7.1 -- Yours, Alexey V. Borzov, Webmaster of RDW.ru ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] url to free text search stategies in postgresql?
* Matthew Kennedy <[EMAIL PROTECTED]> wrote: | | There was a brief mention in the "RFC: PostgreSQL and MySQL comparison" | thread about "free text search" in postgresql. Is there any information | avaliable on this? Nothing came up when I searched the interactive Check the openfts addon for postgresql at : http://openfts.sourceforge.net/ You can also test it in action by searching the PostgreSQL mailing lists at : http://fts.postgresql.org/db/mw/ cheers, Gunnar -- Gunnar Rønning - [EMAIL PROTECTED] Senior Consultant, Polygnosis AS, http://www.polygnosis.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Solution found! was Re: Finding the number of rows affected by UPDATE or INSERT?
I found out how to do it: In pl/pgsql: INT nr; UPDATE GET DIAGNOSTICS SELECT PROCESSED INTO nr; will put the number of rows into nr. It's a very useful feature to have this in PG7.1. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re[2]: [GENERAL] RFC: PostgreSQL and MySQL comparison.
Greetings, Peter! At 29.08.2001, 23:32, you wrote: >> But I do think that >> the statements in >> http://www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.html >> should NOT go unanswered. PE> Okay, I answered them: PE> http://webmail.postgresql.org/~petere/comparison.html Looks good, but it's not exactly what I've had in mind. You see it is too often considered that if someone has to justify oneself, he is automatically guilty. I want to write an article that would make MySQL's developers justify themselves... Besides, one has to read the MySQL's article *before* this just to understand what's this all about... BTW, can I use parts of your answers in my work? And, a question to SQL standard gurus: is it necessary for a DBMS to implement subqueries and views to be considered entry-level SQL92 compliant. Besides, is there any document which has all PostgreSQL's deviations from standard in one place? PE> I tried to be reasonable and biased at the same time. ;-) PE> This article does not go into the advantages of PostgreSQL, since that's PE> already done elsewhere, such as here: PE> http://www.ca.postgresql.org/features.html -- Yours, Alexey V. Borzov, Webmaster of RDW.ru ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly