Re: [SQL] Problems invoking psql. Help please.
On Sat, Nov 16, 2002 at 02:11:58PM -0500, Hugh Esco wrote: > Hey folks: > > I've copied the shell dialogue below. > Everything in: /usr/lib/postgresql/bin is owned by root:root. It's default instalation in Debian Woody and it works fine > >postgres@biko:/home/hesco$ psql > >env: /usr/lib/postgresql/bin/readpgenv: Permission denied Is your readpgenv executable? Try chmod 755 readpgenv Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems invoking psql. Help please.
Hugh Esco <[EMAIL PROTECTED]> writes: >> postgres@biko:/home/hesco$ psql >> env: /usr/lib/postgresql/bin/readpgenv: Permission denied >> No database specified >> postgres@biko:/home/hesco$ psql ggp_test >> env: /usr/lib/postgresql/bin/readpgenv: Permission denied >> Could not execv /usr/lib/postgresql/bin/psql You seem to have a very bizarre setup there --- there is no such thing as "readpgenv" in the standard Postgres distribution, and /usr/lib/postgresql/bin/ isn't the standard place to put the executable files either. Perhaps the above is normal for the Debian package of Postgres, but I'm afraid you'll have to ask the Debian packager for help. Nobody using other platforms is likely to be able to help... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Trees: maintaining pathnames
My existing tree implementation reflects the files contained on disk. The
full pathname to a particlar file is obtained from the path to the parent
directory. I am now considering putting this information into a field in
the table.
Attached you will find the pg_dump from my test database (2.4k) if you
want to test with this setup and in case what I have pasted below
contains an error.
Here is the table and the test data:
create table tree(id int not null, parent_id int, name text not null,
pathname text not null, primary key (id));
insert into tree (id, name, pathname) values (1, 'usr', '/usr');
insert into tree (id, name, parent_id, pathname) values (2, 'ports', 1,
'/usr/ports');
insert into tree values (3, 2, 'security', 'test');
select * from tree;
test=# select * from tree;
id | parent_id | name | pathname
+---+--+-
1 | | usr | /usr
2 | 1 | ports| /usr/ports
3 | 2 | security | /usr/ports/security
(3 rows)
The goal is to ensure that pathname always contains the correct value.
Here are the functions/triggers which I created in order to attain that
goal.
This function ensures that the pathname is set correctly when a row is
inserted or changed.
create or replace function tree_pathname_set()
returns opaque
as '
DECLARE
parent_pathname text;
BEGIN
RAISE NOTICE \'into tree_pathname_set with %:%:%\', new.id,
new.name, new.pathname;
select pathname
into parent_pathname
from tree
where id = new.parent_id;
if found then
new.pathname = parent_pathname || \'/\' || new.name;
else
new.pathname = \'/\' || new.name;
end if;
RETURN new;
END;'
language 'plpgsql';\
create trigger tree_pathname_set before insert or update on tree
for each row execute procedure tree_pathname_set();
This function ensures that any childre of a recently modified row are also
kept up to date.
create or replace function tree_pathname_set_children()
returns opaque
as 'BEGIN
RAISE NOTICE \'into tree_pathname_set_children with %:%:%\',
new.id, new.name, new.pathname;
update tree set pathname = new.pathname || \'/\' || name where
parent_id = new.id;
RETURN new;
END;'
language 'plpgsql';
create trigger tree_pathname_set_children after insert or update on tree
for each row execute procedure tree_pathname_set_children();
NOTE: the above is "insert or update" but as I typed this I realize that
only update is sufficent.
A change to the top level row is shown below:
test=# update tree set name = 'dan' where id = 1;
NOTICE: into tree_pathname_set with 1:dan:/usr
NOTICE: into tree_pathname_set_children with 1:dan:/dan
NOTICE: into tree_pathname_set with 2:ports:/dan/ports
NOTICE: into tree_pathname_set_children with 2:ports:/dan/ports
NOTICE: into tree_pathname_set with 3:security:/dan/ports/security
NOTICE: into tree_pathname_set_children with
3:security:/dan/ports/security
UPDATE 1
test=# select * from tree;
id | parent_id | name | pathname
+---+--+-
1 | | dan | /dan
2 | 1 | ports| /dan/ports
3 | 2 | security | /dan/ports/security
(3 rows)
test=#
Suggestions, comment, open ridicule, most welcome. thanks.
--
-- Selected TOC Entries:
--
\connect - pgsql
--
-- TOC Entry ID 3 (OID 15830772)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: pgsql
--
CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '$libdir/plpgsql',
'plpgsql_call_handler' LANGUAGE 'C';
--
-- TOC Entry ID 4 (OID 15830773)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANCOMPILER '';
\connect - dan
--
-- TOC Entry ID 6 (OID 15830774)
--
-- Name: "tree_pathname_set" () Type: FUNCTION Owner: dan
--
CREATE FUNCTION "tree_pathname_set" () RETURNS opaque AS '
DECLARE
parent_pathname text;
BEGIN
RAISE NOTICE ''into tree_pathname_set with %:%:%'', new.id, new.name,
new.pathname;
select pathname
into parent_pathname
from tree
where id = new.parent_id;
if found then
new.pathname = parent_pathname || ''/'' || new.name;
else
new.pathname = ''/'' || new.name;
end if;
RETURN new;
END;' LANGUAGE 'plpgsql';
--
-- TOC Entry ID 2 (OID 15832154)
--
-- Name: tree Type: TABLE Owner: dan
--
CREATE TABLE "tree" (
"id" integer NOT NULL,
"parent_id" integer,
"name" text NOT NULL,
"pathname" text NOT NULL,
Constraint "tree_pkey" Primary Key ("id")
);
--
-- TOC Entry ID 5 (OID 15834571)
--
-- Name: "tree_pathname_set_children" () Type: FUNCTION Owner: dan
--
CREATE FUNCTION "tree_pathname_set_children" () RETURNS opaque AS 'BEGIN
RAISE
Re: [SQL] Problems invoking psql. Help please.
> You seem to have a very bizarre setup there --- there is no such thing > as "readpgenv" in the standard Postgres distribution, and > /usr/lib/postgresql/bin/ isn't the standard place to put the executable > files either. Perhaps the above is normal for the Debian package of > Postgres, but I'm afraid you'll have to ask the Debian packager for > help. Nobody using other platforms is likely to be able to help... I have Debian and Postgres installed from .deb package. Postgres is installed in /usr/lib/postgresql by default and it contains readpgenv. Psql stops working as described, when I remove executable attribute from readpgenv. readpgenv is a bash script and has only 3 lines: #!/bin/bash . /etc/postgresql/postgresql.env env postgresql.env file is an export of PGDATA/PGLIB/PGACCES_HOME variables Tomasz Myrta ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Trees: maintaining pathnames
Dan, > My existing tree implementation reflects the files contained on disk. > The > full pathname to a particlar file is obtained from the path to the > parent > directory. I am now considering putting this information into a > field in > the table. > Suggestions, comment, open ridicule, most welcome. thanks. This is a fine implementation using the adjacency list model of tree design. However, I think you may find that the string-based tree implementation in /contrib/ltree is more suited to your purposes, and easier to maintain. -Josh Berkus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Trees: maintaining pathnames
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NotDashEscaped: You need GnuPG to verify this message Instead of storing the path in each row, why not let Postgres take care of computing it with a function? Then make a view and you've got the same table, without all the triggers. CREATE TABLE tree ( idINTEGER NOT NULL, parent_id INTEGER, "name"TEXT NOT NULL, PRIMARY KEY (id) ); INSERT INTO tree VALUES (1,NULL,''); INSERT INTO tree VALUES (2,1,'usr'); INSERT INTO tree VALUES (3,1,'tmp'); INSERT INTO tree VALUES (4,1,'home'); INSERT INTO tree VALUES (5,4,'greg'); INSERT INTO tree VALUES (6,5,'etc'); CREATE OR REPLACE FUNCTION pathname(INTEGER) RETURNS TEXT AS ' DECLARE mypath TEXT; myname TEXT; myid INTEGER; BEGIN SELECT parent_id,name FROM tree WHERE id=$1 INTO myid,mypath; IF mypath IS NULL THEN RETURN ''No such id\n''; END IF; LOOP SELECT parent_id,name FROM tree WHERE id=myid INTO myid,myname; mypath := ''/'' || mypath; EXIT WHEN myid IS NULL; mypath := myname || mypath; END LOOP; RETURN mypath; END; ' LANGUAGE 'plpgsql'; CREATE VIEW mytree AS SELECT *, PATHNAME(id) AS path FROM tree; SELECT * FROM tree ORDER BY id; id | parent_id | name +---+-- 1 | | 2 | 1 | usr 3 | 1 | tmp 4 | 1 | home 5 | 4 | greg 6 | 5 | etc (6 rows) SELECT * FROM mytree ORDER BY id; id | parent_id | name | path +---+--+ 1 | | | / 2 | 1 | usr | /usr 3 | 1 | tmp | /tmp 4 | 1 | home | /home 5 | 4 | greg | /home/greg 6 | 5 | etc | /home/greg/etc (6 rows) UPDATE tree SET name='users' WHERE id=4; SELECT * FROM mytree ORDER BY id; id | parent_id | name | path +---+---+- 1 | | | / 2 | 1 | usr | /usr 3 | 1 | tmp | /tmp 4 | 1 | users | /users 5 | 4 | greg | /users/greg 6 | 5 | etc | /users/greg/etc (6 rows) Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200211172015 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE92D9RvJuQZxSWSsgRAn2oAKDyIcrtgB8v1fAMY3B/ITKZ+lBlYgCfXRMe W/xntabEsfuEdseo44cAXbY= =MANm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] be or not to be ???
Hi all(s)... Eu não sou confiável, no entanto, posso confiar no POSTGRES ??? Silvio 2000info
