Re: [GENERAL] PLPython function and multiple line insert

[Igor Katson]

Andi Klapper wrote:


  sql = "("INSERT INTO table1 (field1, field2, field3)
   VALUES ('abc', 'abc', TRUE),
  ('def', 'def', FALSE),
  ('ghi', 'ghi', TRUE");"
  pypl.execute(sql)
.
.
$$
LANGUAGE 'plpythonu' VOLATILE

I ran into trouble with quoting this SQL statement in PLPython 
(PostgreSQL 8.3.7). Any ideas/suggestions how to quote it either with 
pypl.execute() or pypl.plan()?
The statement looks incorrect from the python point of view (not just 
plpython). You should quote multi-lined strings, or use an escape 
character (\) like:

"""line 1
line 2"""
'''line 1
line2'''
'line 1\n\
line2'
So I would try to rewrite the statement with:
 sql = """INSERT INTO table1 (field1, field2, field3)
  VALUES ('abc', 'abc', TRUE),
 ('def', 'def', FALSE),
 ('ghi', 'ghi', TRUE);"""
 pypl.execute(sql)


Thanks for any suggestions and help in advance,
Andi




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Add a serial column to a table based on a sort clause

[Igor Katson]

I have a table, which has a creation_ts (timestamp) column, but does not
have a id (serial) column. I want to add such a one, but, AFAIK, if I enter

ALTER TABLE table ADD COLUMN id serial

it will randomly put the sequence numbers.

I wrote a function, which uses a cursor and UPDATE WHERE CURRENT OF, and
it works, but is there any other, more "elegant", way?

CREATE OR REPLACE FUNCTION newsfeed_seq_add_cursor
() RETURNS void AS $$
DECLARE
curs refcursor;
rec record;
BEGIN
create sequence seq;
ALTER TABLE table ADD COLUMN id int;
OPEN curs FOR SELECT * FROM table ORDER BY creation_ts FOR UPDATE;
FETCH curs INTO rec;
WHILE FOUND IS TRUE
LOOP
UPDATE table SET id = nextval('seq') WHERE CURRENT OF curs;
END LOOP;
ALTER TABLE table ALTER COLUMN id SET NOT NULL, ALTER COLUMN id
SET DEFAULT nextval('seq');
END;
$$ language plpgsql;


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pl/proxy and sequence generation

[Igor Katson]

Hello!

The problem, i'm going to describe is about pl/proxy usage. I'll call 
the bases, which are proxied to, the node-based and the base, which 
contains pl/proxy the proxy-base.


The task, I need to accomplish, is to make an insert into node-bases, 
using the sequence, generated inside the proxy-base, as the primary key.


As far as I understand, this is not a good idea to generate the keys 
inside the node-bases, simply because the values will not be unique, 
cause they will have different sequence generators. So I decided to make 
a sequence in the proxy-base. And the sequence number should be passed 
to the node-bases as a fucntion argument. As far as I know, you should 
have the same number and types of arguments for this function both in 
the node-bases and the proxy-base. So I should have a sequence number as 
an argument in the proxy-base too.


Usually, the application calls the function in the proxy-base, and this 
function calls the same ones in the node-bases. But the application 
should not generate the sequence, and it should not pass it as an 
argument to the function.


So, should I make a wrapper in e.g. PL/pgsql for every insert function 
writen in PL/Proxy to remove the sequence from the argument list and to 
call the sequence generator?

Is there a better way to do that?

Thanks in advance,
Igor Katson.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How can I display the contents of a function?

[Igor Katson]

Thom Brown wrote:
I would like to see what's in a function.  I can do this if I use 
pgAdmin III just by clicking on the function, and it appears in the 
SQL pane opposite.  But how is this done?  Is there a command like 
DESCRIBE FUNCTION logging.do_stuff; ?  And if possible, doing this 
without psql.


Thanks

Thom

You can do
\df+ function_name(arg type, arg type...)
in psql

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How can I look at a recursive table dependency tree?

[Igor Katson]

I want to DROP CASCADE a table, but I am afraid that amoung numerous 
recursive dependencies there will be smth, that I don't want to drop.


Is there a way to watch all dependencies recursively without doing a drop?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How can I display the contents of a function?

[Igor Katson]

Thom Brown wrote:
I would like to see what's in a function.  I can do this if I use 
pgAdmin III just by clicking on the function, and it appears in the 
SQL pane opposite.  But how is this done?  Is there a command like 
DESCRIBE FUNCTION logging.do_stuff; ?  And if possible, doing this 
without psql.


Thanks

Thom

You can also do
\df+ function_name(args)
in psql

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Get object creation sql script in psql client

[Igor Katson]

Is there a way to get i.e. table creation sql script from an existing 
table in psql (not postgresql, but psql client), like it is in pgAdmin?


I.e. i point it to existing table 'foo', and it writes:
CREATE TABLE foo (
  bar int
);

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Get object creation sql script in psql client

[Igor Katson]

Grzegorz Jaśkiewicz wrote:

pg_dump -t foo database
  
Thanks, but pg_dump is not psql client (i meant the */bin/psql 
interactive shell), and there is only an option for table objects, and 
no one for i.e. indices.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Get object creation sql script in psql client

[Igor Katson]

Grzegorz Jaśkiewicz wrote:

pg_dump -t ANYOBJECT database
afaik., try it - play with it.


  
that does not work for indices. But the index creation is shown when 
placing it's parent table into -t. Thanks for the help, Grzegorz, the 
issue is solved.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How can I look at a recursive table dependency tree?

[Igor Katson]

Richard Huxton wrote:

Igor Katson wrote:
  

I want to DROP CASCADE a table, but I am afraid that amoung numerous
recursive dependencies there will be smth, that I don't want to drop.

Is there a way to watch all dependencies recursively without doing a drop?



BEGIN;
DROP CASCADE...
-- check things
ROLLBACK;

  
Thanks everyone, who responded. DROP TABLE while in transaction (if you 
just want to look at the cascading drops) is really fast and safe.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] A complex plproxy query

