Re: [GENERAL] postgre vs MySQL
Scott Marlowe wrote: On Tue, Mar 11, 2008 at 7:33 PM, Justin <[EMAIL PROTECTED]> wrote: I view updates/patches of any kind like this, if ain't broke don't fix it. I normally only update computers with security patches only after i prove it don't destroy installs. But that's juast it. When a postgresql update comes out, it is precisely because the database IS broken. A bug that might eat your data or allow an attacker to get into your database are the kinds of fixes, and the only kind really, that go into production pgsql releases. I too wait a day or two to test it on a staging server, but I've never had a pgsql update blow back in my face, and I've done an awful lot of them. So you missed 8.1.7 then or weren't using those features at the very least? You also didn't have the stats collector issue with 8.2.3, 8.2.4 took quite some time to come out. And remember the policy violation when 8.0 came out, we replaced the buffer expiry algorithm with a patch release. PostgreSQL is not perfect, but as you can see by the problems with 8.1.7 the next update was released very very quickly. Sometimes I fear we pump up our status a little too far with the reliability and only perfectly patched releases. The real key is what's the response when things go wrong, because things will go wrong at some point. I think we need to be careful because it's a much bigger fall the higher the pedestal we put ourselves on. Regards Russell -- 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] postgre vs MySQL
On Wed, Mar 12, 2008 at 12:02 AM, Russell Smith <[EMAIL PROTECTED]> wrote: > > Scott Marlowe wrote: > > On Tue, Mar 11, 2008 at 7:33 PM, Justin <[EMAIL PROTECTED]> wrote: > > > >> I view updates/patches of any kind like this, if ain't broke don't fix > it. > >> I normally only update computers with security patches only after i prove > it > >> don't destroy installs. > >> > > > > But that's juast it. When a postgresql update comes out, it is > > precisely because the database IS broken. A bug that might eat your > > data or allow an attacker to get into your database are the kinds of > > fixes, and the only kind really, that go into production pgsql > > releases. I too wait a day or two to test it on a staging server, but > > I've never had a pgsql update blow back in my face, and I've done an > > awful lot of them. > > > So you missed 8.1.7 then or weren't using those features at the very least? > You also didn't have the stats collector issue with 8.2.3, 8.2.4 took > quite some time to come out. > And remember the policy violation when 8.0 came out, we replaced the > buffer expiry algorithm with a patch release. Yeah, we went from 8.0.x (whatever was current at the time) to 8.2.4. And I do test any update for a couple days before applying it. So when something goes wrong with a release like 8.1.7 was, I suppose, I get the next one and I'm good. I don't just throw updates at production. But I've never been bitten by an update that was more than a couple days old either. And I remember the change in 8.0 in the cache control, and it definitely caused me to be slow on updating at that time, to make sure it worked. It was very well advertised though, so I don't feel like a surprise was sprung upon me. > PostgreSQL is not perfect, but as you can see by the problems with 8.1.7 > the next update was released very very quickly. Sometimes I fear we > pump up our status a little too far with the reliability and only > perfectly patched releases. The real key is what's the response when > things go wrong, because things will go wrong at some point. I think we > need to be careful because it's a much bigger fall the higher the > pedestal we put ourselves on. Agreed. I do think though that the pg developers have gotten much much better about such things as time has gone by. I don't get the feeling MySQL has. The difference is very much in how one handles one's mistakes, and in that arena, I feel like pgsql has fewer in production releases and they fix them much quicker, which is a combination I can live with. -- 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] postgre vs MySQL
On Wed, 12 Mar 2008, Russell Smith wrote: Scott Marlowe wrote: I too wait a day or two to test it on a staging server, but I've never had a pgsql update blow back in my face, and I've done an awful lot of them. So you missed 8.1.7 then or weren't using those features at the very least? You also didn't have the stats collector issue with 8.2.3, 8.2.4 took quite some time to come out...PostgreSQL is not perfect, but as you can see by the problems with 8.1.7 the next update was released very very quickly. The stats collector one made my life miserable for quite some time. But that was all part of a major upgrade that happened to contain a performance regression. The problem had been there since 8.2.0, and major version releases always come with new bugs in the new features. I know I caught it in release validation and held off upgrades until it was dealt with. I think what Scott was suggesting is that it's generally safe to apply minor revision updates and expect that you'll have less bugs afterwards than you'd have if you didn't apply the update. 8.1.7 was out for only two days before the 8.1.8 fix came out; only the most aggressive upgrade plan would have been bit by that. If you look at the link I passed along before, you'll see the difference with MySQL is that they've been abusing their customers with minor point releases that try to add new features. Instead some of these introduce functional regressions, which often hang around for a whole long longer than two days after being noticed (this isn't even considering the delays before those fixes make their way back into the open source product, some only even go to paying customers). Sure, the PG stats bug was around for five months before correction, but it was just a performance issue that only showed up under limited circumstances and once it was reported it got squashed fairly quickly. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] migration of 7.4 to 8.1
sathiya psql wrote: Is there any article describing the migration database from postgresql 7.4to 8.1 The basic tips are: 1. Use pg_dump from 8.1 to dump your 7.4 database 2. Read the release notes for the intervening versions to see what might affect your applications. In particular, the UTF handling was tightened up which means some old data might need to be tidied before import. Also add_missing_from was turned to "off". But do read the release notes for 8.0 and 8.1 to see what might affect you. -- Richard Huxton Archonet Ltd -- 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] FROM + JOIN when more than one table in FROM
On Wed, Mar 12, 2008 at 11:40:18AM +0100, Ivan Sergio Borgonovo wrote: > I'd like to make this query work > > select 1, > st.Name, sm.Name, sm.MethodID, sm.Description, > pt.Name, pm.Name, pm.MethodID, pm.Description > from > shop_commerce_paymethods pm, > shop_commerce_shipmethods sm > > inner join shop_commerce_shiptypes st on sm.TypeID=st.TypeID > inner join shop_commerce_paytypes pt on pm.TypeID=pt.TypeID > where sm.MethodID=1 and pm.MethodID=1 > > I can make it work renouncing to one *t.Name changing the order of > the FROM tables and skipping one join... but I can't have in one run > all I need. From my understanding of SQL join syntax, the above is parsed as: FROM pm,((sm inner join st) inner join pt) which means that pm isn't in scope when doing the inner join on pt. Perhaps this would owrk: FROM sm inner join st inner join pt inner join pm Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] FROM + JOIN when more than one table in FROM
I'd like to make this query work select 1, st.Name, sm.Name, sm.MethodID, sm.Description, pt.Name, pm.Name, pm.MethodID, pm.Description from shop_commerce_paymethods pm, shop_commerce_shipmethods sm inner join shop_commerce_shiptypes st on sm.TypeID=st.TypeID inner join shop_commerce_paytypes pt on pm.TypeID=pt.TypeID where sm.MethodID=1 and pm.MethodID=1 I can make it work renouncing to one *t.Name changing the order of the FROM tables and skipping one join... but I can't have in one run all I need. The above should be the "optimised" version of a much longer query that works: select 0, st.Name, sm.Name, sm.MethodID, sm.Description, pt.Name, pm.Name, pm.MethodID, pm.Description from shop_commerce_baskets b inner join shop_commerce_shipmethods sm on sm.MethodID=b.ShipMethodOnStockID inner join shop_commerce_shiptypes st on sm.TypeID=st.TypeID inner join shop_commerce_paymethods pm on pm.MethodID=b.PayMethodOnStockID inner join shop_commerce_paytypes pt on pm.TypeID=pt.TypeID where b.BasketID=3 union select 1, st.Name, sm.Name, sm.MethodID, sm.Description, pt.Name, pm.Name, pm.MethodID, pm.Description from shop_commerce_baskets b inner join shop_commerce_shipmethods sm on sm.MethodID=b.ShipMethodBackOrderID inner join shop_commerce_shiptypes st on sm.TypeID=st.TypeID inner join shop_commerce_paymethods pm on pm.MethodID=b.PayMethodBackOrderID inner join shop_commerce_paytypes pt on pm.TypeID=pt.TypeID where b.BasketID=3 I don't even know if it is worth to optimise the above till I'll have a working comparison. thx -- 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
Re: [GENERAL] ERROR: text search configuration "pg_catalog.english" does not exist
If I do \dF: Schema | Name | Description ++-- pg_catalog | simple | simple configuration (1 row) \dFd: Schema | Name |Description + +--- pg_catalog | simple | simple dictionary: just lower case and check for stopword And for select * from pg_ts_config the only row shows: simple |11 | 10 | 3722 Is there away of getting the extra catalogues in? Thanks, Tim. On 11 Mar 2008, at 22:25, Tom Lane wrote: Tim Child <[EMAIL PROTECTED]> writes: Hi, I am trying to configure Full Text Search on PostgreSQL 8.3 but I seem to be missing pg_catalog.english as I get the follow when I try and do this: That's odd ... what *do* you have in pg_ts_config? It should look about like this, in a virgin database: postgres=# select * from pg_ts_config; cfgname | cfgnamespace | cfgowner | cfgparser +--+--+--- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] porting vb6 code to pgplsql, referencing fields
Hi everyone, I'm trying to port some vb6 code to pgplsql (PostgreSQL 8.3 winxp) that code is Const f2_MAX_TRAMS = 5 Dim f2_rTarifaA as new ADODB.Recordset Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua Dim Ma(f2_MAX_TRAMS) As Long' m3 aigua tarifa Dim i As Integer, j As Integer ' indexs matrius ... ( open connection, open recordset, etc ) ... -- fill array with field values of M3TRAM1, ..., M3TRAM5 -- PREU1, ..., PREU5 for i = 1 to f2_MAX_TRAMS Ma(i) = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value next in pgplsql, more or less DECLARE c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL; f2_MAX_TRAMS CONSTANT INTEGER := 5; Ma INTEGER[5]; Mpa NUMERIC(10,2)[5]; row_tfa f2_tarifa_a%rowtype; BEGIN OPEN c_tarifa_a (datafac, f2_Mtar); FETCH c_tarifa_a INTO row_tfa; CLOSE c_tarifa_a; For i IN 1..f2_MAX_TRAMS LOOP Ma[i] := row_tfa. ? -- "M3TRAM" + CStr(i)).Value Mpa[i] := row_tfa. ? -- "PREU" + CStr(i)).Value END LOOP; END I would like to know some tips about: How can I declare arrays especifying the size with a constant, but the most important is how can I reference the fields inside de loop Thanks in advance Josep
Re: [GENERAL] FROM + JOIN when more than one table in FROM
On Wed, 12 Mar 2008 11:48:24 +0100 Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Wed, Mar 12, 2008 at 11:40:18AM +0100, Ivan Sergio Borgonovo > wrote: > > I'd like to make this query work > > > > select 1, > > st.Name, sm.Name, sm.MethodID, sm.Description, > > pt.Name, pm.Name, pm.MethodID, pm.Description > > from > > shop_commerce_paymethods pm, > > shop_commerce_shipmethods sm > > > > inner join shop_commerce_shiptypes st on > > sm.TypeID=st.TypeID inner join shop_commerce_paytypes pt on > > pm.TypeID=pt.TypeID where sm.MethodID=1 and pm.MethodID=1 > > > > I can make it work renouncing to one *t.Name changing the order of > > the FROM tables and skipping one join... but I can't have in one > > run all I need. > > From my understanding of SQL join syntax, the above is parsed as: > > FROM pm,((sm inner join st) inner join pt) > > which means that pm isn't in scope when doing the inner join on pt. > Perhaps this would owrk: > FROM sm inner join st inner join pt inner join pm one of the inner join doesn't have an on relationship. As you could see in the other (longer) query I'm just trying to put in the same row what would be select 1, t.Name, m.Name, m.MethodID, m.Description from shop_commerce_shipmethods m inner join shop_commerce_shiptypes t on m.TypeID=t.TypeID where m.MethodID=1 + select 1, t.Name, m.Name, m.MethodID, m.Description from shop_commerce_paymethods m inner join shop_commerce_paytypes t on m.TypeID=t.TypeID where m.MethodID=1 I don't want it to get it with a union since pay and ship are associated. The "natural" way to get them in one row would be to get the table that link them shop_commerce_baskets as in the longer query. But that query contains a lot of unions and loop etc... and I'd be curious to see if fetching ShipMethodOnStockID, PayMethodOnStock, ... in advance and using the above queries would make the query plan simpler... I'm attaching the query plan of the longer query since it is too nested to just get pasted. The scope of the "exercise" would be to avoid 2 scans of the shop_commerce_baskets table. -- Ivan Sergio Borgonovo http://www.webthatworks.it Unique (cost=58.36..58.41 rows=2 width=884) -> Sort (cost=58.36..58.37 rows=2 width=884) Sort Key: "?column?", name, name, methodid, description, name, name, methodid, description -> Append (cost=0.00..58.35 rows=2 width=884) -> Subquery Scan "*SELECT* 1" (cost=0.00..29.18 rows=1 width=884) -> Nested Loop (cost=0.00..29.17 rows=1 width=884) -> Nested Loop (cost=0.00..23.33 rows=1 width=806) -> Nested Loop (cost=0.00..17.49 rows=1 width=446) -> Nested Loop (cost=0.00..11.66 rows=1 width=368) -> Index Scan using shop_commerce_baskets_pkey on shop_commerce_baskets b (cost=0.00..5.82 rows=1 width=8) Index Cond: (basketid = 3) -> Index Scan using shop_commerce_paymethods_pkey on shop_commerce_paymethods pm (cost=0.00..5.82 rows=1 width=364) Index Cond: (pm.methodid = "outer".paymethodonstockid) -> Index Scan using shop_commerce_paytypes_pkey on shop_commerce_paytypes pt (cost=0.00..5.82 rows=1 width=86) Index Cond: ("outer".typeid = pt.typeid) -> Index Scan using shop_commerce_shipmethods_pkey on shop_commerce_shipmethods sm (cost=0.00..5.82 rows=1 width=364) Index Cond: (sm.methodid = "outer".shipmethodonstockid) -> Index Scan using shop_commerce_shiptypes_pkey on shop_commerce_shiptypes st (cost=0.00..5.82 rows=1 width=86) Index Cond: ("outer".typeid = st.typeid) -> Subquery Scan "*SELECT* 2" (cost=0.00..29.18 rows=1 width=884) -> Nested Loop (cost=0.00..29.17 rows=1 width=884) -> Nested Loop (cost=0.00..23.33 rows=1 width=806) -> Nested Loop (cost=0.00..17.49 rows=1 width=446) -> Nested Loop (cost=0.00..11.66 rows=1 width=368) -> Index Scan using shop_commerce_baskets_pkey on shop_commerce_baskets b (cost=0.00..5.82 rows=1 width=8) Index Cond: (basketid = 3) -> Index Scan using shop_commerce_paymethods_pkey on shop_commerce_paymethods pm (cost=0.00..5.82 rows=1 width=364) Index Cond: (pm.methodid = "outer
Re: [GENERAL] Trigger to run @ connection time?
On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kynn Jones" <[EMAIL PROTECTED]> writes: > > If one can set up this insert operation so that it happens automatically > > whenever a new connection is made, I'd like to learn how it's done. > > For manual psql sessions, you can put some setup commands in ~/.psqlrc. > In any other context I'm afraid you're stuck with modifying your client > application code. > > An ON CONNECT trigger enforced by the database seems a bit scary to me. > If it's broken, how you gonna get into the DB to fix it? I guess I don't know just *how broken* a trigger can be :-) ! I guess what you're saying is that a trigger can be *so badly broken* that, even if executed in response to a regular INSERT/UPDATE/DELETE event, it would disable the database to the point that the only recourse would be to kill the connection and open a new one. Such a trigger, if it were associated with an CONNECT event, would render the database inaccessible. It follows from Murphy's law that triggers that are this broken are certainly possible... Which is a long-winded way to say that I see your point! Kynn
Re: [GENERAL] Trouble with Savepoints in postgres
Please always ensure that the list is copied on replies (use "Reply to all") so that other people can help you. sam escribió: > On Mar 11, 5:39 pm, [EMAIL PROTECTED] (Alvaro Herrera) wrote: > > sam escribió: > > > > > Iam not able to understand if this is a version problem or the way iam > > > using savepoints is wrong.Please advice. > > > > It is. You cannot use savepoints in PL/pgSQL functions (or any function > > for that matter). You can use EXCEPTION clauses instead. > Then u please tell me how save points can be used...The > program iam working on is throwing an 'LIMIT EXCEEDED' error so iam > trying to commit data so that a total rollback does not occur.Like > commiting data after every 1000 transactions. I figured that > savepoints would be the solution. No, savepoints will not help you there. No matter what you do, you cannot commit in the middle of a function. What's the limit being exceeded? Perhaps you can solve your problem some other way. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] oralink
Hi, Does somebody knows how to compile oralink in postgresql 8.2? The boss wants it because it saw it working in a postgresql 8.1. I hate oralink because it looks like a dummy's job (the makefile is a mess and there's no documentation, not even a README file). Any information is welcome. Thanks -- Marcus Vinícius --- Administrador de Redes Linux/Microsoft Integrador de Software -- 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] Trigger to run @ connection time?
Kynn Jones wrote: On Tue, Mar 11, 2008 at 5:28 PM, Tom Lane <[EMAIL PROTECTED]> wrote: "Kynn Jones" <[EMAIL PROTECTED]> writes: If one can set up this insert operation so that it happens automatically whenever a new connection is made, I'd like to learn how it's done. For manual psql sessions, you can put some setup commands in ~/.psqlrc. In any other context I'm afraid you're stuck with modifying your client application code. An ON CONNECT trigger enforced by the database seems a bit scary to me. If it's broken, how you gonna get into the DB to fix it? I guess I don't know just *how broken* a trigger can be :-) ! I guess what you're saying is that a trigger can be *so badly broken* that, even if executed in response to a regular INSERT/UPDATE/DELETE event, it would disable the database to the point that the only recourse would be to kill the connection and open a new one. Such a trigger, if it were associated with an CONNECT event, would render the database inaccessible. It follows from Murphy's law that triggers that are this broken are certainly possible... I've been interested in an ON CONNECT trigger, too. My suggestion regarding the scary problem noted above is that there would have to be a configuration setting in postgresql.conf to enable or disable the trigger so that if a broken trigger killed the data base, you could recover by modifying the configuration file so as to disable the trigger and then successfully restart the data base. The problem with the suggested work-around implementation of modifying the client application code is that the (pseudo-)trigger is only fired if the data base is accessed by means of that specifically-rigged-up application. It would not fire if someone went in via a utility like pgAdmin III, or psql, for example. And since a really useful data base is likely to have multiple applications running against it anyway, they would all have to consistently duplicate the pseudo-trigger code. -- BMT -- 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] Trigger to run @ connection time?
On Wed, Mar 12, 2008 at 07:52:29AM -0400, Kynn Jones wrote: > > An ON CONNECT trigger enforced by the database seems a bit scary to me. > > If it's broken, how you gonna get into the DB to fix it? A "psql --skip-on-connect-trigger", only available to, say, superusers ? Or a database flag (like the "accepts connections" one) editable by superusers when connected to another database ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Trigger to run @ connection time?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Hi! I want to set up a trigger (somehow) that, whenever someone connects > database my_db, will fire and thereby run a stored PLPERL procedure > perl_setup() in the new connection's environment. (BTW, this procedure adds > useful definitions, mostly subs, to Perl's main package. This needs to be > done for each connection, because such modifications of package main do not > persist from one session to the next.) I think you are going about this the wrong way. Create a hook in each plperl func that does the initial setup for you as needed. Simply store a value in $_SHARED indicating whether it has already run or not for that session. This also avoid any overhead at all of calling perl_setup() if the connection in question is never going to use plperl, or even if it is going to use plperl but does not need perl_setup(). - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200803121042 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfX6+0ACgkQvJuQZxSWSsiFdQCg4WGmB4+InrL7E+7c8Tq82lFy TFcAn2lQfSXJwO8LUQ9vZPf9ZStLdVHW =R5fK -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pain of postgres upgrade with extensions
This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn’t seem to be a way of dumping the database with out including extension specific information. There is a possible solution to this problem, move all the extension specific functions to an extension specific schema. That way the contents of the database are kept separate from extensions. For example the postgis function area would change to postgis.area assuming the the schema for postgis extension was call postgis, this would also avoid the problem if two extensions happen to have a function with the same name. D. -- Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the Pinan Software -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: text search configuration "pg_catalog.english" does not exist
Tim Child <[EMAIL PROTECTED]> writes: > If I do \dF: > Schema | Name | Description > ++-- > pg_catalog | simple | simple configuration > (1 row) Huh. Seems like initdb forgot to install all the Snowball stemmers. What do you find in $SHAREDIR/snowball_create.sql? (If you're not sure where your SHAREDIR is, pg_config --sharedir will tell you.) Where did this build of Postgres come from, anyway? 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] postgre vs MySQL
On Tue, 2008-03-11 at 06:47 -0700, rrahul wrote: > Any major clients of the two. > You can add you own points too. Perhaps someone can comment on current MySQL backups procedures. I believe that MySQL used to (still does?) require shutdown to be backed up. I don't know whether this was true for all engines or whether it might have been fixed. Having to shutdown a database to make a backup is a non-starter for anything that other than a toy (or read-only) databases. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
[GENERAL] PostgreSQL user documentation wiki open for business
I'm pleased to announce that wiki.postgresql.org is now open for business! The PostgreSQL Wiki replaces the technical documentation area ("techdocs") on the PostgreSQL website and provides an easy-to-use area for PostgreSQL users and developers to read and document experiences with any area of PostgreSQL such as migration issues, integration with other applications or networks, tips and tricks, or useful HOWTOs. All documentation from the old site has been migrated to the wiki. The wiki can be accessed at http://wiki.postgresql.org/ To add or edit pages you will need a PostgreSQL Community logon. If you do not already have an account, you can register one at http://www.postgresql.org/community/signup -- Dave Page PostgreSQL Core Team PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- 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] postgre vs MySQL
You can make a backup in MySQL in several ways: 1) Using mysqldump; 2) Lock tables and copy their files one-by-one (MyISAM-only); 3) Shutdown server and copy all files (can be a slave in a replicated setup); 4) Using InnoDB hot backup (commercial tool); On 3/12/08, Reece Hart <[EMAIL PROTECTED]> wrote: > > On Tue, 2008-03-11 at 06:47 -0700, rrahul wrote: > > Any major clients of the two. > You can add you own points too. > > > Perhaps someone can comment on current MySQL backups procedures. I > believe that MySQL used to (still does?) require shutdown to be backed up. I > don't know whether this was true for all engines or whether it might have > been fixed. Having to shutdown a database to make a backup is a non-starter > for anything that other than a toy (or read-only) databases. > > -Reece > > -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 > > -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: [GENERAL] ERROR: text search configuration "pg_catalog.english" does not exist
Tim Child <[EMAIL PROTECTED]> writes: > In my snowball_create.sql I find: > -- No language-specific snowball dictionaries, for lack of shared > library support Really!? > This build comes from postgresqlformac.com the "Unified Installer - > 8.3.0 (PostgreSQLforMac)" Hm, do they have plpgsql or any of the other PLs? It's hard to credit that anyone would put out a build without shared library support. There's too much functionality that goes missing. > Now I am considering that it might have been better to compile my own, > but before I do this is there anyway to get the libraries / > dictionaries installed? What you need is a non-lobotomized build. You should be able to fix the problem without re-initdb, if you need to, by running the real snowball_create.sql script against each of your databases. 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] postgre vs MySQL
Reece Hart wrote: On Tue, 2008-03-11 at 06:47 -0700, rrahul wrote: Any major clients of the two. You can add you own points too. Perhaps someone can comment on current MySQL backups procedures. I believe that MySQL used to (still does?) require shutdown to be backed up. I don't know whether this was true for all engines or whether it might have been fixed. Having to shutdown a database to make a backup is a non-starter for anything that other than a toy (or read-only) databases. -Reece For a database of InnoDB tables, people tend to replicate the database, and then backup the slave (unless the db is trivially small, in which case, mysqldump). For MyISAM, you can back it up hot, or do the same replication thing as with InnoDB tables. For larger and active MySQL installations, it's not uncommon to see a MySQL database replicate to 2 or more slaves, and: - use a slave to initialize any future additional slaves - use a slave for backups - promote a slave to master in case of master failure There's the hot backup tool you can buy for InnoDB, but I've yet to meet anyone who's actually used it. Paul -- 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] postgre vs MySQL
On Wed, 12 Mar 2008 09:13:14 -0700 paul rivers <[EMAIL PROTECTED]> wrote: > For a database of InnoDB tables, people tend to replicate the > database, and then backup the slave (unless the db is trivially That recalled me the *unsupported* feeling I have that it is easier to setup a HA replication solution on MySQL. Pardon my ignorance of serious DBA jargon... I'm thinking to something suited for load balancing the read as highest priority in terms of performance *and* duplicate the write across different boxes without the application layer has to know about it as second priority in terms of performance... I just would like to be contradicted and pointed to some viable (easy?) setup for pgsql, so that I and other people will get rid of this preconception if any. -- 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
Re: [GENERAL] postgre vs MySQL
Take a look at pgpool . http://pgpool.projects.postgresql.org/ 2008/3/12, Ivan Sergio Borgonovo <[EMAIL PROTECTED]>: > On Wed, 12 Mar 2008 09:13:14 -0700 > paul rivers <[EMAIL PROTECTED]> wrote: > > > For a database of InnoDB tables, people tend to replicate the > > database, and then backup the slave (unless the db is trivially > > > That recalled me the *unsupported* feeling I have that it is easier > to setup a HA replication solution on MySQL. > > Pardon my ignorance of serious DBA jargon... > > I'm thinking to something suited for load balancing the read as > highest priority in terms of performance *and* duplicate the write > across different boxes without the application layer has to know > about it as second priority in terms of performance... > > I just would like to be contradicted and pointed to some viable > (easy?) setup for pgsql, so that I and other people will get rid of > this preconception if any. > > > -- > 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 > -- 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] pain of postgres upgrade with extensions
David Potts wrote: This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn’t seem to be a way of dumping the database with out including extension specific information. Is this something that wouldn't be fixed by: - dump 8.2 database - load dump into 8.3 database - for each extension, run the 8.2 drop extension script in 8.2's contrib - for each extension, run the 8.3 install extension script in 8.3's contrib ?? Or is it a matter of easily keeping an inventory of what extension is installed in what db? Paul -- 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] pain of postgres upgrade with extensions
I noticed this immediately when using the PostgreSQL tool for examples of dumps for creating export of database/table structure/data for the MyJSQLView application. I considered implementing a similar copy dump, but seems it would not be handled properly with SQL statements. danap. This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn’t seem to be a way of dumping the database with out including extension specific information. There is a possible solution to this problem, move all the extension specific functions to an extension specific schema. That way the contents of the database are kept separate from extensions. For example the postgis function area would change to postgis.area assuming the the schema for postgis extension was call postgis, this would also avoid the problem if two extensions happen to have a function with the same name. D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can't rename an existnig DB because it doesn't exist???
V8.2.0 on Linux Can't rename a db, complains that it doesn't exist. Yet psql -l shows that it does and I can connect to it ??? mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to stdb_tmp" ERROR: database "stdb" does not exist mmdcc228_SETUP(121)% psql -l List of databases Name | Owner | Encoding --+--+-- cells| dfgauthi | UTF8 cells_dev| dfgauthi | UTF8 postgres | dfgauthi | UTF8 stdb | dfgauthi | UTF8 stdb2| dfgauthi | UTF8 stdb_standby | dfgauthi | UTF8 template0| dfgauthi | UTF8 template1| dfgauthi | UTF8 (8 rows) mmdcc228_SETUP(122)% psql stdb Welcome to psql 8.2.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit stdb=#
Re: [GENERAL] ERROR: text search configuration "pg_catalog.english" does not exist
Tim Child <[EMAIL PROTECTED]> writes: > There is a file here with the same name: > /Library/PostgreSQL8/lib/postgresql/plpgsql.so What else is in that directory? If you don't have dict_snowball.so it's not gonna work. > So I could try and run that snowball_create.sql script from src (which > I have downloaded) and try and get the required functionality? What I suspect at this point is that postgresqlformac.com hack up the build process for .so's in some weird way, and that they neglected to apply their hack to the Snowball stemmer library. You should report that to them as a bug in their packaging. 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] Can't rename an existnig DB because it doesn't exist???
On Wed, Mar 12, 2008 at 10:03 AM, Gauthier, Dave <[EMAIL PROTECTED]> wrote: > > > V8.2.0 on Linux Look into updating, there were some serious bugs fixed between 8.2.0 and 8.2.6 It's a pretty simple thing, since you don't need to dump / reload for it. > > Can't rename a db, complains that it doesn't exist. Yet psql –l shows that > it does and I can connect to it ??? > > > > mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to stdb_tmp" > > ERROR: database "stdb" does not exist > > mmdcc228_SETUP(121)% psql -l > > List of databases > > Name | Owner | Encoding > > --+--+-- > stdb | dfgauthi | UTF8 > > stdb2| dfgauthi | UTF8 > > stdb_standby | dfgauthi | UTF8 Very strange. maybe the name has a space in it? Try running this query: select '|'||datname||'|' from pg_database ; and see if you have a space or something in there. can you run the alter database rename from the command line and just not from psql? -- 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] postgre vs MySQL
Ivan Sergio Borgonovo wrote: > On Wed, 12 Mar 2008 09:13:14 -0700 > paul rivers <[EMAIL PROTECTED]> wrote: > > > For a database of InnoDB tables, people tend to replicate the > > database, and then backup the slave (unless the db is trivially > > That recalled me the *unsupported* feeling I have that it is easier > to setup a HA replication solution on MySQL. Well, if you have a crappy system that cannot sustain concurrent load or even be backed up concurrently with regular operation, one solution is to write a kick-ass replication system. The other solution is to enhance the ability of the system to deal with concurrent operation. We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr, etc; and they all run on farms and farms of MySQL servers, "because MySQL replication is so good". I wonder if replication is an actual _need_ or it's there just because the other aspects of the system are so crappy. -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] postgre vs MySQL
On Wed, 12 Mar 2008 17:47:35 +0100 "jose javier parra sanchez" <[EMAIL PROTECTED]> wrote: > Take a look at pgpool . http://pgpool.projects.postgresql.org/ I knew about it. Giving a look at http://pgpool.projects.postgresql.org/#restriction it doesn't seem something that can be completely hidden to the application layer. I know that most of the problems (not all) arise from the fact that pg has features that MySQL can just dream of... but it doesn't look as something that is really transparent to the application layer. Not that this should imply I consider easy to achieve such result... I know it is far from being easy, just that it doesn't look as what I was trying to describe. So let me rephrase, in order of importance: - something completely transparent at the application layer - something that won't die if one of your boxes die - something that will improve performances of reads - something that won't suffer too much for replicating writes At a first sight it looks as if pgpool can't boost stuff in pl* functions. -- 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
Re: [GENERAL] postgre vs MySQL
On Wed, Mar 12, 2008 at 10:15 AM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > On Wed, 12 Mar 2008 09:13:14 -0700 > > paul rivers <[EMAIL PROTECTED]> wrote: > > > > > For a database of InnoDB tables, people tend to replicate the > > > database, and then backup the slave (unless the db is trivially > > > > That recalled me the *unsupported* feeling I have that it is easier > > to setup a HA replication solution on MySQL. > > Well, if you have a crappy system that cannot sustain concurrent load or > even be backed up concurrently with regular operation, one solution is > to write a kick-ass replication system. > > The other solution is to enhance the ability of the system to deal with > concurrent operation. > > We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr, > etc; and they all run on farms and farms of MySQL servers, "because > MySQL replication is so good". I wonder if replication is an actual > _need_ or it's there just because the other aspects of the system are so > crappy. Reminds me of the saying that for each problem, there is a simple, elegant solution that is completely wrong. It amazes me that slony, being basically a "bolt on" replication solution has given me much fewer problems than MySQL replication which is known for silent failures. Slony is by no means perfect, but it is quite impressive as both a replication tool and an upgrade tool. An awful lot of the sites running on MySQL are running on it primarily because it's what they started with, and now it's hard to switch because their code is chock full of mysqlisms like "select field1, field2 from table group by field1" and so on that no other database is going to swallow without throwing an error. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL won't start
Last week I set up Postgres 8.3 on a WindowsXP machine. Had it up and running and imported data. Now when I try to start the server (after a machine restart) I get the message: pg_ctl: another server might be running; trying to start server anyway PANIC: could not open control file "global/pg_control": Permission denied This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. server starting In the archives I've seen suggestions to make sure to start using the postgres account (I am), make sure postgres is configured as a service (it is), and make sure global/pg_control and the rest of the PostgreSQL directory has read/write access (it does). What else can I try to start the server? Thanks, Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function Returning SETOF RECORD: Trouble With Char Type
Hello, I am having trouble with a function designed to return all column constraints on a table. The problem is not in the SQL, which works fine on its own. The problem comes when the function is invoked using SELECT * FROM function_name(arg_name) AS temp_table(.). When the function does not contain either of the two char columns, it returns perfectly well, but when either char column is added, it returns "ERROR: wrong record type supplied in RETURN NEXT". I'm hoping this is something obvious or know that I am missing. Working and non-working code are below. Please pardon the complex query. Thanks. --Angus Atkins-Trimnell Begin Working Code: ** CREATE OR REPLACE FUNCTION sql_get_schema_full(select_table text) RETURNS SETOF RECORD AS $$ DECLARE sql_result record; BEGIN FOR sql_result in EXECUTE 'SELECT t.relname, a.attname, c.conname, c.consrc, ft.relname, fa.attname FROM (((pg_class AS t LEFT JOIN (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) AS a ON t.oid=a.attrelid) LEFT JOIN pg_constraint AS c ON t.oid=c.conrelid AND a.attnum=ANY(c.conkey)) LEFT JOIN pg_class AS ft ON ft.oid=c.confrelid) LEFT JOIN (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) AS fa ON ft.oid=fa.attrelid AND fa.attnum=ANY(c.confkey) WHERE t.relname=''' || $1 || LOOP RETURN NEXT sql_result; END LOOP; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * FROM sql_get_schema_full('temp_visit') AS temp_schema(table1 name, column1 name, constname name, consttext text, table2 name, column2 name); This works fine. End Working Code Begin Failing Code CREATE OR REPLACE FUNCTION sql_get_schema_full(select_table text) RETURNS SETOF RECORD AS $$ DECLARE sql_result record; BEGIN FOR sql_result in EXECUTE 'SELECT t.relname, a.attname, c.conname, c.contype, c.consrc, ft.relname, fa.attname FROM (((pg_class AS t LEFT JOIN (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) AS a ON t.oid=a.attrelid) LEFT JOIN pg_constraint AS c ON t.oid=c.conrelid AND a.attnum=ANY(c.conkey)) LEFT JOIN pg_class AS ft ON ft.oid=c.confrelid) LEFT JOIN (SELECT attname, attnum, attrelid FROM pg_attribute WHERE attnum>0) AS fa ON ft.oid=fa.attrelid AND fa.attnum=ANY(c.confkey) WHERE t.relname=''' || $1 || LOOP RETURN NEXT sql_result; END LOOP; END; $$ LANGUAGE plpgsql SECURITY DEFINER; SELECT * FROM sql_get_schema_full('temp_visit') AS temp_schema(table1 name, column1 name, constname name, consttype char, consttext text, table2 name, column2 name); ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "sql_get_schema_full" line 11 at return next ** End Failing Code -- 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] postgre vs MySQL
On Wed, 12 Mar 2008 10:26:21 -0700 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > On Wed, Mar 12, 2008 at 10:15 AM, Alvaro Herrera > <[EMAIL PROTECTED]> wrote: > > Ivan Sergio Borgonovo wrote: > > > On Wed, 12 Mar 2008 09:13:14 -0700 > > > paul rivers <[EMAIL PROTECTED]> wrote: > > > > > > > For a database of InnoDB tables, people tend to replicate the > > > > database, and then backup the slave (unless the db is > > > > trivially > > > > > > That recalled me the *unsupported* feeling I have that it is > > > easier to setup a HA replication solution on MySQL. > > > > Well, if you have a crappy system that cannot sustain concurrent > > load or even be backed up concurrently with regular operation, > > one solution is to write a kick-ass replication system. > > > > The other solution is to enhance the ability of the system to > > deal with concurrent operation. > > > > We keep hearing how great all those Web 2.0 sites are; Slashdot, > > Flickr, etc; and they all run on farms and farms of MySQL > > servers, "because MySQL replication is so good". I wonder if > > replication is an actual _need_ or it's there just because the > > other aspects of the system are so crappy. > Reminds me of the saying that for each problem, there is a simple, > elegant solution that is completely wrong. It amazes me that slony, > being basically a "bolt on" replication solution has given me much > fewer problems than MySQL replication which is known for silent > failures. Slony is by no means perfect, but it is quite impressive > as both a replication tool and an upgrade tool. I heard about Slony as well (dho!)... I'm not complaining about anything... I do like PostgreSQL from a programmer point of view and it makes my life easier. I never pushed it to the limit I need replication, pooling etc... Just as there is a myth out there that mysql outperform pgsql... I'm here to testify there is another myth (?) that says that pg doesn't have an easy (erm kick-ass) replication system. I've no deep knowledge of Slashdot, Flickr or Google to say they don't "high level" of data integrity/coherence as the one pg offers and it is famous for... so I doubt they would hit the limitations of systems like Slony or pgpool etc... etc... and maybe they have no interest in the more advanced features and data integrity pg has to offer... > An awful lot of the sites running on MySQL are running on it > primarily because it's what they started with, and now it's hard to > switch because their code is chock full of mysqlisms like "select > field1, field2 from table group by field1" and so on that no other > database is going to swallow without throwing an error. For what is worth I mostly share your opinion... just Google makes me wonder... -- 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
Re: [GENERAL] PostgreSQL won't start
On Wed, Mar 12, 2008 at 10:49 AM, Lee Hachadoorian <[EMAIL PROTECTED]> wrote: > Last week I set up Postgres 8.3 on a WindowsXP machine. Had it up and > running and imported data. Now when I try to start the server (after > a machine restart) I get the message: > > > > pg_ctl: another server might be running; trying to start server anyway > PANIC: could not open control file "global/pg_control": Permission denied > > This application has requested the Runtime to terminate it in an unusual way. > Please contact the application's support team for more information. > server starting > > If it was running and now isn't, the most common cause is anti-virus software having an exclusive lock on a file. Or changing it's ownership or permissions. -- 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] PostgreSQL won't start
On Wed, Mar 12, 2008 at 10:49 AM, Lee Hachadoorian <[EMAIL PROTECTED]> wrote: > Last week I set up Postgres 8.3 on a WindowsXP machine. Had it up and > running and imported data. Now when I try to start the server (after > a machine restart) I get the message: > > > > pg_ctl: another server might be running; trying to start server anyway > PANIC: could not open control file "global/pg_control": Permission denied Or it could just be that you do already have another postmaster up and running already. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Functional Index Question
I am considering the utility value of creating a functional index on a name field. To minimize the number of invalid searches caused by spacing errors and mis-matched lettercase I am contemplating doing something like this: CREATE UNIQUE INDEX idxUF_table_column ON table (lower(trim(both ' ' from(regexp_replace(, /( ){2,}/g," " ) What I intend this to do is to squeeze out excess whitespace, strip off leading and trailing blanks, and then force the whole thing to lowercase. Is this idea worth pursuing and, if it is, is my regexp correct for the purpose intended? Not a regexp guy. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:[EMAIL PROTECTED] Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PostgreSQL won't start
It was far stupider than that. I had been playing around with a couple of different data clusters before doing a complete reinstall of PostgreSQL. I just realized I was trying to start a cluster that I was no longer using and the postgres account didn't have appropriate permissions for. It's one of those things where once you ask the question, you realize that the answer is implicit in the question. But a useful followup question is, how do I make this start itself when Windows starts? The service is set up to run as postgres and execute "C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\" This *is* pointing to the right data cluster (which I'm able to start successfully from the command line), but it's not starting automatically (even though it's configured to) and when I try to start it manually within the Component Services Manager, it generates the following error: Error 1069: The service did not start due to a logon failure. Thanks, Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center On Wed, Mar 12, 2008 at 2:05 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Wed, Mar 12, 2008 at 10:49 AM, Lee Hachadoorian > <[EMAIL PROTECTED]> wrote: > > > Last week I set up Postgres 8.3 on a WindowsXP machine. Had it up and > > running and imported data. Now when I try to start the server (after > > a machine restart) I get the message: > > > > > > > pg_ctl: another server might be running; trying to start server anyway > > PANIC: could not open control file "global/pg_control": Permission denied > > Or it could just be that you do already have another postmaster up and > running already. > -- 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] postgre vs MySQL
--- Greg Smith <[EMAIL PROTECTED]> wrote: > If you look at the link I passed along before, you'll see the > difference > with MySQL is that they've been abusing their customers with minor > point > releases that try to add new features. Instead some of these > introduce > functional regressions, which often hang around for a whole long > longer > than two days after being noticed (this isn't even considering the > delays > before those fixes make their way back into the open source > product, some > only even go to paying customers). This is something I noticed too when looking at MySQL and postgres. The frequency of bug fixes and features, some coming over pretty quickly from the community release of MySQL scared me. ___ Rise to the challenge for Sport Relief with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ -- 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] FATAL: could not reattach to shared memory (Win32)
Added to TODO: * Remove use of MAKE_PTR and MAKE_OFFSET macros http://archives.postgresql.org/pgsql-general/2007-08/msg01510.php --- Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > > "Trevor Talbot" <[EMAIL PROTECTED]> writes: > >> I gather postgres depends on it being at the same address, and fixing that > >> isn't trivial? > > > I haven't been following the rest of the thread so I'm not sure if this is > > important. But no, fixing that should be relatively trivial as there are > > already some configurations where it's not the case (the EXEC_BACKEND case I > > believe). The rest of the system uses a shared memory base pointer and > > references everything relative to that. > > That hasn't been the case for quite a few years, and we're not going back. > The pointer-to-offset-and-back gymnastics that that required were > utterly destructive to code readability and maintainability, mainly > because if everything stored in shmem data structures is an "offset" > then you can't get any useful error checking from the compiler about how > you are using the fields. It's like decreeing that every pointer > must be declared "void *" and cast to something else when it's used. > > There are a few old bits of code that still use MAKE_PTR/MAKE_OFFSET, > but I think it's mostly just that no one's bothered to rewrite the code > for SHM_QUEUE linked lists. The vast majority of our shmem structures > use regular pointers, and have for years. > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] FATAL: could not reattach to shared memory (Win32)
Added to Win32 TODO: o Diagnose problem where shared memory can sometimes not be attached by postmaster children http://archives.postgresql.org/pgsql-general/2007-08/msg01377.php --- Magnus Hagander wrote: > Shelby Cain wrote: > >> - Original Message From: Magnus Hagander > >> <[EMAIL PROTECTED]> To: Alvaro Herrera > >> <[EMAIL PROTECTED]> Cc: Terry Yapt <[EMAIL PROTECTED]>; > >> pgsql-general@postgresql.org Sent: Thursday, August 23, 2007 > >> 3:43:32 PM Subject: Re: [GENERAL] FATAL: could not reattach to > >> shared memory (Win32) > >> > >> > >> 8.3 will have a new way to deal with shared mem on win32. It's the > >> same underlying tech, but we're no longer trying to squeeze it into > >> an emulation of sysv. With a bit of luck, that'll help :-) > >> > >> //Magnus > >> > > > > Wild guess on my part... could that error be the result of an attempt > > to map shared memory into a process at a fixed location that just > > happens to already be occupied by a dll that Windows had decided to > > relocate? > > Not that wild a guess, really :-) I'd say it's a very good possibility - > but I have no idea why it'd do that, since all backends load the same > DLLs at that stage. > > //Magnus > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] [pgsql-www] PostgreSQL user documentation wiki open for business
On Wed, Mar 12, 2008 at 11:53 AM, Dave Page <[EMAIL PROTECTED]> wrote: > I'm pleased to announce that wiki.postgresql.org is now open for business! Awesome! -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.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] postgre vs MySQL
Glyn Astill wrote: > > --- Greg Smith <[EMAIL PROTECTED]> wrote: > > > If you look at the link I passed along before, you'll see the > > difference > > with MySQL is that they've been abusing their customers with minor > > point > > releases that try to add new features. Instead some of these > > introduce > > functional regressions, which often hang around for a whole long > > longer > > than two days after being noticed (this isn't even considering the > > delays > > before those fixes make their way back into the open source > > product, some > > only even go to paying customers). > > This is something I noticed too when looking at MySQL and postgres. > The frequency of bug fixes and features, some coming over pretty > quickly from the community release of MySQL scared me. MySQL has incentives to _not_ make their community release production-quality. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: text search configuration "pg_catalog.english" does not exist
Tim Child <[EMAIL PROTECTED]> writes: > There is other things in that directory including dict_snowball.so Well, that's even stranger. It's real hard to see how the src/backend/snowball Makefile would have built dict_snowball.so and not built the correct version of snowball_create.sql. Anyway, if you can get the correct snowball_create.sql script from another machine, you should be able to limp along. 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] postgre vs MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 14:35:19 -0400 (EDT) Bruce Momjian <[EMAIL PROTECTED]> wrote: > > This is something I noticed too when looking at MySQL and postgres. > > The frequency of bug fixes and features, some coming over pretty > > quickly from the community release of MySQL scared me. > > MySQL has incentives to _not_ make their community release > production-quality. > This thread is making my talk at MySQLCon very interesting. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2CP+ATb/zqfZUUQRAg78AKCNPJsFvLY7I/zxIXVZ9ndFtf9aMACdF7J+ QJQWDqDMLoj4lTiUIyGx1Ps= =8Xe7 -END PGP SIGNATURE- -- 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] postgre vs MySQL
--- Bruce Momjian <[EMAIL PROTECTED]> wrote: > Glyn Astill wrote: > > > > --- Greg Smith <[EMAIL PROTECTED]> wrote: > > > > > If you look at the link I passed along before, you'll see the > > > difference > > > with MySQL is that they've been abusing their customers with > minor > > > point > > > releases that try to add new features. Instead some of these > > > introduce > > > functional regressions, which often hang around for a whole > long > > > longer > > > than two days after being noticed (this isn't even considering > the > > > delays > > > before those fixes make their way back into the open source > > > product, some > > > only even go to paying customers). > > > > This is something I noticed too when looking at MySQL and > postgres. > > The frequency of bug fixes and features, some coming over pretty > > quickly from the community release of MySQL scared me. > > MySQL has incentives to _not_ make their community release > production-quality. > I mean features being pulled into the enterprise release that haven't had much time to be tested even in the community release. ___ Rise to the challenge for Sport Relief with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ -- 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] postgre vs MySQL
At 09:47 PM 3/11/2008, rrahul wrote: Hi, I am a database professional but have never used Postgre. My client was exploring the posiblity of using Postgre instead of Mysql and wnated to know the comments from the community. I waned you people you post your views on the following comparision points 1] Performance 2] Scalablity 3] community support 4] Speed 5] ease of use 6] robustness Any major clients of the two. You can add you own points too. The "advantage" of MySQL is it looks good on paper with all the ticks on the extensive feature list so it's easy to convince bosses to use it. But the problem with MySQL is you often can't use all the "advertised features" at the same time, some of them are mutually exclusive. For example, with MySQL if you want fast single user selects or insert speeds you use MyISAM tables, but if you start to need transactions or high concurrency writes you have to use InnoDB which is significantly slower. Want to have a consistent backup of your MySQL database? For MyISAM tables you have to lock all tables till the backup is done, and that can affect performance a lot. OK so you use InnoDB. But when the time comes to _restore_a multiGB innodb table, you might find innodb a bit too slow. Worse, apparently "fun" things happen if someone halts the restore process halfway ;). Guess what happens if you have a mix of table types. A "solution" of course is to have multiple database servers with a master server replicating to a slave server that's used for backups, and resort to backing up stuff in on-disk format - shutdown slave and copy the files. This makes restoring faster. But after all this, MySQL stops looking so easy right? In fact such a multi database set up just to do proper backups and restores resembles a bad implementation of Postgresql's MVCC :). Basically with Postgresql, there's a lot less of this "If you want to use Cool Feature A, you can't have Wonderful Feature B" thing. I did a simple mass insert test (followed by backup with pg_dump or mysqldump and restore ) and postgresql 8.1 is faster or as fast as MySQL 5.0.26 with MyISAM tables, and faster than MySQL with innodb tables- default packages from suse 10.2 with tuning done for MySQL (increase of buffers etc) but postgresql is as per suse 10.2 defaults. I use MySQL daily at my workplace, and it's not something I recommend you use if you had a choice. In the old days (before version 6.x) postgresql wasn't good, but postgresql is way ahead now. Lastly, the other problem with MySQL is its Innodb and BDB stuff are now owned by Oracle. While Oracle is not squeezing MySQL yet, who knows what will happen a few years later. Regards, Link. -- 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] pain of postgres upgrade with extensions
paul rivers <[EMAIL PROTECTED]> writes: > Is this something that wouldn't be fixed by: > - dump 8.2 database > - load dump into 8.3 database > - for each extension, run the 8.2 drop extension script in 8.2's contrib > - for each extension, run the 8.3 install extension script in 8.3's contrib The trouble with that is that step 3 also drops anything that depends on the extension. Doesn't work very well for data types, for instance, since you'd lose any user-table columns of that type. The trick that seems to work fairly well (and ought to be better documented) is - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring "object already exists" errors There is a TODO to figure out some cleaner way of handling this sort of thing ... 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] Trigger to run @ connection time?
On Tue, Mar 11, 2008 at 4:51 PM, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote: > On 12/03/2008, Kynn Jones <[EMAIL PROTECTED]> wrote: > Of course I may not have quite > understood how that "this procedure adds useful definitions, > mostly subs, to Perl's main package. This needs to be done > for each connection" is meant to work. > What I mean is illustrated by the following (extremely artificial and clumsy) example: CREATE OR REPLACE FUNCTION setup_perl () RETURNS void AS $PERL$ # globals $::TRUE = 1; $::FALSE = 0; { my $leading_ws = qr/\A\s*/; my $trailing_ws = qr/\s*\z/; # The next assignment defines the Perl function main::trim(); # it has almost the same effect as writing # sub trim { ... } # at the top level scope (in the main package), except that # the definition happens at run time rather than at compile # time. *trim = sub { local $_ = shift; s/$leading_ws//; s/$trailing_ws//; return $_; }; } $PERL$ LANGUAGE plperl IMMUTABLE; CREATE OR REPLACE FUNCTION is_foo ( TEXT ) RETURNS BOOLEAN AS $PERL$ return trim( shift ) eq 'foo' ? $::TRUE : $::FALSE; $PERL$ LANGUAGE plperl IMMUTABLE; CREATE OR REPLACE FUNCTION is_bar ( TEXT ) RETURNS BOOLEAN AS $PERL$ return trim( shift ) eq 'bar' ? $::TRUE : $::FALSE; $PERL$ LANGUAGE plperl IMMUTABLE; Notice that is_foo() and is_bar() both rely on the *perl* function trim. They also refer to the Perl global variables $::TRUE and $::FALSE. This technique facilitates the reuse of Perl code in two ways. First, individual Perl subroutines can be defined once and called from various PLPERL procedures. Second, it simplifies the cut-and-paste porting of Perl code (which often uses subroutines and global or file-scoped lexical variables) straight into to PLPERL. (I wrote more about this technique recently, in the post with the subject line "On defining Perl functions within PLPERL code.") (BTW, notice that, the function trim is actually a closure: it uses a couple of lexical variables, $leading_ws and $trailing_ws, that are defined in the enclosing scope; i.e. these definitions need to happen only once. Such variables serve the same purpose as that of C static variables. The ease of defining such closures is an added bonus of this technique. In this artificial example, of course, this benefit is negligible, but when the computation of such constants is time-consuming, this could be a useful little optimization.) Now, note that if we try to use is_foo() before invoking perl_setup(), it will fail: my_db=> select is_foo( ' foo ' ); ERROR: error from Perl function: Undefined subroutine &main::trim called at line 2. my_db=> select setup_perl(); setup_perl (1 row) (BTW, is there a way to avoid the useless output above?) my_db=> select is_foo( ' foo ' ); is_foo t (1 row) my_db=> select is_bar( ' foo ' ); is_bar f (1 row) That's why it would be nice to run perl_setup() automatically at the beginning of each session. Granted, one workaround would be to include the line spi_query( 'SELECT setup_perl()' ) unless $::TRUE; at the top of ever PLPERL function that required the definitions provided by setup_perl(). Something like an ON CONNECT trigger would obviate this small annoyance, but I guess that's not a possibility at the moment. Kynn
Re: [GENERAL] postgre vs MySQL
Glyn Astill <[EMAIL PROTECTED]> writes: > --- Bruce Momjian <[EMAIL PROTECTED]> wrote: >> MySQL has incentives to _not_ make their community release >> production-quality. > I mean features being pulled into the enterprise release that haven't > had much time to be tested even in the community release. For the last year or so it's actually the other way around: they put things into the for-pay version that have *not* hit the community version yet. So you pay to be a beta tester ;-) 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] pain of postgres upgrade with extensions
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 - dump version N database - create empty version N+1 database - install N+1's version of each needed contrib module into new database - restore dump, ignoring "object already exists" errors There is a TODO to figure out some cleaner way of handling this sort of thing ... I think I smell a GSOC project I think there is a slight misunderstanding here, I was refering to extensions items such as postgis, plr, pgperl, etc. These have a slight different foot print to the projects in the contrib directory. Dave - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -END PGP SIGNATURE- begin:vcard fn:David Potts n:Potts;David x-mozilla-html:FALSE version:2.1 end:vcard -- 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] pain of postgres upgrade with extensions
On Wed, Mar 12, 2008 at 07:34:03PM -, Greg Sabino Mullane wrote: > > - dump version N database > > - create empty version N+1 database > > - install N+1's version of each needed contrib module into new database > > - restore dump, ignoring "object already exists" errors > > > > There is a TODO to figure out some cleaner way of handling this sort > > of thing ... > > I think I smell a GSOC project The most promising way I remember was to create "packages" which wrap a collection of types/tables/functions. The normal pg_depend structure would track this and make sure that things didn't get deleted. On the other side it would provide a way for pg_dump it identify the components and not dump them. The SQL standard has something called "modules" but I don't remember if it was at all compatable. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL user documentation wiki open for business
Hello I am translating tips from czech language, that I would copy to wiki. Please, I invite any help with language fix - my translation is really basic. current work is on: http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks Thank you Pavel Stehule On 12/03/2008, Dave Page <[EMAIL PROTECTED]> wrote: > On Wed, Mar 12, 2008 at 5:55 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: > > Hello Dave, > > > > I have some tips and tricks/ so I can copy this tips for start. I > > translated it, but it's need language correction. I am not native > > speaker. > > > > http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks > > Hi Pavel, > > Please feel free to add them, and if you like, add a note at the top > asking people to fix any errors they may find in the translation. The > beauty of a wiki is that anyone who is passing by can help improve > things :-) > > Unfortunately I only really have time to work on the infrastructure, > Windows issues and pgAdmin. > > > > -- > Dave Page > EnterpriseDB UK Ltd: http://www.enterprisedb.com > PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk > -- 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] pain of postgres upgrade with extensions
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > - dump version N database > - create empty version N+1 database > - install N+1's version of each needed contrib module into new database > - restore dump, ignoring "object already exists" errors > > There is a TODO to figure out some cleaner way of handling this sort > of thing ... I think I smell a GSOC project - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200803121533 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkfYMBMACgkQvJuQZxSWSsjmmwCg1JvB0G2py5jSbJdSZpWR8YyV D4YAoLg2ZinEEGoNEU7S2mcL3bqhmNIh =7pvA -END PGP SIGNATURE- -- 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] Function Returning SETOF RECORD: Trouble With Char Type
"Angus B. Atkins-Trimnell" <[EMAIL PROTECTED]> writes: > I am having trouble with a function designed to return all column > constraints on a table. I think the problem is that you're declaring the contype return column as char (ie, character(1)) when pg_constraint.contype is actually "char" (a historical PG datatype that's really just a single byte). The naming confusion is unfortunate but we've never bitten the bullet to rename "char" to something else. Use quotes, or explicitly cast the catalog column to regular char in the function's query. 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] postgre vs MySQL
Alvaro Herrera wrote: Ivan Sergio Borgonovo wrote: On Wed, 12 Mar 2008 09:13:14 -0700 paul rivers <[EMAIL PROTECTED]> wrote: For a database of InnoDB tables, people tend to replicate the database, and then backup the slave (unless the db is trivially That recalled me the *unsupported* feeling I have that it is easier to setup a HA replication solution on MySQL. Well, if you have a crappy system that cannot sustain concurrent load or even be backed up concurrently with regular operation, one solution is to write a kick-ass replication system. The other solution is to enhance the ability of the system to deal with concurrent operation. We keep hearing how great all those Web 2.0 sites are; Slashdot, Flickr, etc; and they all run on farms and farms of MySQL servers, "because MySQL replication is so good". I wonder if replication is an actual _need_ or it's there just because the other aspects of the system are so crappy "Kick-ass" imho really means "really simple to setup" and included as part of the standard db. There are all kinds of corner cases that can bite you with MySQL replication. Offhand, I wager most of these (at least in InnoDB) result from the replication "commit" status of a transaction is in the binlogs, which is not the same as the InnoDB database commit status in the .ibd files. Writing out binlog entries happens at a higher level than the storage engine, and so it's not hard to imagine what can go wrong there. There are a few my.cnf settings that let you really roll the dice with data integrity based on this dichotomy, if you so choose. In those high volume shops, imho replication is a requirement, but in part to overcome technical limitations of MySQL. Or to phrase it from a MySQL point of view, to do it the MySQL way. If you have 50-ish minutes, this video by the YouTube people talks about their evolution with MySQL (among many other things) : http://video.google.com/videoplay?docid=-6304964351441328559 The summary from the video is: - Start with a MySQL instance using InnoDB - Go to 1-M replication, and use the replicants as read-only version. - Eventually the cost of replication outweighs the gains, so go to database sharding - Keep 1-M replication within a shard group to allow easy backups of a slave, some read-only use of the slaves, and a new master in case of master failure (i.e. high availability) Almost everyone finds MyISAM unworkable in large scale environments because of the repairs necessary post-crash. Big complaints about MySQL high-volume shops often, imho, come back to : - You can only have so many active threads in the InnoDB storage engine module at a time. See e.g.: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_thread_concurrency - Auto_increment columns as pkeys in InnoDB tables are practically required, yet severely limited scalability due to how a transaction would lock the structure to get the next auto-increment (significantly improved in 5.1) - Shutting down a MySQL engine can take forever, due partly dirty page writes, partly due to insert buffer merging. See: http://dev.mysql.com/doc/refman/5.1/en/innodb-insert-buffering.html There are other complaints you'd expect people to have, but don't seem to get talked about much, because people are so used to (from my point of view) working around them. For example, statistics on an InnoDB table are calculated when the table is first accessed, but not stored anywhere, so there are extra costs on database startup. The backup issue with InnoDB has already been covered. Tablespace management in InnoDB seems exceptionally primitive, and is helped somewhat by the tablespace-per-table option. There are many more, again imho. Paul -- 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] pain of postgres upgrade with extensions
On Mar 12, 2008, at 3:19 PM, Tom Lane wrote: - restore dump, ignoring "object already exists" errors Couldn't one use the dump listing feature of pg_restore and comment out the extensions when restoring? Not likely to be a big improvement over "ignore" errors :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table size in 8.3
Guys, I've created 2 sample tables with 1 column each - type char(1) and type integer. After inserting equal number of rows (4M or more) tablesizes are exactly the same, while I would expect table with char(1) to be slighly smaller... What's causing it ? Thanks! Server version is 8.3. Best regards, Alex Vinogradovs -- 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] Checking is TSearch2 query is valid
Add psql TODO: o Include the symbolic SQLSTATE name in verbose error reports http://archives.postgresql.org/pgsql-general/2007-09/msg00438.php --- Alvaro Herrera wrote: > Tom Lane wrote: > > Benjamin Arai <[EMAIL PROTECTED]> writes: > > > Is there a specific exception code for: > > > ERROR: no operand in tsearch query: "(" > > > > regression=# \set VERBOSITY verbose > > regression=# select to_tsquery('('); > > ERROR: 42601: no operand in tsearch query: "(" > > LOCATION: gettoken_query, tsquery.c:163 > > > > Seems to be "SYNTAX_ERROR". > > Hmm, maybe we should be displaying the textual name of the SQLSTATE > somehow. > > -- > Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC > "El d?a que dejes de cambiar dejar?s de vivir" > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Functional Index Question
On Wed, Mar 12, 2008 at 11:46:12AM -0400, James B. Byrne wrote: > CREATE UNIQUE INDEX idxUF_table_column ON table > (lower(trim(both ' ' from(regexp_replace(, /( ){2,}/g," " ) > What I intend this to do is to squeeze out excess whitespace, strip off > leading and trailing blanks, and then force the whole thing to lowercase. > Is this idea worth pursuing and, if it is, is my regexp correct for the > purpose intended? if you'd try the query you would see instantly: # select lower(trim(both ' ' from(regexp_replace(' depeSz hub ', /( ){2,}/g," " ; ERROR: syntax error at or near "/" LINE 1: ...im(both ' ' from(regexp_replace(' depeSz hub ', /( ){2,}/g... correct way: select lower(trim(both ' ' from(regexp_replace(' depeSz hub ', E'\\s+', ' ', 'g' ; now. i would suggest *not* to use this as base for index. make a wrapper function istead: create function cleaned(text) returns text as $BODY$ select lower(trim(both ' ' from(regexp_replace($1, E'\\s+', ' ', 'g' ; $BODY$ language sql immutable; now you can simply: create unique index xxx on table ( cleaned() ); plus your sql's will look saner. instead of: select * from table where lower(trim(both ' ' from(regexp_replace(, E'\\s+', ' ', 'g' = lower(trim(both ' ' from(regexp_replace('some_string', E'\\s+', ' ', 'g' ; you will have: select * from table where cleaned() = cleaned('some_string'); depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- 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] PostgreSQL won't start
On Wed, 2008-03-12 at 14:22 -0400, Lee Hachadoorian wrote: > It was far stupider than that. I had been playing around with a > couple of different data clusters before doing a complete reinstall of > PostgreSQL. I just realized I was trying to start a cluster that I > was no longer using and the postgres account didn't have appropriate > permissions for. It's one of those things where once you ask the > question, you realize that the answer is implicit in the question. > > But a useful followup question is, how do I make this start itself > when Windows starts? The service is set up to run as postgres and > execute > > "C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N > "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\" > > This *is* pointing to the right data cluster (which I'm able to start > successfully from the command line), but it's not starting > automatically (even though it's configured to) and when I try to start > it manually within the Component Services Manager, it generates the > following error: > > Error 1069: The service did not start due to a logon failure. > The error message tells you exactly what the problem. The service account specified for the service cannot log in. It's either the wrong password, the wrong username, or the account is disabled/expired. //Magnus -- 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] table size in 8.3
Alex Vinogradovs <[EMAIL PROTECTED]> writes: > I've created 2 sample tables with 1 column each - type > char(1) and type integer. After inserting equal number > of rows (4M or more) tablesizes are exactly the same, while > I would expect table with char(1) to be slighly smaller... > What's causing it ? Thanks! Alignment padding ... rows are always padded out to a MAXALIGN multiple. 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
[GENERAL] Subplan and index usage
Consider the following case which is almost exact snapshot of part of our scheme: Table "cities" Column| Type | Modifiers | Description --++---+- ficity_id| integer| not null | ficountry_id | integer| | firegion_id | integer| | fsname | character varying(100) | | fsname_ru| character varying(200) | | Indexes: "pk_geocities" PRIMARY KEY, btree (ficity_id) "idx_cities_name" btree (lower(fsname::text) varchar_pattern_ops) "idx_cities_name_ru" btree (lower(fsname_ru::text) varchar_pattern_ops) "idx_geocities_country_id" btree (ficountry_id) "idx_geocities_region_id" btree (firegion_id) Foreign-key constraints: "fk_geocities_country_id" FOREIGN KEY (ficountry_id) REFERENCES countries(ficountry_id) ON UPDATE CASCADE ON DELETE CASCADE "fk_geocities_region_id" FOREIGN KEY (firegion_id) REFERENCES regions(firegion_id) ON UPDATE CASCADE ON DELETE CASCADE Table "cities_name_words" Column | Type | Modifiers | Description ---++---+- ficity_id | integer| not null | fsword| character varying(200) | not null | Indexes: "idx_cities_name_words_city_id" btree (ficity_id) "idx_cities_name_words_word" btree (fsword varchar_pattern_ops) Foreign-key constraints: "fk_cities_name_words_city_id" FOREIGN KEY (ficity_id) REFERENCES cities(ficity_id) ON UPDATE CASCADE ON DELETE CASCADE Table "cities_name_ru_words" Column | Type | Modifiers | Description ---++---+- ficity_id | integer| not null | fsword| character varying(200) | not null | Indexes: "idx_cities_name_ru_words_city_id" btree (ficity_id) "idx_cities_name_ru_words_word" btree (fsword varchar_pattern_ops) Foreign-key constraints: "fk_cities_name_ru_words_city_id" FOREIGN KEY (ficity_id) REFERENCES cities(ficity_id) ON UPDATE CASCADE ON DELETE CASCADE This is the part of geo location database. The purpose of cities_name_words and cities_name_ru_words is to facilitate indexing on separate words in city name - they contain words of fsname or fsname_ru respectively of the corresponding record in cities if it has more than word. Cities has about 19 records, cities_name_words about 8 and cities_name_ru_words about 5000. Now the query for city by name looks like this: select * from cities where ( ficity_id in ( select ficity_id from cities_name_words where fsword like 'novgorod%' union select ficity_id from cities_name_ru_words where fsword like 'novgorod%' ) or lower(fsname) like 'novgorod%' or lower(fsname_ru) like 'novgorod%' ) QUERY PLAN Seq Scan on cities (cost=16.63..5949.26 rows=95014 width=60) Filter: ((hashed subplan) OR (lower((fsname)::text) ~~ 'novgorod%'::text) OR (lower((fsname_ru)::text) ~~ 'novgorod%'::text)) SubPlan -> Unique (cost=16.61..16.62 rows=2 width=4) -> Sort (cost=16.61..16.62 rows=2 width=4) Sort Key: cities_name_words.ficity_id -> Append (cost=0.00..16.60 rows=2 width=4) -> Index Scan using idx_cities_name_words_word on cities_name_words (cost=0.00..8.31 rows=1 width=4) Index Cond: (((fsword)::text ~>=~ 'novgorod'::text) AND ((fsword)::text ~<~ 'novgoroe'::text)) Filter: ((fsword)::text ~~ 'novgorod%'::text) -> Index Scan using idx_cities_name_ru_words_word on cities_name_ru_words (cost=0.00..8.27 rows=1 width=4) Index Cond: (((fsword)::text ~>=~ 'novgorod'::text) AND ((fsword)::text ~<~ 'novgoroe'::text)) Filter: ((fsword)::text ~~ 'novgorod%'::text) Notice how it uses proper indexes in subplan and goes for sequence scan on the main table. If the where- conditions are applied separately it uses indexes as expected: select * from cities where ( lower(fsname) like 'novgorod%' or lower(fsname_ru) like 'novgorod%' ) QUERY PLAN Bitmap Heap Scan on cities (cost=8.57..12.59 rows=1 width=60) Recheck Cond: ((lower((fsname)::text) ~~ 'novgorod%'::text) OR (lower((fsname_ru)::text) ~~ 'novgorod%'::text)) Filter: ((lower((fsname)::text) ~~ 'novgorod%'::text) OR (lower((fsname_ru)::text) ~~ 'novgorod%'::text)) -> BitmapOr (cost=8.57..8.57 rows=1 width=0) -> Bitmap Index Scan on idx_cities_name (cost=0.00..4.29 rows=1 width=0) Index Cond: ((lower((fsname)::text) ~>=~ 'novgorod'::text) AND (lower((fsname)::text) ~<~ 'novgoroe'::text)) -> Bitmap Index Scan on idx_cities_name_ru (cost=
[GENERAL] Service Account password
Is it possible to change the Service Account password for Service Account name postgres and if so how? Windows XP Home SP2 PostgreSQL v8.3.0 -- 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] Trouble with Savepoints in postgres
On Mar 12, 8:11 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: > Please always ensure that the list is copied on replies (use "Reply to > all") so that other people can help you. > > sam escribió: > > > On Mar 11, 5:39 pm, [EMAIL PROTECTED] (Alvaro Herrera) wrote: > > > sam escribió: > > > > > Iam not able to understand if this is a version problem or the way iam > > > > using savepoints is wrong.Please advice. > > > > It is. You cannot use savepoints in PL/pgSQL functions (or any function > > > for that matter). You can use EXCEPTION clauses instead. > > Then u please tell me how save points can be used...The > > program iam working on is throwing an 'LIMIT EXCEEDED' error so iam > > trying to commit data so that a total rollback does not occur.Like > > commiting data after every 1000 transactions. I figured that > > savepoints would be the solution. > > No, savepoints will not help you there. No matter what you do, you > cannot commit in the middle of a function. > > What's the limit being exceeded? Perhaps you can solve your problem > some other way. > > -- > Alvaro Herrera Developer,http://www.PostgreSQL.org/ > "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio) > > -- > Sent via pgsql-general mailing list ([EMAIL PROTECTED]) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general Also can u please tell mewhy i get the error...ERROR: SPI_execute_plan failed executing query "PREPARE TRANSACTION 'foo'": SPI_ERROR_TRANSACTION I get this error when i also use COMMIT, ROLLBACK.does this mean a patch is missing ? Thanks Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] porting vb6 code to pgplsql, referencing fields
Hi everyone, I'm trying to port some vb6 code to pgplsql (PostgreSQL 8.3 winxp) that code is Const f2_MAX_TRAMS = 5 Dim f2_rTarifaA as new ADODB.Recordset Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua Dim Ma(f2_MAX_TRAMS) As Long' m3 aigua tarifa Dim i As Integer, j As Integer ' indexs matrius ... ( open connection, open recordset, etc ) ... -- fill array with field values of M3TRAM1, ..., M3TRAM5 -- PREU1, ..., PREU5 for i = 1 to f2_MAX_TRAMS Ma(i) = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value next in pgplsql, more or less DECLARE c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL; f2_MAX_TRAMS CONSTANT INTEGER := 5; Ma INTEGER[5]; Mpa NUMERIC(10,2)[5]; row_tfa f2_tarifa_a%rowtype; BEGIN OPEN c_tarifa_a (datafac, f2_Mtar); FETCH c_tarifa_a INTO row_tfa; CLOSE c_tarifa_a; For i IN 1..f2_MAX_TRAMS LOOP Ma[i] := row_tfa. ? -- "M3TRAM" + CStr(i)).Value Mpa[i] := row_tfa. ? -- "PREU" + CStr(i)).Value END LOOP; END I would like to know some tips about: How can I declare arrays especifying the size with a constant, but the most important is how can I reference the fields inside de loop Thanks in advance Josep
Re: [GENERAL] ERROR: text search configuration "pg_catalog.english" does not exist
There is a file here with the same name: /Library/PostgreSQL8/lib/postgresql/plpgsql.so So I could try and run that snowball_create.sql script from src (which I have downloaded) and try and get the required functionality? That would be the easiest for now. On the production system I will compile my own PostgreSQL but for now I wanted a minimum of fuss setup ;-) Thanks again for all the help. Tim. On 12 Mar 2008, at 17:03, Tom Lane wrote: Tim Child <[EMAIL PROTECTED]> writes: In my snowball_create.sql I find: -- No language-specific snowball dictionaries, for lack of shared library support Really!? This build comes from postgresqlformac.com the "Unified Installer - 8.3.0 (PostgreSQLforMac)" Hm, do they have plpgsql or any of the other PLs? It's hard to credit that anyone would put out a build without shared library support. There's too much functionality that goes missing. Now I am considering that it might have been better to compile my own, but before I do this is there anyway to get the libraries / dictionaries installed? What you need is a non-lobotomized build. You should be able to fix the problem without re-initdb, if you need to, by running the real snowball_create.sql script against each of your databases. 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] ERROR: text search configuration "pg_catalog.english" does not exist
There is other things in that directory including dict_snowball.so ascii_and_mic.so latin2_and_win1250.so utf8_and_big5.so utf8_and_gbk.so cyrillic_and_mic.so latin_and_mic.so utf8_and_cyrillic.so utf8_and_iso8859.so dict_snowball.sopgxs utf8_and_euc_cn.so utf8_and_iso8859_1.so euc_cn_and_mic.so plperl.soutf8_and_euc_jis_2004.so utf8_and_johab.so euc_jis_2004_and_shift_jis_2004.so plpgsql.soutf8_and_euc_jp.so utf8_and_shift_jis_2004.so euc_jp_and_sjis.so plpython.soutf8_and_euc_kr.so utf8_and_sjis.so euc_kr_and_mic.so pltcl.so utf8_and_euc_tw.so utf8_and_uhc.so euc_tw_and_big5.so utf8_and_ascii.so utf8_and_gb18030.so utf8_and_win.so I have already put in a bug report to them, but have no idea how active the maintainer is in building new versions. Thanks, Tim On 12 Mar 2008, at 18:04, Tom Lane wrote: Tim Child <[EMAIL PROTECTED]> writes: There is a file here with the same name: /Library/PostgreSQL8/lib/postgresql/plpgsql.so What else is in that directory? If you don't have dict_snowball.so it's not gonna work. So I could try and run that snowball_create.sql script from src (which I have downloaded) and try and get the required functionality? What I suspect at this point is that postgresqlformac.com hack up the build process for .so's in some weird way, and that they neglected to apply their hack to the Snowball stemmer library. You should report that to them as a bug in their packaging. 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] ERROR: text search configuration "pg_catalog.english" does not exist
In my snowball_create.sql I find: -- No language-specific snowball dictionaries, for lack of shared library support This build comes from postgresqlformac.com the "Unified Installer - 8.3.0 (PostgreSQLforMac)" Now I am considering that it might have been better to compile my own, but before I do this is there anyway to get the libraries / dictionaries installed? Thanks again, Tim On 12 Mar 2008, at 16:07, Tom Lane wrote: Tim Child <[EMAIL PROTECTED]> writes: If I do \dF: Schema | Name | Description ++-- pg_catalog | simple | simple configuration (1 row) Huh. Seems like initdb forgot to install all the Snowball stemmers. What do you find in $SHAREDIR/snowball_create.sql? (If you're not sure where your SHAREDIR is, pg_config --sharedir will tell you.) Where did this build of Postgres come from, anyway? regards, tom lane
[GENERAL] Using PL/R for predictive analysis of data.
Hi Everyone, I am wanting to ask some opinions on implementing PL/R into V8.3 on Win32. I have a need to be able to perform some relatively demanding statistical functions as the basis of producing data for reports. In short R appears to have more than enough capability to do the job (from a statistical perspective), however there doesnt seem to be that much discussion on using the PL/R implementation, or for that matter tutorials on using PL/R. What I would like to know is: 1/ Is is possible to create a view that has its columns based on the output of a PL/R function? 2/ Are there special considerations for the source data? 3/ Has anyone any experience with NonLinear Regression Analysis using PL/R to predict future outcomes as a resultset? (ie/ the equivalent of output from an SQL SELECT statement) Any advice or counsel would be greatly appreciated. I am not super famailar with Postgres, but I am famailar with other large scale databases as well as some desktop ones so dont be afraid to throw technical answers if you need to :-) Greatly appreciated The Frog -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] porting vb6 code to pgplsql, referencing fields
Hi everyone, I'm trying to port some vb6 code to pgplsql (PostgreSQL 8.3 winxp) that code is Const f2_MAX_TRAMS = 5 Dim f2_rTarifaA as new ADODB.Recordset Dim Mpa(f2_MAX_TRAMS) As Double ' preu aigua Dim Ma(f2_MAX_TRAMS) As Long' m3 aigua tarifa Dim i As Integer, j As Integer ' indexs matrius ... ( open connection, open recordset, etc ) ... -- fill array with field values of M3TRAM1, ..., M3TRAM5 -- PREU1, ..., PREU5 for i = 1 to f2_MAX_TRAMS Ma(i) = f2_rTarifaA.Fields("M3TRAM" + CStr(i)).Value Mpa(i) = f2_rTarifaA.Fields("PREU" + CStr(i)).Value next in pgplsql, more or less DECLARE c_tarifa_c CURSOR (dfac DATE, key INTEGER) IS SELECT * FROM F2_TARIFA_C WHERE TIPUS = key AND dfac BETWEEN DINICI AND DFINAL; f2_MAX_TRAMS CONSTANT INTEGER := 5; Ma INTEGER[5]; Mpa NUMERIC(10,2)[5]; row_tfa f2_tarifa_a%rowtype; BEGIN OPEN c_tarifa_a (datafac, f2_Mtar); FETCH c_tarifa_a INTO row_tfa; CLOSE c_tarifa_a; For i IN 1..f2_MAX_TRAMS LOOP Ma[i] := row_tfa. ? -- "M3TRAM" + CStr(i)).Value Mpa[i] := row_tfa. ? -- "PREU" + CStr(i)).Value END LOOP; END I would like to know some tips about: How can I declare arrays especifying the size with a constant, but the most important is how can I reference the fields inside de loop Thanks in advance Josep
Re: [GENERAL] postgre vs MySQL
Well, if you have a crappy system that cannot sustain concurrent load or even be backed up concurrently with regular operation, one solution is to write a kick-ass replication system. Still, it would be nice to have a kick-ass replication system for PG, too. We've been toying with WAL archiving and backup db recovery, which works pretty well it seems as it appears to support all of our tables (not just those with an explicit primary key) and does the DDL stuff for creating/altering/dropping tables, columns, etc. The downside is that the backup is not operational in order to run even run a SELECT against, and because it's asynchronous in nature, there's always a window of data loss for transactions written to the WAL that haven't been archived yet. David -- 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] Relocation error:/usr/lib/libpq.so.5:undefinedsymbol: krb5_cc_get_principal
Hi Devrim, When I tried to install, I got this error. [EMAIL PROTECTED] pq8.2.6]# rpmbuild --rebuild --define 'buildrhel3 1' postgresql-8.2.6-1PGDG.f8.src.rpm -bash: rpmbuild: command not found Do you know where can I download rpmbuild for RHEL 3? Thank you for your help, Tri -Original Message- From: Devrim GÜNDÜZ [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2008 2:40 PM To: Tri Quach Cc: PostgreSQL - General ML Subject: RE: [GENERAL] Relocation error:/usr/lib/libpq.so.5:undefinedsymbol: krb5_cc_get_principal Hi, On Tue, 2008-03-11 at 14:27 -1000, Tri Quach wrote: > It is RHEL 3. The packages you are installing are for RHEL 4, which won't install on RHEL 3 correctly. Unfortunately, we don't have RHEL 3 - x86_64 packages. Please download: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.2.6 %2Flinux%2Fsrpms%2Fredhat%2Frhel-3.0-x86_64%2Fpostgresql-8.2.6-1PGDG.f8.src. rpm and rebuild it on your server with: rpmbuild --rebuild --define 'buildrhel3 1' postgresql-8.2.6-1PGDG.f8.src.rpm You can find more information about this process at: http://pgfoundry.org/docman/view.php/148/1338/PostgreSQL-RPM-Installatio n-PGDG.pdf -HTH. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.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] Trigger to run @ connection time?
On Mar 11, 2008, at 10:28 PM, Tom Lane wrote: An ON CONNECT trigger enforced by the database seems a bit scary to me. If it's broken, how you gonna get into the DB to fix it? regards, tom lane If creating the trigger wouldn't be possible from within the database that it's defined for (which would be strange anyway as far as I'm concerned, since you are already connected at that point and thus missed an opportunity to fire that trigger) this shouldn't be a problem. To put that into an SQL statement, something like: #template1 $ CREATE TRIGGER my_database_connect_trg BEFORE CONNECT ON my_database EXECUTE PROCEDURE my_database_setup() Although of course that begs the question where that procedure would be stored; Rather not in template1, I suppose! This points to another problem with ON CONNECT triggers, you'll likely need to be connected to reach the stored procedure that the trigger calls! A nice chicken and egg problem, with some scope issues on the horizon... Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47d85f64233091819183316! -- 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] Trigger to run @ connection time?
Alban Hertroys wrote: On Mar 11, 2008, at 10:28 PM, Tom Lane wrote: An ON CONNECT trigger enforced by the database seems a bit scary to me. If it's broken, how you gonna get into the DB to fix it? regards, tom lane If creating the trigger wouldn't be possible from within the database that it's defined for (which would be strange anyway as far as I'm concerned, since you are already connected at that point and thus missed an opportunity to fire that trigger) this shouldn't be a problem. To put that into an SQL statement, something like: #template1 $ CREATE TRIGGER my_database_connect_trg BEFORE CONNECT ON my_database EXECUTE PROCEDURE my_database_setup() Although of course that begs the question where that procedure would be stored; Rather not in template1, I suppose! This points to another problem with ON CONNECT triggers, you'll likely need to be connected to reach the stored procedure that the trigger calls! A nice chicken and egg problem, with some scope issues on the horizon... I envision this not so much as a BEFORE connect trigger, but rather as an event that happens after the point of the user being successfully authenticated, but before executing any user application commands -- in fact before even starting to listen for any incoming application commands. A particular implementation I see this useful for, to give some context to thinking about this, is to update a user password expiration date (to, say, CURRENT_DATE + 30) at each login. This would then allow the creation of a system that lets unused accounts expire but automatically maintains the validity of actively used accounts, for example. I can think of other uses, too. I currently achieve this functionality with an event triggered in an end-user application, but I'd really like it to happen in the data base so that every application that access this data base doesn't have to recreate that particular functionality -- and also so as to eliminate to problem of the functionality not being implemented by other applications outside our control that access the data base. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Column Statistics - How to dertermine for whole database
Hi, I finally figure out how come (i think) my analyszing of some specific tables is taking so freaking long. 12million rows, ~11GB table. I had some of the columns with the stat level set up to 1000. (this was previously because I was trying to optimise somethings to make things faster. ) When the table was small, the analyse of that table went by pretty fast, until it became bigger, now it's a headache. So, my investigation found that It was due to the stats level I put into that column. (normal stat level = 100). Is there a query to pg_catalog tables to find out which table/column has the stat level not at default in 1 sweep? Appreciate any pointers. -- 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] porting vb6 code to pgplsql, referencing fields
josep porres wrote: but the most important is how can I reference the fields inside de loop By "the fields" I assume you mean the fields with names that end in a number from 1 to 5, and you want to access those fields in a loop as if you were indexing an array? I think you might want to explain what you're actually trying to do, as the right answer might not really be how to load/store your array but might involve looking at how and why you're using arrays this way too. In particular, maybe it's better to store an array in the record. Looking at your VB6 code it appears that your f2_tarifa_a table has some sequentially numbered fields, and might be defined like (assuming a SERIAL pkey): CREATE TABLE f2_tarifa_a ( id SERIAL PRIMARY KEY, -- other values M3TRAM1 INTEGER, PREU1 NUMERIC(10,2) M3TRAM2 INTEGER, PREU2 NUMERIC(10,2) M3TRAM3 INTEGER, PREU3 NUMERIC(10,2) M3TRAM4 INTEGER, PREU4 NUMERIC(10,2) M3TRAM5 INTEGER, PREU5 NUMERIC(10,2) ); ... and you're essentially using it to store 5-element arrays. You have a few options here. The simplest is probably just to explicitly fetch each element of the array, eg: Ma[1] := row_tfa.M3TRAM1; Mpa[1] := row_tfa.PREU1; Ma[2] := row_tfa.M3TRAM2; Mpa[2] := row_tfa.PREU2; etc. Alternately you could adjust your schema to store arrays: CREATE TABLE f2_tarifa_a ( id SERIAL PRIMARY KEY, -- other values M3TRAM INTEGER[5], PREU NUMERIC(10,2)[5] ); ... and fetch/store those directly. Another option is to switch from using an array to a secondary table. If your arrays are in any way variable in length that's probably a good ideea. For example: CREATE TABLE f2_tarifa_a ( id SERIAL PRIMARY KEY, -- other values ); CREATE TABLE f2_tarifa_a_trampreu ( f2_tarifa_a_id INTEGER REFERENCES f2_tarifa_a(id) ON DELETE CASCADE, M3TRAM INTEGER, PREU NUMERIC(10,2) ); CREATE INDEX f2_tarifa_a_trampreu_fkey_id ON f2_tarifa_a_trampreu(f2_tarifa_a_id); ... then you can FOR loop though the secondary table. -- 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] Column Statistics - How to dertermine for whole database
> Is there a query to pg_catalog tables to find out which table/column > has > the stat level not at default in 1 sweep? Try this: select c.relname, a.attname, attstattarget from pg_catalog.pg_attribute a, pg_catalog.pg_class c, pg_catalog.pg_namespace n where a.attrelid = c.oid and c.relnamespace=n.oid and n.nspname = 'public' and a.attnum > 0 The value "-1" means to use the default (set in postgreql.conf) -- 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] postgre vs MySQL
On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <[EMAIL PROTECTED]> wrote: > - Auto_increment columns as pkeys in InnoDB tables are practically > required, yet severely limited scalability due to how a transaction > would lock the structure to get the next auto-increment (significantly > improved in 5.1) Pretty sure they implemented the fix for that in an early 5.0 release. I remember chatting with Heikki Turri about it. > There are other complaints you'd expect people to have, but don't seem > to get talked about much, because people are so used to (from my point > of view) working around them. For example, statistics on an InnoDB table > are calculated when the table is first accessed, but not stored > anywhere, so there are extra costs on database startup. The backup issue > with InnoDB has already been covered. Tablespace management in InnoDB > seems exceptionally primitive, and is helped somewhat by the > tablespace-per-table option. There are many more, again imho. I remember seeing something about some problems that using the tablespace per table option on some mysql site... goes to look... paraphrased from the Mysql Performance Blod... Using the innodb_file_per_table=1 setting really tends to work against you, as you tend to get lots of bloated tables over time. If all your innodb tables are in the same file, then when one frees space, another can use it. with files per table, you can't recover space this way. My real complaint with InnoDB is it's a red headed step child. If mysql supported only innodb, it would be a very different database, and probably a bit simpler as well. no need to worry about how you state fk-pk relationships (currently column level references are silently dropped for innodb OR myisam). If there was a run time switch that said "use only innodb and use syntax that's sane" I'd probably be willing to test 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] Column Statistics - How to dertermine for whole database
On Wed, 2008-03-12 at 21:33 -0500, Adam Rich wrote: > select c.relname, a.attname, attstattarget > from pg_catalog.pg_attribute a, pg_catalog.pg_class c, > pg_catalog.pg_namespace n > where a.attrelid = c.oid and c.relnamespace=n.oid > and n.nspname = 'public' and a.attnum > 0 Funny, that does not work. note : I did change the nspace value to reflect the DB I'm using/querying I've even changed the stat level to 200 (default is 100 or -1) -- 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] postgre vs MySQL
Scott Marlowe wrote: On Wed, Mar 12, 2008 at 1:02 PM, paul rivers <[EMAIL PROTECTED]> wrote: - Auto_increment columns as pkeys in InnoDB tables are practically required, yet severely limited scalability due to how a transaction would lock the structure to get the next auto-increment (significantly improved in 5.1) Pretty sure they implemented the fix for that in an early 5.0 release. I remember chatting with Heikki Turri about it. Definitely not fixed until 5.1, in fact not until very recently (5.1.22) : http://bugs.mysql.com/bug.php?id=16979 Anyway, enough of that for me. It's a Postgres list, and my list of MySQL complaints is far longer than my enthusiasm for documenting them. Paul -- 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] Can't rename an existnig DB because it doesn't exist???
You might want to keep this one on the list. I'm out of my league with this one I think On Wed, Mar 12, 2008 at 11:24 AM, Gauthier, Dave <[EMAIL PROTECTED]> wrote: > stdb2=# select '|'||datname||'|' from pg_database ; > ?column? > > |postgres| > |template1| > |template0| > |cells_dev| > |stdb2| > |stdb| > |stdb_standby| > |cells| > (8 rows) > > > > > > -Original Message- > From: Scott Marlowe [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 12, 2008 1:22 PM > To: Gauthier, Dave > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Can't rename an existnig DB because it doesn't > exist??? > > On Wed, Mar 12, 2008 at 10:03 AM, Gauthier, Dave > <[EMAIL PROTECTED]> wrote: > > > > > > V8.2.0 on Linux > > Look into updating, there were some serious bugs fixed between 8.2.0 > and 8.2.6 It's a pretty simple thing, since you don't need to dump / > reload for it. > > > > > Can't rename a db, complains that it doesn't exist. Yet psql -l shows > that > > it does and I can connect to it ??? > > > > > > > > mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to > stdb_tmp" > > > > ERROR: database "stdb" does not exist > > > > mmdcc228_SETUP(121)% psql -l > > > > List of databases > > > > Name | Owner | Encoding > > > > --+--+-- > > stdb | dfgauthi | UTF8 > > > > stdb2| dfgauthi | UTF8 > > > > stdb_standby | dfgauthi | UTF8 > > Very strange. maybe the name has a space in it? > > Try running this query: > select '|'||datname||'|' from pg_database ; > and see if you have a space or something in there. can you run the > alter database rename from the command line and just not from psql? > -- 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] FROM + JOIN when more than one table in FROM
On Wed, Mar 12, 2008 at 4:48 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > one of the inner join doesn't have an on relationship. > > As you could see in the other (longer) query I'm just trying to put in > the same row what would be Could you get what you want by cross joining the first two tables? -- 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] Column Statistics - How to dertermine for whole database
On Wed, Mar 12, 2008 at 8:45 PM, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > On Wed, 2008-03-12 at 21:33 -0500, Adam Rich wrote: > > select c.relname, a.attname, attstattarget > > from pg_catalog.pg_attribute a, pg_catalog.pg_class c, > > pg_catalog.pg_namespace n > > where a.attrelid = c.oid and c.relnamespace=n.oid > > and n.nspname = 'public' and a.attnum > 0 > > Funny, that does not work. > > note : I did change the nspace value to reflect the DB I'm > using/querying > > I've even changed the stat level to 200 (default is 100 or -1) The nspname setting setting is for schema, not db name. -- 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] Can't rename an existnig DB because it doesn't exist???
On Wed, 12 Mar 2008 21:30:57 -0700 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > You might want to keep this one on the list. I'm out of my league > with this one I think Can you pg_dump the database? What about vacuum? What does: SELECT * FROM pg_database; Return? J > > On Wed, Mar 12, 2008 at 11:24 AM, Gauthier, Dave > <[EMAIL PROTECTED]> wrote: > > stdb2=# select '|'||datname||'|' from pg_database ; > > ?column? > > > > |postgres| > > |template1| > > |template0| > > |cells_dev| > > |stdb2| > > |stdb| > > |stdb_standby| > > |cells| > > (8 rows) > > > > > > > > > > > > -Original Message- > > From: Scott Marlowe [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, March 12, 2008 1:22 PM > > To: Gauthier, Dave > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Can't rename an existnig DB because it > > doesn't exist??? > > > > On Wed, Mar 12, 2008 at 10:03 AM, Gauthier, Dave > > <[EMAIL PROTECTED]> wrote: > > > > > > > > > V8.2.0 on Linux > > > > Look into updating, there were some serious bugs fixed between > > 8.2.0 and 8.2.6 It's a pretty simple thing, since you don't need > > to dump / reload for it. > > > > > > > > Can't rename a db, complains that it doesn't exist. Yet psql -l > > > shows > > that > > > it does and I can connect to it ??? > > > > > > > > > > > > mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to > > stdb_tmp" > > > > > > ERROR: database "stdb" does not exist > > > > > > mmdcc228_SETUP(121)% psql -l > > > > > > List of databases > > > > > > Name | Owner | Encoding > > > > > > --+--+-- > > > stdb | dfgauthi | UTF8 > > > > > > stdb2| dfgauthi | UTF8 > > > > > > stdb_standby | dfgauthi | UTF8 > > > > Very strange. maybe the name has a space in it? > > > > Try running this query: > > select '|'||datname||'|' from pg_database ; > > and see if you have a space or something in there. can you run the > > alter database rename from the command line and just not from psql? > > > -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit signature.asc Description: PGP signature
[GENERAL] Duplicate key violation on UPDATE
Hi all, I have recently turned up the postgresql logging facilities on a rather database intensive application in the hope of finding bugs and tuning queries. We're using 8.0.8, though thinking of moving to 8.3 as the new HOT functionality looks like it would be useful for us given the high UPDATE frequency in some of our tables. Anyhow, the problem... The table is question is defined as: CREATE SEQUENCE NimrodGridRun_id ; create table NimrodGridRun( agent_idINTEGER DEFAULT nextval('NimrodGridRun_id') primary key, service_id INTEGER not null references NimrodGridService, compute_id INTEGER not null references NimrodComputeResource, executable varchar(255) not null, arguments TEXT not null, exe_typechar(1) not null check (exe_type in ('A', 'P', 'R', 'S')), control varchar(8) not null default 'start' check (control in ('nostart', 'start', 'stop')), status varchar(8) not null default 'pending' check (status in ('pending', 'active', 'queued', 'failed', 'done', 'stopping', 'apending')), actuator_id INTEGER references NimrodGridActuator on delete set null, job_ident varchar(255), error_info TEXT not null default '', more_info TEXT not null default '', active_time timestamp, lastcheck timestamp, tag INTEGER not null default 0 references NimrodJobAgentTag, run_oncechar(1) not null default 'F' check (run_once in ('F', 'T')), tmpcleaned char(1) not null default 'F' check (tmpcleaned in ('F', 'T')), hostvarchar(255), workdir varchar(255), durationreal not null, idletimereal not null, finishbyvarchar(8) check (finishby in ('agent', 'asched', 't5misc', 'actuator', 'dbserver', 'fserver')), finish_reason varchar(255), create_time timestamp not null default CURRENT_TIMESTAMP, start_time timestamp, submit_time timestamp, finish_time timestamp, lastheartbeat timestamp, checkcount INTEGER not null default 0 ) WITH OIDS; I came across this error in the logs and can't understand what might be causing it. PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev - LOG: statement: BEGIN PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev - LOG: statement: UPDATE NimrodGridRun SET control = 'stop' WHERE status = 'done' AND tag = '9' AND compute_id = 2 AND finish_time < CURRENT_TIMESTAMP - INTERVAL '1 minute' PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev - ERROR: duplicate key viola tes unique constraint "nimrodgridrun_pkey" PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev - STATEMENT: UPDATE NimrodGridRun SET control = 'stop' WHERE status = 'done' AND tag = '9' AND compute_id = 2 AND finish_time < CURRENT_TIMESTAMP - INTERVAL '1 minute' PID:11444 TS:2008-03-13 14:21:24 EST TID:220581763 U:blair D:blair-dev - LOG: statement: ROLLBACK Why would we be getting a duplicate key violation on the primary key of this table when we aren't doing anything in the UPDATE (that I can tell) to change it? Cheers, -Blair -- In science one tries to tell people, in such a way as to be understood by everyone, something that no one ever knew before. But in poetry, it's the exact opposite. - Paul Dirac -- 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] Duplicate key violation on UPDATE
"Blair Bethwaite" <[EMAIL PROTECTED]> writes: > Why would we be getting a duplicate key violation on the primary key > of this table when we aren't doing anything in the UPDATE (that I can > tell) to change it? Corrupted index, perhaps? Can you REINDEX that table? There are at least two known bugs in 8.0.8 that could lead to this type of index corruption. If you can't immediately upgrade to a later major release, I'd strongly recommend updating to a more current 8.0.x. 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] Column Statistics - How to dertermine for whole database
On Wed, 2008-03-12 at 21:40 -0700, Scott Marlowe wrote: > On Wed, Mar 12, 2008 at 8:45 PM, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > > On Wed, 2008-03-12 at 21:33 -0500, Adam Rich wrote: > > > select c.relname, a.attname, attstattarget > > > from pg_catalog.pg_attribute a, pg_catalog.pg_class c, > > > pg_catalog.pg_namespace n > > > where a.attrelid = c.oid and c.relnamespace=n.oid > > > and n.nspname = 'public' and a.attnum > 0 > > > > Funny, that does not work. > > > > note : I did change the nspace value to reflect the DB I'm > > using/querying > > > > I've even changed the stat level to 200 (default is 100 or -1) > > The nspname setting setting is for schema, not db name. select c.relname, a.attname, attstattarget from pg_catalog.pg_attribute a, pg_catalog.pg_class c, pg_catalog.pg_namespace n where a.attrelid = c.oid and c.relnamespace=n.oid and n.nspname = 'xmms' and a.attnum > 0 and attstattarget <> -1; This works now. The clarification on it being the schema name was useful. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Segementation fault in PQgetvalue()
This is what I'm trying to: char lmt_str[100]; sprintf(lmt_str,"%s",PQgetvalue(res, 0, nFields-1)); //the last field is of type timestamp This is giving segmentation fault. I'm confused -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general