Re: [GENERAL] Lost password
It is assumed you are running Unix (or Linux). The commands you've been provided with are standard Unix system commands that will help you find the files (pg_hba.conf) you are looking for, the output of these commands (locate or find) is the absolute path to your queried file (in your case pg_hba.conf) if found. Once you find the file you will then need to edit this file, this file may only be accessible by user root or user postgres (I could be wrong). Once edited you may want to restart PostgreSQL then try to log in. For help on how to edit the pg_hba.conf file (and to understand what each section if it's contents means) you will need to look at the PostgreSQL documentation. If you still have difficulties you may what to Google for more elaborate answers or use the man command. Allan. On Tue, Dec 16, 2008 at 7:20 AM, Martin Roach martin_roach2...@yahoo.com.au wrote: Hi I started the installation of postrgres got distracted and then started again but forgot my password. I have received the info below: Now what? If you have access to shell account on the machine PostgreSQL is running, and your shell works as the same user as Postgres itself, or root - solution is easy. Find your pg_hba.conf file. It might be in many files so try: $ locate pg_hba.conf find /var/lib/ -type f -name pg_hba.conf find /etc -type f -name pg_hba.conf find / -type f -name pg_hba.conf Of course last option is your last resort - it will take a long time. However, i'm not that computer savvy and don't now how to go about the above. How come you can't just delete the software and try again? Any help in finding these files and working what to do would be a massive help. Thanks. Start your day with Yahoo!7 and win a Sony Bravia TV. Enter now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this a security risk?
I would like to provide a limited view of my database to some users,
so i thought of creating a second database (I can control access by IP
address through pg_hba.conf) with some views that queried the first
database using dblink.
The problem is that dblink requires non-superusers to provide a
password, but i would like to use the authentication from the first
database connection in the second dblink connection.
I can do this with the example below, but i was wondering is this a
really bad idea or does it create a security hole?
Example code:
CREATE DATABASE test1;
CREATE DATABASE test2;
\c test1
CREATE TABLE test (id int);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
\c test2
CREATE OR REPLACE FUNCTION my_func() RETURNS SETOF record
AS $$
DECLARE
_username text;
_query text;
_row record;
old_path text;
BEGIN
old_path := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', 'public, pg_temp',
true);
SELECT INTO _username session_user;
_query := 'SELECT * FROM dblink(''dbname=test1'', ''SET SESSION
AUTHORIZATION ' || _username || ';';
_query := _query || ' SELECT * FROM test'') ';
_query := _query || ' AS t1(id int);';
FOR _row IN EXECUTE _query LOOP
RETURN NEXT _row;
END LOOP;
PERFORM pg_catalog.set_config('search_path', old_path, true);
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;
SELECT * FROM my_func() AS (id int);
thanks for any help
adam
--
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] tup_returned/ tup_fetched
El Martes, 16 de Diciembre de 2008 04:02:09 Sebastian Böhm escribió: sorry I was not very precise. My question is what does it actually mean in the end for these two functions: pg_stat_get_db_tuples_returned(oid) bigint Number of tuples returned for database pg_stat_get_db_tuples_fetched(oid) bigint Number of tuples fetched for database I read the description below, but I don't completely understand the difference. From 8.2 docs pg_stat_get_tuples_returned(oid) bigint Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index pg_stat_get_tuples_fetched(oid) bigint Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index I want to plot a realtime graph: tuples read from buffers or disk is it both combined ? Thanks Sebastian Am 15.12.2008 um 04:28 schrieb Gregory Stark: Sebastian Böhm s...@exse.net writes: one question: what actually is tup_returned and tup_fetched ? RTFM -- admittedly it's not so easy to find these since you have to know they come from the following functions: pg_stat_get_tuples_returned(oid) bigint Number of rows read by sequential scans when argument is a table, or number of index entries returned when argument is an index pg_stat_get_tuples_fetched(oid) bigint Number of table rows fetched by bitmap scans when argument is a table, or table rows fetched by simple index scans using the index when argument is an index From: http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- No imprima este correo si no es necesario. El medio ambiente está en nuestras manos. __ Clist UAH a.k.a Angel __ Te has metido en un hipoteca de 70M y encima te roban una panda de Ninjas... Crisis Ninja para Dummies -- 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] Trigger/Rules Order of operations
I can't answer your question but I think you may have a serious database design issue at hand. Why not try to accomplish your goals in a simpler way? Regards, Ivan Pavlov On Dec 15, 12:49 pm, ket...@ketema.net (Ketema Harris) wrote: I am interested in finding out the pros, cons, pitfalls of using the following design: Manual insert into Table A. Table A has a BEFORE INSERT trigger that causes an insert to table B. Table B has an AFTER INSERT trigger that causes an insert back to table A (With different criteria not an endless loop) Table A will have its Before Trig fire again and this time the criteria causes it to finish with a return new. Will the second insert into table A commit before the first insert into table A? What order does the insert into table B finish up? Ketema J. Harriswww.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general -- 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] Logg errors during UPDATE
Neiter LOG ERRORS nor REJECT LIMIT are implemented in PostgreSQL, though I agree they may be useful. Both can be simulated with a custom stored procedure which loops over a cursor and updates row-by-row, trapping errors along the way. This will, of course, be slower. regards, Ivan Pavlov On Dec 12, 4:34 am, spam_ea...@gmx.net (Thomas Kellerer) wrote: Hi, with Oracle I have the ability to tell the system to log errors during a long transaction into a separate table and proceed with the statement. This is quite handy when updating large tables and the update for one out of a million rows fails. The syntax is something like this: UPDATE affecting a lot of rows LOG ERRORS INTO target_log_table; Any row that can not be updated will logged into the specified table (which needs to have a specific format of course) and the statement continues. You can add a limit on how many errors should be tolerated. This works for INSERT and DELETE as well. Is there something similar in Postgres? Or a way how I could simulate this? Cheers Thomas -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general -- 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 restrict select on a view ?
Merlin Moncure schrieb: what does this get you? Not as much as I first hoped. In hindsight it was a silly question. The point was that the view gets linked into an Access-Client. There are some Users who shouldn't bother to figure over the results as it does some performance statistics. The users arent able to install other clients like pgAdmin to enter the DB w/o Access. Most of them are likely not interested enough to figure out how one would create a query anyway. A query-object just waiting to get clicked in an idle moment is another thing though. Thanks a lot anyway as I learned 1-2 thingies :) -- 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] Trigger/Rules Order of operations
On Dec 16, 10:31 am, Ivan Pavlov ivan.pav...@gmail.com wrote: I can't answer your question but I think you may have a serious database design issue at hand. Why not try to accomplish your goals in a simpler way? Regards, Ivan Pavlov On Dec 15, 12:49 pm, ket...@ketema.net (Ketema Harris) wrote: I am interested in finding out the pros, cons, pitfalls of using the following design: Manual insert into Table A. Table A has a BEFORE INSERT trigger that causes an insert to table B. Table B has an AFTER INSERT trigger that causes an insert back to table A (With different criteria not an endless loop) Table A will have its Before Trig fire again and this time the criteria causes it to finish with a return new. Will the second insert into table A commit before the first insert into table A? What order does the insert into table B finish up? Ketema J. Harriswww.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general I am all for simple, but some times there is not a simple answer. Complex business rules don't always have a simple solution. And complex design is not necessarily bad design. I am tasked with creating a transaction system that has a lot of things occur automatically after certain input. In analyzing the task I saw two main paths. Use the trigger and rule system that Pg provides or construct external methods to control the logic and issue simple commands to insert data when appropriate. I chose to use the trigger and rule system because the DB is built for transactional applications. Isn't that the point of ACID and atomicity and all those other buzzwords? I did not want to have to recreate what the database already can do. I just want to make sure that my understanding of what I think is going to happen is on target, and I'm looking for experience from others, as well as the tests I am performing. So far it is working as expected, I'd appreciate any feedback from anyone who has done something similar to avoid stepping in the same potholes others may have discovered. Thanks -- 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] View vs Constantly Updated Table
How often are you using the bank balance value? I have no data on this as of yet, but it obviously needs to be correct for when the user looks at it. The opposite argument, is how long does the computation take? The computation is simple, however the amount of data that goes into it grows as there are more and more transactions added. This is why I was thinking of storing the balance in a table sort of as a cache to avoid spinning through all pas transaction to get the current balance. and how quickly do you need it? as fast as possible of course :) Also, if you would be taking any action with the value returned by the balance calculation, remember to lock any tables necessary to ensure the balance doesn't change between the time you compute it and the time you act on it. I'll keep this in mind. never done a lot of manual locking before, but I can see where this is going to be needed. Its possible to take row locks and not complete table locks correct? (manual chapter 13.3) I'm thinking if I use a single table to hold all users cached balances then I would not want to lock the entire table just to retrieve and act on one users balance. thanks for the input On Dec 15, 2008, at 11:27 AM, Adam Rich wrote: Truncated... -- 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 restrict select on a view ?
On Mon, Dec 15, 2008 at 9:38 PM, Klint Gore kgo...@une.edu.au wrote: Andreas wrote: I'd like to have a view only to be used by certain users. The tables are public. Can this only be done by restricting access to the tables? GRANT/REVOKE works on views revoke all on aview from public; grant select on aview to user1; As Raymond pointed out, if user2 knows what the definition of aview is, they can just run it against the raw tables. e.g. create view aview as select * from pg_proc; revoke all on aview from public; grant select on aview to user1; set session authorization user2; select * from aview; -- fails select * from pg_proc; -- works and gives the same result Yes, but: * you can still \d the view (or \d equivalent in sql) which shows it's definition * if you can \d view, you can 'create temporary view' with the same definition on public tables what does this get you? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Isolating a record column from a PL-Pgsql function call ?
Hi: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let's say the first element/column is called id_num. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; (my_plpgsql_fn is the function call, id_num is the columns I want returned). Thanks -dave
Re: [GENERAL] how to load text file that has embeded nul character
Sun Dong wrote: When I tried to load it, I always get this error: invalid byte sequence for encoding UTF8: 0x00 how could I load this into postgresql database? I'm afraid you'll have to preprocess the file to convert the 0x00 into \000. Obviously, if you're inserting this file into a text field or similar, the data will be truncated at that point; if you want the data after the NUL to be preserved, you'll need a bytea field (or change the NUL to something else). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] monitor effectiveness of fillfactor and vacuums
Hi, currently i'm trying to $SUBJECT, my actual approach is to look at n_tup_upd and n_tup_hot_upd assuming the more near they are the better... is that a good assumption? what else can i see? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Trigger/Rules Order of operations
On 16/12/2008 16:26, Ketema wrote: automatically after certain input. In analyzing the task I saw two main paths. Use the trigger and rule system that Pg provides or construct external methods to control the logic and issue simple commands to insert data when appropriate. I chose to use the trigger You could encapsulate your business rules in functions (pl/pgsql or whatever) within the databaseeasier to understand what's going on (speaking as one who is completely ignorant about the rule system) and you get full ACIDity. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] what happens to indexes when TRUNCATEing
Willy-Bas Loos escribió: What happens to indexes on a table that is TRUNCATEd? They are truncated too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] what happens to indexes when TRUNCATEing
What happens to indexes on a table that is TRUNCATEd? WBL -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [GENERAL] Trigger/Rules Order of operations
2008/12/15 Ketema Harris ket...@ketema.net: I am interested in finding out the pros, cons, pitfalls of using the following design: Manual insert into Table A. Table A has a BEFORE INSERT trigger that causes an insert to table B. Table B has an AFTER INSERT trigger that causes an insert back to table A (With different criteria not an endless loop) Table A will have its Before Trig fire again and this time the criteria causes it to finish with a return new. how do you will control that for each insertion on A don't insert on B when this tuple is just inserted? WHy i do this question? Becuase you can't put 2 before insert triggers at the same time (you can but is not cleany) I mean, if you want to do a 'control' of the inserted data i think is better a good trigger that filter data to insert, and if you want a log table make a rule or in the same function of the trigger put the logging Will the second insert into table A commit before the first insert into table A? What order does the insert into table B finish up? the 2 triggers will execute, i think, at the same time. Just think: waht are the beneffits of this implementation? what do you want to resolve with it? Ketema J. Harris www.ketema.net ket...@ketema.net ketemaj on iChat -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] TIMESTAMP with a timezone offset
Hi all, I've got a database with a column I CAST as a TIMESTAMP. The data in the database is GMT. I want to say in my WHERE clause to offset the value I am giving by X number of hours and to display the column I've cast as a timestamp offset by the same X hours. I am sure this is possible, and probably fairly simple. :) Here a simplified query I am using that currently has no TZ data: 'bar' is a timestamp from the system, 'baz' is a string from an external source CAST as a timestamp. SELECT foo, bar, CAST (baz AS TIMESTAMP) AS last_state_change FROM history.table WHERE bar = '2008-12-15 14:01:09' AND foo=153; Thanks! Madi -- 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] TIMESTAMP with a timezone offset
On 16/12/2008 19:16, Madison Kelly wrote: I want to say in my WHERE clause to offset the value I am giving by X number of hours and to display the column I've cast as a timestamp offset by the same X hours. You could use AT TIME ZONE to shift it the required number of hours: http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT I hope this helps. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Isolating a record column from a PL-Pgsql function call ?
Gauthier, Dave dave.gauth...@intel.com schrieb: Hi: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let’s say the first element/column is called “id_num”. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Isolating a record column from a PL-Pgsql function call ?
The example I gave was overly simplistic. I actually want to look at more than one of the columns in the returned record, so rewritting it to return an int won't address the need for the other columns. And no, it does not return a set/array of records. It returns just one record. If I run the function outside of a query, it returns something like... (myvcharval1,myvcharval2,myintval) Notice the parens, the commas and the lack of single quotes around the myvcharval(x) values. I suppose I could do some funky string dissection with this, but I was wondering if there was something more straignt-forward. -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Tuesday, December 16, 2008 2:51 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? Gauthier, Dave dave.gauth...@intel.com schrieb: Hi: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let's say the first element/column is called id_num. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] TIMESTAMP with a timezone offset
Raymond O'Donnell wrote: On 16/12/2008 19:16, Madison Kelly wrote: I want to say in my WHERE clause to offset the value I am giving by X number of hours and to display the column I've cast as a timestamp offset by the same X hours. You could use AT TIME ZONE to shift it the required number of hours: http://www.postgresql.org/docs/8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT I hope this helps. Ray. I was reading that before posting, but have not been able to get it to work. After looking at it again I think it's because I've cast the column I restricted in the SELECT as a 'TIMESTAMP WITHOUT TIME ZONE'. So I ALTERed the column to be 'TIMESTAMP WITH TIME ZONE' and tried again. However, it looks like it cast the time zone on each column to my current time zone instead of UTC. After ALTERing the column and using the AT TIME ZONE 'EST' it returns values five hours ahead. So now I have another question... How can I recast a column to specify that the current values are UTC timestamps? Thanks! Madi -- 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] View vs Constantly Updated Table
It is generally better to save the balance. The general rule in accounting systems design is what can be printed, should be explicitly on disk. for an invoice: value before tax, tax percentage, value after tax, total before tax, total after tax, etc, should all be saved explicitly. An account should have a balance. Every operation should have balance before operation, value, balance after operation. You should never update an operation. This way when business rules change all previous documents are stored in consistent state. Greetings Marcin Mańk -- 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] Isolating a record column from a PL-Pgsql function call ?
Gauthier, Dave dave.gauth...@intel.com schrieb: Hi: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let’s say the first element/column is called “id_num”. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; Okay, i think, i got it: test=# create function x(in int, out a int, out b int) returns record as $$begin a:=1;b:=2;return; end; $$language plpgsql; CREATE FUNCTION Time: 0.412 ms test=*# select 5 where 3=(select b from x(2)); ?column? -- (0 rows) Time: 0.335 ms test=*# select 5 where 2=(select b from x(2)); ?column? -- 5 (1 row) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help to dynamically access to colomns in function!
Hello. I have table classif with columns:
... , group1, group2, group3, ... , group48, ...
In function i do query and want run on every row and dynamically operate on
columns from group1 to group20. I do something like this:
OPEN curs FOR select * from classif;
loop
fetch curs into tmprec;
exit when not found;
for I in 1..20 loop
...
-- problem code is:
value := tmprec.group{I};
-- i cannot dynamically access to group1, group2, ... colomns according
to I variable.
...
end loop;
end loop;
I have to manually identify and handle each entry without a cycle do
something like this:
value := tmprec.group1;
...
value := tmprec.group2;
...
value := tmprec.group2;
...
value := tmprec.group20;
Please help me to do it dynamically with a loop, depending on the I?
something like this in a loop:
value := tmprec.group{I};
Thanks.
Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ?
And it's a bit more complicated at that. Here's why I discovered that I can refer to the function as a relation in the from clause... select y.col1 from my_plpgsql_fn(789) y but I need to pass the function something from outside, sort of like... select x.col_a, y.col1 from mytable x, my_plpgsql_fn(a.val) y where x.col_b = 'foo'; When I try this one, I get a ERROR Function expression in FROM cannot refer to other relations of same query level I could accomplish what I need to because Im actually doing all this in the Perl/DBI. And in Perl, I can just run the x query in a loop, then the my_plpgsql_fn call inside the loop. This will accomplish the same thing, but it'll be 2 queries instead of one (and it'll be far from elegant!) -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Tuesday, December 16, 2008 3:03 PM To: Andreas Kretschmer; pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? The example I gave was overly simplistic. I actually want to look at more than one of the columns in the returned record, so rewritting it to return an int won't address the need for the other columns. And no, it does not return a set/array of records. It returns just one record. If I run the function outside of a query, it returns something like... (myvcharval1,myvcharval2,myintval) Notice the parens, the commas and the lack of single quotes around the myvcharval(x) values. I suppose I could do some funky string dissection with this, but I was wondering if there was something more straignt-forward. -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Tuesday, December 16, 2008 2:51 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? Gauthier, Dave dave.gauth...@intel.com schrieb: Hi: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let's say the first element/column is called id_num. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Isolating a record column from a PL-Pgsql function call ?
Wooops... typo... should have been select x.col_a, y.col1 from mytable x, my_plpgsql_fn(x.val) y where x.col_b = 'foo'; -Original Message- From: Gauthier, Dave Sent: Tuesday, December 16, 2008 3:38 PM To: Gauthier, Dave; Andreas Kretschmer; pgsql-general@postgresql.org Subject: RE: [GENERAL] Isolating a record column from a PL-Pgsql function call ? And it's a bit more complicated at that. Here's why I discovered that I can refer to the function as a relation in the from clause... select y.col1 from my_plpgsql_fn(789) y but I need to pass the function something from outside, sort of like... select x.col_a, y.col1 from mytable x, my_plpgsql_fn(a.val) y where x.col_b = 'foo'; When I try this one, I get a ERROR Function expression in FROM cannot refer to other relations of same query level I could accomplish what I need to because Im actually doing all this in the Perl/DBI. And in Perl, I can just run the x query in a loop, then the my_plpgsql_fn call inside the loop. This will accomplish the same thing, but it'll be 2 queries instead of one (and it'll be far from elegant!) -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Tuesday, December 16, 2008 3:03 PM To: Andreas Kretschmer; pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? The example I gave was overly simplistic. I actually want to look at more than one of the columns in the returned record, so rewritting it to return an int won't address the need for the other columns. And no, it does not return a set/array of records. It returns just one record. If I run the function outside of a query, it returns something like... (myvcharval1,myvcharval2,myintval) Notice the parens, the commas and the lack of single quotes around the myvcharval(x) values. I suppose I could do some funky string dissection with this, but I was wondering if there was something more straignt-forward. -dave -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Tuesday, December 16, 2008 2:51 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Isolating a record column from a PL-Pgsql function call ? Gauthier, Dave dave.gauth...@intel.com schrieb: Hi: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let's say the first element/column is called id_num. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; I think, you should rewrite your function to hand over the id_num as additional parameter. But i'm not sure if i understand you corrently. Is this a setof-returning function? Please, show us your real function, maybe someone can help you more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Need help to dynamically access to colomns in function!
Please disregard my other message. I didn't get what you are trying to do at first. You can do this with dynamic SQL: Look at 38.5.4. Executing Dynamic Commands (http://www.postgresql.org/docs/current/static/plpgsql- statements.html). I guess something like EXECUTE 'SELECT tmprec.group' || i INTO value will work in a loop. Didn't try it though. regards, Ivan Pavlov -- 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] Need help to dynamically access to colomns in function!
If you need them one by one why fetch them into tmprec? Take a look at
the docs for FETCH:
http://www.postgresql.org/docs/8.3/static/sql-fetch.html
especially the FETCH ABSOLUTE...
regards,
Ivan Pavlov
On Dec 16, 3:37 pm, aesthete2...@gmail.com (Иван Марков) wrote:
Hello. I have table classif with columns:
... , group1, group2, group3, ... , group48, ...
In function i do query and want run on every row and dynamically operate on
columns from group1 to group20. I do something like this:
OPEN curs FOR select * from classif;
loop
fetch curs into tmprec;
exit when not found;
for I in 1..20 loop
...
-- problem code is:
value := tmprec.group{I};
-- i cannot dynamically access to group1, group2, ... colomns according
to I variable.
...
end loop;
end loop;
I have to manually identify and handle each entry without a cycle do
something like this:
value := tmprec.group1;
...
value := tmprec.group2;
...
value := tmprec.group2;
...
value := tmprec.group20;
Please help me to do it dynamically with a loop, depending on the I?
something like this in a loop:
value := tmprec.group{I};
Thanks.
--
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] Lost password
It seems you haven't done anything you need to save, so why not save time and just reinstall postgres? TJ Hi  I started the installation of postrgres got distracted and then started again but forgot my password -- 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] Isolating a record column from a PL-Pgsql function call ?
Gauthier, Dave dave.gauth...@intel.com writes: I have a PL-Pgsql function that returns a record of a type that have 4 elements (columns). Let's say the first element/column is called id_num. Is there a way I can specify the id_num element of the record returned from iside a query? For example, select col1 from thetable where my_plpgsql_fn(col2).id_num = 123; You're just missing some parentheses: select col1 from thetable where (my_plpgsql_fn(col2)).id_num = 123; as indeed is documented here: http://www.postgresql.org/docs/8.3/static/sql-expressions.html#AEN1679 There are messy syntactic reasons for requiring these parens, which I don't recall the details of at the moment ... regards, tom lane -- 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] TIMESTAMP with a timezone offset
Madison Kelly li...@alteeve.com writes: I was reading that before posting, but have not been able to get it to work. After looking at it again I think it's because I've cast the column I restricted in the SELECT as a 'TIMESTAMP WITHOUT TIME ZONE'. So I ALTERed the column to be 'TIMESTAMP WITH TIME ZONE' and tried again. However, it looks like it cast the time zone on each column to my current time zone instead of UTC. Yeah, the default conversion from TS-without-TZ to TS-with-TZ assumes that the TS-without-TZ values are in your current timezone. You might be able to get what you want by setting timezone to UTC temporarily while doing the ALTER. However, that approach might give you headaches with inserting more data --- you might find yourself needing to keep timezone = UTC all the time, which might create troubles elsewhere. Another way to do it is, if you're starting from TS-without-TZ data that you want to consider as being in UTC, is (ts_value AT TIME ZONE 'UTC') AT TIME ZONE 'EST' The first conversion says this TS-without-TZ data is in UTC, now produce a correct TS-with-TZ from it. And then the second one rotates that back to local time in EST. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Maximum reasonable free space map
Hi Everyone, Just wondering what the maximum reasonable free space map setting should be? I'm receiving the following advice from vacuum: INFO: free space map contains 170803 pages in 117 relations DETAIL: A total of 185000 page slots are in use (including overhead). 733008 page slots are required to track all free space. Current limits are: 185000 page slots, 5000 relations, using 1623 KB. NOTICE: number of page slots needed (733008) exceeds max_fsm_pages (185000) HINT: Consider increasing the configuration parameter max_fsm_pages to a value over 733008. I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running very high volume 100GB+ DBs. Cheers Phil -- 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] Maximum reasonable free space map
On Wed, Dec 17, 2008 at 12:55 AM, Phillip Berry pbe...@stellaconcepts.com wrote: I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running very high volume 100GB+ DBs. all depends on how often does the data change. I would go with whatever vacuum is suggesting on production :) in 8.4 you won't have to worry about it anymore anyway ;) -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PITR archive_status/%p.done files
A couple of questions about these files: 1) When are these generated? Is this once an archive is generated, or once the full archive_command has run? 2) Is there any harm in removing these files by appending an rm command at the end of archive_command? -salman -- 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] Maximum reasonable free space map
The data in nearly every table is constantly changing due to a high volume of new data constantly coming in, processing on the existing data and heavy reporting being done all at once all day and night. So I guess my question is, is there a point where you start to see diminishing returns or even negative returns by setting the fsm too high? Cheers Phil On Wednesday 17 December 2008 13:02:21 Grzegorz Jaśkiewicz wrote: On Wed, Dec 17, 2008 at 12:55 AM, Phillip Berry pbe...@stellaconcepts.com wrote: I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running very high volume 100GB+ DBs. all depends on how often does the data change. I would go with whatever vacuum is suggesting on production :) in 8.4 you won't have to worry about it anymore anyway ;) -- 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] Maximum reasonable free space map
On Wed, Dec 17, 2008 at 2:29 AM, Phillip Berry pbe...@stellaconcepts.com wrote: The data in nearly every table is constantly changing due to a high volume of new data constantly coming in, processing on the existing data and heavy reporting being done all at once all day and night. So I guess my question is, is there a point where you start to see diminishing returns or even negative returns by setting the fsm too high? I personally didn't experience anything like that. If the data is updated, but not deleted mostly - you should do something to utilize HOT (if using 8.3). Otherwise, you want FSM to be big enough - so that more and more deleted records could get reused without need for so often vacuum. -- GJ -- 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] Maximum reasonable free space map
Phillip Berry pbe...@stellaconcepts.com writes: So I guess my question is, is there a point where you start to see diminishing returns or even negative returns by setting the fsm too high? There is no benefit to having FSM larger than necessary, so I suppose that qualifies as diminishing returns. The only negative effect is the reduced memory available for caches and shared buffers. You might also want to check that you don't have just a few tables which have a lot of dead space in them. If so filling the FSM is the least of your worries. The tables with lots of dead space will perform poorly because of the time spent sifting through all that dead space. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rows count in the cursor ?
Hello. how can i get count of the rows in cursor ? this code always return 0, but table is not empty: open curs for select * from classif order by id; GET DIAGNOSTICS i = ROW_COUNT; -- 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] Maximum reasonable free space map
On Tue, Dec 16, 2008 at 5:55 PM, Phillip Berry pbe...@stellaconcepts.com wrote: Hi Everyone, Just wondering what the maximum reasonable free space map setting should be? I'm receiving the following advice from vacuum: INFO: free space map contains 170803 pages in 117 relations DETAIL: A total of 185000 page slots are in use (including overhead). 733008 page slots are required to track all free space. Current limits are: 185000 page slots, 5000 relations, using 1623 KB. NOTICE: number of page slots needed (733008) exceeds max_fsm_pages (185000) HINT: Consider increasing the configuration parameter max_fsm_pages to a value over 733008. I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running very high volume 100GB+ DBs. It's all about the size of your tables. If you've got 1 table with 100k rows that's updated a lot then an fsm of 100k is likely reasonable, assuming you've got autovac keeping things in check. Got 4G rows but none are ever updated, then you don't need much if any fsm. If you've got 40M rows and 10% are updated each day, then it's likely you'll want 4M fsm entries avaialble for those dead rows. I think that as long as you're not using a huge amount of shared memory it's nothing to worry about much, as long as it's not too small. We had to go to 1Million fsm entries because we routinely have 400k to 600k dead rows in our db at work. -- 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] Rows count in the cursor ?
Hi Ivanmara, There are two things about cursors you may need to remember ( I hope I do remember fairly well not to error in this response :-) ). 1) A cursor is similar to a pointer to a given record, which means it's vision is limited to the current record, and you move the cursor forward by FETCH statement which means it may not really give the count of records which satisfy the query on which it (the cursor) is based on. 2) When you open a cursor, it's bearing is before the first record of the results set. If you call GET DIAGNOSTICS i=ROW_COUNT variable i will have 0 (zero), you will need to FETCH from the cursor to move it to the first record (each fetch moves the cursor by one record). After the first FETCH the execution of GET DIAGNOSTICS i=ROW_COUNT after each subsequent FETCH will give variable i a value of 1 provided the end of result set has not been reached. You may want to read more on cursors on the well written PostgreSQL documentation. Typical use of cursors may be something similar to the following. DECLARE _myRecord RECORD; _curs1 REFCURSOR; _sql_query TEXT; BEGIN _sql_query:='select * from classif order by id'; OPEN _curs1 FOR EXECUTE _sql_query; LOOP FETCH _curs1 INTO _myRecord; EXIT WHEN NOT FOUND; Allan. Ivanmara wrote: Hello. how can i get count of the rows in cursor ? this code always return 0, but table is not empty: open curs for select * from classif order by id; GET DIAGNOSTICS i = ROW_COUNT; -- 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] Need help to dynamically access to colomns in function!
Create a plpgsql function that reformats your row as an array.
Example:
I have a table named task_parameter with three columns:
Table camera.task_parameter
Column | Type | Modifiers
--+-+---
parameter_name | character varying(255) | not null
parameter_value | text |
task_id| bigint | not null
I create a plpgsql function that takes a task_parameter row and returns an
array, one column per array entry:
create or replace function task_parameter_array(tp task_parameter) returns
text[] as $$
declare
result text[];
begin
result[1] := tp.parameter_name;
result[2] := tp.parameter_value;
result[3] := tp.task_id;
return result;
end $$ language plpgsql;
select task_parameter_array(task_parameter) from task_parameter limit 1;
task_parameter_array
{db alignments per query,25,1286428019358957945}
(1 row)
You can write a similar function for your table and then your code would look
like:
OPEN curs FOR select classif_to_array(classif) as group_array from classif;
loop
fetch curs into tmprec;
exit when not found;
for I in 1..20 loop
...
value := tmprec.group_array{I};
of course, this begs the question, whjy not define you table to store an
array...?
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ivan Pavlov
Sent: Tuesday, December 16, 2008 3:55 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help to dynamically access to colomns in function!
If you need them one by one why fetch them into tmprec? Take a look at
the docs for FETCH:
http://www.postgresql.org/docs/8.3/static/sql-fetch.html
especially the FETCH ABSOLUTE...
regards,
Ivan Pavlov
On Dec 16, 3:37 pm, aesthete2...@gmail.com (Иван Марков) wrote:
Hello. I have table classif with columns:
... , group1, group2, group3, ... , group48, ...
In function i do query and want run on every row and dynamically operate on
columns from group1 to group20. I do something like this:
OPEN curs FOR select * from classif;
loop
fetch curs into tmprec;
exit when not found;
for I in 1..20 loop
...
-- problem code is:
value := tmprec.group{I};
-- i cannot dynamically access to group1, group2, ... colomns according
to I variable.
...
end loop;
end loop;
I have to manually identify and handle each entry without a cycle do
something like this:
value := tmprec.group1;
...
value := tmprec.group2;
...
value := tmprec.group2;
...
value := tmprec.group20;
Please help me to do it dynamically with a loop, depending on the I?
something like this in a loop:
value := tmprec.group{I};
Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] Is this a security risk?
Adam Witney wrote: I would like to provide a limited view of my database to some users, so i thought of creating a second database (I can control access by IP address through pg_hba.conf) with some views that queried the first database using dblink. In my opinion dblink is not the right tool for that. It will require a user account on the secret database through which dblink accesses it. You'd have to restrict permissions for that user if you want to keep the thing secure. So why not access the secret database directly with that user and get rid of the added difficulty of dblink? You can rely on the permission system. Just grant the user the appropriate privileges on the necessary objects, and if you need the user to see only part of the data in a table, create a view for that. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