[Igor Katson]

This is a complex question, and I couldn't form it in a short and easy 
way, and I'm sorry for that.


First of all, let me introduce you to the DB (to form a question), for 
you to understand what am I talking about. The module looks like a 
social network, just the users have friends, which can be in different 
groups.


Also it is clustered with PLPROXY by user_id, so the user itself, and 
his friends list (the list of ID's) is always in the same DB, but the 
information about the friends is not (it is clustered through all the 
partitions). Here is a little sketch of a the needed tables:


CREATE TABLE friend
(
  id bigint,
  user_id integer,
  friend_id integer,
  group_id bigint,
...
);
This table is a 'friend link' from one user to another, which can be 
marked as being in some 'group', and the backward link exists also (from 
the 2nd user to the 1st), which can possibly be in another 'group'.


CREATE TABLE user
(
 user_id integer,
 nickname text,
 -- lots of other info
);
This is just a user table.

Both of these are clustered by user_id. I need to form the following 
query, for it to be as fast as possible (here it is written as if it the 
DB was not partitioned):

SELECT something FROM user u, friend f
WHERE u.user_id = f.friend.id
AND f.user_id = $1 (this is given as an argument)
AND f.group_id = $2

So to say, give me the list of friends (not only their ID's, but all the 
needed columns!) of given individual, which are in a given group. That 
seems ok without plproxy, but with using it, I can't imagine how can I 
form a nice query, or a function (or a set of plpgsql + plproxy 
functions) to do the job.


Thanks in advance and regards,
Igor Katson.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Plproxy-users] A complex plproxy query

[Igor Katson]

Hannu Krosing wrote:

On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:

  
So to say, give me the list of friends (not only their ID's, but all the 
needed columns!) of given individual, which are in a given group. That 
seems ok without plproxy, but with using it, I can't imagine how can I 
form a nice query, or a function (or a set of plpgsql + plproxy 
functions) to do the job.



You need to do it in two steps - first run a query on the partition the
user is in to get list of friends ids, then run a second RUN ON ALL
query with 


WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2

to gather all friend info in parallel

  
I was thinking about that. But I don't understand, how can I pass the 
list of id's. Should I turn the output of a select into an array? How 
then? What if the array gets hundreds of items long?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Plproxy-users] A complex plproxy query

