[GENERAL] Trigger email?
Is it possible to setup a trigger so that every time a certain field is changed, an email is sent? Using pl/pgsql. Thanks, CSN __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Can't compile plphp
I'm following the directions here: http://plphp.commandprompt.com/documentation.html (Note that they differ from the directions included in plphp-7.4.x.tar.bz2, which I've also tried.) Using Postgres 7.4.7 and PHP 5.0.4. When I do this: 7. Build and install your plphp.so library. cd src/pl/plphp make && make install I get this error: cp @php_path@/main/php.h ./ cp: cannot stat [EMAIL PROTECTED]@/main/php.h': No such file or directory make: *** [patch] Error 1 Know what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] could not load library plperl.so
I configured postgres base+opt with '--with-perl', make'd, and make install'ed. Trying to add plperl: createlang -U postgres plperl links I get this error: createlang: language installation failed: ERROR: could not load library "/usr/lib/postgresql/plperl.so": libperl.so: cannot open shared object file: No such file or directory plperl.so does exist in /usr/lib/postgresql (libperl.so does not). I also compiled in plpython, and createlang appeared to work for that. Any idea what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] could not load library plperl.so
--- Tom Lane <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > createlang: language installation failed: ERROR: > > could not load library > > "/usr/lib/postgresql/plperl.so": libperl.so: > cannot > > open shared object file: No such file or directory > > Notice it's complaining about libperl.so, not > plperl.so. > > This is probably because libperl.so isn't in your > ldconfig search path. > Before PG 8.0 it's necessary to add the directory > where Perl keeps that > file to your ldconfig configuration. > > If you *are* using 8.0, then please explain exactly > what platform > you're on and what your Perl installation is. > > regards, tom lane Yeah, I did 'ln /usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE/libperl.so /usr/lib/libperl.so' and 'createlang plperl dbname' worked without complaining (I should probably use ldconfig instead). I'm using 7.4.7 (will probably upgrade to 8.x pretty soon). CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] plpythonu and dollar quoting
Can dollar quoting be used with plpythonu (like with plperl - http://www.postgresql.org/docs/8.0/interactive/plperl.html#PLPERL-FUNCS)? When trying to create a function I get this error: 'syntax error at or near "$"' If I use single quotes, then escape all quotes and (a lot of) backslashes in the python code then the function gets created fine. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpythonu and dollar quoting
Nevermind, I see dollar-quoting was added in 8.0. --- CSN <[EMAIL PROTECTED]> wrote: > Can dollar quoting be used with plpythonu (like with > plperl - > http://www.postgresql.org/docs/8.0/interactive/plperl.html#PLPERL-FUNCS)? > When trying to create a function I get this error: > > 'syntax error at or near "$"' > > If I use single quotes, then escape all quotes and > (a > lot of) backslashes in the python code then the > function gets created fine. > > CSN __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] plpythonu and booleans
When accessing Postgres boolean fields (in the TD dictionaries) in plpythonu, their values are '0' or '1'. IIRC, in PHP their values would be 't' or 'f'. How are boolean values actually stored in Postgres? CSN Discover Yahoo! Stay in touch with email, IM, photo sharing and more. Check it out! http://discover.yahoo.com/stayintouch.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Booleans - Why in Postgres and not in Oracle or Mysql?
I like Postgres's boolean type - why do you suppose neither Mysql nor Oracle has bothered to implement it? Booleans are in the SQL-99 spec. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Booleans - Why in Postgres and not in Oracle or Mysql?
--- Dann Corbit <[EMAIL PROTECTED]> wrote: > > why do you suppose > > neither Mysql nor Oracle has bothered to implement > it? > > Probably they just create a domain using a char > which can take on two > values (e.g. 't'/'f' or 1/0). Oracle 10 has a bit > data type (which is a > boolean for all intents and purposes): Could a bit handle NULL's though? CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] plphp1.1 make fails
I followed the new instructions (patch, autoconf, configure --with-php=/usr/lib/apache/libphp5.so, etc.) and get this error when running 'make': make[3]: Entering directory `/usr/local/src/postgresql-8.0.2/src/pl/plphp' patching file php.h Hunk #1 FAILED at 291. 1 out of 1 hunk FAILED -- saving rejects to file php.h.rej make[3]: *** [php.h] Error 1 make[3]: *** Deleting file `php.h' make[3]: Leaving directory `/usr/local/src/postgresql-8.0.2/src/pl/plphp' make[2]: *** [all] Error 2 make[2]: Leaving directory `/usr/local/src/postgresql-8.0.2/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/local/src/postgresql-8.0.2/src' make: *** [all] Error 2 Know what the problem is? Thanks, CSN Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html ---(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] Locale C?
I'm installing PG8 for Windows and the default locale is "C" in the installer. What is locale C? CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] More detailed error logging?
Is it possible to have the database name, datetime, and maybe even the query itself show up in pgsql's error log? Thanks, CSN __ Do you Yahoo!? Yahoo! Small Business - Try our new Resources site http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] plpython error since upgrading from 7.x to 8.x
I had a plpythonu function that worked in 7.x but since upgrading to 8.x it's giving this error: ERROR: plpython: function "notify" failed DETAIL: exceptions.TypeError: unsubscriptable object plpythonu IS installed in the database in which I'm trying to use the function. Any idea what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] plpython error since upgrading from 7.x to 8.x
It happens when I try to insert rows:
insert into table1 (col1, col2, col3) values (val1,
val2, val3);
I have an insert/update trigger on that table, and the
plpythonu function just sends a notification email.
Here's the function:
if TD["new"]["active"] != TD["old"]["active"]:
import smtplib
import string
fromaddr = "[EMAIL PROTECTED]"
toaddr = "[EMAIL PROTECTED]"
if TD["new"]["active"] == 1:
msg = "From: %s\r\nTo: %s\r\nSubject:
Approved\r\n\r\n" % (fromaddr, toaddr)
msg = msg + """Greetings,
...
"""
else:
msg = "From: %s\r\nTo: %s\r\nSubject: New
Record\r\n\r\n" % (fromaddr, toaddr)
msg = msg + """Greetings,
...
"""
server = smtplib.SMTP("localhost")
server.set_debuglevel(1)
server.sendmail(fromaddr, toaddr, msg)
server.quit()
#
I also recently switched to Dbmail, so maybe that
caused a problem (but mail works fine with everything
else).
CSN
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Wed, May 25, 2005 at 02:04:22AM -0700, CSN wrote:
> >
> > I had a plpythonu function that worked in 7.x but
> > since upgrading to 8.x it's giving this error:
> >
> > ERROR: plpython: function "notify" failed
> > DETAIL: exceptions.TypeError: unsubscriptable
> object
>
> Could you post a simple, self-contained example that
> exhibits this
> problem? Debugging would be easier if we could see
> what you're doing.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
__
Yahoo! Mail
Stay connected, organized, and protected. Take the tour:
http://tour.mail.yahoo.com/mailtour.html
---(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] plpython error since upgrading from 7.x to 8.x
Ah, you're right. The function appears to only cause an error on inserts and not updates. Thanks for pointing that out (I'm really green at Python). CSN --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Wed, May 25, 2005 at 12:02:22PM -0700, CSN wrote: > > > > It happens when I try to insert rows: > > > > insert into table1 (col1, col2, col3) values > (val1, > > val2, val3); > > > > I have an insert/update trigger on that table, and > the > > plpythonu function just sends a notification > email. > > Here's the function: > > > > > > if TD["new"]["active"] != TD["old"]["active"]: > > Without a complete, self-contained example that > succeeds in one > version of PostgreSQL and fails in another, we still > have to guess > at what's going on. By "complete, self-contained > example" I mean > a sequence of SQL statements that anybody could load > into an empty > database and get the same results that you get. > When putting > together such an example, it's a good idea to reduce > it as much as > possible, i.e., keep removing things until you can't > possibly make > the example smaller and still get the behavior in > question. Aside > from making it easier for others to focus on the > problem, sometimes > the act of reducing the problem can help you find > the problem on > your own. > > My first guess would be that you're getting an error > because when > the event is INSERT, TD["old"] is None and thus > unsubscriptable. > But if that's the case then I'm not sure why the > version of PostgreSQL > would matter, and without seeing a complete example > I'm not convinced > that it does. Do you still get the error if you > check if TD["event"] > is "UPDATE" before trying to use TD["old"]? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > __ Do you Yahoo!? Yahoo! Small Business - Try our new Resources site http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Setting global vars for use with triggers
I'd like to create a trigger which deletes associated files whenever their corresponding row is deleted. Problem is, I don't want to hard-code directory locations in the trigger function. Is there a way to set a DOCUMENT_ROOT-like variable in Postgresql which triggers could access and use? I'm using PHP for the trigger (and PG 8.x). Thanks, CSN PS - Scott and I have been tossing around a few other ideas here: http://www.phpbuilder.com/board/showthread.php?s=&threadid=10302693 Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com ---(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] Finding points within 50 miles
If I have a table of items with latitude and longitude coordinates, is it possible to find all other items that are within, say, 50 miles of an item, using the geometric functions (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? If so, how? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] pg_dump - dump specific functions and other items?
Is it possible to dump specific function definitions using pg_dump? Any other items that can be specifically dumped, besides just tables? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Internal catalogs error in log file
I saw this in my log file: ERROR: invalid regular expression: quantifier operand invalid STATEMENT: SELECT n.nspname as "Schema", p.proname as "Name", CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) as "Result data type", pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proname ~ '^+$' ORDER BY 1, 2, 3, 4; It's greek to me ;). Any idea what caused this error, and if there's some sort of problem? Thanks, CSN PG 8.0.2 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] untrusted languages and non-global superusers?
I'm using plphpu and I'd like to allow the regular database user to use it, but since it's "untrusted" it requires users to be superusers. If I have to do this, I don't want the user to be a superuser for all databases. Is it possible to grant superuser status to a user for a specific database? All I'm familiar with is "alter user joe createuser." Or is there a better way of handling this language and permission issue? (The function uses mail(), so IIRC that necessitates using plphpu). Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] untrusted languages and non-global superusers?
--- Tom Lane <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > I'm using plphpu and I'd like to allow the regular > > database user to use it, but since it's > "untrusted" it > > requires users to be superusers. If I have to do > this, > > I don't want the user to be a superuser for all > > databases. Is it possible to grant superuser > status to > > a user for a specific database? > > Exactly how would you prevent him from converting > that into global > access? Especially if you're going to give him use > of an untrusted > language? He could easily rewrite any configuration > file you might > think is going to lock him out of your other > databases. You lost me - how is any of that possible? > > > (The function uses mail(), so IIRC that > necessitates > > using plphpu). > > Sending mail from a database function (or doing > anything else that > involves external side-effects) is generally A Bad > Idea, for reasons > that have been covered many times in the list > archives. Why, exactly? In this situation I just set up a trigger that sends a welcome email to newly inserted members. Very convenient. CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] untrusted languages and non-global superusers?
--- Tino Wildenhain <[EMAIL PROTECTED]> wrote: > > > > (The function uses mail(), so IIRC that > > > necessitates > > > > using plphpu). > > > > > > Sending mail from a database function (or doing > > > anything else that > > > involves external side-effects) is generally A > Bad > > > Idea, for reasons > > > that have been covered many times in the list > > > archives. > > > > Why, exactly? In this situation I just set up a > > trigger that sends a welcome email to newly > inserted > > members. Very convenient. > > Why cant your application handle this? > Otoh, why dont you provide a function to send mail, > which takes some parameters and just let your users > use them? No need for everybody to write her own > mail function. Convenience! I want the email sent whether the member is added via the web interface, directly in the database, from the command line, etc. I don't see any downside. It's only one user that'll be using this function. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] untrusted languages and non-global superusers?
--- Tino Wildenhain <[EMAIL PROTECTED]> wrote: > > Convenience! I want the email sent whether the > member > > is added via the web interface, directly in the > > database, from the command line, etc. I don't see > any > > Well, I also do such things with a small script > which > basically LISTENs to notify from database, spools > the > mails and go sleep again. Could you elaborate how you do this? IIRC, there's an example in the docs using C, but I'd prefer using a scripting language. Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Getting actual number of rows updated
Is it possible to have PG report the actual number of rows that actually CHANGED in an update command? e.g. UPDATE items set name=replace(name,'abc','def'); UPDATE 9000 -- Actually only 3 were changed rather than update reporting all rows have been "updated"? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] NOTIFY/LISTEN, PHP, rule vs. trigger, blocking, missed NOTIFY's
Scott and I were discussing NOTIFY/LISTEN using a PHP
script here:
http://phpbuilder.com/board/showthread.php?t=10302693
Basically:
PHP Code:
#!/usr/bin/php -q
And the sql code:
CREATE TABLE ntest ( id serial primary key, path text
);
create table naudit ( id int primary key, path text );
create rule audit_test as on delete to ntest do (
insert into naudit(id,path) values (OLD.id, OLD.path);
notify record_deleted );
insert into ntest (path) values
('/usr/local/lib/php.ini2');
delete from ntest;
I think he may be off on some wild and exotic vacation
;) or something - so I'll post my questions here too:
* Is there any reason to use a rule rather than a
trigger? I guess a rule is just simpler.
* Also, think there's any way to just have the PHP
script block until a notify event is actually
received, rather than checking every [sleep] seconds?
* Finally, PG's docs on notify say that if events
happen in rapid succession, notify's might get
dropped. For example: could many item rows get
deleted, but some of their corresponding files not get
deleted due to dropped notify's?
Thanks,
CSN
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] ERROR: plphp: unable to register function "plphp_proc_4947785_trigger"
I'm using plphp to create a trigger. I don't see any syntax errors in it (I've checked it with php -l (lint)). When I update a row in the table with the trigger, I get this error: ERROR: plphp: unable to register function "plphp_proc_4947785_trigger" I've verified that plphp (and plphpu) is installed with 'createlang -l'. Any idea what the problem is? Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
I'm getting this warning in pgsql's log: LOG: plphp: PHP Warning: Call-time pass-by-reference has been deprecated - argument passed by value; If you would like to pass it by reference, modify the declaration of [runtime function name](). If you would like to enable call-time pass-by-reference, you can set allow_call_time_pass_reference to true in your INI file. However, future versions may not support this any longer. in plphp trigger call on line 1 Is there anything I can do about it? I'd email plphp's list, but their mailing list links are 404. Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] ERROR: plphp: unable to register function "plphp_proc_4947785_trigger"
Nevermind, I found some stray single quotes (in an array var) in a double-quoted string that appears to have been the problem. --- CSN <[EMAIL PROTECTED]> wrote: > I'm using plphp to create a trigger. I don't see any > syntax errors in it (I've checked it with php -l > (lint)). When I update a row in the table with the > trigger, I get this error: > > ERROR: plphp: unable to register function > "plphp_proc_4947785_trigger" > > I've verified that plphp (and plphpu) is installed > with 'createlang -l'. > > Any idea what the problem is? > > Thanks, > CSN > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] plphp crashing server
Uh oh, I think plphp is crashing the server. When I update a row in the table with the trigger, this happens: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. And this is what shows up in the log: LOG: server process (PID 31665) was terminated by signal 11 LOG: terminating any other active server processes Wow. I still don't see anything wrong with the plphp function - how could it cause the entire server to crash? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plphp crashing server
Jeez, nevermind again! Turns out this was the problem in the plphp script: $sql="select * from table where id=123"; $result=spi_exec_query($sqll); I'm still curious about the "Call-time pass-by-reference has been deprecated" warning if anybody knows. Thanks, CSN --- CSN <[EMAIL PROTECTED]> wrote: > Uh oh, I think plphp is crashing the server. When I > update a row in the table with the trigger, this > happens: > > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting > reset: Failed. > > And this is what shows up in the log: > > LOG: server process (PID 31665) was terminated by > signal 11 > LOG: terminating any other active server processes > > Wow. I still don't see anything wrong with the plphp > function - how could it cause the entire server to > crash? > > Thanks, > CSN > > > > > Start your day with Yahoo! - make it your home page > http://www.yahoo.com/r/hs > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
I'm using PHP5, and I'm not passing by reference. My
first stop WAS plphp.commandprompt.com, but none of
their mailing list links for plphp work.
CSN
> # jd ( at ) commandprompt ( dot ) com / 2005-08-11
15:45:18 -0700:
> > Roman Neuhauser wrote:
> > ># cool_screen_name90001 ( at ) yahoo ( dot ) com
/ 2005-08-11 13:23:52 -0700:
> > >
> > >>I'm getting this warning in pgsql's log:
> > >>
> > >>LOG: plphp: PHP Warning: Call-time
pass-by-reference
> > >>has been deprecated - argument passed by value;
If
> > >>you would like to pass it by reference, modify
the
> > >>declaration of [runtime function name](). If
you
> > >>would like to enable call-time
pass-by-reference, you
> > >>can set allow_call_time_pass_reference to true
in your
> > >>INI file. However, future versions may not
support
> > >>this any longer. in plphp trigger call on line
1
> > >>
> > >>Is there anything I can do about it?
> > >
> > >
> > >Yes.
> > >
> >
> > Actually the below is incorrect. He should be
visiting
> > plphp.commandprompt.com and signing up for the
list there.
>
> Why? What does the generic warning emitted by
PHP 4 (no need to get
> PostgreSQL into the mix) since forever on code
like this:
>
> function foo($arg) {} /* foo is declared to take
$arg by value */
> foo(&$var); /* $var is passed by reference */
>
> have to do with PL/PHP?
>
> > >It's completely off topic here, however.
You'll find more help in
> > >the PHP manual and/or php-general ( at )
lists ( dot ) php ( dot ) net (you can subscribe
> > >from http://www.php.net/).
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
Sure-
CREATE or REPLACE FUNCTION email_activated_member ()
RETURNS trigger AS $$
$new=$_TD['new'];
$old=$_TD['old'];
if(($_TD['event']=='INSERT' and $new['active']='t') or
($_TD['event']=='UPDATE' and $new['active']=='t' and
$old['active']=='f')) {
$link=$_TD['new'];
$sql="select *
from groups
where id=$link[group_id]";
$result=spi_exec_query($sql);
if($result) {
$group=spi_fetch_row($result);
}
if($group) {
$message=<<";
mail($to,
"Link Activated - $group[name]!",
$message,
"From: {$group[name]} <$group[email]>\r\n");
}
}
}
$$ LANGUAGE 'plphpu';
-- CREATE TRIGGER email_activated_member AFTER INSERT
or UPDATE ON links FOR EACH ROW EXECUTE PROCEDURE
email_activated_member();
It justs lets people know when their link has been
activated.
CSN
--- Roman Neuhauser <[EMAIL PROTECTED]> wrote:
> # [EMAIL PROTECTED] / 2005-08-11
> 16:49:25 -0700:
> > I'm using PHP5, and I'm not passing by reference.
> My
> > first stop WAS plphp.commandprompt.com, but none
> of
> > their mailing list links for plphp work.
>
> Can you post the code that triggers the warning?
>
> --
> How many Vietnam vets does it take to screw in a
> light bulb?
> You don't know, man. You don't KNOW.
> Cause you weren't THERE.
> http://bash.org/?255991
>
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
--- Roman Neuhauser <[EMAIL PROTECTED]> wrote: > # [EMAIL PROTECTED] / 2005-08-11 > 17:36:49 -0700: > > --- Roman Neuhauser <[EMAIL PROTECTED]> wrote: > > > Can you post the code that triggers the > warning? > > > > Sure- > > > > > > CREATE or REPLACE FUNCTION email_activated_member > () > > RETURNS trigger AS $$ > > > > $new=$_TD['new']; > > $old=$_TD['old']; > > > > if(($_TD['event']=='INSERT' and > $new['active']='t') or > > You are assigning to $new['active'] instead of > the > probably wanted comparison. > > I don't see any byref arguments, and don't know > how to help further. Doh! I fixed it but I'm still getting the same warnings in the log. I'd guess maybe it's something plphp is doing on its own with references, but I should probably play around with some more plphp functions and see if they generate the same warnings. Anyhow, thanks for the help. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Removing tsearch2 from a database
Greetings- How can I remove tsearch2 (all its tables, types, functions, etc.) from a database? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Apostrophe doesn't show up in command line
In a field I have text like "in today's news..." When I select that field in psql using putty (Latin-1), then apostrophe doesn't show up (shows up as "todays"), but it does show up in phppgadmin (and other php programs). Is this an issue with psql, or putty (or something else)? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Removing -'s (header) before records in psql
Is it possible to get rid of the "header" of -'s when selecting rows in psql? For fields with a lot of text, it looks like: select body from news where id=123; -[ RECORD 1 ]- -- -- - body | Additional details ... Thanks, CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Apostrophe doesn't show up in command line
--- Richard Huxton wrote: > CSN wrote: > > In a field I have text like "in today's news..." > When > > I select that field in psql using putty (Latin-1), > > then apostrophe doesn't show up (shows up as > > "todays"), but it does show up in phppgadmin (and > > other php programs). Is this an issue with psql, > or > > putty (or something else)? > > It's an issue with your character-set settings > somwhere along the line. > This sort of thing can be a real pain - you'll need > to check every > component involved. Start at the Windows/putty end, > and check what > character set phppgadmin is using (HINT: is it > utf-8?) Hmm, how can you tell? I don't see character set specified anywhere in phppgadmin (including conf.inc.php). CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] converting curly apostrophes to standard apostrophes
Is there a way to replace all curly apostrophes with standard apostrophes (presumably with replace(x,y,z))? My database is SQL_ASCII and I can't find a character code for curly apostrophes in ASCII here: http://www.lookuptables.com, but nevertheless there appear to be curly apostrophes in the database. Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Apostrophe doesn't show up in command line
Ah, it's Western ISO-8859-1. Putty has the same setting. I tried changing putty's charset to UTF-8 and now curly apostrophes are displayed as a grey box in psql's output (e.g. "in today[box]s news..."). Thanks, CSN --- Richard Huxton wrote: > CSN wrote: > >>and check what > >>character set phppgadmin is using (HINT: is it > >>utf-8?) > > > > > > Hmm, how can you tell? I don't see character set > > specified anywhere in phppgadmin (including > > conf.inc.php). > > View > Character Encoding in firefox while you have > a page open > View > Encoding in IE > > -- >Richard Huxton >Archonet Ltd > Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] converting curly apostrophes to standard apostrophes
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Mon, Aug 15, 2005 at 12:14:16PM -0700, CSN wrote:
> > Is there a way to replace all curly apostrophes
> with
> > standard apostrophes (presumably with
> replace(x,y,z))?
> > My database is SQL_ASCII and I can't find a
> character
> > code for curly apostrophes in ASCII here:
> > http://www.lookuptables.com, but nevertheless
> there
> > appear to be curly apostrophes in the database.
>
> The "Extended ASCII Codes" section of that page
> might not match
> what your system uses (it doesn't match mine). Have
> you tried using
> the ascii() function on the offending data?
I logged back in after changing putty's charset to
UTF-8 and am now able to paste an Â’ (curly apostrophe)
into psql, however that character still appears as a
grey box in psql's select output. I've tried various
queries to find which rows and fields contain it,
without success (I know it's in some of them):
db=>select ascii('Â’');
ascii
---
226
db=>select id from news where body ilike '%Â’%';
(0 rows)
db=>select id from news where body ilike '%' ||
chr(226) || '%';
db'>
db'>^C
db=>
CSN
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] converting curly apostrophes to standard apostrophes
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Mon, Aug 15, 2005 at 01:48:00PM -0700, CSN wrote:
> > db=>select ascii('Â’');
> > ascii
> > ---
> >226
> >
> > db=>select id from news where body ilike '%Â’%';
> > (0 rows)
> >
> > db=>select id from news where body ilike '%' ||
> > chr(226) || '%';
> > db'>
> > db'>^C
> > db=>
>
> What's going on with the last query? The prompt
> change suggests
> that psql is confused with quoting, and the ^C looks
> like you hit
> Control-C to get the regular prompt back. Did you
> ever run this
> query? If it produced no rows then you could widen
> the search.
Hmm, I'm on another computer and I just tried that
last query and it worked without psql thinking it
needed another single quote. Appears the chr code is
146 not 226 (turns out chr(226) is â - why that
doesn't cause problems with iso-8859-1/utf-8 xml and
the single/double quotes and dashes do I don't know).
Anyhow, I ended up doing this:
update news set body=replace(body,chr(146),''''); --
left single quote
update news set body=replace(body,chr(145),''''); --
right single quote
update news set body=replace(body,chr(147),'"'); --
left double quote
update news set body=replace(body,chr(148),'"'); --
right double quote
update news set body=replace(body,chr(150),'-'); -- en
dash
update news set body=replace(body,chr(151),'-'); -- em
dash
and that seems to do the trick. Most places I found
online listed different chars for these codes, but
http://www.webopedia.com/quick_ref/asciicode.asp lists
them. Jeez, I'm so confused with encodings, charsets,
etc. now. :(
Thanks,
CSN
> Example:
>
> SELECT id FROM news WHERE body ~ '[\200-\377]';
>
> You could use the "string from pattern" variant of
> substring() to
> extract characters in a specific range. If you have
> PL/Perl then
> it would be trivial to extract all of and only the
> special characters
> along with their ASCII codes:
>
> CREATE FUNCTION special_chars(text) RETURNS text AS
> '
> return join(" ", map {"$_:" . ord($_)} $_[0] =~
> /[\200-\377]/g);
> ' LANGUAGE plperl IMMUTABLE STRICT;
>
> SELECT id, special_chars(body) FROM news WHERE body
> ~ '[\200-\377]';
>
> --
> Michael Fuhr
>
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] Suggestion for Date/Time Functions Section
Hi, I suggestion for the date/time functions in the docs: http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html I was trying to figure out how to do: update table set next=now() + interval 'table.period seconds'; I tried subqueries, the concat operator, and anything else I could think of until I rediscovered the page on the various ways to cast. Perhaps mix up the examples in the docs so other ways to do date arithmetic (and that allow expressions, fields, etc.) are obvious: date '2001-09-28' + cast((7+7)||' seconds' as interval); date '2001-09-28' + ((7+7)||' seconds')::interval); etc. Or am I missing an easier way to do date arithmetic using a table's fields as part of the equation? (I think mysql has date_add(...), date_subtract(...), etc. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] selecting rows older than X, ensuring index is used
Hi,
I want to select records that haven't had an error
(logged to last_error) in the last 24 hours. My query
is:
select * from table1
where last_error is null
or extract(epoch from now()-last_error) > 86400;
I've created an index on last_error (timestamp with
time zone - can be NULL), then used EXPLAIN:
Seq Scan on table1 (cost=0.00..20.86 rows=217
width=72)
Filter: ((last_error IS NULL) OR
(date_part('epoch'::text, (now() - last_error)) >
86400::double precision))
There are over 550 rows in table1, so it doesn't look
the index is being used. Is there a way to rewrite
this query so the index is used?
Thanks,
CSN
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] history is not supported by this installation
Hi, IIRC psql's "\s" used to work, but I upgraded to 8.x on Windows - which recommends that cygwin/bin be removed from PATH - and I get this error: "history is not supported by this installation" Reading the docs, it states GNU readline is required. I presume that that was in cygwin/path - what's the remedy to get \s to work? BTW, is there a way to set the number of commands returned by "\s"? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] POSS. FEATURE REQ: "Dynamic" Views
For lack of a better term, but I was curious if there is/was any thought about making PG's views automatically "see" changes in underlying tables, as opposed to currently having to drop/create all corresponding views if a table's structure (add/delete fields, etc.) is changed. CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] About dropped notifications
The docs state: "NOTIFY behaves like Unix signals in one important respect: if the same notification name is signaled multiple times in quick succession, recipients may get only one notification event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifications received. Instead, use NOTIFY to wake up applications that need to pay attention to something, and use a database object (such as a sequence) to keep track of what happened or how many times it happened." I'm considering setting up a script that listens for notifications for a table and if a row is deleted the script will delete that row's corresponding files. If there are thousands of rows in the table, and I do "delete from table", or even "delete from table where id >1000 and id<2000", will the script be notified of the deletion of each and every row (and subsequently be able to delete that row's files), or will only one notify event be received (or some number less than the actual number of rows deleted)? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Deferred triggers?
Perhaps another possible feature request! I've looked through the docs and it doesn't appear that it's possible to create deferred triggers - i.e. they don't get called unless the current transaction commits. (My understanding is that they currently get called immediately whether or not there is a transaction in progress.) CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Deferred triggers?
> CSN wrote: > > Perhaps another possible feature request! I've looked > > through the docs and it doesn't appear that it's > > possible to create deferred triggers - i.e. they don't > > get called unless the current transaction commits. > > The semantics of such a thing appear to be indeterminate. What happens > if something in the trigger would have caused the original transaction > to fail? Most people would expect all changes made by the original > transaction, as well as those made by the trigger, to be rolled back. > Using deferred triggers as you've defined it would then require chainged > transactions, which could get very messy. That doesn't sound too messy - the trigger could either cause the current transaction to abort, or commit. > > (My understanding > > is that they currently get called immediately whether or not there is > > a transaction in progress.) > > There is always a transaction in progress. I meant when you explicitly enclose multiple statments in a single transaction. > > -- > Guy Rouillier Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Utility that creates table schema from csv data?
Probably wishful thinking, but who knows - maybe there's something in contrib! I have a bunch of csv data with the field names specified on the first line of the various files. Is there any such utility that will create a table schema using the field names AND look through the data and determine what data types each field should be? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] character varying == text?
Just something I was curious about - is there any difference at all between "character varying" (in the SQL spec) without a length specified and "text" (not in the SQL spec)? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Preventing duplicate records according to several fields
I have a table like so: id, title, yield, directions and would like to prevent duplicate records from being added (i.e. according to the title, yield, and directions fields). I won't normally be querying on the yield or directions fields, so I just have indexes for id and title. What's the best way to prevent duplicates from being added? - Before inserting, do a 'select id from stuff where title=? and yield=? and directions=?'. This would want the title and directions fields indexed (which seems like a waste of space since they won't be used except for rare inserts). - Create a unique index across the title, yield, and directions fields. - Create a 'hash' field by md5'ing the title, yield, and directions fields, and create a unique index on it. Then when inserting new records, first create a hash and check if it already exists, or have the database automatically handle this (trigger to compute hash field at insert time - unique index will raise an exception). Thanks for any help, insights, suggestions, etc. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] index row size exceeds btree maximum
This appears related to my previous post: http://archives.postgresql.org/pgsql-general/2005-09/msg00809.php I setup a unique index using the title, yield, and directions fields. Some inserts are causing this error: DBD::Pg::st execute failed: ERROR: index row size 2832 exceeds btree maximum, 2713 CONTEXT: SQL statement "insert into stuff (title, yield, directions) values ( $1 , $2 , $3 )" What do I do? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] index row size exceeds btree maximum
--- Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Wed, 2005-09-21 at 15:02, CSN wrote: > > This appears related to my previous post: > > > http://archives.postgresql.org/pgsql-general/2005-09/msg00809.php > > > > I setup a unique index using the title, yield, and > > directions fields. Some inserts are causing this > > error: > > > > DBD::Pg::st execute failed: ERROR: index row size > > 2832 exceeds btree maximum, 2713 > > CONTEXT: SQL statement "insert into stuff (title, > > yield, directions) values ( $1 , $2 , $3 )" > > > > What do I do? > > Don't insert such big values? :) > > Actually, the standard solution is to use an md5 of > the three fields: > > create unique index threefieldindex on table1 > (md5(field1||field2||field3)); > Ah, cool! Looks like using tsearch2 would be another option, but I don't plan on searching through the yield or directions fields (except at insert time). http://joseph.randomnetworks.com/archives/2005/08/05/postgresql-index-limitation-index-row-size-x-exceeds-btree-maximum-2713/ CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Finding (and deleting) dupes in relation table
I have a table that relates id's of two other tables: table1id, table2id Dupes have found their way into it (create unique index across both fields fails). Is there a quick and easy way to find and delete the dupes (there are tens of thousands of records)? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Finding (and deleting) dupes in relation table
Nevermind, figured it out: select distinct on (table1id, table2id) * into temp from table3; delete from table3; insert into table3 select * from temp; --- CSN <[EMAIL PROTECTED]> wrote: > I have a table that relates id's of two other > tables: > > table1id, table2id > > Dupes have found their way into it (create unique > index across both fields fails). Is there a quick > and > easy way to find and delete the dupes (there are > tens > of thousands of records)? > > Thanks, > CSN > > > > __ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Function keys cause psql to segfault
If I'm in psql (via putty, from WinXP to Redhat) and hit F1-4 (F5+ just display a ~), psql will segmentation fault and exit. Not that I'm in the habit of entering function keys while in psql - I accidentally hit one while entering numbers (lost a fair amount of history). I just tried it using WinXP's command prompt and none of the function keys cause psql to segfault. CSN __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Function keys cause psql to segfault
Here's what 'od -c' shows for F1-4: ^[OP^[OQ^[OR^[OS CSN --- Bruce Momjian wrote: > CSN wrote: > > If I'm in psql (via putty, from WinXP to Redhat) > and > > hit F1-4 (F5+ just display a ~), psql will > > segmentation fault and exit. Not that I'm in the > habit > > of entering function keys while in psql - I > > accidentally hit one while entering numbers (lost > a > > fair amount of history). > > My guess is that those send a break or some control > sequence. od -c > might show you what is being output. > > -- > Bruce Momjian| > http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) > 359-1001 > + If your life is a hard drive, | 13 Roberts > Road > + Christ can be your backup.| Newtown > Square, Pennsylvania 19073 > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Function keys cause psql to segfault
I did 'strace psql dbname' and this was the output after hitting F1: read(0, "\33", 1) = 1 read(0, "O", 1) = 1 read(0, "P", 1) = 1 --- SIGSEGV (Segmentation fault) @ 0 (0) --- +++ killed by SIGSEGV +++ CSN --- Tom Lane <[EMAIL PROTECTED]> wrote: > Bruce Momjian writes: > > CSN wrote: > >> If I'm in psql (via putty, from WinXP to Redhat) > and > >> hit F1-4 (F5+ just display a ~), psql will > >> segmentation fault and exit. > > > My guess is that those send a break or some > control sequence. od -c > > might show you what is being output. > > Try watching the psql process with strace in another > terminal window. > If Bruce's theory is correct (and it sounds good to > me) then you should > be able to see a signal being delivered to psql. > > regards, tom lane > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Function keys cause psql to segfault
Hmm, all I could think of was perl and php - hitting F1-4 just caused these chars to be displayed (the interpreters didn't exit): ^[OP^[OQ^[OR^[OS CSN --- Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian writes: > > > CSN wrote: > > >> If I'm in psql (via putty, from WinXP to > Redhat) and > > >> hit F1-4 (F5+ just display a ~), psql will > > >> segmentation fault and exit. > > > > > My guess is that those send a break or some > control sequence. od -c > > > might show you what is being output. > > > > Try watching the psql process with strace in > another terminal window. > > If Bruce's theory is correct (and it sounds good > to me) then you should > > be able to see a signal being delivered to psql. > > Also, try the function keys in another command-line > application and see > if that exits too. > > -- > Bruce Momjian| > http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) > 359-1001 > + If your life is a hard drive, | 13 Roberts > Road > + Christ can be your backup.| Newtown > Square, Pennsylvania 19073 > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function keys cause psql to segfault
It looks like I had readline 4.3 installed. I just installed readline 5.0 - the F1-4 keys still cause psql to segfault. (AFAIK I don't need to recompile postgres for psql to use the newly installed readline). CSN --- Tom Lane <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > I did 'strace psql dbname' and this was the output > > after hitting F1: > > > read(0, "\33", 1) = 1 > > read(0, "O", 1) = 1 > > read(0, "P", 1) = 1 > > --- SIGSEGV (Segmentation fault) @ 0 (0) --- > > Hmm ... I don't have an F1 key, but I typed > escape-O-P at a psql > running under Linux, and got this: > > Process 28978 attached - interrupt to quit > read(0, "\33", 1) = 1 > read(0, "O", 1) = 1 > read(0, "P", 1) = 1 > write(2, "\7", 1) = 1 > rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 > read(0, > > So it seems fine here. I'm wondering if there's > something broken > about your machine's readline library. > > regards, tom lane > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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] Function keys cause psql to segfault
Hmm, in putty (Terminal->Keyboard) I changed "the function keys and keypad" from "ESC[n~" to "Linux". Hitting F1-5 in psql outputs "ABCDE" - no segfaults! Setting it to "Xterm R6" also results in function keys 1-4 causing segfaults (there are also options for "VT400", "VT100+", and "SCO" - haven't tried those). Thanks, CSN --- Klint Gore <[EMAIL PROTECTED]> wrote: > On Sun, 25 Sep 2005 20:00:03 -0700 (PDT), CSN > <[EMAIL PROTECTED]> wrote: > > > > I did 'strace psql dbname' and this was the output > > after hitting F1: > > > > read(0, "\33", 1) = 1 > > read(0, "O", 1) = 1 > > read(0, "P", 1) = 1 > > --- SIGSEGV (Segmentation fault) @ 0 (0) --- > > +++ killed by SIGSEGV +++ > > esc O P is PF1 on a VT100/VT200. Does it make any > difference if you > change the terminal emulation in putty or the term > setting in redhat? > > klint. > > +---+-+ > : Klint Gore: "Non > rhyming: > : EMail : [EMAIL PROTECTED] : slang - > the: > : Snail : A.B.R.I.: > possibilities : > : Mail University of New England : are > useless" : > : Armidale NSW 2351 Australia : L.J.J. > : > : Fax : +61 2 6772 5376 : > : > +---+-+ > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Just so I know (and am armed ;) ), are there any new comparable features in MySQL 5.0 that aren't in PostgreSQL up to the forthcoming 8.1? AFAIK, PG just lacks updatable views (which are on the TODO). MySQL 5.0 new features http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
I'm not sure what XA (distributed transactions) is - is that something that can be achieved with Slony? CSN --- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: > > Just so I know (and am armed ;) ), are there any > new > > comparable features in MySQL 5.0 that aren't in > > PostgreSQL up to the forthcoming 8.1? AFAIK, PG > just > > lacks updatable views (which are on the TODO). > > > > MySQL 5.0 new features > > > http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html > > Well "IF" they are being completely honest, we don't > have XA > and we don't have an "instance manager" but of > course who really needs > one? > > Sincerely, > > Joshua D. Drake > > > > > > Thanks, > > CSN > > > > > > > > __ > > Yahoo! Mail - PC Magazine Editors' Choice 2005 > > http://mail.yahoo.com > > > > ---(end of > broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > -- > Your PostgreSQL solutions company - Command Prompt, > Inc. 1.800.492.2240 > PostgreSQL Replication, Consulting, Custom > Programming, 24x7 support > Managed Services, Shared and Dedicated Hosting > Co-Authors: plPHP, plPerlNG - > http://www.commandprompt.com/ > > > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL Gotchas
> On 10/6/05, Aly S.P Dharshi wrote: > > http://sql-info.de/postgresql/postgres-gotchas.html > > Any comments from folks on the list ? - It's a lot shorter than MySQL's gotchas list. - 8 of the 13 are for versions of PostgreSQL <= 8.1 - Of the remaining, I consider "select as" to be really trivial (and it appears a work-around can be hacked). - lowercase folding. I DO sometimes wish I could use fieldID, etc. without quoting it. - I've never found count(*) to be slow. - I don't know enough about the "UNICODE means "UTF-8"" and "RANDOM() failures" to comment. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
--- Scott Marlowe <[EMAIL PROTECTED]> wrote: > > Federated Storage Engine: Allows MySQL to access > tables in other > servers like they are here. No real direct > equivalent in PostgreSQL, > but dblink provides similar functionality. Would that be possible with table partitions? Or Slony? CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
--- Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Wed, 2005-10-05 at 23:41, Tom Lane wrote: > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > > On Wed, 2005-10-05 at 18:37 -0700, CSN wrote: > > >> Just so I know (and am armed ;) ), are there > any new > > >> comparable features in MySQL 5.0 that aren't in > > >> PostgreSQL up to the forthcoming 8.1? AFAIK, PG > just > > >> lacks updatable views (which are on the TODO). > > >> > > >> MySQL 5.0 new features > > >> > http://dev.mysql.com/doc/mysql/en/mysql-5-0-nutshell.html > > > > > Well "IF" they are being completely honest, we > don't have XA > > > and we don't have an "instance manager" but of > course who really needs > > > one? > > > > We don't have XA built into the backend, but if > I've been following the > > jdbc list accurately, there's fairly complete XA > support for the jdbc > > driver, which should be available in the 8.1 > release. > > > > More generally, it's worth making the point that a > lot of MySQL's "brand > > new in 5.0" features have been in Postgres for a > *long* time, and are > > therefore likely to be both more stable and > better-performing than > > MySQL's first cut at them. > > > > (BTW, it sure seems like MySQL 5.0 has been a > heckuva long time in > > getting to release status. Has anyone here been > following that > > process? Why's it been so painful?) > > I've been beta testing 5.0.xx releases and reporting > bugs. They're > pretty fast at fixing individual bugs. > > Not sure why it's taken so long, really. Maybe they > were trying to do > too much at once in one release? > > But what really bugs me is that some things that ARE > bugs simply aren't > getting fixed and probably won't. Specifically, > while mysql understands > fk references made at a table level, it simply > ignores, without error, > warning, or notice, fk references made in a column. > arg... Very > frustrating. If they just didn't support that > syntax it would be much > less bothersome, since I'd try it, get an error, and > try the other > syntax. Instead, I spent an afternoon trying to > figure out why it > wasn't doing ANYTHING when I declared an FK > reference at column level. > > Things like that are, sadly, kinda rampant in MySQL. > What's the difference between a fk at the table level vs. column level? The only fk's I've used are one column referencing another. CSN __ Yahoo! for Good Donate to the Hurricane Katrina relief effort. http://store.yahoo.com/redcross-donate3/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Gotchas
--- CSN <[EMAIL PROTECTED]> wrote: > - 8 of the 13 are for versions of PostgreSQL <= 8.1 Doh! - 8 of the 13 are for versions of PostgreSQL < 8.1! __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
Yep, I think the SQL spec says fold to uppercase. I'm not sure why PostgreSQL folds to lowercase instead, but if folding has to occur, I prefer lowercase. CSN --- "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > On Thu, Oct 06, 2005 at 12:54:43PM -0700, CSN wrote: > > - lowercase folding. I DO sometimes wish I could > use > > fieldID, etc. without quoting it. > > I believe that may be against ANSI SQL. In any case, > the only databases > I can think of that don't fold-case in some form are > MySQL and Access. > -- > Jim C. Nasby, Sr. Engineering Consultant > [EMAIL PROTECTED] > Pervasive Software http://pervasive.com > work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf > cell: 512-569-9461 > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
I had a similar experience speaking to the MySQL folks at (the last) COMDEX. After trying to get them to explain how their licenses work, I was even more confused (and two reps even gave conflicting info). CSN > Hi everyone, > > I've just got back from LinuxWorld in London and seeing this thread thought > I would share my experience of the MySQL stand - if you are of a delicate > dispostion, please look away now. I basically asked them straight up why I > should use MySQL instead of PostgreSQL and was quite surprised by the > result, mainly since it was not done on features but more on FUD. The basic > message was this: > > - MySQL is the most popular open source database, with over 6m > "enterprise" > installs, with a large company supporting it. PostgreSQL is run by a > very > small community of developers. > > - MySQL can be clustered (This was later retracted when I mentioned > I > needed something that would work on large tables, as apparently > their > clustering only works in RAM and so will fail on large queries and > queries > that use a lot of joins). > > - All the companies that have tried to operate by selling PostgreSQL > support > services have gone bankrupt, except for EnterpriseDB. > > - PostgreSQL doesn't have row level locking. > > And this last comment really took the biscuit - I really hope that the none > of the core team read this and decide to throw in the towel: > > "MySQL has the biggest collection of database experts... Open source > people > don't know how to write databases" > > So all in all, to say I was upset by some of these comments was an > understatement. To all the people I spoke to on the PostgreSQL stand, I hope > I did it in a way that made them feel empowered to go and try the PostgreSQL > for their own applications by mentioning its benefits, and not by spreading > FUD about its competition. > > > Mark. > > > WebBased Ltd > South West Technology Centre > Tamar Science Park > Plymouth > PL6 8BT __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
> On 10/6/2005 4:37 AM, Tzvetan Tzankov wrote: > > > They have collation and multiple characterset per table and etc. which actually is from 4.1 (not new in 5.0), and postgresql have only one collation per database cluster :-( > > Otherwise I think their features are all there, but cannot be used togather most of them (you can have foreign key, but not using fulltext ...) > > > AFAIK MySQL's fulltext indexing is only supported on MyIsam tables, so if you want to use it, you lose ACID, hot backup and a couple other nice things entirely for that part of your data. Many MySQL users still believe that the pluggable storage engine design is an advantage ... I think one storage engine that supports the full feature set is better. > > Jan I agree - MySQL really has a confusing array of different database engines: # MyISAM # MERGE # ISAM # HEAP # InnoDB # BDB or BerkeleyDB Tables # Example # Archive # Federated # CSV # Blackhole # NDB Cluster http://dev.mysql.com/doc/mysql/en/storage-engines.html CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Duplicate primary keys/rows
This is weird. I set up a table with a serial id field and created a primary key on it. Then I imported data. Running an app against it, I got periodic errors stating "duplicate key violates unique constraint "pkey_table1." Looking through the table (with phppgadmin), there are duplicate rows: id|f1|f2|f3|f4 585|c|a|e|f 586|a|b|c|d 586|a|b|c|d 587|g|e|r|z However: select * from table1 where id=586; 586|a|b|c|d Yet: select * from table1 where id>=585 and id<=587; 585|c|a|e|f 586|a|b|c|d 586|a|b|c|d 587|g|e|r|z Wow, how is this possible? I'm using PG 8.0.3 on Windows XP. This computer has been crashing repeatedly lately, if that could be blamed (bad memory? hard disk? I haven't quite figured out why.) Using phppgadmin, I was able to delete one of the duplicate rows (there are several) - don't know how it does that - maybe using OIDs? CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle buys Innobase
Look what somebody suggested! --- If the worst happens and Oracle tries to squash InnoDB, there may already be such an alternative out there. I wonder what it would take to add (and optimize) Postgres storage engine support to MySQL? I don't know exactly how current versions of MySQL and Postgres maesure up performance-wise, but PgSQL seems to have made steady progress on performance improvements. Maybe this is a crazy idea, I don't know how technically or legally feasible it is, but I really like the idea of the two open-source communities uniting to battle Oracle. http://jeremy.zawodny.com/blog/archives/005490.html#comment-21233 __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(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] Oracle buys Innobase
Yep, those were two of my very first questions too. ;) CSN --- "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: > > Stupid question, but what does MySQL bring to the > equation? Why not just > use PostgreSQL in the first place? > > On Sun, 9 Oct 2005, CSN wrote: > > > Look what somebody suggested! > > > > --- > > > > If the worst happens and Oracle tries to squash > > InnoDB, there may already be such an alternative > out > > there. > > > > I wonder what it would take to add (and optimize) > > Postgres storage engine support to MySQL? I don't > know > > exactly how current versions of MySQL and Postgres > > maesure up performance-wise, but PgSQL seems to > have > > made steady progress on performance improvements. > > > > Maybe this is a crazy idea, I don't know how > > technically or legally feasible it is, but I > really > > like the idea of the two open-source communities > > uniting to battle Oracle. > > > > > http://jeremy.zawodny.com/blog/archives/005490.html#comment-21233 > > > > > > > > __ > > Start your day with Yahoo! - Make it your home > page! > > http://www.yahoo.com/r/hs > > > > ---(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 > > > > > Marc G. Fournier Hub.Org Networking > Services (http://www.hub.org) > Email: [EMAIL PROTECTED] Yahoo!: yscrappy > ICQ: 7615664 > __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Duplicate primary keys/rows
--- Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sun, Oct 09, 2005 at 12:46:51PM -0700, CSN wrote: > > select * from table1 where id=586; > > 586|a|b|c|d > > Do you get different results from the following > queries? > > SET enable_seqscan TO on; > SET enable_indexscan TO off; > SELECT * FROM table1 WHERE id = 586; This returns 2 rows. > SET enable_seqscan TO off; > SET enable_indexscan TO on; > SELECT * FROM table1 WHERE id = 586; This returns 1 row. > > Yet: > > select * from table1 where id>=585 and id<=587; > > 585|c|a|e|f > > 586|a|b|c|d > > 586|a|b|c|d > > 587|g|e|r|z > > What's the output of the following query? > > RESET enable_seqscan; > RESET enable_indexscan; > > SELECT oid, ctid, xmin, cmin, xmax, cmax, * > FROM table1 > WHERE id >= 585 AND id <= 587; oid | ctid| xmin | cmin | xmax | cmax | id +---+-+--+-+--+- 125465 | (3143,78) | 1664385 |0 | 1664386 |2 | 984 125466 | (2745,50) | 1481020 |0 | 1682425 |2 | 985 125466 | (2672,11) | 1445346 |0 | 1481020 |0 | 985 125467 | (3159,28) | 1671875 |0 | 1671876 |2 | 986 (I'm using a different duplicate row - 985. I deleted 586's duplicate.) Is this a problem with the index? Would rebuilding them fix this problem? I'm still curious why this happened, and somewhat troubled that something like this can happen. Thanks for your help, CSN > If you get the error 'column "oid" does not exist' > then you've > created the table without oids, so just omit oid > from the select > list: > > SELECT ctid, xmin, cmin, xmax, cmax, * > FROM table1 > WHERE id >= 585 AND id <= 587; > > > Wow, how is this possible? I'm using PG 8.0.3 on > > Windows XP. This computer has been crashing > repeatedly > > lately, if that could be blamed (bad memory? hard > > disk? I haven't quite figured out why.) > > Faulty hardware is one possibile explanation. > > -- > Michael Fuhr > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate primary keys/rows
I don't have a compiler on this machine. If somebody can point me to a copy of pg_filedump for Windows (I didn't see any using Google) I'd be happy to use it. Or perhaps I could compile it under cygwin. The hard drive is a Western Digital 200GB JD (SATA), if that can be used to determine how badly it lies. ;) Thanks, CSN --- Tom Lane <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > oid | ctid| xmin | cmin | xmax | > cmax | id > > > +---+-+--+-+--+- > > 125466 | (2672,11) | 1445346 |0 | 1481020 | > 0 | 985 > > 125466 | (2745,50) | 1481020 |0 | 1682425 | > 2 | 985 > > Hmm. The fact that the dup rows have the same OID > indicates pretty > strongly that they are actually two versions of the > same row, and > not two independently inserted rows. Furthermore we > can see that xact > 1481020 deleted the first version and inserted the > second (note I took > the liberty of rearranging your output to make the > rows appear in > chronological order). > > So the index hasn't screwed up, exactly; the problem > is that both rows > appear as good at the same time. But why? > > It's really highly annoying that we can't see the > contents of the > infomasks for the rows. Would you be willing to > grab a copy of > pg_filedump and dump out these two data pages so we > can see the > complete tuple headers? > > (If you don't have a compiler then you'd need to > find a precompiled > copy of pg_filedump for Windows. I don't know if > anyone's made one > available.) > > Given that you say the machine has been crashing, my > bet is that a crash > caused the loss of pg_clog status for xid 1481020 at > a time when > 2745,50's xmin had been marked committed good, but > 2672,11's xmax had > not been similarly marked. We have sufficient > defenses against this > sort of thing *if the disk drive does not lie about > write complete*. > (Unfortunately the vast majority of el-cheapo PCs > are configured to lie > with abandon, which means that we can't guarantee > data consistency > across power failures on such hardware.) It'd be > nice to get direct > confirmation of that theory though. > > regards, tom lane > __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Dumb question about serial's upper limit
If integer's range is -2147483648 to +2147483647, why is serial's range only 1 to 2147483647 instead of 1 to about 4294967294? CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Dumb question about serial's upper limit
--- Tom Lane <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > If integer's range is -2147483648 to +2147483647, > why > > is serial's range only 1 to 2147483647 instead of > 1 to > > about 4294967294? > > How are you going to stuff 4294967294 into an > integer field, which as > you just stated has an upper limit of 2147483647? > > If we had an unsigned int type, we could use it for > serial and get > that result, but we do not. > > regards, tom lane > I was thinking about the types in the C code behind PostgreSQL, rather than types in PG itself. Been a long time since I coded in C but I thought it had unsigned ints and maybe data types could be mapped as so (pardon my ignorance about C/PG's inner workings): PG int => C signed int PG serial => C unsigned int Anyhow, was just something I was curious about. CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] more than one row returned by a subquery used as an expression
I'm trying to get this query to work: update sectors set companies =(select companies from industries where sector_id =sectors.id); PG returns: ERROR: more than one row returned by a subquery used as an expression Column companies is just a count of rows in the related companies table. Queries like this worked: update industries set companies =(select count(id) from companies where industry_id =industries.id); I know I could do a join in the subselect, but I'm curious why this doesn't work. If I do the subselect by itself, it looks like all of the rows from the industries table are return (I expected an error). Is this the "implicit FROM" gotcha? CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Oracle buys Innobase
There are some articles on eweek about this: Oracle Finds the Flaw in MySQL's Business Plan http://www.eweek.com/article2/0,1895,1869989,00.asp "This is what Oracle says in its release: "InnoDB's contractual relationship with MySQL comes up for renewal next year. Oracle fully expects to negotiate an extension of that relationship." This is what Lubet, former Oracle sales mistress, has to say about that: "I'm pretty sure, as an ex-Oracle employee, that the sentence in the release about 'We'll certainly be happy to renew the contract,' that it was written by Larry and that he was laughing out loud as he [dictated it]." __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Anybody using PostGIS?
I've been meaning to try out PostGIS and see what it is capable of. Is anybody using it? Do you have accompanying URLs? Thanks, CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate primary keys/rows
I don't know if I'm going to get a copy of pg_filedump. What's the best way to fix this - dump then restore? CSN --- Tom Lane <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > oid | ctid| xmin | cmin | xmax | > cmax | id > > > +---+-+--+-+--+- > > 125466 | (2672,11) | 1445346 |0 | 1481020 | > 0 | 985 > > 125466 | (2745,50) | 1481020 |0 | 1682425 | > 2 | 985 > > Hmm. The fact that the dup rows have the same OID > indicates pretty > strongly that they are actually two versions of the > same row, and > not two independently inserted rows. Furthermore we > can see that xact > 1481020 deleted the first version and inserted the > second (note I took > the liberty of rearranging your output to make the > rows appear in > chronological order). > > So the index hasn't screwed up, exactly; the problem > is that both rows > appear as good at the same time. But why? > > It's really highly annoying that we can't see the > contents of the > infomasks for the rows. Would you be willing to > grab a copy of > pg_filedump and dump out these two data pages so we > can see the > complete tuple headers? > > (If you don't have a compiler then you'd need to > find a precompiled > copy of pg_filedump for Windows. I don't know if > anyone's made one > available.) > > Given that you say the machine has been crashing, my > bet is that a crash > caused the loss of pg_clog status for xid 1481020 at > a time when > 2745,50's xmin had been marked committed good, but > 2672,11's xmax had > not been similarly marked. We have sufficient > defenses against this > sort of thing *if the disk drive does not lie about > write complete*. > (Unfortunately the vast majority of el-cheapo PCs > are configured to lie > with abandon, which means that we can't guarantee > data consistency > across power failures on such hardware.) It'd be > nice to get direct > confirmation of that theory though. > > regards, tom lane > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] update trigger not working
I'm trying to set up a trigger that simply updates a field's corresponding timestamp to now() whenever the field is updated. But it's not working. Trying to debug, I commented out the inner IF and END and the log seemed to indicate infinite recursion occurred. My next guess is that perhaps NULL's in OLD.stuff is causing the IF to behave other than what I expect. Thanks for any help! CSN CREATE or REPLACE function update_ts() returns trigger as $end$ BEGIN IF (TG_OP='UPDATE') THEN IF (OLD.stuff != NEW.stuff) THEN UPDATE table1 set stuff_ts=now() where id=NEW.id; END IF; END IF; RETURN NULL; END; $end$ language plpgsql; CREATE TRIGGER update_ts AFTER UPDATE ON table1 FOR EACH ROW EXECUTE PROCEDURE update_ts(); __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] NULL != text ?
I was trying this: IF (OLD.value != NEW.value) THEN -- END IF; and couldn't get the condition to evaluate to true at all if OLD.value was NULL. I also tried: IF (OLD.value NOT LIKE NEW.value) THEN -- END IF; with the same result. But this works: IF ((OLD.value is NULL and NEW.value is NOT NULL) or (OLD.value != NEW.value)) THEN -- END IF; So, does NULL != 'abc' always evaluate to false? The manual (http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html) states don't compare NULL values using =, but nothing about using != CSN __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(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] NULL != text ?
BTW, it (the SQL spec I presume) has always seemed contradictory to me that you can't do: select * from table where field=null; but can do: update table set field=null; (as opposed to 'update table set field to null' or similar). CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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] How much slower are numerics?
Another thing I've always wondered about ;), as I use numerics far more than floats. From the docs: "However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types" How much slower are numerics? And why (I guess it has to do with potentially different sizes)? Thanks, CSN __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump with low priority?
nice comes to mind: nice pg_dump ... On Sat, 2005-10-22 at 07:39, Bryan Field-Elliot wrote: > We have a huge database which must be backed up every day with > pg_dump. The problem is, it takes around half an hour to produce the > dump file, and all other processes on the same box are starved for > cycles (presumably due to I/O) during the dump. It's not just an > inconvenience, it's now evolved into a serious problem that needs to > be addressed. > > Is there any mechanism for running pg_dump with a lower priority? I > don't mind if the backup takes two hours instead of half an hour, as > long as other processes were getting their fair share of cycles. __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
Here are some apparent problems with MySQL 5.0: - Concurrent ALTER TABLE - Replicated Session Variables and Concurrent ALTER TABLE - BIT indexing that [doesn't] actually uses a BIT! - SELECT * FROM FOO WHERE ID IN ( SELECT FOO_ID FROM BAR ) [doesn't use index] http://www.feedblog.org/2005/10/whats_next_afte.html __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] trapping errors in plpgsql?
I have a table like so: id|username|email with unique indices on username and email. In a plpgsql function if an insert fails because of a duplicate on one of those fields, is it possible to trap the error, figure out which unique fields it applies to, and raise a custom error message? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] sequence aliases?
I'm checking out Ruby on Rails and there's a tutorial about using Postgresql views and making them updateable (http://wiki.rubyonrails.com/rails/pages/HowtoUsePostgresViewsAsTables). The tutorial suggests renaming the sequence for the table to coincide with the view so that Rails can automatically access the sequence. I don't like this as it may break other things. Is there a way to create an alias or something (e.g. quotes_seq_id points to quotes_table_seq_id), or is there a better way? Thanks, CSN __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] sequence aliases?
--- Martijn van Oosterhout wrote:
> On Sun, Nov 06, 2005 at 12:15:45AM -0500, Greg Stark
> wrote:
> >
> > Tom Lane <[EMAIL PROTECTED]> writes:
> >
> > > My advice to the Rails people would be to fix
> whatever it is in their
> > > code that is assuming a particular sequence
> name, or indeed assuming
> > > a sequence at all...
> >
> > Well how else do you find the id of the last
> inserted record without assuming
> > a sequence?
>
> I suppose using something like:
>
> select currval( pg_get_serial_sequence(
> 'table','col' ) );
I like that better than the current 'table_col_seq'.
Perhaps just currval('table', 'col'). Or perhaps
tables could be made to have a default sequence (one
that's associated with the primary key).
CSN
>
> That avoids hardcoding the sequence id. Not assuming
> a sequences at all
> may be trickier.
>
> Have a nice day,
> --
> Martijn van Oosterhout
> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95%
> perspiration. A patent is a
> > tool for doing 5% of the work and then sitting
> around waiting for someone
> > else to do the other 95% so you can sue them.
>
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] postgres: unknown hard error
I'm using PG 8.0.x on Windows XP Pro and a dialog popped up with "Postgres: unknown hard error". After acknowledging it, the system became somewhat unresponsive and needed a reboot. There was nothing in event viewer (I guess PG logs there - it's configured to log to 'stderr'). Any way to further diagnose this? thanks csn __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Does PG Support Unicode on Windows?
Is there any truth to what this guy is saying? > > On the other hand, Postgresql claims that "Windows does not support > > Unicode" and you can't have Unicode fields on postgresql on Windows. > > This is a big mistake. See: > > http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html > > > What do you mean "a big mistake"? By Microsoft? Or PostgreSQL? > > > > The "Big mistake" is in the Postgresql FAQ in saying that Windows > > doesn't support Unicode. XP supports Unicode very well indeed. > > The FAQ I referenced prattles on about Slovenian code pages, > > of all things. Windows "Code pages" have been superseded by > > Unicode. If you go to http://msdn.microsoft.com and search > > for "Unicode" you get an eyeful. > > > > Having defended the undefendable, however, I'd like to know if > > postgresql really still doesn't support Unicode on windows, as > > I plan to develop on WindowsXP and deploy on Linux. > > > > Warren Seltzer Thread here: http://www.ruby-forum.com/topic/3690#new csn __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] not null error in trigger on unrelated column
I have a members table and an items table. Every time items.member_id or items.active gets changed, members.items_submitted and members.items_approved gets updated by a trigger on items. I added an "admin" column to members, and now this happens: => update items set active = false where member_id=38; ERROR: null value in column "admin" violates not-null constraint CONTEXT: SQL statement "update members set items_approved=items_approved-1 where id= $1 " PL/pgSQL function "update_member_item_counts" line 54 at SQL statement The relevant part of the (after) trigger function on items is: ELSIF OLD.active is true and NEW.active is false then update members set items_approved=items_approved-1 where id=NEW.member_id; END IF; Is it necessary to drop and recreate triggers and/or corresponding functions after changing a table's schema? I don't know how something is trying to set members.admin to null (table members has no triggers). thanks csn Postgresql 8.0.x __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(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] not null error in trigger on unrelated column
I removed the not null contraint on members.admin and "update items set active = false where member_id=38" results in the count columns in members getting updated AND members.admin getting set to NULL. Really bizarre. I dropped the trigger function and the trigger, then recreated both, and now they both appear to function properly. I don't remember seeing anything in the docs stating that this is necessary, so I guess perhaps it's a bug? csn --- CSN <[EMAIL PROTECTED]> wrote: > I have a members table and an items table. Every time items.member_id or > items.active gets > changed, members.items_submitted and members.items_approved gets updated by a > trigger on items. > I > added an "admin" column to members, and now this happens: > > => update items set active = false where member_id=38; > ERROR: null value in column "admin" violates not-null constraint > CONTEXT: SQL statement "update members set items_approved=items_approved-1 > where id= $1 " > PL/pgSQL function "update_member_item_counts" line 54 at SQL statement > > The relevant part of the (after) trigger function on items is: > > ELSIF OLD.active is true and NEW.active is false then > update members set > items_approved=items_approved-1 > where id=NEW.member_id; > END IF; > > Is it necessary to drop and recreate triggers and/or corresponding functions > after changing a > table's schema? I don't know how something is trying to set members.admin to > null (table members > has no triggers). > > thanks > csn > Postgresql 8.0.x > > > > __ > Yahoo! FareChase: Search multiple travel sites in one click. > http://farechase.yahoo.com > __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Selecting from two unrelated tables
I have two tables: items: id, title, added, ... news: id, headline, datetime, ... I'd like to select the latest 25 combined records from both tables. Is there a way to do this using just select? Thanks, csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql rising
PostgreSQL doesn't have any booth babes? ;P csn > On 09/20/06 16:38, Philip Hallstrom wrote: > [snip] > > I think that description is false. At a certain point in the > > management hierarchy, the only way anyone has the ability to > > evaluate something is on the basis of > > > > - if there is someone they can sue. > > - how attractive the sales rep is. > > Back in my youth, working for the family business (roofing/siding > distributor, not many women, fewer attractive women), the most > successful salespeople were always... young attractive women. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Dump all databases to corresponding files
Anybody know of a script that dumps all databases into corresponding dump files, e.g. $ ./dump template0 -> template0.sql template1 -> template1.sql db1 -> db1.sql db2 -> db2.sql ... Also, would this approach add up to equal the output of pg_dumpall, or does pg_dumpall dump additional things (if so, please describe how they'd also be dumped)? Thanks, csn Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates (http://voice.yahoo.com) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] copy - fields enclosed by, ignore x lines
Any chance of changing \copy and COPY to allow specifying what the fields are enclosed by (such as quotes) and to ignore the first x number of lines? I have data like below and don't know of an easy way to finesse it for importing (a simple regexp would remove quotes, but I just got tripped up on commas *within* values). "field1","field2","field3" "val1","val2","val3" __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] copy - fields enclosed by, ignore x lines
Ah, looks like "enclosed by" will be in PG 8 :). Is "QUOTE [ AS ] 'quote'" for the "enclosed by" character? Ignore x lines would be nice, but not as big of a deal. http://developer.postgresql.org/docs/postgres/sql-copy.html --- CSN <[EMAIL PROTECTED]> wrote: > Any chance of changing \copy and COPY to allow > specifying what the fields are enclosed by (such as > quotes) and to ignore the first x number of lines? I > have data like below and don't know of an easy way > to > finesse it for importing (a simple regexp would > remove > quotes, but I just got tripped up on commas *within* > values). > > "field1","field2","field3" > "val1","val2","val3" > > > > __ > Do you Yahoo!? > Yahoo! Mail Address AutoComplete - You start. We > finish. > http://promotions.yahoo.com/new_mail > ___ Do you Yahoo!? Express yourself with Y! Messenger! Free. Download now. http://messenger.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Derived tables?
Just wondering - does PG support derived tables? I'm not really sure what the difference is between them and subqueries. http://www.mysql.com/news-and-events/press-release/release_2004_32.html http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics_printversion.asp __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
