[GENERAL] how to set CACHEDEBUG ?
I noticed there is a piece of code:
#ifdef CACHEDEBUG
#define InitCatCache_DEBUG2 \
do { \
elog(...
} while(0)
#else
#define InitCatCache_DEBUG2
#endif
Now I'd like to set CACHEDEBUG, how to set up it ? where ?
thanks
peng
Re: [GENERAL] Need help to identify stray row in a table
2010/4/23 Merlin Moncure : > > There's way too much logic going on there for me to test all the > different cases. > > I suspect this is your problem: you triggered a case somehow which is > not handled properly via your labyrinth of switches and loops. I > highly doubt this is a case of database corruption. My advice here > would be to not rely on procedural code to guard against something > which can and should be enforced by a constraint. If something is > wrong (source_id being null), declare it to be wrong -- that way the > next time this happens the constraint will bounce the transaction and > you can catch the problem when it happens as opposed to reverse > engineering it. > Thanks for the suggestion and help. Will work on changing the function and constraints for enforcing data accuracy. Thanks and regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.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] how to invalidate a stored procedure's plan?
On Apr 23, 2010, at 6:00 PM, Tom Lane wrote: > Ben Chobot writes: >> I have a procedure that queries a table. This should be fast because of an >> index, but some index bloat has caused the index to become expensive, and so >> the procedure has cached a plan that uses a full table scan. I've since >> fixed the index bloat, but the procedure still seems to be doing full table >> scans. Is there a non-disruptive way to invalidate the cached plan for that >> procedure globally? > > What PG version? In 8.3 and up an ANALYZE on any of the tables > mentioned in the problem query should suffice. > > regards, tom lane Heh, woops, sorry, it's 8.4, and yes, a simple table analyze was all I needed. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to invalidate a stored procedure's plan?
Ben Chobot writes: > I have a procedure that queries a table. This should be fast because of an > index, but some index bloat has caused the index to become expensive, and so > the procedure has cached a plan that uses a full table scan. I've since fixed > the index bloat, but the procedure still seems to be doing full table scans. > Is there a non-disruptive way to invalidate the cached plan for that > procedure globally? What PG version? In 8.3 and up an ANALYZE on any of the tables mentioned in the problem query should suffice. 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] how to invalidate a stored procedure's plan?
I have a procedure that queries a table. This should be fast because of an index, but some index bloat has caused the index to become expensive, and so the procedure has cached a plan that uses a full table scan. I've since fixed the index bloat, but the procedure still seems to be doing full table scans. Is there a non-disruptive way to invalidate the cached plan for that procedure globally? -- 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 stats collector showing high disk I/O
- Original Message - From: Alvaro Herrera Date: Fri, 23 Apr 2010 18:28:03 -0400 Subject: Re: [GENERAL] Postgres stats collector showing high disk I/O To: Justin Pasher CC: dep...@depesz.com, pgsql-general@postgresql.org Justin Pasher wrote: Agh... I used pg_stats_reset (with an s) when searching for it. I ran the function and it returned true, but the stats file only shrunk by ~100k (still over 18MB total). Is there something else I need to do? Does this mean the file is mostly bloated with bogus data that it can't "reset"? I'm guessing I should just try to delete the file outright? Err, yeah, pg_stat_reset only resets the stats for the current database. You need to reset for all databases, or alternatively, shut down the server, remove the file, and restart Ahh, yes. I probably should have realized that. I ran the function on all of the databases (138 total), and now the stats file is down to ~400k. The disk I/O is also practically nothing now. So now as a continuation of my original message, what would cause the stats file to get so big for what seems like (IMO) a small number of databases? I have a Postgres 7.4 cluster that has about 250 databases, but it's stats file is only 3.5MB. Do I need to look into avoiding a bunch of CREATE/DROP statements (a suggestion in the archives)? I don't know the actual usage patterns of the code base (I'll have to get with the developers), but I think at worst, they might create temp tables from time to time. As a matter of fact, I just checked the stats file again (about 10 minutes later) and it's doubled to 800K. Is Postgres just trying to store too much information in the statistics file? Ultimately, the main statistics I care about are current connections and queries being run. A previous post in the archives from Tom said that vacuum (even autovacuum) should clean up potential stat file bloat. Do I need to tweak my autovacuum settings? Everything is currently set to the defaults because I've never had any performance issues that warranted tweaking the settings. -- Justin Pasher -- 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 stats collector showing high disk I/O
Justin Pasher writes: > Agh... I used pg_stats_reset (with an s) when searching for it. I ran > the function and it returned true, but the stats file only shrunk by > ~100k (still over 18MB total). Is there something else I need to do? pg_stat_reset only resets the data for the current database (the one you issue it in). Apparently most of your bloat is for some other database(s). If you've got a whole lot of databases, a possibly less painful alternative to zapping them one at a time is to stop the server, manually remove the stats file, start the server. 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] Postgres stats collector showing high disk I/O
Justin Pasher wrote: > Agh... I used pg_stats_reset (with an s) when searching for it. I > ran the function and it returned true, but the stats file only > shrunk by ~100k (still over 18MB total). Is there something else I > need to do? Does this mean the file is mostly bloated with bogus > data that it can't "reset"? I'm guessing I should just try to delete > the file outright? Err, yeah, pg_stat_reset only resets the stats for the current database. You need to reset for all databases, or alternatively, shut down the server, remove the file, and restart -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres stats collector showing high disk I/O
> I'm guessing I should just try to delete the file outright? > Err... I meant "should NOT" delete. -- Justin Pasher -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with pg_prepare
Hi all. I'm receiving the following message when I try to use pg_prepare() function: "Call to undefined function pg_prepare()". My application works very well with others pg_* commands... I already checked my configuration files and I have no more ideas about how to fix it. Any suggestions? Thank you. -- 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 stats collector showing high disk I/O
- Original Message - From: hubert depesz lubaczewski Date: Fri, 23 Apr 2010 23:40:35 +0200 Subject: Re: [GENERAL] Postgres stats collector showing high disk I/O To: Justin Pasher CC: pgsql-general@postgresql.org On Fri, Apr 23, 2010 at 03:27:55PM -0500, Justin Pasher wrote: haven't tweaked any settings from the defaults. My $PGDATA/global/pgstat.stat file is about 18MB, if that helps. Does it really rewrite this entire file every 500ms? Alvaro suggested resetting the stats, but I'm having trouble figuring out how to do that. Seems like pg_stat_reset() is post- 8.1 ...? I don't have 8.1 handy, but according to this: http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html pg_stat_reset() should be available in 8.1. Best regards, depesz Agh... I used pg_stats_reset (with an s) when searching for it. I ran the function and it returned true, but the stats file only shrunk by ~100k (still over 18MB total). Is there something else I need to do? Does this mean the file is mostly bloated with bogus data that it can't "reset"? I'm guessing I should just try to delete the file outright? -- Justin Pasher -- 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 stats collector showing high disk I/O
On Fri, Apr 23, 2010 at 03:27:55PM -0500, Justin Pasher wrote: > haven't tweaked any settings from the defaults. My > $PGDATA/global/pgstat.stat file is about 18MB, if that helps. Does > it really rewrite this entire file every 500ms? Alvaro suggested > resetting the stats, but I'm having trouble figuring out how to do > that. Seems like pg_stat_reset() is post- 8.1 ...? I don't have 8.1 handy, but according to this: http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html pg_stat_reset() should be available in 8.1. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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 stats collector showing high disk I/O
Hello, Redhat EL4 update 8, 2.6.9-89.0.23.ELsmp Quad Proc, Dual Core Xeon, 16GB RAM Postgres 8.1.18 I'm having some trouble pinning down exactly what is causing our Postgres cluster to run slowly. After some initial investigation, I noticed that the disk write activity is consistently high, and (if I'm reading the output of dstat correctly) the majority of it is being caused by the stats collector process. Here's a snippet of what I typically see in dstat. ./dstat -cd --top-bio total-cpu-usage -dsk/total- most-expensive- usr sys idl wai hiq siq| read writ| block i/o process 2 2 87 10 0 0|3964k 19M|postgres: stats coll 035M 2 1 85 12 0 0|4612k 20M|postgres: stats coll 018M 2 2 85 11 0 0|2360k 36M|postgres: stats coll 024M 1 2 83 14 0 0|1564k 36M|postgres: stats coll 029M 1 1 84 13 0 0|5556k 21M|postgres: stats coll 020M 2 2 82 14 0 0| 10M 19M|postgres: stats coll 033M 2 1 87 10 0 0|9864k 35M|postgres: stats coll 024M 2 2 87 10 0 0| 10M 19M|postgres: stats coll 029M 2 1 86 11 0 0| 10M 19M|postgres: stats coll 024M 3 2 84 12 0 0|8096k 19M|postgres: stats coll 029M 2 1 86 10 0 0|5432k 33M|postgres: stats coll 032M 2 2 86 10 0 0|9200k 19M|postgres: stats coll 021M 2 1 82 14 0 0|3344k 34M|postgres: stats coll 021M 2 2 86 11 0 0|8600k 19M|postgres: stats coll 031M 2 1 82 15 0 0|5392k 19M|postgres: stats coll 029M If there are no queries going on, then the disk usage is virtually nothing, but it only takes a query or two to make it shoot up to this level. I have the following stats related options enabled in postgresql.conf stats_command_string = on stats_row_level = on When I disabled stats_row_level (and even stats_command_string, I believe) and restarted, I was still seeing some high disk I/O. If I disable stats_start_collector, I'm pretty sure the I/O dropped completely off (I can't verify right now since I'd need a maintenance window). However, this make Postgres unable to keep track of database connections/queries in pg_stat_activity, which is very important for us. The odd thing is that when I was playing around with these options, I restarted multiple times to apply them, eventually ending back where I started, but after the final restart, the disk I/O actually dropped to reasonable levels. This lasted for about a day, then went back up to it's current levels (and once again showing the stats collector at the top). I saw some previous posts with similar conditions (but different Postgres version, high CPU load, not disk I/O, etc). http://archives.postgresql.org/pgsql-performance/2010-04/msg00163.php http://archives.postgresql.org/pgsql-general/2010-01/msg01076.php http://archives.postgresql.org/pgsql-performance/2009-06/msg00088.php I don't think there are a lot of CREATE/DROP table statements, but I do know there are some larger update queries that run inside transactions (large in the sense of data they have to read, not the number of queries). Autovacuum is enabled on the server, and I haven't tweaked any settings from the defaults. My $PGDATA/global/pgstat.stat file is about 18MB, if that helps. Does it really rewrite this entire file every 500ms? Alvaro suggested resetting the stats, but I'm having trouble figuring out how to do that. Seems like pg_stat_reset() is post- 8.1 ...? I have a strong suspicion it's ultimately due to some usage pattern of the database, but I'm not sure what it could be. What type of operations would typically cause the stats collector to be doing this much writing to the filesystem? Is there any way to "see" what it's writing? Are there other config options that can/should be tweaked to help this? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help to identify stray row in a table
2010/4/23 சிவகுமார் மா : > 2010/4/23 Merlin Moncure : >> >> You haven't given enough information to make any sort of reasonable >> diagnosis. Most people are going to assume the problem is on your end >> but it's possible to know for sure without having the trigger function >> at the very least. >> > > Thanks merlin for the reply. There are two functions, > > 1. for inserts on stock transaction table, calculating value and > inserting in transaction_value table. > > 2. the other is on transaction_value table itself, to update values of > child transactions of row being inserted/updated/deleted. > > The second function is more than 200 lines. I have attached a text > file containing trigger and function code. > > Thanks for any insights you can provide. There's way too much logic going on there for me to test all the different cases. I suspect this is your problem: you triggered a case somehow which is not handled properly via your labyrinth of switches and loops. I highly doubt this is a case of database corruption. My advice here would be to not rely on procedural code to guard against something which can and should be enforced by a constraint. If something is wrong (source_id being null), declare it to be wrong -- that way the next time this happens the constraint will bounce the transaction and you can catch the problem when it happens as opposed to reverse engineering it. 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] installation on vista
On Fri, Apr 23, 2010 at 4:03 PM, Watson, Nathaniel wrote: > > It appears that no log is being created in %TEMP% that as a result of this > problem. Very odd. Does anything get created in %TEMP%? That early in the installation it's probably unpacking some of the files it'll need for the pre-flight checks, such as the VC++ runtimes. They should be fairly easy to spot - a directory called postgresql_installer for example. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Invalid objects
Using views in Postgres can be a painful process. Changing a column in a
base table will require you to drop all views that depend on it, and all
views that depend on those views and so on.
My coworker was complaining this morning that he now has a bunch of
queries where a view is joined back on the original table to add a
column that was missing from the view. It was easier to do this than to
drop the view and all of it's dependencies and then find all the source
code and rebuild all of the views in the correct order.
So my thought was to create an invalid objects table to store the source
and dependencies (and possibly permissions) when a DDL change
invalidates a view or a function. And later you can call a procedure
that (tries to) rebuild those invalid objects.
My initial plan of attack is to just create a function that stores the
information required to rebuild the dependencies before dropping them.
Something like:
store_and_drop('my_view_name')
I'm thinking that ultimately it would be nice if postgres could do this
automatically. Maybe:
DROP my_view_name CASCADE WITH RESTORE
So before I begin, has anyone already done this? And does anyone have
any advice as to how it may best be done?
Thanks
Scott Bailey
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Live CD based on CentOS 5.4 and PG 8.4.3 released
I released new version of my PostgreSQL 8.4 live CD, which is based on CentOS 5.4. It includes the PostgreSQL related packages that I build on http://yum.pgrpms.org, along with PostgreSQL 8.4.3. Details are here: http://pglivecd.org http://yum.pgrpms.org/livecd.php You can add an encrypted home directory while burning iso to USB stick, which helps you to keep your personal data in your USB stick. This live CD has current versions of many software, like pgAdmin III, phpPgAdmin, Apache, PHP, GNOME, Pidgin, Firefox etc. Kickstart file is configurable, so you can also create your own PostgreSQL Live CD's fairly easily, if you have a CentOS 5.4 machine around, as described in here: https://projects.centos.org/trac/livecd/ Please let me know if you have any questions regarding this live CD. Sincerely, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org 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] installation on vista
-Original Message- From: Dave Page [mailto:dp...@pgadmin.org] Sent: Thu 4/22/2010 3:35 AM To: Craig Ringer Cc: Watson, Nathaniel; pgsql-general@postgresql.org Subject: Re: [GENERAL] installation on vista On Thu, Apr 22, 2010 at 1:47 AM, Craig Ringer wrote: > On 22/04/2010 1:05 AM, Watson, Nathaniel wrote: >> >> I have downloaded the one click installer on Vista. > > From where? What version? > >> I right click on >> the executable and select run as administrator, and an empty dialog box >> appears. This is as far as I can go. Any suggestions? > > Does this dialog appear *after* the UAC run-as-admin prompt? Or before? Also, is a log created in %TEMP%? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company > I downloaded postgresql-8.4.3-1-windows.exe from > http://www.enterprisedb.com/products/pgdownload.do#windows > on an HP Pavilion dv5 Notebook PC. > This occurs After I select "continue" in the UAC prompt. It appears that no log is being created in %TEMP% that as a result of this problem.
Re: [GENERAL] Need help to identify stray row in a table
2010/4/23 Merlin Moncure : > > You haven't given enough information to make any sort of reasonable > diagnosis. Most people are going to assume the problem is on your end > but it's possible to know for sure without having the trigger function > at the very least. > Thanks merlin for the reply. There are two functions, 1. for inserts on stock transaction table, calculating value and inserting in transaction_value table. 2. the other is on transaction_value table itself, to update values of child transactions of row being inserted/updated/deleted. The second function is more than 200 lines. I have attached a text file containing trigger and function code. Thanks for any insights you can provide. Best regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com > merlin > transaction-value-trigger-functions.sql Description: Binary data -- 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] Upgrading 8.2.4 to 8.3 With TSearch2
Howard Cole writes: > I have a database on version 8.2.4 and intend to upgrade to the latest > 8.4.3 version. The 8.2 version has Tsearch2 and I know there are issues > in upgrading tsearch2 and therefore I was wondering which is the best > way to do this upgrade. There's some suggestions in the fine manual ... http://www.postgresql.org/docs/8.4/static/textsearch-migration.html 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] EXCEPT doesn't compare TIMESTAMP type?
Thanks again Alban. I didn't mention but if I remove the braces then it gives below error but the meaning is same as the error when I specify braces. techdb=# SELECT insert_history_info(); ERROR: error from Perl function "insert_history_info": each EXCEPT query must have the same number of columns at line 15. Thanks, Dipti On Fri, Apr 23, 2010 at 6:59 PM, Alban Hertroys < dal...@solfertje.student.utwente.nl> wrote: > On 23 Apr 2010, at 14:28, dipti shah wrote: > > > Great! Thanks Alban, Alexander, and Thomas. > > > > That solved the issue but could you tell me what is the issue when I give > brackets in second query? > > > > techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, > now()) > > except select (id, txid, txtime) > > from changelogtest > > where id=5; > > ERROR: each EXCEPT query must have the same number of columns > > LINE 2: except select (id, txid, txtime) > > > I need brackets because this query actually I am using from trigger like > below and it gives the same error: Could you please help me with it. > > > > CREATE OR REPLACE FUNCTION insert_history_info() > > RETURNS VOID AS > > $BODY$ > > my $query = (< > INSERT INTO changelogtest(id, txid, txtime) > > SELECT (\$1, \$2, \$3) > > EXCEPT > > SELECT (id, txid, txtime) > > FROM changelogtest > > WHERE id = \$1 > > AND txid = \$2 > > AND txtime = \$3; > > ENDQUERY > > You need to remove the braces from the query in your trigger too, they > change the meaning of the query. You use brackets in this way if you need to > reference fields from a composite type. > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:1050,4bd1a0ba10411000594626! > > >
[GENERAL] Upgrading 8.2.4 to 8.3 With TSearch2
Hi, I have a database on version 8.2.4 and intend to upgrade to the latest 8.4.3 version. The 8.2 version has Tsearch2 and I know there are issues in upgrading tsearch2 and therefore I was wondering which is the best way to do this upgrade. I am best upgrading from 8.2.4 to the latest 8.2 build and then dumping that database to restore in an 8.4.3 database, or just dump the existing 8.2.4 database and restore in 8.4.3? Thanks. Howard Cole www.selestial.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] EXCEPT doesn't compare TIMESTAMP type?
On 23 Apr 2010, at 14:28, dipti shah wrote: > Great! Thanks Alban, Alexander, and Thomas. > > That solved the issue but could you tell me what is the issue when I give > brackets in second query? > > techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now()) > except select (id, txid, txtime) > from changelogtest > where id=5; > ERROR: each EXCEPT query must have the same number of columns > LINE 2: except select (id, txid, txtime) > I need brackets because this query actually I am using from trigger like > below and it gives the same error: Could you please help me with it. > > CREATE OR REPLACE FUNCTION insert_history_info() > RETURNS VOID AS > $BODY$ > my $query = (< INSERT INTO changelogtest(id, txid, txtime) > SELECT (\$1, \$2, \$3) > EXCEPT > SELECT (id, txid, txtime) > FROM changelogtest > WHERE id = \$1 > AND txid = \$2 > AND txtime = \$3; > ENDQUERY You need to remove the braces from the query in your trigger too, they change the meaning of the query. You use brackets in this way if you need to reference fields from a composite type. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4bd1a0c310411470018361! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help to identify stray row in a table
2010/4/23 சிவகுமார் மா : > 1. We have a production system tracking value added to a batch through > series of stages. Value table is updated through triggers on data > tables. > > 2. These trigger functions have been tested and validated for over 1.5 > years with more than 100,000 records. > > 3. We found a difference in the calculation while verifying March 2010 > records. Rechecked functions and data. Identified the source of > difference as a row in value table which could not be explained. > > This table is filled by a trigger function, not touched by application code. > > 4. Tried looking at oid of the rows. > > select oid, * from transaction_value where transaction_id in (633509, > 633507, 633505) and cost_type_id=1; > oid | transaction_id | source_id | cost_type_id | section_id | value > ---++---+--++- > 570938 | 633505 | | 1 | | > 614078.0250 > 292333023 | 633509 | 629483 | 1 | | > 12284.9411 > 292332829 | 633505 | 629483 | 1 | | > 115701.8092 > 292332944 | 633507 | 629483 | 1 | | > 85101.1377 > > Three rows starting with 292333--- are expected ones. The one with > oid=570938 is the unexplained one. > > Does this indicate any thing? Or should we look elsewhere? You haven't given enough information to make any sort of reasonable diagnosis. Most people are going to assume the problem is on your end but it's possible to know for sure without having the trigger function at the very least. 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] How to read the execution Plan
thanks a lot. I am going thru it Regards On Thu, Apr 22, 2010 at 10:56 AM, Ben Chobot wrote: > On Apr 22, 2010, at 5:43 AM, akp geek wrote: > > > Hi all - > > > > I would request, If any one has document on how to read and > interpret the postgres execution plan, can you please share it? > > http://wiki.postgresql.org/wiki/Using_EXPLAIN
[GENERAL] Need help to identify stray row in a table
1. We have a production system tracking value added to a batch through series of stages. Value table is updated through triggers on data tables. 2. These trigger functions have been tested and validated for over 1.5 years with more than 100,000 records. 3. We found a difference in the calculation while verifying March 2010 records. Rechecked functions and data. Identified the source of difference as a row in value table which could not be explained. This table is filled by a trigger function, not touched by application code. 4. Tried looking at oid of the rows. select oid, * from transaction_value where transaction_id in (633509, 633507, 633505) and cost_type_id=1; oid| transaction_id | source_id | cost_type_id | section_id |value ---++---+--++- 570938 | 633505 | |1 || 614078.0250 292333023 | 633509 |629483 |1 || 12284.9411 292332829 | 633505 |629483 |1 || 115701.8092 292332944 | 633507 |629483 |1 || 85101.1377 Three rows starting with 292333--- are expected ones. The one with oid=570938 is the unexplained one. Does this indicate any thing? Or should we look elsewhere? 5. Running PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux) Have not done upgrades for quite some time. Will do so over the week end. Thanks for any help. Best regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.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] EXCEPT doesn't compare TIMESTAMP type?
Great! Thanks Alban, Alexander, and Thomas. That solved the issue but could you tell me what is the issue when I give brackets in second query? techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now()) except *select id, txid, txtime *from changelogtest where id=5; INSERT 0 1 techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now()) except select* (id, txid, txtime)* from changelogtest where id=5; ERROR: each EXCEPT query must have the same number of columns LINE 2: except select (id, txid, txtime) I need brackets because this query actually I am using from trigger like below and it gives the same error: Could you please help me with it. CREATE OR REPLACE FUNCTION insert_history_info() RETURNS VOID AS $BODY$ my $query = (< wrote: > On 23 Apr 2010, at 13:17, dipti shah wrote: > > For this case you're using 3 values in the first half of the expression and > only 1 in the second: > > > techdb=# INSERT INTO changelogtest (id, txid, txtime) > > values (5, 123, 'now') > ^^ ^^^ --- 3 columns, namely int, int & text. > > except > > select (id, txid, txtime) >^^^--- 1 column, a row-type containing (int, int, > timestamp) > > from changelogtest > > where id=5; > > ERROR: each EXCEPT query must have the same number of columns > > LINE 2: except select (id, txid, txtime) > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:1050,4bd18a8610411242712669! > > >
Re: [GENERAL] EXCEPT doesn't compare TIMESTAMP type?
On 23 Apr 2010, at 13:17, dipti shah wrote: For this case you're using 3 values in the first half of the expression and only 1 in the second: > techdb=# INSERT INTO changelogtest (id, txid, txtime) > values (5, 123, 'now') ^^ ^^^ --- 3 columns, namely int, int & text. > except > select (id, txid, txtime) ^^^--- 1 column, a row-type containing (int, int, timestamp) > from changelogtest > where id=5; > ERROR: each EXCEPT query must have the same number of columns > LINE 2: except select (id, txid, txtime) 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,4bd18a9110411947912088! -- 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] EXCEPT doesn't compare TIMESTAMP type?
On Friday 23 April 2010 14:17:32 dipti shah wrote: > Thanks but I don't have text type in my table. But you are trying to insert text value 'now' into table, that's why appears this error. If you want to insert current time try using function now() not text 'now' In Your case insert should be following INSERT INTO changelogtest (id, txid, txtime) values (5, 123, now()) except select id, txid, txtime from changelogtest where id=5; -- Alexandr Popov
Re: [GENERAL] EXCEPT doesn't compare TIMESTAMP type?
dipti shah, 23.04.2010 13:17: Thanks but I don't have text type in my table. sysdb=# \d changelogtest techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now') except select id, txid, txtime from changelogtest where id=5; 'now' *is* a text type value Thomas -- 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] EXCEPT doesn't compare TIMESTAMP type?
Thanks but I don't have text type in my table. sysdb=# \d changelogtest ... Table "sysdb.changelogtest" Column |Type |Modifiers +-+-- id | integer | not null txid | integer | not null default txid_current() txtime | timestamp without time zone | not null default transaction_timestamp() Foreign-key constraints: "changelogtest_id_fkey" FOREIGN KEY (id) REFERENCES logtable(id) Moreover, the query behaves differently when I proive brackates after SELECT in second query. techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now') except select id, txid, txtime from changelogtest where id=5; ERROR: EXCEPT types text and timestamp without time zone cannot be matched LINE 2: except select id, txid, txtime ^ techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now') except select (id, txid, txtime) from changelogtest where id=5; ERROR: each EXCEPT query must have the same number of columns LINE 2: except select (id, txid, txtime) ^ Below works: techdb=# INSERT INTO changelogtest (id, txid) values (5, 123) except select id, txid from changelogtest where id=5; INSERT 0 0 I don't know how to resolve my issue. Could you please help me out. Thanks, Dipti On Fri, Apr 23, 2010 at 4:08 PM, Raymond O'Donnell wrote: > On 23/04/2010 11:31, dipti shah wrote: > > > ERROR: EXCEPT types text and timestamp without time zone cannot be > matched > > LINE 2: except select id, txid, txtime > > Try adding a cast to one of them. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
Re: [GENERAL] PSQL segmentation fault after setting host
Hooray, uninstalling psqlODBC worked! Thank you so much for all of your help! Cheers, Morgan Taschuk Craig Ringer wrote: On 23/04/2010 2:06 AM, Tom Lane wrote: On Red Hat systems the thing to do is install the postgresql-debuginfo RPM that matches your postgresql RPMs, but I'm not sure exactly how Ubuntu packages that information. Though it turns out to be unnecessary for this person's question, for future reference: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Installing_External_symbols -- 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] EXCEPT doesn't compare TIMESTAMP type?
On 23/04/2010 11:31, dipti shah wrote: > ERROR: EXCEPT types text and timestamp without time zone cannot be matched > LINE 2: except select id, txid, txtime Try adding a cast to one of them. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] EXCEPT doesn't compare TIMESTAMP type?
it tells you that it is not able to compare timestamp with text. Different types. Cast if you have to explicitly. -- GJ
[GENERAL] EXCEPT doesn't compare TIMESTAMP type?
Hi, could anyone please tell me what is wrong in below query. Does it mean that EXCEPT doesn't allow comparing TIMESTAMP type? techdb=# INSERT INTO changelogtest (id, txid, txtime) values (5, 123, 'now') except select id, txid, txtime from changelogtest where id=5; ERROR: EXCEPT types text and timestamp without time zone cannot be matched LINE 2: except select id, txid, txtime ^ Thanks, Dipti
Re: [GENERAL] Multicolumn primary key with null value
On 23/04/10 15:50, Adrian von Bidder wrote:
On Friday 23 April 2010 03.27:29 Craig Ringer wrote:
insert into test (a,b) values ('fred',NULL);
insert into test (a,b) values ('fred',NULL);
... and will succeed:
Hmm. Perhaps not as ugly as "none" placeholders:
create unique index on test (b) where a is null;
create unique index on test (a) where b is null;
True ... and Pg can even use them both together for bitmap index scans,
albeit not as efficiently as a single multicolumn index.
This really isn't viable for >2 nullable fields, though, as the number
of indexes increases to impractical levels rather quickly.
--
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] Creating indexes?
On Thursday 22 April 2010 23.36:51 Bjørn T Johansen wrote: > E.g I have two fields in a table that I want indexed, is it best to > create one index combining the two fields or creating one for each > field? This depends on the queries you run against the table. It's not possible to give a general answer here. cheers -- vbi -- How to overclock the board to the attachment from Windows? You should telnet from the floppy disk and from the tools menu inside Netscape you either never have to log from the POP3 miditower, or can't debug a clock of a OpenGL file of a software of a BIOS in order to explore the editor. signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Multicolumn primary key with null value
On Friday 23 April 2010 03.27:29 Craig Ringer wrote:
> insert into test (a,b) values ('fred',NULL);
> insert into test (a,b) values ('fred',NULL);
>
>
> ... and will succeed:
Hmm. Perhaps not as ugly as "none" placeholders:
create unique index on test (b) where a is null;
create unique index on test (a) where b is null;
cheers
-- vbi
--
Protect your privacy - encrypt your email: http://fortytwo.ch/gpg/intro
signature.asc
Description: This is a digitally signed message part.
Re: [GENERAL] Issue in Improving the performance using prepared plan
:) I realized that. Thanks. On Thu, Apr 22, 2010 at 6:53 PM, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > > I have written following trigger and trying to improve the performance by > > using prepared query everytime. I have used spi_prepare to prepare the > query > > and $_SHARED global hash to persist the prepared plan but it doesn't seem > to > > work. Though $query will be same always in following trigger, it prepares > > query everytime and never uses prepared plan. > > Could anyone tell me what's wrong going on? > > Works fine for me. Note that your elog outputs are switched - you are > claiming > the already prepared plan for the first time (if exists) and claiming the > first prepare when in fact it is reusing (else). > > - -- > Greg Sabino Mullane g...@turnstep.com > End Point Corporation http://www.endpoint.com/ > PGP Key: 0x14964AC8 201004220922 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -BEGIN PGP SIGNATURE- > > iEYEAREDAAYFAkvQTasACgkQvJuQZxSWSsiH1wCgwiuBRmjmGZ0WWKKD/6BwovhR > M7IAoME88RAuNAd0P1tH4ug/I8FFJ8Bj > =CG70 > -END PGP SIGNATURE- > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