[Igor Katson]

Marko Kreen wrote:

On 1/22/09, Igor Katson  wrote:
  

Hannu Krosing wrote:
 > On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:
 >
 >
 >> So to say, give me the list of friends (not only their ID's, but all the
 >> needed columns!) of given individual, which are in a given group. That
 >> seems ok without plproxy, but with using it, I can't imagine how can I
 >> form a nice query, or a function (or a set of plpgsql + plproxy
 >> functions) to do the job.
 >>
 >
 > You need to do it in two steps - first run a query on the partition the
 > user is in to get list of friends ids, then run a second RUN ON ALL
 > query with
 >
 > WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2
 >
 > to gather all friend info in parallel
 >
 >

I was thinking about that. But I don't understand, how can I pass the
 list of id's. Should I turn the output of a select into an array? How
 then? What if the array gets hundreds of items long?



Yes, array works fine.  And if it's long, then let it be long...

  
Ok, thank you, guys. What is the best way to make an array out of a 
column? I didn't make up anything better then writing a function:


CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[] 
AS $$

   DECLARE
   arr int[];
   rec int;
   BEGIN
   FOR rec IN EXECUTE query
   LOOP
   arr := array_append('{}',rec);
   END LOOP;
   RETURN arr;
   END;
$$ language plpgsql;


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Plproxy-users] A complex plproxy query

[Igor Katson]

Hannu Krosing wrote:

On Thu, 2009-01-22 at 15:10 +0300, Igor Katson wrote:

  
Ok, thank you, guys. What is the best way to make an array out of a 
column? I didn't make up anything better then writing a function:


CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[] 
AS $$

DECLARE
arr int[];
rec int;
BEGIN
FOR rec IN EXECUTE query
LOOP
arr := array_append('{}',rec);
END LOOP;
RETURN arr;
END;
$$ language plpgsql;



hannu=# select ARRAY(select usename from pg_user);
   ?column?
---

 {postgres,hannu,m1,skyncuser}
(1 row)



  
Lots of thanks! I tried the same one, but with ARRAY[], so i didn't get 
anything.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [Plproxy-users] A complex plproxy query

[Igor Katson]

Hannu Krosing wrote:

On Thu, 2009-01-22 at 16:13 +0200, Hannu Krosing wrote:
  

On Thu, 2009-01-22 at 15:10 +0300, Igor Katson wrote:


Ok, thank you, guys. What is the best way to make an array out of a 
column? I didn't make up anything better then writing a function:


CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[] 
AS $$

DECLARE
arr int[];
rec int;
BEGIN
FOR rec IN EXECUTE query
LOOP
arr := array_append('{}',rec);
END LOOP;
RETURN arr;
END;
$$ language plpgsql;
  

hannu=# select ARRAY(select usename from pg_user);
   ?column?
---

 {postgres,hannu,m1,skyncuser}
(1 row)



So what yo need is

select * from
gather_friend_info_from_all_partitions(get_friend_id_list_form_user_partition(user_id));

  
Yes, after using arrays, I figured out perfectly, how to do that. And 
thanks for the help!


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using null or not null in function arguments

[Igor Katson]

I have a row search function, smth like

CREATE OR REPLACE FUNCTION user_func.search_users
(i_city_id int, i_edu_id int, i_first_name text, i_last_name text,
limit_ int, offset_ int) RETURNS SETOF user.user AS $$
. SELECT * FROM user WHERE
 city_id = i_city_id
...
$$ language plpgsql;

How do I write a function without complex logic, which will do:
a) If the input argument is NULL, then the corresponding select 
statement will change from


column = arg
to
column IS NULL

maybe there is some built-in function for that?

b) If the input argument is NULL, then the corresponding select 
statement will be removed, so if it was not written.


I think, this is a common problem.

Thanks in advance and regards,
Igor Katson.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using null or not null in function arguments

[Igor Katson]

Raymond O'Donnell wrote:

On 23/01/2009 11:16, Igor Katson wrote:

  

How do I write a function without complex logic, which will do:
a) If the input argument is NULL, then the corresponding select
statement will change from

column = arg
to
column IS NULL



You could build your statement dynamically as a string, then execute it
using EXECUTE:

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


  

