Re: [GENERAL] Date index not used when selecting a date range
You can prevent postgres from using the index on node by changing the reference in the WHERE clause to an expression, like so: SELECT * FROM public.stat WHERE node||'' = '1010101010' AND (date = '2008-06-30'::date AND date = '2008-01-01'::date); Perhaps this will lead the optimizer to choose the index on date. However, I have noticed reluctance in the postgres optimizer to use multi-column indexes, presumably because the increased size of the indexed values lowers expectations for performance of the index. I think you are right about the multi-column usage. When I use node||'' instead of node, it will do a seq scan. Poul -- 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 to give input a file for a stored procedure
aravind chandu wrote: Hi, I am writing a stored procedure where the input to it is a file.I did not have any idea of how to give input as a file for a stored procedure.could you please help me. I assume that by a file you mean a file in the filesystem, outside the database, rather than BLOB in the database. If so, then there are functions in one of the contrib modules that provide filesystem access from the PostgreSQL server. They're only available to superusers, but if you were REALLY careful you could use them in a SECURITY DEFINER stored procedure. This is a VERY DANGEROUS thing to do unless you absolutely trust all users of the database. If you do not know what the above means, then do not do it. Read the PostgreSQL documentation to find out more. Start here: http://www.postgresql.org/docs/8.3/static/index.html Pay particular attention to the section Additional Supplied Modules in the appendices. Note that those functions do not get you access to the *client* filesystem. Access is to the server file system with the rights of the user the postgresql server runs under. If you want to access a file from the client with the rights of the user the client is running under, you will need to have your client application load the file and send it to the server. You might load the file into the database as a BLOB, or just pass it as a bytea field. Perhaps if you explained the purpose of what you were attempting you might get more useful answers. -- Craig Ringer -- 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] Date index not used when selecting a date range
This is the index: CREATE INDEX idx_stat_date_node_type ON public.stat USING btree (date, node, type); explain SELECT * FROM public.stat WHERE node = '1010101010' AND (date = '2008-06-30'::date AND date = '2008-01-01'::date) Try changing the index order to node, date rather than date, node. You need the column on which you'll be doing range checking to be the furthest to the right in the index column list. Then it works. Unfortunately the production database is always in use and it contains more than 100 mill. rows, so creating an index is not an option. Poul -- 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] Date index not used when selecting a date range
Have you run analyze on the table? Yes How selective is the condition node = '1010101010' and the date range. In particular, do you have an idea what percentange of the table fits into that date range? There are around 1000 nodes and there is data for two years, so it must be around 1/4000 of all rows What about the type column? You have it in the index, but not in your query. Have you tried adding type to the query? Will that make it more selective? Why haven't I tried that ... That did the trick, thanks! Poul -- 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] Clone a database to other machine
Hi And you can use Londiste from Skytools. It's simpler and better suited for your task. http://pgfoundry.org/projects/skytools/ Asko On Mon, Jul 28, 2008 at 7:44 PM, Garg, Manjit [EMAIL PROTECTED] wrote: Hi All, I'm stuck to an issue while cloning the pgsql database, can you please help, or give any docs to help out. Query - Trying to have same database on two seprate linux servers. One will be used to upport Applications and other will be used for Report generation only. Want to keep both the database in Sync, hourly or nightly. Kindly help to achive the same. Thanks and regards, Manjit Garg Corbus Global Support Team INDIA -- Email: [EMAIL PROTECTED] Phone: +91-120-304-4000, Ext 252 Fax : +91-120-256-7040 Mob : 9810679256 -- *CONFIDENTIALITY NOTICE:*This message, including any attachments hereto, (collectively the Email Message) is intended solely for the personal and confidential use of the designated recipient(s) and may contain privileged, proprietary, or otherwise private information which may be subject to attorney-client privilege or may constitute inside information protected by law. If the reader of this message is not the intended recipient, you are hereby notified of the following: (i) Any disclosure, printing, copying, or distribution of this Email Message by you or (ii) the taking of any action by you based on the contents of this Email Message or (iii) any other use of this Email Message by you, are strictly prohibited. If you have received this message in error, please notify the sender immediately and remove all traces of the electronic mail message and its attachments from your system.
[GENERAL] Right way to reject INSERTs and UPDATEs
Hello! I have postgresql table that stores dome date range: it has range-begin and range-end columns of type date. I want to enforce that 1) range-begin would always before range-end and 2) there would be no range overlaps. First problem could be solved with CHECK constraint. The only possibility to solve second problem is to use a trigger. Trigger would fire BEFORE INSERT OR UPDATE and FOR EACH ROW and invoke pgplsql function that returns NULL or new depending on condition met or not; These solution work and enforce data integrity as needed. Sadly, database interface I use (Trolltech Qt 3) can't track when trigger aborts update or insert. Can I raise exception in trigger or something? -- A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing in e-mail? -- 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] array_accum() and quoted content
On Jul 29, 12:08 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Raymond C. Rodgers escribió: The query in which I'm using array_accum() is building a list of companies and the associated publishers for each. For example: SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON c.company_id = cpa.company_id LEFT JOIN publisher_table p ON cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name ORDER BY company_name (This query isn't direct out of my code, and thus may have errors, but it should convey the idea of what I'm trying to accomplish.) The result is that I should have a single row containing the company_id, company_name, and publishers' names if any. In order to do this you can use a custom aggregate function to concatenate the texts. I have described this previously here: http://archives.postgresql.org/message-id/20080327234052.GZ8764%40alv... the text is in spanish but the SQL commands should be trivial to follow. I think this is a FAQ. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list ([EMAIL PROTECTED]) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Appropos, have you had a chance to compare the performance of this approach and when you use array_to_string( array_accum( $1 ), ' ' ) instead of the text_concat( $1 ) PL/pgSQL based aggregate function? -- 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] Right way to reject INSERTs and UPDATEs
Dmitry Teslenko wrote: These solution work and enforce data integrity as needed. Sadly, database interface I use (Trolltech Qt 3) can't track when trigger aborts update or insert. Can I raise exception in trigger or something? RAISE EXCEPTION 'Failed to update mytable: date range (%,%) overlaps an existing entry', NEW.from_dt, NEW.to_dt; You can also do RAISE NOTICE, WARNING etc. See the pl/pgsql section of the manual for details. -- Richard Huxton Archonet Ltd -- 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] Right way to reject INSERTs and UPDATEs
On Tue, Jul 29, 2008 at 02:25:21PM +0400, Dmitry Teslenko wrote: These solution work and enforce data integrity as needed. Sadly, database interface I use (Trolltech Qt 3) can't track when trigger aborts update or insert. Can I raise exception in trigger or something? of course. syntax: RAISE EXCEPTION 'description, with param %', some_param; depesz -- Linked in: http://www.linkedin.com/in/depesz jid/gtalk: [EMAIL PROTECTED] aim: depeszhdl skype: depesz_hdl -- 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] Date index not used when selecting a date range
On Tue, Jul 29, 2008 at 1:25 AM, Poul Møller Hansen [EMAIL PROTECTED] wrote: This is the index: CREATE INDEX idx_stat_date_node_type ON public.stat USING btree (date, node, type); explain SELECT * FROM public.stat WHERE node = '1010101010' AND (date = '2008-06-30'::date AND date = '2008-01-01'::date) Try changing the index order to node, date rather than date, node. You need the column on which you'll be doing range checking to be the furthest to the right in the index column list. Then it works. Unfortunately the production database is always in use and it contains more than 100 mill. rows, so creating an index is not an option. If you're running 8.3 you can create index concurrently. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] interesting trigger behaviour in 8.3
Hello, We came accross interesting behaviour of the update statement inside an after insert or update trigger in PostgreSQL 8.3.1. Briefly, the update run within one line trigger function takes always 1.5 sec whereas exactly the same update hitting the same rows takes always 1ms if run from the psql terminal. In pseudo code it looks like the following. There are 2 tables, empty abstract_table with 3 columns (id, col1, col2) and many tables (e.g. inherited_table1_with_data) that inherit abstract_table. Constraint_exclusion is set up on id column and works perfectly. So we've got update like this UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345; that takes too long when run from trigger (fired on any third table) and takes 1ms if run literally with all the same constants from terminal. However there's one more issue with that. If we change update within trigger to eliminate constraint_exclusion check and point it directly to child table with data: UPDATE inherited_table1_with_data SET col1 = 1, col2 = 2 WHERE id = 12345; trigger works perfectly doing everything for 1ms as in terminal. Any clues? Can anybody suggest how to debug this? Is it possible to get an explain of the query within the trigger? -- Regards, Ivan -- 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 to do after an ERROR: out of memory
We were updating a large set of data (executing a stored procedure against a large set of data in one statement/transaction) while autovacuum was running. The resulting message looked like: 2008-07-28 21:18:08 CDT CONTEXT: automatic vacuum of table databasename._lms.sl_log_2 TopMemoryContext: 154528 total in 18 blocks; 19104 free (62 chunks); 135424 used 2008-07-28 21:28:53 CDT database_other ERROR: out of memory 2008-07-28 21:48:13 CDT ERROR: canceling autovacuum task ... It looked like the update transaction/statement was cancelled, and the autovacuum tasks running at the time were also cancelled. How does Postgres handle out of memory errors, is it simply cancelling these tasks as if the transactions have been rolled back? Is there anything that needs to be done to make sure the data in the database is not corrupted? -- - John L Cheng -- 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] interesting trigger behaviour in 8.3
On Tue, 2008-07-29 at 19:25 +0400, Ivan Zolotukhin wrote: Any clues? Can anybody suggest how to debug this? Is it possible to get an explain of the query within the trigger? I bet it's the difference between prepared/not prepared plans. The trigger prepares the plan without considering the actual parameter values, on the psql prompt you give the parameter values explicitly in the sql. Try to use the PREPARE command to prepare the plan on the psql prompt, and EXPLAIN EXECUTE it to see how it works in the trigger... Cheers, Csaba. -- 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 to do after an ERROR: out of memory
John Cheng [EMAIL PROTECTED] writes: We were updating a large set of data (executing a stored procedure against a large set of data in one statement/transaction) while autovacuum was running. The resulting message looked like: 2008-07-28 21:18:08 CDT CONTEXT: automatic vacuum of table databasename._lms.sl_log_2 TopMemoryContext: 154528 total in 18 blocks; 19104 free (62 chunks); 135424 used 2008-07-28 21:28:53 CDT database_other ERROR: out of memory 2008-07-28 21:48:13 CDT ERROR: canceling autovacuum task ... Given the time delays there, I don't think the out-of-memory in the update had anything to do with the autovacuum cancel. Evidently something sent the autovac process a SIGINT, but it wasn't as a result of the memory issue. Perhaps someone just mis-aimed a pg_cancel_backend call? 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] interesting trigger behaviour in 8.3
Ivan Zolotukhin [EMAIL PROTECTED] writes: In pseudo code it looks like the following. There are 2 tables, empty abstract_table with 3 columns (id, col1, col2) and many tables (e.g. inherited_table1_with_data) that inherit abstract_table. Constraint_exclusion is set up on id column and works perfectly. So we've got update like this UPDATE abstract_table SET col1 = 1, col2 = 2 WHERE id = 12345; I bet it does not *really* look like that, but has a parameterized WHERE clause. As per the fine manual: Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, stable functions such as CURRENT_DATE must be avoided. 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] Index creation and maintenance_work_mem
Besides maintenance_work_mem, what else can be changed to improve index creation? I just did two tests. One with maintenance_work_mem=128MB and another with maintenance_work_mem=1GB. Out of 3 single column index, 2 took slightly longer with the higher value and a third took almost the same. 12GB of ram in the machine. Redhat 4 revision 6. Postgesql 8.3.3. temp_buffers = 64MB work_mem = 96MB checkpoint_segments = 256 checkpoint_timeout = 10min Indexing 60 million rows. Tests run from a script and nothing else was running on the machine during the tests. maintenance_work_mem = 128MB CREATE INDEX Time: 449626.651 ms 7.4 minutes CREATE INDEX Time: 313004.025 ms 5.2 minutes CREATE INDEX Time: 3077382.941 ms 51.2 minutes maintenance_work_mem = 1GB CREATE INDEX Time: 497358.902 ms 8.2 minutes CREATE INDEX Time: 312316.953 ms 5.2 minutes CREATE INDEX Time: 3236472.630 ms 53.9 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How do I convert a timestamp with time zone to local time?
Greetings! What is the best way to convert a time expressed as a timestamp with time zone into a timestamp in the local time zone without knowing what the local time zone is? Thank you. RobR
Re: [GENERAL] How do I convert a timestamp with time zone to local time?
On Tuesday 29 July 2008 15:07:46 Rob Richardson wrote: Greetings! What is the best way to convert a time expressed as a timestamp with time zone into a timestamp in the local time zone without knowing what the local time zone is? Thank you. RobR SELECT extract (epoch from your_time_field) from your_table; SELECT to_timestamp(your_epoch_field) from your_table; -- Valter Douglas Lisbôa Jr. Sócio-Diretor Trenix - IT Solutions Nossas Idéias, suas Soluções! www.trenix.com.br [EMAIL PROTECTED] Tel. +55 19 3402.2957 Cel. +55 19 9183.4244 -- 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] Index creation and maintenance_work_mem
On Tuesday 29 July 2008, Francisco Reyes [EMAIL PROTECTED] wrote: Besides maintenance_work_mem, what else can be changed to improve index creation? Depends where the bottleneck is. 1 CPU core 100% user/system = buy faster CPU cores. System in I/O wait = upgrade disk channel. -- Alan -- 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] Index creation and maintenance_work_mem
On 2:53 pm 07/29/08 Alan Hodgson [EMAIL PROTECTED] wrote: --sar 2 30 Linux 2.6.9-42.ELsmp (trans03) 07/29/2008 12:58:09 PM CPU %user %nice %system %iowait %idle 12:58:11 PM all 12.44 0.00 0.06 0.00 87.50 12:58:13 PM all 12.44 0.00 0.00 0.06 87.50 12:58:15 PM all 12.49 0.00 0.06 0.00 87.45 12:58:17 PM all 12.43 0.00 0.06 1.62 85.88 12:58:19 PM all 12.38 0.00 0.06 0.00 87.55 12:58:21 PM all 12.43 0.00 0.12 0.00 87.45 12:58:23 PM all 12.50 0.00 0.00 0.00 87.50 12:58:25 PM all 12.42 0.00 0.12 0.00 87.45 Quadcore machine. Not using not even 100% of one core. Disk subsystem doesn't seem to be issue (no iowait). Based on a few links I read I thought that, holding everything else constant, increasing maintenance_work_mem would have helped. -- 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] Index creation and maintenance_work_mem
On Tuesday 29 July 2008, Francisco Reyes [EMAIL PROTECTED] wrote: On 2:53 pm 07/29/08 Alan Hodgson [EMAIL PROTECTED] wrote: --sar 2 30 Linux 2.6.9-42.ELsmp (trans03) 07/29/2008 12:58:09 PM CPU %user %nice %system %iowait %idle 12:58:11 PM all 12.44 0.00 0.06 0.00 87.50 12:58:13 PM all 12.44 0.00 0.00 0.06 87.50 12:58:15 PM all 12.49 0.00 0.06 0.00 87.45 12:58:17 PM all 12.43 0.00 0.06 1.62 85.88 12:58:19 PM all 12.38 0.00 0.06 0.00 87.55 12:58:21 PM all 12.43 0.00 0.12 0.00 87.45 12:58:23 PM all 12.50 0.00 0.00 0.00 87.50 12:58:25 PM all 12.42 0.00 0.12 0.00 87.45 Quadcore machine. Not using not even 100% of one core. Disk subsystem doesn't seem to be issue (no iowait). That looks an awful lot like pegged 1/8th usage to me ... are you sure you only have 4 cores? Hyper-threading? Based on a few links I read I thought that, holding everything else constant, increasing maintenance_work_mem would have helped. Yeah, but it didn't. -- Alan -- 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] Index creation and maintenance_work_mem
On 3:19 pm 07/29/08 Alan Hodgson [EMAIL PROTECTED] wrote: sure you only have 4 cores? Hyper-threading? It seems Hyperthreading is on in that machine. Going to see if I can have it turned off. -- 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 do I convert a timestamp with time zone to local time?
Thank you very much, sir. After posting I realized that my question did not cover my problem. I also need to calculate if a given time is within daylight savings time or not. The actual situation is this: I have a table that contains the time at which an event occurred and an estimate of how long it will be before a second event occurs. That interval is likely to be in the range of 2-3 days. I need to handle the case in which that span includes the moment at which daylight savings time status changes. The table stores the event time in both local time and UTC time. So, I need a way to calculate whether the time at the end of the interval will be DST or not. I also have a table named sys_info that contains a single record for system-wide information. I can expand that table if I want. The easiest way I can think of to do what I need is merely to store the DST start and end dates in that table, and then see if the date I'm checking is between them or not. But that won't work either, since the dates change. In the US, DST runs from the second Sunday in March to the first Sunday in November. I can hard-code to that rule (which is what I'm doing for now), but I can't very well store it in a database. And do other countries use DST, and if so, how do they define it? I think that, if I'm careful, I can write my function so I'll be converting everything I need to UTC before I start, and then I won't convert anything back until the very end, and I'll never have to worry about whether I'm spanning the DST change or not. It would still be nice to have a function that will check DST for a given date, but I hope I can avoid needing it. Thanks again! RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How do I set up automatic backups?
Greetings again! A few days ago, I visited a customer's site to talk about administering our system, which is developed around a PostGres database. One of the topics was how to back up the database. I described the process of using PgAdmin to back up and restore a database, and I said a backup should be done every night. I was asked how to automate the procedure, and I couldn't answer. A database administrator said, There's got to be a way. Otherwise, PostGres wouldn't have survived. I agree with him. The only answers I've found on the Internet involve creating a password-less account and using that to run pg_dump. What is the official best way to automatically back up a PostGres database? Thank you very much. RobR -- 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 do I set up automatic backups?
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote: I was asked how to automate the procedure, and I couldn't answer. The options are manifold! http://www.postgresql.org/docs/8.3/interactive/backup.html -- 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 do I set up automatic backups?
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote: Greetings again! A few days ago, I visited a customer's site to talk about administering our system, which is developed around a PostGres database. One of the topics was how to back up the database. I described the process of using PgAdmin to back up and restore a database, and I said a backup should be done every night. I was asked how to automate the procedure, and I couldn't answer. A database administrator said, There's got to be a way. Otherwise, PostGres wouldn't have survived. I agree with him. The only answers I've found on the Internet involve creating a password-less account and using that to run pg_dump. What is the official best way to automatically back up a PostGres database? There's no one best way. A simple way is to use pg_dump or pg_dumpall, running on the same machine as the database connecting via a unix socket using ident authentication to dump a consistent view of the database out to a file. http://www.postgresql.org/docs/8.3/interactive/backup.html discusses several other ways. Cheers, Steve -- 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 do I set up automatic backups?
On Tue, Jul 29, 2008 at 1:28 PM, Christophe [EMAIL PROTECTED] wrote: I was asked how to automate the procedure, and I couldn't answer. http://www.postgresql.org/docs/8.3/interactive/backup.html Regarding the SQL backup option for small databases, I use an OS task scheduler ( *nix Cron-job, MS task-scheduler) to automatically call my custom script file designed to handle that backing up of my databases. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Index creation and maintenance_work_mem
On Tue, 29 Jul 2008, Francisco Reyes wrote: Besides maintenance_work_mem, what else can be changed to improve index creation? Very large values there haven't been all that helpful for me. I've gotten better results in this area giving more of the unused memory to shared_buffers (which you didn't mention your setting for) rather than having a gigantic setting for maintenance_work_mem. Last time I went through a similar exercise to yours, but with a much larger data set, I ran a baseline test at maintenance_work_mem=64MB and larger values didn't seem to improve anything significantly over that. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cursor
I have the following cursor that gives me an error near open. Can someone please tell me what I am doing wrong?? Bob DECLARE procgraphic cursor for select process_id from p_id.p_id, processes_count where p_id.p_id.p_id_id = processes_count.p_id_id; begin Open procgraphic ; Fetch first from procgraphic into process_id; -- 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] Cursor
Bob Pawley wrote: I have the following cursor that gives me an error near open. Can someone please tell me what I am doing wrong?? DECLARE procgraphic cursor for select process_id from p_id.p_id, processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id; begin Open procgraphic ; There is no OPEN, you just FETCH Fetch first from procgraphic into process_id; -- Richard Huxton Archonet Ltd -- 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] Cursor
Richard Huxton [EMAIL PROTECTED] writes: Bob Pawley wrote: DECLARE procgraphic cursor for select process_id from p_id.p_id, processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id; begin Open procgraphic ; There is no OPEN, you just FETCH No, he does need an OPEN. The extract looks correct as far as it goes, so I think the mistake was in something that was omitted. 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] Cursor
Following is more complete. The balance of the trigger that is not shown works when tested separately. I didn't include it because it is quite long. Bob DECLARE process_total integer ; process_id integer ; procgraphic cursor for select process_id from p_id.p_id, processes_count where p_id.p_id.p_id_id = processes_count.p_id_id; begin Insert into processes_count (p_id_id) select new.p_id_id from project.project ; Select count (p_id.p_id.process_id) INTO process_total FROM p_id.p_id, processes_count Where p_id.p_id.p_id_id = processes_count.p_id_id; Open procgraphic; Fetch first from procgraphic into process_id; Update p_id.p_id set proc_graphic_position = one From graphics.proc_position, processes_count where graphics.proc_position.proc_count = process_total and process_id = p_id.p_id.process_id; - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Richard Huxton [EMAIL PROTECTED] Cc: Bob Pawley [EMAIL PROTECTED]; PostgreSQL pgsql-general@postgresql.org Sent: Tuesday, July 29, 2008 2:35 PM Subject: Re: [GENERAL] Cursor Richard Huxton [EMAIL PROTECTED] writes: Bob Pawley wrote: DECLARE procgraphic cursor for select process_id from p_id.p_id, processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id; begin Open procgraphic ; There is no OPEN, you just FETCH No, he does need an OPEN. The extract looks correct as far as it goes, so I think the mistake was in something that was omitted. 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] How do I set up automatic backups?
Slony-I replication is also a viable choice for backups. On Tue, Jul 29, 2008 at 1:34 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Jul 29, 2008 at 1:28 PM, Christophe [EMAIL PROTECTED] wrote: I was asked how to automate the procedure, and I couldn't answer. http://www.postgresql.org/docs/8.3/interactive/backup.html Regarding the SQL backup option for small databases, I use an OS task scheduler ( *nix Cron-job, MS task-scheduler) to automatically call my custom script file designed to handle that backing up of my databases. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng -- 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] Cursor
Bob Pawley [EMAIL PROTECTED] writes: Following is more complete. The balance of the trigger that is not shown works when tested separately. I didn't include it because it is quite long. Hmm, I still don't see anything that looks like a syntax error, but I'll bet this is a name collision rather than the effect you want: DECLARE process_total integer ; process_id integer ; ^^ procgraphic cursor for select process_id from p_id.p_id, processes_count ^^ where p_id.p_id.p_id_id = processes_count.p_id_id; You probably ought to qualify the column reference in the cursor. 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] Cursor
On Tue, Jul 29, 2008 at 5:42 PM, Bob Pawley [EMAIL PROTECTED] wrote: begin Don't you need a ; after your begin...? -- - David T. Wilson [EMAIL PROTECTED] -- 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] Cursor
-- Original message -- From: Tom Lane [EMAIL PROTECTED] Bob Pawley [EMAIL PROTECTED] writes: Following is more complete. The balance of the trigger that is not shown works when tested separately. I didn't include it because it is quite long. Hmm, I still don't see anything that looks like a syntax error, but I'll bet this is a name collision rather than the effect you want: DECLARE process_total integer ; process_id integer ; ^^ procgraphic cursor for select process_id from p_id.p_id, processes_count ^^ where p_id.p_id.p_id_id = processes_count.p_id_id; ^^ Just to clarify is this supposed to be schema p_id,table p_id,column p_id_id? You probably ought to qualify the column reference in the cursor. regards, tom lane -- Adrian Klaver [EMAIL PROTECTED] -- 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] Cursor
Thanks Tom Qualifying the column was the solution. Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL pgsql-general@postgresql.org Sent: Tuesday, July 29, 2008 2:51 PM Subject: Re: [GENERAL] Cursor Bob Pawley [EMAIL PROTECTED] writes: Following is more complete. The balance of the trigger that is not shown works when tested separately. I didn't include it because it is quite long. Hmm, I still don't see anything that looks like a syntax error, but I'll bet this is a name collision rather than the effect you want: DECLARE process_total integer ; process_id integer ; ^^ procgraphic cursor for select process_id from p_id.p_id, processes_count ^^ where p_id.p_id.p_id_id = processes_count.p_id_id; You probably ought to qualify the column reference in the cursor. 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] Cursor
Yes Bob - Original Message - From: Adrian Klaver [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED]; Bob Pawley [EMAIL PROTECTED] Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL pgsql-general@postgresql.org Sent: Tuesday, July 29, 2008 3:03 PM Subject: Re: [GENERAL] Cursor -- Original message -- From: Tom Lane [EMAIL PROTECTED] Bob Pawley [EMAIL PROTECTED] writes: Following is more complete. The balance of the trigger that is not shown works when tested separately. I didn't include it because it is quite long. Hmm, I still don't see anything that looks like a syntax error, but I'll bet this is a name collision rather than the effect you want: DECLARE process_total integer ; process_id integer ; ^^ procgraphic cursor for select process_id from p_id.p_id, processes_count ^^ where p_id.p_id.p_id_id = processes_count.p_id_id; ^^ Just to clarify is this supposed to be schema p_id,table p_id,column p_id_id? You probably ought to qualify the column reference in the cursor. regards, tom lane -- Adrian Klaver [EMAIL PROTECTED] -- 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] Cursor
Bob Pawley [EMAIL PROTECTED] writes: Qualifying the column was the solution. Huh. What was the error message you got, exactly? Because it doesn't seem like that should have led to a syntax error. 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] Cursor
The syntax error was running the function while not in a trigger. The trigger gave null as a return. The error was syntax error at or near Open. Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Richard Huxton [EMAIL PROTECTED]; PostgreSQL pgsql-general@postgresql.org Sent: Tuesday, July 29, 2008 3:30 PM Subject: Re: [GENERAL] Cursor Bob Pawley [EMAIL PROTECTED] writes: Qualifying the column was the solution. Huh. What was the error message you got, exactly? Because it doesn't seem like that should have led to a syntax error. 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] Cursor
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote: No, he does need an OPEN. Really? I thought that PG didn't use OPEN: The PostgreSQL server does not implement an OPEN statement for cursors; a cursor is considered to be open when it is declared. http://www.postgresql.org/docs/8.3/interactive/sql-declare.html -- 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 do I set up automatic backups?
On Tue, Jul 29, 2008 at 2:24 PM, Rob Richardson [EMAIL PROTECTED] wrote: Greetings again! A few days ago, I visited a customer's site to talk about administering our system, which is developed around a PostGres database. One of the topics was how to back up the database. I described the process of using PgAdmin to back up and restore a database, and I said a backup should be done every night. I was asked how to automate the procedure, and I couldn't answer. A database administrator said, There's got to be a way. Otherwise, PostGres wouldn't have survived. I agree with him. The only answers I've found on the Internet involve creating a password-less account and using that to run pg_dump. What is the official best way to automatically back up a PostGres database? For future reference, you'll get less scattered ansewrs if you tell us what OS you're running on, specifically whether or not it's unix or windows. In unix you can write a handy dandy bash shell script like this: #!/bin/bash if (! (pg_dump dbname /dir/filename.sql)); then echo Backup failed|sendmail -s admin alert [EMAIL PROTECTED]; fi; or something like that to run as a crontab job. -- 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] Cursor
Christophe wrote: On Jul 29, 2008, at 2:35 PM, Tom Lane wrote: No, he does need an OPEN. Really? I thought that PG didn't use OPEN: The PostgreSQL server does not implement an OPEN statement for cursors; a cursor is considered to be open when it is declared. http://www.postgresql.org/docs/8.3/interactive/sql-declare.html It's different in PL/pgSQL. Before a cursor can be used to retrieve rows, it must be opened. (This is the equivalent action to the SQL command DECLARE CURSOR.) http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] Cursor
On Jul 29, 2008, at 4:51 PM, Klint Gore wrote: It's different in PL/pgSQL. Ah, yes, sorry, didn't catch that it was a PL/pgSQL function. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
