Re: [SQL] UNICODE and PL/PGSQL
Due to a lack of time, I'm closing the thread. It takes a little more code, but I've decided to just forget about the loop and write every single statement in my function. Thanks anyway for explaning and brainstorming. >>> Ragnar <[EMAIL PROTECTED]> 2006-11-28 10:28 >>> On þri, 2006-11-28 at 09:14 +0100, Bart Degryse wrote: > Yes, I tried and it didn't work. PostgreSQL surely makes something of > it, but not the right characters. maybe you should show us exacly what you did, and what you got, and what you expected > Unless Markus can make his idea using "decode" work, this might be > something pl/pgsql cannot do. did you try the function I posted yesterday elsewhere in this thread? gnari
Re: [SQL] consistent random order
Jeff Herrin wrote: I don't think cursors are going to help in this case. The order by random() is still going to give different result sets on different pages. Jeff A cursor will maintain the order it was created with until it is disposed of. It won't work with a web app though as each page will come from a different connection in the available pool (or created for each page) meaning you will loose the cursor between pages. I would think you want to look at having a sort column that has a random number in it that is used for sorting. mysortcol integer default random() or maybe update the column a couple of times a day to keep the variety you seem to be after. - Original Message - From: Andreas Kretschmer <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wednesday, November 29, 2006 12:27:42 PM GMT-0500 US/Eastern Subject: Re: [SQL] consistent random order Jeff Herrin <[EMAIL PROTECTED]> schrieb: I am returning results ordered randomly using 'order by random()'. My issue has to do with page numbers in our web application. When I hit the 2nd page and retrieve results with an offset, ordering by random() isn't really what I want since I will often receive results that were on the 1st page (they get re- randomized). I'm looking for a way to order in a controled random order. Maybe a UDF. I think you are searching for CURSORs. 18:25 < akretschmer> ??cursor 18:25 < rtfm_please> For information about cursor 18:25 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-cursors.html 18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-declare.html With a CURSOR, you get one result-set and can walk through this result. Andreas -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Autovaccum
Hi list, I would like to know if it is necessary to set my database to autovaccum if the intent of my DB Manager is do not make any deletion in any time. If there is no deletions why autovaccum ok ? Regards Ezequias ---(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] Autovaccum
Ezequias Rodrigues da Rocha wrote: > Hi list, > > I would like to know if it is necessary to set my database to > autovaccum if the intent of my DB Manager is do not make any deletion > in any time. > > If there is no deletions why autovaccum ok ? You need to vacuum from time to time anyway, even if you don't delete anything. The easiest way to do it is let autovacuum do it for you. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Autovaccum
On 30/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Ezequias Rodrigues da Rocha wrote: > Hi list, > > I would like to know if it is necessary to set my database to > autovaccum if the intent of my DB Manager is do not make any deletion > in any time. > > If there is no deletions why autovaccum ok ? You need to vacuum from time to time anyway, even if you don't delete anything. The easiest way to do it is let autovacuum do it for you. Complete Answer. 1. If you do any updates or deletes you need to vacuum. 2. If you do any update, deletes or inserts you need to analyse. 3. If you never change anything No Updates, No Inserts, No Deletes you need to vacuum once ever 2 billion transactions due to wrap around if you don't your data will disappear. But depending on how busy your database is this could be once a year. 4. Auto Vacuum does all this automatically. Which is kind of useful. 5. You can do this manually via cron or by hand if you wish. Peter. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Autovaccum
It is possible to set this up on PgAdmin ? I need to shutdown the postgresql service to change this parameters ? ps: Your information was very clear for me. Thank you very much. Ezequias 2006/11/30, Peter Childs <[EMAIL PROTECTED]>: On 30/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Ezequias Rodrigues da Rocha wrote: > > Hi list, > > > > I would like to know if it is necessary to set my database to > > autovaccum if the intent of my DB Manager is do not make any deletion > > in any time. > > > > If there is no deletions why autovaccum ok ? > > You need to vacuum from time to time anyway, even if you don't delete > anything. The easiest way to do it is let autovacuum do it for you. > Complete Answer. 1. If you do any updates or deletes you need to vacuum. 2. If you do any update, deletes or inserts you need to analyse. 3. If you never change anything No Updates, No Inserts, No Deletes you need to vacuum once ever 2 billion transactions due to wrap around if you don't your data will disappear. But depending on how busy your database is this could be once a year. 4. Auto Vacuum does all this automatically. Which is kind of useful. 5. You can do this manually via cron or by hand if you wish. Peter. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Autovaccum
Alvaro Herrera wrote: Ezequias Rodrigues da Rocha wrote: Hi list, I would like to know if it is necessary to set my database to autovaccum if the intent of my DB Manager is do not make any deletion in any time. If there is no deletions why autovaccum ok ? > You need to vacuum from time to time anyway, even if you don't delete anything. The easiest way to do it is let autovacuum do it for you. One thing that vacuum/autovacuum does is mark space used by deleted rows to be reused. Without deletes this won't be necessary in table data files. But when you update a record an index may also be updated and have the same effect within the index storage space. There are other things that vacuum does to keep your database running optimally. One is to update planner statistics about how many rows are in each table which effects the query planning and optimizing. Without deletes a plain vacuum won't achieve a great deal, but a regular VACUUM ANALYZE (as done by autovacuum) will make a difference to the performance of your database. If no data in your db changes then you won't have to bother vacuuming. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] pg_xlog on separate drive
Hey guys, sorry if this is slightly OT for this list, but I figure it's a simple question. If I'm storing pg_xlog on a second non-redundant drive using the symlink method and the journal drive were to crash, how difficult is recovery? Will Postgresql simply be able to reinitialize the journal on a new drive and carry on, or is there more to it than that? I realize any pending transactions would be lost, but that's not a huge concern for me because everything I'm importing comes from raw data. Thanks, Travis
Re: [SQL] consistent random order
Michael, I think you may have solved my problem. We're still experimenting with it but I think setseed is going to work. Thank you very, very much! Jeff Herrin - Original Message - From: Michael Fuhr <[EMAIL PROTECTED]> To: Jeff Herrin <[EMAIL PROTECTED]> Cc: Andreas Kretschmer <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org Sent: Thursday, November 30, 2006 2:58:23 AM GMT-0500 US/Eastern Subject: Re: [SQL] consistent random order On Wed, Nov 29, 2006 at 12:32:56PM -0500, Jeff Herrin wrote: > I don't think cursors are going to help in this case. The order > by random() is still going to give different result sets on different > pages. Have you tried using setseed() to seed the random number generator to the same value before each query? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg_xlog on separate drive
"Travis Whitton" <[EMAIL PROTECTED]> writes: > Hey guys, sorry if this is slightly OT for this list, but I figure it's a > simple question. If I'm storing pg_xlog on a second non-redundant drive > using the symlink method and the journal drive were to crash, how difficult > is recovery? Will Postgresql simply be able to reinitialize the journal on a > new drive and carry on, or is there more to it than that? I realize any > pending transactions would be lost, but that's not a huge concern for me > because everything I'm importing comes from raw data. Losing xlog is pretty bad: there's a serious risk of data corruption, in that transactions made since your last checkpoint may be only partially applied. I wouldn't recommend a setup in which xlog is less redundant than your main storage array. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Autovaccum
Ezequias Rodrigues da Rocha wrote: It is possible to set this up on PgAdmin ? I don't know. I need to shutdown the postgresql service to change this parameters ? No, you can just modify the params in postgresql.conf and HUP the server so that it re-reads the config file, no restart required. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Autovaccum
Just a little question. What is the interval of time the vacuum will run on my database ? Ezequias 2006/11/30, Matthew T. O'Connor : Ezequias Rodrigues da Rocha wrote: > It is possible to set this up on PgAdmin ? I don't know. > I need to shutdown the postgresql service to change this parameters ? No, you can just modify the params in postgresql.conf and HUP the server so that it re-reads the config file, no restart required. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Select (1-3)
Hi list, It is possible to make a selection like Select (list 1 to 1000); And get the rows ? producao=# select ...; row - 1 2 3 . . . 1000 -- -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Select (1-3)
On 11/30/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, It is possible to make a selection like Select (list 1 to 1000); And get the rows ? producao=# select ...; row - 1 2 3 . . . 1000 *select * from generate_series(1, 1000);* See http://oreillynet.com/pub/a/databases/2006/09/07/plpgsql-batch-updates.html?page=2 -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] Select (1-3)
Ezequias Rodrigues da Rocha wrote: > Hi list, > > It is possible to make a selection like > > Select (list 1 to 1000); Sure, use the generate_series() function. select * from generate_series(1, 1000); -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Select (1-3)
Uow ! 1 to one m select * from generate_series(1, 100); In 5203 miliseconds (that's a great performmance). My server is not quite good but the performance was great for me. Thank you so much Ezequias 2006/11/30, Aaron Bono <[EMAIL PROTECTED]>: On 11/30/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: > Hi list, > > It is possible to make a selection like > > Select (list 1 to 1000); > > And get the rows ? > > producao=# select ...; >row > - >1 >2 >3 > . > . > . > 1000 > select * from generate_series(1, 1000); See http://oreillynet.com/pub/a/databases/2006/09/07/plpgsql-batch-updates.html?page=2 -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com == -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Grants
Hi list, I am having problem with grants and users on PostgreSQL. I am using pgAdmin to connect like other user to test my permissions. As the owner of the database I have criated two roles: administrators (cannot connect) ezequias (can connect) I give permissions to a table I have: GRANT SELECT, UPDATE, INSERT ON TABLE base.table1 TO administrators; My user: CREATE ROLE ezequias LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT administradores TO ezequias; My group CREATE ROLE administradores NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; When I try to access the table base.table1 with ezequias login the pgAdmin reports: (see attached image) Could someone tell me what I did wrong ? Ezequias <> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Grants
You did not grant access privileges to schema. Also GRANT administrators on the base schema as you did for the table. --Imad www.EnterpriseDB.com On 12/1/06, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote: Hi list, I am having problem with grants and users on PostgreSQL. I am using pgAdmin to connect like other user to test my permissions. As the owner of the database I have criated two roles: administrators (cannot connect) ezequias (can connect) I give permissions to a table I have: GRANT SELECT, UPDATE, INSERT ON TABLE base.table1 TO administrators; My user: CREATE ROLE ezequias LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; GRANT administradores TO ezequias; My group CREATE ROLE administradores NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; When I try to access the table base.table1 with ezequias login the pgAdmin reports: (see attached image) Could someone tell me what I did wrong ? Ezequias ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] retrieve row number
Hi, Can I retrieve the row number in a select statement? For example : if I have the following table "foo" col1 | col2 -+- a | x b | y c | z select ?? as row_number, col1, col2 from foo; I should obtain the following result : row_number | col1 | col2 ---+--+- 1 | a | x 2 | b | y 3 | c | z Thank you Pascal
Re: [SQL] retrieve row number
am Fri, dem 01.12.2006, um 8:31:41 +0200 mailte Pascal Tufenkji folgendes:
> Hi,
>
>
>
> Can I retrieve the row number in a select statement?
> For example : if I have the following table ?foo?
> I should obtain the following result :
>
>
>
> row_number | col1 | col2
> ---+--+-
> 1 | a | x
> 2 | b | y
> 3 | c | z
test=> create temporary sequence tmp_seq;
CREATE SEQUENCE
test=*> select nextval('tmp_seq') as row_number, col1, col2 from foo;
row_number | col1 | col2
+--+--
1 | a| x
2 | b| y
3 | c| z
(3 rows)
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