b) If the input argument is NULL, then the corresponding select
statement will be removed, so if it was not written.



Likewise - something like

  if i_city_id is null then
... build statement...
... execute statement ...
  end if;

HTH,

Ray.
  
Thanks, Ray, but I see now, that I didn't explain the exact problem 
correctly. The one is, that this kind of functions (search ones) can 
have tens of arguments, and the more the amount of arguments is, the 
more combinations of IF ... THEN conditionals will be present to build 
the logic. If I have a couple of them, this can be easily handled 
through IF THEN, or dynamic statements, but what if I have tens of 'em?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using null or not null in function arguments

[Igor Katson]

Sam Mason wrote:

On Fri, Jan 23, 2009 at 02:16:34PM +0300, Igor Katson wrote:
  
a) If the input argument is NULL, then the corresponding select 
statement will change from


column = arg
to
column IS NULL



I think you want to use the IS [NOT] DISTINCT FROM operator.  It
works like the = and <> operators. i.e. the following expressions are
equivalent:

  x IS NOT DISTINCT FROM y

and

  CASE WHEN x IS NULL THEN y IS NULL
   ELSE COALESCE(x = y, FALSE) END
  

  
b) If the input argument is NULL, then the corresponding select 
statement will be removed, so if it was not written.



not sure what you mean here, but maybe one of the existing suggestions
may help or the "RETURNS NULL ON NULL INPUT" flag when you're creating
the function may be what you're looking for.


  
That one is awesome, thanks, I completely forgot about CASE statement. 
The search func now looks as follows, and works perfectly:


CREATE OR REPLACE FUNCTION isocial_user_func.search_users
(i_city_id int, i_edu_id int, i_firstname text, i_lastname text,
limit_ int, offset_ int) RETURNS SETOF isocial_user.user AS $$
   DECLARE
  rec isocial_user.user;
   BEGIN
  FOR rec IN SELECT * FROM isocial_user.user
 WHERE
 CASE
 WHEN i_city_id IS NULL THEN TRUE
 ELSE city_id = i_city_id
 END AND
 CASE
 WHEN i_edu_id IS NULL THEN TRUE
 ELSE edu_id = i_edu_id
 END AND
 CASE
 WHEN i_firstname IS NULL THEN TRUE
 ELSE upper(firstname) ~ upper(i_firstname)
 END AND
 CASE
 WHEN i_lastname IS NULL THEN TRUE
 ELSE upper(lastname) ~ upper(i_lastname)
 END
 LIMIT limit_
 OFFSET offset_
  LOOP
  RETURN NEXT rec;
  END LOOP;
  RETURN;
   END;
$$ language plpgsql;


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using null or not null in function arguments

[Igor Katson]

Michael Glaesemann wrote:


On Jan 23, 2009, at 10:11 , Igor Katson wrote:

That one is awesome, thanks, I completely forgot about CASE 
statement. The search func now looks as follows, and works perfectly:


CREATE OR REPLACE FUNCTION isocial_user_func.search_users
(i_city_id int, i_edu_id int, i_firstname text, i_lastname text,
limit_ int, offset_ int) RETURNS SETOF isocial_user.user AS $$
  DECLARE
 rec isocial_user.user;
  BEGIN
 FOR rec IN SELECT * FROM isocial_user.user
WHERE
CASE
WHEN i_city_id IS NULL THEN TRUE
ELSE city_id = i_city_id
END AND
CASE
WHEN i_edu_id IS NULL THEN TRUE
ELSE edu_id = i_edu_id
END AND
CASE
WHEN i_firstname IS NULL THEN TRUE
ELSE upper(firstname) ~ upper(i_firstname)
END AND
CASE
WHEN i_lastname IS NULL THEN TRUE
ELSE upper(lastname) ~ upper(i_lastname)
END
LIMIT limit_
OFFSET offset_
 LOOP
 RETURN NEXT rec;
 END LOOP;
 RETURN;
  END;
$$ language plpgsql;


Here's an alternate formulation that eliminates the CASE statements 
which I find hard to read:


