Re: [GENERAL] Array comparison - subset
On 9/3/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Sep 01, 2006 at 11:55:32AM -0400, Christopher Murtagh wrote: I've got a function that returns and array $foo, and an array $bar. Is there an elegant way to test if $bar is a subset of $foo? I've been looking through the docs and haven't found anything. Am I missing something obvious, or am I out of luck? In the specific case of integer arrays you could use contrib/intarray. Cool. This is exactly what I needed. Thanks a bunch! Cheers, Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Array comparison - subset
Greetings folks, I've got a function that returns and array $foo, and an array $bar. Is there an elegant way to test if $bar is a subset of $foo? I've been looking through the docs and haven't found anything. Am I missing something obvious, or am I out of luck? Cheers, Chris ---(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
[GENERAL] UTF-8 and stripping accents
Greetings folks,
I'm trying to write a stored procedure that strips accents from UTF-8
encoded text. I saw a thread on this list discussing something very
similar to this on April 8th, and used it to start. However, I'm
getting odd behaviour.
My stored procedure:
CREATE OR REPLACE FUNCTION strip_accents(text) RETURNS text
AS '
use Unicode::Normalize;
use Encode;
my $string = NFD( decode( utf8 = $_[0] ) );
$string =~ s/\p{Mn}+//ogsm;
return NFC($string);
'
LANGUAGE plperlu;
I'm trying this on two different postgres dbs. One is pg 7.4.6, the
other is 8.1.4 and they both break in different ways.
On the 8.1.4:
test=# select strip_accents('This is Québec, français, noël, à la mode');
-[ RECORD 1 ]-+--
strip_accents | This is Qu�bec, fran�ais, no�l, � la mod
(not sure how this will arrive to the list, but basically all accented
characters are repliaced with a cedile)
and if I try a 'select strip_accents(column) from table;' in a UTF8
encoded database I get:
ERROR: error from Perl function: Cannot decode string with wide
characters at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Encode.pm
line 166.
On the 7.4.6, I simply get the input without any changes for both the
direct input and for a column.
test=# select strip_accents('This is Québec, français, noël, à la mode');
-[ RECORD 1 ]-+--
strip_accents | This is Québec, français, noël, à la mod
Now, on both of these machines, I have the following simple perl script:
[EMAIL PROTECTED] ~]$ cat strip_accents.pl
#!/usr/bin/perl
use Unicode::Normalize;
use Encode;
my $string = NFD( decode( utf8 = $ARGV[0] ) );
$string =~ s/\p{Mn}+//ogsm;
print NFC($string).\n;
When executed, it behaves as expected:
[EMAIL PROTECTED] ~]$ ./strip_accents.pl 'This is Québec, français, noël,
à la mode'
This is Quebec, francais, noel, a la mode
So, I'm obviously doing something dumb/wrong with encodings, but I
can't for the life of me figure it out. I've tried setting client
encodings, verifying database encodings, etc.. all to no avail. Is
there something obvious that I'm missing? Is there a better way to
achieve what I'm trying to do?
Thanks in advance for any insight.
Cheers,
Chris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] SOLVED: Re: UTF-8 and stripping accents
Hey, I solved my own problem! I'm posting here because while I was
looking for solutions, I found tons of folks tackling the same
problem, most didn't find the solution or had to do cumbersome
'translate()'s to get what they wanted.
The difference between my 7.4.6 and 8.1.4 DBs was that 7.4.6 had
UNICODE as it's encoding, whereas the 8.1.4 was UTF8. So, the 7.4.6
needs the decode and the 8.1.4 doesn't.
Also, I had to escape the '\' in the regex.
So, for the record, to strip out all accents from UTF8 encoded text:
CREATE OR REPLACE FUNCTION strip_accents(text) RETURNS text
AS '
use Unicode::Normalize;
use Encode;
my $string = NFD($_[0]);
$string =~ s/\\p{Mn}//ogsm;
return NFC($string);
'
LANGUAGE plperlu;
For the 7.4.6 DB whose encoding was UNICODE, a slight difference:
CREATE OR REPLACE FUNCTION strip_accents(text) RETURNS text
AS '
use Unicode::Normalize;
use Encode;
my $string = NFD(decode( utf8 = $_[0]));
$string =~ s/\\p{Mn}//ogsm;
return NFC($string);
'
LANGUAGE plperlu;
I hope this is of some use to other folks here. Thanks to Mike
Rylander for the initial code.
Cheers,
Chris
---(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: [GENERAL] plpgsql return setof integer?
On Wed, 24 Aug 2005, Alvaro Herrera wrote:
On Wed, Nov 12, 2003 at 11:42:56PM -0500, Christopher Murtagh wrote:
Thanks once again. You've really helped a lot on this. I especially
liked your 'return qq/{/ . (join qq/,/, @_) . qq/}/;' code. If you
were in Montreal, I would owe you a dinner or at least a coffee and a
big thanks. If you ever come this way, please be sure to give me a call.
I didn't really expect to remember this two years later :-), but I
was going over my old mail in order to delete the trash that is usually
left behind. So if you still have that coffee around, there's news
that you'll be soon able to get rid of it: I'll be visiting McGill Uni
or thereabouts, for a Slony-II meeting. Maybe I can relieve you of it
then ;-)
Very cool. Please let me know when you'll be by and I'd be happy to show
you around, and at the very least buy you that coffee. :-)
Cheers,
Chris
--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger that spawns forked process
On Wed, 2005-05-11 at 00:08 +0200, Martijn van Oosterhout wrote:
On Tue, May 10, 2005 at 05:31:56PM -0400, Christopher Murtagh wrote:
I'm not sure what happens when you do exit here, but I'll lay odds
against it being exactly the right things.
It ends the daemonized process, kinda like a wrapper suicide. :-)
I think you have a problem here. PostgreSQL is a complete program,
which use signal, atexit handlers, callback all or which fork()
preserves. When your little daemon exits it may trigger all the code
normally run on backend exit, you know, closing WAL files, etc...
The rest of the program has no idea it's a forked process rather than a
real one. Say the backend had a alarm() set and it goes off in your
forked process. Havoc ensues...
Ok, I think I'm seeing the light here. Thanks for your input!
So, if I made a slight modification to my script to this instead:
CREATE or REPLACE function exec_test() returns void as '
unless (defined ($pid=fork)) {
die cannot fork: $!;
}
unless ($pid) {
$cmd=/path/to/some/script.pl;
exec $cmd;
}
RETURN;
' language plperlu;
Then the exec'd $cmd shouldn't inherit any of the signals or atexit
handlers. My script.pl can handle any errors it encounters (other than
not being executed of course, but I can live with that), and this way I
don't have to write a daemon that polls listening for a NOTIFY.
Is this less objectionable? Again, thanks to all for your input and
feedback, I really do appreciate it.
Cheers,
Chris
--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
---(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: [GENERAL] Trigger that spawns forked process
On Tue, 2005-05-10 at 11:11 -0500, Jim C. Nasby wrote:
Well, LISTEN and NOTIFY are built into PostgreSQL
(http://www.postgresql.org/docs/8.0/interactive/sql-notify.html). If the
processes that you're trying to notify of the changes are connected to
the database then this might be the easiest way to do what you're
looking for. Setting up some form of replication, such as Slony, also
comes to mind. But it's impossible to really make a recommendation
without having a better idea of what you're doing.
BTW, my understanding is that it's pretty easy to write a daemon in
perl, and there are examples of how to do this floating around.
Yes, I saw the LISTEN/NOTIFY stuff, and it could be interesting. As to
the replication, Slony won't do it for me, as it isn't the database I
want to replicate. Here's a basic description:
I have 4 cluster nodes all running the same content management software
(home grown). When a change request comes in to one of them (update to
an XML document), it submits the new XML doc to the database (which is
the master repository of all content), then performs an XSLT. Upon the
new change, I want the database to propagate the new result of the XSLT
to the other nodes so that they can pre-cache it (to avoid page loading
latency).
I was given an example of how to spawn a forked process with plperlu,
and it looks pretty simple and straightforward and exactly what I want:
CREATE or REPLACE function somefunc() returns void as $$
$SIG{CHLD}='IGNORE';
# the preceding line removes any zombies created.
# Assumes you do not want to handle the return value
#from the child process
unless (defined ($pid=fork)) {
die cannot fork: $!;
}
unless ($pid) {
$cmd=your command here;
system $cmd;
if ($? != 0) {
# handle errors here
}
exit;
}
RETURN;
$$ language plperlu;
This seems to be pretty trivial, and near fail-proof to me. my '$cmd'
would then be a script that handles the replication of the cached file
to the nodes (already written and tested). Why is a daemon more robust
than this? (BTW, I ask out of ignorance, not out of arrogance).
Cheers,
Chris
--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Service Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger that spawns forked process
On Tue, 2005-05-10 at 13:50 -0700, David Fetter wrote: Why do you think Slony won't work for this? One way it could do it is to have an ON INSERT trigger that populates one or more tables with the result of the XSLT, which table(s) Slony replicates to the other servers. Because the nodes are not databases, they are Apache/PHP web servers which have file system caching where the URL = directory/file. The XSLT also converts XML objects to PHP code. So basically a content editor can do something like: br /br / This is my course description: coursedesc courseid=AAA 123 / ... etc. and the XSLT converts the coursedesc / tag into a PHP function (that does a db lookup). Cheers, Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Trigger that spawns forked process
On Tue, 2005-05-10 at 16:17 -0400, Tom Lane wrote:
... let's see, you already broke the backend there --- unless its normal
setting of SIGCHLD is IGNORE, in which case munging it is unnecessary
anyway ...
Here's my (probably all garbled) explanation: Essentially what that code
is a self-daemonizing perl wrapper. Setting SIGCHLD to IGNORE will
prevent zombie processes from hanging around, essentially
daemonizing/orphaning/forking the perl script.
unless ($pid) {
$cmd=your command here;
system $cmd;
if ($? != 0) {
# handle errors here
}
exit;
}
I'm not sure what happens when you do exit here, but I'll lay odds
against it being exactly the right things.
It ends the daemonized process, kinda like a wrapper suicide. :-)
(An atexit hook in a backend
process is entitled to suppose it is cleaning up a backend.) Also,
exactly what is your handle errors step going to be?
Well, if my command fails for some reason, I can replace '#handle
errors' with something that notifies me (email, or by populating the
database, etc.).
You don't get to reflect anything back into the database at that point.
That's ok, my $cmd might or might not have db connections in it, same
for the error checking script (which could be written in a totally
different language).
The main reason why this is probably a bad idea is that your
transaction is causing side-effects outside the database that cannot
be undone if the transaction later rolls back. The general consensus
of people who have looked at this is that it's safer to fire those
operations after the transaction actually commits.
I should have stated that this will get used only by single auto-commit
transactions. Any rollbacks are essentially changes to the past and
aren't permitted. Instead if someone wanted to 'undo' a change, they
would re-submit a previous version. This way, I can keep my replication
code to very atomic things which makes it very simple to write and
maintain.
From my (somewhat limited experience) point of view, I think that this
plperlu script isn't much different from writing a daemon to receive
signals via NOTIFY. Instead the script is self daemonizing, and it will
always run (instead of a couple of NOTIFY's building up and only one
being sent), which is more in line with what I want.
Sorry, my explanation probably isn't very clear at all, I've been
writing talk material and my brain is in a totally different space. Feel
free to deliver any LARTs. :-)
Cheers,
Chris
---(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
[GENERAL] Trigger that spawns forked process
I would like to write a trigger or function that spawns a forked process so that the transaction is considered 'complete' to the client, but continues to perform more work. I've been looking for examples to steal^H^H^H^H^H learn from but have only found someone asking pretty much the same question: http://archives.postgresql.org/pgsql-general/2002-12/msg01187.php Has anyone done anything like this? Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Service Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Trigger that spawns forked process
On Mon, 2005-05-09 at 15:38 -0400, Tom Lane wrote: Christopher Murtagh [EMAIL PROTECTED] writes: I would like to write a trigger or function that spawns a forked process so that the transaction is considered 'complete' to the client, but continues to perform more work. It's not very clear what you are hoping to have the forked process do, but if it's supposed to be another backend, forget it --- won't work. No, I don't want the trigger to do any db stuff at all. Basically, I've got a content management system that is going to be split across a cluster. Upon an update submission from one of them, I want to replicate across the others (which can happen in pseudo real time). So, basically the DB is the master, once it makes an update, it will spawn a process to the syncronization (all of this code is written). I just don't want the initial update process to wait for replication to finish (which is only a second or two under normal load). I could write a daemon that would sit an listen to these replication requests, but that just seems to be more complex than I need. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Service Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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: [GENERAL] Trigger that spawns forked process
On Mon, 2005-05-09 at 17:01 -0400, Douglas McNaught wrote: Why not have a client connection LISTENing and doing the synchronization, and have the trigger use NOTIFY? Or, you could have the trigger write to a table, and have another client periodically scanning the table for new sync events. Either one of those would be simpler and more robust than fork()ing inside the backend. How is writing a daemon simpler than using something that could be done within Postgres? Forking is something that should be natural to Unix systems, I shouldn't need to write another application to do this. I don't see how a daemon would necessarily be more robust either. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Service Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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: [GENERAL] Trigger that spawns forked process
On Mon, 2005-05-09 at 17:07 -0400, Tom Lane wrote: Douglas McNaught [EMAIL PROTECTED] writes: Why not have a client connection LISTENing and doing the synchronization, and have the trigger use NOTIFY? Or, you could have the trigger write to a table, and have another client periodically scanning the table for new sync events. Either one of those would be simpler and more robust than fork()ing inside the backend. ... not to mention it would avoid the risk of propagating not-yet-committed changes. How's that? If I can notify a daemon that the change is committed, then why couldn't I write a forking plperl function that executes when the transaction is done? How is one riskier than the other? Is there something obvious I'm missing here? Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Service Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL Porting Project
On Wed, 31 Dec 2003, Greg Sabino Mullane wrote: Maybe this is where the advocacy site and community can help the most. We could list these utilities that only support MySQL and ask for members of the community to contact the developers to help them support Postgres. Now, there are a lot of these types of apps, most of them aren't worth downloading let alone fixing, but unfortunately I suspect this preventing a lot of people from using Postgres. This has been on my todo list since someone mentioned something similar at last year's Postgres BOF at OSCON, but I have not gotten any further than designing it on paper. I will get to it eventually, but I have a lot of other projects, so if anyone wants to help out, please let me know and I'll share what I have so far. I'm interested in helping out where I can. Let me know what you've got and I'll try to help. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Permissions issue?
On Wed, 2003-12-17 at 13:20, Christopher Murtagh wrote: I'm trying to create a user without create privileges and I don't seem to be able to do it. I could be clueless, but after my revoke statements, the new user still seems to be able to create dbs, and then have full privileges on them. Am I missing something? Oops, I meant to say that they could create tables, etc. Not dbs. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Permissions issue?
On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote: I think you probably want to revoke create on the public schema. Create on databases controls the creation of schemas. From the grant page: Hrm, thanks for the reply. I tried that too. Here's what I got (below). Am I missing something obvious? [EMAIL PROTECTED] chris]$ createuser newuser Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER [EMAIL PROTECTED] chris]$ psql chris Welcome to psql 7.4RC2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit chris=# REVOKE ALL ON SCHEMA public FROM newuser; REVOKE chris=# REVOKE ALL ON DATABASE chris FROM newuser; REVOKE chris=# \q [EMAIL PROTECTED] chris]$ psql chris -U newuser Welcome to psql 7.4RC2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit chris= create table foo(bar integer); CREATE TABLE chris= insert into foo values (1); INSERT 6274026 1 chris= select * from foo; bar - 1 (1 row) ---(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: [GENERAL] Permissions issue?
On Wed, 2003-12-17 at 15:25, Tom Lane wrote: Christopher Murtagh [EMAIL PROTECTED] writes: Am I missing something obvious? The permissions were granted to PUBLIC, not to newuser, and so the REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and then grant them back to whomever should have them. Ahhh. that's it! I was missing something obvious. Thanks for the clue! Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] restore error - language plperlu is not trusted
Greetings, I just had to dump and restore one of my DBs (7.4RC2), and I got an interesting message. I first did: pg_dump dbname db_restore.sql Then at console did the following: \i db_restpre.sql which performed everything as expected with the following ERROR message: ERROR: language plperlu is not trusted and it gave a line number, which contained the following: GRANT ALL ON LANGUAGE plperlu TO postgres WITH GRANT OPTION; Now, my plperlu functions seem to behaving as expected (they read from and write to /tmp). Should I be worried? Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plperl/createlang issue
On Tue, 2003-11-11 at 21:22, Ed L. wrote: $ createlang plperl template1 ERROR: Load of file /opt/pgsql/installs/postgresql-7.3.4/lib/plperl.so failed: libperl.so: cannot open shared object file: No such file or directory createlang: language installation failed I had the exact problem recently, and I found the solution. 1) First, locate your libperl.so file. On my PPC box it was: [EMAIL PROTECTED] chris]$ locate libperl.so /usr/lib/perl5/5.8.0/ppc-linux-thread-multi/CORE/libperl.so 2) Add that directory to /etc/ld.so.conf 3) run ldconfig (as root) Then your creatlang statement should work. Hope that helps. Cheers, Chris But it sure looks like its there to me: $ ls -l /opt/pgsql/installs/postgresql-7.3.4/lib/plperl.so -rwxr-xr-x1 pg pg 35770 Nov 11 19:39 /opt/pgsql/installs/postgresql-7.3.4/lib/plperl.so* I *think* my perl is threaded based on perl -v: $ perl -v This is perl, v5.8.0 built for i386-linux-thread-multi ... Any clues for the clueless? TIA. Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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
[GENERAL] plpgsql return setof integer?
Greetings,
I've got a fairly simple function that I'm trying to return a set, but
seem to have come across a stumbling block. When I execute the function
below (the input params are not used at the moment) I get this:
chris=# select htdig('foo', 'foo');
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function htdig line 14 at return next
CREATE OR REPLACE FUNCTION htdig(text, text) RETURNS SETOF integer AS '
DECLARE
result text[];
low integer;
high integer;
item integer;
BEGIN
result := htsearch(''sample_return.txt'',''dbname'');
low := 2;
high := array_upper(result, 1);
FOR i IN low..high LOOP
item := result[i];
RETURN NEXT item;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' STABLE STRICT;
The function htsearch is working as expected (other than a strange HASH
as the first element, but I've compensated for that by starting at array
index 2). This is what it outputs:
chris=# select htsearch('sample_return2.txt','dbname');
htsearch
--
{HASH(0x835c298),2100,2113,2114}
(1 row)
Any obvious thing that I'm doing wrong? I'm using 7.4RC2. As always, any
help or info would be much appreciated. Bonus points if someone knows
what the HASH is. :-)
Cheers,
Chris
--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
---(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: [GENERAL] PL/Perl returning multiple rows
On Tue, 2003-11-11 at 00:07, Joe Conway wrote: Write a Pl/Perl function that just does the syscall, and call it from PL/pgSQL. Similarly for complex string parsing, etc. That would work if I could get the Pl/Perl function to return an array or set of results, but this brings me back to the original problem (unless I'm missing something obvious). Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Two build problems - openssl and plperl on RedHat
Greetings, I'm trying to build 7.3.4 and I've come across two problems, one during the configure and the other afterward. Problem 1) Trying to build with openssl support gives this: ./configure --with-openssl --enable-odbc --with-perl --enable-multibyte ... checking openssl/ssl.h usability... no checking openssl/ssl.h presence... no checking for openssl/ssl.h... no configure: error: header file openssl/ssl.h is required for OpenSSL [EMAIL PROTECTED] postgresql-7.3.4]# locate openssl/ssl.h /usr/include/openssl/ssl.h I've tried many flavors of --with-openssl=/usr/include but with no luck. I've removed the --with-openssl, because it isn't needed immediately, and everything configured and compiled fine. 2) I cannot figure out how to createlang plperl and plperlu. I'm getting this message: [EMAIL PROTECTED] postgres]$ /usr/local/pgsql/bin/createlang plperlu chris ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: libperl.so: cannot open shared object file: No such file or directory createlang: language installation failed The INSTALL docs state that I must have a full install of Perl (which I do: perl-5.8.0 via RedHat's rpm) and that it Postgres won't be able to compile if it isn't a shared object. Since Postgres compiled, can I assume that the Perl install is ok? My machine is a P4 running RedHat 9.0. Any info would be greatly appreciated. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is counter intuitive to some people and those of us who are coming from say a Perl background are going to be much more proficient in using pl/Perl then having to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Basically, what I'm doing is using htDig to index and search text objects within Postgres. I spent a lot of time trying to get GiST and tsearch to work, but the lack of documentation and complexity of it made it impossible. Plus, htDig already has features that allow it to ignore HTML, phrase searching as well as fuzzy logic for lexemes, soudex and whatnot. We donated a G4 (and hopefully more soon) to the htDig team to help get 3.2 out of beta, and it is paying off big time. Here's a prototype of one component of the search engine: http://newfind.mcgill.ca/ads/ which basically is an index of: http://www.mcgill.ca/classified/ That search tool works well, but it is a PHP wrapper/hack. I would much rather do it at the DB level rather than PHP as it makes it a much more powerful tool. If I had a month or two, I would take the htDig source and make it a Postgres plugin, but unfortunately I don't. The worst part of this is that I have about two days to finish building this. :-( So, perhaps I should stick with Perl for now, and hope that with a real SPI, the speed will improve significantly. Someone mentioned earlier that there is an experimental SPI... just how experimental exactly? Segfault and die or less dangerous? Thanks again for all the feedback. I'd be happy for any more thoughts and ideas. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote: Hello, If you can code in Perl then pl/C wouldn't be a deep jump. That might not be a bad idea. Haven't done much C programming since my CS days, but I really loved it then. Other than here: http://www.postgres.org/docs/7.3/static/xfunc-c.html Can you recommend more reading on writing C functions for Postgres? Books, anything? Thanks again. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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: [GENERAL] Graphical Mapping a Database
On Fri, 2003-08-15 at 00:17, David Fetter wrote: Tim Edwards [EMAIL PROTECTED] wrote: Does anyone have a utility to map out database structures and put it in a printable format ? PDF, GIF, JPG, etc. Try DBVisualizer at http://www.minq.se/. FWIW, we bought a site license for DBVis and it has been pretty great. It runs on my YellowDog Linux (PPC) and RedHat desktops as well as our MacOS X machines. Well worth the $$ IMO. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 8: explain analyze is your friend
PHP db support was Re: [GENERAL] Dreamweaver
On Sat, 2003-08-16 at 12:34, Ken Harris wrote: The closet thing PHP has is PEAR - it has the Dataobject concept and is as close to neutral as PHP gets. A long time ago, I wrote a DB Neutral set of routines which works with Oracle, Postgresql and, untested, MySQL. That just seemed to be the easiest thing to do, but I have not tried to plug it into Dreamweaver. PHP also has a module called dbx that allows you to do this as well. It has some serious performance advantages over the PEAR library because it is a module (written in C) rather than a bunch of PHP classes. More on dbx here: http://ca.php.net/manual/en/ref.dbx.php Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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: [GENERAL] Changing DB ownership
On Tue, 2003-08-05 at 11:11, Jimmie H. Apsey wrote: Why would you want to do that? Why would I want to change ownership of a database? I could think of numerous reasons. Why not do it an easier way and dump the database and restore it into your new database? Because that would mean that the database would be down while I did that. The users to whom I gave ownership to the DB already had permissions on the tables in the DB. This meant no down time. There's got to be a lot of stuff to consider when doing something as radical as renaming a database. I didn't rename any database, nor do anything 'radical'. Please re-read my message and you'll see. This SQL query: UPDATE pg_database SET datdba = 504 WHERE datname='chris'; is obviously simpler than a pg_dump, dropdb, createdb, pg_restore. Cheers, Chris -- Christopher Murtagh Webmaster / Sysadmin Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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
[GENERAL] Changing DB ownership
Dumb question maybe, but how does one change database ownership? I've tried several permutations of: ALTER DATABASE SET (|'| )owner(|'| ) TO (|'| )newowner(|'| ); and I tried looking at pg_database and I *was* able to hack this (got a clue half way through writing this email... sorry): UPDATE pg_database SET datdba = 504 WHERE datname='chris'; And that worked as expected, but I'm worried that I might have missed something somewhere else. Will the above UPDATE cause problems down the road? Any info/clue would be much appreciated. Thanks in advance. Cheers, Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
