Re: [GENERAL] Postgresql CBT
On 05/24/2011 02:05 AM, sade...@yahoo.com wrote: Id like to familiarize with postgresql and looking for a decent CBT but not able to find it. Most PostgreSQL training happens in one of the following ways: -Self-learning using the manual or one of the PostgreSQL books -In-person training at shared classrooms or on-site at companies who hire a trainer -Training offered as part of the many PostgreSQL conferences -Webcasts of training material You can find many of the schedules for these at http://www.postgresql.org/about/eventarchive , books are at http://www.postgresql.org/docs/books/ , and the large manual is at http://www.postgresql.org/docs/manuals/ The closest thing to CBT I know of are the videos recorded of past conference and user's group sessions. See http://vimeo.com/channels/postgres and http://fosslc.org/drupal/category/community/databases/postgresql for some samples. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] unable to restore. pg_restore: implied data-only restore
Tim Uckun writes: >> There is no support for that built into pg_dump. You could try: >> * dumping to a text script and doing search-and-replace for the schema >> name on the script file. > I did a dump without privileges or owners so I was thinking I could > just replace the > SET search_path = public, pg_catalog; > At the top and it might work. However I do see a lot of comments with > the schema name in them like. The comments are not a problem, but in all but the simplest DB designs there are likely to be some embedded references to the schema name, too. 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 CBT
On 05/24/2011 10:49 AM, Scott Marlowe wrote: On Tue, May 24, 2011 at 8:36 AM, Vick Khera wrote: On Tue, May 24, 2011 at 10:33 AM, Scott Marlowe wrote: Id like to familiarize with postgresql and looking for a decent CBT but not able to find it. Could someone help pls? CBT? Please define. my guess is computer based training. Oh good. My first response from google, with safe search turned off, was much more distressing... : Cognitive behavioral therapy is only necessary for people migrating to PostgreSQL after using Access as if it were a database for too long. That's a very specific type of post-traumatic stress disorder, and mild cases can be treated with CBT. Severe cases will instead require ECT, aka electroshock. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] unable to restore. pg_restore: implied data-only restore
> > There is no support for that built into pg_dump. You could try: That's too bad. > > * dumping to a text script and doing search-and-replace for the schema > name on the script file. I did a dump without privileges or owners so I was thinking I could just replace the SET search_path = public, pg_catalog; At the top and it might work. However I do see a lot of comments with the schema name in them like. -- -- TOC entry 18 (class 1255 OID 16417) -- Dependencies: 6 -- Name: get_text_document(character varying); Type: FUNCTION; Schema: public; Owner: - -- Does pg_restore use these comments in some way? Should I change those as well? > * temporarily renaming the target database's public schema out of the > way, then renaming after the restore. > > * doing the schema rename on the source database before you dump. This might be the easiest way now that I think about it. -- 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] unable to restore. pg_restore: implied data-only restore
Tim Uckun writes: > I am trying to backup one database and restore it into a new schema in > another database. Database1 has the tables in the public schema > database2 has some tables in the public schema but their names will > clash so the database needs to be stored in a different schema. There is no support for that built into pg_dump. You could try: * dumping to a text script and doing search-and-replace for the schema name on the script file. * temporarily renaming the target database's public schema out of the way, then renaming after the restore. * doing the schema rename on the source database before you dump. 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] Preventing OOM kills
On Tue, May 24, 2011 at 7:01 PM, John R Pierce wrote: > On 05/24/11 5:50 PM, Andrej wrote: >> >> Add more RAM? Look at tunables for other processes on >> the machine? At the end of the day making the kernel shoot >> anything out of despair shouldn't be the done thing. > > somehow, 'real' unix has neither a OOMkiller nor does it flat out die under > heavy loads, it just degrades gracefully. I've seen Solaris and AIX and BSD > servers happily chugging along with load factors in the 100s, significant > portions of memory paging, etc, without completely crumbling to a halt. > Soimetimes I wonder why Linux even pretends to support virtual memory, as > you sure don't want it to be paging. I've found that on servers with multiple drives and the page file spread across them linux does pretty well when swapping out. Even going pretty far back, when I had 6 9G SCSI drives on an old Sparc 20 running RHEL with 256M ram the swapping was quite speedy with a 100M or so on each drive. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unable to restore. pg_restore: implied data-only restore
I am trying to backup one database and restore it into a new schema in another database. Database1 has the tables in the public schema database2 has some tables in the public schema but their names will clash so the database needs to be stored in a different schema. I back up like this. /usr/bin/pg_dump --host localhost --port 5432 --username tim --format custom --blobs --verbose --file "/usr/local/home/tim/tmp/database.backup" database1 /usr/bin/pg_restore --host localhost --port 5432 --username tim --dbname database2 --schema database1_schema --verbose "/usr/local/home/tim/tmp/database.backup" pg_restore: connecting to database for restore pg_restore: implied data-only restore Nothing gets restored. What is the proper way to restore databases into a particular schema? -- 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] Preventing OOM kills
On 05/24/11 5:50 PM, Andrej wrote: Add more RAM? Look at tunables for other processes on the machine? At the end of the day making the kernel shoot anything out of despair shouldn't be the done thing. somehow, 'real' unix has neither a OOMkiller nor does it flat out die under heavy loads, it just degrades gracefully. I've seen Solaris and AIX and BSD servers happily chugging along with load factors in the 100s, significant portions of memory paging, etc, without completely crumbling to a halt.Soimetimes I wonder why Linux even pretends to support virtual memory, as you sure don't want it to be paging. -- john r pierceN 37, W 123 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-general
Re: [GENERAL] Preventing OOM kills
On Tue, 2011-05-24 at 17:32 -0700, Yang Zhang wrote:
> PG tends to be picked on by the Linux OOM killer, so lately we've been
> forcing the OOM killer to kill other processes first with this script:
>
> while true; do
> for i in `pgrep postgres`; do
> echo -17 > /proc/$i/oom_adj
> done
> sleep 60
> done
>
> Is there a Better Way? Thanks in advance.
Why don't you start postmaster with this value? Here is what we do in
RPM init scripts.
PG_OOM_ADJ=-17
test x"$PG_OOM_ADJ" != x && echo "$PG_OOM_ADJ" > /proc/self/oom_adj
$SU -l postgres -c "$PGENGINE/postmaster -p '$PGPORT' -D '$PGDATA'
${PGOPTS} &" >> "$PGLOG" 2>&1 < /dev/null
Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] Preventing OOM kills
On Tue, May 24, 2011 at 6:50 PM, Andrej wrote: > On 25 May 2011 12:32, Yang Zhang wrote: >> PG tends to be picked on by the Linux OOM killer, so lately we've been >> forcing the OOM killer to kill other processes first with this script: >> >> while true; do >> for i in `pgrep postgres`; do >> echo -17 > /proc/$i/oom_adj >> done >> sleep 60 >> done >> >> Is there a Better Way? Thanks in advance. > > Add more RAM? Look at tunables for other processes on > the machine? At the end of the day making the kernel shoot > anything out of despair shouldn't be the done thing. I thought that setting vm.overcommit_memory=2 stopped the OOM killer. -- 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] Preventing OOM kills
On 25 May 2011 12:32, Yang Zhang wrote: > PG tends to be picked on by the Linux OOM killer, so lately we've been > forcing the OOM killer to kill other processes first with this script: > > while true; do > for i in `pgrep postgres`; do > echo -17 > /proc/$i/oom_adj > done > sleep 60 > done > > Is there a Better Way? Thanks in advance. Add more RAM? Look at tunables for other processes on the machine? At the end of the day making the kernel shoot anything out of despair shouldn't be the done thing. Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Preventing OOM kills
PG tends to be picked on by the Linux OOM killer, so lately we've been forcing the OOM killer to kill other processes first with this script: while true; do for i in `pgrep postgres`; do echo -17 > /proc/$i/oom_adj done sleep 60 done Is there a Better Way? Thanks in advance. -- 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] Values larger than 1/3 of a buffer page cannot be indexed (hstore)
Stefan Keller writes: > Hi Tom, hi all > Thanks, Tom, for your tipps. You answered 2011/5/1: >> (...), and there's no point in having the >> index column contents be the entire tags value (which is what's leading >> to the failure). Consider >> >> create index planet_osm_point_amenity on planet_osm_point ((tags->amenity)); > To get a more general purpose index I tried also: > CREATE INDEX planet_osm_point_tags ON planet_osm_point USING gist(tags); > -- ERROR: invalid hstore value found > -- SQL state: XX000 > And I'm really interested in being able to use GIST. GIST is also > recommended here: > http://www.bostongis.com/PrinterFriendly.aspx?content_name=loading_osm_postgis > But I still get error 'invalid hstore value found' on my machine - > whatever I do! Do you have a self-contained test case for this? The symptom seems similar to a bug we found back around 9.0 release --- maybe there's another instance of the same mistake someplace. 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] Postgres Triggers instead of requiring a field - fire when field not included
Hello, In Postgres 9.0, trying to prevent a recursive trigger by adding a column "notrigger". Psuedo code: create trigger before_update_holdings_table BEFORE UPDATE ON holdings FOR EACH ROW when (new.notrigger is not given) EXECUTE PROCEDURE before_update_holdings(); I see in the docs, I can do the opposite - have it fire only when a field is included. 1) Is there any way to do the above, have a trigger fire when a field is not specifically included? 2) If not, what is the easiest way to prevent recursive triggers (in the update trigger, other update are done to the table and the trigger should be ignored). Thanks,
Re: [HACKERS] [GENERAL] Error compiling sepgsql in PG9.1
2011/5/24 Kohei Kaigai : > The attached patch enables to abort configure script when we run it with > '--with-selinux' > option, but libselinux is older than minimum requirement to SE-PostgreSQL. > > As the documentation said, it needs libselinux-2.0.93 at least, because this > or later > version support selabel_lookup(3) for database object classes; used to > initial labeling. > > The current configure script checks existence of libselinux, but no version > checks. > (getpeercon_raw(3) has been a supported API for a long term.) > The selinux_sepgsql_context_path(3) is a good watermark of libselinux-2.0.93 > instead. Looks to me like you need to adjust the wording of the error message. Maybe "libselinux version 2.0.93 or newer is required", or something like that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Error compiling sepgsql in PG9.1
The attached patch enables to abort configure script when we run it with '--with-selinux' option, but libselinux is older than minimum requirement to SE-PostgreSQL. As the documentation said, it needs libselinux-2.0.93 at least, because this or later version support selabel_lookup(3) for database object classes; used to initial labeling. The current configure script checks existence of libselinux, but no version checks. (getpeercon_raw(3) has been a supported API for a long term.) The selinux_sepgsql_context_path(3) is a good watermark of libselinux-2.0.93 instead. Thanks, -- NEC Europe Ltd, SAP Global Competence Center KaiGai Kohei > -Original Message- > From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org] > Sent: 21. Mai 2011 07:46 > To: Kohei Kaigai > Cc: Emanuel Calvo; postgresql Forums; KaiGai Kohei > Subject: Re: [GENERAL] Error compiling sepgsql in PG9.1 > > On Sat, 2011-05-21 at 02:50 +0100, Kohei Kaigai wrote: > > As documentation said, it needs libselinux 2.0.93 or higher. > > This version supports selabel_lookup(3) for database object classes. > > AFAICS, we are not checking it during configure. It might be worth to add > libselinux version check > in the configure phase. > -- > Devrim GÜNDÜZ > Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com > PostgreSQL > Danışmanı/Consultant, Red Hat Certified Engineer > Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr > http://www.gunduz.org Twitter: > http://twitter.com/devrimgunduz sepgsql-fix-config-version.patch Description: sepgsql-fix-config-version.patch -- 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 CBT
On 05/24/2011 01:02 PM, fork wrote: A psql prompt and the excellent postgresql documentation? if you're concerned about mucking something up, download a postgresql livecd -- 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] Views permision -- please help and suggestion
On 05/24/2011 10:04 AM, salah jubeh wrote: Hello Adrian, I have changed the permission of one table where view B depend on and my problem is over. Still; the whole issue is confusing me. See here for explanation: http://www.postgresql.org/docs/9.0/interactive/sql-createview.html "Access to tables referenced in the view is determined by permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables. However, not all views are secure against tampering; see Section 37.4 for details. Functions called in the view are treated the same as if they had been called directly from the query using the view. Therefore the user of a view must have permissions to call all functions used by the view. " And in turn: http://www.postgresql.org/docs/9.0/interactive/rules-privileges.html Regards -- Adrian Klaver adrian.kla...@gmail.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] Views permision -- please help and suggestion
Hello Adrian, I have changed the permission of one table where view B depend on and my problem is over. Still; the whole issue is confusing me. Regards From: Adrian Klaver To: pgsql-general@postgresql.org Cc: salah jubeh Sent: Tue, May 24, 2011 4:13:38 PM Subject: Re: [GENERAL] Views permision -- please help and suggestion On Tuesday, May 24, 2011 6:50:38 am salah jubeh wrote: > I have two views A and B such that A depend on B. Both of them has the same > permissions. What are the permissions? > > > when I excute > > SELECT * FROM A; Who are you executing the above as? > > ERROR: permission denied for relation B > > ** Error ** > > ERROR: permission denied for relation B > SQL state: 42501 > > > However, I can do > > SELECT * FROM B; > > How can I trace this problem, It really confuses me. As was mentioned earlier the issue is in the relationship between A and B. As a user you may have permission to both A and B and can access either. The A view though may have permissions that do not allow it to access B. As was pointed out the ability of A to select from B is dependent on As permissions, not the the user initiating the select. > > Regards -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Postgresql CBT
yahoo.com> writes: > > Hi, > > Yes. Computer based training. A psql prompt and the excellent postgresql documentation? (Sorry to be flip, but typing in examples and fixing mistakes and tweaking input was how I learned... Hopefully one of the consulting guys on the list will come back with exactly what you want, but I wouldn't hold my breath. I would also search for postgresql on Youtube...) -- 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 CBT
Hi, Yes. Computer based training. Thanks Sent from my iPhone On 25/05/2011, at 0:38, Adrian Klaver wrote: > On Tuesday, May 24, 2011 7:35:02 am Markus Wanner wrote: >> On 05/24/2011 08:05 AM, sade...@yahoo.com wrote: >>> Id like to familiarize with postgresql and looking for a decent CBT but >>> not able to find it. Could someone help pls? >> >> And CBT is? (First hit on Google reads "Cognitive behavioral therapy", >> but I somehow doubt that's what you are interested in...) >> >> Regards >> >> Markus Wanner > > Computer Based Training is what I believe the OP is after. > > -- > Adrian Klaver > adrian.kla...@gmail.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] Postgresql CBT
On 05/24/2011 10:49 AM, Scott Marlowe wrote: Oh good. My first response from google, with safe search turned off, was much more distressing... in other news, google trends for cbt just jumped -- 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 CBT
On Tue, May 24, 2011 at 8:36 AM, Vick Khera wrote: > On Tue, May 24, 2011 at 10:33 AM, Scott Marlowe > wrote: >>> Id like to familiarize with postgresql and looking for a decent CBT but not >>> able to find it. Could someone help pls? >> >> CBT? Please define. >> > > > my guess is computer based training. Oh good. My first response from google, with safe search turned off, was much more distressing... :) -- 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 CBT
On Tuesday, May 24, 2011 7:35:02 am Markus Wanner wrote: > On 05/24/2011 08:05 AM, sade...@yahoo.com wrote: > > Id like to familiarize with postgresql and looking for a decent CBT but > > not able to find it. Could someone help pls? > > And CBT is? (First hit on Google reads "Cognitive behavioral therapy", > but I somehow doubt that's what you are interested in...) > > Regards > > Markus Wanner Computer Based Training is what I believe the OP is after. -- Adrian Klaver adrian.kla...@gmail.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] Error compiling sepgsql in PG9.1
2011/5/24 Kohei Kaigai : > I noticed that selinux_sepgsql_context_path(3) was also got merged at > libselinux-2.0.83. > So, we could check correctness of library versions using existence of this > function. > > Does this patch expectedly abort the configure script on older libselinux > installation? > I'm not available to setup Ubuntu environment immediately. > I tried to apply your patch, and reject some lines: ecalvo-laptop@dell-desktop:~/postgresql-9.1beta1$ cat configure.rej --- configure.in +++ configure.in @@ -960,7 +960,7 @@ # for contrib/sepgsql if test "$with_selinux" = yes; then - AC_CHECK_LIB(selinux, getpeercon_raw, [], + AC_CHECK_LIB(selinux, selinux_sepgsql_context_path, [], [AC_MSG_ERROR([library 'libselinux' is required for SELinux support])]) fi I'm not with CVS version, I'm using beta download. I need to update my CVS version. at least I will apply these lines manually to test now. -- -- Emanuel Calvo Helpame.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] Postgresql CBT
On Tue, May 24, 2011 at 10:33 AM, Scott Marlowe wrote: >> Id like to familiarize with postgresql and looking for a decent CBT but not >> able to find it. Could someone help pls? > > CBT? Please define. > my guess is computer based training. -- 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 CBT
On 05/24/2011 08:05 AM, sade...@yahoo.com wrote: > Id like to familiarize with postgresql and looking for a decent CBT but not > able to find it. Could someone help pls? And CBT is? (First hit on Google reads "Cognitive behavioral therapy", but I somehow doubt that's what you are interested in...) Regards Markus Wanner -- 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 CBT
On Tue, May 24, 2011 at 12:05 AM, wrote: > Hi, > > Id like to familiarize with postgresql and looking for a decent CBT but not > able to find it. Could someone help pls? CBT? Please define. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql CBT
Hi, Id like to familiarize with postgresql and looking for a decent CBT but not able to find it. Could someone help pls? Sent from my iPhone -- 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] Views permision -- please help and suggestion
On Tuesday, May 24, 2011 6:50:38 am salah jubeh wrote: > I have two views A and B such that A depend on B. Both of them has the same > permissions. What are the permissions? > > > when I excute > > SELECT * FROM A; Who are you executing the above as? > > ERROR: permission denied for relation B > > ** Error ** > > ERROR: permission denied for relation B > SQL state: 42501 > > > However, I can do > > SELECT * FROM B; > > How can I trace this problem, It really confuses me. As was mentioned earlier the issue is in the relationship between A and B. As a user you may have permission to both A and B and can access either. The A view though may have permissions that do not allow it to access B. As was pointed out the ability of A to select from B is dependent on As permissions, not the the user initiating the select. > > Regards -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL 9.01 error database help
tuanhoanganh writes: > I am running PostgreSQL 9.01 on windows 2008, RAID 10 with 4 disk. > Yesterday, one of 4 disks RAID 10 error and I copy data directory to > USB. > Today, When I start postgresql i have error log > 2011-05-24 17:20:01 ICT LOG: database system was shut down at > 2011-05-24 02:40:49 ICT > 2011-05-24 17:20:01 ICT LOG: unexpected pageaddr 1/A1F8E000 in log > file 1, segment 177, offset 16310272 > 2011-05-24 17:20:01 ICT LOG: invalid primary checkpoint record > 2011-05-24 17:20:01 ICT LOG: invalid secondary checkpoint record > 2011-05-24 17:20:01 ICT PANIC: could not locate a valid checkpoint record It looks like the contents of pg_control are out of sync with what's in pg_xlog/ ... are you sure you copied the *whole* data directory? If this is all you have to work with, you can probably use pg_resetxlog to wipe out the broken WAL data and then you'll be able to start the database, but expect to find some amount of data corruption if you go that way. A dump and reload would probably be wise at that point. 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] Views permision -- please help and suggestion
I have two views A and B such that A depend on B. Both of them has the same permissions. when I excute SELECT * FROM A; ERROR: permission denied for relation B ** Error ** ERROR: permission denied for relation B SQL state: 42501 However, I can do SELECT * FROM B; How can I trace this problem, It really confuses me. Regards
Re: [GENERAL] postgres crash - illegal instruction
2011/5/24 Jiří Pavlovský : > I'v run into the following today: > > 2893LOG: server process (PID 24519) was terminated by signal 4: Illegal > instruction > > Is this an error in postgres? > I'll tend to suspect hardware error, specifically memory corruption, or on-disk corruption of the binary. Do you use ECC and is it working correctly? It is extremely unlikely that gcc generated a binary with illegal instructions in it, especially for x86 systems. -- 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] Error compiling sepgsql in PG9.1
I noticed that selinux_sepgsql_context_path(3) was also got merged at libselinux-2.0.83. So, we could check correctness of library versions using existence of this function. Does this patch expectedly abort the configure script on older libselinux installation? I'm not available to setup Ubuntu environment immediately. Thanks, -- NEC Europe Ltd, SAP Global Competence Center KaiGai Kohei > -Original Message- > From: Kohei Kaigai [mailto:kohei.kai...@emea.nec.com] > Sent: 24. Mai 2011 12:44 > To: Emanuel Calvo; Devrim GÜNDÜZ > Cc: postgresql Forums; KaiGai Kohei > Subject: RE: [GENERAL] Error compiling sepgsql in PG9.1 > > > 2011/5/21 Devrim GÜNDÜZ : > > > On Sat, 2011-05-21 at 02:50 +0100, Kohei Kaigai wrote: > > >> As documentation said, it needs libselinux 2.0.93 or higher. > > >> This version supports selabel_lookup(3) for database object classes. > > > > > > AFAICS, we are not checking it during configure. It might be worth to > > > add libselinux version check in the configure phase. > > > -- > > > > So it could be added into the configure the check and I think > > a patch in the doc could complete this issue. That's rigth? > > > Correct. > > Now, configure script checks existence of libselinux using AC_CHECK_LIB(), > but getpeercon(3) has been supported for a long time, thus, an older version > of libselinux can also pass this test. > > What I want to check here is an existence of SELABEL_CTX_DB definition in > selinux/label.h header file; supported on 2.0.93 or later. > > Do you have any good idea to check existence of a particular definition in > a particular header file. > > -- in selinux/label.h > /* >* Available backends. >*/ > > /* file contexts */ > #define SELABEL_CTX_FILE0 > /* media contexts */ > #define SELABEL_CTX_MEDIA 1 > /* x contexts */ > #define SELABEL_CTX_X 2 > /* db objects */ > #define SELABEL_CTX_DB 3 <-- not exist libselinux older than > 2.0.93 > > Thanks, > -- > NEC Europe Ltd, SAP Global Competence Center > KaiGai Kohei > > > > -Original Message- > > From: Emanuel Calvo [mailto:postgres@gmail.com] > > Sent: 24. Mai 2011 12:30 > > To: Devrim GÜNDÜZ > > Cc: Kohei Kaigai; postgresql Forums; KaiGai Kohei > > Subject: Re: [GENERAL] Error compiling sepgsql in PG9.1 > > > > 2011/5/21 Devrim GÜNDÜZ : > > > On Sat, 2011-05-21 at 02:50 +0100, Kohei Kaigai wrote: > > >> As documentation said, it needs libselinux 2.0.93 or higher. > > >> This version supports selabel_lookup(3) for database object classes. > > > > > > AFAICS, we are not checking it during configure. It might be worth to > > > add libselinux version check in the configure phase. > > > -- > > > > So it could be added into the configure the check and I think > > a patch in the doc could complete this issue. That's rigth? > > > > > > > > -- > > -- > > Emanuel Calvo > > Helpame.com sepgsql-fix-config-version.patch Description: sepgsql-fix-config-version.patch -- 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] Error compiling sepgsql in PG9.1
> 2011/5/21 Devrim GÜNDÜZ : > > On Sat, 2011-05-21 at 02:50 +0100, Kohei Kaigai wrote: > >> As documentation said, it needs libselinux 2.0.93 or higher. > >> This version supports selabel_lookup(3) for database object classes. > > > > AFAICS, we are not checking it during configure. It might be worth to > > add libselinux version check in the configure phase. > > -- > > So it could be added into the configure the check and I think > a patch in the doc could complete this issue. That's rigth? > Correct. Now, configure script checks existence of libselinux using AC_CHECK_LIB(), but getpeercon(3) has been supported for a long time, thus, an older version of libselinux can also pass this test. What I want to check here is an existence of SELABEL_CTX_DB definition in selinux/label.h header file; supported on 2.0.93 or later. Do you have any good idea to check existence of a particular definition in a particular header file. -- in selinux/label.h /* * Available backends. */ /* file contexts */ #define SELABEL_CTX_FILE0 /* media contexts */ #define SELABEL_CTX_MEDIA 1 /* x contexts */ #define SELABEL_CTX_X 2 /* db objects */ #define SELABEL_CTX_DB 3 <-- not exist libselinux older than 2.0.93 Thanks, -- NEC Europe Ltd, SAP Global Competence Center KaiGai Kohei > -Original Message- > From: Emanuel Calvo [mailto:postgres@gmail.com] > Sent: 24. Mai 2011 12:30 > To: Devrim GÜNDÜZ > Cc: Kohei Kaigai; postgresql Forums; KaiGai Kohei > Subject: Re: [GENERAL] Error compiling sepgsql in PG9.1 > > 2011/5/21 Devrim GÜNDÜZ : > > On Sat, 2011-05-21 at 02:50 +0100, Kohei Kaigai wrote: > >> As documentation said, it needs libselinux 2.0.93 or higher. > >> This version supports selabel_lookup(3) for database object classes. > > > > AFAICS, we are not checking it during configure. It might be worth to > > add libselinux version check in the configure phase. > > -- > > So it could be added into the configure the check and I think > a patch in the doc could complete this issue. That's rigth? > > > > -- > -- > Emanuel Calvo > Helpame.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] Returning Rows in Procedure
Create function a(int)
Returns TABLE(col1 text)
As $$
...
$$
Language 'plpgsql'
On May 24, 2011, at 4:08, Adarsh Sharma wrote:
> Dear all,
>
> I need to return the rows of a table which was also created in that procedure.
>
> I know it is very easy when the table is existed before and we can specify
> like this to return
>
> create function a(integer) returns setof exist_table as $$
>
> But it gives error when the table is also created in the procedure like below
> :
>
> create function a(integer) returns setof record as $$
> declare
> a text;
> begin
> execute 'insert into a values('asdd');
> execute 'insert into a values('asdd');
> execute 'insert into a values('affsdd');
> execute 'insert into a values('ashjgdd');
> execute 'insert into a values('asfjfgddd');
>
> ---Now i want to return the rows of a
> DECLARE
> r a%ROWTYPE;
> BEGIN
> FOR r in SELECT * FROM a
> LOOP
> RETURN NEXT r;
> END LOOP;
> RETURN;
> END;
> END;
> $$ LANGUAGE 'plpgsql' ;
>
> ERROR: relation "user_news_tmp2" does not exist
> CONTEXT: compilation of PL/pgSQL function "create_user_report2" near line 22
>
> How to achieve this ?
>
> Thanks & best Regards,
> Adarsh
>
> --
> 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] Error compiling sepgsql in PG9.1
2011/5/21 Devrim GÜNDÜZ : > On Sat, 2011-05-21 at 02:50 +0100, Kohei Kaigai wrote: >> As documentation said, it needs libselinux 2.0.93 or higher. >> This version supports selabel_lookup(3) for database object classes. > > AFAICS, we are not checking it during configure. It might be worth to > add libselinux version check in the configure phase. > -- So it could be added into the configure the check and I think a patch in the doc could complete this issue. That's rigth? -- -- Emanuel Calvo Helpame.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] postgres crash - illegal instruction
On 24.5.2011 12:40, Pavel Stehule wrote: Hello Dne 24. května 2011 12:21 Jiří Pavlovský napsal(a): Hi, I'v run into the following today: 2893LOG: server process (PID 24519) was terminated by signal 4: Illegal instruction Is this an error in postgres? I'm running 8.4.2 - yes, I should update. what is operation system? what is output from SELECT version()? CetOS5 Linux p69.project-syndicate.org 2.6.18-194.11.4.el5PAE #1 SMP Tue Sep 21 05:48:23 EDT 2010 i686 i686 i386 GNU/Linux PostgreSQL 8.4.2 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44), 32-bit I's from the rpms on postgres.org Regards, Jiri -- 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] postgres crash - illegal instruction
Hello Dne 24. května 2011 12:21 Jiří Pavlovský napsal(a): > Hi, > > I'v run into the following today: > > 2893LOG: server process (PID 24519) was terminated by signal 4: Illegal > instruction > > Is this an error in postgres? > > I'm running 8.4.2 - yes, I should update. > what is operation system? what is output from SELECT version()? Regards Pavel Stehule > > > -- > 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
[GENERAL] postgres crash - illegal instruction
Hi, I'v run into the following today: 2893LOG: server process (PID 24519) was terminated by signal 4: Illegal instruction Is this an error in postgres? I'm running 8.4.2 - yes, I should update. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.01 error database help
I am running PostgreSQL 9.01 on windows 2008, RAID 10 with 4 disk. Yesterday, one of 4 disks RAID 10 error and I copy data directory to USB. Today, When I start postgresql i have error log 2011-05-24 17:20:01 ICT LOG: database system was shut down at 2011-05-24 02:40:49 ICT 2011-05-24 17:20:01 ICT LOG: unexpected pageaddr 1/A1F8E000 in log file 1, segment 177, offset 16310272 2011-05-24 17:20:01 ICT LOG: invalid primary checkpoint record 2011-05-24 17:20:01 ICT LOG: invalid secondary checkpoint record 2011-05-24 17:20:01 ICT PANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2011-05-24 17:20:01 ICT LOG: startup process (PID 6252) exited with exit code 3 2011-05-24 17:20:01 ICT LOG: aborting startup due to startup process failure Is there any way to help me? Thanks you very much Tuan Hoang Anh -- 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] Postgres questions
On 05/24/2011 05:15 PM, Trenta sis wrote: That I need is to connect to another databse (Cache Intersystems) to use select from Postgres. I have tried to configure dbilink but is not working with this database, with sql server seems to work but with poor peroformance. You really need to be specific about things like "not working" and "poor performance". Nobody can help you if that's all you say, you need to give error messages, versions, commands, configurations, etc. You haven't shown any queries, any EXPLAIN ANALYZE output, any schema, any timing data, or pretty much anything else that'd allow anyone to help you. It might be worth remedying that. See: In addition to that, some obvious things to test and report back on include: Can you connect to Cache from a stand-alone Perl script using Perl DBI? If so, can you connect to Cache from a PL/Perlu script using Perl DBI? Is MS-SQL performance OK when connecting from a stand-alone Perl script outside the database using Perl DBI? Is MS-SQL performance OK when connecting from a custom test plperlu script using Perl DBI? What do you expect performance to be like? Why? What kind of queries are you executing? Which ones perform badly? /- What kind of guarantees do I need about data loss windows at failover time? Can I afford to lose the last transactions / seconds worth of transactions? Or must absolutely every transaction be retained at all costs?/ A windows data loss could be some secodn/minutes but if is not very complex no data loss will be excellent. It sounds like your requirements can probably be satisfied by PostgreSQL 9.0's built-in replication combined with the use of repmgr and heartbeat. For anything like this, though, I STRONGLY suggest that you hire a consultant who has done what you need before and has learned the traps and pitfalls. See: http://www.postgresql.org/support/professional_support I don't do PostgreSQL support commercially and am not experienced with failover/HA setups, so I can't help you much there and suggest you find someone who _really_ knows what they are doing. Getting HA right is *hard*. -- 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] how to start a procedure after postgresql started.
2011/5/24 Craig Ringer : > On 24/05/11 12:46, jun yang wrote: > >> thanks for the info,i am just not have such deep learn of pg internal, >> i am on user level,not hacker,so the mail is in pgsql-general,not >> hacker list. > > Then you really, really, REALLY don't want to start a thread within the > backend, and should avoid spawning processes from backends too. To get > either approach right will require a much deeper understanding of how Pg > works. > thanks for your warnning again. >>> Part of the reason the postmaster hasn't been altered to support managing >>> daemons is because some people (understandably) think that that's the OS's >>> job, and not something PostgreSQL should duplicate. >>> >> well,from user viewpoint,i prefer that pg bundle with such >> function,like extension in pg,the function default is disable.make it >> easier for those who need it will be a promotion for pg. >> many commercial db production include such a schedule function, not >> only for making money,there is user need in practice. > > Yep, I think it'd be nice. Nobody has volunteered to write such a > feature yet, though, and nobody is stepping up to pay someone else to > write it. Or at least any efforts so far haven't reached > production-quality committable code. > even a basic pim program has buildin schedule function,so it should not be so hard for a big software like pg. the important of buildin schedule function is that the whole schedule is in pg ,you can dump and restore database,that's all,no need to examine all external things run ok. > The downside of working with an open source database is that there's no > incentive to write marketing-checkbox features. Someone has to actually > want to put in the time and effort to implement it, usually because they > want to use it. > haha,it seems i demand too much and can't put in the time and effort. i am terribly sorry about that. >> yes,it is so complicated for a common user to do such things. > > ... which is why the VAST majority of people achieve what they need > using a separate daemon or just integrate this sort of functionality > into their middleware. Neither option is difficult to do. > > What you want to do - integrate your app directly and completely into > the database - is not something that a common user typically wants to do > in the first place. > yes,it is like hacking pg. > It's more common for people who want to hide the database behind a > messaging system to instead write a program that accepts messages and > embed a database like Berkeley DB, SQLite or Firebird directly into > their program, rather than the other way around. PostgreSQL cannot be > embedded that way, it's not designed for that kind of use. > i found pg_amqp,some thing like what i want to do,though by write a pg module in c,if pg support write pg module by it's procedure language,there would be more people can hack pg. > -- > 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] Returning Rows in Procedure
On 24 May 2011, at 10:08, Adarsh Sharma wrote:
> Dear all,
>
> I need to return the rows of a table which was also created in that procedure.
>
> I know it is very easy when the table is existed before and we can specify
> like this to return
>
> create function a(integer) returns setof exist_table as $$
>
> But it gives error when the table is also created in the procedure like below
> :
>
> create function a(integer) returns setof record as $$
> declare
> a text;
> begin
> execute 'insert into a values('asdd');
> execute 'insert into a values('asdd');
> execute 'insert into a values('affsdd');
> execute 'insert into a values('ashjgdd');
> execute 'insert into a values('asfjfgddd');
You're skating on thin ice here, you have a function named "a", a table named
"a" and a variable named "a" (that doesn't get used BTW) - are you sure they're
not mixed up anywhere?
I also don't quite see the need to use dynamic SQL here for insertions into the
"a" table.
Is this your actual function? I don't think it is.
>
> ---Now i want to return the rows of a
> DECLARE
> r a%ROWTYPE;
> BEGIN
> FOR r in SELECT * FROM a
> LOOP
> RETURN NEXT r;
> END LOOP;
> RETURN;
> END;
I'm not sure the above would work with the dynamic SQL from before. I'd try
using either all static SQL in that function or all dynamic SQL and see if that
makes a difference with respect to the error you're seeing. If you can do this
in all static SQL it'll probably perform better.
> END;
> $$ LANGUAGE 'plpgsql' ;
>
> ERROR: relation "user_news_tmp2" does not exist
> CONTEXT: compilation of PL/pgSQL function "create_user_report2" near line 22
Well, according to the code you provided your table is named "a", and not
"user_news_tmp2". There's obviously something different between this code and
your actual code, and it seems a relevant difference too. Perhaps you could
give us a better example, or show us the actual code even?
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4ddb79f211928090216264!
--
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] Postgres questions
Hi Craig, First of all, thanks for you answer. That I need is to connect to another databse (Cache Intersystems) to use select from Postgres. I have tried to configure dbilink but is not working with this database, with sql server seems to work but with poor peroformance. About second question I'll try to answer your questions: *- Do I need true HA with failover, or just to protect against data loss?* It's a critical environment and if it is possible we need failover... *- Can I modify my apps to be aware of failover, or does failover have to be transparent?* Failover must be transparent for applications. *- Do I need multi-site failover or is all access of interest within one site?* With 2 site It could be a valid option, if one is master (RW), and secondary could be (RO) to use as datawarehouse (allow only selects, except in failover scenario: allow all) *- What kind of guarantees do I need about data loss windows at failover time? Can I afford to lose the last transactions / seconds worth of transactions? Or must absolutely every transaction be retained at all costs?* A windows data loss could be some secodn/minutes but if is not very complex no data loss will be excellent. I have scheduled to create a second server but I'm not sure what options to choose to implement with our requirements... Thanks 2011/5/24 Craig Ringer > On 24/05/2011 6:10 AM, Trenta sis wrote: > > - I need to connect from postgres to other database (linked server) no >> postgres (for example with jdbc or odbc). I have tried to work with >> dbi-link with sql, it seems to work but with poor performance and whit >> other database different mssql is not working. What possible options >> exsits with 8.3? and with other versions? >> > > DBI-link is probably your best bet. Another possibility is to use an > in-database procedural language to talk to the other database - for example, > PL/perl via DBI::DBD or PL/Python via a PEP-249 ( > http://www.python.org/dev/peps/pep-0249/) database driver like pymssql. > > Otherwise you can do the data sharing/sync/whatever via a client > application that has connections to PostgreSQL and to the other database of > interest. That's often a better choice for more complex jobs. > > Perhaps it'd help if you explained why you need this and what you want to > accomplish with it? > > > - This server has some critical applications and I need high >> availability, but I'm not sure about possible options for this versions >> or similar. I have thought about active/active, active/passive or >> active/read-only but I'm not sure what are real options, and what could >> be a possible environment for this situation >> > > It depends a LOT on what your needs are, and what your budget is. You have > some basic questions to ask yourself, like: > > - Do I need true HA with failover, or just to protect against data loss? > > - Can I modify my apps to be aware of failover, or does failover have to > be transparent? > > - Do I need multi-site failover or is all access of interest within > one site? > > - What kind of guarantees do I need about data loss windows at failover > time? Can I afford to lose the last transactions / seconds worth > of transactions? Or must absolutely every transaction be retained > at all costs? > > Once you've worked out the answers to those kinds of questions, THEN you > can look at bucardo, slony-I, PostgreSQL 9.0 native replication, etc etc as > well as failover-control options like heartbeat and decide what might be > suitable for you. > > -- > Craig Ringer > > Tech-related writing at http://soapyfrogs.blogspot.com/ >
Re: [GENERAL] disable seqscan
On 24 May 2011, at 8:22, Nick Raj wrote: > One think i am not able to understand is, if i use ndpoint_overlap method it > is going for seq. scan every time but if i use && operator it is using index > scan. Why it is so? > Why these is happening? Tom already explained that, but in short: Because a function is not an operator. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ddb741511921606159980! -- 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] strange behaviour in 9.0.2 / ERROR: 22003: value out of range: overflow
On 23 May 2011, at 22:53, rudi wrote:
> On 20 Mai, 22:55, rudi wrote:
>> Hi all,
>>
>> I feel like I hit a bug in postgres 9.0.2 with a query like this
>> (there's actually a quite complicated view hidden behind), however
>> note
>> the 'IN' selection contains two identical keys. When I execute a
>> similar query without the duplicate, the query returns, so I would
>> conclude it shoud be a bug.
>>
>> This query fails as you can tell from the output:
>>
>> mydb=# select * from cpcpk_by_lot where foundry='x' and lot='valerie'
>> and epclass='wac' and area='device' and parameter in
>> ('RVT_2P_NOM_1UX5_N_VTSAT','RVT_2P_NOM_1UX5_N_VTSAT');
>> ERROR: 22003: value out of range: overflow
>> LOCATION: float4mul, float.c:750
>>
>> while the a practically identical query returns with the expected
>> result?
What's in that view? Aggregations and something like a UNION ALL maybe? I'm
guessing the difference between this query and the below one is in the result
of some aggregation that happens twice if the value occurs in the IN-list twice.
Apparently you're using a numeric field somewhere for which 22003 is too large
a value. Finding out why it becomes too large is what you need to do.
If my guess is correct, you probably have other (smaller) values that got the
same issue, but that didn't overflow the column data-type. It's only a select
statement, but you'd still be returning wrong information.
>> mydb=# select * from cpcpk_by_lot where foundry='x' and lot='valerie'
>> and epclass='wac' and area='device' and parameter in
>> ('RVT_2P_NOM_1UX5_N_VTSAT');
> I wonder whether the behaviour can
> be optimized, such that the individual value can be set to 'nan'
> instead of causing a fail for the entire query.
That would be a really bad idea. It is a number, so saying it's not is just
wrong. An overflowing number still is a number, and throwing an error when it
overflows is the correct thing to do. There's something wrong in your queries
and it needs fixing.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4ddb717d11921570220170!
--
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] Trapping errors
On 23 May 2011, at 22:08, Shane W wrote: > Hello list, > > I have a table with double precision columns and update > queries which multiply and divide these values. I am > wondering if it's possible to catch overflow and underflow > errors to set the column to 0 in the case of an underflow > and a large value in the case of an overflow. > > Currently, I have an exception handler in a PLPGSQL > ufunction that sort of does this. > > begin > update tbl set score = score/s > exception when numeric_value_out_of range then > update tbl set score=0 > where cast(score/s as numeric) < 1e-200 > end; > > But this is messy since the exception needs to rescan the > entire table if even one row fails the update. Is there a > better way to do this? If you move the overflow/underflow check into a before-trigger, then you can use the NEW.* and OLD.* variables to alter the row before it gets written. That way you scan the table only once and you also moved your handling of such errors into the database (which means that if other applications than your client ever write values to that table, the same rules are applied). Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4ddb6e8111921119526771! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dumping schemas using pg_dump without extensions (9.1 Beta)
I am trying to backup a single schema only, without any other database objects such as extensions. pg_dump however always includes extensions, even with the --schema=schema option specified (see below for example). Is there a workaround for this? Cheers, Adrian -- -- TOC entry 20 (class 3079 OID 11673) -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- TOC entry 2978 (class 0 OID 0) -- Dependencies: 20 -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: - -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -- -- TOC entry 19 (class 3079 OID 35645) -- Name: adminpack; Type: EXTENSION; Schema: -; Owner: - -- CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog; -- 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] Returning Rows in Procedure
Hello
2011/5/24 Adarsh Sharma :
> Pavel Stehule wrote:
>>
>> Hello
>>
>> you have to use a dynamic sql
>>
>> look on statement
>>
>> FOR r IN EXECUTE
>> or RETURN QUERY EXECUTE
>>
>
> Can u explain in the example, I find it difficult to understand .
>
> I think we have to specify return type while creating procedures.
>
a) is not good idea to write too general functions
b) when function returns setof record, you have to describe return type in query
create or replace function foo(c int)
returns setof record as $$
begin
return query execute 'SELECT ' || repeat(' i,', c - 1) || 'i FROM
generate_series(1,3) g(i)';
end
$$ language plpgsql;
postgres=# select * from foo(2) x(a int,b int);
a │ b
───┼───
1 │ 1
2 │ 2
3 │ 3
(3 rows)
postgres=# select * from foo(3) x(a int,b int, c int);
a │ b │ c
───┼───┼───
1 │ 1 │ 1
2 │ 2 │ 2
3 │ 3 │ 3
(3 rows)
Regards
Pavel Stehule
> Thanks
>>
>> Regards
>>
>> Pavel Stehule
>>
>> 2011/5/24 Adarsh Sharma :
>>
>>>
>>> Dear all,
>>>
>>> I need to return the rows of a table which was also created in that
>>> procedure.
>>>
>>> I know it is very easy when the table is existed before and we can
>>> specify
>>> like this to return
>>>
>>> create function a(integer) returns setof exist_table as $$
>>>
>>> But it gives error when the table is also created in the procedure like
>>> below :
>>>
>>> create function a(integer) returns setof record as $$
>>> declare
>>> a text;
>>> begin
>>> execute 'insert into a values('asdd');
>>> execute 'insert into a values('asdd');
>>> execute 'insert into a values('affsdd');
>>> execute 'insert into a values('ashjgdd');
>>> execute 'insert into a values('asfjfgddd');
>>>
>>> ---Now i want to return the rows of a
>>> DECLARE
>>> r a%ROWTYPE;
>>> BEGIN
>>> FOR r in SELECT * FROM a
>>> LOOP
>>> RETURN NEXT r;
>>> END LOOP;
>>> RETURN;
>>> END;
>>> END;
>>> $$ LANGUAGE 'plpgsql' ;
>>>
>>> ERROR: relation "user_news_tmp2" does not exist
>>> CONTEXT: compilation of PL/pgSQL function "create_user_report2" near
>>> line
>>> 22
>>>
>>> How to achieve this ?
>>>
>>> Thanks & best Regards,
>>> Adarsh
>>>
>>> --
>>> 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] Returning Rows in Procedure
Pavel Stehule wrote:
Hello
you have to use a dynamic sql
look on statement
FOR r IN EXECUTE
or RETURN QUERY EXECUTE
Can u explain in the example, I find it difficult to understand .
I think we have to specify return type while creating procedures.
Thanks
Regards
Pavel Stehule
2011/5/24 Adarsh Sharma :
Dear all,
I need to return the rows of a table which was also created in that
procedure.
I know it is very easy when the table is existed before and we can specify
like this to return
create function a(integer) returns setof exist_table as $$
But it gives error when the table is also created in the procedure like
below :
create function a(integer) returns setof record as $$
declare
a text;
begin
execute 'insert into a values('asdd');
execute 'insert into a values('asdd');
execute 'insert into a values('affsdd');
execute 'insert into a values('ashjgdd');
execute 'insert into a values('asfjfgddd');
---Now i want to return the rows of a
DECLARE
r a%ROWTYPE;
BEGIN
FOR r in SELECT * FROM a
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
END;
$$ LANGUAGE 'plpgsql' ;
ERROR: relation "user_news_tmp2" does not exist
CONTEXT: compilation of PL/pgSQL function "create_user_report2" near line
22
How to achieve this ?
Thanks & best Regards,
Adarsh
--
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] Returning Rows in Procedure
Hello
you have to use a dynamic sql
look on statement
FOR r IN EXECUTE
or RETURN QUERY EXECUTE
Regards
Pavel Stehule
2011/5/24 Adarsh Sharma :
> Dear all,
>
> I need to return the rows of a table which was also created in that
> procedure.
>
> I know it is very easy when the table is existed before and we can specify
> like this to return
>
> create function a(integer) returns setof exist_table as $$
>
> But it gives error when the table is also created in the procedure like
> below :
>
> create function a(integer) returns setof record as $$
> declare
> a text;
> begin
> execute 'insert into a values('asdd');
> execute 'insert into a values('asdd');
> execute 'insert into a values('affsdd');
> execute 'insert into a values('ashjgdd');
> execute 'insert into a values('asfjfgddd');
>
> ---Now i want to return the rows of a
> DECLARE
> r a%ROWTYPE;
> BEGIN
> FOR r in SELECT * FROM a
> LOOP
> RETURN NEXT r;
> END LOOP;
> RETURN;
> END;
> END;
> $$ LANGUAGE 'plpgsql' ;
>
> ERROR: relation "user_news_tmp2" does not exist
> CONTEXT: compilation of PL/pgSQL function "create_user_report2" near line
> 22
>
> How to achieve this ?
>
> Thanks & best Regards,
> Adarsh
>
> --
> 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
[GENERAL] Returning Rows in Procedure
Dear all,
I need to return the rows of a table which was also created in that
procedure.
I know it is very easy when the table is existed before and we can
specify like this to return
create function a(integer) returns setof exist_table as $$
But it gives error when the table is also created in the procedure like
below :
create function a(integer) returns setof record as $$
declare
a text;
begin
execute 'insert into a values('asdd');
execute 'insert into a values('asdd');
execute 'insert into a values('affsdd');
execute 'insert into a values('ashjgdd');
execute 'insert into a values('asfjfgddd');
---Now i want to return the rows of a
DECLARE
r a%ROWTYPE;
BEGIN
FOR r in SELECT * FROM a
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
END;
$$ LANGUAGE 'plpgsql' ;
ERROR: relation "user_news_tmp2" does not exist
CONTEXT: compilation of PL/pgSQL function "create_user_report2" near
line 22
How to achieve this ?
Thanks & best Regards,
Adarsh
--
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 start a procedure after postgresql started.
2011/5/24 Rick Genter : > > On May 23, 2011, at 9:46 PM, jun yang wrote: > >> thanks for the info,i am just not have such deep learn of pg internal, >> i am on user level,not hacker,so the mail is in pgsql-general,not >> hacker list. > > What you are asking to do is not a typical user function. It would be more > appropriate for a "hacker list". i am just thinking pg as a special os,it should can be scripted when some event happen. so much like a program has scripting function. > -- > Rick Genter > rick.gen...@gmail.com > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
