regex back-references?

Michael Blakeley <mike@blakeley.com> 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 ...

regards, tom lane

At 1:30 AM -0400 8/21/2000, Tom Lane wrote:

Michael Blakeley <mike@blakeley.com> 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

Michael Blakeley <mike@blakeley.com> 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).

regards, tom lane

At 4:26 PM -0400 8/21/2000, Tom Lane wrote:

Michael Blakeley <mike@blakeley.com> 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

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(0x00000000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE, 40, 0)
= 0xEF590000
9973/1: mmap(0x00000000, 90112, PROT_READ|PROT_EXEC,
MAP_PRIVATE, 40, 0)
= 0xED3E0000
9973/1: mmap(0xED3F2000, 9060,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVA
TE|MAP_FIXED, 40, 8192) = 0xED3F2000
9973/1: munmap(0xED3E4000, 57344) = 0
9973/1: memcntl(0xED3E0000, 6604, MC_ADVISE, 0x0003, 0, 0) = 0
9973/1: close(40) = 0
9973/1:
open("/usr/local/lib/perl5/5.6.0/sun4-solaris/auto/Opcode/Opcode
.so", O_RDONLY) = 40
9973/1: fstat(40, 0xEFFFDAAC) = 0
9973/1: mmap(0xEF590000, 8192, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_FIXE
D, 40, 0) = 0xEF590000
9973/1: mmap(0x00000000, 90112, PROT_READ|PROT_EXEC,
MAP_PRIVATE, 40, 0)
= 0xED3C0000
9973/1: mmap(0xED3D4000, 4216,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVA
TE|MAP_FIXED, 40, 16384) = 0xED3D4000
9973/1: munmap(0xED3C6000, 57344) = 0
9973/1: memcntl(0xED3C0000, 6240, MC_ADVISE, 0x0003, 0, 0) = 0
9973/1: close(40) = 0
9973/1: open("/usr/lib/libperl.so", O_RDONLY) = 40
9973/1: fstat(40, 0xEFFFDAAC) = 0
9973/1: mmap(0xEF590000, 8192, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_FIXE
D, 40, 0) = 0xEF590000
9973/1: mmap(0x00000000, 1171456, PROT_READ|PROT_EXEC, MAP_PRIVATE, 40,
0) = 0xED280000
9973/1: mmap(0xED38E000, 56028,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIV
ATE|MAP_FIXED, 40, 1040384) = 0xED38E000
9973/1: open("/dev/zero", O_RDONLY) = 41
9973/1: mmap(0xED39C000, 1536,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVA
TE|MAP_FIXED, 41, 0) = 0xED39C000
9973/1: munmap(0xED380000, 57344) = 0
9973/1: memcntl(0xED280000, 136444, MC_ADVISE, 0x0003, 0, 0) = 0
9973/1: close(40) = 0
9973/1: mprotect(0xED3E0000, 15895, PROT_READ|PROT_WRITE|PROT_EXEC) = 0
9973/1: mprotect(0xED3E0000, 15895, PROT_READ|PROT_EXEC) = 0
9973/1: mprotect(0xED3C0000, 19335, PROT_READ|PROT_WRITE|PROT_EXEC) = 0
9973/1: mprotect(0xED3C0000, 19335, PROT_READ|PROT_EXEC) = 0
9973/1: mprotect(0xED280000, 1042504, PROT_READ|PROT_WRITE|PROT_EXEC) =
0
9973/1: mprotect(0xED280000, 1042504, PROT_READ|PROT_EXEC) = 0
9973/1: close(41) = 0
9973/1: munmap(0xEF590000, 8192) = 0
9973/1: brk(0x0039EBB8) = 0
9973/1: brk(0x003A0BB8) = 0
9973/1: getuid() = 101 [101]
9973/1: getuid() = 101 [101]
9973/1: getgid() = 100 [100]
9973/1: getgid() = 100 [100]
9973/1: time() = 968015516
9973/1: getcontext(0xEFFFDB50)
9973/1: Incurred fault #5, FLTACCESS %pc = 0xED2AB9E8
9973/1: siginfo: SIGBUS BUS_ADRALN addr=0x001F5045
9973/1: Received signal #10, SIGBUS [default]
9973/1: siginfo: SIGBUS BUS_ADRALN addr=0x001F5045
9973/1: *** process killed ***

The postgres log is also uninformative:

Server process (pid 8307) exited with status 138 at Sun Sep 3 14:04:38 2000
Terminating any active server processes...
The Data Base System is in recovery mode
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died
abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to
terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Server processes were terminated at Sun Sep 3 14:04:38 2000
Reinitializing shared memory and semaphores
DEBUG: Data Base System is starting up at Sun Sep 3 14:04:38 2000
DEBUG: Data Base System was interrupted being in production at Sun
Sep 3 13:54:36 2000
DEBUG: Data Base System is in production state at Sun Sep 3 14:04:38 2000

Any ideas?

thanks,
-- Mike

Michael Blakeley <mike@blakeley.com> 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?

regards, tom lane

At 8:07 PM -0400 9/3/2000, Tom Lane wrote:

Michael Blakeley <mike@blakeley.com> 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

hi ..
new user to postgresql access..

upon trying to create new database getting error as below:

tcl error executing pg_exec
create database handiman
is not a valid postgresql
connection

well first off i'm not on network is that reason for error..
i haven't done much reading yet but just in case i wanted to post this for
time sake

if this error is mentioned in readme somewhere either in docs or website
i'll find it and just ignore this
otherwise any help appreciated......

lee
----------------

-----Original Message-----
From: lee johnson <lee@imyourhandiman.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Wednesday, September 06, 2000 8:52 AM
Subject: [GENERAL] postgres not allowing new database?

hi ..
new user to postgresql access..

upon trying to create new database getting error as below:

tcl error executing pg_exec
create database handiman
is not a valid postgresql
connection

You don't have a connection to the database yet (like the message says).
You must first do a pg_connect and use the returned handle in the pg_exec
call:

% set c [pg_connect template1]
pg_exec $c "create database handiman"
pg_disconnect $c

set c [pg_connect handiman]
pg_exec $c "Whatever query you want to do on the handiman DB"

Hope this helps.

"Trewern, Ben" wrote:

You need to create a database first and connect to it.

Best to do

createdb testdb

at the command line

below is error i get when trying to do above command
i realize i need to start postmaster but all trys not successful
as from docs i can't tell how to get my template1 created
( in /usr/local/data......... )
as that is required apparantly for postmaster..........
--------------------------------------------
[handiman@localhost /root]$ createdb handiman
psql: connectDBStart() -- connect() failed: Connection refused
Is the postmaster running at 'localhost'
and accepting connections on Unix socket '5432'?
createdb: database creation failed

upon trying to create new database getting error as below:

tcl error executing pg_exec
create database handiman
is not a valid postgresql
connection

You don't have a connection to the database yet (like the message says).
You must first do a pg_connect and use the returned handle in the pg_exec
call:

% set c [pg_connect template1]
pg_exec $c "create database handiman"
pg_disconnect $c

set c [pg_connect handiman]
pg_exec $c "Whatever query you want to do on the handiman DB"

not sure i completely understood ( i am new to postgres AND this kind of
software...........) and end user to boot........not on network just local...
I can find NO documentation of how to get postermaster going for
newbies............

i can only get above commands to work ( which was likely the orig. posters
intent ) if I
type in set c in front of them all.........I did NOT try the last line
Pg_exec as i just started pgaccess after
set c pg_connect handiman

that line produced no errors so i thought i was ready to go.....
but once inside pgaccess i was unable to connect to handiman database........
i was gettting unable to connect to localhost/is postmaster running with -i
and accepint connections on tcp/ip at 5432 .........????

thanks for any help
lee

getting

well i'm in :-) whew!!!!!!
likely i missed reading howto somewhere in docs dunno regarldess i got in via:

from console:

su postgres
postmaster -i -D /usr/local/data ( i was getting can't connect tcp/ip error
before so i put in the -i trigger as suggested )

then I started pgaccess [database name]
then username/password and whala..

kewl now i can get down and dirty with the business at hand.....:)

lee
-----------------