CREATE OR REPLACE FUNCTION isocial_user_func.search_users
(i_city_id int, i_edu_id int, i_firstname text, i_lastname text,
limit_ int, offset_ int) RETURNS SETOF isocial_user.user AS $$
  DECLARE
 rec isocial_user.user;
  BEGIN
 FOR rec IN
   SELECT *
 FROM isocial_user.user
 WHERE (i_city_id IS NULL OR city_id = i_city_id)
   AND (i_edu_id IS NULL OR edu_id = i_edu_id)
   AND (i_firstname IS NULL OR upper(firstname) ~ 
upper(i_firstname))
   AND (i_lastname IS NULL OR upper(lastname) ~ 
upper(i_lastname))

 LIMIT limit_
 OFFSET offset_
 LOOP
 RETURN NEXT rec;
 END LOOP;
 RETURN;
  END;
$$ language plpgsql;

And you really don't even need to use PL/pgSQL: an SQL function would 
work just as well.


CREATE OR REPLACE FUNCTION
isocial_user_func.search_users (i_city_id int, i_edu_id int,
i_firstname text, i_lastname text,
limit_ int, offset_ int,
)
RETURNS SETOF RECORD
LANGUAGE SQL AS $$
  SELECT *
FROM isocial_user.user
WHERE ($1 IS NULL OR city_id = i_city_id)
  AND ($2 IS NULL OR edu_id = i_edu_id)
  AND ($3 IS NULL OR upper(firstname) ~ upper(i_firstname))
  AND ($4 IS NULL OR upper(lastname) ~ upper(i_lastname))
LIMIT $5
OFFSET $6
$$;

Michael Glaesemann
grzm seespotcode net




Thank you, Michael, that one looks prettier.
Sam, I'm not sure if this is correct to do that, as you I don't want to 
remember what will happen, if you use NULL = NULL or upper(NULL) etc.:


 WHERE
   COALESCE(city_id = i_city_id, TRUE) AND
   COALESCE(edu_id  = i_edu_id,  TRUE) AND
   COALESCE(upper(firstname) ~ upper(i_firstname), TRUE) AND
   COALESCE(upper(lastname)  ~ upper(i_lastname),  TRUE)




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Storing a result of a select in a variable

[Igor Katson]

As I cannot do usual joins while using plproxy, there is a need to 
perform a following set of operations:


get some (3 in fact) result columns from one function, then provide one 
of this columns to another function as an array, and join the result of 
the first function with the result of the second function. I don't like 
the solution, that I made up, cause it executes one of the functions 2 
times, once to get an array from it, and the other to make a join.


This happens cause I haven't found a way to keep the whole result of a 
function (several columns) in one variable in PL/pgsql.


Is there a way to make it better? Maybe using more-than-one-dimensional 
arrays or something? Which one would be more effective?


I marked the places, where the function gets called twice.

Thanks in advance.

CREATE OR REPLACE FUNCTION friend_func.get_friends(i_user_id int,
limit_ int, offset_ int) RETURNS SETOF friend_func.user_friend_full AS $$
   DECLARE
   arr int[];
   rec friend_func.user_friend_full;
   BEGIN
>>arr := ARRAY(SELECT friend_id FROM 
friend_func.get_friends_short(i_user_id,

   limit_, offset_));
   FOR rec IN SELECT a.id,
 b.creation_ts AS fr_creation_ts,
 b.group_id,
 b.alias,
 a.nickname,
 a.phone_number,
 a.creation_ts AS usr_creation_ts,
 a.passwd,
 a.login_enabled,
 a.city_id,
 a.edu_id,
 a.firstname,
 a.lastname,
 a.is_male,
 a.current_status
  FROM isocial_user_func.get_users_from_array(arr) a,
>>friend_func.get_friends_short(i_user_id,
   limit_, offset_) b
  WHERE a.id = b.friend_id
   LOOP
   RETURN NEXT rec;
   END LOOP;
   RETURN;
   END;
$$ language plpgsql;

Regards,
Igor Katson

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Text search name and name synonims dictionary

[Igor Katson]

I have a column, containing the name of the user and there is a need to 
organize an indexed search on this column. As far as I understand, I 
need to use the full-text search capabilities of postgres.


I would like to attach a dictionary, containing many possible names, and 
the short names like:


