Re: [GENERAL] Application takes longer time to fetch large no of records from Postgresql 9.0.3
On Mon, Oct 15, 2012 at 9:24 PM, Deven Thaker devenmtha...@gmail.comwrote: Hi, My application takes longer time (we see time out even) when data to be fetched from Postgresql 9.0.3 is around 190 records. I am doing an improvement at application level, but from database side any performance tuning do i need to do? When you say fetched do you mean the select query takes a long time, or that fetch operations on a cursor get slow after a certain point? Best Wishes, Chris Travers Regards
[GENERAL] return query execute SQL-problem
Hi there, here is something I don't quite grasp (PG 9.1.3): This function: CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS $BODY$ declare sql text; begin sql:='SELECT ''sometext''::text as alias'; return query execute SQL; end; $BODY$ LANGUAGE plpgsql VOLATILE; returns its result as: contactking=# select * from f_aliastest(); f_aliastest - sometext (1 row) I was hoping I'd get the data back as 'alias', not as 'f_aliastest'. If I do: contactking=# select alias from f_aliastest(); ERROR: column alias does not exist LINE 1: select alias from f_aliastest(); Is there a way that I can make my function return the field aliases? Best wishes from Berlin, Maximilian Tyrtania http://www.contactking.de -- 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] Application takes longer time to fetch large no of records from Postgresql 9.0.3
On 10/16/2012 12:40 PM, Craig Ringer wrote: On 10/16/2012 12:24 PM, Deven Thaker wrote: Hi, My application takes longer time (we see time out even) when data to be fetched from Postgresql 9.0.3 is around 190 records. I am doing an improvement at application level, but from database side any performance tuning do i need to do? I have not changed any parameters in postgresql.conf, so using default values. Any recommendations to improve the performance. Hi My earlier reply was a tad grumpy; my apologies. The point stands, but the wording could've been nicer. It isn't really clear *where* the slowness is. That's why I'm asking for EXPLAIN (BUFFERS, ANALYZE) results. If that's fast then it tells you the problem is somewhere else. What is the client application? What database driver does it use - PgJDBC? libpq? psqlODBC? npgsql? Something else? What language is it written in? Does it read the whole result set into memory at once, or does it use a cursor? If you're reading the whole result set into memory at once you might want to consider using DECLARE and FETCH: http://www.postgresql.org/docs/current/static/sql-declare.html so you don't have to read the whole result into memory at once. -- 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] return query execute SQL-problem
On 2012-10-16 10:44, Maximilian Tyrtania wrote: Hi there, here is something I don't quite grasp (PG 9.1.3): This function: CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS $BODY$ declare sql text; begin sql:='SELECT ''sometext''::text as alias'; return query execute SQL; end; $BODY$ LANGUAGE plpgsql VOLATILE; returns its result as: contactking=# select * from f_aliastest(); f_aliastest - sometext (1 row) I was hoping I'd get the data back as 'alias', not as 'f_aliastest'. If I do: contactking=# select alias from f_aliastest(); ERROR: column alias does not exist LINE 1: select alias from f_aliastest(); Is there a way that I can make my function return the field aliases? Best wishes from Berlin, Maximilian Tyrtania http://www.contactking.de You can use AS select f_aliastest() AS alias; Regards, C -- 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] Who is LISTENing?
On 10/16/2012 07:49 AM, Raghavendra wrote: On Tue, Oct 16, 2012 at 11:01 AM, Sim Zacks s...@compulab.co.il wrote: On 10/15/2012 06:11 PM, rektide wrote: Hi pgsql-general, I'm interested in writing a supervisory process that can insure worker processes are running/spawn new ones if not. These workers will mainly be responsible for LISTENing to the db, which is emitting triggered_change_notification s. Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it? Links: http://www.postgresql.org/docs/9.2/interactive/sql-notify.html http://www.postgresql.org/docs/9.2/interactive/tcn.html Regards, You can look in the pg_listener table. The relname is the Listen/notify code that you call and the listenerpid is the OS pid. You can see more details of that in the pg_stat_activity Sim I guess pg_listener table is deprecated and no longer exist in PG 9.0 onwards. --Raghav I guess I'm dating myself (still on 8.2) This discussion indicates that it is not possible. http://archives.postgresql.org/pgsql-hackers/2011-06/msg00016.php
Re: [GENERAL] return query execute SQL-problem
On Tue, Oct 16, 2012 at 09:44:03AM +0200, Maximilian Tyrtania wrote: Is there a way that I can make my function return the field aliases? CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS function defined as above returns set of values without any name. name is chosen by pg. you can't rename the columns in the return query part, but you can by changing definition of function to: create or replace function f_aliastest(OUT alias TEXT) returns setof record AS ... Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- 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] return query execute SQL-problem
Am 16.10.2012 um 11:37 schrieb hubert depesz lubaczewski dep...@depesz.com: create or replace function f_aliastest(OUT alias TEXT) returns setof record AS … Ah, yes, I forgot about the OUT parameters, thanks for the reminder! Maximilian Tyrtania Contact King Software Entwicklung Maximilian Tyrtania http://www.contactking.de -- 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] Strategies/Best Practises Handling Large Tables
Thank you all for your suggestions. Since all of you recommended the Partition option, I decided to try it out myself. I found a function that created partition tables for every month in the last two years. A trigger was also added to the parent table to ensure that every insert into it from hence forth will be inserted into the approapriate child table. However, there were a few observations that I made which I would appreciate your comments on: 1. Since existing data was in the parent table, I had to do a pg_dump on it, drop it, and then to a restore on it to force the trigger to work on existing data. Is this how partitioning existing data should be done? 2. I noticed that there are two copies of the same record - i.e the one that was inserted into the parent table and another that was inserted in the child table. If I delete the record in the parent table, the child record gets automatically deleted. I was under the impression that partitioning meant that my parent table will not be large anymore because the data will be moved to smaller child tables. Is this the case? 3. Is there a way for me to evaluate the effectiveness of the partitioned table? Would performing an Explain Analyse allow me to determine whether querying the parent table for statistics is quicker than querying against a massive non-partitioned table? Thank you. On Oct 13, 2012 3:49 AM, John R Pierce pie...@hogranch.com wrote: On 10/12/12 7:44 AM, Chitra Creta wrote: 1. Purge old data 2. Reindex 3. Partition 4. Creation of daily, monthly, yearly summary tables that contains aggregated data specific to the statistics required if most of your queries read the majority of the tables, indexing will be of little help parittioning will aid in purging old data, as you can partitions by date (for instance, by week) and drop whole partitions rather than deleting individual records. aggregate tables likely will be the biggest win for your statistics if they reduce the mount of data you need to query. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strategies/Best Practises Handling Large Tables
On Tue, Oct 16, 2012 at 09:26:09PM +1100, Chitra Creta wrote: Thank you all for your suggestions. Since all of you recommended the Partition option, I decided to try it out myself. I found a function that created partition tables for every month in the last two years. A trigger was also added to the parent table to ensure that every insert into it from hence forth will be inserted into the approapriate child table. However, there were a few observations that I made which I would appreciate your comments on: 1. Since existing data was in the parent table, I had to do a pg_dump on it, drop it, and then to a restore on it to force the trigger to work on existing data. Is this how partitioning existing data should be done? I just wrote a one-time function to move it. 2. I noticed that there are two copies of the same record - i.e the one that was inserted into the parent table and another that was inserted in the child table. If I delete the record in the parent table, the child record gets automatically deleted. I was under the impression that partitioning meant that my parent table will not be large anymore because the data will be moved to smaller child tables. Is this the case? The data *is* in the child tables. Queries on the parent tables, by default, affect data in the child tables. So, issuing a SELECT against your parent table will also query the child tables. DELETE will, similarly, delete data in the child tables. You may target just the parent table using ONLY, e.g. SELECT * FROM ONLY foo. This behavior is also controlled by the GUC sql_inheritance, though I encourage you not to change this value. To get a better idea of what it happening, look at the output from EXPLAIN to see all the tables that are being included in your plan. 3. Is there a way for me to evaluate the effectiveness of the partitioned table? Would performing an Explain Analyse allow me to determine whether querying the parent table for statistics is quicker than querying against a massive non-partitioned table? Well, you can do it with EXPLAIN ANALYZE, or you can do it by timing your query, so that the overhead of EXPLAIN ANALYZE does not come into play. Also, I assume you've read this: http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html That link will be helpful in understanding how partitioning could benefit you. -Ryan Kelly -- 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] Who is LISTENing?
On 2012-10-15, rektide rekt...@voodoowarez.com wrote: Hi pgsql-general, I'm interested in writing a supervisory process that can insure worker processes are running/spawn new ones if not. These workers will mainly be responsible for LISTENing to the db, which is emitting triggered_change_notification s. Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it? Notifies are not reliable, what I mean is they are best effort this is unlike the other things postgres does, there's no guarantee that you'll get the message, for example the network might go down at the same time as the notifiy is emitted, if that happenss a listening client would miss the notify message and by the time it reconnects the message is gone. If you need reliable mesaging use a mesage queue in a table: Emit a notify when you insert into the queue and the listeners can check the queue when they connect, and again after each notify. OTOH, if best effort is good enough, the table pg_stat_activity will give you the username of each connected client. perhaps ypu can infer from that who was probably listening when you last checked... -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: transitive pruning optimization on the right side of a join for partition tables
Hi, I have tried more on this, if you just use an equality constraint on the foreign key in the right side of the join, it works. Other constraints like between, in, , do not work. Anyone else got to achieve this in postgresql? -- View this message in context: http://postgresql.1045698.n5.nabble.com/transitive-pruning-optimization-on-the-right-side-of-a-join-for-partition-tables-tp5726019p5728401.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] return query execute SQL-problem
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Maximilian Tyrtania Sent: Tuesday, October 16, 2012 3:44 AM To: pgsql-general@postgresql.org Subject: [GENERAL] return query execute SQL-problem Hi there, here is something I don't quite grasp (PG 9.1.3): This function: CREATE OR REPLACE FUNCTION f_aliastest() RETURNS setof text AS $BODY$ declare sql text; begin sql:='SELECT ''sometext''::text as alias'; return query execute SQL; end; $BODY$ LANGUAGE plpgsql VOLATILE; returns its result as: contactking=# select * from f_aliastest(); f_aliastest - sometext (1 row) I was hoping I'd get the data back as 'alias', not as 'f_aliastest'. If I do: contactking=# select alias from f_aliastest(); ERROR: column alias does not exist LINE 1: select alias from f_aliastest(); Is there a way that I can make my function return the field aliases? Best wishes from Berlin, Maximilian Tyrtania http://www.contactking.de Use the RETURNS TABLE form of the output definition: CREATE FUNCTION ... RETURNS TABLE (alias varchar) AS $$ ... $$ There is no way to make the name dynamic or to specify it using the contents of the function body. David J. -- 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] return query execute SQL-problem
Am 16.10.2012 um 10:56 schrieb Condor con...@stz-bg.com: You can use AS select f_aliastest() AS alias; Yeah, thanks, well, my question is basically if there is a way to make the function alias-savvy. In the meantime I realized that the problem is not limited to return query execute SQL but to return query in general. Maximilian Tyrtania http://www.contactking.de -- 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] Strategies/Best Practises Handling Large Tables
On Fri, Oct 12, 2012 at 10:00 AM, Lonni J Friedman netll...@gmail.com wrote: On Fri, Oct 12, 2012 at 7:44 AM, Chitra Creta chitracr...@gmail.com wrote: Hi, I currently have a table that is growing very quickly - i.e 7 million records in 5 days. This table acts as a placeholder for statistics, and hence the records are merely inserted and never updated or deleted. Many queries are run on this table to obtain trend analysis. However, these queries are now starting to take a very long time (hours) to execute due to the size of the table. I have put indexes on this table, to no significant benefit. Some of the other strategies I have thought of: 1. Purge old data 2. Reindex 3. Partition 4. Creation of daily, monthly, yearly summary tables that contains aggregated data specific to the statistics required Does anyone know what is the best practice to handle this situation? I would appreciate knowledge sharing on the pros and cons of the above, or if there are any other strategies that I could put in place. Partitioning is prolly your best solution. 3 4 sound like variations on the same thing. Before you go that route, you should make sure that your bottleneck is really a result of the massive amount of data, and not some other problem. Are you sure that the indices you created are being used, and that you have all the indices that you need for your queries? Look at the query plan output from EXPLAIN, and/or post here if you're unsure. Partitioning is not a strategy to improve query performance unless you are exploiting the data structure in some way through the partition. merlin -- 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] Who is LISTENing?
On Tue, Oct 16, 2012 at 5:18 AM, Jasen Betts ja...@xnet.co.nz wrote: On 2012-10-15, rektide rekt...@voodoowarez.com wrote: Hi pgsql-general, I'm interested in writing a supervisory process that can insure worker processes are running/spawn new ones if not. These workers will mainly be responsible for LISTENing to the db, which is emitting triggered_change_notification s. Is there any means to check a NOTIFY queue to see who or if anyone is LISTEN ing on it? Notifies are not reliable, what I mean is they are best effort this is unlike the other things postgres does, there's no guarantee that you'll get the message, for example the network might go down at the same time as the notifiy is emitted, if that happenss a listening client would miss the notify message and by the time it reconnects the message is gone. If you need reliable mesaging use a mesage queue in a table: Emit a notify when you insert into the queue and the listeners can check the queue when they connect, and again after each notify. OTOH, if best effort is good enough, the table pg_stat_activity will give you the username of each connected client. perhaps ypu can infer from that who was probably listening when you last checked... One of the goals of pg_message_queue was to create a simple message queue system with listen/notify support (which is missing in pgq btw). It is designed to be reasonably reliable but is still relatively feature poor. It will probably never be big and professional like pgq, but may be very helpful in many cases nonetheless. http://pgxn.org/dist/pg_message_queue/ The basic idea is that it automates some of the basic bailing twine you might want to create in such a solution. Future versions will have more logging, anti-refutation controls, etc. Note the way we addressed this problem was that listen/notify was optional. An application could be run from cron once a day and process queued items, or it could connect and wait for notifications. As of 0.1, it supports payload types of text, xml, and bytea. More types coming soon in 0.2. I would be interested in feedback on this, and if anyone wants to contribute, I certainly will be happy to facilitate. Best Wishes, Chris Travers
[GENERAL] PostgreSQL training recommendations?
Hi pgsql-general, I'm looking for advice on good training courses for PostgreSQL (on- or off-site, on- or off-line). I'm hoping to find something that can cover basic administration, performance optimization topics, and clustering tools like Slony and pgpool for someone. I realize that PostgreSQL documentation is a great resource, but I'm looking for something more intensive and expert-driven. Do any of you have recommendations based on courses you took, had colleagues take, or teach yourself? Thanks in advance, Matt Kappel -- 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] Strategies/Best Practises Handling Large Tables
On 10/12/2012 09:44 AM, Chitra Creta wrote: 4. Creation of daily, monthly, yearly summary tables that contains aggregated data specific to the statistics required If the partitioning doesn't help you, I strongly urge you to build one or more Fact tables with appropriate Diminsions. If you don't know what these terms mean, there are lots of books on the subject. They're very versatile for producing fast reports on varying inputs. You can also layer them by having cascading levels of aggregation from day - month - year, and so on. These kinds of reporting structures are perfect for huge data accumulation warehouses. The book most recommended to me back in the day was The Data Warehouse Toolkit, and I can also vouch that it covers these subjects pretty well. I still recommend partitioning simply due to maintenance overhead, but you'll want to look into this too. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] transaction log file 000000010000097600000051 could not be archived: too many failures
HI I am getting the following error in my postgresql log file. cp: cannot stat `pg_xlog/000109760051': No such file or directory gzip: /usr/local/pgsql/wal_archive/000109760051: No such file or directory 76800 2012-10-16 07:58:11.903 CDT 0 LOG: archive command failed with exit code 1 76800 2012-10-16 07:58:11.903 CDT 0 DETAIL: The failed archive command was: /usr/local/pgsql/data/wal_archive_command.sh pg_xlog/000109760051 000109760051 76800 2012-10-16 07:58:11.903 CDT 01000 WARNING: transaction log file 000109760051 could not be archived: too many failures Earlier when the database was started , the directory /usr/local/pgsql/wal_archive/ did not exist..Now I have created this directory,,,But im still getting this error... Moreover there are around 15000 files in data/pg_xlog/archive_status and all of them have status as .ready.. Can anyone let me know if they have faced this kind of issue before and how do I rectify it? -- DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of *Ver sé Innovation Pvt Ltd*.
Re: [GENERAL] PostgreSQL training recommendations?
I assume the EntrerpriseDB certification seminars are an obvious quick answer: http://www.enterprisedb.com/products-services-training/training/dba-training But TBH, I find the PostgreSQL manual to be an excelent guide if you don't mind reading. It is extremely well written (kudos to whoever is on the writing team), definitely written by experts, it delves reasonably enough into detail where needed and most of all: it serves not only as a Pg manual, but as a DB theory/good practice manual as well. I realize that 2.8Kpages is not easy to digest, but the first 30 Chapters seem to cover more than enough to just get you started (though not Slony/pgpool). best regards, Thalis K. On Tue, Oct 16, 2012 at 12:23 PM, Matthew Kappel mkap...@cray.com wrote: Hi pgsql-general, I'm looking for advice on good training courses for PostgreSQL (on- or off-site, on- or off-line). I'm hoping to find something that can cover basic administration, performance optimization topics, and clustering tools like Slony and pgpool for someone. I realize that PostgreSQL documentation is a great resource, but I'm looking for something more intensive and expert-driven. Do any of you have recommendations based on courses you took, had colleagues take, or teach yourself? Thanks in advance, Matt Kappel -- 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] transaction log file 000000010000097600000051 could not be archived: too many failures
Mathew Thomas mathew.tho...@verse.in writes: I am getting the following error in my postgresql log file. cp: cannot stat `pg_xlog/000109760051': No such file or directory If there's a .ready file corresponding to that, remove it. I'm not entirely sure how you could have ended up with a .ready file but not the base file, but that seems the only explanation of this symptom. 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] PostgreSQL training recommendations?
On Tue, Oct 16, 2012 at 02:53:14PM -0300, Thalis Kalfigkopoulos wrote: I assume the EntrerpriseDB certification seminars are an obvious quick answer: http://www.enterprisedb.com/products-services-training/training/dba-training But TBH, I find the PostgreSQL manual to be an excelent guide if you don't mind reading. It is extremely well written (kudos to whoever is on the writing team), definitely written by experts, it delves reasonably enough into detail where needed and most of all: it serves not only as a Pg manual, but as a DB theory/good practice manual as well. I realize that 2.8Kpages is not easy to digest, but the first 30 Chapters seem to cover more than enough to just get you started (though not Slony/pgpool). I think the big thing the training manual is missing is giving inexperienced users a framework to understand all the pieces. Training does help in that area, and I am unclear how we could improve the manual to address that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] PostgreSQL training recommendations?
On Tue, Oct 16, 2012 at 02:34:37PM -0400, Bruce Momjian wrote: On Tue, Oct 16, 2012 at 02:53:14PM -0300, Thalis Kalfigkopoulos wrote: I assume the EntrerpriseDB certification seminars are an obvious quick answer: http://www.enterprisedb.com/products-services-training/training/dba-training But TBH, I find the PostgreSQL manual to be an excelent guide if you don't mind reading. It is extremely well written (kudos to whoever is on the writing team), definitely written by experts, it delves reasonably enough into detail where needed and most of all: it serves not only as a Pg manual, but as a DB theory/good practice manual as well. I realize that 2.8Kpages is not easy to digest, but the first 30 Chapters seem to cover more than enough to just get you started (though not Slony/pgpool). I think the big thing the training manual is missing is giving inexperienced users a framework to understand all the pieces. Training does help in that area, and I am unclear how we could improve the manual to address that. As a disclaimer, I should add that I do training for EnterpriseDB. I think the communication of a mental framework in understanding Postgres is one of the most valuable things I can give students. I think my presentations have a similar focus: http://momjian.us/main/presentations/ -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] PostgreSQL training recommendations?
True about the lack of framework putting the pieces together and providing an overview. Also IMHO another difficulty the manual poses is that the reader doesn't have a way to confirm his level of understanding after reading a chapter. Letting aside the concepts for which creating a scenario/test-case are downright complex, hard to reproduce or dependent on a per-installation basis, the learning experience could greatly benefit from a pg-tailored QA section at the end of each chapter. Perhaps even a downloadable test database to play with? And not wanting to just be lighting fires here, I'd be happy to volunteer. Now I'd understand the Pg manual writers being reluctant about shifting from manual to DB-book, but I'm guessing, the manual being as well written as it is, that many of us are already using it as a learning book anyway. best regards, Thalis K. On Tue, Oct 16, 2012 at 3:41 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Oct 16, 2012 at 02:34:37PM -0400, Bruce Momjian wrote: On Tue, Oct 16, 2012 at 02:53:14PM -0300, Thalis Kalfigkopoulos wrote: I assume the EntrerpriseDB certification seminars are an obvious quick answer: http://www.enterprisedb.com/products-services-training/training/dba-training But TBH, I find the PostgreSQL manual to be an excelent guide if you don't mind reading. It is extremely well written (kudos to whoever is on the writing team), definitely written by experts, it delves reasonably enough into detail where needed and most of all: it serves not only as a Pg manual, but as a DB theory/good practice manual as well. I realize that 2.8Kpages is not easy to digest, but the first 30 Chapters seem to cover more than enough to just get you started (though not Slony/pgpool). I think the big thing the training manual is missing is giving inexperienced users a framework to understand all the pieces. Training does help in that area, and I am unclear how we could improve the manual to address that. As a disclaimer, I should add that I do training for EnterpriseDB. I think the communication of a mental framework in understanding Postgres is one of the most valuable things I can give students. I think my presentations have a similar focus: http://momjian.us/main/presentations/ -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] PostgreSQL training recommendations?
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Thalis Kalfigkopoulos Sent: Tuesday, October 16, 2012 3:24 PM To: Bruce Momjian Cc: Matthew Kappel; pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL training recommendations? True about the lack of framework putting the pieces together and providing an overview. Also IMHO another difficulty the manual poses is that the reader doesn't have a way to confirm his level of understanding after reading a chapter. Letting aside the concepts for which creating a scenario/test-case are downright complex, hard to reproduce or dependent on a per-installation basis, the learning experience could greatly benefit from a pg-tailored QA section at the end of each chapter. Perhaps even a downloadable test database to play with? And not wanting to just be lighting fires here, I'd be happy to volunteer. Now I'd understand the Pg manual writers being reluctant about shifting from manual to DB-book, but I'm guessing, the manual being as well written as it is, that many of us are already using it as a learning book anyway. best regards, Thalis K. Thalis, please do not top-post; especially when others have already bottom-posted before you. IMO writing and maintaining educational/training materials is a somewhat different skill set and focus than writing and maintaining technical documentation. They have their own timelines and needs and the gatekeepers for the documentation are not necessarily the best people to gatekeep educational materials. There are many different ideas out there - both content/format as well as pricing models. For better and worse the PostgreSQL core community does not attempt to play favorites or provide recommendations or a centralized database of what is out there. The wiki and FAQ extend what is provided for in the documentation somewhat but on the whole it is a very loose coalition. Such decentralization, combined with very little spare capacity of PostgreSQL skilled persons, makes getting started from scratch a difficult proposition. Aside from all of that the documentation is written in SGML thus making contributing that much more difficult. If you are interested in volunteering then just do it. Develop content and then work with the community to determine how to best integrate it with the existing materials out there or at worse see if someone will host it for you. David J. -- 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] PostgreSQL training recommendations?
On Tue, Oct 16, 2012 at 16:24:08 -0300, Thalis Kalfigkopoulos tkalf...@gmail.com wrote: Also IMHO another difficulty the manual poses is that the reader doesn't have a way to confirm his level of understanding after reading a chapter. It isn't too hard to play with a toy database. I personally found (and still find) the Postgres manual to be a great resource for learning SQL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Does Postgres Object-Relational Syntax follow Standard?
Hi. I was having a discussion with people at work about the Postgres object-relational syntax. The question came up: does this follow an SQL standard? Or is it rather a Postgres-only feature with different RDBMSs doing it differently? I tried some quick checks on Google and Wikipedia but couldn't find clear answers. There seem to be different SQL standards (for example SQL 2003, SQL 2008), but from what I can see, these don't cover object-relational syntax. -Will -- 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] PostgreSQL training recommendations?
On 10/16/12 3:24 PM, Thalis Kalfigkopoulos wrote: Now I'd understand the Pg manual writers being reluctant about shifting from manual to DB-book, but I'm guessing, the manual being as well written as it is, that many of us are already using it as a learning book anyway. The official manual is a reference manual that also includes some good tutorial material. Just trying to cover that depth well, it's already so large as to be cumbersome--both from the perspective of new readers and the people maintaining it. Expecting to expand its scope even further toward the tutorial and example side is not something I'd expect to gain much traction. Every example that appears in the manual is yet another place for the documentation to break when code changes are made. And it's the same group of people maintaining both the documentation and the code. Anyone who tries to rev up adding even more docs is going to pull focus off new code. Would you like the core features to expand or to get a new type of documentation? The way things are organized right now, you can't get both. I would say that it's easier to write 400 pages of material outside of the manual and distribute them to the world than to add 40 pages to the official manual. And I say that as someone who tried wandering down both paths to see which was more productive. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- 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] Does Postgres Object-Relational Syntax follow Standard?
On 10/17/2012 05:00 AM, Will Rutherdale (rutherw) wrote: Hi. I was having a discussion with people at work about the Postgres object-relational syntax. What syntax specifically? Do you mean table inheritance and SELECT ONLY ? -- 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] Does Postgres Object-Relational Syntax follow Standard?
On Tue, Oct 16, 2012 at 2:00 PM, Will Rutherdale (rutherw) ruth...@cisco.com wrote: Hi. I was having a discussion with people at work about the Postgres object-relational syntax. The question came up: does this follow an SQL standard? Or is it rather a Postgres-only feature with different RDBMSs doing it differently? I tried some quick checks on Google and Wikipedia but couldn't find clear answers. There seem to be different SQL standards (for example SQL 2003, SQL 2008), but from what I can see, these don't cover object-relational syntax. Not really. The one area the standard discusses in this area, namely single inheritance for structured data types, is not supported by PostgreSQL. As far as I can tell, the SQL 2003 standard followed more or less the approach Illustra (which began as a Pg fork but had a totally independent SQL implementation) but limited it to structured data types only and thus avoided issues like jagged rows (which survive in Informix, but pose practical programming challenges and therefore have never been supported on PostgreSQL). The one area that is supported is CREATE TABLE foo OF TYPE bar; but that's pretty anemic support if you ask me. In my view, while there are rough edges, the PostgreSQL approach is richer than the SQL 2003 approach, and I suspect the reason for the lack of SQL 2003 UNDER supertype support is that there hasn't been sufficient demand to justify implementing it. This isn't a commonly used feature of Oracle or DB2. I would say that while there are some object-relational aspects to SQL 2003, the overlap between those and the PostgreSQL model is non-existent for practical purposes. I have actually really come to like the PostgreSQL model. Best Wishes, Chris Travers
[GENERAL] 9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered
Hi all
In this SO question:
http://dba.stackexchange.com/questions/26905/how-do-i-implement-insert-if-not-found-for-transactions-at-serializable-isolatio/26909#26909
the author is running a series of queries that I'd expect to abort on
commit with a serialisation failure. No such failure occurs, and I'm
wondering why.
SETUP
create table artist (id serial primary key, name text);
SESSION 1 SESSION 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN ISOLATION LEVEL
SERIALIZABLE;
SELECT id FROM artist
WHERE name = 'Bob';
INSERT INTO artist (name)
VALUES ('Bob')
INSERT INTO artist (name)
VALUES ('Bob')
COMMIT; COMMIT;
I'd expect one of these two to abort with a serialization failure and
I'm not sure I understand why they don't in 9.1/9.2's new serializable
mode. Shouldn't the SELECT for Bob cause the insertion of Bob in the
other transaction to violate serializability?
--
Craig Ringer
[GENERAL] Streaming replication failed to start scenarios
Hi all, I have been testing streaming replication in windows with postgres 9.1.1. For few scenario's I haven't found a solution. Please advice me. 1. Precautions before promoting standby server to primary manually considering the dead primary server ?? 2. How could we ensure the standby has received all transactions sent by primary till the point primary server is dead. (Meaning the dead primary and standby server are exactly same, so that the dead primary comes back it can be turned to standby without any issues). 3. When the dead primary is switchedto standby the streaming is not happening due to current_wal_location is ahead in the standby server is ahead of wal_sent_location. In this case how can I start streaming without taking a fresh base backup from current primary ?? 4. When the dead primary comes back the DB still accepts data and it goes to out of sync with the current primary and streaming won't start. Is there any solution for this case ?? Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
