[HACKERS] missing entry in GucSource_Names
It appears that the patch here: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=a30fa4ca33d055c46bebc0e5c701d5b4fd27814d missed adding PGC_S_DATABASE_USER to a few locations, most notably GucSource_Names, where the PGC_S_SESSION now points off the end of the array. Patch attached. Regards, Jeff Davis diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 1f63e06..776efe3 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -460,6 +460,7 @@ const char *const GucSource_Names[] = /* PGC_S_ARGV */ command line, /* PGC_S_DATABASE */ database, /* PGC_S_USER */ user, + /* PGC_S_DATABASE_USER */ database user, /* PGC_S_CLIENT */ client, /* PGC_S_OVERRIDE */ override, /* PGC_S_INTERACTIVE */ interactive, @@ -4556,7 +4557,8 @@ set_config_option(const char *name, const char *value, */ elevel = IsUnderPostmaster ? DEBUG3 : LOG; } - else if (source == PGC_S_DATABASE || source == PGC_S_USER) + else if (source == PGC_S_DATABASE || source == PGC_S_USER || + source == PGC_S_DATABASE_USER) elevel = WARNING; else elevel = ERROR; @@ -5762,6 +5764,7 @@ define_custom_variable(struct config_generic * variable) break; case PGC_S_DATABASE: case PGC_S_USER: + case PGC_S_DATABASE_USER: case PGC_S_CLIENT: case PGC_S_SESSION: default: -- 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] Skip WAL in ALTER TABLE
On Tue, 2009-10-13 at 11:39 +0900, Itagaki Takahiro wrote: We can skip writing WAL during COPY and CLUSTER if archive_mode is off, but we don't use the skipping during tables rewrites in ALTER TABLE. Also we don't use BulkInsertState there. Is it possible to use WAL-skipping and BulkInsertState in ATRewriteTable() ? If ok, I'll submit a patch for the next commitfest. Yes -- Simon Riggs www.2ndQuadrant.com -- 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] transaction_isolation vs. default_transaction_isolation
On Mon, 2009-10-12 at 22:22 -0700, Jeff Davis wrote: On Mon, 2009-10-12 at 22:13 -0700, Josh Berkus wrote: However, for *two* settings, and two settings only, we distinguish that by naming an identical setting default_* in postgresql.conf. This is confusing and inconsistent with the rest of the GUCS. Namely: default_transaction_isolation default_transaction_read_only I think they are named default_ because whatever you specify at the beginning of a transaction overrides the GUC. For example, in: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; SET default_transaction_isolation=serializable; ... the default_ makes it more clear which setting overrides the other. Yeah, they basically have semantics specified by the SQL standard that are not compatible with anything else in GUC land. They are more like SET LOCAL settings, but again not quite. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SQL Standard Committee
Please can someone pass me details on/off-list about joining the SQL Standard Committee, as discussed at developer meeting in May. Thanks, -- Simon Riggs www.2ndQuadrant.com -- 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] SQL Standard Committee
On Tue, 2009-10-13 at 10:18 +0100, Simon Riggs wrote: Please can someone pass me details on/off-list about joining the SQL Standard Committee, as discussed at developer meeting in May. I have replied to Simon off-list about the details, which had also been forwarded to the funds group. The current status is that no one at INCITS is replying to my emails. If someone preferably in the right time zone is interested in phoning them up or pursuing other means of getting an answer out of them, please let me know privately. I can't give this the attention it apparently needs right now. -- 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] Concurrency testing
On Wed, 2009-10-07 at 13:07 -0400, Alvaro Herrera wrote: David Fetter wrote: I seem to recall that there were some patches to get psql to help with such things, but they didn't go in. Time to revive them? Yeah, the API they implemented wasn't ideal, so there was some discussion that ended up with a specification everyone was happy with, but then nobody got around to implementing it. Feel free to whack that patch and resubmit ... See in the archives around here: http://archives.postgresql.org/message-id/8204.1207689...@sss.pgh.pa.us I would hope that Concurrent psql can be revived. There were some issues, but not really major ones. The main requirement is to be able to specify multiple sessions of activity from a single script. I would prefer it if we could do that via psql. If we start inventing new features in other tools we get situation similar to pgbench, which has some cute features, but they aren't in psql, which also has cute features, but different ones. Fragmentation wastes effort. I think Greg's comments are correct but I would say also correct. There is no reason to have just one test framework. We need as many as we need. -- Simon Riggs www.2ndQuadrant.com -- 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] COPY enhancements
Tom Lane wrote: Ultimately, there's always going to be a tradeoff between speed and flexibility. It may be that we should just say if you want to import dirty data, it's gonna cost ya and not worry about the speed penalty of subtransaction-per-row. But that still leaves us with the 2^32 limit. I wonder whether we could break down COPY into sub-sub transactions to work around that... Regarding that tradeoff between speed and flexibility I think we could propose multiple options: - maximum speed: current implementation fails on first error - speed with error logging: copy command fails if there is an error but continue to log all errors - speed with error logging best effort: no use of sub-transactions but errors that can safely be trapped with pg_try/catch (no index violation, no before insert trigger, etc...) are logged and command can complete - pre-loading (2-phase copy): phase 1: copy good tuples into a [temp] table and bad tuples into an error table. phase 2: push good tuples to destination table. Note that if phase 2 fails, it could be retried since the temp table would be dropped only on success of phase 2. - slow but flexible: have every row in a sub-transaction - is there any real benefits compared to pg_loader? Tom was also suggesting 'refactoring COPY into a series of steps that the user can control'. What would these steps be? Would that be per row and allow to discard a bad tuple? Emmanuel -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: m...@frogthinker.org Skype: emmanuel_cecchet -- 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
Robert Haas robertmh...@gmail.com wrote: I sometimes want to know what the planner thinks the cost of some plan other than the one actually selected would be. Another DBMS I used for years had a way to turn on an *extremely* verbose mode for their planner; it showed everything it considered with its related cost information. Even a moderately complex query generated hundreds or thousands of lines of output, so I rarely used it; but for those particularly stubborn queries, where you just can't understand why it's picking the plan it is, a little work wading through the output would *always* clear up the mystery. Now that we can generate EXPLAIN output in more structured formats, perhaps we could think about adding an extremely verbose mode where the planner would think out loud as a whole separate section from where we show the chosen plan? -Kevin -- 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I sometimes want to know what the planner thinks the cost of some plan other than the one actually selected would be. Another DBMS I used for years had a way to turn on an *extremely* verbose mode for their planner; it showed everything it considered with its related cost information. Even a moderately complex query generated hundreds or thousands of lines of output, so I rarely used it; but for those particularly stubborn queries, where you just can't understand why it's picking the plan it is, a little work wading through the output would *always* clear up the mystery. Now that we can generate EXPLAIN output in more structured formats, perhaps we could think about adding an extremely verbose mode where the planner would think out loud as a whole separate section from where we show the chosen plan? Well, we have OPTIMIZER_DEBUG, which is a compile-time flag, but that perhaps can be changed to output as part of EXPLAIN. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] missing entry in GucSource_Names
Jeff Davis wrote: It appears that the patch here: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=a30fa4ca33d055c46bebc0e5c701d5b4fd27814d missed adding PGC_S_DATABASE_USER to a few locations, most notably GucSource_Names, where the PGC_S_SESSION now points off the end of the array. Woops! applied, thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] COPY enhancements
Emmanuel Cecchet m...@frogthinker.org writes: - speed with error logging best effort: no use of sub-transactions but errors that can safely be trapped with pg_try/catch (no index violation, There aren't any. You can *not* put a try/catch around arbitrary code without a subtransaction. Don't even think about it. 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] EvalPlanQual seems a tad broken
Tom Lane t...@sss.pgh.pa.us wrote: 5. Now commit in the second session. First session resumes and prints f1 | f2 | f3 | f4 +++- 1 | 1 | 1 | 111 1 | 1 | 1 | 112 2 | 42 | 2 | 113 2 | 42 | 2 | 114 2 | 42 | 2 | 113 2 | 42 | 2 | 114 (6 rows) Of course the expected answer is f1 | f2 | f3 | f4 +++- 1 | 1 | 1 | 111 1 | 1 | 1 | 112 2 | 42 | 2 | 113 2 | 42 | 2 | 114 (4 rows) which is what you'll get if you simply repeat the test query. Is this related to issue 4593? (SELECT FOR UPDATE can return results in a sequence inconsistent with actual result rows and the ORDER BY clause -- rows are ordered by the pre-UPDATE values, while the results show the post-UPDATE values.) http://archives.postgresql.org/pgsql-bugs/2009-01/msg00017.php On the face of it, it seems very similar. Will the patch address this anomaly of SELECT FOR UPDATE, too? -Kevin -- 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] EvalPlanQual seems a tad broken
Kevin Grittner kevin.gritt...@wicourts.gov writes: Is this related to issue 4593? No, not directly. Now that locking is done in a separate plan node, we could think about addressing #4593 by switching the order of the LockRows and Sort plan nodes, but that has nothing to do with how well EvalPlanQual works. I was planning to start a separate thread discussing whether to do that, but it's a bit moot until we have a fix for EvalPlanQual --- at least one of the possible answers is to throw out EvalPlanQual altogether and do something else, in which case LockRows might go away again. 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] Client application name
A useful feature found in other DBMSs such as MS SQL Server that has been requested on these lists a few times, is the ability for a client application to report its name to the server. This information may then be presented in logs, activity reports and so on to aid debugging and help the sysadmin/DBA see easily what activity is coming from where on their server. The attached patch is a first quick cut of the basic functionality to do this. Currently, it makes the following changes: - Adds a new userset GUC called application_name. - Modifies psql to set application_name to 'psql' following connection to an 8.5 or above server. - Adds the application_name value to the CSV log output. - Adds a new parameter %a to the log line prefix which is replaced by the application_name value. - Reports the application_name with the other session stats in shared memory. - Adds application_name to the pg_stat_activity view and pg_stat_get_activity(int) function. Work to be done: - Docs - libpq modifications - Set the application_name in pg_dump and pals. My questions to the group are: - Is my approach reasonable? - What interface should I include in libpq? On the second question, obviously the user can call SET to set a value, as I've done for now in psql, however in other DBMSs, it may be set in the connection string. My feeling would be to do that, and possibly add PQsetApplicationName(PGconn *conn, char *name) and char *PQapplicationName(PGconn *conn);. What do others think? (Yes, I know I should technically discuss then code, but I was going to do this as a pet project to dabble in the server code which I don't do nearly often enough and figured I'd just send a WIP :-p ). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com appname-v1.diff Description: Binary data -- 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] Client application name
Dave Page wrote: The attached patch is a first quick cut of the basic functionality to do this. Currently, it makes the following changes: Couple of thoughts, - should we use argv[0] automatically in libpq unless overridden? - should we reject funny chars such as \n? (hopefully \0 won't be a problem) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Client application name
Dave Page wrote: My questions to the group are: - Is my approach reasonable? - What interface should I include in libpq? On the second question, obviously the user can call SET to set a value, as I've done for now in psql, however in other DBMSs, it may be set in the connection string. My feeling would be to do that, and possibly add PQsetApplicationName(PGconn *conn, char *name) and char *PQapplicationName(PGconn *conn);. What do others think? Doing it with a GUC will not be nearly so useful as having it in the wire protocol, IMNSHO. Just one example: it wouldn't be present in connection records, because it wouldn't be set yet. cheers andrew -- 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] Client application name
On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Dave Page wrote: The attached patch is a first quick cut of the basic functionality to do this. Currently, it makes the following changes: Couple of thoughts, - should we use argv[0] automatically in libpq unless overridden? How can I get to it from libpq? I could use getprogname() if present. - should we reject funny chars such as \n? (hopefully \0 won't be a problem) Is there any need? I can't see that it would do any harm other than maybe messing up some query output - and the solution would be 'don't do that then' :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Client application name
On Tue, Oct 13, 2009 at 4:11 PM, Andrew Dunstan and...@dunslane.net wrote: Doing it with a GUC will not be nearly so useful as having it in the wire protocol, IMNSHO. Just one example: it wouldn't be present in connection records, because it wouldn't be set yet. I quite like the flexibility of being able to set/show a GUC at any time, but you raise a good point. I'll need to venture into previously unknown territory (for me at least :-p) to figure out how best to do that, and if possible keep the GUC... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Client application name
Dave Page wrote: On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: - should we reject funny chars such as \n? (hopefully \0 won't be a problem) Is there any need? I can't see that it would do any harm other than maybe messing up some query output - and the solution would be 'don't do that then' :-) I worry about log_line_prefix expansion with an unexpected newline. I'm not sure don't do that is a good enough answer because you might be dealing with uncooperative application writers :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Client application name
Dave Page wrote:
On Tue, Oct 13, 2009 at 4:11 PM, Andrew Dunstan and...@dunslane.net wrote:
Doing it with a GUC will not be nearly so useful as having it in the wire
protocol, IMNSHO. Just one example: it wouldn't be present in connection
records, because it wouldn't be set yet.
I quite like the flexibility of being able to set/show a GUC at any
time, but you raise a good point. I'll need to venture into previously
unknown territory (for me at least :-p) to figure out how best to do
that, and if possible keep the GUC...
From time to time people ask for scalar variable facility. ISTM what
you're trying to do is just a special case of that. Maybe we could
approach it by providing a builtin (and non-removable)
custom_variable_classes entry ('pg_variables'?). Then you could have
clients safely do:
set pg_variables.client_name = 'blurfl';
And I'm sure other people would find interesting uses for such a gadget.
cheers
andrew
--
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] Buffer usage in EXPLAIN and pg_stat_statements (review)
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Here is an update version of buffer usage patch. I started to look at this patch, and I have a few comments: 1. I was expecting this patch to get rid of ShowBufferUsage() and friends altogether, instead of adding yet more static counters to support them. Isn't that stuff pretty well superseded by having EXPLAIN support? 2. I do not understand the stuff with propagating counts into the top instrumentation node. That seems like it's going to double-count those counts. In any case it is 100% inconsistent to propagate only buffer counts that way and not any other resource usage. I think you should drop the TopInstrument variable and the logic that propagates counts up. 3. I don't believe that you've sufficiently considered the problem of restoring the previous value of CurrentInstrument after an error. It is not at all adequate to do it in postgres.c; consider subtransactions for example. However, so far as I can see that variable is useless anyway. Couldn't you just drop both that and the prev link? (If you keep TopInstrument then the same objection applies to it.) 4. I don't believe this counting scheme works, except in the special case where all buffer access happens in leaf plan nodes (which might be enough if it weren't for Sort, Materialize, Hash, etc). It looks to me like counts will be transferred into the instrumentation node for the next plan node to stop execution, which could be a descendant of the node that really ought to get charged. You could deal with #4 by having the low-level I/O routines accumulate counts directly into *CurrentInstrument and not have static I/O counters at all, but then you'd have to contend with fixing #3 properly instead of just eliminating that global variable. It might be better to add a start field to struct Instrumentation for each counter, and do something like this: * StartNode copies static counter into start field * StopNode computes delta = static counter - start field, then adds delta to node's count and resets counter to start The reason for the reset is so that the I/O isn't double counted by parent nodes. If you wanted buffer I/O to be charged to the node causing it *and* to all parent nodes, which would be more consistent with the way we charge CPU time, then don't do the reset. Offhand though that seems to me like it'd be more surprising than useful. 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] Client application name
On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan and...@dunslane.net wrote:
From time to time people ask for scalar variable facility. ISTM what
you're trying to do is just a special case of that. Maybe we could approach
it by providing a builtin (and non-removable) custom_variable_classes entry
('pg_variables'?). Then you could have clients safely do:
set pg_variables.client_name = 'blurfl';
And I'm sure other people would find interesting uses for such a gadget.
I'm not sure that's really related to this - for example, we wouldn't
want to push everything in the custom class through the logger or into
per-backend shared memory, which would mean special-casing this
particular variable for which doing those things is the primary use
case.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
--
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] Client application name
Dave Page dp...@pgadmin.org writes: - Is my approach reasonable? - What interface should I include in libpq? I thought the plan was to have libpq look at an environment variable, compare PGCLIENTENCODING for example. I'm not convinced psql should be involved in the logic at all --- if it is, there definitely must be a way for scripts to override the psql value. In general the place that is most reasonable to set the value might be several software levels up from libpq, which is what makes the environment-variable approach attractive. 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] Client application name
Dave Page dp...@pgadmin.org writes: On Tue, Oct 13, 2009 at 4:06 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: - should we use argv[0] automatically in libpq unless overridden? How can I get to it from libpq? You can't. 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] Client application name
Dave Page dp...@pgadmin.org writes: On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan and...@dunslane.net wrote: From time to time people ask for scalar variable facility. I'm not sure that's really related to this It isn't; I think Andrew confused this thread with the one where someone wanted to know about trigger context. 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] Client application name
On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: - Is my approach reasonable? - What interface should I include in libpq? I thought the plan was to have libpq look at an environment variable, I wasn't aware we had a plan :-) compare PGCLIENTENCODING for example. I'm not convinced psql should be involved in the logic at all --- if it is, there definitely must be a way for scripts to override the psql value. In general the place that is most reasonable to set the value might be several software levels up from libpq, which is what makes the environment-variable approach attractive. The current implementation just has psql do SET application_name = 'psql'; immediately following connection to setup a sensible default. That can be overridden at any time with another SET. I can have libpq look at the environment as it does for PGCLIENTENCODING, but I'd certainly like to be able to use the connection string as well, as environment variables are not really the first choice of a Windows programmer for such things. I'm not sure psql should be looking directly at the environment though should it? Or would you envisage it only SETing application_name itself, if libpq didn't already have a value from elsewhere? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Client application name
Tom Lane wrote: Dave Page dp...@pgadmin.org writes: On Tue, Oct 13, 2009 at 4:34 PM, Andrew Dunstan and...@dunslane.net wrote: From time to time people ask for scalar variable facility. I'm not sure that's really related to this It isn't; I think Andrew confused this thread with the one where someone wanted to know about trigger context. I didn't but I'm multitasking and should stop. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Wire protocol docs
On http://www.postgresql.org/docs/8.4/interactive/protocol.html we say: Higher level features built on this protocol (for example, how libpq passes certain environment variables when the connection is established) are covered elsewhere. I cannot find anything that is obviously 'elsewhere' in the docs - does that need fixing, or do my searching skills need improving? *starts reading source code* :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Client application name
On Tue, 13 Oct 2009, Dave Page wrote: A useful feature found in other DBMSs such as MS SQL Server that has been requested on these lists a few times, is the ability for a client application to report its name to the server. This information may then be presented in logs, activity reports and so on to aid debugging and help the sysadmin/DBA see easily what activity is coming from where on their server. As a point of reference the JDBC API specifies the following which allows multiple properties that are similar to the desired request which are useful in a multi-tier application. http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#setClientInfo(java.lang.String,%20java.lang.String) Kris Jurka -- 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] Client application name
I can have libpq look at the environment as it does for PGCLIENTENCODING, but I'd certainly like to be able to use the connection string as well, as environment variables are not really the another challenge with the Environment variable: they are (at least on windows) usually set for one logged on user. And ususally I have pg_admin, python for development, psql and my application all connected to the same PostgreSQL server. I would love to see 4 different application names, and not the value of one environment-variable. that argv[0] that was somewhere in this thread looked nice :) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality
Re: [HACKERS] COPY enhancements
Tom Lane wrote: Emmanuel Cecchet m...@frogthinker.org writes: - speed with error logging best effort: no use of sub-transactions but errors that can safely be trapped with pg_try/catch (no index violation, There aren't any. You can *not* put a try/catch around arbitrary code without a subtransaction. Don't even think about it. Well then why the tests provided with the patch are working? I hear you when you say that it is not a generally applicable idea, but it seems that at least a couple of errors can be trapped with this mechanism. Emmanuel -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com -- 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] COPY enhancements
Emmanuel Cecchet m...@frogthinker.org writes: Tom was also suggesting 'refactoring COPY into a series of steps that the user can control'. What would these steps be? Would that be per row and allow to discard a bad tuple? The idea is to have COPY usable from a general SELECT query so that the user control what happens. Think of an SRF returning bytea[] or some variation on the theme. Maybe WITH to the rescue: WITH csv AS ( -- no error here as the destination table is in memory tuple store, -- assuming we have adunstan patch to ignore rows with too few or -- too many columns COPY csv(a, b, c, d) FROM STDIN WITH CSV HEADER --- and said options ) INSERT INTO destination SELECT a, b, f(a + b - d), strange_timestamp_reader(c) FROM csv WHERE validity_check_passes(a, b, c, d); That offers complete control to the user about the stages that transform the data. In a previous thread some ideas I forgot the details offered to the users some more control, but I don't have the time right now to search in the archives. Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte -- 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] Wire protocol docs
At 2009-10-13 17:25:15 +0100, dp...@pgadmin.org wrote: I cannot find anything that is obviously 'elsewhere' in the docs - does that need fixing, or do my searching skills need improving? I don't know, but… *starts reading source code* :-) Look at what fe-protocol3.c:build_startup_packet() does with its options argument (and see fe-connect.c:EnvironmentOptions to see what is passed to it). Basically, libpq sets some connection parameters to values taken from the environment (e.g. client_encoding from PGCLIENTENCODING). What the documentation you quoted is saying is that the wire protocol doesn't know or care where the values came from. -- ams -- 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] Wire protocol docs
On Tue, Oct 13, 2009 at 5:41 PM, Abhijit Menon-Sen a...@toroid.org wrote: At 2009-10-13 17:25:15 +0100, dp...@pgadmin.org wrote: I cannot find anything that is obviously 'elsewhere' in the docs - does that need fixing, or do my searching skills need improving? I don't know, but… *starts reading source code* :-) Look at what fe-protocol3.c:build_startup_packet() does with its options argument (and see fe-connect.c:EnvironmentOptions to see what is passed to it). Basically, libpq sets some connection parameters to values taken from the environment (e.g. client_encoding from PGCLIENTENCODING). Yeah, grep found that pretty quickly :-) What the documentation you quoted is saying is that the wire protocol doesn't know or care where the values came from. Right. My complaint though, is that the docs imply that the info on how those values get set is in the docs somewhere, which appears to be incorrect. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Client application name
On Tuesday 13 October 2009 18:30:54 Massa, Harald Armin wrote: I can have libpq look at the environment as it does for PGCLIENTENCODING, but I'd certainly like to be able to use the connection string as well, as environment variables are not really the another challenge with the Environment variable: they are (at least on windows) usually set for one logged on user. And ususally I have pg_admin, python for development, psql and my application all connected to the same PostgreSQL server. I would love to see 4 different application names, and not the value of one environment-variable. that argv[0] that was somewhere in this thread looked nice :) Well, those applications could set it themselves... Andres -- 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] Client application name
On Tue, Oct 13, 2009 at 12:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: - Is my approach reasonable? - What interface should I include in libpq? I thought the plan was to have libpq look at an environment variable, compare PGCLIENTENCODING for example. I'm not convinced psql should be involved in the logic at all --- if it is, there definitely must be a way for scripts to override the psql value. In general the place that is most reasonable to set the value might be several software levels up from libpq, which is what makes the environment-variable approach attractive. What happens if we want to change the application name after the fact? Consider the case where there is a connection pooler between the database and application, for example. ...Robert -- 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] COPY enhancements
Emmanuel Cecchet m...@asterdata.com writes: Tom Lane wrote: There aren't any. You can *not* put a try/catch around arbitrary code without a subtransaction. Don't even think about it. Well then why the tests provided with the patch are working? Because they carefully exercise only a tiny fraction of the system. The key word in my sentence above is arbitrary. You don't know what a datatype input function might try to do, let alone triggers or other functions that COPY might have to invoke. They might do things that need to be cleaned up after, and subtransaction rollback is the only mechanism we have for 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
Re: [HACKERS] Client application name
Robert Haas robertmh...@gmail.com writes: What happens if we want to change the application name after the fact? As long as it's a GUC variable you can just do SET. I think the point of discussion is what is the best convention for getting it set initially. 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] Client application name
On Tue, Oct 13, 2009 at 11:55 AM, Robert Haas robertmh...@gmail.com wrote: What happens if we want to change the application name after the fact? Consider the case where there is a connection pooler between the database and application, for example. good point... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Wire protocol docs
On Tue, 13 Oct 2009, Dave Page wrote: On http://www.postgresql.org/docs/8.4/interactive/protocol.html we say: Higher level features built on this protocol (for example, how libpq passes certain environment variables when the connection is established) are covered elsewhere. http://www.postgresql.org/docs/8.4/static/libpq-envars.html and http://www.postgresql.org/docs/8.4/static/libpq-connect.html are what I think it's alluding to here. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] SQL Standard Committee
Peter, The current status is that no one at INCITS is replying to my emails. If someone preferably in the right time zone is interested in phoning them up or pursuing other means of getting an answer out of them, please let me know privately. I can't give this the attention it apparently needs right now. Can you send me the contact details? I'm sure we can recruit a volunteer. --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] Wire protocol docs
Dave Page dp...@pgadmin.org writes: Right. My complaint though, is that the docs imply that the info on how those values get set is in the docs somewhere, which appears to be incorrect. The libpq documentation does cover the fact that libpq uses those variables to establish initial settings. I wouldn't expect it to go into implementation details, would you? 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] Client application name
Dave Page dp...@pgadmin.org writes: On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: - Is my approach reasonable? I thought the plan was to have libpq look at an environment variable, I wasn't aware we had a plan :-) There was some previous discussion of this, which I am too lazy to look up but I thought we had sketched out an API. 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] transaction_isolation vs. default_transaction_isolation
Yeah, they basically have semantics specified by the SQL standard that are not compatible with anything else in GUC land. They are more like SET LOCAL settings, but again not quite. Mind you, transaction_isolation and transaction_read_only aren't documented anywhere in our docs *as settings*, even though they show up in pg_settings. Doc patch coming ... --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] Wire protocol docs
On Tue, Oct 13, 2009 at 6:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: Right. My complaint though, is that the docs imply that the info on how those values get set is in the docs somewhere, which appears to be incorrect. The libpq documentation does cover the fact that libpq uses those variables to establish initial settings. I wouldn't expect it to go into implementation details, would you? Not the libpq docs, no. I was expecting something to though, having been told in the intro to the low level protocol details that: Higher level features built on this protocol (for example, how libpq passes certain environment variables when the connection is established) are covered elsewhere. Note that it says *how* libpq passes those variables which implies the mechanism of passing them to the server. Anyway, I can figure out what I need from the existing code - I just found that sentence misleading and think it should probably be removed. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Client application name
On Tue, Oct 13, 2009 at 6:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Tue, Oct 13, 2009 at 5:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: - Is my approach reasonable? I thought the plan was to have libpq look at an environment variable, I wasn't aware we had a plan :-) There was some previous discussion of this, which I am too lazy to look up but I thought we had sketched out an API. This seems to be the recent one, which is probably what was in the back of my mind when I decided to spend some spare time on this project: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01008.php There's no specific API discussion in either of the referenced threads though - just some vague hand waving and opining about what it should do and how it should look (pretty much all of which is covered by my patch). Oh, and apologies to Jaime who I just noticed had volunteered to work on this :-( -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Tue, 2009-10-13 at 09:14 -0500, Kevin Grittner wrote: Now that we can generate EXPLAIN output in more structured formats, perhaps we could think about adding an extremely verbose mode where the planner would think out loud as a whole separate section from where we show the chosen plan? Tom Raney did that a while back: http://archives.postgresql.org/pgsql-patches/2008-07/msg00011.php He also had an accompanying visual tool to navigate the output in a meaningful way. If he has moved on to other projects, it would be great if someone could pick it up. Regards, Jeff Davis -- 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Tue, 2009-10-13 at 11:26 -0700, Jeff Davis wrote: On Tue, 2009-10-13 at 09:14 -0500, Kevin Grittner wrote: Now that we can generate EXPLAIN output in more structured formats, perhaps we could think about adding an extremely verbose mode where the planner would think out loud as a whole separate section from where we show the chosen plan? Tom Raney did that a while back: http://archives.postgresql.org/pgsql-patches/2008-07/msg00011.php He also had an accompanying visual tool to navigate the output in a meaningful way. If he has moved on to other projects, it would be great if someone could pick it up. No kidding. It was a very cool project. Here is a video of a presentation he did at West 2008: http://www.vimeo.com/4101141 Joshua D. Drake Regards, Jeff Davis -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Tue, Oct 13, 2009 at 10:14 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: I sometimes want to know what the planner thinks the cost of some plan other than the one actually selected would be. Another DBMS I used for years had a way to turn on an *extremely* verbose mode for their planner; it showed everything it considered with its related cost information. Even a moderately complex query generated hundreds or thousands of lines of output, so I rarely used it; but for those particularly stubborn queries, where you just can't understand why it's picking the plan it is, a little work wading through the output would *always* clear up the mystery. Now that we can generate EXPLAIN output in more structured formats, perhaps we could think about adding an extremely verbose mode where the planner would think out loud as a whole separate section from where we show the chosen plan? I wouldn't object to such a thing, but for simple cases I think it would be more convenient to modify the planner's assumptions and then try replanning. An exhaustive dump of everything the planner has considered is going to be a LOT of data, and I don't really want to have to set up a graphical visualization tool every time I have a planning question. I am a command-line kind of guy... ...Robert -- 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] Client application name
On Tue, Oct 13, 2009 at 1:07 PM, Dave Page dp...@pgadmin.org wrote: Oh, and apologies to Jaime who I just noticed had volunteered to work on this :-( never mind... i get blocked for the ugliness of the libpq api connect functions... and my first attempt to solve that was seriously broken... besides, as Robert mention, because of pooler connections using a GUC is more appropiate... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Client application name
On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Tue, Oct 13, 2009 at 1:07 PM, Dave Page dp...@pgadmin.org wrote: Oh, and apologies to Jaime who I just noticed had volunteered to work on this :-( never mind... i get blocked for the ugliness of the libpq api connect functions... and my first attempt to solve that was seriously broken... Funny - I reverted my first attempt at that bit too. besides, as Robert mention, because of pooler connections using a GUC is more appropiate... I'd like both options to be available to the programmer. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Unicode UTF-8 table formatting for psql text output
Roger Leigh rle...@codelibre.net writes: The attached updated patch renames all user-visible uses of utf8 to unicode. It also updates the documentation regarding locale to psql client character set encoding so the docs now match the code exactly. Applied with light editorialization. The main non-cosmetic change I made was to postpone selection of default line_style until runtime (see get_line_style() in the committed patch). The original coding required knowledge of the line_style default rule not only in three different places in psql, but in every other place using print.c, such as createlang/droplang -l (which dumped core with the patch as submitted). I changed it so that leaving line_style NULL implies the default encoding-driven behavior, so that we don't need to touch any of the callers. 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] Client application name
Dave Page dp...@pgadmin.org writes: On Tue, Oct 13, 2009 at 9:13 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: besides, as Robert mention, because of pooler connections using a GUC is more appropiate... I'd like both options to be available to the programmer. We have several things already that can be fed either from an environment variable or an option in the connection string. Is there any compelling reason why those two mechanisms aren't adequate for this? 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] Re: [GENERAL] contrib/plantuner - enable PostgreSQL planner hints
On Tue, 13 Oct 2009, Robert Haas wrote: An exhaustive dump of everything the planner has considered is going to be a LOT of data, and I don't really want to have to set up a graphical visualization tool every time I have a planning question. I am a command-line kind of guy... Wouldn't this be easy enough to cope with in a scripting language though? If the planner produces the comprehensive report via something like XML, that moves the problem of how to best present that into user space, where I think it belongs at least at first. I'm sure someone can produce an example program in Perl or Python that produces a fairly collapsed tree via command line and then allows expanding on bits you want more detail on. That's the sort of development you can easily get people to do, as opposed to the dreary details of exporting the detail in the first place. Get the full report out there, and I'm sure we can produce terse ones in user-space; once that's nailed down and explored, maybe then it's appropriate to talk about how to provide squished versions directly. As already pointed out, some people are never going to be satisfied with anything other than the most detail possible, so you might as well start with that if the simpler views can be derived from them. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Triggers on columns
On Sat, 2009-10-10 at 00:04 +0300, Peter Eisentraut wrote:
On Mon, 2009-09-14 at 18:58 +0900, Itagaki Takahiro wrote:
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote:
Ok, the attached patch implements standard-compliant version of
column trigger.
Here is an updated version of column-level trigger patch.
I forgot to adjust pg_get_triggerdef() in the previous version.
pg_dump also uses pg_get_triggerdef() instead of building
CREATE TRIGGER statements to avoid duplicated codes if the
server version is 8.5 or later.
I have committed the parts involving pg_get_triggerdef and pg_dump. I
will get to the actual column trigger functionality next.
Attached is a merged up patch with some slightly improved documentation.
I think the patch is almost ready now. One remaining issue is, in
TriggerEnabled() you apparently check the column list only if it is a
row trigger. But columns are supported for statement triggers as well
per SQL standard. Check please.
Btw., I might not get a chance to commit this within the next 48 hours.
If someone else wants to, go ahead.
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 6185a7d..f656cbf 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -122,6 +122,16 @@ CREATE TRIGGER replaceable class=PARAMETERname/replaceable { BEFORE | AFTE
this specifies the event that will fire the trigger. Multiple
events can be specified using literalOR/literal.
/para
+
+ para
+ For commandUPDATE/command triggers, it is possible to
+ specify a list of columns using this syntax:
+synopsis
+UPDATE OF replaceablecolname1/replaceable [, replaceablecolname2/replaceable, ...]
+/synopsis
+ The trigger will only fire if at least one of the listed columns
+ is mentioned as a target of the update.
+ /para
/listitem
/varlistentry
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index 36bf050..ed956fe 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -36,10 +36,13 @@
performed. Triggers can be defined to execute either before or after any
commandINSERT/command, commandUPDATE/command, or
commandDELETE/command operation, either once per modified row,
-or once per acronymSQL/acronym statement. Triggers can also fire
-for commandTRUNCATE/command statements. If a trigger event occurs,
-the trigger's function is called at the appropriate time to handle the
-event.
+or once per acronymSQL/acronym
+statement. commandUPDATE/command triggers can moreover be set
+to only fire if certain columns are mentioned in
+the literalSET/literal clause of the commandUPDATE/command
+statement. Triggers can also fire for commandTRUNCATE/command
+statements. If a trigger event occurs, the trigger's function is
+called at the appropriate time to handle the event.
/para
para
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 790cbdc..1af0c61 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -30,8 +30,11 @@
#include executor/executor.h
#include executor/instrument.h
#include miscadmin.h
+#include nodes/bitmapset.h
#include nodes/makefuncs.h
#include parser/parse_func.h
+#include parser/parse_relation.h
+#include parser/parsetree.h
#include pgstat.h
#include storage/bufmgr.h
#include tcop/utility.h
@@ -66,7 +69,8 @@ static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata,
MemoryContext per_tuple_context);
static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event,
bool row_trigger, HeapTuple oldtup, HeapTuple newtup,
- List *recheckIndexes);
+ List *recheckIndexes, Bitmapset *modifiedCols);
+static bool TriggerEnabled(Trigger *trigger, Bitmapset *modifiedCols);
/*
@@ -98,6 +102,8 @@ CreateTrigger(CreateTrigStmt *stmt,
bool checkPermissions)
{
int16 tgtype;
+ int ncolumns;
+ int2 *columns;
int2vector *tgattr;
Datum values[Natts_pg_trigger];
bool nulls[Natts_pg_trigger];
@@ -337,8 +343,39 @@ CreateTrigger(CreateTrigStmt *stmt,
CStringGetDatum());
}
- /* tgattr is currently always a zero-length array */
- tgattr = buildint2vector(NULL, 0);
+ /* build column references for UPDATE OF */
+ ncolumns = list_length(stmt-columns);
+ if (ncolumns == 0)
+ columns = NULL;
+ else
+ {
+ ListCell *cell;
+ int x = 0;
+
+ columns = (int2 *) palloc(ncolumns * sizeof(int2));
+
+ foreach (cell, stmt-columns)
+ {
+ char *name = strVal(lfirst(cell));
+ int attnum;
+ int y;
+
+ /* Lookup column name. System columns are not allowed. */
+ attnum = attnameAttNum(rel, name, false);
+
+ /* Check for duplicates */
+ for (y = x - 1; y = 0; y--)
+ {
+if (columns[y] == attnum)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_COLUMN),
+ errmsg(column \%s\ specified more
Re: [HACKERS] [PATCH] Largeobject access controls
KaiGai Kohei kai...@ak.jp.nec.com writes: The attached patch is the revised one for largeobejct access controls, because it conflicted to the GRANT/REVOKE ON ALL xxx IN SCHEMA. I started to look through this patch with the hope of committing it, but found out that it's not really ready. The most serious problem is that you ripped out myLargeObjectExists(), apparently because you didn't understand what it's there for. The reason it's there is to ensure that pg_dump runs don't fail. pg_dump is expected to produce a consistent dump of all large objects that existed at the time of its transaction snapshot. With this code, pg_dump would get a large object doesn't exist error on any LO that is deleted between the time of the snapshot and the time pg_dump actually tries to dump it --- which could be quite a large window in a large database. The reason this is a significant problem and not just an easily fixable oversight is that it's not entirely clear what to do instead. We can certainly make the pure existence test use the query snapshot instead of SnapshotNow, but what about the implied permissions tests? Should we attempt to make them run using the version of the LO's ACL found in the query-snapshot-time metadata row? The trouble with that is it might refer to roles that don't exist anymore, perhaps resulting in failures down inside the ACL checking routines. It would be safer to rely on the current metadata row contents, but then we have the question of whether to allow the access if the row doesn't exist according to SnapshotNow. Now these issues arise to some extent already in pg_dump, but the current window for failure is quite short because it obtains access share locks on all the tables it will dump at the start of the run. With large objects the window in which things could have changed is very much longer. Of course, in the cases that people are most concerned about, pg_dump is running as superuser and so the actual ACL contents don't really matter anyway, so long as we don't fail outright before getting to the check. So I'm kind of inclined to say that the least evil solution is to apply the permissions check using the query-snapshot-time metadata row. It's definitely a debatable question though. We'd also want to make sure that the aclcheck code doesn't fail if it finds a nonexistent role ID in the ACL. Moving on to lesser but still significant problems, you probably already guessed my next one: there's no pg_dump support. If the system tracks owner and ACL for large objects, pg_dump *must* be prepared to dump that information. It'd also be worthwhile to teach pg_dump that in servers = 8.5, it can look in the metadata catalog to make the list of LO OIDs instead of having to do a SELECT DISTINCT from pg_largeobject. I notice that the patch decides to change the pg_description classoid for LO comments from pg_largeobject's OID to pg_largeobject_metadata's. This will break existing clients that look at pg_description (eg, pg_dump and psql, plus any other clients that have any intelligence about comments, for instance it probably breaks pgAdmin). And there's just not a lot of return that I can see. I agree that using pg_largeobject_metadata would be more consistent given the new catalog layout, but I'm inclined to think we should stick to the old convention on compatibility grounds. Given that choice, for consistency we'd better also use pg_largeobject's OID not pg_largeobject_metadata's in pg_shdepend entries for LOs. In the category of lesser issues that have still got to be fixed: * You need to pay more attention to updating comments. For example your changes to LargeObjectCreate render its header comment a complete lie, but you didn't change the comment. (And what is the purpose of renaming it to CreateLargeObject, and similarly for the adjacent routines? You didn't change the API meaningfully, so there is no reason to break calling code that way.) * The documentation needs work too, eg a new system catalog requires a page in catalogs.sgml, and I'll bet there's a few references to large objects and/or permissions that need to be updated. * largeobject is not an English word. The occurrences in user-visible messages and documentation must get changed to large object. I've got mixed emotions even about using it in code identifiers, although we certainly aren't going to rename pg_largeobject, so anything that's named in parallel to that should stay as-is. In the same vein, acl is not a word we want to expose to users, so largeobject_check_acl is doubly bad as a GUC variable name. Perhaps large_object_privilege_checks would do. * I'm not really happy with the ac_largeobject_foo shim layer, and would frankly prefer to rip it out and put those tests inline. It's poorly thought out IMO --- eg, what the heck is that cascade boolean --- and doesn't look like any of the rest of the Postgres code stylistically, and it makes the calling code look different from similar
Re: [HACKERS] [PATCH] Largeobject access controls
Tom Lane wrote: KaiGai Kohei kai...@ak.jp.nec.com writes: The attached patch is the revised one for largeobejct access controls, because it conflicted to the GRANT/REVOKE ON ALL xxx IN SCHEMA. I started to look through this patch with the hope of committing it, but found out that it's not really ready. The most serious problem is that you ripped out myLargeObjectExists(), apparently because you didn't understand what it's there for. The reason it's there is to ensure that pg_dump runs don't fail. pg_dump is expected to produce a consistent dump of all large objects that existed at the time of its transaction snapshot. With this code, pg_dump would get a large object doesn't exist error on any LO that is deleted between the time of the snapshot and the time pg_dump actually tries to dump it --- which could be quite a large window in a large database. The reason this is a significant problem and not just an easily fixable oversight is that it's not entirely clear what to do instead. We can certainly make the pure existence test use the query snapshot instead of SnapshotNow, but what about the implied permissions tests? Should we attempt to make them run using the version of the LO's ACL found in the query-snapshot-time metadata row? The trouble with that is it might refer to roles that don't exist anymore, perhaps resulting in failures down inside the ACL checking routines. It would be safer to rely on the current metadata row contents, but then we have the question of whether to allow the access if the row doesn't exist according to SnapshotNow. Now these issues arise to some extent already in pg_dump, but the current window for failure is quite short because it obtains access share locks on all the tables it will dump at the start of the run. With large objects the window in which things could have changed is very much longer. Of course, in the cases that people are most concerned about, pg_dump is running as superuser and so the actual ACL contents don't really matter anyway, so long as we don't fail outright before getting to the check. So I'm kind of inclined to say that the least evil solution is to apply the permissions check using the query-snapshot-time metadata row. It's definitely a debatable question though. We'd also want to make sure that the aclcheck code doesn't fail if it finds a nonexistent role ID in the ACL. The origin of this matter is the basis of existence was changed. Our current basis is whether pg_largeobject has one or more data chunk for the given loid in the correct snapshot, or not. The newer one is whether pg_largeobject_metadata has the entry for the given loid in the SnapshotNow, or not. The newer basis is same as other database objects, such as functions. But why pg_dump performs correctly for these database objects? In my understanding, because it reads the system catalog directly in the query snapshot. So, it will be visible, if concurrent transaction dropped a function to be backed up. Now, pg_dump uses libpq's large object interface which internally uses loread()/lowrite(). If pg_dump fetches data chunks from the system catalog, it seems to me the matter is reasonably solvable. My assumption is that you're not talking about a generic situation when a certain database object is unavailable even if we can find it within the system catalog, apart from large object backups. For example, we can easily produce a similar behavior when we tries to use a function which can be found in pg_proc, but concurrent transaction already removed it. I don't believe PostgreSQL guarantees equivalence between the visibility of system catalog and the availability of the related database object. So, is it the simplest approach to patch on the pg_dump? Moving on to lesser but still significant problems, you probably already guessed my next one: there's no pg_dump support. If the system tracks owner and ACL for large objects, pg_dump *must* be prepared to dump that information. It'd also be worthwhile to teach pg_dump that in servers = 8.5, it can look in the metadata catalog to make the list of LO OIDs instead of having to do a SELECT DISTINCT from pg_largeobject. Hmm. I planed to add support to the pg_dump next to the serve-side enhancement. If both of patches are necessary soon, I'll include it in this phase. I notice that the patch decides to change the pg_description classoid for LO comments from pg_largeobject's OID to pg_largeobject_metadata's. This will break existing clients that look at pg_description (eg, pg_dump and psql, plus any other clients that have any intelligence about comments, for instance it probably breaks pgAdmin). And there's just not a lot of return that I can see. I agree that using pg_largeobject_metadata would be more consistent given the new catalog layout, but I'm inclined to think we should stick to the old convention on compatibility grounds. Given that choice, for consistency