William Will Bill Billy
James Jim Jimmy

etc., which will give me a possibility to perform indexed search on any 
of these names and it will give me all the matches on variations of the 
name, regardless of which of them was chosen as a search argument.


1. Is it possible to do that with postgres' full-text search capabilities?
2. Does anyone know, if there is a Russian dictionary of these names?
3. What is the name of such kind of dictionary, to know, what to google for?

Thanks in advance, and regards
Igor Katson.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search name and name synonims dictionary

[Igor Katson]

Oleg Bartunov wrote:

On Thu, 29 Jan 2009, Igor Katson wrote:

I have a column, containing the name of the user and there is a need 
to organize an indexed search on this column. As far as I understand, 
I need to use the full-text search capabilities of postgres.


I would like to attach a dictionary, containing many possible names, 
and the short names like:


William Will Bill Billy
James Jim Jimmy

etc., which will give me a possibility to perform indexed search on 
any of these names and it will give me all the matches on variations 
of the name, regardless of which of them was chosen as a search 
argument.


1. Is it possible to do that with postgres' full-text search 
capabilities?


yes, look on contrib/dict_xsyn


2. Does anyone know, if there is a Russian dictionary of these names?


there are printed dictionaries, not sure about electronic versions.


3. What is the name of such kind of dictionary, to know, what to 
google for?


http://yandex.ru/yandsearch?text=словарь+личных+имен



Thanks in advance, and regards
Igor Katson.




Regards,
Oleg
_


Thanks for the help, Oleg!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Plproxy functions inside transactions and Pl/pgsql exception handling

[Igor Katson]

As far as I understand, it is a known problem of using plproxy, that it 
cannot be rolled back if used inside transactions. But I need something 
similar to this functionality.


I have some data, that is duplicated across the DB partitions, and to be 
exact, there is i.e. a plproxy-partitioned DB, containing users. For the 
list of user's friends to be in the same DB, where the user himself is, 
I need to duplicate the 'user-friend' data to the partition of the user, 
and the partition of the friend.


So I need to call SEVERAL plproxy functions inside a transaction.

Well, I understand that plproxy does not support well that kind of usage 
(will it?). But I need to create some mechanism to do a check and a 
rollback (if neccessary) manually inside the PL/pgsql function that does 
this job.


How can I do that, if, afaik, PL/pgsql does not support exception handling?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [Plproxy-users] Plproxy functions inside transactions and Pl/pgsql exception handling

[Igor Katson]

Hannu Krosing wrote:

On Fri, 2009-01-30 at 14:09 +0300, Igor Katson wrote:
  
As far as I understand, it is a known problem of using plproxy, that it 
cannot be rolled back if used inside transactions. But I need something 
similar to this functionality.


I have some data, that is duplicated across the DB partitions, and to be 
exact, there is i.e. a plproxy-partitioned DB, containing users. For the 
list of user's friends to be in the same DB, where the user himself is, 
I need to duplicate the 'user-friend' data to the partition of the user, 
and the partition of the friend.


So I need to call SEVERAL plproxy functions inside a transaction.

Well, I understand that plproxy does not support well that kind of usage 
(will it?). But I need to create some mechanism to do a check and a 
rollback (if neccessary) manually inside the PL/pgsql function that does 
this job.


How can I do that, if, afaik, PL/pgsql does not support exception handling?



To do so, you would need two phase commit (2PC) which is usually a pita
to maintain (needs a separate transaction manager) and also it does not
scale. 


As the whole point on pl/proxy is scaling, you want to avoid 2PC

The way to avoid 2PC is to design your system so that you can use async
replication for maintaining "secondary" data / read-only copies.

The way to do it in a scalable fashion is to have one
pl/proxy-partitioned function to update users friend list on that users
partition and then use pgQ (from SkyTools) to capture changes and then
apply them to partitions of each friend.

This mean that there will be a delay between updating users friend list
and the "reverse" friend-with list of each friend, which must be
considered in the design. But it is easy to do on most cases and doable
in 100% of cases.

Typical pgQ delay can be below one second, even a few tenths of second
is doable.

  

Thanks for the great answer.

