Re: [SQL] can someone jelp me on this?
On Tue, 2005-04-26 at 20:18 +, Lord Knight of the Black Rose wrote: > hey guys I have a question that I couldnt maneged to solve for the last 4 > days. Im kinda new to these stuff so dont have fun with me if it was so > easy. Ok now heres the question. > > [snip class assignment] we'd all love to help you to do your assignment. how far have you got? what is your main stumbling-block ? show us what you have done so far, and point out what the problems with it are gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pgtop, display PostgreSQL processes in `top' style
Cosimo Streppone <[EMAIL PROTECTED]> writes: > 1) is it possible to know Pg backend uptime with > SQL queries? Or must I look at postmaster.pid file? > or even something else? I suppose you mean postmaster uptime, not the lifespan of your own connection. We don't track that at the moment, but IIRC there is a patch in the queue to provide a function that returns this for 8.1. For now you could perhaps look at "ps" output to see how long the postmaster process has been running, but that's surely ugly and fraught with pitfalls ... Interesting thought here: should we track the postmaster process uptime, or the time since the last database restart? Not the same at all. You could argue that from a reliability standpoint the latter is the interesting number. > 2) how can I know the ip addresses/hostnames of clients > connecting to server? You can't (again, unless you want to parse "ps" output). But I think someone has submitted a patch to add such columns to the pg_stat_activity view. We need to figure out whether the visibility of these columns needs to be restricted for security, but pending that discussion it'll probably be in 8.1 in some form. > 3) Is there a way to know the number of queries performed > against Pg backend (by each client)? Can I distinguish > between selects / updates / inserts / copy / ... ? Nope, and nope. We could perhaps teach the stats collector to count querystrings it's received from each backend, which'd answer the first one to a reasonable extent. I'm unconvinced that we should expend the overhead to be able to do the second. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger/rule question
* Ramakrishnan Muralidharan <[EMAIL PROTECTED]> wrote: Hi, > Going through you mail, I assume that you are updating the mtime > only after inserting the record. An "normal" update (=done by an application or user) should also update the mtime. But there's an replication subsystem, which writes should go through untouched. > It is always possible to check the mtime filed value of the inserted > record and take action based on it in the trigger. yeah, but how to detect whether the application has explicitly written it ? The only chance I currently have in mind is to use some session dependent data, i.e. username or some persistant storage (could be easily done ie. w/ plphp) for this decision. The sync subsystem has to do some "special" login (ie. separate user or setting the session wide variable) before doing its work. I would be happier to let a rule do this, so there's not an extra function per written row. But all my experiments ran into infinite recoursion trouble. > Is it possible to send me detail about the trigger? The trigger isn't existing yet. I'm currently maintaining the mtime updates within the application, but I wanna get away from that. It probably would be interesting, if a normal application couldn't touch the mtime at all. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger/rule question
* Christoph Haller <[EMAIL PROTECTED]> wrote: Hi, > I assume this still refers to > [SQL] RULE for mtime recording > from last Friday. ehm, yeah. I forgot that I've already asked this stuff ... hmmpf. seems I'm not getting younger ;-) > I gave it another thought and > I am now having something which seems to work. > The trick is interpose a view to avoid the > rule recursion: correct me if I'm wrong: you dont let the application write to the actual storage table, but instead to a view, which a modified write to the actual storage, where also the reads get their data from. okay, that's really an idea worth to think about :) insert should work the same way. but how to implement delete ? (the application should only see one table, so in our case the view). if we user "DO INSTEAD", we wont get anything to delete (AFAIK), so we cannot intercept here. the only chance seems to leave out "INSTEAD" and live with duplicate data. Did I miss anyting ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Array of Arrays of int
Why does
select array(select array[1,2]);
give me an error: "could not find array type for data type integer[]"?
Is there a type-cast missing, or is this generally impossible? I was
expecting something like
{{1,2}}
In my real-world application, the subquery returns more than one pair
of ints, but I think it all boils down to this simpler example. I am
using version 7.4 of PostgreSQL.
Thanks a lot,
Leo
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] pgtop, display PostgreSQL processes in `top' style
* Cosimo Streppone <[EMAIL PROTECTED]> wrote: Hi, > I'm currently working on something like a `mytop' clone, > also written in Perl with DBI + DBD::Pg interface. > > mytop is a command line utility that displays mysql > server status and clients connected modeled after > `top' unix utility. (http://mytop.sourceforge.net). Great thing. I'd like to invest some time in it, but I'd prefer coding it in java for several reasons. Did you set up an mailing list for that project ? (if not, I could offer to host it - its just some keystrokes away for me) cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Division in Postgre
Thank you - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "tuan" <[EMAIL PROTECTED]> Cc: Sent: Saturday, April 30, 2005 10:56 AM Subject: Re: [SQL] Division in Postgre > On Sun, 24 Apr 2005, tuan wrote: > > > In sql server my division select cast(3 as float)/10 is 0.299. > > But in postgres select cast(3 as float8)/10 is 0.3. How to get result like > > sql server? > > I believe you can control what precision is used in printing the float > results with extra_float_digits. I believe setting it to 2 will give a > 0.29... result however it has more 9s than the above. > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Array of Arrays of int
Leo Fink <[EMAIL PROTECTED]> writes: > Why does > select array(select array[1,2]); > give me an error: "could not find array type for data type integer[]"? We don't do arrays of arrays. You seem to be hoping for a 2-D array, which is something fundamentally different (even though some programming languages consider them the same ;-)) regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Array of Arrays of int
Hello Tom. Am 02.05.2005 um 06:11 schrieb Tom Lane: Leo Fink <[EMAIL PROTECTED]> writes: Why does select array(select array[1,2]); give me an error: "could not find array type for data type integer[]"? We don't do arrays of arrays. You seem to be hoping for a 2-D array, which is something fundamentally different (even though some programming languages consider them the same ;-)) Actually, I don't. In my application I can work around the problem by using an array of string representations of these pairs, or by using two arrays of ints. I just thought that was less elegant and I was missing something obvious. Thanks for your quick response. Best, Leo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
