Re: [GENERAL] plperl crashing backend
At 8:07 PM -0400 9/3/2000, Tom Lane wrote:
>Michael Blakeley <[EMAIL PROTECTED]> writes:
>> I'm running postgresql 7.0.1 on Solaris 2.6, with the latest patch
>> cluster. I've compiled pg and Perl 5.6.0 with gcc 2.9.5-2.
>
>Hmm. Your trace shows
>
>> open("/usr/local/pgsql/lib/plperl.so", O_RDONLY) = 40
>>
>>open("/usr/local/lib/perl5/5.6.0/sun4-solaris/auto/Opcode/Opcode.so",
>>O_RDONLY) = 40
>> open("/usr/lib/libperl.so", O_RDONLY) = 40
>
>I'd have expected libperl to be opened from somewhere in the same
>general area as the Opcode module, say
>/usr/local/lib/perl5/5.6.0/sun4-solaris/CORE/libperl.so
>if the shape of the Perl install tree hasn't changed recently.
>Perhaps you are pulling in some older, incompatible release of Perl
>that came with your OS?
No, the postgres backend was trying to load libperl.so from /usr/lib,
so I put a symbolic link there. It's all 5.6.0.
-- Mike
[GENERAL] plperl crashing backend
I'm running postgresql 7.0.1 on Solaris 2.6, with the latest patch
cluster. I've compiled pg and Perl 5.6.0 with gcc 2.9.5-2.
I want to do some work with plperl fuctions, for text parsing. So I
run psql and do:
db=# CREATE FUNCTION testfunction(VARCHAR) RETURNS VARCHAR AS
db-# 'return "foo"' LANGUAGE 'plperl';
CREATE
db=# SELECT testfunction('foo');
...after a pause, I see:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
truss-ing the backend process while this is happening doesn't show
much. Here it is, though:
9973/1: recv(6, 0x0017D8F0, 8192, 0)(sleeping...)
9973/1: recv(6, " Q S E L E C T t e s t".., 8192, 0) = 27
9973/1: time() = 968015516
9973/1: open("/usr/local/pgsql/data/base/db/pg_aggregate", O_RDWR)
= 35
9973/1: lseek(35, 0, SEEK_END) = 8192
9973/1: lseek(13, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: open("/usr/local/pgsql/data/base/db/pg_aggregate_name_type
_index", O_RDWR) = 36
9973/1: lseek(36, 0, SEEK_END) = 16384
9973/1: lseek(36, 0, SEEK_SET) = 0
9973/1: read(36, "\0\b1FF01FF0 \0\005 1 b".., 8192)= 8192
9973/1: lseek(36, 8192, SEEK_SET) = 8192
9973/1: read(36, "\0BC17801FF0 \0 ?8080 0".., 8192)= 8192
9973/1: lseek(35, 0, SEEK_END) = 8192
9973/1: lseek(35, 0, SEEK_SET) = 0
9973/1: read(35, "\0BC\r p \0 \0 ? 80 m".., 8192)= 8192
9973/1: lseek(13, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: open("/usr/local/pgsql/data/base/db/pg_proc_proname_narg_t
ype_index", O_RDWR) = 37
9973/1: lseek(37, 0, SEEK_END) = 229376
9973/1: lseek(37, 0, SEEK_SET) = 0
9973/1: read(37, "\0\b1FF01FF0 \0\005 1 b".., 8192)= 8192
9973/1: lseek(37, 24576, SEEK_SET) = 24576
9973/1: read(37, "\0 p14901FF0 \0 ?\080 p".., 8192)= 8192
9973/1: lseek(37, 139264, SEEK_SET) = 139264
9973/1: read(37, "\0C4\v `1FF0 \0 ?\080 p".., 8192)= 8192
9973/1: lseek(33, 204800, SEEK_SET) = 204800
9973/1: read(33, "\0A80198 \0 \0 >8080BD".., 8192)= 8192
9973/1: lseek(25, 8192, SEEK_SET) = 8192
9973/1: read(25, "01 01 ` \0 \0 ? 80 m".., 8192)= 8192
9973/1: lseek(34, 49152, SEEK_SET) = 49152
9973/1: read(34, "039011D01FF0 \0 ?C08010".., 8192)= 8192
9973/1: open("/usr/local/pgsql/data/base/db/pg_language", O_RDWR)
= 38
9973/1: lseek(38, 0, SEEK_END) = 8192
9973/1: lseek(13, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: open("/usr/local/pgsql/data/base/db/pg_language_oid_index"
, O_RDWR) = 39
9973/1: lseek(39, 0, SEEK_END) = 16384
9973/1: lseek(39, 0, SEEK_SET) = 0
9973/1: read(39, "\0\b1FF01FF0 \0\005 1 b".., 8192)= 8192
9973/1: lseek(39, 8192, SEEK_SET) = 8192
9973/1: read(39, "\0181FB01FF0 \0 ?C08010".., 8192)= 8192
9973/1: lseek(38, 0, SEEK_SET) = 0
9973/1: read(38, "\0181EA8 \0 \0 ? `80 O".., 8192)= 8192
9973/1: stat("/usr/local/pgsql/lib/plperl.so", 0xEFFFDD98) = 0
9973/1: open("/usr/local/pgsql/lib/plperl.so", O_RDONLY) = 40
9973/1: fstat(40, 0xEFFFDB7C) = 0
9973/1: mmap(0x, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE, 40, 0)
= 0xEF59
9973/1: mmap(0x, 90112, PROT_READ|PROT_EXEC,
MAP_PRIVATE, 40, 0)
= 0xED3E
9973/1: mmap(0xED3F2000,
[GENERAL] Re: Stripping a prefix
> Date: Fri, 25 Aug 2000 09:35:43 -0600 > From: Bruce Guenter <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Stripping a prefix > > Is there a function available to strip a string from the start of a > string? trim and ltrim can strip any occurrence of a set of characters > from the start of a string, but I want to be able to strip an exact > string. In particular, if the string starts with "www." (including the > dot), then it should be stripped. trim(leading 'www.' from foo) -- Mike
Re: [GENERAL] regex back-references?
At 4:26 PM -0400 8/21/2000, Tom Lane wrote: >Michael Blakeley <[EMAIL PROTECTED]> writes: >> Do I need to tell postgres to rebuild pg_language, perhaps? > >See the createlang utility script. PL languages aren't installed >by default (due to possibly-overzealous concern about security). Thanks - that did it. RFE: change the error message ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. to read ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. If you have compiled postgres with 'plperl' support, you may need to run the 'createlang' utility. thanks, -- Mike
Re: [GENERAL] regex back-references?
At 1:30 AM -0400 8/21/2000, Tom Lane wrote: >Michael Blakeley <[EMAIL PROTECTED]> writes: >> Does postgresql support regex back-references? > >There's no such function at the SQL level, AFAIR. > >I'd recommend writing a function in either plperl or pltcl, according >to your taste. Both offer pretty much all the string-bashing >functionality you could possibly want ... Sounds like a plan - but I can't seem to create a function - can anyone tell me what I'm doing wrong? A brief check of the archives didn't turn up any hints... Solaris 2.6, Postgresql 7.0.1, Perl 5.6, gcc 2.95.2. After compiling and installing with ./configure --with-perl --without-tcl psql=# CREATE FUNCTION pltest(VARCHAR) RETURNS VARCHAR AS 'return $_[0]' LANGUAGE 'plperl'; ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. Can I check for the presence of a .so somewhere? It seems to have installed a couple in $PGSQL/lib: -rwxr--r-- 1 root other 295784 Aug 21 12:47 plperl.so* -rw-r--r-- 1 root other 690992 Aug 21 12:47 plpgsql.so Also, psql=# select * from pg_language; lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler --+-+--+---+- internal | f | f| 0 | n/a C| f | f| 0 | /bin/cc sql | f | f| 0 | postgres (3 rows) Do I need to tell postgres to rebuild pg_language, perhaps? thanks, -- Mike
Re: [GENERAL] interval questions
At 10:21 PM -0500 6/1/2000, Ed Loehr wrote: >Michael Blakeley wrote: >> >> CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) ); >> >> I'm trying to find the average age of the records. I've gotten as far as: >> SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; >> >> Now, I need the DISTINCT ON(id), but that means I can't simply >>avg() the age: >> ERROR: Attribute events.id must be GROUPed or used in an >> aggregate function >> > >Interesting problem. Would this do it? > >select into temp_age id, sum(age(stamp)) as age_sum, count(id) >from EVENTS group by id; > >followed by > >select avg(age_sum/count) from temp_age; I oversimplified - I left out the outer join, which I was performing in the wrong (non-unique id) direction. I wanted to query for the age of ids that have had events (recently, but I'll omit that part). The following is a little closer to what I was trying to do: CREATE TABLE IDS (id varchar(16) primary key, created date); SELECT DISTINCT ON(id) avg(age(IDS.created))) FROM EVENTS WHERE id=IDS.id; Reversing the join gives me unique ids, and allowed me to leave out the DISTINCT ON clause. So avg() now works, and gives me the single number I was after. Like: SELECT AVG(AGE(created))) FROM IDS WHERE id=EVENTS.id; Thanks for the help - it wasn't until I explained the problem properly that I figured it out :-). -- Mike
[GENERAL] interval questions
I hope someone on the list can suggest a solution for me - given a table like CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) ); I'm trying to find the average age of the records. I've gotten as far as: SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; Now, I need the DISTINCT ON(id), but that means I can't simply avg() the age: ERROR: Attribute events.id must be GROUPed or used in an aggregate function Can anyone suggest a solution? I could do the averaging myself, except that the output is non-trivial to parse: 7 mons 6 10:29 2 mons 30 07:43:38 3 mons 4 09:50:56 (To be accurate, my code has to get the days in each month right, etc., and it feels like I'm reinventing the wheel there.) Thanks in advance for any suggestions. -- Mike
[GENERAL] group by week (ww), plus -S performance
I thought I'd pass along a work-around I came up with for the limits
in 'ww' support (7.0 final). Maybe this would be a useful example for
the docs? They're a little lean on date/time examples, IMO. So is the
new book.
Task:
Select a count of records from a table, grouped by the week of the
record. The table is something like
CREATE table EVENTS
(event varchar(128) not null, stamp datetime default now());
And I want the output to use human-readable dates, like
week| count
+---
2000-03-06 | 4
2000-03-13 | 5
2000-03-20 | 3
My immediate solution was something like
SELECT to_date(date_part('year',stamp)||'-'||date_part('week',stamp),
'-ww'),count(*) FROM EVENTS GROUP BY to_date;
but to_date() doesn't seem to support 'ww' format for text-to-date
translation (not documented, AFAIK).
The solution I eventually found was
SELECT to_date(date_part('year',stamp),'')-5+7*date_part('week',stamp)
as week,count(*) FROM EVENTS GROUP BY week ORDER BY week;
I haven't tested the '-5' kludge very extensively. It seems to work
ok, for the dates I tested in 2000. I'm sure it would run into
trouble with calendar-change weirdness pre-1900.
I'd also love to hear any suggestions for performance improvements -
it's cpu-bound on my system, and takes about 70 sec to run with
86,000 rows.
The query plan is
Aggregate (cost=9155.76..9584.66 rows=8578 width=20)
-> Group (cost=9155.76..9370.21 rows=85780 width=20)
-> Sort (cost=9155.76..9155.76 rows=85780 width=20)
-> Seq Scan on events (cost=0.00..2126.80 rows=85780 width=20)
The order-by clause doesn't seem to add much overhead - the query
plan is the same with or without it.
I'm running with "-i -N 64 -B 1024 -o '-F'", and I've tried up to '-S
8192' without seeing any noticeable improvement. At higher values,
performance actually went down by almost 50% - something to do with
shmem segment sizes? This is on Solaris 2.6, and I compiled PG7.0
using gcc 2.95.
The -S does reduce disk I/O, but I think that's only about 5% of the
work that's going on (90% user time). An index on 'stamp' doesn't
seem to help, either.
thanks,
-- Mike
