Re: [SQL] Obtaining client IP address
Michael Fuhr wrote: On Sat, Feb 25, 2006 at 12:16:31AM +0100, Alexis Paul Bertolini wrote: The client has no idea what public IP it has. The server ought to know... however I haven't found how I could get this info into my query. What version of PostgreSQL are you running? In 8.0 and later you can use inet_client_addr(). http://www.postgresql.org/docs/8.1/interactive/functions-info.html I forget if earlier versions have a way to get the client's IP address without resorting to hacks (e.g., writing a function in a privileged language like plperlu and calling netstat or lsof). Mmmm, I didn't spot that one, but it did the trick! Thanks! Alex. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Installing dbLink
Hi. Thanks for the assistance. I have another question. Is it possible to install this function on Window XP?"A. Kretschmer" [EMAIL PROTECTED] wrote: am 24.02.2006, um 11:48:44 + mailte Richard C folgendes: Hi When I execute the query: SELECT * FROM dblink('dbname=Bas','SELECT id FROM person') AS t(a int) WHERE t.a 9000 AND T.a 9050; I receive the error message ERROR: function dblink("unknown", "unknown") does not exist How do I install or setup the function dbLink so that I can use this feature.You should read the docu.Which distribution do you have? You should install the contrib-packageand then you should run the dblink.sql.psql your_database dblink.sqlAfter this, y
ou can
use dblink.HTH, Andreas-- Andreas Kretschmer (Kontakt: siehe Header)Heynitz: 035242/47215, D1: 0160/7141639GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe === ---(end of broadcast)---TIP 4: Have you searched our list archives?http://archives.postgresql.org
Yahoo! Photos NEW, now offering a quality print service from just 8p a photo.
[SQL] Index to support LIKE '%suffix' queries
Is it possible to create an index to support queries of the form column LIKE '%suffix' (similar to an ordinary index for LIKE 'prefix%', which I also need)? I could define a function which reverts strings (or revert them in the application) and use a normal B-tree index, but I wonder if there is a better way. ---(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
Re: [SQL] After Trigger assignment to NEW
On Sat, 25 Feb 2006, Achilleus Mantzios wrote: O Owen Jacobson ?? Feb 24, 2006 : Achilleus Mantzios wrote: O Tom Lane ?? Feb 24, 2006 : By definition, an AFTER trigger is too late to change what was stored. Use a BEFORE trigger. Too late if someone wants to store it. I wanna store the intented original values, thats why i use AFTER trigger. But i would like to alter what a final AFTER trigger would see. I'll elabarote a little. An update happens. The row is stored. An after trigger is fired that alters some NEW columns (nullifies them), aiming for a subsequent trigger to see the altered results . It should be something like a pointer to a HeapTuple, (right?), so that would be feasible i suppose. I would not even make a post if it was something that trivial. I hope you get my point. Your real problem is that the subsequent trigger has behaviour you don't like. That's what you should be fixing. If dbmirror has no way to exclude specific tables from mirroring, take it up with them as a feature request, or patch dbmirror to work how you want it to. AFTER triggers *must* receive the row that was actually inserted/updated/deleted. If they could receive a modified row that didn't reflect what was actually in the database, all sorts of useful trigger-based logging and replication patterns wouldn't work, and there's really no other way to implement them. See also Tom Lane's other message for further implications of being able to modify the rows seen by AFTER triggers. As i have explained my dbmirror is FK null values gnostic(=aware) already as we speak. [...] So nullifying a value just before the dbmirror trigger would do exactly the right thing (for me) Yes it does what you want for this very specific case. But, would it do what you want if someone put a trigger before it that changed the values to some non-NULL thing? That seems likely to break your mirroring. I'd also be hesitant to write triggers that have to execute in a specific order. Meaning that would hurt portability? Most people need features rathen than the relief to know they can migrate to another database (which they probably never will) In this case, you're giving up the feature that users can write constraints, logging or mirroring after triggers that are guaranteed to get the data that was actually inserted in order to get the feature that a trigger can affect the data to the next trigger. This seems like a general loss in functionality for a larger fraction of users than those who gain. Back to AFTER trigger changing values issue, i think things are not so dramatic if FK triggers could just be fired first. Actually, I think we technically fire the checks too early as it is, so I don't see enshrining that or making it earlier is a good idea. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Relation 0 does not exist
Yasuhiro Furuse wrote: When I run SQL command, I've got the error messe 'Relation 0 doesn't exist' and following log messages were generated. After this error happens, I can not access database at all. Could you kindly advise why this kind of error happens? Regards, y.furuse --- [Version] PosrgreSQL7.2.4 Hmm. Who knows which one of the data-corrupting unfixed known bugs in this ancient version caused this particular problem. I doubt you will find much people trying to figure it out; instead, you should be looking into doing an upgrade rather soonish. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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
Re: [SQL] Index to support LIKE '%suffix' queries
Florian Weimer [EMAIL PROTECTED] writes: Is it possible to create an index to support queries of the form column LIKE '%suffix' (similar to an ordinary index for LIKE 'prefix%', which I also need)? Sounds like what you *really* need is full-text search, not half measures ... have you looked at tsearch2? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Index to support LIKE '%suffix' queries
* Tom Lane: Florian Weimer [EMAIL PROTECTED] writes: Is it possible to create an index to support queries of the form column LIKE '%suffix' (similar to an ordinary index for LIKE 'prefix%', which I also need)? Sounds like what you *really* need is full-text search, not half measures ... have you looked at tsearch2? Uh-oh, the table in question has got 50+ million rows (and is still growing). Each document contains about three words. Do you think tsearch2 could deal with that? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Index to support LIKE '%suffix' queries
Florian Weimer [EMAIL PROTECTED] writes: Is it possible to create an index to support queries of the form column LIKE '%suffix' (similar to an ordinary index for LIKE 'prefix%', which I also need)? It is possible to create a functional index on the reverse of the string. You need to also reverse the patter at query-time as well. This will make the % be at the end of the pattern, making it an indexable condition. Whether or not this beats tsearch2 is something you should investigate ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
