[HACKERS] Doubt in index subplan query
Hello, I have a query plan for a certain query Nested Loop (cost=1.00..38761761090.50 rows=3000608 width=8) - Seq Scan on lineitem (cost=1.00..100213649.15 rows=6001215 width=8) - Index Scan using oindex2 on myorders (cost=0.00..6442.27 rows=1 width=4) Index Cond: (outer.l_orderkey = myorders.o_orderkey) Filter: (subplan) SubPlan - Index Scan using cnation on customer (cost=0.00..12859.39 rows=5251 width=0) Index Cond: (c_nationkey = 10) How is the subplan handled by postgres at index level ? Is any sort of hashing done ? Thanks and regards, Suresh
[HACKERS] ...Roll Back issue in PGSQL..
Hi I am trying to create a TRIGGER function,that populates values to another table upon inserting a value into a table. The issue is,if there is a exception(in Table 2) everything gets rolled back including Table1.I dont want this to happen.i want the value of table1 to stay. I tried tryexcept and introduced SAVEPOINT.But resulted in runtime error. Can anyone please help me out -- Deepak
Re: [HACKERS] Backend Stats Enhancement Request
Hi, I'm new to the postgresql source, thought I'd try my hand at implementing the change suggested (i.e. the GUC-ification of the PGBE_ACTIVITY_SIZE constant) to get my hands dirty with the code. How does this sound: * A new GUC variable -- activity_message_size -- will be introduced * The PGBE_ACTIVITY_SIZE #define becomes PGBE_DEFAULT_ACTIVITY_SIZE * Minimum value of PGBE_DEFAULT_ACTIVITY_SIZE, maximum value of INT_MAX? I'm struggling a little to come up with a decent description of the GUC variable -- something along the lines of Sets the maximum length of backend status messages. Any suggestions? Also: how should we allocate the memory for PgBackendStatus.st_activity? I'm guessing it's going to be necessary to keep this in shmem ... Cheers, T David Miller wrote: That's not where the problem is. The people who will be left holding the short end of the stick are the ones who can't raise their SHMMAX setting past a couple of megabytes. It might be feasible to make pg_stat_activity's max string length a postmaster-start-time configuration option. I am fine with a postmaster-start-time configuration option. It is not as flexible as I would like, but would serve the immediate need and keep me from having to patch every release of Postgres we install on boxes. The load on our production servers really prohibits any kind of processing of the log files locally. We have tried using several log shipping methods to process the logs on a machine with fewer running processes. These large queries are generated by a third party tool that we have very limited control over. Some of the queries captured are as large 16K. The queries are poorly written/generated. David Miller River Systems, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Not valid dump [8.2.9, 8.3.1]
Hi all, we have faced lately dumps not valid, the bug can be replicated using a 8.2.9 or a 8.3.1 server. These are the steps to create the database that will generate a not valid dump: --- CREATE TABLE t_public ( a integer ); CREATE OR REPLACE FUNCTION sp_public ( ) RETURNS INTEGER AS' BEGIN PERFORM * FROM t_public LIMIT 1; RETURN 0; END; ' LANGUAGE 'plpgsql' IMMUTABLE; CREATE SCHEMA my_schema; CREATE TABLE my_schema.table_ref(x integer primary key); CREATE TABLE my_schema.table_test(x integer references my_schema.table_ref (x)); CREATE INDEX idx ON my_schema.table_test (x) WHERE x = sp_public(); Briefly: in the public schema we have a function that uses a table. In another schema we have a table with a foreign key to another table, and an partial index that uses the function in the public schema. The function is immutable because in our case the table being used inside the function is a lookup table (readonly). When the dump is restored the index idx is created but the foreign key is not. This is the error we obtain during the restore: psql:test.dump:143: ERROR: relation t_public does not exist CONTEXT: SQL statement SELECT * FROM t_public LIMIT 1 PL/pgSQL function sp_public line 2 at perform SQL statement SELECT fk.x FROM ONLY my_schema.table_test fk LEFT OUTER JOIN ONLY my_schema.table_ref pk ON (pk.x=fk.x) WHERE pk.x IS NULL AND (fk.x IS NOT NULL) Regards -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ecpg generated files ignorable?
On Wed, Jun 18, 2008 at 09:26:24PM -0400, Tom Lane wrote: ? src/interfaces/ecpg/compatlib/libecpg_compatddll.def ? src/interfaces/ecpg/ecpglib/libecpgddll.def ? src/interfaces/ecpg/pgtypeslib/libpgtypesddll.def IIRC these are for MS VC++. I am not sure if these should stay on the source dir (i.e. are they needed in the tarball). If they are supposed to, then I think they should be listed in the corresponding .cvsignore file. Makefile.shlib builds these in the distprep action, so I suppose they're supposed to be there. libpq .cvsignore's its equivalent files, so I'd agree with doing that. It looks like there should be three such files in each directory, though, not just one? Well, in my source tree I have two, one for MS VC++ and one for Borland C++ Builder. And yes, I can build a third one for MS VC++ as well by just issuing the corresponding make call. However, I have no idea whether we need both, the only differ in the lib name: --- libecpgddll.def 2008-06-20 12:33:29.0 +0200 +++ libecpgdll.def 2008-06-20 12:33:16.0 +0200 @@ -1,5 +1,5 @@ ; DEF file for MS VC++ -LIBRARY LIBECPGD +LIBRARY LIBECPG EXPORTS ECPGallocate_desc@ 1 ECPGconnect @ 2 Maybe someone with more Windows knowledge can explain this? Magnus? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]
Gaetano Mendola [EMAIL PROTECTED] writes: we have faced lately dumps not valid, the bug can be replicated using a 8.2.9 or a 8.3.1 server. These are the steps to create the database that will generate a not valid dump: This is a bug in your function: it will not work if the search path doesn't contain the public schema. You'd be best advised to make it qualify the reference to t_public explicitly. In 8.3 another possibility would be to attach an explicit search_path setting to the function. (Of course you realize that referencing any table at all in an immutable function is probably a mortal sin...) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
Thomas Lee [EMAIL PROTECTED] writes: How does this sound: * A new GUC variable -- activity_message_size -- will be introduced Well, message doesn't seem quite le mot juste to me for a column that is displaying a SQL command. Usually we'd use statement, command, or query to refer to one of those things. Since the relevant column of pg_stat_activity is already named current_query, perhaps the best choice is activity_query_size. Or activity_query_length? Another consideration is that it might be a good idea to name it to be obviously related to the controlling track_activities boolean. That would lead to track_activity_query_size, or track_activity_max_length, or some such. * Minimum value of PGBE_DEFAULT_ACTIVITY_SIZE, maximum value of INT_MAX? I was thinking about a range of 100 to 100K or thereabouts. INT_MAX is just silly... I'm struggling a little to come up with a decent description of the GUC variable -- something along the lines of Sets the maximum length of backend status messages. Any suggestions? Be specific: Sets the maximum length of pg_stat_activity.current_query. Also: how should we allocate the memory for PgBackendStatus.st_activity? I'm guessing it's going to be necessary to keep this in shmem ... Yup. Look at existing variable-size shmem allocations. max_prepared_transactions might be a good reference, since it's not used in very many places. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ...Roll Back issue in PGSQL..
On Fri, 2008-06-20 at 13:10 +0530, Deepak wrote: Hi I am trying to create a TRIGGER function,that populates values to another table upon inserting a value into a table. The issue is,if there is a exception(in Table 2) everything gets rolled back including Table1.I dont want this to happen.i want the value of table1 to stay. I tried tryexcept and introduced SAVEPOINT.But resulted in runtime error. Can anyone please help me out The function executed by the trigger will be executed as a single transaction. If any part fails, they all fail. Joshua D. Drake -- Deepak -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doubt in index subplan query
On Jun 20, 2008, at 1:11 AM, Suresh wrote: I have a query plan for a certain query Nested Loop (cost=1.00..38761761090.50 rows=3000608 width=8) - Seq Scan on lineitem (cost=1.00..100213649.15 rows=6001215 width=8) - Index Scan using oindex2 on myorders (cost=0.00..6442.27 rows=1 width=4) Index Cond: (outer.l_orderkey = myorders.o_orderkey) Filter: (subplan) SubPlan - Index Scan using cnation on customer (cost=0.00..12859.39 rows=5251 width=0) Index Cond: (c_nationkey = 10) How is the subplan handled by postgres at index level ? Is any sort of hashing done ? This is better asked on pgsql-general... but the subplan does exactly what it says; an index scan. It will be executed for every row of the calling query. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Backend Stats Enhancement Request
On Jun 20, 2008, at 9:49 AM, Tom Lane wrote: * Minimum value of PGBE_DEFAULT_ACTIVITY_SIZE, maximum value of INT_MAX? I was thinking about a range of 100 to 100K or thereabouts. INT_MAX is just silly... I realize we just got rid of stats_command_string, but if we're adding a GUC back in we might as well allow it to be set to 0 which disables logging. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Backend Stats Enhancement Request
Decibel! [EMAIL PROTECTED] writes: I realize we just got rid of stats_command_string, but if we're adding a GUC back in we might as well allow it to be set to 0 which disables logging. How would that not duplicate track_activities? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Need Round Robin Reviewer volunteers
Hackers, At the developer meeting, we determined that one thing needed to speed up the commitfests is a list of people who were available to review assigned patches, and someone to do the assigning. Well, for July I'm the assignor, and I'm looking for some assignees. Here's how it will work: July 1: commitfest starts. July 7: reviewer assignment, 1st round July 10: reviewer assignment, 2nd round July 15 (hopefully): commitfest complete. Reviewer assgnment: I look over the list of submitted patches and see which ones aren't getting attention. shared_buffer fashion, I start assigning them round-robin from the people our pool of reviewers who haven't already taken on patches. I'll e-mail you with a patch I want you to review, and you will accept or reject the assignment promptly. I'll continue this until everything is assigned. So, can everyone who is qualified to be part of the pool please put your name on the RRR list? http://wiki.postgresql.org/wiki/RRReviewers Thanks! --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
On Jun 20, 2008, at 11:48 AM, Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: I realize we just got rid of stats_command_string, but if we're adding a GUC back in we might as well allow it to be set to 0 which disables logging. How would that not duplicate track_activities? Sorry, I thought there was more rolled into that than just current_query. I know this is quite a bit of churn here, but ISTM we should deprecate track_activities in favor of setting the new size GUC to 0. Unless folks are really tied to being able to control that without a restart... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Backend Stats Enhancement Request
On Friday 20 June 2008 10:49:49 Tom Lane wrote:
Thomas Lee [EMAIL PROTECTED] writes:
How does this sound:
* A new GUC variable -- activity_message_size -- will be introduced
Well, message doesn't seem quite le mot juste to me for a column that
is displaying a SQL command. Usually we'd use statement, command,
or query to refer to one of those things. Since the relevant column
of pg_stat_activity is already named current_query, perhaps the
best choice is activity_query_size. Or activity_query_length?
Another consideration is that it might be a good idea to name it to
be obviously related to the controlling track_activities boolean.
That would lead to track_activity_query_size, or
track_activity_max_length, or some such.
* Minimum value of PGBE_DEFAULT_ACTIVITY_SIZE, maximum value of INT_MAX?
I was thinking about a range of 100 to 100K or thereabouts. INT_MAX
is just silly...
I'm struggling a little to come up with a decent description of the GUC
variable -- something along the lines of Sets the maximum length of
backend status messages. Any suggestions?
Be specific:
Sets the maximum length of pg_stat_activity.current_query.
I think there are other places this might manifest itself besides
pg_stat_activity... I'm struggling to come up with something other than our
custom dtrace prob... ah, well, this will also control the size of statement
written into the logfile right? So we might want to take that into account.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] -head build error report
Linux jd-laptop 2.6.24-19-generic #1 SMP Wed Jun 4 16:35:01 UTC 2008 i686 GNU/Linux Using built-in specs. Target: i486-linux-gnu Configured with: ../src/configure -v --enable-languages=c,c ++,fortran,objc,obj-c++,treelang --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --with-gxx-include-dir=/usr/include/c++/4.2 --program-suffix=-4.2 --enable-clocale=gnu --enable-libstdcxx-debug --enable-objc-gc --enable-mpfr --enable-targets=all --enable-checking=release --build=i486-linux-gnu --host=i486-linux-gnu --target=i486-linux-gnu Thread model: posix gcc version 4.2.3 (Ubuntu 4.2.3-2ubuntu7) de -D_GNU_SOURCE -c -o gistget.o gistget.c In file included from gistget.c:21: ../../../../src/include/pgstat.h:15:36: error: portability/instr_time.h: No such file or directory In file included from gistget.c:21: ../../../../src/include/pgstat.h:326: error: expected specifier-qualifier-list before ‘instr_time’ ../../../../src/include/pgstat.h:566: error: expected specifier-qualifier-list before ‘instr_time’ make[4]: *** [gistget.o] Error 1 make[4]: Leaving directory `/home/jd/repos/pgsql/src/backend/access/gist' make[3]: *** [gist-recursive] Error 2 make[3]: Leaving directory `/home/jd/repos/pgsql/src/backend/access' make[2]: *** [access-recursive] Error 2 make[2]: Leaving directory `/home/jd/repos/pgsql/src/backend' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/jd/repos/pgsql/src' make: *** [all] Error 2 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
Robert Treat [EMAIL PROTECTED] writes: On Friday 20 June 2008 10:49:49 Tom Lane wrote: Be specific: Sets the maximum length of pg_stat_activity.current_query. I think there are other places this might manifest itself besides pg_stat_activity... No, there aren't. I'm struggling to come up with something other than our custom dtrace prob... ah, well, this will also control the size of statement written into the logfile right? And *certainly* not that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
