Re: [HACKERS] WIP checksums patch
On Monday, October 01, 2012 11:11 PM Jeff Davis wrote: On Mon, 2012-10-01 at 18:14 +0100, Simon Riggs wrote: You are missing large parts of the previous thread, giving various opinions on what the UI should look like for enabling checksums. I read through all of the discussion that I could find. There was quite a lot, so perhaps I have forgotten pieces of it. But that one section in the docs does look out of date and/or confusing to me. I remember there was discussion about a way to ensure that checksums are set cluster-wide with some kind of special command (perhaps related to VACUUM) and a magic file to let recovery know whether checksums are set everywhere or not. That doesn't necessarily conflict with the GUC though (the GUC could be a way to write checksums lazily, while this new command could be a way to write checksums eagerly). If some consensus was reached on the exact user interface, can you please send me a link? AFAICT complete consensus has not been reached but one of the discussions can be found on below link: http://archives.postgresql.org/pgsql-hackers/2012-03/msg00279.php Here Robert has given suggestions and then further there is more discussion based on that points. According to me, the main points where more work for this patch is required as per previous discussions is as follows: 1. Performance impact of WAL log for hint-bits needs to be verified for scenario's other than pg_bench (Such as bulk data load (which I feel there is some way to optimize, but I don't know if that’s part of this patch)). 2. How to put the information in Page header. I think general direction is use pd_tli. Storing whether page has checksum should be done or it needs to be maintained at table or db level is not decided. 3. User Interface for Checksum options. 4. Still not there is consensus about locking the buffer. 5. Any more which I have missed? Apart from above, one of the concern raised by many members is that there should be page format upgrade infrastructure first and then we should add thinking of checksums(http://archives.postgresql.org/pgsql-hackers/2012-02/msg01517.php). The major point for upgrade is that it should be an online upgrade and the problem which I think there is no clear solution yet is hot to ensure that a database will never have more than 2 page formats. If the general consensus is we should do it without having upgrade, then I think we can pursue discussion about the main points listed above. With Regards, Amit Kapila. -- 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] Bad Data back Door
David E. Wheeler wrote: As the author I agree that this is a bug in oracle_fdw. Thanks. Should I file a report somewhere? That's not necessary. Thanks for reporting the problem. It may be a few days until I get around to fix that. Oracle does not care much about correct encoding. Yeah, same here. I've been looking into write a function to try to fix poorly-encoded data, though, but haven't got far, because CONVERT() does not indicate failure. If you have any insight on this, I'd appreciate your thoughts on this Stack Overflow question: http://stackoverflow.com/q/12717363/79202 The only thing I can think of is a stored procedure in Java. You could use java.nio.charset.CharsetEncoder and java.nio.charset.CharsetDecoder which should throw exceptions if they encounter illegal bytes. Yours, Laurenz Albe -- 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] sortsupport for text
Peter Geoghegan pe...@2ndquadrant.com writes: On 8 October 2012 21:35, Robert Haas robertmh...@gmail.com wrote: Gentlemen, you can't fight here. This is the War Room. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Deparsing DDL command strings
Jim Nasby j...@nasby.net writes: I definitely want to be able to parse DDL commands to be able to either enforce things or to drive other parts of the system based on what's changing. Without the ability to capture (and parse) DDL commands I'm stuck creating wrapper functions around anything I want to capture and then trying to ensure that everyone uses the wrappers and not the raw DDL commands. Are you mainly working on some Auditing system? Event triggers that just spit out raw SQL give me the first part of this, but not the second part: I'm still stuck trying to parse raw SQL on my own. Having normalized SQL to parse should make that a bit easier, but ideally I'd like to be able to pull specific elements out of a command. I'd want to be able to do things like: The current design for event triggers is to spit out several things: - command tag is already commited - object id, can be null - schema name, can be null - object name - operationeither ALTER, CREATE or DROP, … - object type TABLE, VIEW, FUNCTION, … - normalized command string After some more thinking, it appears that in several case you want to have all those information filled in and you don't want to care if that means your trigger needs to run at ddl_command_start or ddl_command_end. The proposal I want to make here is to introduce a generic event (or an event alias) named ddl_command_trace that the system provides at the right spot where you have the information. That's useful when you don't intend to divert the execution of the DDL and need to know all about it. For a DROP operation, ddl_command_trace would be ddl_command_start, and for a CREATE operation, that would be ddl_command_end, so that the target object (still|already) exists when the trigger is fired. IF command is ALTER TABLE THEN That's called TG_TAG, see http://www.postgresql.org/docs/devel/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER FOR EACH subcommand IF subcommand IS DROP COLUMN THEN do something that needs to know what column is being dropped ELSE IF subcommand IS ADD COLUMN THEN do something that needs to know the definition of the column being added We decided not to publish any notion of subcommand at this stage. I don't think every bit of that has to be dealt with by the event trigger code itself. For example, if you're adding a column to a table and the entries have already been made in the catalog, you could query to get the details of the column definition if you were given an OID into pg_attributes. It's easy enough to provide the OID of the newly created main command target object, it's harder to provide in a generic way all the OID of the objects you might be interested into, because each command has its own set of such. DROP can target multiple objects, they all are the main target. ALTER target only a single object, but can link to dependent objects. CREATE an operator class or a cast and you're talking about a bunch of operators and functions to tie together. It's not that easy. Having said all that, an event system that spits back the raw SQL would certainly be better than nothing. But realize that people would still need to do parsing on it (ie: replication solutions will need to know what table just got ALTER'd). You would have most of what you're asking. I think that looking into the normalized string to get the information you need when you already know you're looking at an ALTER TABLE statement and you already have the object references (schema, name, oid) is going to make things easier. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Truncate if exists
Hi, With the help of Cédric, here's a patch changing the TRUNCATE TABLE command, adding the IF EXISTS option to allow the presence in the list of tables of a missing or invisible table. This meets the needs of scripts that should be run in different stages, and do not always have the same visibility on the tables, as well as DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we prefer to ignore the absence of the table. It is a small patch which changes very little code, but that could be quite useful. Regards, -- Sébastien Lardière PostgreSQL DBA Team Manager Hi-Media diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index 7b9c2f3..8af9f0b 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation refsynopsisdiv synopsis -TRUNCATE [ TABLE ] [ ONLY ] replaceable class=PARAMETERname/replaceable [ * ] [, ... ] +TRUNCATE [ TABLE ] [ IF EXISTS ] [ ONLY ] replaceable class=PARAMETERname/replaceable [ * ] [, ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] /synopsis /refsynopsisdiv @@ -44,6 +44,16 @@ TRUNCATE [ TABLE ] [ ONLY ] replaceable class=PARAMETERname/replaceable [ variablelist varlistentry +termliteralIF EXISTS/literal/term +listitem + para + Do not throw an error if a table does not exist. A notice is issued + in this case. + /para +/listitem + /varlistentry + + varlistentry termreplaceable class=PARAMETERname/replaceable/term listitem para @@ -222,7 +232,8 @@ TRUNCATE othertable CASCADE; also appear in that standard, but have slightly different though related meanings. Some of the concurrency behavior of this command is left implementation-defined by the standard, so the above notes should be - considered and compared with other implementations if necessary. + considered and compared with other implementations if necessary. The + literalIF EXISTS/ option is a productnamePostgreSQL/ extension. /para /refsect1 /refentry diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 359d478..f48dbf8 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -948,6 +948,9 @@ ExecuteTruncate(TruncateStmt *stmt) ResultRelInfo *resultRelInfo; SubTransactionId mySubid; ListCell *cell; + ListCell *prev; + + prev = NULL; /* * Open, exclusive-lock, and check all the explicitly-specified relations @@ -959,7 +962,22 @@ ExecuteTruncate(TruncateStmt *stmt) bool recurse = interpretInhOption(rv-inhOpt); Oid myrelid; - rel = heap_openrv(rv, AccessExclusiveLock); + rel = heap_openrv_extended(rv, AccessExclusiveLock, stmt-missing_ok); + /* TODO : if oid is not valid + * NOTICE with table name + * invalid oid in the list + */ + if(rel == NULL) { + ereport(NOTICE, + (errcode(ERRCODE_UNDEFINED_TABLE), + errmsg(relation %s does not exist, rv-relname ) + ) + ); + list_delete_cell(stmt-relations, cell, prev) ; + continue; + } + prev = cell; + myrelid = RelationGetRelid(rel); /* don't throw error for TRUNCATE foo, foo */ if (list_member_oid(relids, myrelid)) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e4ff76e..f6a03f3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -5065,6 +5065,16 @@ TruncateStmt: n-relations = $3; n-restart_seqs = $4; n-behavior = $5; + n-missing_ok = false; + $$ = (Node *)n; +} +| TRUNCATE opt_table IF_P EXISTS relation_expr_list opt_restart_seqs opt_drop_behavior +{ + TruncateStmt *n = makeNode(TruncateStmt); + n-relations = $5; + n-restart_seqs = $6; + n-behavior = $7; + n-missing_ok = true; $$ = (Node *)n; } ; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 09b15e7..814b129 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1954,6 +1954,7 @@ typedef struct TruncateStmt List *relations; /* relations (RangeVars) to be truncated */ bool restart_seqs; /* restart owned sequences? */ DropBehavior behavior; /* RESTRICT or CASCADE behavior */ + bool missing_ok; /* skip error if object is missing? */ } TruncateStmt; /* -- diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out index 5c5277e..839104d 100644 --- a/src/test/regress/expected/truncate.out +++ b/src/test/regress/expected/truncate.out @@ -420,3 +420,13 @@ SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped ERROR: relation truncate_a_id1 does not exist LINE 1: SELECT nextval('truncate_a_id1'); ^ +-- test IF EXISTS +CREATE TABLE truncate_a(id int); +INSERT INTO truncate_a VALUES ( 1 ); +TRUNCATE TABLE IF EXISTS truncate_a ; +INSERT INTO truncate_a VALUES ( 1
Re: [HACKERS] Truncate if exists
On 9 October 2012 09:33, Sébastien Lardière slardi...@hi-media.com wrote: With the help of Cédric, here's a patch changing the TRUNCATE TABLE command, adding the IF EXISTS option to allow the presence in the list of tables of a missing or invisible table. This meets the needs of scripts that should be run in different stages, and do not always have the same visibility on the tables, as well as DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we prefer to ignore the absence of the table. It is a small patch which changes very little code, but that could be quite useful. Agreed. Patch looks fine, but please observe the coding standards wrt nested brackets. Will apply in 48 hours barring objections. Anyone want to check for any other missing IF EXISTS capability in other DDL? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Bugs in CREATE/DROP INDEX CONCURRENTLY
On 6 October 2012 00:56, Tom Lane t...@sss.pgh.pa.us wrote: 1. These operations think they can use ordinary heap_update operations to change pg_index entries when they don't have exclusive lock on the parent table. The lack of ex-lock means that another backend could be currently loading up its list of index OIDs for the table --- and since it scans pg_index with SnapshotNow to do that, the heap_update could result in the other backend failing to see this index *at all*. That's okay if it causes the other backend to not use the index for scanning... but not okay if it causes the other backend to fail to make index entries it is supposed to make. I think this could possibly be fixed by using nontransactional update-in-place when we're trying to change indisvalid and/or indisready, but I've not really thought through the details. Only solution there is to fix SnapshotNow, as we discussed last Christmas. I'll dig out my patch for that, which IIRC I was nervous of committing at last minute into 9.2. 2. DROP INDEX CONCURRENTLY doesn't bother to do TransferPredicateLocksToHeapRelation until long after it's invalidated the index. Surely that's no good? Is it even possible to do that correctly, when we don't have a lock that will prevent new predicate locks from being taken out meanwhile? No idea there. Input appreciated. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Truncate if exists
On 10/09/2012 11:09 AM, Simon Riggs wrote: On 9 October 2012 09:33, Sébastien Lardière slardi...@hi-media.com wrote: With the help of Cédric, here's a patch changing the TRUNCATE TABLE command, adding the IF EXISTS option to allow the presence in the list of tables of a missing or invisible table. This meets the needs of scripts that should be run in different stages, and do not always have the same visibility on the tables, as well as DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we prefer to ignore the absence of the table. It is a small patch which changes very little code, but that could be quite useful. Agreed. Patch looks fine, but please observe the coding standards wrt nested brackets. Will apply in 48 hours barring objections. Anyone want to check for any other missing IF EXISTS capability in other DDL? Indeed, brackets was not correct, it's better now (I think), and correct some comments. Thanks, -- Sébastien Lardière PostgreSQL DBA Team Manager Hi-Media diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index 7b9c2f3..8af9f0b 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation refsynopsisdiv synopsis -TRUNCATE [ TABLE ] [ ONLY ] replaceable class=PARAMETERname/replaceable [ * ] [, ... ] +TRUNCATE [ TABLE ] [ IF EXISTS ] [ ONLY ] replaceable class=PARAMETERname/replaceable [ * ] [, ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] /synopsis /refsynopsisdiv @@ -44,6 +44,16 @@ TRUNCATE [ TABLE ] [ ONLY ] replaceable class=PARAMETERname/replaceable [ variablelist varlistentry +termliteralIF EXISTS/literal/term +listitem + para + Do not throw an error if a table does not exist. A notice is issued + in this case. + /para +/listitem + /varlistentry + + varlistentry termreplaceable class=PARAMETERname/replaceable/term listitem para @@ -222,7 +232,8 @@ TRUNCATE othertable CASCADE; also appear in that standard, but have slightly different though related meanings. Some of the concurrency behavior of this command is left implementation-defined by the standard, so the above notes should be - considered and compared with other implementations if necessary. + considered and compared with other implementations if necessary. The + literalIF EXISTS/ option is a productnamePostgreSQL/ extension. /para /refsect1 /refentry diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 359d478..cb08bac 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -948,6 +948,9 @@ ExecuteTruncate(TruncateStmt *stmt) ResultRelInfo *resultRelInfo; SubTransactionId mySubid; ListCell *cell; + ListCell *prev; + + prev = NULL; /* * Open, exclusive-lock, and check all the explicitly-specified relations @@ -959,7 +962,23 @@ ExecuteTruncate(TruncateStmt *stmt) bool recurse = interpretInhOption(rv-inhOpt); Oid myrelid; - rel = heap_openrv(rv, AccessExclusiveLock); + rel = heap_openrv_extended(rv, AccessExclusiveLock, stmt-missing_ok); + /* if oid is not valid + * NOTICE with table name + * then invalid oid in the list + */ + if(rel == NULL) + { + ereport(NOTICE, + (errcode(ERRCODE_UNDEFINED_TABLE), + errmsg(relation %s does not exist, rv-relname ) + ) + ); + list_delete_cell(stmt-relations, cell, prev) ; + continue; + } + prev = cell; + myrelid = RelationGetRelid(rel); /* don't throw error for TRUNCATE foo, foo */ if (list_member_oid(relids, myrelid)) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e4ff76e..f6a03f3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -5065,6 +5065,16 @@ TruncateStmt: n-relations = $3; n-restart_seqs = $4; n-behavior = $5; + n-missing_ok = false; + $$ = (Node *)n; +} +| TRUNCATE opt_table IF_P EXISTS relation_expr_list opt_restart_seqs opt_drop_behavior +{ + TruncateStmt *n = makeNode(TruncateStmt); + n-relations = $5; + n-restart_seqs = $6; + n-behavior = $7; + n-missing_ok = true; $$ = (Node *)n; } ; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 09b15e7..814b129 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1954,6 +1954,7 @@ typedef struct TruncateStmt List *relations; /* relations (RangeVars) to be truncated */ bool restart_seqs; /* restart owned sequences? */ DropBehavior behavior; /* RESTRICT or CASCADE behavior */ + bool missing_ok; /* skip error if object is missing? */ } TruncateStmt; /* -- diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out index 5c5277e..839104d 100644 ---
Re: [HACKERS] Truncate if exists
On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs si...@2ndquadrant.com wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? Yes, DEALLOCATE.
Re: [HACKERS] Detecting libpq connections improperly shared via fork()
On Thursday, October 04, 2012 03:23:54 AM Tom Lane wrote: Daniel Farina dan...@heroku.com writes: On Wed, Oct 3, 2012 at 3:14 PM, Andres Freund and...@2ndquadrant.com wrote: Hm. An easier version of this could just be storing the pid of the process that did the PQconnectdb* in the PGconn struct. You can then check that PGconn-pid == getpid() at relatively few places and error out on a mismatch. That should be doable with only minor overhead. I suppose this might needlessly eliminate someone who forks and hands off the PGconn struct to exactly one child, but it's hard to argue with its simplicity and portability of mechanism. Yeah, the same thing had occurred to me, but I'm not sure that getpid() is really cheap enough to claim that the overhead is negligible. I guess its going to be os/libc dependant. In glibc systems getpid() should be basically just be a function call (no syscall). A bigger problem with this is that it only fixes the issue for cases in which somebody makes new threads of control with fork(). I believe that issues involving multiple threads trying to use the same PGconn are at least as widespread. I'm not terribly excited about removing functionality and adding overhead to protect against just one variant of the problem. True. But people seem to be more wary of problems in the case of threads... We could play similar things with pthread_self() or gettid() but I am not sure how portable even the former is... Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Detecting libpq connections improperly shared via fork()
On Tue, Oct 9, 2012 at 2:51 AM, Andres Freund and...@2ndquadrant.com wrote: On Thursday, October 04, 2012 03:23:54 AM Tom Lane wrote: Daniel Farina dan...@heroku.com writes: On Wed, Oct 3, 2012 at 3:14 PM, Andres Freund and...@2ndquadrant.com wrote: Hm. An easier version of this could just be storing the pid of the process that did the PQconnectdb* in the PGconn struct. You can then check that PGconn-pid == getpid() at relatively few places and error out on a mismatch. That should be doable with only minor overhead. I suppose this might needlessly eliminate someone who forks and hands off the PGconn struct to exactly one child, but it's hard to argue with its simplicity and portability of mechanism. Yeah, the same thing had occurred to me, but I'm not sure that getpid() is really cheap enough to claim that the overhead is negligible. I guess its going to be os/libc dependant. In glibc systems getpid() should be basically just be a function call (no syscall). To protect users who fork but then thoroughly forget about the connection in either the parent or child process, the original sketch I had in mind (which did not use getpid) would be to increment-and-check a monotonic number of some kind when protocol traffic is initiated (effectively tell on the socket). If that shared state is incremented in an unexpected way, then it is known that another process somewhere has mucked with the protocol state, and it's time to deliver a lucid error. That means both a shared (such as an anonymous mmap) and a not-shared (process-local as per most things when forking, or in the case of threads thread-local) state would be required. Both halves have thorny portability problems AFAIK, so I was somewhat hesitant to bring it up. However, I would like to re-iterate that this is a very common problem, so it may be worth pausing to think about solving it. Whenever someone writes in saying what's up with these strange SSL errors, generally the first question in response is are you using unicorn? (for Ruby, 'gunicorn' for Python). The answer is almost invariably yes. The remainder have renegotiation issues. -- fdr -- 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] Move postgresql_fdw_validator into dblink
Hanada-san, It is fair enough for me. So, I'd like to hand over this patch for committers. Thanks, 2012/10/9 Shigeru HANADA shigeru.han...@gmail.com: (2012/10/09 0:30), Kohei KaiGai wrote: The attached patch is a revised one according to my previous suggestion. It re-defines PQconninfoOption *options as static variable with NULL initial value, then, PQconndefaults() shall be invoked at once. The default options never changed during duration of the backend process, so here is no reason why we allocate and free this object for each validator invocation. Sorry for delayed response. It seems reasonable, so I just fixed obsolete comment and indent. Please see attached v3 patch which was rebased against latest head of master. If it is also OK for you, I'd like to take over this patch to comitter. This patch is prerequisite of postgresql_fdw, so I hope this patch getting merged soon. Please go ahead. :-) Regards, -- Shigeru HANADA -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] Truncate if exists
On Tue, Oct 9, 2012 at 11:51 AM, Vik Reykja vikrey...@gmail.com wrote: On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs si...@2ndquadrant.comwrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? Yes, DEALLOCATE. Patch attached. deallocate_if_exists.patch 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] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Mon, Oct 8, 2012 at 10:42 AM, Amit Kapila amit.kap...@huawei.com wrote: How about following: 1. replication_client_timeout -- shouldn't it be client as new configuration is for wal receiver 2. replication_standby_timeout ISTM that the client and the standby are the same thing. If we introduce a new parameter for wal receiver, wouldn't replication_timeout be confusing for user? Maybe. I actually don't think that I understand what problem we're trying to solve here. If the connection between the master and the standby is lost, shouldn't the standby realize that it's no longer receiving keepalives from the master and terminate the connection? I thought I had tested this at some point and it was working, so either it's subsequently gotten broken again or the scenario you're talking about is different in some way that I don't currently understand. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Bad Data back Door
I wrote: Tom Lane wrote: Now, having said that, I think it has to be the reponsibility of the FDW to apply any required check ... which makes this a bug report against oracle_fdw, not the core system. As the author I agree that this is a bug in oracle_fdw. Ok, fixed. David, could you try the latest version from CVS to see if it works for you? Yours, Laurenz Albe -- 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] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Tuesday, October 09, 2012 6:00 PM Robert Haas wrote: On Mon, Oct 8, 2012 at 10:42 AM, Amit Kapila amit.kap...@huawei.com wrote: How about following: 1. replication_client_timeout -- shouldn't it be client as new configuration is for wal receiver 2. replication_standby_timeout ISTM that the client and the standby are the same thing. Yeah same, but may be one (replication_standby_timeout) can be more easily understandable by user. If we introduce a new parameter for wal receiver, wouldn't replication_timeout be confusing for user? Maybe. I actually don't think that I understand what problem we're trying to solve here. If the connection between the master and the standby is lost, shouldn't the standby realize that it's no longer receiving keepalives from the master and terminate the connection? For wal receiver keepalives are also like one kind of message, so the behavior is such that when it checks that it doesn't receive any message, it tries to send reply/feedback message to master after an interval of wal_receiver_status_interval. So after every wal_receiver_status_interval, wal receiver sends a reply, but still the socket send doesn't fail. It fails only after many send calls as internally might be in send(), until the sockets internal buffer is full, it keeps accumulating even if other side recv has not received the data. So that's the reason we decided to introduce a timeout parameter in wal receiver similar to what we have currently in walsender. I thought I had tested this at some point and it was working, so either it's subsequently gotten broken again or the scenario you're talking about is different in some way that I don't currently understand. Standby takes quite longer around 15 minutes to detect whereas master is able to detect quite sooner in 2-3 mins and master also mainly detects due to timeout functionality in wal sender. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Behavior for crash recovery when it detects a corrupt WAL record
I have observed that currently during recovery, while it applies the WAL records even if it detects that there is a corrupt record by crc validation, it proceeds. Basically ReadRecord(), returns NULL in such cases which makes the behavior same as it has reached end of WAL. After that server get started and user can perform operations normally. However ITSM that this is a problem as user might loose some committed data. Is there any particular reason for this behavior? With Regards, Amit Kapila.
Re: [HACKERS] Truncate if exists
Simon Riggs si...@2ndquadrant.com writes: On 9 October 2012 09:33, Sébastien Lardière slardi...@hi-media.com wrote: With the help of Cédric, here's a patch changing the TRUNCATE TABLE command, adding the IF EXISTS option to allow the presence in the list of tables of a missing or invisible table. Will apply in 48 hours barring objections. I object: this doesn't deserve to be fast-tracked like that with no thought about whether the semantics are actually useful or sensible. For starters, the use-case hasn't been explained to my satisfaction. In what situation is it actually helpful to TRUNCATE a table that's not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS to keep from failing later in the script? If so, why not just do that first? Second, to my mind the point of a multi-table TRUNCATE is to ensure that all the referenced tables get reset to empty *together*. With something like this, you'd have no such guarantee. Consider a timeline like this: Session 1 Session 2 TRUNCATE IF EXISTS a, b, c; ... finds c doesn't exist ... ... working on a and b ... CREATE TABLE c ( ... ); INSERT INTO c ...; ... commits ... Now we have a, b, and c, but c isn't empty, violating the expectations of session 1. So even if there's a use-case for IF EXISTS on a single table, I think it's very very dubious to allow it in multi-table commands. 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] Behavior for crash recovery when it detects a corrupt WAL record
On 09.10.2012 16:42, Amit Kapila wrote: I have observed that currently during recovery, while it applies the WAL records even if it detects that there is a corrupt record by crc validation, it proceeds. Basically ReadRecord(), returns NULL in such cases which makes the behavior same as it has reached end of WAL. After that server get started and user can perform operations normally. Yeah. We rely on the CRC to detect end of WAL during recovery. If the system crashes while the WAL is being flushed to disk, it's normal that there's a corrupt (ie. partially written) record at the end of the WAL. This is a common technique used by pretty much every system with a transaction log / journal. The other option would be to perform two fsyncs for every commit; one to flush the WAL to disk, and another to update some global pointer to point to the end of valid WAL (e.g in pg_control). - Heikki -- 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] Truncate if exists
=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= slardi...@hi-media.com writes: Indeed, brackets was not correct, it's better now (I think), and correct some comments. Still wrong ... at the very least you missed copyfuncs/equalfuncs. In general, when adding a field to a struct, it's good practice to grep for all uses of that struct. 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] Add FET to Default and Europe.txt
On Mon, Oct 8, 2012 at 4:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: for instance IST (the Indians and the Israelis both use this, but not to mean the same thing). And Ireland btw. So I'm not sure if this goes anywhere but could we get away with picking the timezone with the matching abbreviation closest to the system timezone? -- greg -- 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] TODO item: teach pg_dump about sparsely-stored large objects
On Tue, Oct 9, 2012 at 3:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Admittedly, this is no different than what happens when you try to back up a sparsely-stored Unix file, at least with simpler backup tools. But it seems to me we should try a bit harder. Fwiw both GNU tar and GNU cp support creating sparse files. They do it by just detecting blocks of NULs and skipping over them. pg_restore could do that today without any API changes. That said, an API to save pg_dump the time and space of reading the fake zeros out of the database dosen't sonud like a bad thing. -- greg -- 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] Truncate if exists
On 10/09/2012 04:06 PM, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On 9 October 2012 09:33, Sébastien Lardière slardi...@hi-media.com wrote: With the help of Cédric, here's a patch changing the TRUNCATE TABLE command, adding the IF EXISTS option to allow the presence in the list of tables of a missing or invisible table. Will apply in 48 hours barring objections. I object: this doesn't deserve to be fast-tracked like that with no thought about whether the semantics are actually useful or sensible. For starters, the use-case hasn't been explained to my satisfaction. In what situation is it actually helpful to TRUNCATE a table that's not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS to keep from failing later in the script? If so, why not just do that first? it could be useful to not rollback transactions : - if a table is not yet or no more visible, because of search_path modification - if a table was dropped, for any reason Second, to my mind the point of a multi-table TRUNCATE is to ensure that all the referenced tables get reset to empty *together*. With something like this, you'd have no such guarantee. Consider a timeline like this: Session 1 Session 2 TRUNCATE IF EXISTS a, b, c; ... finds c doesn't exist ... ... working on a and b ... CREATE TABLE c ( ... ); INSERT INTO c ...; ... commits ... Now we have a, b, and c, but c isn't empty, violating the expectations of session 1. So even if there's a use-case for IF EXISTS on a single table, I think it's very very dubious to allow it in multi-table commands. Well, I have to say that if I'm the guy who create the table c, I don't want to see the table empty after my insert, don't you think ? I understand your point about the multi-table TRUNCATE, but my point is to commit transaction, whatever the visibility or presence of a given table. In a perfect world, we could review all our processes, and change them to guarantee commit, then we don't need IF EXISTS ; But i'm not in this case, and maybe some others neither, are you ? -- Sébastien Lardière PostgreSQL DBA Team Manager Hi-Media -- 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] Switching timeline over streaming replication
On 06.10.2012 15:58, Amit Kapila wrote: One more test seems to be failed. Apart from this, other tests are passed. 2. a. Master M-1 b. Standby S-1 follows M-1 c. insert 10 records on M-1. verify all records are visible on M-1,S-1 d. Stop S-1 e. insert 2 records on M-1. f. Stop M-1 g. Start S-1 h. Promote S-1 i. Make M-1 recovery.conf such that it should connect to S-1 j. Start M-1. Below error comes on M-1 which is expected as M-1 has more data. LOG: database system was shut down at 2012-10-05 16:45:39 IST LOG: entering standby mode LOG: consistent recovery state reached at 0/176A070 LOG: record with zero length at 0/176A070 LOG: database system is ready to accept read only connections LOG: streaming replication successfully connected to primary LOG: fetching timeline history file for timeline 2 from primary server LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions LOG: new timeline 2 forked off current database system timeline 1 before current recovery point 0/176A070 LOG: re-handshaking at position 0/100 on tli 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions LOG: new timeline 2 forked off current database system timeline 1 before current recovery point 0/176A070 k. Stop M-1. Start M-1. It is able to successfully connect to S-1 which is a problem. l. check in S-1. Records inserted in step-e are not present. m. Now insert records in S-1. M-1 doesn't recieve any records. On M-1 server following log is getting printed. LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 Hmm, seems we need to keep track of which timeline we've used to recover before. Before restart, the master correctly notices that timeline 2 forked off earlier in its history, so it cannot recover to that timeline. But after restart the master begins recovery from the previous checkpoint, and because timeline 2 forked off timeline 1 after the checkpoint, it concludes that it can follow that timeline. It doesn't realize that it had some already recovered/flushed some WAL in timeline 1 after the fork-point. Attached is a new version of the patch. I committed the refactoring of XLogPageRead() already, as that was a readability improvement even without this patch. All the reported issues should be fixed now, although I will continue testing this tomorrow. I added various checks that that the correct timeline is followed during recovery. minRecoveryPoint is now accompanied by a timeline ID, so that when we restart recovery, we check that we recover back to minRecoveryPoint along the same timeline as last time. Also, it now checks at beginning of recovery that the checkpoint record comes from the correct timeline. That fixes the problem that you reported above. I also adjusted the error messages on timeline history problems to be more clear. - Heikki streaming-tli-switch-4.patch.gz Description: GNU Zip compressed 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] Detecting libpq connections improperly shared via fork()
On Thu, Oct 04, 2012 at 12:14:02AM +0200, Andres Freund wrote: On Thursday, October 04, 2012 12:08:18 AM Daniel Farina wrote: It would be fantastic for libpq to somehow monitor use of a connection from multiple PIDs that share a parent and deliver an error indicating what is wrong. Unfortunately detecting that would require either a file or some kind of shared memory map, AFAIK, and I don't know how keen anyone is on accepting that patch. So, may I ask: how keen is anyone on accepting such a patch, and under what conditions of mechanism? Hm. An easier version of this could just be storing the pid of the process that did the PQconnectdb* in the PGconn struct. You can then check that PGconn-pid == getpid() at relatively few places and error out on a mismatch. That should be doable with only minor overhead. On system's that support it, pthread_atfork() might help. Though being like a signal handler you don't have access to the PGconn structure, so you can't flag anything easily. Maybe just to cache getpid()? In any case, adding a check to PQexec and friends would probably be sufficient, since that's what every program is going to start with. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Truncate if exists
On Tue, Oct 9, 2012 at 12:28 PM, Sébastien Lardière slardi...@hi-media.com wrote: For starters, the use-case hasn't been explained to my satisfaction. In what situation is it actually helpful to TRUNCATE a table that's not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS to keep from failing later in the script? If so, why not just do that first? it could be useful to not rollback transactions : - if a table is not yet or no more visible, because of search_path modification I don't think I understand the case you are describing here. - if a table was dropped, for any reason But in this case surely you could use DROP IF EXISTS. I've been a big proponent of adding IF EXISTS support to CREATE TABLE and ALTER TABLE but I'm having a hard time getting excited about this one. I can't imagine that many people would use it, and those who do can implement it in about 10 lines of PL/pgsql. The existence of DO blocks and the fact that PL/pgsql is now installed by default have made it much more convenient to solve these kinds of problems using those tools rather than needing dedicated syntax. That does not mean that the most frequently used cases shouldn't have dedicated syntax anyway, for convenience, but I'm doubtful that this falls into that category. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Truncate if exists
On 9 October 2012 15:06, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 9 October 2012 09:33, Sébastien Lardière slardi...@hi-media.com wrote: With the help of Cédric, here's a patch changing the TRUNCATE TABLE command, adding the IF EXISTS option to allow the presence in the list of tables of a missing or invisible table. Will apply in 48 hours barring objections. I object: this doesn't deserve to be fast-tracked like that with no thought about whether the semantics are actually useful or sensible. I wasn't fast tracking it, just looking to apply small uncontentious patches quickly. Your objection is enough to stall until next commitfest for further discussion. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Truncate if exists
Robert, I've been a big proponent of adding IF EXISTS support to CREATE TABLE and ALTER TABLE but I'm having a hard time getting excited about this one. I can't imagine that many people would use it, and those who do can implement it in about 10 lines of PL/pgsql. The existence of DO blocks and the fact that PL/pgsql is now installed by default have made it much more convenient to solve these kinds of problems using those tools rather than needing dedicated syntax. That does not mean that the most frequently used cases shouldn't have dedicated syntax anyway, for convenience, but I'm doubtful that this falls into that category. On the other hand, it's useful to consistently have IF EXISTS syntax for the majority of utility commands. It's confusing to users that they can do DROP TABLE IF EXISTS but not TRUNCATE IF EXISTS, even if the latter is less useful than the former. So that's one reason to support this. The second is for making deployment scripts idempotent. For example, say you have script A which creates table josh, and script B which needs table josh to be empty, if present. Since the two scripts are tied to different database features, and you don't know which one will be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can solve that problem with DO, but why make users go to the extra effort? Is it *as* useful as other IF EXISTS? No. Is it replaceable with a DO $$ statement? Yes. Is that a reason to block a fairly trivial patch which makes things 0.1% easier for users? No. Not if the patch itself is broken, that's another story. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Truncate if exists
On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? -- 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] Truncate if exists
On 10/10/12 09:35, Peter Eisentraut wrote: On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? INSERT IF NOT EXISTS ? -- 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] Truncate if exists
On 9 October 2012 21:35, Peter Eisentraut pete...@gmx.net wrote: On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? I'm not involved in the planning or justification for this patch, and have no opinion. I discussed applying it because it was an uncontentious patch. It clearly is not -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Truncate if exists
On Tue, Oct 9, 2012 at 2:04 PM, Simon Riggs si...@2ndquadrant.com wrote: On 9 October 2012 21:35, Peter Eisentraut pete...@gmx.net wrote: On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? I'm not involved in the planning or justification for this patch, and have no opinion. I discussed applying it because it was an uncontentious patch. It clearly is not I also read Simon's approach as not a push for inclusion, but defaulting to commit for smaller patches that basically look mechanically legitimate with no objections to streamline communication. Since pgsql-hackers has a good record objecting to patches that require objection in a timely manner, I think that's reasonable. The cost of revert would not be that high, either. Clearly those conditions were not met, but I don't think it's justified to jump on Simon for this approach on a patch like this. -- fdr -- 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] [GENERAL] pgxs problem...
John R Pierce pie...@hogranch.com writes: Further, it appears the link command pljava is using for the AIX case is given in its makefile as... $(COMPILER) $(LDFLAGS_NO_L) $(LDFLAGS_SL) -o $(plugin) $ -Wl,-bE:$(NAME)$(EXPSUFF) $(SHLIB_LINK) I can't find anywhere LDFLAGS_NO_L is defined. however, SHLIB_LINK is defined to concatenate PLJAVA_LDFLAGS, so I set that to -lm and POOF, its built. scary! Well, the reason I mentioned contrib/cube is that it's known to need libm. I see in its makefile SHLIB_LINK += $(filter -lm, $(LIBS)) so apparently that's the de rigueur way to add libm when you need it. I'd suggest pestering the pljava people to do likewise. They might be getting away without this on more-forgiving platforms, but that doesn't make it good practice to omit. So if I've not lost track, the scorecard is: 1. We need to install mkldexport.sh when on AIX, so that pgxs builds can use it. 2. Makefile.aix has the wrong idea about where to find postgres.imp when in pgxs mode. 3. pljava needs -lm and isn't explicitly asking for it. I will see about fixing the first two, but the third is on pljava to fix. (These aren't new bugs BTW --- it looks to me like this has been wrong since the pgxs code was created, in 8.1. I guess we don't have many AIX users :-() 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] replace plugins directory with GUC
About that plugins directory ($libdir/plugins) ... I don't think we ever really got that to work sensibly. I don't remember the original design discussion, but I have seen a number of explanations offered over the years. It's not clear who decides what to put in there (plugin author, packager, DBA?), how to put it there (move it, copy it, symlink it? -- no support in pgxs), and based on what criteria. It would seem to be much more in the spirit of things to simply list the allowed plugins in a GUC variable, like some_clever_name_here = $libdir/this, $libdir/that but there is probably a reason why this wasn't done that way in the first place. Anyway, the current setup stinks. Can we come up with something better? -- 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] [GENERAL] pgxs problem...
I wrote: So if I've not lost track, the scorecard is: 1. We need to install mkldexport.sh when on AIX, so that pgxs builds can use it. 2. Makefile.aix has the wrong idea about where to find postgres.imp when in pgxs mode. 3. pljava needs -lm and isn't explicitly asking for it. I will see about fixing the first two, but the third is on pljava to fix. I've committed a patch for the first two things: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bd0ef304f8a306522983f3b4b06274fdc45beed8 ... but not having an AIX machine, I can't actually test it. Would you verify it works? 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] Truncate if exists
On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus j...@agliodbs.com wrote: The second is for making deployment scripts idempotent. For example, say you have script A which creates table josh, and script B which needs table josh to be empty, if present. Since the two scripts are tied to different database features, and you don't know which one will be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can solve that problem with DO, but why make users go to the extra effort? Hmm. That's an interesting point. I think we're currently in somewhat of a limbo zone about where we ought to have IF EXISTS and IF NOT EXISTS options, and where we should not. Really, I'd like to figure out what policy we want to have, and then go make everything work that way. I don't exactly know what the policy should be, but if we don't have one then we're going to have to argue about every patch individually, which is already getting to be more than tedious. At the one extreme, you have Tom, who probably would not have added any of these given his druthers; at the other extreme, there are probably some people who would say we ought to have this for every command in the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR CREATE for good measure?). I'm not sure what the right thing to do is... but we should probably come up with some consensus position we can all live with, and then go make this uniform[1]. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company [1] And yes, I will volunteer to do some or all of the required implementation work, if that's helpful. Or else somebody else can do it. That's good, too. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers