[PATCHES] fix log_min_duration_statement logic error
This patch fixes an obvious bug in the should I print the duration of this query? logic in postgres.c I also don't particularly like the format of the log message (for one thing, the duration_statement prefix in the log message shouldn't include an underscore, it's not a variable or anything -- and the case can be made that if we printed the duration because log_duration is set, we don't need to print it again if the duration of the query exceeded log_min_duration_statement), but I haven't changed it. -Neil Index: src/backend/tcop/postgres.c === RCS file: /var/lib/cvs/pgsql-server/src/backend/tcop/postgres.c,v retrieving revision 1.363 diff -c -r1.363 postgres.c *** src/backend/tcop/postgres.c 14 Sep 2003 00:03:32 - 1.363 --- src/backend/tcop/postgres.c 22 Sep 2003 16:14:37 - *** *** 955,964 usecs = (long) (stop_t.tv_sec - start_t.tv_sec) * 100 + (long) (stop_t.tv_usec - start_t.tv_usec); /* ! * Output a duration_query to the log if the query has exceeded * the min duration. */ ! if (usecs = save_log_min_duration_statement * 1000) ereport(LOG, (errmsg(duration_statement: %ld.%06ld %s, (long) (stop_t.tv_sec - start_t.tv_sec), --- 955,965 usecs = (long) (stop_t.tv_sec - start_t.tv_sec) * 100 + (long) (stop_t.tv_usec - start_t.tv_usec); /* ! * Output a duration_statement to the log if the query has exceeded * the min duration. */ ! if (save_log_min_duration_statement 0 ! usecs = save_log_min_duration_statement * 1000) ereport(LOG, (errmsg(duration_statement: %ld.%06ld %s, (long) (stop_t.tv_sec - start_t.tv_sec), ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] fix log_min_duration_statement logic error
Neil Conway [EMAIL PROTECTED] writes: I also don't particularly like the format of the log message (for one thing, the duration_statement prefix in the log message shouldn't include an underscore, it's not a variable or anything -- and the case can be made that if we printed the duration because log_duration is set, we don't need to print it again if the duration of the query exceeded log_min_duration_statement), but I haven't changed it. I think there should be just one duration: nnn log entry, printed if either condition holds. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] contrib mode - pgenv
Tom Lane writes: I should think there would be a notable performance advantage, since one need not create a temp table (which in our current implementation is just as expensive as creating a permanent table); not to mention dropping the temp table later, vacuuming up the resulting dead rows in pg_class and pg_attribute, etc. Whether that advantage is great enough to justify a nonstandard feature is unproven, but I imagine Mike could answer it with a little experimentation. We could support that if we implemented temporary tables with the standard SQL semantics, namely that the table structure persists but the data is deleted at the end of the session. That would also help in some other scenarios, such as creating functions that reference temporary tables. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] contrib mode - pgenv
Peter Eisentraut wrote:
Tom Lane writes:
I should think there would be a notable performance advantage, since
one need not create a temp table (which in our current implementation is
just as expensive as creating a permanent table); not to mention
dropping the temp table later, vacuuming up the resulting dead rows in
pg_class and pg_attribute, etc. Whether that advantage is great enough
to justify a nonstandard feature is unproven, but I imagine Mike could
answer it with a little experimentation.
We could support that if we implemented temporary tables with the standard
SQL semantics, namely that the table structure persists but the data is
deleted at the end of the session. That would also help in some other
scenarios, such as creating functions that reference temporary tables.
You're right, Peter. Another problem with the contrib module is that
it ignores transaction semantics:
BEGIN;
SELECT set_session_variable('Mike', 1);
ABORT;
And, in fact, the problem (Richard Huxton's desire for essentially
parameterized views) it attemps to solve could be worked around by
using a normal table with a function, assuming a function to get the
SQL-session-identifier exists, such as the backend_pid() function in
/contrib/misc:
CREATE TABLE session_data (
key SERIAL NOT NULL,
session_identifier text not null,
session_var text not null);
CREATE INDEX i_session_data1 ON session_data(session_identifier);
CREATE FUNCTION APP_SESSION_VAR() RETURNS text AS '
SELECT session_var
FROM session_data
WHERE session_identifier = backend_pid()
ORDER BY key DESC LIMIT 1;
' LANGUAGE 'SQL' STABLE;
CREATE VIEW my_project_list AS
SELECT *
FROM project_list
WHERE owner = APP_SESSION_VAR();
-- Upon session creation
INSERT INTO session_data (session_identifier, session_var)
VALUES (backend_pid(), 'Mike');
SELECT *
FROM my_project_list;
...
Mike Mascari
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] contrib mode - pgenv
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: We've rejected session variables many times before because they duplicate temporary tables. I don't see anything new added by this proposal. I should think there would be a notable performance advantage, since one need not create a temp table (which in our current implementation is just as expensive as creating a permanent table); not to mention dropping the temp table later, vacuuming up the resulting dead rows in pg_class and pg_attribute, etc. Whether that advantage is great enough to justify a nonstandard feature is unproven, but I imagine Mike could answer it with a little experimentation. Yes. I guess the lifetime of this contrib module would be short - SQL temporary tables that don't suffer those performance penalties would be the correct solution. I think it might be useful to some in the interim. From what I've seen on the mailing lists, people would like to build VIEW driven applications where the application maintains users and therefore they don't have the ability to leverage CURRENT_USER in view definitions... This gives them that opportunity. Perhaps its usefulness doesn't warrant a contrib module though... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] bug in clusterdb script
Hi There's a bug in the clusterdb script where it looks like the arguments to the psql command are being passed in the wrong order, so it fails when you run it on a database that is not on localhost. Here's the output from the command: 133 anands-Computer:bin/scripts clusterdb -h wooster -U rr granada psql: warning: extra option wooster ignored psql: warning: extra option -U ignored psql: warning: extra option rr ignored psql: warning: extra option -F: ignored psql: warning: extra option -P ignored psql: warning: extra option format=unaligned ignored psql: warning: extra option -t ignored psql: warning: extra option -c ignored psql: warning: extra option SELECT nspname, pg_class.relname, pg_class_2.relname FROM pg_class, pg_class AS pg_class_2 JOIN pg_namespace ON (pg_namespace.oid=relnamespace), pg_index WHERE pg_class.oid=pg_index.indrelid AND pg_class_2.oid=pg_index.indexrelid AND pg_index.indisclustered AND pg_class.relowner=(SELECT usesysid FROM pg_user WHERE usename=current_user) ignored psql: FATAL: user -h does not exist I'm attaching a patch that fixes the problem. The diff was run on postgresql 7.3.4 Thanks a lot. Anand Ranganathan __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com clusterdb.patch Description: clusterdb.patch ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
