Re: [GENERAL] Problem with starting PostgreSQL server 7.4.19
Craig Ringer [EMAIL PROTECTED] writes: Kakoli Sen wrote: It was running fine initially and the database was lying idle for a few days. Today I looged into the machine and restarted the server by killing the process by 'kill -9 pid'. And then restarted it by 'postmaster -i -D /opt/pgsql/data/'. Why did you use `kill -9' ? Certainly not good practice, but theoretically PG should be proof against even such deliberate abuse as that. What seemed odd to me was LOG: database system was interrupted at 2008-03-06 14:15:17 IST LOG: record with incorrect prev-link 1/0 at 0/A4EB08 LOG: invalid primary checkpoint record LOG: record with incorrect prev-link 42FD/0 at 0/A4EAC8 LOG: invalid secondary checkpoint record Experimentation shows that a freshly initialized 7.4 database has WAL locations like this: Latest checkpoint location: 0/9DFCF0 Prior checkpoint location:0/9D92C0 so either you'd only ever thrown a few kilobytes of stuff into the DB or there was something seriously wrong with pg_control to begin with. I'm wondering about mistaken filesystem restores ... 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] Problem with starting PostgreSQL server 7.4.19
Hi, Actually, I tried stopping server by 'kill `cat /opt/pgsql/data/postmaster.pid`. This did not work. So I used kill -9 on Red Hat 4. This is a test database where we are in the process of setting up. So it does not have live data. Still I do agree, it was not a good idea. Now, do I have to re-install PostgreSQL or is there any way out? Server configuration is default. Only change from default is allowing tcp/ip connections. Regards, Kakoli -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Craig Ringer Sent: Wednesday, March 12, 2008 11:09 AM To: Kakoli Sen Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Problem with starting PostgreSQL server 7.4.19 Kakoli Sen wrote: Hello all, It was running fine initially and the database was lying idle for a few days. Today I looged into the machine and restarted the server by killing the process by 'kill -9 pid'. And then restarted it by 'postmaster -i -D /opt/pgsql/data/'. Why did you use `kill -9' ? Was it not responding to `kill -15' ( ie SIGTERM, kill -TERM ) or shutdown using the init script? SIGKILL, ie signal 9, terminates the process without giving it a chance to clean its state up. It gets no chance to write out buffered data, mark data files as clean, or take any other safe shutdown actions. It's a REALLY REALLY BAD IDEA to do this on a database server, though it should still be able to recover if it's configured to operate with fsync enabled etc. Then it gives the following error on stdout : LOG: database system was interrupted at 2008-03-06 14:15:17 IST LOG: record with incorrect prev-link 1/0 at 0/A4EB08 LOG: invalid primary checkpoint record LOG: record with incorrect prev-link 42FD/0 at 0/A4EAC8 LOG: invalid secondary checkpoint record PANIC: could not locate a valid checkpoint record Ouch. It can't handle either of the checkpoints, and so it can't load the database. I don't know what database repair tools exist, but personally at this point I'd be glad my backups are always kept up to date. What is the problem? It was running fine all this time. I suspect that killing it without giving it a chance to do any cleanup operations might not have helped. What's your server configuration? Could you have disabled any safe I/O options to get some more speed out of the database, perhaps? I'm pretty sure 8.x copes with SIGKILL (because of its use of WAL logging, strong fsync requirements, etc) though of course it's still not a good idea. I don't know about 7.x . -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Re: [GENERAL] SELECT overhead in explicit transaction
Blair Bethwaite [EMAIL PROTECTED] writes: I'm wondering whether there would be any extra overhead (CPU, memory, io, etc), above and beyond the implicit ACCESS SHARE, incurred by putting a simple SELECT into a transaction block? Every PG command is executed in a transaction, whether explicit or implicit. So there is not really any difference on the backend side between SELECT blah and BEGIN; SELECT blah; COMMIT. However the latter definitely can have some extra overhead. At minimum the server has got to parse two more statements, even though they don't do very much when executed. What is really likely to hurt, though, is if your client-side code is such that you incur three round trips to the server instead of just one ... 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] Problem with starting PostgreSQL server 7.4.19
Kakoli Sen wrote: Hi, Actually, I tried stopping server by 'kill `cat /opt/pgsql/data/postmaster.pid`. This did not work. So I used kill -9 on Red Hat 4. This is a test database where we are in the process of setting up. So it does not have live data. Still I do agree, it was not a good idea. Now, do I have to re-install PostgreSQL or is there any way out? Well, if it's a test database you should be able to rename or remove the data directory and then re-run initdb, since you don't care about the data in the database. I'm sure you want to find out why this happened, though, so maybe you should keep the damaged database around for a while and see if anybody here has ideas about what could've happened. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with starting PostgreSQL server 7.4.19
Kakoli Sen [EMAIL PROTECTED] writes: Actually, I tried stopping server by 'kill `cat /opt/pgsql/data/postmaster.pid`. This did not work. So I used kill -9 on Red Hat 4. Define did not work ... what happened exactly? I do not know of any prepackaged Postgres distribution for Red Hat that would put the data directory under /opt. Did you build from source? If you used a prepackaged build then I'm thinking that you did not find every place that needed to be changed to move the data directory. I'm also kind of wondering why you are using either PG 7.4 or RH 4 for a new experimental setup. Both of those versions can see their EOL dates coming round the corner. 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] SELECT in explicit transaction
Hi all, I'm wondering whether there would be any extra overhead (CPU, memory, io, etc), above and beyond the implicit ACCESS SHARE, incurred by putting a simple SELECT into a transaction block? 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] 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.paymethodbackorderid) - Index Scan using
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#8217;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#8217;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#8217;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 attnum0)
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 attnum0)
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 attnum0) 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 attnum0)
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(column, /( ){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(column, /( ){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(column) );
plus your sql's will look saner.
instead of:
select * from table where lower(trim(both ' ' from(regexp_replace(column,
E'\\s+', ' ', 'g' = lower(trim(both ' ' from(regexp_replace('some_string',
E'\\s+', ' ', 'g' ;
you will have:
select * from table where cleaned(field) = 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=0.00..4.28 rows=1 width=0)
[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
