Re: [GENERAL] Tracking SQLs that update data
It's far easier to adjust the logging. psql mydb alter mydb set log_statement='mod'; Thanks Scott. Two questions: 1. Will this log_statement='mod' be a permanent thing, or when I have done my testing and identifying the problem SQL, I can set it back to default so not too much logging is being done? 2. After this statement, how or where do I find the culprit SQL? Thanks -- 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] [Blatant Alias Abuse] Anyone want to help out with a coding problem?
I am not anti-DotNet.. i wanted to make sure everyone understands the pitfalls one encounters when writing code for a OS and .Net-version specific framework specifically: 1)change OS - code doesnt work 2)change supported .NET framework version - code doesnt work if you're writing missile-guidance systems that only work for one specific suite of OS -compiler-environment thats perfectly ok..in fact you specifically dont want portable code it has been my experience that non-portable code is generally 1)easy to code upfront..usually a day or two 2)a nightmare to maintain..as you'll have to rewrite what you have for each OS and .NET-framework permutation you encounter so to lure customers in go for the quick fix onetime solution. then when they needs conversions ..quote them big $ conversion the latest Mono implementation i have is horribly gacked..do you have connections at novell that have a working copy? Takk! Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Thu, 17 Jun 2010 06:14:24 -0700 Subject: Re: [GENERAL] [Blatant Alias Abuse] Anyone want to help out with a coding problem? From: m...@kitchenpc.com To: mgai...@hotmail.com CC: pgsql-general@postgresql.org Regarding the RAM thing, all the recipes will be in the database but when the engine loads, it will create a graph in memory of all the recipes. They'll actually be linked by similarity, recipe A will have an array of pointers to other recipes that someone who likes recipe A might also like. Though this design may be rethought if it doesn't work out. The data in RAM is read-only and will be updated with fresh DB data once every day or so. The reason I need to keep the data in RAM is I need to be able to very quickly walk this graph and a relational DB doesn't really fit for this. I've done some testing and if I store about 100,000 recipes in memory, it's only a few hundred megs of RAM. This is nothing for a web server, and even if I need more, this facility could be moved over to a dedicated server that the main website just queues up requests on. Regarding Mono, I'm not sure it's a wreck. It's come a long way and several sites use it in production. However, I'm keeping my code runnable on both .NET/Windows and Mono/Apache because I haven't yet reached any definitive conclusions on what it will be run on. My code is 100% managed, there are no native DLLs as you mention. I've actually ported my site over to Mono as a proof of concept and it took one evening, not 6 mon. I don't really think your rant on MS/.NET is really warranted here. To each is own, if you're a Java guy then that's fantastic. I'm not much into it and prefer C#, especially since I have 10 years of experience with it and zero with Java. I'm well aware of the requirements of .NET, Mono, Apache, etc. Thanks for your feedback. Mike On Thu, Jun 17, 2010 at 5:57 AM, Martin Gainty mgai...@hotmail.com wrote: keeping anything in RAM means your data will be lost forever when the session is lost..(then again if the data is garbage to begin with then throwing away garbage is no problem) if your data is NOT garbage then you're going to want to persist the data ..look at oracle/mysql and yes even SQL-server if you have to use ODBC instead of JDBC Mono is a wreck..i've written to the mormon is at novell multiple times to try to get a build that works and yet to hear any semicompetent/coherent response from any of them be prepared to dedicate yourself to a minimum of 6 months of effort.. just to get mono working C#
[GENERAL] Inserting additional data into pg_statistics
Hello everyone, I'm new here, but I have a problem and couldn't find a solution so I hope you will help me. I'm trying to reproduce the pg_statistics catalog from one database server to another one. To retrieve the data from the first server was a trivial job. The trouble was when I tryied to insert new record to pg_statistic table on the second server. To be more precise, I want to run this [0] query and I'm struggling with this error [1]. The query is generated by pg_dump. Anyways, it will be the same problem if I write the SQL statement by hand. So, has anyone any ideas how can I insert new record to pg_statistics system catalog ? [0] INSERT INTO pg_statistic VALUES ( 54655,1,0,26,-1,2,3,0,0,1058,1058,0,0,null,'{1}',null,null,'{AFRICA ,AMERICA}',null,null,null ); [1] SQL error: ERROR: cannot accept a value of type anyarray Thank you a lot. Looking forward for your replies. -Tedy -- 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] Access remote DB (outside local network) via PgAdmin
Here [- http://old.nabble.com/-GENERAL--Access-to-databas-from-the-Internet-td6388294.html#a6388294 http://old.nabble.com/-GENERAL--Access-to-databas-from-the-Internet-td6388294.html#a6388294 ] there is a very similar thread, I feel sorry for the redundancy. Anyway I would ask to you some more thing about this topic: I've been reading here [ http://library.linode.com/databases/postgresql/pgadmin-windows http://library.linode.com/databases/postgresql/pgadmin-windows ] that you can access remotely creating a VPN channel. What are the real advantages of creating a VPN, if you have already set your server like this for example: hostall all zzz.zzz.zzz.zzz/32 md5 I mean, don't you already have an md5 encrypted channel between the server and zzz.zzz.zzz.zzz ? Thank you for reading. Piero Piero Campa wrote: Hi to everyone, well until now I could easiily access a remote hosted database in my local network via PgAdmin: just set the IP and port and login info and I was done. But whether I want to access that database from outside my network, even without encrypted tunnels by now, what do I have to do? In my thoughts I should configure my router in order to bind for example Server_IP (192.168.0.2):5432 -- Router_External_IP:some_port. So when I add a server in PgAdmin and I'm outside the network I set Router_External_IP as IP and some_port as listening port. Is it possible? Or, maube better, which is the correct way of working like this? Thank you in advance. Piero -- View this message in context: http://old.nabble.com/Access-remote-DB-%28outside-local-network%29-via-PgAdmin-tp28933725p28933763.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Access remote DB (outside local network) via PgAdmin
Hi to everyone, well until now I could easiily access a remote hosted database in my local network via PgAdmin: just set the IP and port and login info and I was done. But whether I want to access that database from outside my network, even without encrypted tunnels by now, what do I have to do? In my thoughts I should configure my router in order to bind for example Server_IP (192.168.0.2):5432 -- Router_External_IP:some_port. So when I add a server in PgAdmin and I'm outside the network I set Router_External_IP as IP and some_port as listening port. Is it possible? Or, maube better, which is the correct way of working like this? Thank you in advance. Piero -- View this message in context: http://old.nabble.com/Access-remote-DB-%28outside-local-network%29-via-PgAdmin-tp28933725p28933725.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inserting additional data into pg_statistics
Teodor Macicas teodor.maci...@epfl.ch writes: I'm trying to reproduce the pg_statistics catalog from one database server to another one. You can't really, and manually fooling with system catalogs is usually a bad idea anyway. Just run ANALYZE. 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] Inserting additional data into pg_statistics
Thank you for your reply, Tom. Why I can't ? And for my purpose is not a bad idea. I mean, I have to do this and somehow I should find a solution. In order to use ANALYZE I need the same data on 2nd machine, but the data is quite large and the only information I need are the statistics from pg_statistic. So, why I receive that ERROR and I run the INSERT statement ? Can anyone tell me where to look into Postgresql source to see how the engine updates this table ? Looking forward for reply. With kindest regards, Tedy Tom Lane wrote: Teodor Macicas teodor.maci...@epfl.ch writes: I'm trying to reproduce the pg_statistics catalog from one database server to another one. You can't really, and manually fooling with system catalogs is usually a bad idea anyway. Just run ANALYZE. 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] Inserting additional data into pg_statistics
Teodor Macicas teodor.maci...@epfl.ch writes: Why I can't ? And for my purpose is not a bad idea. I mean, I have to do this and somehow I should find a solution. In order to use ANALYZE I need the same data on 2nd machine, but the data is quite large and the only information I need are the statistics from pg_statistic. Er, if you haven't got the data on the second machine, then you *don't* need or want that stuff in its pg_statistic. It won't do you any good to have incorrect information in there. 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] Inserting additional data into pg_statistics
Hi Tom, Thank you again for your quick reply. Shall we pass the reason for I want to do this ? I can explain, but it's a long story. The idea is that *for sure* I need those metadata there. I want to use them on the second machine. Regards, Tedy Tom Lane wrote: Teodor Macicas teodor.maci...@epfl.ch writes: Why I can't ? And for my purpose is not a bad idea. I mean, I have to do this and somehow I should find a solution. In order to use ANALYZE I need the same data on 2nd machine, but the data is quite large and the only information I need are the statistics from pg_statistic. Er, if you haven't got the data on the second machine, then you *don't* need or want that stuff in its pg_statistic. It won't do you any good to have incorrect information in there. 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] Tracking SQLs that update data
On Sat, Jun 19, 2010 at 5:28 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: It's far easier to adjust the logging. psql mydb alter mydb set log_statement='mod'; Thanks Scott. Two questions: 1. Will this log_statement='mod' be a permanent thing, or when I have done my testing and identifying the problem SQL, I can set it back to default so not too much logging is being done? Just alter it back to 'none' when you're done. Note you can also set it per user, so if it's a certain user you want to log you can only log their statements. 2. After this statement, how or where do I find the culprit SQL? They should be in the postgresql logs. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A thought about other open source projects
I happened across (yet) another open source project which supports MySql and Derby (its a Java app) and is thinking about supporting Oracle (they have actually bought a licence) but does not support Postgresql. This particular project is onehippo.org, but there are many others. Another perhaps more important project is the Akonadi project in KDE, which is only gradually getting around to Postgresql. These projects need help to realise that adding Postgresql is not a big job, especially for those using JDBC which can already connect to all DBs. It strikes me that if the project could write a few pages gleaned from other porting operations, then whenever a project like this is found they can be pointed to these pages and shown how easy it is to do. Then if someone spots a project that might use Postgresql they can simply point them at the pages. 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] A thought about other open source projects
On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote: These projects need help to realise that adding Postgresql is not a big job, especially for those using JDBC which can already connect to all DBs. It strikes me that if the project could write a few pages gleaned from other porting operations, then whenever a project like this is found they can be pointed to these pages and shown how easy it is to do. http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL -- 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] Working with pages of data (LIMIT/OFFSET keyword)
I am using a PHP client. Is there a way to do the above task with php? On Thu, Jun 17, 2010 at 11:38 PM, Francisco Figueiredo Jr. franci...@npgsql.org wrote: I would go with 2). Npgsql supports multiple resultsets. You can pass a query separated by semicolon ';' or you can use a procedure call which return a setof refcursor. On both ways, you will need to call NextResult in your Datareader just like with SQLServer. You can check our user manual: http://manual.npgsql.org to get more info about how to use refcursors I hope it helps. Please, let me know if you need more info. On Wed, Jun 16, 2010 at 16:44, Szymon Guz mabew...@gmail.com wrote: 2010/6/16 Mike Christensen m...@kitchenpc.com I'm generating a query on the fly to return a set of data, however I only want to display 30 rows at a time to the user. For this reason, I use the LIMIT 30 OFFSET x clause on the select statement. However, I also want to know the total rows that match this query had there been no limit, that way I can display to the user the total count and the number of pages, and have Next/Prev buttons in my UI. I can think of the following ways to do this: 1) When the page loads, execute two totally separate queries. One that does the COUNT, and then another query immediately after to get the desired page of data. I don't like this as much because the two queries will execute in separate transactions and it'd be nice if I could just perform a single SQL query and get all this information at once. However, I will use this if there's no other way. Just run them in one transaction. You can also just show the Next/Prev buttons and then do something just for the case where there is no data. Or use LIMIT 31 so you always know that there is the next page with at least one record. regards Szymon Guz -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general