Re: [SQL] amount of join's and sequential access to the tables involved
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió: Gerardo Herzig gher...@fmed.uba.ar writes: Hi all. Im working on a 'simple' query with 7, 8 left joins. After the 9nth join or so, explain analyze became to show the plan with many tables being read in sequential fashion. Of course, this slows down the query response in a factor on 10. increase join_collapse_limit, perhaps? regards, tom lane Im sory Tom and all, again stuck with this. Now i have 9 joins, and join_collapse_limit = 20, and the query slows down big time when the 9th join appears. Another configuration variable to check against? Thanks again. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] amount of join's and sequential access to the tables involved
Hi all. Im working on a 'simple' query with 7, 8 left joins. After the 9nth join or so, explain analyze became to show the plan with many tables being read in sequential fashion. Of course, this slows down the query response in a factor on 10. I can alter the order in wich the join's are appended, allways with the same result. So,it looks like a resources config situation. Wich config variables are involved in the use of hash joins? (I dont have access right now to the server running pg, so i cannot show query plan nor pg configuration) Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] amount of join's and sequential access to the tables involved
El mié, 11-01-2012 a las 10:40 -0500, Tom Lane escribió: Gerardo Herzig gher...@fmed.uba.ar writes: Hi all. Im working on a 'simple' query with 7, 8 left joins. After the 9nth join or so, explain analyze became to show the plan with many tables being read in sequential fashion. Of course, this slows down the query response in a factor on 10. increase join_collapse_limit, perhaps? regards, tom lane Crap, yes. That was it. Just curious about one thing: That query is part of a function. If i raise set_join_collapse in a psql bash session, and then excecute the function, there is no changes. But if i excecute the query directly in the psql session, it flys. So, what happens, when a plsql function is excecuted, it takes is own enviroment variables, or something like that? Thanks again, Tom! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using of select (myfunction()).* is so slow
El vie, 04-02-2011 a las 12:17 +0100, Pavel Stehule escribió: M ok Thanks...So there is no workaround/alternative to this? yes, (SELECT x.* from func(...) x) instead SELECT (func(...)).* regards Pavel Stehule Gerardo Great, i will check that. Thanks again Pavel! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using of select (myfunction()).* is so slow
Hi all, im using a function of my own in a subquery, and when wonderig about the slowliness of this one, y relalize that: test=# SELECT (_xxfunction(854,'711H',11,false)).* ; (4 filas) --Result DELETED Duración: 1069,465 ms glyms=# SELECT * from _xxfunction(854,'711H',11,false) ; (4 filas) Duración: 228,699 ms For privacy reasons, i just deleted the result and the function name, but the resulst are obviously exactly the same, and the ().* form (which i needed) is taking so much more...there is a reason why? A workaround? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] differences between function returning 'setof table' and 'table'
Hi all. I dont understand why this is happening. Simple hardcoded function, looking for an id who doesn't exists: REATE FUNCTION pp() returns setof public.pp as $$ select * from public.pp where id=99; $$ language sql; So there is no id=9 in table public.pp test=# SELECT * from pp(); id| descripcion| +-+ (0 filas) Great Now, when the function is defined to return `returns public.pp', when i execute it... test=# SELECT * from pp(); id | descripcion +- | | (1 fila) 1 record? Why? Is this the expected behaviour? this is pg 8.3.11 Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ORDER BY is case insensitive
Bryan White wrote:
I was suprised to find out that ORDER BY is case insensitive. Is
there a way to do a case sensitive ORDER BY clause?
This transcript demonstrates what I am seeing:
$ createdb bryan
$ psql bryan
psql (8.4.4)
Type help for help.
bryan=# create table t (f text);
CREATE TABLE
bryan=# insert into t (f) values ('a');
INSERT 0 1
bryan=# insert into t (f) values ('b');
INSERT 0 1
bryan=# insert into t (f) values ('c');
INSERT 0 1
bryan=# insert into t (f) values ('B');
INSERT 0 1
bryan=# select * from t order by f;
f
---
a
b
B
c
(4 rows)
Well. Im not really surprised. The column is text, so it sound
reasonable to order by its *text* representation. You may want to order
from its *ascii* value instead:
regression=# SELECT * from test order by ascii(data);
data
--
B
a
b
c
(4 rows)
Or similar...Wich order are you expecting to see?
HTH
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cast record type to array?
Jasen Betts wrote: On 2010-05-26, Gerardo Herzig gher...@fmed.uba.ar wrote: Hi all. Im not being able to cast a record variable into an array. Im writing a trigger, and i would like to store NEW (and OLD) as text[]. There is a way to do it in plpgsql? (w/o any contrib modules) why not store them as text instead? new::text is a valid cast. Hi Jasen. Storing the record in text format will difficult furter analisis (p.e.searching columns with differences between OLD and NEW). It will not be easy to play with NEW as string for obtaining each column values (string_to_array will not work, the values for NEW can be anything) As this trigger will save information for audit, it is important to get it right. Even worst, im *strongly encouraged* not to use any other language except plpgsql, so i cant use plpythonu, in wich will be trivial to do so. thanks for your time, Jasen Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] cast record type to array?
Hi all. Im not being able to cast a record variable into an array. Im writing a trigger, and i would like to store NEW (and OLD) as text[]. There is a way to do it in plpgsql? (w/o any contrib modules) thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] advice on query joining 10 tables
Hi all. Im triyng to see if i can improve the performance of a query (mainly a 10 table join) 1) Besides of triyng to use indexes, there is some rules of thumb to follow? 2) Should i try to join the bigger tables last in the query? 3) There is some place for understanding EXPLAIN better? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] advice on query joining 10 tables
Scott Marlowe wrote: On Mon, May 10, 2010 at 12:40 PM, Gerardo Herzig gher...@fmed.uba.ar wrote: Hi all. Im triyng to see if i can improve the performance of a query (mainly a 10 table join) 1) Besides of triyng to use indexes, there is some rules of thumb to follow? log long running queries for later analysis? 2) Should i try to join the bigger tables last in the query? The query planner can do that for you automatically. 3) There is some place for understanding EXPLAIN better? http://explain.depesz.com/ Here and the docs? I've found it pretty easy to post one here and ask for help and get it on why a query isn't running well. Note that explain analyze is much preferred to plain explain. Well, thanks Tom and Scott for the answers. I will take some more time reading the docs. Im looking more to know better, rather than just waiting to someone to point out my errors. By the way, that site is cute! Thanks again! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] I, nead to capture the IP number from the PC how is running the script ...
John Dizaro wrote: I, nead to capture the IP number from the PC how is running the script update TABLE1 set campo1 = 123 where ...; Can someone help me please? the pg_stat_activity view has a column named client_addr and a current_query column. That should help. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] an aggregate to return max() - 1 value?
Louis-David Mitterrand wrote: Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks, Mmmm what about select max(col) from table where col not in (select max(col) from table; ? Looks like a double table reading, but it works. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] get sequence name from table name
Uwe Maiwald wrote:
how to get the name of the sequence that is responsible for setting the
autoincrement value of a tables primary key column?
i only have the name of the table and need to have an appropiate sql
statement.
i need this to write a program that loops through all tables of a
database and then ajusts the start values of the sequencees in case the
table has an automatic id value (serial/bigserial)
thanks,
Uwe
The information schema provides what you need.
test=# create table testing (id serial);
NOTICE: CREATE TABLE will create implicit sequence testing_id_seq for
serial column testing.id
test=# SELECT table_name, column_name, column_default from
information_schema.columns where table_name='testing';
table_name | column_name | column_default
+-+-
testing| id | nextval('testing_id_seq'::regclass)
(1 row)
You may need an extra work finding out which the primary keys are, look
at the information schema docs [0]. Maybe you will also need the help of
the system catalogs [1].
[0] http://www.postgresql.org/docs/8.3/static/information-schema.html
[1] http://www.postgresql.org/docs/8.3/static/catalogs.html
(as you can see, this docs are from the 8.3 version. Check yours)
HTH
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] create user xxx with md5 string?
Hi all. Im triyng, with no success, to create a user, and set the password with a md5 string. I did this: comechingon:~ # echo -n 123 | md5sum 202cb962ac59075b964b07152d234b70 - Ok, so then test=# CREATE USER foobar ENCRYPTED PASSWORD 'md5202cb962ac59075b964b07152d234b70'; --I have to add 'md5' at the begging of the string CREATE ROLE test=# SELECT * from pg_shadow where usename='foobar'; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd| valuntil | useconfig -+--+-+--+---+-+--+--- foobar | 3250592 | f | f| f | md5202cb962ac59075b964b07152d234b70 | | (1 row) test=# \c test foobar Password for user foobar: [123, off course] FATAL: password authentication failed for user foobar Previous connection kept test=# SHOW server_version; server_version 8.3.6 Im wondering which my mystake is: Using md5sum? I do have 'md5' as validation mechanism in pg_hba.conf Any hints? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create user xxx with md5 string?
Tom Lane wrote: Gerardo Herzig gher...@fmed.uba.ar writes: Hi all. Im triyng, with no success, to create a user, and set the password with a md5 string. The correct method for computing the md5'd password includes the username. I think it's 'md5' || md5sum(password || username) but look at the code or docs. regards, tom lane Perfect! I could not find that in the docs. It works. Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] slow count(CASE) query
Grant Masan wrote: Hi all, I have this kind of query that I need to do, yes my query is giving right answers now but it is long and slow. I am now asking you that if you have another solution for my query to make that more smarter ! Hope you can help me with this ! select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM (select count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, count(CASE WHEN (type 90) THEN 1 ELSE NULL END) as ship5 FROM school_proj_boat where length 100 GROUP BY type ORDER BY type) as koo UNION ALL select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM (select count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, count(CASE WHEN (type 90) THEN 1 ELSE NULL END) as ship5 FROM school_proj_boat where length between 100 and 200 GROUP BY type ORDER BY type) as koo UNION ALL select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM (select count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, count(CASE WHEN (type 90) THEN 1 ELSE NULL END) as ship5 FROM school_proj_boat where length between 200 and 300 GROUP BY type ORDER BY type) as koo UNION ALL select '300999' as length, sum(ship1) as ship1 ,sum(ship2) as ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM (select count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, count(CASE WHEN (type 90) THEN 1 ELSE NULL END) as ship5 FROM school_proj_boat where length 300 GROUP BY type ORDER BY type) as koo Well, it looks like this will read school_proj_boat 4 times. What about 1) A plsql function that iterates *one time* on school_proj_boat, with a nested CASE, or a par of IF's 2) Could be a good place for using window functions http://www.postgresql.org/docs/current/static/tutorial-window.html HTH Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_restore WARNING: errors ignored on restore
Bryce Nesbitt wrote: So when restoring a particular DB with pg_restore, I get WARNING: errors ignore on restore. Is there a way to dump a list of those errors? # /usr/local/bin/pg_restore8.3 -d SUP-3067 -h 192.168.1.93 -p 5433 -U postgres -L toc_with_parts_commented_out.txt -v production_db.dump.20091016 pg_restore8.3: setting owner and privileges for FK CONSTRAINT api1_view_test_arguments_test_fkey pg_restore8.3: setting owner and privileges for FK CONSTRAINT api1_view_test_expected_results_test_fkey WARNING: errors ignored on restore: 985 try using pg_restore .. 2errors.txt That would create the errors.txt file, with everything that is writen to STDERR (ussualy errors and warnings) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] about pg_proc (column pronamespace)
i all. Looking for a way to change some functions to 'security
definer'. This is only going to happen in some of the schemas.I found
that pg_catalog.pg_proc have a bool column (prosecdef), which contains
if some function is defined as 'security definer'. So good. It also
contains a column named pronamespace, which contains (or should) the
schema.
After this
SELECT distinct pronamespace from pg_catalog.pg_proc;
pronamespace
--
11
2200
11313
1901391
1901393
1901396
1901397
1901398
1901399
i would say that, in pg_catalog.pg_namespace i should be able to found
the schema for a particular function. But nope:
glyms_f_test=# SELECT * from pg_catalog.pg_namespace ;
nspname | nspowner | nspacl
+--+-
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
pg_toast | 10 |
pg_temp_1 | 10 |
pg_toast_temp_1| 10 |
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
[snip]
public | 10 | {postgres=UC/postgres,=UC/postgres}
(15 rows)
Obviously im missing something, i cant find any relation between
pg_proc.pronamespace and pg_namespace.
Im using PG 8.3.5 (Also tested in 8.2.5 with same results)
Any hints?
Thanks!
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] about pg_proc (column pronamespace)
Tom Lane wrote: Gerardo Herzig gher...@fmed.uba.ar writes: Obviously im missing something, i cant find any relation between pg_proc.pronamespace and pg_namespace. You didn't look at pg_namespace.oid, which is a system column meaning SELECT * doesn't show it. regards, tom lane Oh, yeah. A little surprised, but yes there it is! Thanks Tom. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] its posible to use a 2 column returning function as a subquery?
Hi All. My poor english doest not allow me to get a better subject. Here is the thing: I have a function who returns a two column result, that is is used inside another function, like this CREATE FUNCTION show_result((some args), OUT shoe varchar, OUT desc varchar , OUT price numeric) ... as $$ select shoe, (get_desc_and_price(shoe)).desc, (get_desc_and_price(shoe)).price from ... $$ language sql; This is the only way i get this thing working. According to the timming, it looks like get_desc_and_price() is being called twice. There is some syntax for calling get_desc_and_price only once? As getting the 'description' and the 'price' share most of the logic, it looks ok to write a single function that does the job. Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using case to select 'which version to use'
Rodrigo E. De León Plicet wrote: On Thu, Aug 20, 2009 at 10:17 AM, Gerardo Herziggher...@fmed.uba.ar wrote: Hi all. I have 2 functions , which returns the same columns, but with tottaly different logics. I want to wrap it with a third function, who will receive a boolean argument to decide wich sub-function have to use. I want to use case, so i dont have to use pl/pgsql. This is a non-working example: CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, OUT cod varchar , OUT description varchar) returns setof record security definer as $$ select * from case $4 when true then (select * from get_oferta_from_a($1, $2, $3)) else (select * from get_oferta_from_b($1, $2, $3)) end; $$ language sql; Can i use case for a case like this? Thanks! Gerardo Try: SELECT * FROM get_oferta_from_a ($1, $2, $3) WHERE $4 UNION ALL SELECT * FROM get_oferta_from_b ($1, $2, $3) WHERE NOT $4; Well, looks like it will work. I think i found the right syntax now: select foo.* from (select case $4 when false then get_oferta_from_a($1, $2, $3) else get_oferta_from_b($1, $2, $3) end) as foo; Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] multiple substitution in a single replace call?
Hi all. There is a way to simulate the `pipe' in linux so y can use replace() for replacing 2 different things? Or i just have to call replace two times? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] proposal for a CookBook in postgresql.org
I just saw a beatifull answer from Pavel, as an answer to this question: I'm just wondering if there's some way to retrieve the hour column as the sum of the array values... Just like this: hour| statistics_date +- 9000 | 2008-01-03 With this function: postgres=# create or replace function sum_items(bigint[]) returns bigint as $$ select sum($1[i])::bigint from generate_series(array_lower($1,1), array_upper($1,1)) g(i)$$ language sql immutable; CREATE FUNCTION Time: 2,510 ms postgres=# select sum_items(array[1,2,3,4]); sum_items --- 10 (1 row) I think this is a good time to propose some kind of CookBook, to preserve this kind of answers. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
Dave Page wrote: On Mon, May 18, 2009 at 1:29 PM, Gerardo Herzig gher...@fmed.uba.ar wrote: I think this is a good time to propose some kind of CookBook, to preserve this kind of answers. What, like this one? http://wiki.postgresql.org/wiki/Snippets :-) Oops. Yeah, kind of :) Did not see any direct link from Docs main page. Sory about the noise. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] backup and restore
Jyoti Seth wrote: Hello, I have two databases db1 and db2 with the same database structure on different systems with no network connection. In the first system with the db1 database user updates the master data. At the end of every day, the user needs to take the backup of updated data of master tables on db1 and update the data on another system with db2 database. We can't use WAL as in this as we want to take incremental backup of few tables only and can't use slony as there is no network connection between the systems. Please suggest some solution. Thanks, Jyoti I guess you are triyng to avoid the 'pg_dump - save_to_floppy - walk_to_db2_place - pg_restore' pattern. Well, can you save `pg_dump' in some middle place? One place which machine1 and machine2 have access to? If so, you can kind of automate the job using two programs, one (in the master) who 'upload' (via ftp maybe, scp or whatever) in this middle place, ando other in the slave who 'download' and process the file using pg_restore. HTH Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Luigi N. Puleio wrote: Hello everyone... I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE can't be shown on windows with a popup message like the EXCEPTION, indeed it goes to log messages list.. So, is there any way to show a popup message with a custom textmessage on it from a PL/PgSQL function?... Thanks to all in advance, Ciao Luigi That looks like a sql-client (pgAdmin, pgAccess,?) implementation feature, isnt? Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] RAISE NOTICE
Luigi N. Puleio wrote: --- On Fri, 5/8/09, Gerardo Herzig gher...@fmed.uba.ar wrote: From: Gerardo Herzig gher...@fmed.uba.ar Subject: Re: [SQL] RAISE NOTICE To: Luigi N. Puleio npul...@rocketmail.com Cc: pgsql-sql@postgresql.org Date: Friday, May 8, 2009, 11:34 AM Luigi N. Puleio wrote: Hello everyone... I have a PL/PgSQL function where I use RAISE NOTICE but this kind of RAISE can't be shown on windows with a popup message like the EXCEPTION, indeed it goes to log messages list.. So, is there any way to show a popup message with a custom textmessage on it from a PL/PgSQL function?... Thanks to all in advance, Ciao Luigi That looks like a sql-client (pgAdmin, pgAccess,?) implementation feature, isnt? Gerardo Sort ofthe message should come along a standalone application too when with an ADO component I do a Post() which calls the function...just the matter is I can't modify the application to manage a possibly notification...only the postgresql's function... That's why I'm asking about a possibility to get a notification from the function like it's an EXCEPTION... Well, in that case, the ADO component is the client. This is the place to modify de code, to capture the NOTICE. Im shure the ADO (or any other component who actually connect to the database and makes the query) has the ability to react when an EXCEPTION is thrown. My guess is that EXCEPTION's goes trough StandardError, but NOTICE's goes trough StandardOutput, and that could be some idea why your app is not getting notice about NOTICES :) I will wait for some expert dude to respond this, because im going myself into unknown territory. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] finding UNIQUES in information_schema
Hi all. Im failing while trying to obtain some metainfo from information_schema. Acording to the docs, constraint_column_usage and key_column_usage views contains some information about constraints and indexes. See: Table public.almatnov Column| Type | Modifiers -++ formu | integer| alucod | integer| default 0 codcarr | character varying(3) | default ''::character varying anifm | character varying(2) | default 20 comentarios | text | default ''::text Indexes: almatnov_alucod_codcarr_idx UNIQUE, btree (alucod, codcarr) almatnov_codcarr_alucod UNIQUE, btree (alucod, codcarr) And now: ematerias=# SELECT * from information_schema.constraint_table_usage where table_name='almatnov'; table_catalog | table_schema | table_name | constraint_catalog | constraint_schema | constraint_name ---+--+++---+- (0 rows) ematerias=# SELECT * from information_schema.key_column_usage where table_name='almatnov'; constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint +---+-+---+--++-+--+--- (0 rows) 1) Im a doing anything wrong? 2) It is safe to extract metainfo from pg_catalog? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] finding UNIQUES in information_schema
Tom Lane wrote: Gerardo Herzig gher...@fmed.uba.ar writes: Hi all. Im failing while trying to obtain some metainfo from information_schema. Acording to the docs, constraint_column_usage and key_column_usage views contains some information about constraints and indexes. No, they contain information about constraints, period. How did you create those indexes? If you made them via SQL-standard PRIMARY KEY or UNIQUE constraint syntax, they should show in the information_schema. Otherwise not. regards, tom lane Well, i just dont get it. Official docs from 8.2 says: The view key_column_usage identifies all columns in the current database that are restricted by some *unique*, *primary key*, or foreign key constraint. Check constraints are not included in this view. Only those columns are shown that the current user has access to, by way of being the owner or having some privilege. Well, damn, lets create some UNIQUE edatos=# CREATE UNIQUE INDEX aluestud_alu_cod_anifm on aluestud (alucod, codcarr1, anifm); CREATE INDEX edatos=#\d aluestud [...] Indexes: estud_idx1 PRIMARY KEY, btree (alucod, codcarr1) aluestud_alu_cod_anifm UNIQUE, btree (alucod, codcarr1, anifm) And... edatos=# select table_name, constraint_name, column_name from information_schema.key_column_usage where table_name='aluestud'; table_name | constraint_name | column_name +-+- aluestud | estud_idx1 | alucod aluestud | estud_idx1 | codcarr1 (2 rows) Damn, im that idiot? Or should i get the UNIQUE i have just defined also? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] custom serial number
A. Kretschmer wrote:
am Tue, dem 18.11.2008, um 1:09:44 -0800 mailte mahmoud ewiwi folgendes:
hi gurus
i have a problem in generating serial number with the form mm how
can i do that?
test=# create temporary sequence foo;
CREATE SEQUENCE
test=# select to_char(current_date,
'mm')||trim(to_char(nextval('foo'),'0'));
?column?
-
2008111
(1 row)
Thats nice. You should now take care about changing months.
I guess a cron job can do the job restarting the sequence the first day
of the month. (If we are talking about Unix/Linux, off course)
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] trapping a NULL querystring error
Hi all. Inside a plpgsql function, i have a EXECUTE statement, which sometimes seems to be receiving a null querystring. Can i check that condition in a BEGIN..EXCEPTION block? Under which condition? Or should just check if that string is null? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] returning count(*) when it is 1, else -1
Hi all. Im triyng to implement this in plain sql. The only thing i have working is select case when (select count(*) from test where id=$1 ) 0 then (select count(*) from test where id=$1) else -1 end; But it does a doble count(*) that i must avoid. I cant refer to the 'first' count like select case when (select count(*) from test where id=$1 ) AS total 0 then total else -1 end; Because i have Syntax error near AS I have a plpgsql version of this, but i swear to my boss that it can be done is plain sql. Please tell me that im right :) Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is 1, else -1
Richard Huxton wrote: Gerardo Herzig wrote: But it does a doble count(*) that i must avoid. I cant refer to the 'first' count like select case when (select count(*) from test where id=$1 ) AS total 0 then total else -1 end; SELECT CASE WHEN total 0 THEN total ELSE -1 END AS new_total FROM ( SELECT count(*) AS total FROM test WHERE id=$1 ) AS raw_total Pavel, Richard, you got it dudes! I have to say, that kinda 'reference before assingment' of total doesnt look logical to me. Thanks again!! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is 1, else -1
Achilleas Mantzios wrote: Στις Friday 17 October 2008 15:11:10 ο/η Gerardo Herzig έγραψε: Richard Huxton wrote: Gerardo Herzig wrote: But it does a doble count(*) that i must avoid. I cant refer to the 'first' count like select case when (select count(*) from test where id=$1 ) AS total 0 then total else -1 end; SELECT CASE WHEN total 0 THEN total ELSE -1 END AS new_total FROM ( SELECT count(*) AS total FROM test WHERE id=$1 ) AS raw_total Pavel, Richard, you got it dudes! I have to say, that kinda 'reference before assingment' of total doesnt look logical to me. Then, both you and your boss need some SQL courses :) (no offense) Hahaha none taken dude, youre absolutely right. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning count(*) when it is 1, else -1
Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] writes: SELECT CASE WHEN total 0 THEN total ELSE -1 END AS new_total FROM ( SELECT count(*) AS total FROM test WHERE id=$1 ) AS raw_total Actually you could just do SELECT CASE WHEN count(*) 0 THEN count(*) ELSE -1 END AS total FROM test WHERE id=$1; PG has avoided redundant calculations of duplicate aggregates for some time. (This doesn't help in the original formulation because it actually had two different sub-selects; the case that is handled is identical aggregate expressions within SELECT list or HAVING of a single SELECT.) regards, tom lane Thanks Tom! I like this one! It seems more readable to me. Thank you all! Yeah, even you Achilleas bastard (no ofense) :) Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] replicating a table in several databases
Hi all. Im facing a situation where i have to replicate a table from database A in databases B,C,F and so on. The first (and only) thing i have in mind is using triggers with dblink for comunications with the other DB's. I dont even like the idea of replicating tables across databases, but it is kind of an order :( Since dblink is not transactional, it seems error prone over time, so the tables will be inconsistent sooner or later, right? Do any have some less error-prone idea for this? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pg_dump and could not identify an ordering operator for type name
Hi dudes. Im facing a problem with pg_dump, pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not identify an ordering operator for type name HINT: Use an explicit ordering operator or modify the query. pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname as indexname, pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, t.relnatts as indnkeys, i.indkey, i.indisclustered, c.contype, c.conname, c.tableoid as contableoid, c.oid as conoid, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace, array_to_string(t.reloptions, ', ') as options FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN pg_catalog.pg_depend d ON (d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i') LEFT JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) WHERE i.indrelid = '7929896'::pg_catalog.oid ORDER BY indexname Dumping of other databases works fine. Looks like a corrupted internal table, isnt? Any hints? Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple databases
Sumaya wrote: Hi, I am converting sql code from sql server to postgresql. Data is currently being retrieved from multiple databases, how do I do this using postgresql. I have tried using eg. select * from datbasename.tablename but this does not work. Any ideas? Thanks, Sumaya Are you saying that you want a particular function stored in say database A to be able to retrieve some data from dabatase X? If that so, you may take a look to the dblink contrib package. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to returns set of records in PL/python
Anoop G wrote: Hi , Iam new to plpython,how can I return a recordset from a plpython function? Is there is any way give me an example; plpgsql function CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT pk_bint_country_id,vchr_country FROM tbl_country LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; How i can get the same result using plpythonu and how I can call the function (is there any user defined type required like in plpgsql)? Thanks in advance Anoop Yes, plpython can return setofs. You may need to define a new type for that. After that you can return a list, tuple, dictionary, set, generator object, or any You may also check the plpython version, as i recall, its kind of *new* issue. This works on 8.2.5, python 2.5, For a simple case, something like that would work regression=# \d countries Table public.countries Column| Type| Modifiers --+---+--- country_id | integer | country_name | character varying | CREATE OR REPLACE FUNCTION get_countries() returns setof countries security definer as $$ return plpy.execute(select * from countries) $$ language plpythonu; Hope that helps. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to returns set of records in PL/python
Anoop G wrote: Hi , Iam new to plpython,how can I return a recordset from a plpython function? Is there is any way give me an example; plpgsql function CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT pk_bint_country_id,vchr_country FROM tbl_country LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; How i can get the same result using plpythonu and how I can call the function (is there any user defined type required like in plpgsql)? Thanks in advance Anoop Forgot to mention that postgres home page has some info about plpython. http://www.postgresql.org/docs/8.3/static/plpython-funcs.html Cya. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compiling plpython compilation error (solved)
Tom Lane wrote: Gerardo Herzig [EMAIL PROTECTED] writes: Hi all. Im having a hard time trying to compile the plpython package. This is the error make gives me: /usr/lib/python2.5/config/libpython2.5.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC Well, I'd try following the error message's advice: use -fPIC not -fpic. Note that it's not real clear whether this needs to be done for plpython, or libpython, or perhaps both; so you might well be in for making a custom libpython installation. regards, tom lane Yes, i was trying to recompile plpy with -fPIC with no success, recompiling *python* with -fPIC did work. Thanks!! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-sql
[SQL] compiling plpython compilation error
Hi all. Im having a hard time trying to compile the plpython package. This is the error make gives me: [EMAIL PROTECTED]:/usr/local/src/postgresql-8.2.5/src/pl/plpython make gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -shared -Wl,-soname,libplpython.so.0 plpython.o -L/usr/lib/python2.5/config -L../../../src/port -lpython2.5 -lpthread -ldl -lutil -lm -Wl,-rpath,'/usr/lib/python2.5/config' -o libplpython.so.0.0 /usr/lib64/gcc/x86_64-suse-linux/4.2.1/../../../../x86_64-suse-linux/bin/ld: /usr/lib/python2.5/config/libpython2.5.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/lib/python2.5/config/libpython2.5.a: could not read symbols: Bad value collect2: ld returned 1 exit status make: *** [libplpython.so.0.0] Error 1 This runs on OpenSuse 10.3. python 2.5 postgres 8.2.5 ( and 8.3.0) Any clues? Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-sql
Re: [SQL] transaction and triggers
D'Arcy J.M. Cain wrote: On Fri, 18 Jan 2008 12:16:04 -0300 Gerardo Herzig [EMAIL PROTECTED] wrote: Right. But today, that trigger do some other work, wich includes writing some files to disk, so there is my problem. Crap, i guess i will have to review the main logic. I built a replication system that syncs up dozens of systems in a multi-master environment spanning multiple continents in almost real-time and it works flawlessly so don't give up hope. And im trying with 3 virtual machines...this is embarrasing :) It is doable. I can't give you the code because it was written under contract and it was based heavily on our specific business requirements but I can give you a few pointers. You have discovered the basic problem of trying to replicate in full real time. You'll probably have to give up on that. Instead, focus on making updates to the local database. Create a replication table or tables that you update with triggers. Basically this needs to be a log of every change to the database in a structured way. Crap. That was my first approach! I later chose the inmediate file writing, trying to minimize the changes that would be lost in case of primary system crash. I guess i will come with it again. Once you have the replication table(s) you can create external programs that connect to the master and update the slave. In the slave you can track the last ID that completed. Do the insert/update/delete in a transaction so that you have a guarantee that your database is up to date to a very specific point. Note that you can have multiple slaves in this scenario and, in fact, the slaves can have slaves using the exact same scheme giving you a hierarchy. If you need multi-master you just need to have another process to feed your local changes up to the master. This is not just a matter of making the master a slave though. If you do that you get into a feedback loop. Also, if you need multi-master, you have to think about your sequencing. If you need unique IDs on some tables you will have to think about setting up ranges of sequences based on server or have a central sequence server. We used a combination of both as well as specifying that certain tables could only be inserted to on one system. Of course, this system doesn't need to be the same as the top of the hierarchy and, in fact, different tables can have different generator systems. What i want to do is something like: If the master fails, it will be a peace of soft that would change the conf files (which indicate who's the master, slaves, and so on), so one of the slaves take the master's place. Since those are a common pc, when the real master come back to life, it has to be re-sync, and take his place as the master again. Im thinking in something as simple as posible (since im not a senior programmer), something like a ip address change could do the trick Hope this gets you started. There's still lots of gotchas on the way. Oh yes, im specting so much fun Thanks for sharing your knowledge with us!! Mamooth replicator, Slone-I, feel the fear! :) Thanks again, D'arcy! Gerardo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] transaction and triggers
Filip Rembiałkowski wrote: 2008/1/18, Gerardo Herzig [EMAIL PROTECTED]: Hi all. Im puzzled again. Just thinking: As im having fun trying to make my own replication system, im stuck in this situation: Consider a simple table with a unique index on the `id' field, and a function who will fail, such as insert into test (id) values (1); insert into test (id) values (1); This will fail and the transaction will be rollback'ed, but as the basis of my replication system is on row level triggers, the first time the insert is called, the trigger will be executed, and i will like to be able to stack the triggers in some way, in order to be fired only after a succesfull execution of the hole function. If the transaction is rolled back, changes made by your trigger to local database will be also canceled. Unless you make any manipulation on remote databases, you have no problem. Any changes made to remote databases, for example if you call some dblink functions, are not transactional, and will not be rolled back. In this case you have to rethink your design, as there is no ON COMMIT trigger (yet?) Right. But today, that trigger do some other work, wich includes writing some files to disk, so there is my problem. Crap, i guess i will have to review the main logic. Thanks! Gerardo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] transaction and triggers
Hi all. Im puzzled again. Just thinking: As im having fun trying to make my own replication system, im stuck in this situation: Consider a simple table with a unique index on the `id' field, and a function who will fail, such as insert into test (id) values (1); insert into test (id) values (1); This will fail and the transaction will be rollback'ed, but as the basis of my replication system is on row level triggers, the first time the insert is called, the trigger will be executed, and i will like to be able to stack the triggers in some way, in order to be fired only after a succesfull execution of the hole function. Im also reading the NOTIFY/LISTEN mechanism and the rule system as a workarround on this, but the fact is that there is a lot of client code, and will take a big amount of time to change it. Any sugestions? Thanks! Gerardo ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] transaction and triggers
Alvaro Herrera wrote: Gerardo Herzig escribió: Right. But today, that trigger do some other work, wich includes writing some files to disk, so there is my problem. Crap, i guess i will have to review the main logic. Probably it's better to move the actual file writing to a listener external process -- the transaction only does a NOTIFY, which is certain to be delivered only when the transaction commits. So if it aborts, no spurious write occurs. Mmmhmm, sounds good...I will give it a try on monday. Now its beer time :) Thanks all. Gerardo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] obtaining the query string inside a trigger
Hi all. Im working on a on each statement update trigger, so NEW and OLD are NULL. Suppose a simple query like 'update mytable set id=id+500 where id 50'...There is a way to obtaining the 'set id=..' and the where clause in some way? Thanks! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL stored function inserting and returning data in a row.
Daniel Caune wrote:
Hi,
Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?
CREATE TABLE matchmaking_session
(
session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'),
...
);
CREATE FUNCTION create_matchmaking_sesssion(...)
RETURNS bigint
AS $$
INSERT INTO matchmaking_session(...)
VALUES (...)
RETURNING session_id;
$$ LANGUAGE SQL;
2008-01-10 22:08:48 EST ERROR: return type mismatch in function
declared to return bigint
2008-01-10 22:08:48 EST DETAIL: Function's final statement must be a
SELECT.
2008-01-10 22:08:48 EST CONTEXT: SQL function
create_matchmaking_sesssion
What about
$$
INSERT INTO ;
select currval('seq_matchmaking_session_id');
$$ language sql;
?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] trigger for TRUNCATE?
Pavel Stehule wrote:
On 08/01/2008, Chris Browne [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] (Gerardo Herzig) writes:
Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger
on the table being truncated.
There is a way to capture a TRUNCATE in any way?
I think there's some sort of to do on that...
It ought to be not *too* difficult (I imagine!) to be able to
associate a trigger with the TRUNCATE action, and therefore run some
stored function any time TRUNCATE takes place.
For the Slony-I replication system, it would be attractive for this to
lead to attaching two functions:
- One function would return an exception so that TRUNCATE against
a subscriber node would fail...
- Another would pretty much be as simple as submitting an event;
perform createEvent('_ourcluster', 'TRUNCATE_TABLE', table_id);
A new event, TRUNCATE_TABLE, would do a TRUNCATE against the
subscribers.
This represents a pretty easy enhancement, given the new kind of
trigger.
--
(reverse (concatenate 'string moc.enworbbc @ enworbbc))
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Frisbeetarianism: The belief that when you die, your soul goes up on
the roof and gets stuck...
Hello
theoretically you can have trigger on any statement, but I am not sure
about conformance with std. But, you can wrap TRUNCATE statement into
some procedure, and then call this procedure with some other actions.
Regards
Pavel Stehule
Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level
thing than i think.
Gerardo
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Gerardo Herzig escribió: Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level thing than i think. TRUNCATE currently does not fire triggers, but that doesn't mean it's impossible to do it. I think it would be fairly easy to add support for that. The entire point of TRUNCATE is to not do a table scan, so making it fire per-row triggers seems pretty misguided to me. We could maybe make it fire per-statement ON DELETE triggers, but there's a future-proofing pitfall in that: someday it'd be nice for statement-level triggers to have access to the set of deleted rows, and then you'd be stuck either scanning the table or having TRUNCATE act differently from plain DELETE. My feeling is that if you want to know what was deleted, you shouldn't use TRUNCATE. regards, tom lane I 100% agree, i can live using delete instead, but i can't ensure the whole team i work with will not use TRUNCATE. It was my bad naming the thread with such a contradictory name, im just looking the way to capture it in any form. I would even consider the posibility of *ignoring* a TRUNCATE command, if thats possible. Thanks you all, dudes! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] trigger for TRUNCATE?
Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger on the table being truncated. There is a way to capture a TRUNCATE in any way? Thanks! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] reading WAL files in python
Hi all. Im having some fun trying to write my own replication system using python. I will use the postgres own WAL archiving to write the files, then my app will read them and do some stuff. As im not a C programmer, im stuck in the mission of reading the binary files. I guess im needing to know the internals of how wals archives are, how big the buffer has to be, in order to have a complete sql command. Can someone point some advice? I was trying to understad src/backend/access/transam/xlog.c, but seems too difficult to me :( Thanks! Gerardo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] reading WAL files in python
C. Bergström wrote: On Mon, 2008-01-07 at 10:19 -0300, Gerardo Herzig wrote: Hi all. Im having some fun trying to write my own replication system using python. I will use the postgres own WAL archiving to write the files, then my app will read them and do some stuff. As im not a C programmer, im stuck in the mission of reading the binary files. I guess im needing to know the internals of how wals archives are, how big the buffer has to be, in order to have a complete sql command. Can someone point some advice? I was trying to understad src/backend/access/transam/xlog.c, but seems too difficult to me :( imho you can save yourself a lot of time by taking a look at skytools[1] as it may be more along the lines of what you're trying to accomplish anyway. Cheers, ./C [1] https://developer.skype.com/SkypeGarage/DbProjects/SkyTools Oh yes, you already told me about skytools. But im also trying to learn more about postgres and python, and this seems to a great oportunity to do that. I dont mind if im reinventing the wheel, i usually have fun doing it. Thanks! Gerardo ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] WAL archiving blowed.
Hi all. Im trying to set a Standby server for HA. I have set the program to copy the WAL files to the standby server, but because im so stupid, for some stupid reason i delete those files from the standby server, and now, when i set the recovery_command at the standby server, it tells me that cant get 'stat' over 0100: No such file. I guess i have at least 2 easy options: 1) Telling the master server to restart the file naming procedure, and 2) Telling the standby server which is the 'first' filename to recover from. But cant find information about this 2 options Any clues? Thanks! Gerardo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpythonu and hello concurrent world
Tom Lane wrote: Gerardo Herzig [EMAIL PROTECTED] writes: Hi all. Im having some problems with a small concurrent plpython function. Don't even *think* about starting multiple threads inside the Postgres backend. It's an excellent way to break things. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Damn!! Well, comming from you Tom, i guess i will just look for another approach without complaining ^^. Thanks! Gerardo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] plpythonu and hello concurrent world
Hi all. Im having some problems with a small concurrent plpython function.
Based on a small example [1] about concurrent programming, there is some
code which works fine under python:
#! /usr/bin/python
import threading, random
import time
def myPrint(str):
print 'searching...', str
time.sleep(random.randint(10, 1) / 1000.0)
print str, 'OK!'
myThreads = (threading.Timer(random.random(), myPrint, [hello]), \
threading.Timer(random.random(), myPrint, [concurrent]), \
threading.Timer(random.random(), myPrint, [world]))
for thr in myThreads:
thr.start()
[EMAIL PROTECTED]: python pp.py
searching... concurrent
searching... world
searching... hello
hello OK!
concurrent OK!
world OK!
So far, so good. Almost the same example in plpythonu:
CREATE OR REPLACE FUNCTION search_t()
returns bigint
security definer
as
$$
import threading, random
import time
def myPrint(str):
plpy.notice ('searching...', str)
time.sleep(random.randint(10, 1) / 1000.0)
plpy.notice(str, 'OK!')
myThreads = (threading.Timer(random.random(), myPrint, [hello]), \
threading.Timer(random.random(), myPrint, [concurrent]), \
threading.Timer(random.random(), myPrint, [world]))
for thr in myThreads:
thr.start()
return 1
$$ language plpythonu;
gse_new_version=# select * From search_t();
search_t
--
1
(1 row)
Looks like myPrint() is not executing at all!!
Have no idea why, so i decided writing both on python and postgres
forums. Any ideas??
Postgres 8.1.3
python 2.5.1
Thanks!!
Gerardo
[1]
http://forums.hostrocket.com/showthread.php?t=13325
---(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: [SQL] statement-level trigger sample out there?
Stephen Cook wrote: I am curious (coming from a MS SQL Server background, I just started playing with PostgreSQL recently). What type of situation would warrant a statement-level trigger that can't access the old and new values? Without that access, isn't the only information you get is the fact that an operation occurred on the table? Or am I missing something? -- Stephen What about this. Suppose you have this table planets: planet_name | star_id| There is a lot of stars, right? And if a very common query involves a select planet_name, count(*) from planets group by star_idWell, if there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per galaxy...Thats a lot of planets to count!!! So maybe you want a helper table who maintains such of subtotals. Well, each time you discover a new galaxy, insert every planet in the monster table, and *after* all the inserts, run a trigger for updating the helper table. Cheers. Gerardo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] statement-level trigger sample out there?
Alvaro Herrera wrote: Gerardo Herzig escribió: Stephen Cook wrote: I am curious (coming from a MS SQL Server background, I just started playing with PostgreSQL recently). What type of situation would warrant a statement-level trigger that can't access the old and new values? Without that access, isn't the only information you get is the fact that an operation occurred on the table? Or am I missing something? What about this. Suppose you have this table planets: planet_name | star_id| There is a lot of stars, right? And if a very common query involves a select planet_name, count(*) from planets group by star_idWell, if there is 1.000.000.000 of galaxies, and 1.000.000.000.000 of stars per galaxy...Thats a lot of planets to count!!! So maybe you want a helper table who maintains such of subtotals. Well, each time you discover a new galaxy, insert every planet in the monster table, and *after* all the inserts, run a trigger for updating the helper table. Right, but it would be much more useful if you can access the NEW set and instead of counting all the planets from scratch, you just take the current count and add the number of planets being added. You can do it with FOR EACH ROW triggers, but it's much worse because you need one UPDATE on the counter for each new planet. Perhaps the usefulness is that you store _in memory_ the number of planets added during the FOR EACH ROW trigger, and when that's done, call the FOR EACH STATEMENT trigger that does a single update adding the number in memory. This would work only if the FOR EACH STATEMENT trigger was promised to be executed after all the FOR EACH ROW triggers were called. Shure. In that case, i will do the initial inserts into a temporary table, do the counting, updating the helper table, and then insert into the planets table. I use that approach and works fine to me. Gerardo ---(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
[SQL] pg_clog (?) problem with VACUMM
Hi all. Well, im having this problem for the first time.
When executing a VACUUM, i have this msg:
gse_new_version=# VACUUM ;
ERROR: could not access status of transaction 118
DETAIL: could not open file pg_clog/: No existe el fichero o el
directorio
Ok, wtf, i will create the file. So touch pg_clog/ and VACUUM again
ERROR: could not access status of transaction 118
DETAIL: could not read from file pg_clog/ at offset 0: Conseguido
I hope the error will still be clear due to the word Conseguido, wich
will be something like 'acomplished', or 'obtained'
The database was created making a with template=gse modifiers.
Vacuuming the 'gse' database gives me no errors at all.
Here is the final lines from strace when triyng to vacuum...
lseek(20, 0, SEEK_SET) = 0
read(20, , 8192) = 0
close(20) = 0
rt_sigprocmask(SIG_SETMASK, ~[QUIT ILL TRAP ABRT BUS FPE KILL SEGV CONT
STOP SYS RTMIN RT_1], NULL, 8) = 0
stat(/etc/localtime, {st_mode=S_IFREG|0644, st_size=377, ...}) = 0
stat(/etc/localtime, {st_mode=S_IFREG|0644, st_size=377, ...}) = 0
stat(/etc/localtime, {st_mode=S_IFREG|0644, st_size=377, ...}) = 0
write(2, 2007-11-27 16:54:23 ART, gse_ne..., 346) = 346
sendto(8, E\0\0\0\240SERROR\0CXX000\0Mcould not ac..., 161, 0, NULL,
0) = 161
munmap(0x2aaabffa9000, 839680) = 0
munmap(0x2aaabdfa8000, 33558528)= 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
sendto(7, \4\0\0\0\310\3\0\0\1\0\0\0\2731\0\0 S\255\0\r\0\0\0+\0...,
968, 0, NULL, 0) = 968
sendto(7, \4\0\0\0\310\3\0\0\1\0\0\0\2731\0\0 S\255\0\r\0\0\0\0\0...,
968, 0, NULL, 0) = 968
sendto(7, \4\0\0\0\310\3\0\0\1\0\0\0\2731\0\0 S\255\0\r\0\0\0\0\0...,
968, 0, NULL, 0) = 968
sendto(7, \4\0\0\0\310\3\0\0\1\0\0\0\2731\0\0 S\255\0\r\0\0\0\0\0...,
968, 0, NULL, 0) = 968
sendto(7, \4\0\0\0\370\0\0\0\1\0\0\0\2731\0\0 S\255\0\3\0\0\0\0\0...,
248, 0, NULL, 0) = 248
sendto(7, \4\0\0\0008\3\0\0\1\0\0\0\2731\0\0\0\0\0\0\v\0\0\0\0\0...,
824, 0, NULL, 0) = 824
sendto(7, \3\0\0\0\27\0\0\0\1\0\0\0\2731\0\0IDLE\0, 23, 0, NULL, 0) = 23
sendto(8, Z\0\0\0\5I, 6, 0, NULL, 0) = 6
recvfrom(8,
Dont know what to do now.
Wait...i have some more.
When vacuuming a table at a time, i got the table making vacuum crash.
It's this one:
gse_new_version=# \d words
Table public.words
Column | Type| Modifiers
---+---+---
page_id | integer |
word | character varying |
word_position | integer |
original_word | character varying |
Indexes:
words_idx btree (upper(word::text))
words_page_id_idx btree (page_id)
words_page_id_word_position_id btree (page_id, word_position) CLUSTER
words_upper_idx btree (upper(word::text) varchar_pattern_ops)
Im souspiciuos about the CLUSTER (dont ask why :-). Meanwhile im looking
for 'unclustering' tables and triyng again.
Server version: 8.1.2
On SuSE 9.1
Some other ideas will be apreciated.
Thanks!!
Gerardo.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] pg_clog (?) problem with VACUMM
Gregory Stark wrote: Gerardo Herzig [EMAIL PROTECTED] writes: Hi all. Well, im having this problem for the first time. When executing a VACUUM, i have this msg: gse_new_version=# VACUUM ; ERROR: could not access status of transaction 118 DETAIL: could not open file pg_clog/: No existe el fichero o el directorio [] Ok, wtf, i will create the file. So touch pg_clog/ and VACUUM again ERROR: could not access status of transaction 118 DETAIL: could not read from file pg_clog/ at offset 0: Conseguido That's almost the right idea for a last ditch attempt to extract what data you can from a corrupted table. You have to fill the file with nul bytes though. Something like dd if=/dev/zero of= bs=1k count=nnn where nnn is, uh, I'm not sure how large, it won't take much to cover transactionid 118 though. Mmmm, yeah, after dd'ing the file, VACUUM execute just fine!! BTW, a previous atempt to build a CLUSTER gives me the same error as with VACUUM, so i dont think it was a VACUUM issue. Thanks Gregor for your help!! Gerardo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] puzzled by SELECT INTO
Rodrigo De León wrote:
On 10/30/07, Gerardo Herzig [EMAIL PROTECTED] wrote:
Wreird enough to me, need some advice plz!
CREATE OR REPLACE FUNCTION READ_WORDS(BIGINT, INT[])
RETURNS VARCHAR
AS
$$
DECLARE
RETURNVALUE VARCHAR;
BEGIN
SELECT ARRAY_TO_STRING(ARRAY(
SELECT WORD
FROM WORDS WHERE PAGE_ID=$1
AND WORD_POSITION = ANY ($2)
), ' ') INTO RETURNVALUE;
RETURN RETURNVALUE;
END;
$$ LANGUAGE PLPGSQL;
SELECT READ_WORDS(99466, '{2994,2995,2996}');
See:
http://www.postgresql.org/docs/8.2/static/arrays.html
Mmmm, yes, that make perfect sense. I did 'resolve' the previous
situation by using EXECUTE, i will try your solution now.
Gracias Rodrigo.
Gerardo
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] puzzled by SELECT INTO
Hi all. Im having some trouble here that cannot understand. Consider this function: CREATE OR REPLACE FUNCTION read_words(bigint, varchar) returns varchar as $$ declare returnValue varchar ; BEGIN select * into returnValue from array_to_string(array(select word from words where page_id=$1 and word_position in ($2)), ' '); return returnValue; END; $$ language plpgsql; So far, so good. But... select * from read_words(99466::bigint, '2994,2995,2996'); read_words (1 row) But...if i do a select * from array_to_string(array(select word from words where page_id=99466 and word_position in (2994,2995,2996)), ' ') array_to_string - man page inside Means that the query itself seems OK, but something in the SELECT INTO thing is not working to me. Mmmm...i guess is not that. I just make the sql version of that function CREATE OR REPLACE FUNCTION read_words(bigint, varchar) returns varchar as $$ select * from array_to_string(array(select word from words where page_id=$1 and word_position in ($2)), ' '); $$ language sql; with the same (NULL) resultsLooks like im having some mistake near 'and word_position in ($2)...' Wreird enough to me, need some advice plz! Thanks! Gerardo ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Inconsistent sql result
Patrick De Zlio wrote: Hi listers, As a PG administrator, I'm trying to read technical data from pg_class table to monitor tables and indexes space. We are running a quite big postgres platform, with multiple databases, multiples schemes in each database, and hundreds tables. When I run the attach python script, I get 1809 rows as result. When I run the included (from the script) select query from phpPgAdmin or pgAdmin III, I get 2010 rows as result. When I try to focus on specific table including where relname ='tablename' in both parts of the join, I also get different numbers of rows. So I can't have the full size of all indexes attached on a table. Does anyone has a clue of why the same query, on same database gives different result depending on it is included in a python script, or ran from a console? Many Thanks Patrick #!/usr/bin/python2.4 # import sys import pgdb DEBUG = True global db # # Database access # def opendb(): global db # connect to database port='5432' username='xxx' dbname='xxx' host='xx.xx.xx.xx' password='xxx' try: db = pgdb.connect(database=dbname,host=host, user=username, password=password) except Exception, detail: db.rollback() if DEBUG: print 'Error occured while connecting to database : %s' % detail sys.exit(0) # # Close Database # def closedb(): global db # Commit all changes before closing db.commit() db.close() if __name__== '__main__': # # Main # opendb() query = SELECT relname, relnamespace, relkind, relfilenode, relpages, reltoastrelid, relname AS idx_table_name FROM pg_class UNION SELECT pg_c_i.relname, pg_c_i.relnamespace, pg_c_i.relkind, pg_c_i.relfilenode, pg_c_i.relpages, pg_c_i.reltoastrelid, pg_c_i_o.relname AS idx_table_name FROM pg_class pg_c_i, pg_index pg_i, pg_class pg_c_i_o WHERE pg_c_i.relfilenode = pg_i.indexrelid AND pg_i.indrelid = pg_c_i_o.relfilenode cur = db.cursor() cur.execute(query) tables_details = cur.fetchall() nb_tables = len(tables_details) for table in tables_details: print table print Tables count=,nb_tables closedb() Hi Patrick: I tried your script and have the expected behaviour (both results are identical). I didnt use pg_admin nor pgaccess, i just use the psql. I have tried using pgdb and PyGresql, having the exact (good) behaviour. So, sory but have to ask: Are you reaally shure that you are executing the query on the same database? Python postgres are basicly not much but wrappers to C functionality, thats why the error you post looks very strange to me. I tried it on - python 2.4 - postgres 8.1.3 - pgdb and pyGreSql libraries Can you check that? If you are really executing the exactly same query on the exacly same database, you could build 2 temporary tables with the query results, and then look for the diff and try to figure out what the diffs are, and continue watching, i dont know, if you need to add a search_path in order to search on all the schemas or something. Cheers. Gerardo ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Install two different versions of postgres which should run in parallel
Check the postgresql.conf in the $prefix/data dir. Also post the exact error log (which in this case should be some like address already in use, because port 5432 is in use by the 8.2.4 version), and also the exact command you are executing. Cheers. Gerardo I didn't succeed installing the two versions of postgres. For installing I am running the configure script as follows: ./configure --prefix=/usr/local/pgsql-7.4.5 --with-java --with-pgport=6947 Although I specify a different port than the default one, the postgres it is installed with the default port. The port must be specified also in another configuration files? Regards, Loredana ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Install two different versions of postgres which should run in parallel
Loredana Curugiu wrote: Hi all, I need to have two different vesions of postgres running in parallel on different ports. Does anyone knows how to install two different versions of postgres (7.4.5 and 8.2.4) on the same computer? I am using Linux operating system. Any information would greatly be appreciated. Loredana Shure. You just have to take care about --prefix (executable path) and --with-pgport (default port) when you ./configure, and also take care of the datadir you chose when executing initdb. Good luck. Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] current_date / datetime stuff
We should have a onliner contest. I love oneliners!!!
oneliner:
select date_trunc('month',now()) + ((8 - extract('dow' from date_trunc
('month',now()))||'days')::text)::interval;
Kristo
On 04.06.2007, at 19:39, Michael Glaesemann wrote:
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] logging amount rows retrieved?
Hi all. Im wondering if there is a way to include in the postgresql logs the total rows retrieved after a query (im logging the query itself right now). Something like apache does when it logs the amount of bytes retrieved after a GET. Thanks! Gerardo ---(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
[SQL] union with count?
Hi dudes, i have the following question i have 2 tables with the same format, and i want to know if is there a possibility of using some sort of count(*) for achieving this result: select a from table1 where (condition) union select a from table2 where (condition), count(a) group by a The idea is having how many times (condition) is true for both tables. Im not shure im explaining myself clearly, my english is not the best (as you can see ;) Thanks! Gerardo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] union with count?
Thanks! But now i have another problem related with count():
select page_id, word, word_position, count(page_id) from (select * from
search_word('word1', 'table1') union search_word('word2', 'table2')) foo
group by page_id;
and gives me foo.word must appear in GROUP clause or be used in an
aggregate function
And i want to group by page_id only, because that is what i need to
count. Tips here?
Thanks again man.
Gerardo
am Wed, dem 28.03.2007, um 11:04:29 -0300 mailte Gerardo Herzig folgendes:
Hi dudes, i have the following question
i have 2 tables with the same format, and i want to know if is there a
possibility of using some sort of count(*) for achieving this result:
select a from table1 where (condition) union select a from table2 where
(condition), count(a) group by a
The idea is having how many times (condition) is true for both tables.
Im not shure im explaining myself clearly, my english is not the best
(as you can see ;)
You can use a subselect, a simple example:
test=# select *, count(1) from (select 1 union select 2 union select 3) foo
group by 1;
?column? | count
--+---
1 | 1
2 | 1
3 | 1
(3 rows)
Andreas
---(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
[SQL] log file permissions?
Hi dudes. I have my pg log file rotation configured so i have a psql_`today`.log. The thing is, can i configure postgres so the file permissions (today is 600) could by 640, so developers could login and tailf the logfile (without using the postgres superuser, course)? Thanks! Gerardo ---(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: [SQL] index not being used. Why?
Someday i will ask some question that will makes you think a little more. You'll see. I will populate the tables and redo the explain. Thanks dudes!! Gerardo Gerardo Herzig [EMAIL PROTECTED] writes: Watch the last row of the explain command. It makes a sequential scan on the pages table, like it is not using the index on the id field. The result itself is OK, but i will populate the tables so i think that later that sequential scan would be a problem. Why do you think that the plans won't change when the tables get bigger? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] best index for ~ ordering?
Hi all, i have a large table with one varchar field, and im triyng to get information about what index should i use in order to using that index (if this is possible) for ~ searching, and/or using LIKE searching. Thanks! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] spliting a row to make several rows
Hi all: What a want to do is something like this: suppose i have this record aa--bb--cc I guess if im able to do some sql/plsql procedure to get something like it aa bb cc (3 records, rigth?) Thanks a lot Gerardo ---(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: [SQL] spliting a row to make several rows
Cool!! Thanks a lot! I will try it as soon as possible
Gerardo
Gerardo Herzig wrote:
Hi all: What a want to do is something like this:
suppose i have this record
aa--bb--cc
I guess if im able to do some sql/plsql procedure to get something
like it
aa
bb
cc
(3 records, rigth?)
Thanks a lot
Gerardo
dev=#select split_to_rows('aa--bb--cc','--');
split_to_rows
---
aa
bb
cc
(3 rows)
This function was written by David Fetter,
http://archives.postgresql.org/pgsql-general/2005-12/msg00080.php
CREATE OR REPLACE FUNCTION split_to_rows(TEXT,TEXT) RETURNS SETOF TEXT
AS $$
SELECT (string_to_array($1, $2))[s.i]
FROM generate_series(
1,
array_upper(string_to_array($1, $2), 1)
) AS s(i);
$$ language sql strict;
---(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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