Concerning plpgsql and exceptions: btw, I was not right, and there IS 
exception handling in plpgsql, but implementing it is ok only somewhere, 
and in the other cases it seems like hell, considering this problem 
(doing a fully manual "rollback" in the remote DB), e.g. when in the 
first plproxy func something is deleted, and the second func gives out 
an error, I must manually get the data to to be deleted in the 1st, and 
insert it back manually in case of failure of the 2nd. I don' like this 
method.


What I really like is 2-phase commit idea, that you described. When 
reading about it in Wikipedia 
(http://en.wikipedia.org/wiki/Two-phase_commit_protocol), it seems the 
exactly right thing, that I need, but when scrolling the Postgres manual 
(prepare transaction, commit prepared and rollback prepared) it does not.


Is there a way to deploy 2PC, as described in Wiki, with postgres? I 
mean, that all the partitions will do a rollback, if one of them says 
'abort' ?


P.S. I can't understand, why it can ruin the whole plproxy idea in my 
case, because I always need only 2 partitions acting in a 2PC 
transaction — the user one, and the friend one.


Thanks in advance.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dict-xsyn converts word to synlist, what about backwards?

[Igor Katson]

Oleg Bartunov wrote:

On Tue, 3 Feb 2009, Igor Katson wrote:


On my question about doing a first name search by it's synonyms, like

William Will Bill Billy
James Jim Jimmy

Oleg answered, that I can use dict_xsyn for that.

In the dict_xsyn manual, it is said that:
"This dictionary type replaces words with groups of their synonyms, 
and so makes it

possible to search for a word using any of its synonyms"

This means, that if the dictionary sees the name "William", it will 
replace it with "William Will Bill Billy", so that I can search with 
any of them.


But what if the parser sees the word "Billy"? I want it to do 
absolutely the same. If not, as far as I understand, I will make a 
default synonym dictionary with pairs like:


will william
bill william
billy william   etc.

Is there a way not to use this?


no, but you always can generate rules for that using other tools.

Thanks. Oleg, and what do you think about creating an xsyn dictionary 
like with e.g. a python script:

William Will Bill Billy
Will William Bill Billy
Bill William Will Billy
Billy William Will Bill ?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dict-xsyn converts word to synlist, what about backwards?

[Igor Katson]

Oleg Bartunov wrote:

I contacted with author of dict_xsyn, so probably, he'll add option to
support what you want.

Thanks for cooperation, Oleg. By now, I will use that workaround.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dict-xsyn converts word to synlist, what about backwards?

[Igor Katson]

On my question about doing a first name search by it's synonyms, like

William Will Bill Billy
James Jim Jimmy

Oleg answered, that I can use dict_xsyn for that.

In the dict_xsyn manual, it is said that:
"This dictionary type replaces words with groups of their synonyms, and 
so makes it

possible to search for a word using any of its synonyms"

This means, that if the dictionary sees the name "William", it will 
replace it with "William Will Bill Billy", so that I can search with any 
of them.


But what if the parser sees the word "Billy"? I want it to do absolutely 
the same. If not, as far as I understand, I will make a default synonym 
dictionary with pairs like:


will william
bill william
billy william   etc.

Is there a way not to use this?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Deleting conflicting rows when creating a foreign key

[Igor Katson]

I am doing an ALTER TABLE to create a foreign key, however with some 
rows i get:


insert or update on table "name" violates foreign key constraint "name_fkey"

How can I just drop the conflicting rows while doing that?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Deleting conflicting rows when creating a foreign key

[Igor Katson]

Craig Ringer wrote:

Richard Huxton wrote:

  

DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2);



