Re: [HACKERS] 9.1.2 ?
2011-11-10 03:35 keltezéssel, Joshua D. Drake írta: On 11/09/2011 06:15 PM, Robert Haas wrote: 2011/11/9 Devrim GÜNDÜZdev...@gunduz.org: On Wed, 2011-11-09 at 21:12 -0500, Robert Haas wrote: The point is that all the packaging will be done *before* people leave to go eat Turkey. Eating me? :-) No, just your country. I hear it is a little dry. Especially on the throat, as the Koran forbids wine. :-) But that didn't prohibit turks enjoy wine in Hungary from 1526 to 1686, Hungary was occupied during that time by the turks. It's documented by some historian that their belief was that Allah listened in their heads and in their smartness they figured out that they just had to yell loudly. This way Allah scared off and ran into their legs and he didn't notice them drinking wine. :-D I didn't mean to offend you, Devrim ;-) -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Disable OpenSSL compression
Magnus Hagander wrote: Is the following proposal acceptable: - Add a GUC ssl_compression, defaulting to on. - Add a client option sslcompression and an environment variable PGSSLCOMPRESSION, defaulting to 1. Seems like the reasonable thing, yes. Compression will be disabled if either side refuses. I assume OpenSSL takes care of this for us, right? We just have to set the flags on the connection? Right. 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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
Hi José and Robert, thanks for your time and a review. Comments below.
On 11/10/11 03:47, Robert Haas wrote:
It does this already, without this patch. This patch is about CHECK
constraints, not UNIQUE ones.
That's right. This is how to check what the patch changes:
jkt= CREATE TABLE tbl (name TEXT PRIMARY KEY, a INTEGER CHECK (a0));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
tbl_pkey for table tbl
CREATE TABLE
jkt= INSERT INTO tbl (name, a) VALUES ('x', 10);
INSERT 0 1
jkt= UPDATE tbl SET a = -a;
ERROR: new row for relation tbl violates check constraint tbl_a_check
DETAIL: New row with data (x, -10) violates check constraint tbl_a_check.
The last line, the detailed error message, is added by the patch.
I believe we've previously rejected patches along these lines on the
grounds that the output could realistically be extremely long.
Imagine that you have a table with an integer primary key column and a
text column. The integer column has a check constraint on it. But
the text column might contain a kilobyte, or a megabyte, or even a
gigabyte worth of text, and we don't necessarily want to spit that all
out on an error. For unique constraints, we only emit the values that
are part of the constraint, which in most cases will be relatively
short (if they're more than 8kB, they won't fit into an index block);
but here the patch wants to dump the whole tuple, and that could be
really big.
That's an interesting thought. I suppose the same thing is an issue with
unique keys, but they tend to not be created over huge columns, so it
isn't really a problem, right?
Would you object to a patch which outputs just the first 8kB of each
column? Having at least some form of context is very useful in my case.
(And as a side note, I'm not really familiar with Postgres' internals,
so it took me roughly six hours to arrive to a working patch from the
very start. I'd therefore welcome pointers about the best way to achieve
that with Postgres' string stream interface.)
With kind regards,
Jan
--
Trojita, a fast e-mail client -- http://trojita.flaska.net/
signature.asc
Description: OpenPGP digital signature
Re: [HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
2011/11/10 Jan Kundrát j...@flaska.net:
On 11/10/11 03:47, Robert Haas wrote:
It does this already, without this patch. This patch is about CHECK
constraints, not UNIQUE ones.
That's right. This is how to check what the patch changes:
jkt= CREATE TABLE tbl (name TEXT PRIMARY KEY, a INTEGER CHECK (a0));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
tbl_pkey for table tbl
CREATE TABLE
jkt= INSERT INTO tbl (name, a) VALUES ('x', 10);
INSERT 0 1
jkt= UPDATE tbl SET a = -a;
ERROR: new row for relation tbl violates check constraint tbl_a_check
DETAIL: New row with data (x, -10) violates check constraint tbl_a_check.
The last line, the detailed error message, is added by the patch.
The patch uses 'New row with data ' but it was an UPDATE, if you
go further with this patch, IMO the message should be fixed too.
--
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
--
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: [patch] Include detailed information about a row failing a CHECK constraint into the error message
On 11/10/11 12:41, Dickson S. Guedes wrote: jkt= UPDATE tbl SET a = -a; ERROR: new row for relation tbl violates check constraint tbl_a_check DETAIL: New row with data (x, -10) violates check constraint tbl_a_check. The last line, the detailed error message, is added by the patch. The patch uses 'New row with data ' but it was an UPDATE, if you go further with this patch, IMO the message should be fixed too. I'm not sure whether the code can determine whether the check gets triggered by an UPDATE or an INSERT (both commands lead to this code path). Please note that the already-existing error message (the ERROR: line in the output I enclosed) already uses the phrase new row. That said, I'll of course be more than happy to include whatever string which fits better, and am open to any suggestions. Cheers, Jan -- Trojita, a fast e-mail client -- http://trojita.flaska.net/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
Hi, Ok, understood. PFA, a patch against git head. We take the AccessShareLock lock on the schema in DefineRelation now. Note that we do not want to interlock with other concurrent creations in the schema. We only want to interlock with deletion activity. So even performance wise this should not be much of an overhead in case of concurrent DDL operations to the same schema. Adding this in DefineRelation handles creation of tables/views/types/sequences. I do not think we need to do stuff in ALTER commands, because the objects pre-exist and this issue appears to be with new objects only. Comments? Regards, Nikhils git_head_lock_schema_ddl.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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
On Thu, Nov 10, 2011 at 5:40 AM, Jan Kundrát j...@flaska.net wrote: That's an interesting thought. I suppose the same thing is an issue with unique keys, but they tend to not be created over huge columns, so it isn't really a problem, right? Pretty much. Would you object to a patch which outputs just the first 8kB of each column? Having at least some form of context is very useful in my case. Well, if we're going to try to emit some context here, I'd suggest that we try to output only the columns implicated in the CHECK constraint, rather than the whole tuple. I'm not sure whether emitting only a certain amount of output (either total, or for each column) can be made to work nicely, or whether the feature overall is something we want. It seems like a trade-off between possibly useful context and possibly annoying log clutter, and I guess I don't have a strong opinion on which way to go with it. Anyone else have an opinion? -- 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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
On 11/10/11 13:04, Robert Haas wrote: Well, if we're going to try to emit some context here, I'd suggest that we try to output only the columns implicated in the CHECK constraint, rather than the whole tuple. I'm not sure whether emitting only a certain amount of output (either total, or for each column) can be made to work nicely, or whether the feature overall is something we want. It seems like a trade-off between possibly useful context and possibly annoying log clutter, and I guess I don't have a strong opinion on which way to go with it. OK, let me start with some background on why I actually want to have such a feature. The project which we're working on [1] (and [2] for some context about why the hell we bother) allows users to define layout of their DB tables using standard CREATE TABLE ... stanzas, including various triggers, check constraints etc etc. What our project does is generating plenty of stored procedures which essentially built a version-control infrastructure around the user-specified table layout. Our workflow utilizes something similar to the concept of a working copy in Subversion. It means that any modifications that users perform are executed on an extra table (the history one) which does not enforce any user-specified constraints. It's only at the time of a commit, where data is moved by `UPDATE tabl SELECT ... FROM tbl_history where revision = $pending_changeset` to its final destination and all the checks, triggers and constraints are enforced. The issue which we've hit is that when the user has specified a CHECK constraint and tries to save many rows at once, we don't have any information about what went wrong besides the name of the check which failed. It's better than nothing, but given that Pg provides very similar information for UNIQUE columns, it looked like a good feature to implement. What I want to find in the end is something which tells me this row causes the error. Unfortunately, as the new row of the table with the constraint is not yet on disk, it doesn't really have its own ctid, and therefore I cannot report that. (Which makes sense, obviously.) I also realize that our use case is a bit esoteric and very far from the mainstream Postgres applications, but I believe that simply having detailed error messages is a good thing overall. Of course it's clearly possible that we're doing it completely wrong, so if someone has a suggestion or would like to chat about that, I'm all ears (feel free to go off-list here). Now I realize that there might be some concerns about error log cluttering etc. On the other hand, I'd take it for granted that it's a good idea to include at least *some* context in the error messages (and I assume that's what the detail field is for). If it's acceptable for UNIQUE constraints to show the index values (which are enough to identify the troublesome row), it seems to me that extending this to CHECKs is a natural further development and leads to better consistency. As I've said earlier, I'm not at all familiar with Postgres' internals, so before I go ahead and spend another night finding out how to look at the table/check metadata and print just the columns which are referenced by a CHECK, if that's even possible, I'd like to know whether such a patch would be welcome and accepted or not :). Again, a big thank you for your review -- it's much appreciated. Cheers, Jan [1] https://projects.flaska.net/projects/deska [2] https://projects.flaska.net/attachments/download/74/2011-11-10-deska-18e4c5b.pdf -- Trojita, a fast e-mail client -- http://trojita.flaska.net/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Disable OpenSSL compression
On 11/08/2011 12:39 PM, Tom Lane wrote: Jeroen Vermeulenj...@xs4all.nl writes: Another reason why I believe compression is often used with encryption is to maximize information content per byte of data: harder to guess, harder to crack. Would that matter? Yes, it would. There's a reason why the OpenSSL default is what it is. An interesting data point on this is that RedHat's nss_compat_ossl package doesn't support SSL compression at all http://fedoraproject.org/wiki/Nss_compat_ossl, and it's supposed to be a path to FIPS 140 compliance: http://fedoraproject.org/wiki/FedoraCryptoConsolidation. The latter URL, incidentally, contains a lot of good information, and lays out many of the reasons why I'd like to see us support NSS as an alternative to OpenSSL, notwithstanding the supposed dirtiness of its API. I imagine this would be of interest to commercial Postgres vendors also. 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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
What I want to find in the end is something which tells me this row causes the error. Unfortunately, as the new row of the table with the constraint is not yet on disk, it doesn't really have its own ctid, and therefore I cannot report that. (Which makes sense, obviously.) Would an error with the row's PK value be useful? Something like row with primary key 'pk_val' fails check 'foo_check'. That would be limited in size, yet give some context. There are two problems I can see: - The PK value doesn't necessarily identify the row in any useful manner (SERIAL primary key in INSERT). - The table might lack PK constraint (skip the detail in this case?) - Anssi -- 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: [patch] Include detailed information about a row failing a CHECK constraint into the error message
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 5:40 AM, Jan Kundrát j...@flaska.net wrote: Would you object to a patch which outputs just the first 8kB of each column? Having at least some form of context is very useful in my case. Well, if we're going to try to emit some context here, I'd suggest that we try to output only the columns implicated in the CHECK constraint, rather than the whole tuple. I think that's likely to be impractical, or at least much more trouble than the feature is worth. Also, if you might emit only a subset of columns, then you have to label them, a la the FK error messages: Key (x,y,z) = (this,that,theother) That's going to make the line length problem worse not better. I concur with just length-limiting the dumped values, and in fact would prefer a limit much more draconian than 8K. Don't we limit the key lengths to 1K or so in FK and unique-key messages? If the goal is to identify the problematic line, I would think that a few dozen bytes per column would be plenty. I'm not sure whether emitting only a certain amount of output (either total, or for each column) can be made to work nicely, or whether the feature overall is something we want. It seems like a trade-off between possibly useful context and possibly annoying log clutter, and I guess I don't have a strong opinion on which way to go with it. I agree with Jan that this is probably useful; I'm pretty sure there have been requests for it before. We just have to make sure that the length of the message stays in bounds. One tip for keeping the length down: there is no value in repeating information from the primary error message, such as the name of the constraint. 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] Disable OpenSSL compression
On Thursday, November 10, 2011, Andrew Dunstan wrote: On 11/08/2011 12:39 PM, Tom Lane wrote: Jeroen Vermeulenj...@xs4all.nl writes: Another reason why I believe compression is often used with encryption is to maximize information content per byte of data: harder to guess, harder to crack. Would that matter? Yes, it would. There's a reason why the OpenSSL default is what it is. An interesting data point on this is that RedHat's nss_compat_ossl package doesn't support SSL compression at all http://fedoraproject.org/** wiki/Nss_compat_ossl http://fedoraproject.org/wiki/Nss_compat_ossl, and it's supposed to be a path to FIPS 140 compliance: http://fedoraproject.org/**wiki/FedoraCryptoConsolidationhttp://fedoraproject.org/wiki/FedoraCryptoConsolidation **. The latter URL, incidentally, contains a lot of good information, and lays out many of the reasons why I'd like to see us support NSS as an alternative to OpenSSL, notwithstanding the supposed dirtiness of its API. I imagine this would be of interest to commercial Postgres vendors also. Interesting points. I hadn't really considered it from the FIPS perspective. I thought the main idea was that if we want to support another one it's probably going to be GnuTLS because that one offers key-file-compatibility with OpenSSL, which NSS doesnät. //Magnus -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] Disable OpenSSL compression
Tom Lane wrote: Is the following proposal acceptable: - Add a GUC ssl_compression, defaulting to on. - Add a client option sslcompression and an environment variable PGSSLCOMPRESSION, defaulting to 1. A GUC is entirely, completely, 100% the wrong answer. It has no way to deal with the fact that some clients may need compression and others not. If you leave the GUC at its default value, you can control compression on the client side. You can force a certain SSL cipher on the client, why not a compression setting? It should be a client option, full stop. The fact that that will be more work to implement does not make kluge it at the server the right answer. I could go and try to convince Npgsql and JDBC to accept patches to do that on the client side, but that would be more effort than I want to invest. But then there's still closed source software like Devart dotConnect... In my environment it would make sense to control the setting on the server side, because all our database clients connect via LAN, and network bandwidth is not the bottleneck in our database applications. 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] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
On Thu, Nov 10, 2011 at 10:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 5:40 AM, Jan Kundrát j...@flaska.net wrote: Would you object to a patch which outputs just the first 8kB of each column? Having at least some form of context is very useful in my case. Well, if we're going to try to emit some context here, I'd suggest that we try to output only the columns implicated in the CHECK constraint, rather than the whole tuple. I think that's likely to be impractical, or at least much more trouble than the feature is worth. Also, if you might emit only a subset of columns, then you have to label them, a la the FK error messages: Key (x,y,z) = (this,that,theother) That's going to make the line length problem worse not better. Depends. A lot of CHECK constraints may only reference one column: CHECK (a 0). The whole record is likely to be a lot longer than (a)=(-32768), and frankly tuples without column names aren't that readable anyway. I'd argue that to some degree, CHECK constraints, like UNIQUE constraints, probably tend to be placed primarily on relatively short columns. Now, UNIQUE constraints have a hard limitation, because a too-large value won't fit into an index block. And certainly you could do CHECK (document_is_valid_json(mumbleblump)). But many things that contain large amounts of text will just be free text fields, they won't be part of any constraint, and including them will just make things unreadable. -- 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
[HACKERS] MPI programming in postgreSQL backend source code
do you have more documentation about OPENMP and PostgreSQL? El 09-11-2011 20:12, Greg Smith escribió: On 11/09/2011 04:10 PM, Rudyar Cortés wrote: I'm a new programmer in postgreSQL source code.. Is possible use MPI functions in postgreSQL source code? To do this the proper way, you would need to modify the database's configure step to: -Check if the OpenMPI libraries are available and include the necessary bits. For example, this is in one of the projects I work on: #ifdef _OPENMP extern int omp_get_num_threads(); #endif Some form of that test and defining the functions available would be needed for what you want. -Link OpenMPI in. At the gcc level you'll need -fopenmp to start. Then you could start using OpenMPI functions in database code. You might hack the build steps to do this in a simpler way, too, rather than fight with configure the proper way. Since a lot of the MPI functions aim at specific types of thread use and I/O, it would be a major effort to utilize the library for too many things. The existing notion of how processes are created and managed is scattered throughout the PostgreSQL code. And the I/O parts of the database are buried through a few layers of indirection. -- 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
Nikhil Sontakke nikkh...@gmail.com writes: PFA, a patch against git head. We take the AccessShareLock lock on the schema in DefineRelation now. Um ... why would we do this only for tables, and not for creations of other sorts of objects that belong to schemas? Also, if we are going to believe that this is a serious problem, what of ALTER ... SET SCHEMA? Also, the proposed solution is pretty silly on its face, because it has not removed the race condition only made the window somewhat narrower. You would have to acquire the lock as part of the initial schema lookup, not lock the OID after the fact. And could we please not do something as silly as translate the OID back to a string and then look up that string a second time? (To be clear, I don't particularly believe that this is a problem worthy of spending code space and cycles on. But if it's deemed to be a problem, I want to see a solution that's actually watertight.) 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: [patch] Include detailed information about a row failing a CHECK constraint into the error message
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 10:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Well, if we're going to try to emit some context here, I'd suggest that we try to output only the columns implicated in the CHECK constraint, rather than the whole tuple. I think that's likely to be impractical, or at least much more trouble than the feature is worth. Also, if you might emit only a subset of columns, then you have to label them, a la the FK error messages: Key (x,y,z) = (this,that,theother) That's going to make the line length problem worse not better. Depends. A lot of CHECK constraints may only reference one column: CHECK (a 0). The whole record is likely to be a lot longer than (a)=(-32768), and frankly tuples without column names aren't that readable anyway. Well, the other concern here is: how much context does it take to identify the problematic row? It's entirely likely that showing only the value of a isn't enough to solve the user's problem anyhow. So I think the argument for showing a subset of columns is quite weak. 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: [patch] Include detailed information about a row failing a CHECK constraint into the error message
On 11/10/11 16:05, Tom Lane wrote:
I agree with Jan that this is probably useful; I'm pretty sure there
have been requests for it before. We just have to make sure that the
length of the message stays in bounds.
One tip for keeping the length down: there is no value in repeating
information from the primary error message, such as the name of the
constraint.
Thanks to your comments and suggestions, I appreciate the time of the
reviewers.
Attached is a second version of this patch which keeps the size of the
output at 64 characters per column (which is an arbitrary value defined
as a const int, which I hope matches your style). Longer values have
their last three characters replaced by ..., so there's no way to
distinguish them from a legitimate string that ends with just that.
There's also no escaping of special-string values, similar to how the
BuildIndexValueDescription operates.
Cheers,
Jan
--
Trojita, a fast e-mail client -- http://trojita.flaska.net/
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 504f4de..9c2b285 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1364,10 +1364,42 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
const char *failed;
if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) !=
NULL)
+ {
+ StringInfoData buf;
+ int natts = rel-rd_att-natts;
+ int i;
+ initStringInfo(buf);
+ for (i = 0; i natts; ++i)
+ {
+ char *val;
+ Oid foutoid;
+ bool typisvarlena;
+ size_t fieldlen;
+ const int cutofflen = 64;
+
getTypeOutputInfo(rel-rd_att-attrs[i]-atttypid, foutoid, typisvarlena);
+ if (slot-tts_isnull[i])
+ val = NULL;
+ else
+ val = OidOutputFunctionCall(foutoid,
slot-tts_values[i]);
+ if (i 0)
+ appendStringInfoString(buf, , );
+ fieldlen = strlen(val);
+ if (fieldlen cutofflen)
+ {
+ appendBinaryStringInfo(buf, val,
cutofflen - 3);
+ appendStringInfoString(buf, ...);
+ }
+ else
+ {
+ appendStringInfoString(buf, val);
+ }
+ }
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg(new row for relation \%s\
violates check constraint \%s\,
-
RelationGetRelationName(rel), failed)));
+
RelationGetRelationName(rel), failed),
+errdetail(Failing row: (%s).,
buf.data)));
+ }
}
}
signature.asc
Description: OpenPGP digital signature
Re: [HACKERS] Disable OpenSSL compression
Albe Laurenz laurenz.a...@wien.gv.at writes: Tom Lane wrote: A GUC is entirely, completely, 100% the wrong answer. It has no way to deal with the fact that some clients may need compression and others not. You can force a certain SSL cipher on the client, why not a compression setting? To my mind, the argument for the ssl_cipher setting is to allow the DBA to enforce a site-wide security policy to the effect of we consider only these ciphers strong enough for production use. It's a pretty weak argument (especially since the setting is not cognizant of where the connection is coming from), but at least it's an argument. There's no comparable security argument for an ssl_compression setting. If anything, a security-minded DBA might wish to insist on compression being *on*, but you aren't proposing to give him control in that direction (and AFAICT openssl doesn't offer a force-on flag for it). But in any case, my objection is that there's no adequate use-case for this GUC, because it's much more sensible to set it from the client side. We have too many GUCs already --- Josh B regularly goes on the warpath looking for ones we can remove. This one should never get in there to start with. I could go and try to convince Npgsql and JDBC to accept patches to do that on the client side, but that would be more effort than I want to invest. But then there's still closed source software like Devart dotConnect... This argument reads as nothing except I'm too lazy to solve it right, so I want you to accept a wrong solution. 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] Disable OpenSSL compression
On Thu, Nov 10, 2011 at 11:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: But in any case, my objection is that there's no adequate use-case for this GUC, because it's much more sensible to set it from the client side. We have too many GUCs already --- Josh B regularly goes on the warpath looking for ones we can remove. This one should never get in there to start with. Of course, we also have no shortage of connection parameters. -- 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
[HACKERS] Parsing output of EXPLAIN command in PostgreSQL
I'm developing an application that requires parsing of execution plans (those produced as output by issuing an EXPLAIN [query] command). Are you aware of any Java library that I could use for this purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1. However, in that case it would better to have the XML Schema of the generated plans available. Is there any other solution I am not aware of? Thank you -- 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] Disable OpenSSL compression
On Thu, Nov 10, 2011 at 5:18 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote: I could go and try to convince Npgsql and JDBC to accept patches to do that on the client side, but that would be more effort than I want to invest. But then there's still closed source software like Devart dotConnect... Are you certain Java/C# even support SSL compression? Quick grep over some old jdk code I had around did not find it... In any case, the connection libraries do not need to match such optional features. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Parsing output of EXPLAIN command in PostgreSQL
I'm developing an application that requires parsing of execution plans (those produced as output by issuing an EXPLAIN [query] command). Are you aware of any Java library that I could use for this purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1. However, in that case it would better to have the XML Schema of the generated plans available. Is there any other solution I am not aware of? Thank you -- 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
Um ... why would we do this only for tables, and not for creations of other sorts of objects that belong to schemas? Right, we need to do it for other objects like functions etc. too. Also, if we are going to believe that this is a serious problem, what of ALTER ... SET SCHEMA? I admit, I hadn't thought of this. Also, the proposed solution is pretty silly on its face, because it has not removed the race condition only made the window somewhat narrower. You would have to acquire the lock as part of the initial schema lookup, not lock the OID after the fact. And could we please not do something as silly as translate the OID back to a string and then look up that string a second time? The comment mentions that part is a kluge but that we get to re-use the existing function because of it. The get_object_address function will bail out anyways if the schema has vanished from down under and it does lock it up immediately after it's found to be valid. (To be clear, I don't particularly believe that this is a problem worthy of spending code space and cycles on. But if it's deemed to be a problem, I want to see a solution that's actually watertight.) Got the message. Regards, Nikhils
Re: [HACKERS] warning in pg_upgrade
Robert Haas wrote: On Thu, Nov 3, 2011 at 3:45 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Untested patch attached for purposes of discussion. I got in a little testing on it -- not only does this patch eliminate the compile-time warning, but if you try to run pg_upgrade when another session has removed your current working directory, you get a reasonable message instead of the program attempting to proceed with undefined (potential garbage) for a working directory. Committed. Also fixed another compiler warning that popped up for me. Thanks Kevin and Robert. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)
On 11-11-09 06:35 PM, Tom Lane wrote:
Steve Singerssin...@ca.afilias.info writes:
I've tracked the issue down to collectSecLabels in pg_dump.c
SELECT label, provider, classoid, objoid, objsbid FROM
pg_catalog.pg_seclabel;
returns 0 rows.
The code in collectSecLabels() is not prepared to deal with a zero row
result and tries to malloc 0 bytes.
pg_seclabel is almost always empty, so I'm not convinced that you've
identified your problem correctly.
regards, tom lane
The attached patch seems to fix the issue.
The man page for malloc on AIX is pretty clear on what happens when you
try to malloc 0 bytes. It returns NULL.
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index fce9d3b..9e31767 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*** findSecLabels(Archive *fout, Oid classoi
*** 11760,11766
/* Get security labels if we didn't already */
if (nlabels 0)
nlabels = collectSecLabels(fout, labels);
!
/*
* Do binary search to find some item matching the object.
*/
--- 11760,11770
/* Get security labels if we didn't already */
if (nlabels 0)
nlabels = collectSecLabels(fout, labels);
! if (nlabels == 0)
! {
! *items=NULL;
! return 0;
! }
/*
* Do binary search to find some item matching the object.
*/
*** collectSecLabels(Archive *fout, SecLabel
*** 11858,11875
i_objsubid = PQfnumber(res, objsubid);
ntups = PQntuples(res);
!
! labels = (SecLabelItem *) malloc(ntups * sizeof(SecLabelItem));
!
! for (i = 0; i ntups; i++)
{
! labels[i].label = PQgetvalue(res, i, i_label);
! labels[i].provider = PQgetvalue(res, i, i_provider);
! labels[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
! labels[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
! labels[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
}
/* Do NOT free the PGresult since we are keeping pointers into it */
destroyPQExpBuffer(query);
--- 11862,11889
i_objsubid = PQfnumber(res, objsubid);
ntups = PQntuples(res);
! if ( ntups == 0)
{
! labels = NULL;
}
+ else
+ {
+ labels = (SecLabelItem *) malloc(ntups * sizeof(SecLabelItem));
+ if (labels == NULL )
+ {
+ write_msg(NULL, out of memory);
+ exit(1);
+ }
+ for (i = 0; i ntups; i++)
+ {
+ labels[i].label = PQgetvalue(res, i, i_label);
+ labels[i].provider = PQgetvalue(res, i, i_provider);
+ labels[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
+ labels[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
+ labels[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
+ }
+ }
/* Do NOT free the PGresult since we are keeping pointers into it */
destroyPQExpBuffer(query);
--
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] IDLE in transaction introspection
On Nov 5, 2011 9:02 AM, Greg Smith g...@2ndquadrant.com wrote: On 11/04/2011 05:01 PM, Tom Lane wrote: Scott Meadsco...@openscg.com writes: I leave the waiting flag in place for posterity. With this in mind, is the consensus: RUNNING or ACTIVE Personally, I'd go for lower case. I was thinking it would be nice if this state looked like the WAL sender state values in pg_stat_replication, which are all lower case. For comparison those states are: startup backup catchup streaming +1, it'll be easier to query against. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] IDLE in transaction introspection
Scott Mead wrote: On Wed, Nov 2, 2011 at 4:12 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Andrew Dunstan wrote: On 11/01/2011 09:52 AM, Tom Lane wrote: I'm for just redefining the query field as current or last query. +1 I could go either way on whether to rename it. Rename it please. current_query will just be wrong. I'd be inclined just to call it query or query_string and leave it to the docs to define the exact semantics. +1 for renaming, +1 for a state column. I think it is overkill to keep a query history beyond that -- if you want that, you can resort to the log files. ISTM that we're all for: creating a new column: state renaming current_query = query State will display RUNNING, IDLE, IDLE in transaction, etc... query will display the last query that was executed. I've written this up in the attached patch, looking for feedback. (NB: Originally I was using 9.1.1 release, I just did a git clone today to generate this). It might be cleaner to use booleans: active: t/f in transaction: t/f or maybe instead of 'active': idle: t/f in transaction: t/f That avoids the magic string values for the state column. Those are much easier to query against too: WHERE NOT idle; -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)
Steve Singer ssin...@ca.afilias.info writes: The man page for malloc on AIX is pretty clear on what happens when you try to malloc 0 bytes. It returns NULL. Yes, that's a pretty common behavior for malloc(0). It should not cause a problem here AFAICS. ... Oh, I see, the problem is that labels[-1] might not compare to labels[0] the way we want. I think only the first hunk of your patch is actually necessary. 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] IDLE in transaction introspection
Bruce Momjian br...@momjian.us writes: It might be cleaner to use booleans: active: t/f in transaction: t/f I don't think so, because that makes some very strict assumptions that there are exactly four interesting states (an assumption that isn't even true today, to judge by the activity strings we're using now). 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] IDLE in transaction introspection
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: It might be cleaner to use booleans: active: t/f in transaction: t/f I don't think so, because that makes some very strict assumptions that there are exactly four interesting states (an assumption that isn't even true today, to judge by the activity strings we're using now). Well, we could use an optional details string for that. If not, we are still using the magic-string approach, which I thought we didn't like. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Is there a good reason we don't have INTERVAL 'infinity'?
Brar Piening wrote: Josh Berkus wrote: Hackers, Is there a reason why INTERVAL 'infinity' is not implemented? That is, an interval which is larger than all defined intervals, and which added to any timestamp turns it into 'infinity'. Or is it just Round TUITs? Probably the latter. There is even a function |isfinite(interval)| which doesn't seem to do anything useful. See complaint in http://archives.postgresql.org/message-id/200101241913.f0ojduu45...@hub.org Although the operation used in this complaint isn't obviously defined there certainly are operations that are defined like infinity + infinity = infinity. See http://de.wikipedia.org/wiki/Unendlichkeit#Analysis (Sorry for linking the german wikipedia - the english text is ways less verbose on this.) TODO has: Allow infinite intervals just like infinite timestamps -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] const correctness
Florian Pflug f...@phlo.org wrote: On Nov9, 2011, at 22:54 , Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: I don't doubt that just duplicating macros and inlineable functions is a wash performance-wise (in fact, in principle it shouldn't change the generated code at all). I had the impression that compilers these days could sometimes better optimize across calls to functions with const parameters, because previously-referenced elements of the structures could be trusted to be unchanged across the call. I'm not talking about calls to the inlineable function or macros themselves, but the higher level functions which can then use const. I don't think that's true. Const (for pointer types) generally only means you cannot modify the value through *this* pointer. But there may very well be other pointers to the same object, and those may very well be used to modify the value at any time. So unless both the calling and the called function are in the same compilation unit, the compiler needs to assume that any non-local (and even local values whose address was taken previously) value in the calling function may change as a result of the function call. Or at least I think so. You two seem to be right. I checked some generated code where I would have expected it to help if it was ever going to, and the generated code was absolutely identical. It appears that the *only* real argument for this is to document the function's contract. Whether the benefit of that outweighs any distraction it causes seems to be the key argument to be had here. If we're concerned about helping the compiler produce better code, I think we should try to make our code safe under strict aliasing rules. AFAIK, that generally helps much more than const-correctness. (Dunno how feasible that is, though) I hacked my configure file to use strict aliasing and -O3, and my usual set of regression tests passed. (make check-world, make installcheck-world against a cluster with default_transaction_isolation = 'serializable' and max_prepared_transactions = 10, and make -C src/test/isolation installcheck against the same cluster) I did get 10 warnings like this: warning: dereferencing type-punned pointer will break strict-aliasing rules I haven't yet compared code or run benchmarks. Since 9.2 seems to be shaping up mainly as a performance release, now might be a good time to review these compile options to see how far we can now safely push them. -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] IDLE in transaction introspection
Bruce Momjian br...@momjian.us writes: Well, we could use an optional details string for that. If not, we are still using the magic-string approach, which I thought we didn't like. No, we're not using magic strings, we're using an enum --- maybe not an officially declared enum type, but it's a column with a predetermined set of possible values. It would be a magic string if it were still in the query field and thus confusable with user-written queries. 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] const correctness
On ons, 2011-11-09 at 10:49 -0500, Tom Lane wrote: Now admittedly you can hack it, in the same spirit as the C library functions that are declared to take const pointers and return non-const pointers to the very same data Which C library functions do that? -- 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] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)
On 11-11-10 02:00 PM, Tom Lane wrote: Steve Singerssin...@ca.afilias.info writes: The man page for malloc on AIX is pretty clear on what happens when you try to malloc 0 bytes. It returns NULL. Yes, that's a pretty common behavior for malloc(0). It should not cause a problem here AFAICS. ... Oh, I see, the problem is thatlabels[-1] might not compare to labels[0] the way we want. I think only the first hunk of your patch is actually necessary. regards, tom lane Yes the problem is still fixed if I only apply the first hunk. -- 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] -Wcast-qual cleanup, part 1
On mån, 2011-11-07 at 10:07 -0500, Tom Lane wrote: 2. Macros accessing structures should come in two variants: a get version, and a set/anything else version, so that the get version can preserve the const qualifier. I'm not prepared to buy into that as a general coding rule. Maybe it would be better to just add -Wno-cast-qual to CFLAGS. OK, I understand the concerns that have been raised here and in the other thread. I'll work instead on removing lying const qualifiers on the upper layers that were the causes of attempting to push the consts down. -- 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] const correctness
Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-11-09 at 10:49 -0500, Tom Lane wrote: Now admittedly you can hack it, in the same spirit as the C library functions that are declared to take const pointers and return non-const pointers to the very same data Which C library functions do that? Tom mentioned the strchr() function, which does do that. I don't actually find that surprising given my understanding of the semantics. That means that the function is promising not to modify the character array, but is not asserting that it knows the character array to be immutable. Makes sense to me. It's up to the caller to assign it to a const char * if it knows it passed in an immutable object. -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] foreign key locks, 2nd attempt
Alvaro Herrera wrote: Hello, After some rather extensive rewriting, I submit the patch to improve foreign key locks. To recap, the point of this patch is to introduce a new lock tuple mode, that lets the RI code obtain a lighter lock on tuples, which doesn't conflict with updates that do not modify the key columns. What kind of operations benefit from a non-key lock like this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] const correctness
Peter Eisentraut pete...@gmx.net writes: On ons, 2011-11-09 at 10:49 -0500, Tom Lane wrote: Now admittedly you can hack it, in the same spirit as the C library functions that are declared to take const pointers and return non-const pointers to the very same data Which C library functions do that? strchr() is the classic example, but I believe there are some others. 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] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)
Steve Singer ssin...@ca.afilias.info writes: On 11-11-10 02:00 PM, Tom Lane wrote: ... Oh, I see, the problem is thatlabels[-1] might not compare to labels[0] the way we want. I think only the first hunk of your patch is actually necessary. Yes the problem is still fixed if I only apply the first hunk. OK, everything seems satisfactorily explained then. Will commit the fix, thanks for the report! 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] const correctness
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom mentioned the strchr() function, which does do that. I don't actually find that surprising given my understanding of the semantics. That means that the function is promising not to modify the character array, but is not asserting that it knows the character array to be immutable. Makes sense to me. It's up to the caller to assign it to a const char * if it knows it passed in an immutable object. The problem with it of course is that mistaken use could have the effect of casting-away-const, which is exactly what we hoped to prevent. Still, there may not be a better solution. 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] pl/python custom datatype parsers
On tis, 2011-11-08 at 16:08 -0500, Andrew Dunstan wrote: On 03/01/2011 11:50 AM, Peter Eisentraut wrote: On fre, 2011-02-11 at 16:49 +0100, Jan Urbański wrote: I believe it's (b). But as we don't have time for that discussion that late in the release cycle, I think we need to consider it identical to (c). As I previously mentioned, I think that there should be an SQL-level way to tie together languages and types. I previously mentioned the SQL-standard command CREATE TRANSFORM as a possibility. I've had this on my PL/Python TOTHINK list for a while. Thankfully you removed all the items ahead of this one, so I'll think of something to do in 9.2. Of course we'll be able to use the actual transform code that you already wrote. Peter, Did you make any progress on this? No, but it's still somewhere on my list. I saw your blog post related to this. I think the first step would be to set up some catalog infrastructure (without DDL commands and all that overhead), and try to adapt the big case statement of an existing language to that, and then check whether that works, performance, etc. Some other concerns of the top of my head: - Arrays: Would probably not by handled by that. So this would not be able to handle, for example, switching the array handling behavior in PL/Perl to ancient compatible mode. - Range types: no idea I might work on this, but not before December, would be my guess. -- 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] type privileges and default privileges
On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Let me put this differently. Should we either continue to hardcode the default privileges in the acldefault() function, or should we instead initialize the system catalogs with an entry in pg_default_acl as though ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been executed? If you're proposing to replace acldefault() with a catalog lookup, I vote no. I think that's a performance hit with little redeeming social value. No, I'm pondering having pg_default_acl initialized so that newly created types have explicit USAGE privileges in their typacl column, so acldefault() wouldn't be needed. (And builtin types would have their typacl initialized analogously.) I suppose this is how we might have done it if we had invented ALTER DEFAULT PRIVILEGES first. -- 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] foreign key locks, 2nd attempt
On Sun, Nov 6, 2011 at 2:28 AM, Jeroen Vermeulen j...@xs4all.nl wrote: On 2011-11-04 01:12, Alvaro Herrera wrote: I would like some opinions on the ideas on this patch, and on the patch itself. If someone wants more discussion on implementation details of each part of the patch, I'm happy to provide a textual description -- please just ask. Jumping in a bit late here, but thanks for working on this: it looks like it could solve some annoying problems for us. I do find myself idly wondering if those problems couldn't be made to go away more simply given some kind of “I will never ever update this key” constraint. I'm having trouble picturing the possible lock interactions as it is. :-) +1 on that, though I'd make it more general than that. There's value in having an immutability constraint on a column, where, in effect, you're not allowed to modify the value of the column, once assigned. That certainly doesn't prevent issuing DELETE + INSERT to get whatever value you want into place, but that's a big enough hoop to need to jump through to get rid of some nonsensical updates. And if the target of a foreign key constraint consists of immutable columns, then, yes, indeed, UPDATE on that table no longer conflicts with references. In nearly all cases, I'd expect that SERIAL would be reasonably followed by IMMUTABLE. create table something_assigned ( something_id serial immutable primary key, something_identifier text not null unique ); -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] foreign key locks, 2nd attempt
2011/11/10 Christopher Browne cbbro...@gmail.com: On Sun, Nov 6, 2011 at 2:28 AM, Jeroen Vermeulen j...@xs4all.nl wrote: On 2011-11-04 01:12, Alvaro Herrera wrote: I would like some opinions on the ideas on this patch, and on the patch itself. If someone wants more discussion on implementation details of each part of the patch, I'm happy to provide a textual description -- please just ask. Jumping in a bit late here, but thanks for working on this: it looks like it could solve some annoying problems for us. I do find myself idly wondering if those problems couldn't be made to go away more simply given some kind of “I will never ever update this key” constraint. I'm having trouble picturing the possible lock interactions as it is. :-) +1 on that, though I'd make it more general than that. There's value in having an immutability constraint on a column, where, in effect, you're not allowed to modify the value of the column, once assigned. That certainly doesn't prevent issuing DELETE + INSERT to get whatever value you want into place, but that's a big enough hoop to need to jump through to get rid of some nonsensical updates. And if the target of a foreign key constraint consists of immutable columns, then, yes, indeed, UPDATE on that table no longer conflicts with references. In nearly all cases, I'd expect that SERIAL would be reasonably followed by IMMUTABLE. create table something_assigned ( something_id serial immutable primary key, something_identifier text not null unique ); I like this idea - it can solve two problem Regards Pavel Stehule -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] pg_upgrade automatic testing
Peter Eisentraut wrote: On m?n, 2011-09-19 at 07:06 +0300, Peter Eisentraut wrote: I found a simpler way to get this working. Just hack up the catalogs for the new path directly. So I can now run this test suite against older versions as well, like this: contrib/pg_upgrade$ make installcheck oldsrc=somewhere oldbindir=elsewhere Any comments on how to proceed with this? I think it has been useful in detecting pg_upgrade breakage a few times already, so I'd like to commit it and start using it. I don't have a problem with adding it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] const correctness
Tom Lane t...@sss.pgh.pa.us wrote: The problem with it of course is that mistaken use could have the effect of casting-away-const, which is exactly what we hoped to prevent. Still, there may not be a better solution. Yeah, I've come to the conclusion that the compiler doesn't do the apparently-available optimizations using const precisely because it is so easy to cast away the property maliciously or accidentally. -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] foreign key locks, 2nd attempt
Christopher Browne cbbro...@gmail.com wrote: There's value in having an immutability constraint on a column, where, in effect, you're not allowed to modify the value of the column, once assigned. +1 We would definitely use such a feature, should it become available. -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] foreign key locks, 2nd attempt
On Thu, Nov 10, 2011 at 3:29 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Christopher Browne cbbro...@gmail.com wrote: There's value in having an immutability constraint on a column, where, in effect, you're not allowed to modify the value of the column, once assigned. +1 We would definitely use such a feature, should it become available. Added to TODO list. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] proposal: psql concise mode
Robert Haas wrote: On Sun, Nov 6, 2011 at 3:29 PM, Josh Kupershmidt schmi...@gmail.com wrote: On Sun, Nov 6, 2011 at 1:16 PM, Dickson S. Guedes lis...@guedesoft.net wrote: test=# \d+ foo ? ? ? ? ? ? ? ? ? ? ? ? Table public.foo ?Column | ?Type ? | Storage +-+- ?a ? ? ?| integer | plain ?b ? ? ?| integer | plain Has OIDs: no Using your example, what if column 'b' has a comment and 'a' not? How the above output will be displayed? Then the comments would be displayed as they previously were, like so: ? ? ? ? ? ? ? ? ? ? ? ? Table public.foo ?Column | ?Type ? | Storage | Description +-+-+- ?a ? ? ?| integer | plain ? | ?b ? ? ?| integer | plain ? | some comment Has OIDs: no I don't strongly object to this, but I wonder how useful it will really be in practice. It strikes me as the sort of advanced psql hackery that only a few people will use, and only some of those will gain any benefit. Empty columns don't really take up that much screen width, and even one value in any given column will require its inclusion anyway. I can also see myself turning it on and then going - oh, wait, is that column not there, or did it just disappear because I'm in concise mode? Not saying we shouldn't do it, just some food for thought. Have you tried \d+ with this psql mode: \pset format wrapped It wraps the data so it fits on the screen --- it is my default in my .psqlrc. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] foreign key locks, 2nd attempt
Excerpts from Bruce Momjian's message of jue nov 10 16:59:20 -0300 2011: Alvaro Herrera wrote: Hello, After some rather extensive rewriting, I submit the patch to improve foreign key locks. To recap, the point of this patch is to introduce a new lock tuple mode, that lets the RI code obtain a lighter lock on tuples, which doesn't conflict with updates that do not modify the key columns. What kind of operations benefit from a non-key lock like this? I'm not sure I understand the question. With this patch, a RI check does SELECT FOR KEY SHARE. This means the tuple is locked with that mode until the transaction finishes. An UPDATE that modifies the referenced row will not conflict with that lock. An UPDATE that modifies the key columns will be blocked, just as now. Same with a DELETE. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] const correctness
Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: The problem with it of course is that mistaken use could have the effect of casting-away-const, which is exactly what we hoped to prevent. Still, there may not be a better solution. Yeah, I've come to the conclusion that the compiler doesn't do the apparently-available optimizations using const precisely because it is so easy to cast away the property maliciously or accidentally. Right. The compiler would have to look at the function code, and all functions called by that function, to determine if const was honored --- not something that is easily done. I agree that the strchr() approach is best. I realize the patch only added 1-2 new const functions, but this is only a small area of the code being patched --- a full solution would have many more complex duplicates, and awkward changes as we add features. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] unaccent extension missing some accents
Tom Lane wrote: J Smith dark.panda+li...@gmail.com writes: I've attached a patch against master for unaccent.c that uses swscanf along with char2wchar and wchar2char instead of sscanf directly to initialize the unaccent extension and it appears to fix the problem in both the master and 9.1 branches. swscanf doesn't seem like an acceptable approach: it's a function that is relied on nowhere else in PG, so it adds new portability risks of its own. It doesn't exist on some platforms that we support (like the one I'm typing this message on) and there's no real good reason to assume that it's not broken in its own ways on others. If you really want to pursue this, I'd suggest parsing the line manually, perhaps via strchr searches for \t and \n. It likely wouldn't be very many more lines than what you've got here. However, the bigger picture is that OS X's UTF8 locales are broken through-and-through, and most of their other problems are not feasible to work around. So basically you can't use them for anything interesting, and it's not clear that it's worth putting any time into solving individual problems. In the particular case here, the issue presumably is that sscanf is relying on isspace() ... but we rely on isspace() directly, in quite a lot of places, so how much is it going to fix to dodge it right here? If Apple's low-level code came from FreeBSD and NetBSD, how did they get so broken? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] unaccent extension missing some accents
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: However, the bigger picture is that OS X's UTF8 locales are broken through-and-through, and most of their other problems are not feasible to work around. If Apple's low-level code came from FreeBSD and NetBSD, how did they get so broken? AFAIK, they're broken in the BSDen too, or at least were when Apple branched off from whichever BSD they started from (which was years ago). There may be a better solution available upstream by now, but it doesn't appear to me that Apple has any interest in fixing this area. 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] Syntax for partitioning
Jeff Janes jeff.ja...@gmail.com writes: shouldn't it need a DBA to declare it? How is the system supposed to anticipate that at some point years in the future I will want to run the command sequence create foo_archive as select from foo where year2009; delete from foo where year2009, or its partition-based equivalent, and have it operate on several billion rows cleanly and quickly? I don't think we can expect the system to anticipate what it has never before experienced. This is the DBA's job. Well, the not-fully spelled out proposal would be to still work it out from a list of columns picked by the DBA. I though that an existing index would be best, but maybe just columns would be good. I guess it's already time to play loose and invent some SQL syntax to make it easier talking about the same thing: ALTER TABLE foo SEGMENT ON (year, stamp); Now the aim would be to be able to implement the operation you describe by using the new segment map, which is an index pointing to sequential ranges of on-disk blocks where the data is known to share a common key range over the columns you're segmenting on. I would imagine this SQL: TRUNCATE foo WHERE year 2009; As the on-disk location of the data that qualify this WHERE clause is known, it could be possible to (predicate) lock it and bulk remove it, unlinking whole segments (1GB) at a time when relevant. While automatic clustering would be nice, it isn't the same thing as partitioning. That has been my initial reaction to that kind of ideas too. After some more time brewing the ideas, I'm not convinced that the use cases that usually drives you to the latter can't be solved with the former. 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] foreign key locks, 2nd attempt
Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of jue nov 10 16:59:20 -0300 2011: Alvaro Herrera wrote: Hello, After some rather extensive rewriting, I submit the patch to improve foreign key locks. To recap, the point of this patch is to introduce a new lock tuple mode, that lets the RI code obtain a lighter lock on tuples, which doesn't conflict with updates that do not modify the key columns. What kind of operations benefit from a non-key lock like this? I'm not sure I understand the question. With this patch, a RI check does SELECT FOR KEY SHARE. This means the tuple is locked with that mode until the transaction finishes. An UPDATE that modifies the referenced row will not conflict with that lock. An UPDATE that modifies the key columns will be blocked, just as now. Same with a DELETE. OK, so it prevents non-key data modifications from spilling to the referred rows --- nice. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] LOCK_DEBUG is busted
It's possible to compile the source tree with LOCK_DEBUG defined, but the resulting postgres promptly dumps core, due to the fact that user_lockmethod doesn't supply any value for trace_flag; thus, the first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer. This is the result of the following commit: commit 0180bd6180511875db046bf8ddcaa633a2952dfd Author: Bruce Momjian br...@momjian.us Date: Thu Oct 13 19:59:13 2011 -0400 Remove all traces of trace_userlocks, because userlocks were removed in PG 8.2. As far as I can see, that commit was just wrong and should be reverted. I believe that Bruce's motivation for this commit was the following sentence from the documentation: -User locks were removed as of PostgreSQL version 8.2. This option -currently has no effect. The trouble with this is that it's just not true. With that commit reverted and LOCK_DEBUG defined in pg_config_manual.h: rhaas=# set trace_userlocks=1; LOG: LockReleaseAll: lockmethod=2 STATEMENT: set trace_userlocks=1; LOG: LockReleaseAll done STATEMENT: set trace_userlocks=1; SET rhaas=# select pg_advisory_lock(31,31); LOG: LockAcquire: lock [16384,31] ExclusiveLock STATEMENT: select pg_advisory_lock(31,31); LOG: LockAcquire: new: lock(0x103ad37c0) id(16384,31,31,2,8,2) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(ExclusiveLock) STATEMENT: select pg_advisory_lock(31,31); LOG: LockAcquire: new: proclock(0x103b590b8) lock(0x103ad37c0) method(2) proc(0x103d72b30) hold(0) STATEMENT: select pg_advisory_lock(31,31); LOG: LockCheckConflicts: no conflict: proclock(0x103b590b8) lock(0x103ad37c0) method(2) proc(0x103d72b30) hold(0) STATEMENT: select pg_advisory_lock(31,31); LOG: GrantLock: lock(0x103ad37c0) id(16384,31,31,2,8,2) grantMask(80) req(0,0,0,0,0,0,1)=1 grant(0,0,0,0,0,0,1)=1 wait(0) type(ExclusiveLock) STATEMENT: select pg_advisory_lock(31,31); LOG: LockReleaseAll: lockmethod=2 STATEMENT: select pg_advisory_lock(31,31); LOG: LockReleaseAll done STATEMENT: select pg_advisory_lock(31,31); pg_advisory_lock -- (1 row) Now, whether or not this facility is well designed is a worthwhile question. Trace_lock_oidmin seems pretty sketchy to me, especially because it's blindly applied to even to lock tags where the second field isn't a relation - i.e. SET_LOCKTAG_TRANSACTION sets it to zero, SET_LOCKTAG_VIRTUALTRANSACTION sets it to the localTransactionId, SET_LOCKTAG_OBJECT sets it to the classId member of the objectaddress, and advisory locks set it to 32 bits of the user's chosen locktag. So by default, with trace_userlocks turned on and no other changes, pg_advisory_lock(16384,0) produces output like that shown above and pg_advisory_lock(16383,0) is met with silence. So maybe we should just rip some or all of this stuff out instead of worrying too much about it. If we're not going to do that, then we should revert the above commit, so that it works again, at least as much as it did before. -- 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] type privileges and default privileges
On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Let me put this differently. Should we either continue to hardcode the default privileges in the acldefault() function, or should we instead initialize the system catalogs with an entry in pg_default_acl as though ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been executed? If you're proposing to replace acldefault() with a catalog lookup, I vote no. I think that's a performance hit with little redeeming social value. No, I'm pondering having pg_default_acl initialized so that newly created types have explicit USAGE privileges in their typacl column, so acldefault() wouldn't be needed. (And builtin types would have their typacl initialized analogously.) I suppose this is how we might have done it if we had invented ALTER DEFAULT PRIVILEGES first. I'm not convinced. That's a lot of catalog clutter for no benefit. -- 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] Parsing output of EXPLAIN command in PostgreSQL
2011/11/10 Αναστάσιος Αρβανίτης tasosarvani...@yahoo.gr: I'm developing an application that requires parsing of execution plans (those produced as output by issuing an EXPLAIN [query] command). Are you aware of any Java library that I could use for this purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1. However, in that case it would better to have the XML Schema of the generated plans available. Is there any other solution I am not aware of? Not that I know of. I think pgAdmin can parse the EXPLAIN output, too, but that's in C++. -- 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] const correctness
Bruce Momjian br...@momjian.us wrote: I realize the patch only added 1-2 new const functions No, version 2 of the patch used the strchr() technique and has *zero* new functions and *zero* new macros. but this is only a small area of the code being patched --- a full solution would have many more complex duplicates, and awkward changes as we add features. I'm not convinced of that, and I don't think it really has a bearing on doing where it can be done with no new functions and no changes to the code other than adding const to existing lines of code. -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] const correctness
Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: I realize the patch only added 1-2 new const functions No, version 2 of the patch used the strchr() technique and has *zero* new functions and *zero* new macros. Right. I was referring to the non-strchr() approach in the initial patch. but this is only a small area of the code being patched --- a full solution would have many more complex duplicates, and awkward changes as we add features. I'm not convinced of that, and I don't think it really has a bearing on doing where it can be done with no new functions and no changes to the code other than adding const to existing lines of code. Right, again I was referring to the non-strchr() approach, e.g. new functions. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Parsing output of EXPLAIN command in PostgreSQL
On 11/10/2011 04:29 PM, Robert Haas wrote: 2011/11/10 Αναστάσιος Αρβανίτηςtasosarvani...@yahoo.gr: I'm developing an application that requires parsing of execution plans (those produced as output by issuing an EXPLAIN [query] command). Are you aware of any Java library that I could use for this purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1. However, in that case it would better to have the XML Schema of the generated plans available. Is there any other solution I am not aware of? Not that I know of. I think pgAdmin can parse the EXPLAIN output, too, but that's in C++. Pg--Explain is extremely well written, and should be easily translatable to Java if you really need to. The whole thing is less than 2000 lines, and a large part of that is comments. 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] Disable OpenSSL compression
Tom Lane wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: Tom Lane wrote: A GUC is entirely, completely, 100% the wrong answer. It has no way to deal with the fact that some clients may need compression and others not. You can force a certain SSL cipher on the client, why not a compression setting? To my mind, the argument for the ssl_cipher setting is to allow the DBA to enforce a site-wide security policy to the effect of we consider only these ciphers strong enough for production use. It's a pretty weak argument (especially since the setting is not cognizant of where the connection is coming from), but at least it's an argument. There's no comparable security argument for an ssl_compression setting. If anything, a security-minded DBA might wish to insist on compression being *on*, but you aren't proposing to give him control in that direction (and AFAICT openssl doesn't offer a force-on flag for it). But in any case, my objection is that there's no adequate use-case for this GUC, because it's much more sensible to set it from the client side. We have too many GUCs already --- Josh B regularly goes on the warpath looking for ones we can remove. This one should never get in there to start with. How is the compression connection parameter set? It seems odd for it to be compiled into the application because the application could be run on different networks. I don't know of any way to inject connection options from outside the application like libpq's PGOPTIONS. Would we add a libpq environment variable for this, like PGUSER? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] const correctness
Bruce Momjian br...@momjian.us wrote: No, version 2 of the patch used the strchr() technique and has *zero* new functions and *zero* new macros. Right. I was referring to the non-strchr() approach in the initial patch. I'm sorry that I misunderstood you. So, I don't think I've heard any argument against version 2 of this patch. Does anyone oppose this version? Is any committer willing to commit it? I'm not sure there's much point putting it into the CF application, since in spot-checks of object files I thought were most likely to be affected, I found that identical object code was generated. It seems to be strictly a matter of whether the code is more or less readily understood with the patch applied. -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] Parsing output of EXPLAIN command in PostgreSQL
Andrew Dunstan and...@dunslane.net writes: On 11/10/2011 04:29 PM, Robert Haas wrote: 2011/11/10 ÁíáóôÜóéïò Áñâáíßôçòtasosarvani...@yahoo.gr: Is there any other solution I am not aware of? Not that I know of. I think pgAdmin can parse the EXPLAIN output, too, but that's in C++. Pg--Explain is extremely well written, and should be easily translatable to Java if you really need to. The whole thing is less than 2000 lines, and a large part of that is comments. Nonetheless, it's solving the wrong problem. Any program that is being written today to read EXPLAIN output should be written to read one of the machine-readable formats. If you insist on reading the text format, don't be surprised when (not if) it breaks in future releases, and don't expect any sympathy from these quarters. It really shouldn't be that difficult to come by pre-fab code to read at least one of XML, JSON, or YAML into a suitable data structure. Now, if you're looking for something that understands the resulting data structure in more than a superficial fashion, that's a different question. But you'd need to get a lot more specific about what you need it to do. 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] LOCK_DEBUG is busted
Robert Haas robertmh...@gmail.com writes: It's possible to compile the source tree with LOCK_DEBUG defined, but the resulting postgres promptly dumps core, due to the fact that user_lockmethod doesn't supply any value for trace_flag; thus, the first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer. This is the result of the following commit: commit 0180bd6180511875db046bf8ddcaa633a2952dfd +1 for just reverting that commit. I'm not sure how much use the LOCK_DEBUG infrastructure has in exactly its current form, but I can certainly imagine wanting to use it or some variant of it to debug tough problems. If it's gone entirely, people would have to reinvent most of it for that type of debugging. On the other side of the coin, I don't have a clear enough use-case for it to want to spend time right now on redesigning it, nor a clear idea of exactly what changes might make it more useful. So I think we should just revert and not spend additional effort now. 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] LOCK_DEBUG is busted
Robert Haas wrote: Now, whether or not this facility is well designed is a worthwhile question. Trace_lock_oidmin seems pretty sketchy to me, especially because it's blindly applied to even to lock tags where the second field isn't a relation - i.e. SET_LOCKTAG_TRANSACTION sets it to zero, SET_LOCKTAG_VIRTUALTRANSACTION sets it to the localTransactionId, SET_LOCKTAG_OBJECT sets it to the classId member of the objectaddress, and advisory locks set it to 32 bits of the user's chosen locktag. So by default, with trace_userlocks turned on and no other changes, pg_advisory_lock(16384,0) produces output like that shown above and pg_advisory_lock(16383,0) is met with silence. So maybe we should just rip some or all of this stuff out instead of worrying too much about it. Please rip out whatever I missed. Thanks. The user locks were the old lock type before we had advisor locks, as far as I remember. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] LOCK_DEBUG is busted
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: It's possible to compile the source tree with LOCK_DEBUG defined, but the resulting postgres promptly dumps core, due to the fact that user_lockmethod doesn't supply any value for trace_flag; thus, the first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer. This is the result of the following commit: commit 0180bd6180511875db046bf8ddcaa633a2952dfd +1 for just reverting that commit. I'm not sure how much use the LOCK_DEBUG infrastructure has in exactly its current form, but I can certainly imagine wanting to use it or some variant of it to debug tough problems. If it's gone entirely, people would have to reinvent most of it for that type of debugging. On the other side of the coin, I don't have a clear enough use-case for it to want to spend time right now on redesigning it, nor a clear idea of exactly what changes might make it more useful. So I think we should just revert and not spend additional effort now. I am confused. I thought it was lock_debug referencing user locks that was broken. Does lock_debug need user locks? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] type privileges and default privileges
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut pete...@gmx.net wrote: No, I'm pondering having pg_default_acl initialized so that newly created types have explicit USAGE privileges in their typacl column, so acldefault() wouldn't be needed. (And builtin types would have their typacl initialized analogously.) I suppose this is how we might have done it if we had invented ALTER DEFAULT PRIVILEGES first. I'm not convinced. That's a lot of catalog clutter for no benefit. To actually get rid of acldefault, we'd have to do that not only for types but for all objects with ACLs. That's a LOT of catalog bulk, and like Robert I'm not seeing much benefit. It's not unreasonable to want the typical case to be small and fast. 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] Disable OpenSSL compression
Bruce Momjian br...@momjian.us writes: How is the compression connection parameter set? It seems odd for it to be compiled into the application because the application could be run on different networks. I don't know of any way to inject connection options from outside the application like libpq's PGOPTIONS. Huh? You put it in the connection string, typically. This is not different from how you'd specify sslmode to start with. 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] Parsing output of EXPLAIN command in PostgreSQL
On 11/10/2011 11:10 AM, Αναστάσιος Αρβανίτης wrote: I'm developing an application that requires parsing of execution plans (those produced as output by issuing an EXPLAIN [query] command). Are you aware of any Java library that I could use for this purpose? I found https://github.com/depesz/Pg--Explain but it is built in Perl. There's also a little Javascript program that consumes the JSON version at: http://www.postgresonline.com/journal/archives/171-pgexplain90formats_part1.html http://www.postgresonline.com/journal/archives/174-pgexplain90formats_part2.html Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1. However, in that case it would better to have the XML Schema of the generated plans available. That's the easiest way to solve this problem in Java, and in that case most of the text-based code in Pg--Explain will just be a distraction. I know some of the earlier versions of XML EXPLAIN included a DTD option to output that, but I don't see that in the committed code. I'm not sure where that is at actually; it's a good question. The only reference to doing this I found was Andrew's blog: http://people.planetpostgresql.org/andrew/index.php?/archives/32-A-couple-of-nice-tools.html where he talks about there being a RELAXNG specification for the XML output. I can't find where that came from either. Andrew? -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] Disable OpenSSL compression
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: How is the compression connection parameter set? It seems odd for it to be compiled into the application because the application could be run on different networks. I don't know of any way to inject connection options from outside the application like libpq's PGOPTIONS. Huh? You put it in the connection string, typically. This is not different from how you'd specify sslmode to start with. Well, you are saying the client is more flexible, but if the client is a binary, it isn't flexible without an environment variable to control it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Parsing output of EXPLAIN command in PostgreSQL
On 2011-11-10 17:23, Αναστάσιος Αρβανίτης wrote: Also another option I am considering is to use EXPLAIN [query] FORMAT XML which is available in PostgreSQL 9.1. However, in that case it would better to have the XML Schema of the generated plans available. Is there any other solution I am not aware of? Hi, I recommend using the XML, JSON or YAML version of the plan, whichever is easiest in your programming language to parse. I do not think anyone has written a formal schema yet for the XML but it still should be much easier to parse than rolling your own parser for the human readable format. But if you insist on parsing the human readable format, e.g. to support versions before 9.1, I recommend that you take a look at http://explain.depesz.com/ which supports parsing text plans in addition to XML and JSON. The site is open source and written in Perl. The source code can be found at: https://github.com/depesz/Pg--Explain Best regards, Andreas -- Andreas Karlsson -- 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] Disable OpenSSL compression
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Huh? You put it in the connection string, typically. This is not different from how you'd specify sslmode to start with. Well, you are saying the client is more flexible, but if the client is a binary, it isn't flexible without an environment variable to control it. As long as the client can take a connection string, it's perfectly flexible. If it can't, this is just one more reason why it should be fixed to do so. 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] Parsing output of EXPLAIN command in PostgreSQL
On 2011-11-10 23:42, Andreas Karlsson wrote: Hi, I recommend using the XML, JSON or YAML version of the plan, whichever is easiest in your programming language to parse. I do not think anyone has written a formal schema yet for the XML but it still should be much easier to parse than rolling your own parser for the human readable format. But if you insist on parsing the human readable format, e.g. to support versions before 9.1, I recommend that you take a look at http://explain.depesz.com/ which supports parsing text plans in addition to XML and JSON. The site is open source and written in Perl. The source code can be found at: https://github.com/depesz/Pg--Explain Sorry, I did not see the other thread about the same thing. Let's keep all discussion there. -- Andreas Karlsson -- 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] LOCK_DEBUG is busted
On Thu, Nov 10, 2011 at 5:07 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: It's possible to compile the source tree with LOCK_DEBUG defined, but the resulting postgres promptly dumps core, due to the fact that user_lockmethod doesn't supply any value for trace_flag; thus, the first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer. This is the result of the following commit: commit 0180bd6180511875db046bf8ddcaa633a2952dfd +1 for just reverting that commit. I'm not sure how much use the LOCK_DEBUG infrastructure has in exactly its current form, but I can certainly imagine wanting to use it or some variant of it to debug tough problems. If it's gone entirely, people would have to reinvent most of it for that type of debugging. On the other side of the coin, I don't have a clear enough use-case for it to want to spend time right now on redesigning it, nor a clear idea of exactly what changes might make it more useful. So I think we should just revert and not spend additional effort now. I am confused. I thought it was lock_debug referencing user locks that was broken. Does lock_debug need user locks? It supports tracing them. The point is, they're not gone. -- 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] proposal: psql concise mode
Josh Kupershmidt schmi...@gmail.com writes: FWIW, I just played around with 7.4 and 7.3 servers. (I had some bad memories of the older tarballs not building, but that must have been only on OS X -- I can build at least back to 7.3 on this Ubuntu 11.04 machine.) Most meta-commands worked alright on 7.4, or at least failed gracefully. The ones I saw which failed unexpectedly were \sf and \ef, which complained: ERROR: function pg_catalog.pg_get_functiondef(integer) does not exist I think we need a server version check for these two meta-commands, unless someone cares to make them work on 8.4, trivial patch attached. Agreed, we should check that. Will commit. As I suggested, many more unexpected failures (e.g. \dnS+) pop up when talking to a 7.3 server. It's not a big deal, but it'd be nice if we could instead error out with a sorry, we're too lazy to try to support 7.3 on the meta-commands which fail thusly, and make the various else clauses more explicit about just how far back their support really goes. Probably not worth the trouble ... how many pre-7.4 servers are still in the wild, and of those, how many might somebody try to talk to with a modern psql? The more realistic direction of future change, I think, is that we move up the cutoff version so we can take out some code, rather than add more. At the moment I'd find it a hard sell to drop support for 8.1 or later; so maybe there's not enough removable code to make it worth any effort. But in a few more years it'd be worth doing. What *would* be worth doing today, IMO, is ripping out pg_dump's support for servers older than 7.3 or 7.4; in particular getting rid of its kluges for server versions without pg_depend info. 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] proposal : backend startup hook / after logon trigger
Hi,
I occasionally need to perform some action whenever a user connects, and
there's nothing like an AFTER LOGON trigger (available in some other
databases).
Is there any particular reason why there's not a backend start hook,
executed right after a backend is initialized? I've tried a very simple
PoC (basically just a new hook definition, called from PostgresMain(),
see the after-logon-hook.diff (and a simple module that uses it is in
logon.c).
This obviously is not a complete patch or something, but is there a good
reason why this is a stupid idea?
Obviously this is a bit low-level approach, as most of the time the
triggers are implemented in a PL. But who says you can't call a PL from
the C module ...
Tomas
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
new file mode 100644
index 976a832..35f1926
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
*** int max_stack_depth = 100;
*** 104,110
/* wait N seconds to allow attach from a debugger */
int PostAuthDelay = 0;
!
/*
*private variables
--- 104,110
/* wait N seconds to allow attach from a debugger */
int PostAuthDelay = 0;
! backend_startup_hook_type backend_startup_hook = NULL;
/*
*private variables
*** PostgresMain(int argc, char *argv[], con
*** 3750,3755
--- 3750,3769
if (!ignore_till_sync)
send_ready_for_query = true;/* initially, or after error */
+ if (backend_startup_hook != NULL)
+ {
+ PG_TRY();
+ {
+ backend_startup_hook(MyProcPid, MyDatabaseId, dbname,
username);
+ }
+ PG_CATCH();
+ {
+ elog(FATAL, Error calling after-logon trigger);
+ proc_exit(0);
+ }
+ PG_END_TRY();
+ }
+
/*
* Non-error queries loop here.
*/
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
new file mode 100644
index 9d19417..3971268
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
*** extern bool is_authenticated_user_replic
*** 388,391
--- 388,396
extern bool BackupInProgress(void);
extern void CancelBackup(void);
+ typedef void (*backend_startup_hook_type) (pid_t pid, Oid databaseOid,
+const char * dbname, const char *
username);
+
+ extern backend_startup_hook_type backend_startup_hook;
+
#endif /* MISCADMIN_H */
#include stdio.h
#include math.h
#include string.h
#include sys/time.h
#include unistd.h
#include limits.h
#include postgres.h
#include utils/array.h
#include utils/lsyscache.h
#include utils/numeric.h
#include utils/builtins.h
#include utils/memutils.h
#include catalog/pg_type.h
#include miscadmin.h
static backend_startup_hook_type prev_backend_startup_hook = NULL;
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
void _PG_init(void);
void _PG_fini(void);
static
void my_startup_hook (pid_t pid, Oid databaseOid,
const char * dbname, const char * username);
/*
* Module load callback
*/
void
_PG_init(void)
{
prev_backend_startup_hook = backend_startup_hook;
backend_startup_hook = my_startup_hook;
}
void
_PG_fini(void)
{
backend_startup_hook = prev_backend_startup_hook;
}
static
void my_startup_hook (pid_t pid, Oid databaseOid,
const char * dbname, const char * username) {
if (! strcmp(dbname, testdb)) {
elog(ERROR, startup hook is failing);
} else {
elog(NOTICE, startup hook finished OK);
}
}
--
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] Parsing output of EXPLAIN command in PostgreSQL
On 11/10/2011 04:59 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: Pg--Explain is extremely well written, and should be easily translatable to Java if you really need to. The whole thing is less than 2000 lines, and a large part of that is comments. Nonetheless, it's solving the wrong problem. Any program that is being written today to read EXPLAIN output should be written to read one of the machine-readable formats. Umm, it *does* handle all the formats: $ wc -l ../Pg--Explain/lib/Pg/Explain.pm ../Pg--Explain/lib/Pg/Explain/* 248 ../Pg--Explain/lib/Pg/Explain.pm 75 ../Pg--Explain/lib/Pg/Explain/FromJSON.pm 182 ../Pg--Explain/lib/Pg/Explain/From.pm 202 ../Pg--Explain/lib/Pg/Explain/FromText.pm 109 ../Pg--Explain/lib/Pg/Explain/FromXML.pm 77 ../Pg--Explain/lib/Pg/Explain/FromYAML.pm 785 ../Pg--Explain/lib/Pg/Explain/Node.pm 292 ../Pg--Explain/lib/Pg/Explain/StringAnonymizer.pm 1970 total One of the obvious reasons for handling text is to deal with old servers before we had machine readable output. 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] Parsing output of EXPLAIN command in PostgreSQL
On 11/10/2011 05:26 PM, Greg Smith wrote: I know some of the earlier versions of XML EXPLAIN included a DTD option to output that, but I don't see that in the committed code. I'm not sure where that is at actually; it's a good question. The only reference to doing this I found was Andrew's blog: http://people.planetpostgresql.org/andrew/index.php?/archives/32-A-couple-of-nice-tools.html where he talks about there being a RELAXNG specification for the XML output. I can't find where that came from either. Andrew? I created one at some stage, but I it's rather bitrotted. Essentially I think we decided that we were going to be pretty free to whack around the format and having a spec wasn't going to be terribly helpful. 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] proposal: psql concise mode
On Thu, Nov 10, 2011 at 3:41 PM, Bruce Momjian br...@momjian.us wrote:
Have you tried \d+ with this psql mode:
\pset format wrapped
It wraps the data so it fits on the screen --- it is my default in my
.psqlrc.
I think that's one of the many psql features I haven't experimented
with, thanks for the suggestion. It looks OK for some things, but I
find the column-wrapping behavior can be rather illegible, e.g.
create table test (
some_column_name serial PRIMARY KEY,
another_column_name integer NOT NULL,
another_col integer, username text
);
tmp=# \d+ test
Table public.test
Column | Type | Modifiers | Storage | Stats target | Description
+-+--+-+--+-
some_column_na.| integer | not null def.| plain | |
.me | |.ault nextval.| | |
| |.('test_some_.| | |
| |.column_name_.| | |
| |.seq'::regcla.| | |
| |.ss) | | |
another_column.| integer | not null | plain | |
._name | | | | |
another_col| integer | | plain | |
username | text| | extende.| |
| | |.d | |
That wrapping is pretty ugly, and the culprit is all the wasted
horizontal space for Stats Target and Description in this case
(and probably for many users, who never set either column modifier).
That output might be much nicer if, instead of Modifiers, Column,
and Storage getting squeezed, the empty Stats Target and
Description column headers got squeezed instead, giving the
populated columns more horizontal space.
--
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] Parsing output of EXPLAIN command in PostgreSQL
Andrew Dunstan and...@dunslane.net writes: On 11/10/2011 04:59 PM, Tom Lane wrote: Nonetheless, it's solving the wrong problem. Any program that is being written today to read EXPLAIN output should be written to read one of the machine-readable formats. Umm, it *does* handle all the formats: The point I'm trying to make is that translating the parts that handle text into another language seems like wasted effort. depesz has a specific problem to solve, namely wanting to accept emailed input from users who are likely to send any format; and that requires him to expend a lot of effort that is not likely to be necessary in any other setting. 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] psql expanded auto
On Tue, Nov 08, 2011 at 06:36:52AM +0200, Peter Eisentraut wrote: Here is an updated patch that addresses all the issues you pointed out. Looks ready to me. Thanks. -- 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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
On 18 July 2011 02:46, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of dom jul 17 20:36:49 -0400 2011: Does git allow for additional commit fields? That would allow for easy tracking without much additional burden on committers. I mean, there's git notes, but that's not exactly what we're looking for here, and I don't see how it would easy the burden on committers anyway, and it doesn't solve the problem of not being able to change things after the fact. Eh, git notes *can* be changed after the fact, and are *not* append only. And as the committer who started this discussion in the first place, I don't have any problem with having to edit them separately from the commit message, which is a tiny portion of the work involved in figuring out the patch, anyway. What's not clear to me, is whether they are sent to the remote when you invoke git push. I'm not clear on whether this needing a separate command or more arguments to push, or it's just not possible. I think this is a clear-cut case of needing some sort of web application to manage this. I'd even be willing to help fill in the relevant info. But I'm not going to write it myself... Having a web app would work for me, but a larger job than just using git notes. So if the notes really work, +1 to them from me. I've only just noticed that this still doesn't work for me: test6=# CREATE TABLE a (num INT); CREATE TABLE test6=# INSERT INTO a (num) VALUES (90); INSERT 0 1 test6=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num 20) NOT VALID; ALTER TABLE test6=# \q toucan:~ thom$ createdb test7 toucan:~ thom$ pg_dump -f /tmp/test.sql test6 toucan:~ thom$ psql test7 /tmp/test.sql SET SET SET SET SET CREATE EXTENSION COMMENT SET SET SET CREATE TABLE ALTER TABLE ERROR: new row for relation a violates check constraint meow CONTEXT: COPY a, line 1: 90 STATEMENT: COPY a (num) FROM stdin; ERROR: new row for relation a violates check constraint meow CONTEXT: COPY a, line 1: 90 REVOKE REVOKE GRANT GRANT The dump correctly contains: CREATE TABLE a ( num integer, CONSTRAINT meow CHECK ((num 20)) NOT VALID ); And the COPY command is: COPY a (num) FROM stdin; 90 \. So this is broken. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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] proposal: psql concise mode
On Thu, Nov 10, 2011 at 6:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: As I suggested, many more unexpected failures (e.g. \dnS+) pop up when talking to a 7.3 server. It's not a big deal, but it'd be nice if we could instead error out with a sorry, we're too lazy to try to support 7.3 on the meta-commands which fail thusly, and make the various else clauses more explicit about just how far back their support really goes. Probably not worth the trouble ... how many pre-7.4 servers are still in the wild, and of those, how many might somebody try to talk to with a modern psql? The more realistic direction of future change, I think, is that we move up the cutoff version so we can take out some code, rather than add more. At the moment I'd find it a hard sell to drop support for 8.1 or later; so maybe there's not enough removable code to make it worth any effort. But in a few more years it'd be worth doing. I am 100% on board with dropping support for such old servers whenever feasible, so as to cut down on the cruft in psql -- that's the only reason I cared to go poking at this at all. I would suggest we bump the minimum supported server version for psql up to 8.0 at some point in the not-too-distant future, perhaps even for 9.2. What *would* be worth doing today, IMO, is ripping out pg_dump's support for servers older than 7.3 or 7.4; in particular getting rid of its kluges for server versions without pg_depend info. Yeah, that was another can of worms I had in the back of my mind. I think there's a good case for maintaining longer backwards compatibility in pg_dump vs. psql, to help people upgrade an ancient server to a modern one. But certainly, anything older than 7.3 or 7.4 is pushing the boundaries in terms of being supported. Jsoh -- 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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
On 10 November 2011 23:56, Thom Brown t...@linux.com wrote: On 18 July 2011 02:46, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of dom jul 17 20:36:49 -0400 2011: Does git allow for additional commit fields? That would allow for easy tracking without much additional burden on committers. I mean, there's git notes, but that's not exactly what we're looking for here, and I don't see how it would easy the burden on committers anyway, and it doesn't solve the problem of not being able to change things after the fact. Eh, git notes *can* be changed after the fact, and are *not* append only. And as the committer who started this discussion in the first place, I don't have any problem with having to edit them separately from the commit message, which is a tiny portion of the work involved in figuring out the patch, anyway. What's not clear to me, is whether they are sent to the remote when you invoke git push. I'm not clear on whether this needing a separate command or more arguments to push, or it's just not possible. I think this is a clear-cut case of needing some sort of web application to manage this. I'd even be willing to help fill in the relevant info. But I'm not going to write it myself... Having a web app would work for me, but a larger job than just using git notes. So if the notes really work, +1 to them from me. I've only just noticed that this still doesn't work for me: test6=# CREATE TABLE a (num INT); CREATE TABLE test6=# INSERT INTO a (num) VALUES (90); INSERT 0 1 test6=# ALTER TABLE a ADD CONSTRAINT meow CHECK (num 20) NOT VALID; ALTER TABLE test6=# \q toucan:~ thom$ createdb test7 toucan:~ thom$ pg_dump -f /tmp/test.sql test6 toucan:~ thom$ psql test7 /tmp/test.sql SET SET SET SET SET CREATE EXTENSION COMMENT SET SET SET CREATE TABLE ALTER TABLE ERROR: new row for relation a violates check constraint meow CONTEXT: COPY a, line 1: 90 STATEMENT: COPY a (num) FROM stdin; ERROR: new row for relation a violates check constraint meow CONTEXT: COPY a, line 1: 90 REVOKE REVOKE GRANT GRANT The dump correctly contains: CREATE TABLE a ( num integer, CONSTRAINT meow CHECK ((num 20)) NOT VALID ); Actually I mean incorrectly contains, because the constraint needs adding after the data insertion, not as part of the create table statement. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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] proposal: psql concise mode
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10-11-2011 21:42, Josh Kupershmidt wrote:
On Thu, Nov 10, 2011 at 3:41 PM, Bruce Momjian br...@momjian.us wrote:
Have you tried \d+ with this psql mode:
\pset format wrapped
It wraps the data so it fits on the screen --- it is my default in my
.psqlrc.
I think that's one of the many psql features I haven't experimented
with, thanks for the suggestion. It looks OK for some things, but I
find the column-wrapping behavior can be rather illegible, e.g.
create table test (
some_column_name serial PRIMARY KEY,
another_column_name integer NOT NULL,
another_col integer, username text
);
tmp=# \d+ test
Table public.test
Column | Type | Modifiers | Storage | Stats target |
Description
+-+--+-+--+-
some_column_na.| integer | not null def.| plain | |
.me | |.ault nextval.| | |
| |.('test_some_.| | |
| |.column_name_.| | |
| |.seq'::regcla.| | |
| |.ss) | | |
another_column.| integer | not null | plain | |
._name | | | | |
another_col| integer | | plain | |
username | text| | extende.| |
| | |.d | |
That wrapping is pretty ugly, and the culprit is all the wasted
horizontal space for Stats Target and Description in this case
(and probably for many users, who never set either column modifier).
That output might be much nicer if, instead of Modifiers, Column,
and Storage getting squeezed, the empty Stats Target and
Description column headers got squeezed instead, giving the
populated columns more horizontal space.
I'm not convinced yet, because a single comment in only one column is
enough to show the Description's column again.
IMHO it seems to me that the only gain of this new proposed behaviour
only happens when there is no especial information about the columns,
like comments or stat target, and I don't know if this is a common
situation.
[]s
- --
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br
http://github.net/guedes - twitter: @guediz
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iQEcBAEBAgAGBQJOvGpeAAoJEBa5zL7BI5C7V6cH/3chliawlBO0Sf0HB2DSmHeK
AyfA+LPiKRa9Egnlt7jHIAqdiplVkfUkaP8P2L2OryexStb8eXtAWeRJjHdR11di
X15M6551vHf7fqwmCpHtNebACXDdrQefHuw9MFtPYe4jaJwy1kU7IyLQcpRjNA0s
mvPJKrH08WqdFIw0DgiZi1+EFZE2Swr/zAKbNU2snGhKA0w2juHpoBOHmlfxmXQq
Z2zWvins8nqG1lpAhhD1QZzImGpWf/W7hiXDoP2BGo9wYjU38obbVdZJHNAey75B
9C4f75vQH4MRGy/wWYEPxttLoBerQaVedfEFPyHIOoqUOpIMJeLLqbk6mY5AIDM=
=5Oez
-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] LOCK_DEBUG is busted
On Thu, Nov 10, 2011 at 5:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: It's possible to compile the source tree with LOCK_DEBUG defined, but the resulting postgres promptly dumps core, due to the fact that user_lockmethod doesn't supply any value for trace_flag; thus, the first LockReleaseAll(USER_LOCKMETHOD) dereferences a NULL pointer. This is the result of the following commit: commit 0180bd6180511875db046bf8ddcaa633a2952dfd +1 for just reverting that commit. I'm not sure how much use the LOCK_DEBUG infrastructure has in exactly its current form, but I can certainly imagine wanting to use it or some variant of it to debug tough problems. If it's gone entirely, people would have to reinvent most of it for that type of debugging. On the other side of the coin, I don't have a clear enough use-case for it to want to spend time right now on redesigning it, nor a clear idea of exactly what changes might make it more useful. So I think we should just revert and not spend additional effort now. I don't feel like it accomplishes much of anything that can't be trivially accomplished by throwing in a couple of ad-hoc elog() calls wherever you happen to want them. I experimented with this when developing the fastlock patches and found it didn't tell me what I wanted to know, so just stuck in debugging code in the places that were relevant to my patch's then-current problems. Once those bugs were fixed, I took the debugging code back out. I think the author of this code did pretty much the same thing, but then developed the pretension that the particular places he stuck the elog() calls in would be generally relevant, which I don't believe to be the case. Or maybe they were relevant at one time, but this code has been with us for an awfully long time, and I think it's considerably outlived its usefulness. What I think it's mostly doing at this point is making it more difficult to make further changes - you do whatever you want to do, and then you have to go figure out what to do about the crazy LOCK_DEBUG stuff that no one uses. -- 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] proposal : backend startup hook / after logon trigger
On 10-11-2011 21:12, Tomas Vondra wrote: I occasionally need to perform some action whenever a user connects, and there's nothing like an AFTER LOGON trigger (available in some other databases). Are you proposing an on-logon hook or an on-connect trigger? It is two separate things. The former can't solve some tasks (e.g. execute whatever pl code) and the latter can't be implemented with a simple hook (you will have to propose a syntax and offer some machinery to execute the pl code). Of course, if you want to propose any of these ideas, keep in mind that a symmetric functionality (e.g. on-logoff hook or on-disconnect trigger) should be implemented too. -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- 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] LOCK_DEBUG is busted
Robert Haas robertmh...@gmail.com writes: ... What I think it's mostly doing at this point is making it more difficult to make further changes - you do whatever you want to do, and then you have to go figure out what to do about the crazy LOCK_DEBUG stuff that no one uses. [ shrug... ] If you're sufficiently exercised about it to take it out entirely, I won't stand in the way. I have not found it to be an impediment myself, though. 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] Syntax for partitioning
On Thu, Nov 10, 2011 at 1:19 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Now the aim would be to be able to implement the operation you describe by using the new segment map, which is an index pointing to sequential ranges of on-disk blocks where the data is known to share a common key range over the columns you're segmenting on. I would imagine this SQL: TRUNCATE foo WHERE year 2009; As the on-disk location of the data that qualify this WHERE clause is known, it could be possible to (predicate) lock it and bulk remove it, unlinking whole segments (1GB) at a time when relevant. I am basically in agreement with you. After wanting better partitioning (Oracle-style) in Postgres for some time just to be free of the mechanically painful table-inheritance version, I have come around to thinking it's basically a bad idea, but one that with a little bit of finessing can be made a good idea. The reason I have started to think this is because of an old feature that works very well: CREATE INDEX. In spite of what people might think, I think it's pretty clear that CREATE INDEX is not DDL: it's actually physical advice to the system. I have seen the fourth-generation-language promise delivered upon quite a few times in production, now: we witness an access pattern that becomes problematic, we run CREATE INDEX CONCURRENTLY, the problem is solved without any change to the application, and the index definition is backported to our application bootstrapping process. It would be hard for me to understate how valuable this has been to avoid both premature optimization and excessive panic when dealing with change. Similar to the overall project stance on query hints, I don't think Postgres should retreat on its ground from being a 4GL system. I think both indexes and a hypothetical partitioning feature should be clearly isolated as directives to the system about how to physically organize and access data, and any partitioning feature that creates new relation namespace entries and expects you to manipulate them to gain the benefits seems like extra, non-desirable surface area to me. I think this becomes especially apparent once one considers on-line repartitioning (I am exposing a bias here, but any feature in Postgres that cannot be done concurrently -- like VACUUM FULL -- is very dangerous to both me and my customers, whereas it may not be useless or dangerous to a build-your-own data warehouse). It feels like it would be desirable to have the physical partitions exist in an inconsistent-state whereby they are being brought into alignment with the newly desired physical description. Finally, I think a legitimate objection to this inclination is that it can be really easy to issue a DELETE that is usually fast, but when any mistake or change creeps in becomes very slow: I have heard from some friends making heavy use of table partitioning via inheritance that one of the problems is not quite exactly matching the table constraint, and then hosing their hardware. As a result, they mangle partitions explicitly in the application to prevent foot-gunning. That's clearly lame (and they know it), but I think may indicate a need to instead allow for some kind of physical-access-method assertion checking quite apart from the logical content of the query that can deliver a clear, crisp error to application developers if a preferred access pattern is not usable. My experience suggests that while solving problems is good, turning problems into flat-out errors is *nearly* as good, and worth some more investigation. -- 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] type privileges and default privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut pete...@gmx.net wrote: No, I'm pondering having pg_default_acl initialized so that newly created types have explicit USAGE privileges in their typacl column, so acldefault() wouldn't be needed. (And builtin types would have their typacl initialized analogously.) I suppose this is how we might have done it if we had invented ALTER DEFAULT PRIVILEGES first. I'm not convinced. That's a lot of catalog clutter for no benefit. To actually get rid of acldefault, we'd have to do that not only for types but for all objects with ACLs. That's a LOT of catalog bulk, and like Robert I'm not seeing much benefit. It's not unreasonable to want the typical case to be small and fast. A LOT of catalog bulk..? Am I missing something here? Aren't we just talking about 16 bytes, or so, per catalog entry? On one of my larger databases, with ~250k catalog records, we're talking about 4MB. That catalog is currently 130MB. An empty database has, what, 5000 objects? That'd be ~80KB? My thought would be to have a place where we keep what the 'default default' ACL is for each object type (yes, with the idea that it could be modified, I'd see that as a nice feature and I don't think we need to worry, as much, about performance of DDL..) and then we populate the ACL for each created object with the 'default default' ACL. Perhaps we integrate this with the existing default permissions system.. This isn't a new idea to me, to be honest.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011: On 10 November 2011 23:56, Thom Brown t...@linux.com wrote: The dump correctly contains: CREATE TABLE a ( num integer, CONSTRAINT meow CHECK ((num 20)) NOT VALID ); Actually I mean incorrectly contains, because the constraint needs adding after the data insertion, not as part of the create table statement. Interesting, thanks -- I'll look into it. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] type privileges and default privileges
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: To actually get rid of acldefault, we'd have to do that not only for types but for all objects with ACLs. That's a LOT of catalog bulk, and like Robert I'm not seeing much benefit. It's not unreasonable to want the typical case to be small and fast. A LOT of catalog bulk..? Am I missing something here? What I'm missing is what actual benefit we get from spending the extra space. (No, I don't believe that changing the defaults is something that users commonly will or should do; it's certainly not the case to optimize for.) 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] type privileges and default privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: A LOT of catalog bulk..? Am I missing something here? What I'm missing is what actual benefit we get from spending the extra space. (No, I don't believe that changing the defaults is something that users commonly will or should do; it's certainly not the case to optimize for.) Typical database *users*? No. A DBA or SA? Certainly, and we already provide a way to do that, in part. Supporting it for the 'default defaults' would be nice as would support for default privileges for schemas (rather than just objects that go *in* schemas). Certainly a big one that people get caught by is our default of execute to public on functions.. Most of our privileges are set up as minimal access to others, functions are an oddity in that regard. Rather than fight the battle of what the default *should* be for functions, we could just give the DBA the ability to configure it for their database. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] type privileges and default privileges
On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: A LOT of catalog bulk..? Am I missing something here? What I'm missing is what actual benefit we get from spending the extra space. (No, I don't believe that changing the defaults is something that users commonly will or should do; it's certainly not the case to optimize for.) Typical database *users*? No. A DBA or SA? Certainly, and we already provide a way to do that, in part. Supporting it for the 'default defaults' would be nice as would support for default privileges for schemas (rather than just objects that go *in* schemas). Certainly a big one that people get caught by is our default of execute to public on functions.. Most of our privileges are set up as minimal access to others, functions are an oddity in that regard. Rather than fight the battle of what the default *should* be for functions, we could just give the DBA the ability to configure it for their database. Sure, let's do. But that hardly means that we need to store useless catalog records in every database with the DBA doesn't do that. -- 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
On Wed, Nov 9, 2011 at 1:56 AM, Nikhil Sontakke nikkh...@gmail.com wrote: Hi, Consider the following sequence of events: s1 # CREATE SCHEMA test_schema; s1 # CREATE TABLE test_schema.c1(x int); Now open another session s2 and via gdb issue a breakpoint on heap_create_with_catalog() which is called by DefineRelation(). s2 # CREATE TABLE test_schema.c2(y int); The above will break on the function. Now issue a drop schema in session s1 s1 # DROP SCHEMA test_schema CASCADE; NOTICE: drop cascades to table test_schema.c1 DROP SCHEMA Continuing in gdb, also completes the creation of c2 table without any errors. We are now left with a dangling entry in pg_class along with all the corresponding data files in our data directory. The problem becomes worse if c2 was created using a TABLESPACE. Now dropping of that tablespace does not work at all. Am sure we can come up with myriad such other issues. Hmm. Does this break pg_dump? I have reported a bug whereby dangling pg_class entries point to a namespace that has since been dropped in the past (and has been reported many times before that, even). The bug report is here, whereby I also aggregate other similar bug reports that have taken place over a very long period of time: http://archives.postgresql.org/pgsql-bugs/2011-02/msg00185.php Given that the schema is successfully dropped, yet another table is created presumably using this already-resolved schema OID, it seems like it would run into this... You could run this query, which should return 0, but may not in your case: select count(distinct typnamespace) from pg_type where not exists (select 1 from pg_namespace where oid = pg_type.typnamespace); -- 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] type privileges and default privileges
* Robert Haas (robertmh...@gmail.com) wrote: On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost sfr...@snowman.net wrote: Certainly a big one that people get caught by is our default of execute to public on functions.. Most of our privileges are set up as minimal access to others, functions are an oddity in that regard. Rather than fight the battle of what the default *should* be for functions, we could just give the DBA the ability to configure it for their database. Sure, let's do. But that hardly means that we need to store useless catalog records in every database with the DBA doesn't do that. Fair enough, so the direction would be to add 'IN DATABASE' options to 'ALTER DEFAULT PRIVILEGES' and have all the same options there, plus flags for schema (and any other schema-level/entire-database things) options? I presume that the 'IN SCHEMA' / 'FOR USER' options would be used, where those exist, and we'd only fall back to the higher ones if those don't exist? Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Multiple Extensions
Hackers, I’m preparing a new release of pgTAP, and have started breaking it down into smaller extensions. I’ve been planning to have them all in one distribution file for now, but it seems that one cannot specify multiple extension names in the EXTENSION variable. In my Makefile, I have EXTENSION= pgtap pgtap-core pgtap-schema But having more than the one, I get: make grep: pgtap: No such file or directory grep: pgtap-core: No such file or directory grep: pgtap: No such file or directory grep: pgtap-core: No such file or directory grep: pgtap: No such file or directory grep: pgtap-core: No such file or directory Makefile:79: *** multiple target patterns. Stop. So is that not supported? If not, why not? Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
