Re: [HACKERS] Transactions per second
Ühel kenal päeval, R, 2006-05-05 kell 17:51, kirjutas Jim C. Nasby: On Sat, May 06, 2006 at 12:09:45AM +0300, Hannu Krosing wrote: ??hel kenal p??eval, N, 2006-05-04 kell 17:23, kirjutas Jim Nasby: I often find myself wanting to know how many transactions per second a database is committing to disk, as well as how many queries per second it's processing. While Larry's busy making stats changes, I'd like to propose a few more counters: Number of commits: Ideally, this would only count transactions that actually modify data I' prefer one counter for total and one for data modifying statements. The reason I added in a transaction counter is because that's the only thing that tells you about the fsync rate on the WAL. Number of statements: Simply, how many statements have been executed Number of DML statements: how many insert/update/delete statements executed. I'd like to add a request for function call counters, presented to user as view pg_stat_user_functions, similar in content to current pg_stat_user_tables. actually I'd like to have the following data gathered for each function: call count total call time min running time max running time Wouldn't capturing timing statistics for short-running functions be too prohibitive? I'm thinking this is similar to the overheads we see with EXPLAIN ANALYZE... I hope they are still several orders of manitude cheper than whole statements, even simple ones like 'BEGIN;'. Currently I have them recorded in pg_log anyway, and I suspect that they are computed internally even if not requested by something like log_min_duration_statement. Having these timings in stats views would save me a lot of log-parsing, which is often not trivial, especially when having to distinguish functions with same name but different argument sets. I'd also like a possibility to gather information about usage of locks for both function calls and simple DML statements. What do you mean by 'usage of locks'? Mostly I would like to have statistics about the locks that were not granted immediately, that is if there has been a need to wait on locks. It would be nice to have this info in both pg_stat_*_tables/indexes and independently in pg_stat_locks view for locs not associated with any relation. Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] InsertXLogFile in pg_resetxlog
On Mon, May 01, 2006 at 10:26:33PM -0400, Jonah H. Harris wrote: Just to update everyone, I've refactored a good amount of the rebuild-control-values-from-WAL code and should have it ready for -patches tomorrow. I've not seen any patch for this come past... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] Remove behaviour of postmaster -o
The first item on the todo list is remove behaviour of postmaster -o. Does that simply mean remove the option and the associated processing from postmaster.c? Is anyone working on this? I've attached a naive patch that does what I've described above. It compiles and passes the test script in the tools directory. Is there anything else this todo should address? Thanks, Andy ___ To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre. http://uk.security.yahoo.com*** ./src/backend/postmaster/postmaster.c.orig 2006-05-06 11:26:58.0 +0100 --- ./src/backend/postmaster/postmaster.c 2006-05-06 11:42:20.0 +0100 *** *** 175,184 #define MAXLISTEN 64 static int ListenSocket[MAXLISTEN]; - /* - * Set by the -o option - */ - static char ExtraOptions[MAXPGPATH]; /* * These globals control the behavior of the postmaster in case some --- 175,180 *** *** 346,352 #endif char my_exec_path[MAXPGPATH]; char pkglib_path[MAXPGPATH]; - char ExtraOptions[MAXPGPATH]; char lc_collate[LOCALE_NAME_BUFLEN]; char lc_ctype[LOCALE_NAME_BUFLEN]; } BackendParameters; --- 342,347 *** *** 517,529 SetConfigOption(allow_system_table_mods, true, PGC_POSTMASTER, PGC_S_ARGV); break; - case 'o': - /* Other options to pass to the backend on the command line */ - snprintf(ExtraOptions + strlen(ExtraOptions), - sizeof(ExtraOptions) - strlen(ExtraOptions), - %s, optarg); - break; - case 'P': SetConfigOption(ignore_system_indexes, true, PGC_POSTMASTER, PGC_S_ARGV); break; --- 512,517 *** *** 1158,1164 printf(_( -l enable SSL connections\n)); #endif printf(_( -N MAX-CONNECT maximum number of allowed connections\n)); - printf(_( -o OPTIONS pass \OPTIONS\ to each server process (obsolete)\n)); printf(_( -p PORT port number to listen on\n)); printf(_( -s show statistics after each query\n)); printf(_( -S WORK-MEM set amount of memory for sorts (in kB)\n)); --- 1146,1151 *** *** 2844,2856 * postgres [secure switches] -y databasename [insecure switches] * where the switches after -y come from the client request. * - * The maximum possible number of commandline arguments that could come - * from ExtraOptions or port-cmdline_options is (strlen + 1) / 2; see - * split_opts(). * */ maxac = 10; /* for fixed args supplied below */ - maxac += (strlen(ExtraOptions) + 1) / 2; if (port-cmdline_options) maxac += (strlen(port-cmdline_options) + 1) / 2; --- 2831,2839 *** *** 2860,2872 av[ac++] = postgres; - /* - * Pass any backend switches specified with -o in the postmaster's own - * command line. We assume these are secure. (It's OK to mangle - * ExtraOptions now, since we're safely inside a subprocess.) - */ - split_opts(av, ac, ExtraOptions); - /* Tell the backend what protocol the frontend is using. */ snprintf(protobuf, sizeof(protobuf), -v%u, port-proto); av[ac++] = protobuf; --- 2843,2848 *** *** 3815,3822 StrNCpy(param-pkglib_path, pkglib_path, MAXPGPATH); - StrNCpy(param-ExtraOptions, ExtraOptions, MAXPGPATH); - StrNCpy(param-lc_collate, setlocale(LC_COLLATE, NULL), LOCALE_NAME_BUFLEN); StrNCpy(param-lc_ctype, setlocale(LC_CTYPE, NULL), LOCALE_NAME_BUFLEN); --- 3791,3796 *** *** 4018,4025 StrNCpy(pkglib_path, param-pkglib_path, MAXPGPATH); - StrNCpy(ExtraOptions, param-ExtraOptions, MAXPGPATH); - setlocale(LC_COLLATE, param-lc_collate); setlocale(LC_CTYPE, param-lc_ctype); } --- 3992,3997 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Remove behaviour of postmaster -o
Andy Chambers wrote: The first item on the todo list is remove behaviour of postmaster -o. Does that simply mean remove the option and the associated processing from postmaster.c? Is anyone working on this? I've attached a naive patch that does what I've described above. It compiles and passes the test script in the tools directory. Is there anything else this todo should address? I thought Peter was working on this. Peter? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remove behaviour of postmaster -o
Andy Chambers [EMAIL PROTECTED] writes: The first item on the todo list is remove behaviour of postmaster -o. Does that simply mean remove the option and the associated processing from postmaster.c? No, it means something closer to this: http://archives.postgresql.org/pgsql-hackers/2005-12/msg01031.php http://archives.postgresql.org/pgsql-patches/2006-01/msg00239.php Not sure why Peter didn't continue working on it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pseudo-type record arguments for PL-functions
David Fetter wrote: On Thu, May 04, 2006 at 09:02:02PM +0200, Thomas Hallgren wrote: Tom Lane wrote: Why can PLs not handle pseudo-types? No one's done the work to figure out which ones are sensible to support and then add the logic needed to support them. PL/Java will handle the RECORD type correctly. I'm just finalizing a new, more flexible, type mapping implementation for PL/Java and it would be easy to add support for more pseudo types too. But what others would make sense? Ideally, some way to get all kinds of user-defined types. DOMAINs, too. :) OK, got them covered as well. Only thing that remain now is arrays. I have a hard time figuring out how to manage them. I'm looking at the arrayutils.c. The thing that makes me a bit confused is the ArrayMetaState. The functions obtain it using: my_extra = (ArrayMetaState *) fcinfo-flinfo-fn_extra; which is fine if there's only one array parameter. What happens if I have two? And how do I declare a function that takes, say, an array of int's as a parameter (in SQL that is)? I find very little information about how to write functions that deals with arrays. My only source of information right now is the arrayutils.c. Other pointers to docs and code are greatly appreciated. Kind Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pseudo-type record arguments for PL-functions
On Sat, May 06, 2006 at 05:26:31PM +0200, Thomas Hallgren wrote: I find very little information about how to write functions that deals with arrays. My only source of information right now is the arrayutils.c. Other pointers to docs and code are greatly appreciated. Looking at contrib/intarray/_int_op.c might help. It does something like this: ArrayType *a = (ArrayType *) DatumGetPointer(PG_DETOAST_DATUM_COPY(PG_GETARG_DATUM(0))); The file src/include/utils/array.h also seems to have many useful functions. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] pseudo-type record arguments for PL-functions
Thomas Hallgren [EMAIL PROTECTED] writes: The thing that makes me a bit confused is the ArrayMetaState. The functions obtain it using: my_extra = (ArrayMetaState *) fcinfo-flinfo-fn_extra; which is fine if there's only one array parameter. What happens if I have two? Make a struct that can hold two ArrayMetaStates. Or whatever else you need. What a C function keeps in fn_extra is its own affair. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] InsertXLogFile in pg_resetxlog
On 5/6/06, Martijn van Oosterhout kleptog@svana.org wrote: I've not seen any patch for this come past... Yes, I got a little busy. I ended up refactoring a good amount of the code because the entire thing is a little ugly. I'll go ahead and just fix the Coverity stuff first and send the refactored patch later. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] InsertXLogFile in pg_resetxlog
Jonah H. Harris wrote: On 5/6/06, Martijn van Oosterhout kleptog@svana.org wrote: I've not seen any patch for this come past... Yes, I got a little busy. I ended up refactoring a good amount of the code because the entire thing is a little ugly. I'll go ahead and just fix the Coverity stuff first and send the refactored patch later. Jonah, it doesn't have to be 100% cleaned up, but if you can fix the actual bugs, and clean up 50% of it, it is better than doing just the bug fixes. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] [PATCH] Add support for GnuTLS
Martijn van Oosterhout wrote: -- Start of PGP signed section. On Sat, May 06, 2006 at 02:47:33PM -0400, Bruce Momjian wrote: Martijn van Oosterhout wrote: If you like I can split it into two patches, one patch splits the openssl stuff out of the main files and a second which adds gnutls support. Yes, I understood that, but we now have duplicate files for secure connections, meaning we have double maintenance in some cases. Hmm, I see your point. I guess that's an unavoidable side-effect of the process :(. However, judging from the CVS logs, these have not been files with a high change rate. I think it's worth it but I can imagine other people see that differently. There is not a lot of code can be shared. What can be already is eg. prepare_for_client_read and client_read_ended, the names of the files used, EPIPE handling, etc. [ Discussion moved to hackers.] The only other case I can think of where we support multiple libraries for licensing reasons is readline/libedit, but in that case libedit has the same API as readline, so we don't require much code duplication, must some configure magic. I see the problem with the OpenSSL license: http://www.gnome.org/~markmc/openssl-and-the-gpl.html Of course, we are trading a BSD license with advertizing clause with an LGPL license. I guess it makes sense. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pseudo-type record arguments for PL-functions
Tom Lane wrote: Make a struct that can hold two ArrayMetaStates. Or whatever else you need. What a C function keeps in fn_extra is its own affair. Yes, of course. I see that now. I was unaware that a function had an associated user data. What's the semantics associated with the fn_extra? Does it retain its setting throughout a session (i.e. the lifetime of the backend process)? PL/Java associates a structure with a function using a hash map lookup on the function Oid. Seems I could use the fn_extra and remove that map altogether. Then again, there are times when I need to invalidate the associated structure of all java functions due to reload of jar files. Is there any way that I can list all functions for a specific language and get hold of their current setting of the fn_extra? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pseudo-type record arguments for PL-functions
Martijn van Oosterhout wrote: Looking at contrib/intarray/_int_op.c might help. It does something like this: ArrayType *a = (ArrayType *) DatumGetPointer(PG_DETOAST_DATUM_COPY(PG_GETARG_DATUM(0))); The file src/include/utils/array.h also seems to have many useful functions. Hope this helps, Yes, the intarray stuff was very helpful but also somewhat confusing. Why are there two ways of representing some of the array types? I mean, why is there an _int4 when you could just as well write int4[]? I'm probably missing the point altogether. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bug? non working casts for domain
I can confirm that this is a bug. The attached SQL shows that creating
a CAST _to_ a domain type doesn't work, though the cast can be created.
The attached SQL provided by Fabien shows the failure.
The error is coming from parse_expr.c::typecast_expression, and its call
to typenameTypeId(). I wish I understood how we do domains better to
fix this properly. Anyone?
---
Fabien COELHO wrote:
Dear PostgreSQL developer.
Although it is allowed to create a cast for a domain, it seems that there
is no way to trigger it. You can find attached an sql script to illustrate
the issue with postgresql 8.1.3. The create cast and create domain
documentations do not seem to discuss this point.
ISTM that it is a pg bug. Indeed, either
(1) the create cast should be rejected if it is not allowed for domains.
or
(2) the function should be triggered by explicit casts to the domain.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
DROP DOMAIN a_year CASCADE;
-- a simple domain
CREATE DOMAIN a_year AS INTEGER
CHECK (VALUE BETWEEN 1 AND 3000);
-- ok
SELECT 1::a_year;
SELECT CAST('2000' AS a_year);
-- fails as expected
SELECT 0::a_year;
CREATE FUNCTION date2year(DATE)
RETURNS a_year IMMUTABLE STRICT AS $$
SELECT EXTRACT(YEAR FROM $1)::a_year;
$$ LANGUAGE sql;
-- ok
SELECT date2year(CURRENT_DATE);
-- fails as expected
SELECT date2year(DATE '3001-01-01');
CREATE CAST (DATE AS a_year)
WITH FUNCTION date2year(DATE);
-- fails, I would expect 1970
SELECT (DATE '1970-03-20')::a_year;
-- fails, I would expect the current year
SELECT CURRENT_DATE::a_year;
SELECT CAST(CURRENT_DATE AS a_year);
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] pseudo-type record arguments for PL-functions
Thomas Hallgren [EMAIL PROTECTED] writes: Yes, of course. I see that now. I was unaware that a function had an associated user data. What's the semantics associated with the fn_extra? Does it retain its setting throughout a session (i.e. the lifetime of the backend process)? No, just for the query. I'd advise using it only as a cache, although set-returning functions sometimes use it to hold state associated with successive rows of their result. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] bug? non working casts for domain
Bruce Momjian pgman@candle.pha.pa.us writes: The error is coming from parse_expr.c::typecast_expression, and its call to typenameTypeId(). I wish I understood how we do domains better to fix this properly. Anyone? The reason the cast isn't found is that find_coercion_pathway() strips off the domains before it ever even looks in pg_cast. We can't simply remove that logic without breaking things (notably, the ability to cast between a domain and its base type). I think it would be a mistake to consider this behavior in isolation anyway --- it's fairly tightly tied to the way that domains are handled (or, mostly, ignored) in operator/function lookup. See recent gripes from Elein. If someone can put together a coherent proposal for how domains should be dealt with in operator/function resolution, I'm all ears. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pseudo-type record arguments for PL-functions
On Sun, May 07, 2006 at 12:16:16AM +0200, Thomas Hallgren wrote: Yes, the intarray stuff was very helpful but also somewhat confusing. Why are there two ways of representing some of the array types? I mean, why is there an _int4 when you could just as well write int4[]? I'm probably missing the point altogether. FWICT, Prefixing a '_' is the convention used to make the array type's typname unique. Being able to reference array types as _type is a side effect. (array types being actual rows in pg_type) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pseudo-type record arguments for PL-functions
James William Pye [EMAIL PROTECTED] writes: On Sun, May 07, 2006 at 12:16:16AM +0200, Thomas Hallgren wrote: Why are there two ways of representing some of the array types? I mean, why is there an _int4 when you could just as well write int4[]? I'm probably missing the point altogether. FWICT, Prefixing a '_' is the convention used to make the array type's typname unique. Being able to reference array types as _type is a side effect. (array types being actual rows in pg_type) There used to be some contexts where you *had* to write _foo instead of foo[] because the grammar only allowed simple names and not the full TypeName production. I think we've fixed them all, but very likely there are places in contrib still following the old convention. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