Just as a side note: If you have a large number of missing IDs and don't
want to wait a long time, you may be better off with something like
(untested, but I think it's right - TEST FIRST):

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE t2.id IS NULL;

-- check that the rows to be deleted are OK

DELETE FROM table1
USING table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE table1.id = t1.id AND t2.id IS NULL;

(by the way, being able to specify an explicit join method in a DELETE
... USING or update ... USING would be *great*).

--
Craig Ringer
  

Thanks for the advice, Craig, I didn't know about that syntax before.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Two-phase commmit, plpgsql and plproxy

[Igor Katson]

I would like to call several plproxy functions one after another (which 
will call plpgsql functions in different target partitions), and in case 
one of them fails, i want to roll back changes in every one.


That is exactly how two-phase-commit (2PC) should work.

As far as I understand, the postgres' group of statements like PREPARE 
TRANSACTION can do this job. But when trying to insert a 'PREPARE 
TRANSACTION' statement into a PL/Pgsql function I get an error:


ERROR:  XX000: SPI_execute_plan failed executing query "PREPARE 
TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION


Is there a way to achieve the needed behaviour with two-phase commmit, 
plpgsql and plproxy ?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Making a result of transaction visible to everyone, saving the ability for a rollback

[Igor Katson]

I need to execute a rather complex plpgsql function on a production 
server, the result of which is hard to test by myself.


I want to make the result of a transaction, in which this function is 
executed, visible to the outer clients, but to have the ability of 
making a rollback in case one of the clients says something went wrong. 
Is it possible to do that?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

[Igor Katson]

Jeff Davis wrote:

On Wed, 2009-02-11 at 12:43 +0300, Igor Katson wrote:
  
ERROR:  XX000: SPI_execute_plan failed executing query "PREPARE 
TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION



It's probably treating the word PREPARE specially. You can avoid this
problem by using EXECUTE and specifying the command as a string.

  

Yes, doing an EXECUTE helped to create the function.
Is there a way to achieve the needed behaviour with two-phase commmit, 
plpgsql and plproxy ?



You can't begin or end a transaction inside a function. If that was
allowed, what would the function do after the transaction was prepared?

  
I think you need to do PREPARE TRANSACTION separately, somehow. You

might need to modify plproxy to do that the way you want.
  

Thanks, Jeff. That's not good news, cause I am not able to do that.

The postgres manual says, that
" The intended usage of the feature is that a prepared transaction will 
normally be committed or rolled back as soon as an external transaction 
manager has verified that other databases are also prepared to commit. "


So does this "external transaction manager" exist? I am not clear about 
what it is.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

[Igor Katson]

Jeff Davis wrote:

On Thu, 2009-02-12 at 00:57 +0300, Igor Katson wrote:
  

Thanks, Jeff. That's not good news, cause I am not able to do that.



There may be some creative solution, but I don't know plproxy well
enough to suggest one.

  

The postgres manual says, that
" The intended usage of the feature is that a prepared transaction will 
normally be committed or rolled back as soon as an external transaction 
manager has verified that other databases are also prepared to commit. "


So does this "external transaction manager" exist? I am not clear about 
what it is.



PostgreSQL does not provide a transaction manager.

When you are dealing with multiple databases, the transaction manager
needs to make decisions like "this transaction failed on one node,
therefore we need to roll all the other transactions back".

I think you are basically trying to make plproxy into the transaction
manager. You might get some better suggestions from people who know
plproxy well.
  
Thanks, Jeff. Googling smth like "postgresql transaction manager" does 
not give any nice result. It seems, that the one just does not exist. 
Hope, plproxy developers will answer smth. considering this problem.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Accessing array elements in a FOR PL/pgsql loop

[Igor Katson]

For each element in the array, I need to make some operation with plpgsql.
I usually use the syntax:

DECLARE
 array_len int;
BEGIN
 array_len := array_upper(i_array, 1);
 FOR i IN 1 .. array_len
 LOOP
SOME OPERATION (i_array[i])
 END LOOP;

But I don't like that. Is there any built-in way to do that without 
using the length, i.e like in python, e.g.

 for element in array:
   
This example does not work in Postgres.

I think I need a built-in function to make a column from an array, like 
in the backwards operation SELECT ARRAY(column)


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Accessing array elements in a FOR PL/pgsql loop

[Igor Katson]

A. Kretschmer wrote:

In response to Igor Katson :
  
I think I need a built-in function to make a column from an array, like 
in the backwards operation SELECT ARRAY(column)



By David Fetter:

CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;

HTH, Andreas
  

Thanks. I thought, there is a built-in one for that.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Parallel postgres client

[Igor Katson]

Is there any kind of a parallel psql client, to control a cluster of 
databases, without the need to write a plproxy function for each request?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general