Re: [HACKERS] Implementing incremental backup
On 20/06/2013 03:25, Tatsuo Ishii wrote: On Wed, Jun 19, 2013 at 8:40 PM, Tatsuo Ishii is...@postgresql.org wrote: On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost sfr...@snowman.net wrote: * Claudio Freire (klaussfre...@gmail.com) wrote: [...] The only bottleneck here, is WAL archiving. This assumes you can afford WAL archiving at least to a local filesystem, and that the WAL compressor is able to cope with WAL bandwidth. But I have no reason to think you'd be able to cope with dirty-map updates anyway if you were saturating the WAL compressor, as the compressor is more efficient on amortized cost per transaction than the dirty-map approach. Thank you for detailed explanation. I will think more about this. Just for the record, I was mulling over this idea since a bunch of month. I even talked about that with Dimitri Fontaine some weeks ago with some beers :) My idea came from a customer during a training explaining me the difference between differential and incremental backup in Oracle. My approach would have been to create a standalone tool (say pg_walaggregate) which takes a bunch of WAL from archives and merge them in a single big file, keeping only the very last version of each page after aggregating all their changes. The resulting file, aggregating all the changes from given WAL files would be the differential backup. A differential backup resulting from a bunch of WAL between W1 and Wn would help to recover much faster to the time of Wn than replaying all the WALs between W1 and Wn and saves a lot of space. I was hoping to find some time to dig around this idea, but as the subject rose here, then here are my 2¢! Cheers, -- Jehan-Guillaume (ioguix) de Rorthais -- 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] Allowing multi -t and adding -n to vacuumdb ?
On 01/03/2012 23:13, Tom Lane wrote: Jehan-Guillaume (ioguix) de Rorthais j...@dalibo.com writes: One of our customer send us a patch he wrote for his needs (on src/bin/scripts/vacuumdb.c, no doc were included). He's using one schema per application and would like to be able to run vacuumdb on each of them independently so he added the --schema|-n option and send us the patch. Reviewing his patch, I thought it would be more useful to allow multi --table|-t options on the command line first. It might be possible to pass an array of tables to vacuum_one_database function instead of just one. Then, we could add the --schema|-n option which would fetch and build the table list and call vacuum_one_database. But before I start writing this patch, I would like some opinion, pros / cons. Do you think such a feature could be accepted in official PostgreSQL code or should we keep this as an external script ? I think most of us see vacuumdb as a historical leftover. We keep it around in case anyone is still relying on it, but improving it seems like misdirected effort. Why isn't your customer using autovacuum? If there are concrete reasons why that doesn't get the job done for him, it would be more useful in the long run to work on fixing that. I usually advice to use both of them: vacuumdb and autovacuum (with tuning if needed). Autovacuum is launching its workers whenever tables need some maintenance. It is usually pretty discrete in most case, but not all of them (cf. Kevin Grittner email) and you cannot control when these workers are launched. As a DBA you know when you can afford some maintenances during the day/week/month WRT your db activity and plan them accordingly. Hence, running vacuumdb from crontab will just do autovacuum's job in some situation avoiding a random worker during the day. regards, tom lane -- Jehan-Guillaume (ioguix) de Rorthais http://www.dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Allowing multi -t and adding -n to vacuumdb ?
Hi, One of our customer send us a patch he wrote for his needs (on src/bin/scripts/vacuumdb.c, no doc were included). He's using one schema per application and would like to be able to run vacuumdb on each of them independently so he added the --schema|-n option and send us the patch. Reviewing his patch, I thought it would be more useful to allow multi --table|-t options on the command line first. It might be possible to pass an array of tables to vacuum_one_database function instead of just one. Then, we could add the --schema|-n option which would fetch and build the table list and call vacuum_one_database. But before I start writing this patch, I would like some opinion, pros / cons. Do you think such a feature could be accepted in official PostgreSQL code or should we keep this as an external script ? Thank you, -- Jehan-Guillaume (ioguix) de Rorthais http://www.dalibo.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] Google Summer of Code? Call for mentors.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, On 01-02-2012 20:59, Josh Berkus wrote: Hackers, The call is now open for Google Summer of Code. If you are interested in being a GSoC mentor this summer, please reply to this email. I want to gauge whether or not we should participate this summer. Yes, I'll be available to mentor a student on phpPgAdmin this year again. Thanks, - -- Jehan-Guillaume (ioguix) de Rorthais http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk9CC+4ACgkQXu9L1HbaT6KwBQCg3eEbJ22rEyuJA5AddS56H+ta fOoAnj9xecZ1fRUNFvuv40lgMfXbnfuI =ml12 -END PGP SIGNATURE- -- 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] GSoC 2011 - Mentors? Projects?
On 08/03/2011 07:44, Selena Deckelmann wrote: Hi! PostgreSQL is applying for GSoC again this year. We're looking for: * Mentors Ready to mentor again this year for phpPgAdmin * Project ideas Would you like to mentor? Please let me know! Our application closes on Friday, so please contact me *before* Friday. I've started a wiki page: http://wiki.postgresql.org/wiki/GSoC_2011 It's seeded with last year's todo lists and information. We need to add project ideas for students to it. I updated the page with last year PPA project The wiki pages for 2008 and 2010 are available, including links to the original student proposals: http://wiki.postgresql.org/wiki/GSoC_2010 http://wiki.postgresql.org/wiki/GSoC_2008 Thanks! -selena -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] some comments rewording in recovery.conf.sample about SR
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Hackers, I believe comments in the recovery.conf.sample are somehow confusing about standby and SR: # Edit this file to provide the parameters that PostgreSQL needs to # perform an archive recovery of a database, or to act as a log-streaming # replication standby. It seems incomplete. It doesn't recover OR act as a log-streaming replication standby. It can behave as a standby node without SR as well. # When standby_mode is enabled, the PostgreSQL server will work as # a standby. It tries to connect to the primary according to the # connection settings primary_conninfo, and receives XLOG records # continuously. This one is quite unclear as well. We might think that once we are in standby_mode we will be in SR. Again, we can use the standby_mode without primary_conninfo. This parameter only allows to keep the cluster in recovery, it doesn't imply to set up the SR. # By default, a standby server keeps streaming XLOG records from the # primary indefinitely. Same story... Please, find in attachment a proposal of rewording. Comments ? Regards, - -- Jehan-Guillaume (ioguix) de Rorthais www.dalibo.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0nBWAACgkQxWGfaAgowiJRlACfcXwFiGr1LlYuAPjwnOLIvoHl SEAAoJbtNqG4bk1+htYKd9KALKCv/wCt =q/x/ -END PGP SIGNATURE- -- 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] some comments rewording in recovery.conf.sample about SR
Obviously... PFA Le 07/01/2011 13:22, Jehan-Guillaume (ioguix) de Rorthais a écrit : Hello Hackers, I believe comments in the recovery.conf.sample are somehow confusing about standby and SR: # Edit this file to provide the parameters that PostgreSQL needs to # perform an archive recovery of a database, or to act as a log-streaming # replication standby. It seems incomplete. It doesn't recover OR act as a log-streaming replication standby. It can behave as a standby node without SR as well. # When standby_mode is enabled, the PostgreSQL server will work as # a standby. It tries to connect to the primary according to the # connection settings primary_conninfo, and receives XLOG records # continuously. This one is quite unclear as well. We might think that once we are in standby_mode we will be in SR. Again, we can use the standby_mode without primary_conninfo. This parameter only allows to keep the cluster in recovery, it doesn't imply to set up the SR. # By default, a standby server keeps streaming XLOG records from the # primary indefinitely. Same story... Please, find in attachment a proposal of rewording. Comments ? Regards, diff --git a/src/backend/access/transam/recovery.conf.sample b/src/backend/access/transam/recovery.conf.sample index 146ad03..9a68fb0 100644 --- a/src/backend/access/transam/recovery.conf.sample +++ b/src/backend/access/transam/recovery.conf.sample @@ -3,8 +3,8 @@ # --- # # Edit this file to provide the parameters that PostgreSQL needs to -# perform an archive recovery of a database, or to act as a log-streaming -# replication standby. +# perform an archive recovery of a database, or to act as a log replication +# standby, optionnaly streamed. # # If recovery.conf is present in the PostgreSQL data directory, it is # read on postmaster startup. After successful recovery, it is renamed @@ -88,21 +88,29 @@ # STANDBY SERVER PARAMETERS #--- # -# When standby_mode is enabled, the PostgreSQL server will work as -# a standby. It tries to connect to the primary according to the -# connection settings primary_conninfo, and receives XLOG records -# continuously. +# standby_mode +# +# When standby_mode is enabled, the PostgreSQL server will work as a +# standby. It will keep waiting for the next XLOG records, either using the +# restore_command and/or primary_conninfo depending on their setting, and +# never start as a primary server by itself. # #standby_mode = off # +# primary_conninfo +# +# When primary_conninfo is set, the PostgreSQL server tries to connect to +# the primary using this connection string and receives XLOG records +# continuously. +# #primary_conninfo = '' # e.g. 'host=localhost port=5432' # # -# By default, a standby server keeps streaming XLOG records from the -# primary indefinitely. If you want to stop streaming and finish recovery, +# By default, a standby server keeps restoring XLOG records from the primary +# indefinitely. If you want to stop the standby mode, finish recovery and # opening up the system in read/write mode, specify path to a trigger file. -# Server will poll the trigger file path periodically and stop streaming -# when it's found. +# Server will poll the trigger file path periodically and start as a primary +# server when it's found. # #trigger_file = '' # signature.asc Description: OpenPGP digital signature
Re: [HACKERS] some comments rewording in recovery.conf.sample about SR
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Le 07/01/2011 16:58, Robert Haas a écrit : On Fri, Jan 7, 2011 at 7:23 AM, Jehan-Guillaume (ioguix) de Rorthais iog...@free.fr wrote: PFA Committed, with some additional wordsmithing. Thank you ! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk0nS6sACgkQxWGfaAgowiL8ewCeNmThauDgdrJzp6npSSRCFroC q0cAn2gnjSYH6g4Oodvre6trADT01RON =6aSt -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Typo in plperl doc ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hey, While translating the plperl page from the manual, I found the following sentence: The varname%_SHARED/varname variable and other global state within the language is public data. Should it be : The varname%_SHARED/varname variable and other global variables within the language are public data. ? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwV3tYACgkQxWGfaAgowiJPFQCfaROauKGDLu0ODZXCbshb9dVe AfYAnAx+PvopgycLm7KPHdd6l8202vOm =Ky/1 -END PGP SIGNATURE- -- 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] Typo in plperl doc ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/06/2010 14:08, Robert Haas wrote: On Mon, Jun 14, 2010 at 3:48 AM, Jehan-Guillaume (ioguix) de Rorthais iog...@free.fr wrote: While translating the plperl page from the manual, I found the following sentence: The varname%_SHARED/varname variable and other global state within the language is public data. Should it be : The varname%_SHARED/varname variable and other global variables within the language are public data. ? State is a more general term, which would include any information that is globally accessible from within the interpreter, not just what's stored in variables. Off the top of my head, I'm not sure if there is anything like that, but I wouldn't bet on there not being any... Ok, so shouldn't it be The varname%_SHARED/varname variable and other global state(s?) within the language *are* public data ? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwWOhsACgkQxWGfaAgowiK1WwCfQbFGVIRW4PbJwdrV7fjRL8wt PiEAn3/NuXSGm5CjHtGzHREnl5DB+LBa =4IEb -END PGP SIGNATURE- -- 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] providing tokenized version of parsed SQL script
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 28/04/2010 02:20, Robert Haas wrote: On Tue, Apr 27, 2010 at 7:45 PM, Jehan-Guillaume (ioguix) de Rorthais iog...@free.fr wrote: I thought Michael was trying to write a tokenizer based on node tree returned by raw_parser. As it seems Michael is not even sure about what he's trying to do, I prefer refocus a bit this thread [...] Having dropped an eye here and there in the parser code, I am not sure where I could get required info and mix them to produce something close to my draft yet. But I prefer to discussing first before spending too much time and throwing any potential code after... I can't quite tell, from reading this, what you're trying to do with this... I would like to do what I described here: http://archives.postgresql.org/pgsql-hackers/2010-04/msg00974.php Pavel Stehule convinced me a function would be a better approach than a new SQL keyword. Moreover he points an existing function in the Oracle ecosystem that is doing very closely what I descibed in my draft: http://archives.postgresql.org/pgsql-hackers/2010-04/msg00976.php http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29#PLVlex Presently I am just studying some parts of the parser code to find where I could find : - token/lexer representation of a SQL script - type of each token Sorry the mess and confusion, hope this mail is clearer ? ...Robert - -- JGuillaume (ioguix) de Rorthais http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvX7aIACgkQxWGfaAgowiI5PQCeITOqAlCbeFyYc2KdHPUOfXhe YY8An0jdfF31SK75bmd21ixFffXiyoEF =ymbW -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] providing tokenized version of parsed SQL script (was: nodeToString format and exporting the SQL parser)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 25/04/2010 03:02, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Apr 24, 2010 at 8:07 PM, Bruce Momjian br...@momjian.us wrote: Sounds useful to me, though as a function like suggested in a later email. If tool-builders think this is useful, I have no problem with making it available. It should be suitably disclaimed: We reserve the right to rip out the entire flex/yacc-based lexer and parser at any time and replace them with a hand-coded system written in Prolog that emits tokenization information only in ASN.1-encoded pig latin. If massive changes in the way this function works - or its complete disappearance - are going to make you grumpy, don't call it. I'm a bit concerned with the vagueness of the goals here. We started with a request to dump out node trees, ie, post-parsing representation; but the example use case of syntax highlighting would find that representation quite useless. (Example: foo::bar and CAST(foo AS bar) yield the same parse tree.) Well, the tokenizer stuff was actually my understanding of the following quote from Michael Tharp : « ... making the internal SQL parser available to clients via a C-language SQL function. ». I thought Michael was trying to write a tokenizer based on node tree returned by raw_parser. As it seems Michael is not even sure about what he's trying to do, I prefer refocus a bit this thread A syntax highlighter might get some use out of the lexer-output token stream, but I'm afraid from the proposed output that people might be expecting more semantic information than the lexer can provide. The lexer doesn't, for example, have any clue that some keywords are commands and others aren't; nor any very clear understanding about the semantic difference between the tokens '=' and ';'. Exact, a proper tokenizer function should be able to give some (simple) information about the type of each token. That is what I tried to define in this draft with the type field : = SELECT pgtokenize($script$ SELECT 1; UPDATE test SET a=2; $script$); type | pos | value | line -+-+--+-- SQL_COMMAND | 1 | 'SELECT' | 1 CONSTANT| 8 | '1' | 1 DELIMITER | 9 | ';' | 1 SQL_COMMAND | 11 | 'UPDATE' | 2 IDENTIFIER | 18 | 'test' | 2 SQL_KEYWORD | 23 | 'SET'| 2 IDENTIFIER | 27 | 'a'| 2 OPERATOR| 30 | '=' | 2 CONSTANT| 31 | '1' | 2 Also, if all you want is the lexer, it's not that hard to steal psql's version and adapt it to your purposes. The lexer doesn't change very fast, and it's not that big either. Stealing the lexer from psql is possible...for C application. Don't know yet if we could port it to other languages easily and if a simple lexer would really answer the use cases here. Anyway, it certainly wouldn't be hard for an add-on module to provide a SRF that calls the lexer (or parser) and returns some sort of tabular representation of the results. I'm just not sure how useful it'll be in the real world. Well, I would prefer not to tell users of pgAdmin or phpPgAdmin that they depend on a contrib module. Moreover, PostgreSQL already expose a lot of informations about its internal mechanisms, configuration, ddl etc. I think having a proper tokenizer function is just a natural new functionality for core if possible. Having dropped an eye here and there in the parser code, I am not sure where I could get required info and mix them to produce something close to my draft yet. But I prefer to discussing first before spending too much time and throwing any potential code after... regards, tom lane - -- JGuillaume (ioguix) de Rorthais http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkvXdxgACgkQxWGfaAgowiJujQCglXpCYpFttwHOkmkCd92zMxnv r00An1sjmRrR6u61VjCtXputcNBevHsz =ri3i -END PGP SIGNATURE- -- 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] [RFC] nodeToString format and exporting the SQL parser
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 04/04/2010 18:10, David Fetter wrote:
On Sat, Apr 03, 2010 at 03:17:30PM +0200, Markus Schiltknecht wrote:
Hi,
Michael Tharp wrote:
I have been spending a little time making the internal SQL parser
available to clients via a C-language SQL function.
This sounds very much like one of the Cluster Features:
http://wiki.postgresql.org/wiki/ClusterFeatures#API_into_the_Parser_.2F_Parser_as_an_independent_module
Is this what you (or David) have in mind?
I'm not a fan of statement-based replication of any description. The
use cases I have in mind involve things like known-correct syntax
highlighting in text editors.
The point here is not to expose the internal data structure, but to
deliver a tokenized version of the given SQL script.
There's actually many different use cases for external projects :
- syntax highlighting
- rewrite query with proper indentation
- replication
- properly splitting queries from a script
- define type of the query (SELECT ? UPDATE/DELETE ? DDL ?)
- checking validity of a query before sending it
- ...
In addition of PgPool needs, I can see 3 or 4 direct use cases for
pgAdmin and phpPgAdmin.
So it seems to me having the parser code in a shared library would be
very useful for external C projects which can link to it. However it
would be useless for other non-C projects which can't use it directly
but are connected to a PostgreSQL backend anyway (phpPgAdmin as instance).
What about having a new SQL command like TOKENIZE ? it would kinda act
like EXPLAIN but giving a tokenized version of the given SQL script. As
EXPLAIN, it could speak XML, YAML, JSON, you name it...
Each token could have :
- a type ('identifier', 'string', 'sql command', 'sql keyword',
'variable'...)
- the start position in the string
- the value
- the line number
- ...
A simple example of a tokenizer is the php one:
http://fr.php.net/token_get_all
And here is a basic example which return pseudo rows here :
= TOKENIZE $script$
SELECT 1;
UPDATE test SET a=2;
$script$;
type | pos | value | line
- -+-+--+--
SQL_COMMAND | 1 | 'SELECT' | 1
CONSTANT| 8 | '1' | 1
DELIMITER | 9 | ';' | 1
SQL_COMMAND | 11 | 'UPDATE' | 2
IDENTIFIER | 18 | 'test' | 2
SQL_KEYWORD | 23 | 'SET'| 2
IDENTIFIER | 27 | 'a'| 2
OPERATOR| 30 | '=' | 2
CONSTANT| 31 | '1' | 2
Cheers,
David.
As a phpPgAdmin dev, I am thinking about this subject since a long time.
I am interested about trying to create such a patch after discussing it
and if you think it is doable.
- --
JGuillaume (ioguix) de Rorthais
http://www.dalibo.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkvPOJMACgkQxWGfaAgowiLrUACfa7qMVr3oiOVS7JfhTa1S9EqY
pYkAn3Sj6cezC/EdWPu2+kzrgjaDygGE
=oY1c
-END PGP SIGNATURE-
--
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] Bug ? different behaviour between 8.3 and 8.4 won IS NULL with sub arrays of nulls
On Tue, 19 Jan 2010, Tom Lane wrote: iog...@free.fr writes: I found a difference of behaviour between 8.3 and 8.4 on IS NULL with multi-level arrays with NULL values. 8.3's behavior is just a bug --- Ok, should I report through the -bugs ml for tracking purpose ? or is it useless cause it's on -hackers andsomeone will jump on this bug to fix and forget it ? try comparing the results when the values are variables that happen to be null, rather than simple constant nulls. 8.4 is consistent with that case, 8.3 isn't. Right, it behaves consistently with variables. Here is another test case where 8.3 is inconsistent with *himself* this time: postgres=# SELECT substring(version(),12,5); substring --- 8.3.9 (1 ligne) postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL FROM (SELECT 1) t; ?column? -- t (1 ligne) postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL; ?column? -- f (1 ligne) regards, tom lane -- Jehan-Guillaume (ioguix) de Rorthais DBA http://www.dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug ? different behaviour between 8.3 and 8.4 won IS NULL with sub arrays of nulls
Hi, I found a difference of behaviour between 8.3 and 8.4 on IS NULL with multi-level arrays with NULL values. I looked at the Changelog between 8.3 and 8.4, but I didn't find something really clear about this. Is this a bug or a known issue or a normal, documented, difference of behaviour ? See the following use case: $ psql -p 5433 -U postgres -h localhost -c \ 'SELECT substring(version() from 0 for 15), ROW(NULL,NULL) IS NULL, ROW(ROW(NULL),NULL) IS NULL;' substring| ?column? | ?column? +--+-- PostgreSQL 8.3 | t| f (1 ligne) $ psql -p 5434 -U postgres -h localhost -c \ 'SELECT substring(version() from 0 for 15), ROW(NULL,NULL) IS NULL, ROW(ROW(NULL),NULL) IS NULL;' substring| ?column? | ?column? +--+-- PostgreSQL 8.4 | t| t (1 ligne) Regards, -- Jehan-Guillaume (ioguix) de Rorthais DBA http://www.dalibo.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] Triggers on columns
On Thu, 3 Sep 2009, Robert Haas wrote: On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentrautpete...@gmx.net wrote: On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: On Sep 3, 2009, at 7:44 AM, Peter Eisentraut pete...@gmx.net wrote: The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. Well, if you find that bizarre, consider the existing behavior: Why should an ON UPDATE row trigger fire when none of the values of the row's columns actually change? I think if you read TRIGGER ON UPDATE as TRIGER ON UPDATE OF all columns then it makes some sense. Not to me. I use triggers to maintain database invariants, such as: CREATE TABLE foo (id serial, name varchar, number_of_bars integer not null default 0, primary key (id)); CREATE TABLE bar (id serial, foo_id integer not null references foo (id)); By setting up INSERT, UPDATE, and DELETE triggers on bar, I can maintain the invariant that number_of_bars for each foo is in fact the number of bars where foo_id is the id of that foo. However, in order to suppress unnecessary updates to the foo table, I have to have the update trigger check whether OLD.foo_id = NEW.foo_id before it does anything. If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. But I also wouldn't argue that should be the only available behavior. Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. A simple use case would be to update a timestamp column with CURRENT_TIMESTAMP as instance. ...Robert -- Guillaume (ioguix) de Rorthais -- 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] generic options for explain
Magnus Hagander a écrit : Dave Page wrote: I was thinking something similar, but from the pgAdmin perspective. We already use libxml2, but JSON would introduce another dependency for us. Yeah, but probably not a huge one. There is one for wx, but I don't think it's included by default. +1 for the machine readable explain. FWIW, I have an early patch for phpPgAdmin about a graphical explain. IIRC when I wrote it, I told myself the parser might actually be broken with multi-level sub-queries or something. But I ended with the same parsing code than pgAdmin anyway. About the format, JSON would be the best here, as it is a one function call in PHP to retrieve an associative array from the JSON code. -- Guillaume (ioguix) de Rorthais -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
