Re: [HACKERS] Re: [ADMIN] PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum
On 12.01.2011 06:21, Fujii Masao wrote: On Sat, Dec 25, 2010 at 2:09 PM, Maxim Bogukmaxim.bo...@gmail.com wrote: While I trying create reproducible test case for BUG #5798 I encountered very strange effect on two of my servers (both servers have same hardware platform/OS (freebsd 7.2) and PostgreSQL 8.4.4). Very simple test table created as: CREATE TABLE test (id integer); INSERT INTO test select generate_series(0,1); And I trying repeateble vacuum of that table with script: perl -e foreach (1..10) {system \psql -d test -h -c 'vacuum test'\;} And once per like an minute (really random intervals can be 5 minutes without problems can be 3 vacuum in row show same error) I getting next errors: WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test page 1 ... WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation test page 30 for all pages of the relation. Oh, interesting. This is the first time anyone can reliably reproducible that. I can't reproduce that on my laptop with that script, though, so I'm going to need your help to debug this. Can you compile PostgreSQL with the attached patch, and rerun the test? It will dump the pages with incorrectly set flags to files in /tmp/, and adds a bit more detail in the WARNING. Please run the test until you get those warnings, and tar up the the created /tmp/pageimage* files, and post them along with the warning generated. We'll likely need to go back and forth a few times with various debugging patches until we get to the heart of this.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index 67e0be9..0e88aa5 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -48,6 +48,7 @@ #include pgstat.h #include postmaster/autovacuum.h #include storage/bufmgr.h +#include storage/fd.h #include storage/freespace.h #include storage/lmgr.h #include utils/inval.h @@ -668,13 +669,26 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, /* Update the all-visible flag on the page */ if (!PageIsAllVisible(page) all_visible) { + elog(WARNING, debugging: setting PD_ALL_VISIBLE in relation \%s\ on page %u (OldestXmin %u), relname, blkno, OldestXmin); PageSetAllVisible(page); SetBufferCommitInfoNeedsSave(buf); } else if (PageIsAllVisible(page) !all_visible) { - elog(WARNING, PD_ALL_VISIBLE flag was incorrectly set in relation \%s\ page %u, - relname, blkno); + elog(WARNING, PD_ALL_VISIBLE flag was incorrectly set in relation \%s\ page %u (OldestXmin %u), + relname, blkno, OldestXmin); + { +char fname[MAXPGPATH]; +FILE *fp; + +/* dump the raw page to a file */ +snprintf(fname, sizeof(fname), /tmp/pageimage_%s_%d, + relname, blkno); +fp = AllocateFile(fname, wb); +fwrite(page, 1, BLCKSZ, fp); +FreeFile(fp); + + } PageClearAllVisible(page); SetBufferCommitInfoNeedsSave(buf); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing multiple concurrent base backups
On Tue, Jan 11, 2011 at 8:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Tue, Jan 11, 2011 at 19:51, Tom Lane t...@sss.pgh.pa.us wrote: Seems like either one of these is fairly problematic in that you have to have some monstrous kluge to get the backup_label file to appear with the right name in the tarfile. How badly do we actually need this? I don't think the use-case for concurrent base backups is all that large in practice given the I/O hit it's going to involve. I think it can be done cleaner in the tar file injection - I've been chatting with Heikki offlist about that. Not sure, but I have a feeling it does. One point that I'm particularly interested to see how you'll kluge it is ensuring that the tarball contains only the desired temp data and not also the real $PGDATA/backup_label, should there be a normal base backup being done concurrently with the streamed one. The whole thing just seems too fragile and dangerous to be worth dealing with given that actual usage will be a corner case. *I* sure wouldn't trust it to work when the chips were down. Maybe if pg_start_backup() notices that there is another backup running should block waiting for another session to run pg_stop_backup() ? Or have a new function like pg_start_backup_wait() ? Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell . Greetings Marcin -- 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] Streaming base backups
On Mon, Jan 10, 2011 at 11:09 PM, Magnus Hagander mag...@hagander.net wrote: I've committed the backend side of this, without that. Still working on the client, and on cleaning up Heikki's patch for grammar/parser support. Great work! I have some comments: While walsender is sending a base backup, WalSndWakeup should not send the signal to that walsender? In sendFile or elsewhere, we should periodically check whether postmaster is alive and whether the flag was set by the signal? At the end of the backup by walsender, it forces a switch to a new WAL file and waits until the last WAL file has been archived. So we should change postmaster so that it doesn't cause the archiver to end before walsender ends when shutdown is requested? Also, when shutdown is requested, the walsender which is streaming WAL should not end before another walsender which is sending a backup ends, to stream the backup-end WAL? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] multiset patch review
On Wed, Jan 12, 2011 at 15:21, Peter Eisentraut pete...@gmx.net wrote: You may want to read this thread about the cardinality function are you trying to add: http://archives.postgresql.org/pgsql-hackers/2009-02/msg01388.php Since our archive is split per month, this might be more readable: http://postgresql.1045698.n5.nabble.com/cardinality-td2003172.html We've discussed what number should cardinality() returns: #1. The total number of elements. (It's currently implemented.) #2. The length of the first dimension. It's as same as array_length(array, 1) . I prefer #1 because we have no easy way to retrieve the number. array_dims() returns similar numbers, but calculate the total number is a bit complex. If we will support array of arrays (jugged array), cardinality() can return the number of elements in the most outer array. It's similar definition in multi-dimensional arrays in C#, that has both array of arrays and multi-dimensional arrays. http://msdn.microsoft.com/library/system.array.length(v=VS.100).aspx We can compare those SQL functions and C# array methods: * cardinality(array) -- array.Length * array_length(array. dim) -- array.GetLength(dim) Also, what happened to the idea of a separate MULTISET type? I don't have any plans to implement dedicated MULTISET type for now because almost all functions and operators can work also for arrays. If we have a true MULTISET data type, we can overload them with MULTISET arguments. One exception might be collect() aggregate function because we might need to change the result type from array to multiset. collect(anyelement) = anyarray for now Note that fusion() won't be an issue because we can overload it: fusion(anyarray) = anyarray and (anymultiset) = anymultiset -- Itagaki Takahiro -- 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_depend explained
2011/1/12 Florian Pflug f...@phlo.org: I suggest you try to node-folding strategy and see how far it gets you. Good suggestion! :-) That's exactly what I've been trying to do, but failed miserably :-( I have written a thorough description of my problem and put it on my github: https://github.com/gluefinance/pov/tree/master/doc In the example, pg_depend.sql, we want to reach the following conclusions in an algorithmic way, only by using pg_depend as in-data: table t1, function f1, sequence s1 have no dependencies, other than the public schema table t2 depends on t1 and table t3 depends on f1 view v1 depends on t1 and view v2 depends on t2 view v3 depends on both v1 and v2 view v4 depends on v3 and f1 The topological tree automatically generated from the data in pg_depend is presented in pg_depend.dot, pg_depend.svg and pg_depend.png. The actual topological tree (possible order of creation) created manually is presented in pg_depend_actual.dot, pg_depend_actual.svg and pg_depend_actual.png. The automatically created objects, such as primary key indexes, constraints and triggers, have been ignored in this graph, as they are implicitly created when creating the base objects. Objective: Define a general algorithm taking ONLY the pg_depend data as input, generating a valid topological directional graph, including at least the nodes in pg_depend_actual.dot, but might as well include all nodes, although it is not necessary, it's certainly won't hurt. It will be necessary to look not only at the nodes (objects) and edges (obj-refobj), but also the deptype for each edge. List of files: pg_depend.sql : A small but sufficient database model of tables, sequences, functions and views. pg_depend.dot : Digraph in DOT language (plaintext format), generated automatically only by using data from pg_data. pg_depend.png : PNG generated using GraphViz with pg_depend.dot as input pg_depend.svg : SVG generated using GraphViz with pg_depend.dot as input pg_depend_actual.dot : Digraph in DOT language (plaintext format), generated manually, shows the actual possible order of creation, only including the base objects. pg_depend_actual.png : PNG generated using GraphViz with pg_depend_actual.dot as input pg_depend_actual.svg : SVG generated using GraphViz with pg_depend_actual.dot as input -- Best regards, Joel Jacobson Glue Finance -- 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] Fwd: [JDBC] Weird issues when reading UDT from stored function
Dear hackers :) Could you look at this thread from General. --- I say the backend if you have one row type output result treats it as the full output result, it's really bad if you use STRUCT types (in your example you see few columns, but this should be one column!). I think backend should return ROWDESC(1), then per row data describe this row type data. In other words result should be as in my example but without last column. Because this funny behaviour is visible in psql in JDBC I think it's backend problem or some far inconsistency. I don't see this described in select statement. Kind regards, Radek On Tue, 11 Jan 2011 23:54:19 +0100, Lukas Eder wrote: Hmm, you're right, the result seems slightly different. But still the UDT record is not completely fetched as if it were selected directly from T_AUTHOR in a PreparedStatement... 2011/1/11 Radosław Smogura I've done: test=# CREATE FUNCTION p_enhance_address3 (address OUT u_address_type, i1 OUT int) AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE first_name = 'George'; i1 = 12; END; $$ LANGUAGE plpgsql; test=# select * from p_enhance_address3(); address | i1 + (((Parliament Hill,77),NW31A9),) | 12 (1 row) Result is ok. Because UDT is described in same way as row, it's looks like that backand do this nasty thing and instead of 1 column, it sends 6 in your case. Forward to hackers. Maybe they will say something, because I don;t see this in docs. Radek Lukas Eder Tuesday 11 January 2011 16:55:52 Looks to me like you're getting each field of the UDT as a separate column. You printed only the first column i.e. the 'street' part. Exactly, that's what I'm getting It might be informative to run with loglevel=2 and see how the server is returning results. If the driver is reporting 6 columns, that means that the server is reporting 6 fields in its RowDescription message. Here's what I get (there really is a RowDescription(6)): === 08:15:44.914 (1) PostgreSQL 9.0 JDBC4 (build 801) 08:15:44.923 (1) Trying to establish a protocol version 3 connection to localhost:5432 08:15:44.941 (1) FE= StartupPacket(user=postgres, database=postgres, client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2) 08:15:44.962 (1) 08:15:44.968 (1) FE= Password(md5digest=md5ea57d63c7d2afaed5abb3f0bb88ae7b8) 08:15:44.970 (1) 08:15:44.980 (1) 08:15:44.980 (1) 08:15:44.980 (1) 08:15:44.980 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) 08:15:44.981 (1) compatible = 9.0 08:15:44.981 (1) loglevel = 2 08:15:44.981 (1) prepare threshold = 5 getConnection returning driver[className=org.postgresql.Driver,org.postgresql.dri...@77ce3fc5] 08:15:45,021 DEBUG [org.jooq.impl.StoredProcedureImpl ] - Executing query : { call public.p_enhance_address2(?) } 08:15:45.035 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@ 2eda2cef, maxRows=0, fetchSize=0, flags=17 08:15:45.036 (1) FE= Parse(stmt=null,query=select * from public.p_enhance_address2() as result,oids={2278}) 08:15:45.037 (1) FE= Bind(stmt=null,portal=null,=) 08:15:45.038 (1) FE= Describe(portal=null) 08:15:45.038 (1) FE= Execute(portal=null,limit=0) 08:15:45.038 (1) FE= Sync 08:15:45.043 (1) 08:15:45.044 (1) 08:15:45.045 (1) 08:15:45.046 (1) 08:15:45.046 (1) 08:15:45.062 (1) org.postgresql.util.PSQLException: Ein CallableStatement wurde mit einer falschen Anzahl Parameter ausgeführt. at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2S tatement.java:408) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement. java:381) at org.jooq.impl.StoredProcedureImpl.execute(StoredProcedureImpl.java:125) at org.jooq.test.postgres.generatedclasses.Procedures.pEnhanceAddress2(Procedu res.java:91) [...] SQLException: SQLState(42601) 08:15:45.074 (1) FE= Terminate === Oops, looking closer I see what you mean, that's actually 2 columns of the surrounding type - street + zip? Yes, exactly. Somehow the driver stops at the second type element of the surrounding type. This may be correlated to the fact that the inner type has exactly 2 elements? What are the values of the other 5 columns reported by the driver? The other 5 columns are reported as null (always). In pgAdmin III, I correctly get a single column in the result set. Also, the postgres information_schema only holds one parameter: === select parameter_mode, parameter_name, udt_name from information_schema.parameters where specific_name like 'p_enhance_address2%'
Re: [HACKERS] multiset patch review
Hello there is one issue - probably useless checking a type equality in function check_comparable and check_concatinatable, because when your function is registrated with arguments (anyarray, anyarray), then is guaranteed so type of array1 is same as type of array2, and then you don't need to check. Regards Pavel Stehule 2011/1/12 Itagaki Takahiro itagaki.takah...@gmail.com: Thank you for the review. On Mon, Jan 10, 2011 at 04:13, Pavel Stehule pavel.steh...@gmail.com wrote: regress tests failed Fixed. There is often used a fragment + --fn.arg[0] = values1[n1]; + --fn.arg[1] = values2[n2]; + --fn.argnull[0] = false; + --fn.argnull[1] = false; + --fn.isnull = false; + --r = DatumGetInt32(FunctionCallInvoke(fn)); it can be moved to procedure? Agreed. I use FunctionCall2() instead of the fragments. I see only one issue. There isn't documented, what is a MULTISET? I added a short description about MULTISET and example of operators in Arrays 8.14.7. Multiset Support section in the docs. Is it enough? or what kind of information do you want? Separate patches for src and doc attached. It includes a few bug fixes and cleanup. I changed the error code in trim_array() to ERRCODE_ARRAY_ELEMENT_ERROR according to the spec. -- Itagaki Takahiro -- 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] ALTER TYPE 0: Introduction; test cases
On Tue, Jan 11, 2011 at 09:41:35PM -0500, Robert Haas wrote: On Tue, Jan 11, 2011 at 7:25 AM, Noah Misch n...@leadboat.com wrote: On Tue, Jan 11, 2011 at 06:37:33AM -0500, Robert Haas wrote: On Sun, Jan 9, 2011 at 4:59 PM, Noah Misch n...@leadboat.com wrote: This begins the patch series for the design I recently proposed[1] for avoiding some table rewrites in ALTER TABLE ... ALTER COLUMN ... TYPE. ?I'm posting these patches today: 0 - new test cases This doesn't look right. ?You might be building it, but you sure aren't rebuilding it. +CREATE TABLE parent (keycol numeric PRIMARY KEY); +NOTICE: ?CREATE TABLE / PRIMARY KEY will create implicit index parent_pkey for table parent +DEBUG: ?Rebuilding index parent_pkey Yes. ?I considered saying Building unconditionally. ?Differentiating the debug message by passing down the fact that the index recently existed seemed like overkill. ?What do you think? I'm wondering if we should consider moving this call to index_build() so that it appears everywhere that we build an index rather than just in ALTER TABLE, and saying something like: building index %s on table %s The patch does have it in index_build. That new wording seems better. The theoretical basis is that users can do little to directly change the need to rebuild a TOAST index. ?We'll rebuild the TOAST index if and only if we rewrote the table. ?The practical basis is that the TOAST relation names contain parent relation OIDs, so we don't want them mentioned in regression test output. Perhaps in this case we could write: building TOAST index for table %s Good idea; thanks. I'll incorporate those changes into the next version. -- 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] multiset patch review
On Wed, Jan 12, 2011 at 20:18, Pavel Stehule pavel.steh...@gmail.com wrote: there is one issue - probably useless checking a type equality in function check_comparable and check_concatinatable, because when your function is registrated with arguments (anyarray, anyarray), then is guaranteed so type of array1 is same as type of array2, and then you don't need to check. It's true for almost all cases, but we have anyarray columns in pg_statistic.stavaluesN. When we pass them to those array functions, element types of two anyarrays could be different. I guess they are protections only for them. =# SELECT A.stavalues1 SUBMULTISET OF B.stavalues1 FROM pg_statistic A, pg_statistic B WHERE A.stakind1 = 2 AND B.stakind1 = 2; ERROR: cannot compare incompatible arrays DETAIL: Arrays with element types name and oid[] are not compatible for comparison. -- Itagaki Takahiro -- 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] multiset patch review
2011/1/12 Itagaki Takahiro itagaki.takah...@gmail.com: On Wed, Jan 12, 2011 at 20:18, Pavel Stehule pavel.steh...@gmail.com wrote: there is one issue - probably useless checking a type equality in function check_comparable and check_concatinatable, because when your function is registrated with arguments (anyarray, anyarray), then is guaranteed so type of array1 is same as type of array2, and then you don't need to check. It's true for almost all cases, but we have anyarray columns in pg_statistic.stavaluesN. When we pass them to those array functions, element types of two anyarrays could be different. I guess they are protections only for them. =# SELECT A.stavalues1 SUBMULTISET OF B.stavalues1 FROM pg_statistic A, pg_statistic B WHERE A.stakind1 = 2 AND B.stakind1 = 2; ERROR: cannot compare incompatible arrays DETAIL: Arrays with element types name and oid[] are not compatible for comparison. ook Pavel -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SQL/MED - FDW API
Attached are WIP version of patches for FDW API. Basically not changed from last version but rebased to current HEAD. To make review easier, I split core functionality into 3 patches. Please apply these patches in the following order. 1) fdw_handler - this patch adds HANDLER option to both syntax and catalog of FOREIGN DATA WRAPPER. 2) foreign_scan - this patch adds following: ForeignScan executor-node, hooks in planner and executor, and FdwRoutine (FDW API). 3) catalog_lookup - this patch adds GetForeignTable() whicch returns ForeignTable object, similar to GetForeignDataWrapper(), GetForeignServer(), and GetUserMapping(). This function is assumed to be used by FDWs. You would be able to test these patches with file_fdw wrapper which would be posted in another thread. Regards, -- Shigeru Hanada fdw_handler.patch.gz Description: Binary data foreign_scan.patch.gz Description: Binary data catalog_lookup.patch.gz 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] multiset patch review
2011/1/12 Pavel Stehule pavel.steh...@gmail.com: 2011/1/12 Itagaki Takahiro itagaki.takah...@gmail.com: On Wed, Jan 12, 2011 at 20:18, Pavel Stehule pavel.steh...@gmail.com wrote: there is one issue - probably useless checking a type equality in function check_comparable and check_concatinatable, because when your function is registrated with arguments (anyarray, anyarray), then is guaranteed so type of array1 is same as type of array2, and then you don't need to check. It's true for almost all cases, but we have anyarray columns in pg_statistic.stavaluesN. When we pass them to those array functions, element types of two anyarrays could be different. I guess they are protections only for them. =# SELECT A.stavalues1 SUBMULTISET OF B.stavalues1 FROM pg_statistic A, pg_statistic B WHERE A.stakind1 = 2 AND B.stakind1 = 2; ERROR: cannot compare incompatible arrays DETAIL: Arrays with element types name and oid[] are not compatible for comparison. ook so I think it is ready for commit Regards Pavel Stehule Pavel -- Itagaki Takahiro -- 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] SSI and 2PC
On Tue, Jan 11, 2011 at 12:34:44PM -0600, Kevin Grittner wrote: Agreed; but I am starting to get concerned about whether this particular area can be completed by the start of the CF. I might run a few days over on 2PC support. Unless ... Dan? Could you look into this while I chase down the issue Anssi raised? I'll take a look at it, but be forewarned that I currently know extremely little about 2PC in Postgres... Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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_depend explained
Excerpts from Joel Jacobson's message of mié ene 12 07:07:35 -0300 2011: The automatically created objects, such as primary key indexes, constraints and triggers, have been ignored in this graph, as they are implicitly created when creating the base objects. FWIW this idea fails when you consider stuff such as circular foreign keys (and I suppose there are other, more common cases). If you really want something general you need to break those apart. (This is the explanation for the “break the loop” code in pg_dump I imagine) -- Á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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 1:07 AM, David E. Wheeler wrote: On Jan 11, 2011, at 2:25 PM, Alexey Klyukin wrote: Hello, Here's the patch that improves handling of arrays as pl/perl function input arguments, converting postgres arrays of arbitrary dimensions into perl array references. Awesome! I've wanted this for *years*. It includes regression tests and a documentation changes, and it builds and runs successfully on my mac os x and linux boxes. To maintain compatibility with existing pl/perl code a new variable, plperl.convert_array_arguments (better name?), is introduced. Its default value is false, when set to true it triggers the new behavior, i.e. Have you considered instead passing an array-based object with is string overloading designed to return the pg array string format? That would make for nice, transparent compatibility without the need for a GUC. You mean packing both a string representation and a reference to a single SV * value? I haven't considered that (lack of extensive perlgus-foo) although I think that's an interesting idea. One drawback would be that it would require both conversion to a string format and to a perl reference, performing unnecessary actions during every time arrays are passed to a pl/perl function. If there is a strong dislike of the proposed 'compatibility' GUC option - I think I can change the existing patch to incorporate array string representation into the reference-holding SV quite easily. /A -- Alexey Klyukin The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TYPE 1: recheck index-based constraints
On Tue, Jan 11, 2011 at 10:03:11PM -0500, Robert Haas wrote: On Sun, Jan 9, 2011 at 5:00 PM, Noah Misch n...@leadboat.com wrote: When ALTER TABLE rewrites a table, it reindexes, but the reindex does not revalidate UNIQUE/EXCLUDE constraints. ?This behaves badly in cases like this, neglecting to throw an error on the new UNIQUE violation: CREATE TABLE t (c numeric UNIQUE); INSERT INTO t VALUES (1.1),(1.2); ALTER TABLE t ALTER c TYPE int; The comment gave a reason for skipping the checks: it would cause deadlocks when we rewrite a system catalog. ?So, this patch changes things to only skip the check for system catalogs. It looks like this behavior was introduced by Tom's commit 1ddc2703a936d03953657f43345460b9242bbed1 on 2010-02-07, and it appears to be quite broken. The behavior is reasonable in the case of VACUUM FULL and CLUSTER, but your example demonstrates that it's completely broken in the case of ALTER TABLE. This strikes me as something we need to fix in REL9_0_STABLE as well as master, and my gut feeling is that we ought to fix it not by excluding system relations but by making ALTER TABLE work differently from VACUUM FULL and CLUSTER. There's an efficiency benefit to skipping this even on normal relations in cases where it isn't necessary, and it shouldn't be necessary if we're rewriting the rows unchanged. Something like the attached? Also, you need to start adding these patches to the CF app. Done for all. *** a/src/backend/catalog/index.c --- b/src/backend/catalog/index.c *** *** 2543,2558 reindex_index(Oid indexId, bool skip_constraint_checks) * do CCI after having collected the index list. (This way we can still use * catalog indexes while collecting the list.) * ! * We also skip rechecking uniqueness/exclusion constraint properties if ! * heap_rebuilt is true. This avoids likely deadlock conditions when doing ! * VACUUM FULL or CLUSTER on system catalogs. REINDEX should be used to ! * rebuild an index if constraint inconsistency is suspected. * * Returns true if any indexes were rebuilt. Note that a * CommandCounterIncrement will occur after each index rebuild. */ bool ! reindex_relation(Oid relid, bool toast_too, bool heap_rebuilt) { Relationrel; Oid toast_relid; --- 2543,2559 * do CCI after having collected the index list. (This way we can still use * catalog indexes while collecting the list.) * ! * If we rebuilt a heap without changing tuple values, we also skip rechecking ! * uniqueness/exclusion constraint properties. This avoids likely deadlock ! * conditions when doing VACUUM FULL or CLUSTER on system catalogs. REINDEX ! * should be used to rebuild an index if constraint inconsistency is suspected. * * Returns true if any indexes were rebuilt. Note that a * CommandCounterIncrement will occur after each index rebuild. */ bool ! reindex_relation(Oid relid, bool toast_too, bool heap_rebuilt, !bool tuples_changed) { Relationrel; Oid toast_relid; *** *** 2629,2635 reindex_relation(Oid relid, bool toast_too, bool heap_rebuilt) if (is_pg_class) RelationSetIndexList(rel, doneIndexes, InvalidOid); ! reindex_index(indexOid, heap_rebuilt); CommandCounterIncrement(); --- 2630,2636 if (is_pg_class) RelationSetIndexList(rel, doneIndexes, InvalidOid); ! reindex_index(indexOid, heap_rebuilt !tuples_changed); CommandCounterIncrement(); *** *** 2661,2670 reindex_relation(Oid relid, bool toast_too, bool heap_rebuilt) /* * If the relation has a secondary toast rel, reindex that too while we !* still hold the lock on the master table. */ if (toast_too OidIsValid(toast_relid)) ! result |= reindex_relation(toast_relid, false, false); return result; } --- 2662,2674 /* * If the relation has a secondary toast rel, reindex that too while we !* still hold the lock on the master table. There's never a reason to !* reindex the toast table if heap_rebuilt; it will always be fresh. */ + Assert(!(toast_too heap_rebuilt)); if (toast_too OidIsValid(toast_relid)) ! result |= reindex_relation(toast_relid, false, heap_rebuilt, ! tuples_changed); return result; } *** a/src/backend/commands/cluster.c --- b/src/backend/commands/cluster.c *** *** 565,571 rebuild_relation(Relation OldHeap, Oid indexOid, * rebuild the target's indexes and throw away the
Re: [HACKERS] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 4:06 AM, Robert Haas wrote: On Tue, Jan 11, 2011 at 7:55 PM, Andrew Dunstan and...@dunslane.net wrote: On 01/11/2011 07:17 PM, David E. Wheeler wrote: On Jan 11, 2011, at 3:44 PM, Andrew Dunstan wrote: I think there's at least a danger of breaking legacy code doing that. Say you have some code that does a ref test on the argument, for example. The behavior would now be changed. I think that'd be pretty rare. Possibly it would. But we usually try pretty hard to avoid that sort of breakage. By the same token, I'm not convinced it's a good idea for this behavior to be off by default. Surely many people will altogether fail to notice that it's an option? If we're going to have a backward-compatibility GUC at all, ISTM that you ought to get the good stuff unless you ask for the old way. I think the number of people failing to notice the changes would be the same whenever we set the new or the old behavior by default. I decided to default to the the old behavior since it won't break the existing code as opposed to just hiding the good stuff, although it would slower the adoption of the new behavior. /A -- Alexey Klyukin The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/12 Alvaro Herrera alvhe...@commandprompt.com: FWIW this idea fails when you consider stuff such as circular foreign keys (and I suppose there are other, more common cases). If you really want something general you need to break those apart. (This is the explanation for the “break the loop” code in pg_dump I imagine) Good point. Yes, the algorithm must be able to break the loop, i.e. remove the edges causing the loops. This has already been properly solved in pg_dump, but I frankly don't really understand exactly how this is done, at least not the general rule on how to do it, even after spending hours studying the source code. Also, circular dependencies seems impossible for some object classes, such as functions, views, constraints and triggers. None of them can possibly refer to them self. If you only need to create/drop objects of these classes (like in my case within the pov-project), it's not important to break the loop in a clever way, i.e. simply removing any of the edges, not necessarily the best suited one, will do for my purpose. I have updated the example with two circular relations: -- Circular dependencies: CREATE TABLE tselfref ( id int not null PRIMARY KEY, parentid int not null REFERENCES tselfref(id) ); CREATE TABLE tcircular ( id int not null PRIMARY KEY, id2 int not null REFERENCES tselfref(id) ); ALTER TABLE tselfref ADD COLUMN id2 int not null REFERENCES tcircular ( id ); I have also updated pd_depend.[sql|dot|png|svg] and pg_depend_actual.[dot|png|svg] with the circular references. The dotted edges in pg_depend_actual are the edges which must be removed to break the loop. Any ideas on how to design an algorithm to transform the digraph pg_depend into pg_depend_actual are highly appreciated. -- Best regards, Joel Jacobson Glue Finance -- 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] ALTER TYPE 1: recheck index-based constraints
On Wed, Jan 12, 2011 at 8:14 AM, Noah Misch n...@leadboat.com wrote: Something like the attached? I haven't really analyzed in this detail, but yes, approximately something sorta like that. Also, you need to start adding these patches to the CF app. Done for all. Thanks. -- 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] Add support for logging the current role
Greetings, Minor enhancement, but a valuable one imv. Hopefully there aren't any issues with it. :) Thanks! Stephen commit 3cb707aa9f228e629e7127625a76a223751a778b Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 09:17:31 2011 -0500 Add support for logging the current role This adds a '%o' option to the log_line_prefix GUC which will log the current role. The '%u' option only logs the Session user, which can be misleading, but it's valuable to have both options. *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** *** 3508,3513 local0.*/var/log/postgresql --- 3508,3518 entryyes/entry /row row + entryliteral%o/literal/entry + entryCurrent role name/entry + entryyes/entry + /row + row entryliteral%d/literal/entry entryDatabase name/entry entryyes/entry *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *** *** 1826,1831 log_line_prefix(StringInfo buf, ErrorData *edata) --- 1826,1841 appendStringInfoString(buf, username); } break; + case 'o': + if (MyProcPort) + { + const char *rolename = GetUserNameFromId(GetUserId()); + + if (rolename == NULL || *rolename == '\0') + rolename = _([unknown]); + appendStringInfoString(buf, rolename); + } + break; case 'd': if (MyProcPort) { signature.asc Description: Digital signature
Re: [HACKERS] multiset patch review
Excerpts from Itagaki Takahiro's message of mié ene 12 01:52:12 -0300 2011: Separate patches for src and doc attached. It includes a few bug fixes and cleanup. I changed the error code in trim_array() to ERRCODE_ARRAY_ELEMENT_ERROR according to the spec. Two small nitpicks: + static void + check_concatinatable(Oid element_type1, Oid element_type2) + { + if (element_type1 != element_type2) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg(cannot concatenate incompatible arrays), + errdetail(Arrays with element types %s and %s are not +compatible for concatenation., +format_type_be(element_type1), +format_type_be(element_type2; + } I think the word is either concatenable or concatenatable. Also please don't break up the string in errdetail() even if it's longer than 80 chars. (The function below this one has this too) I didn't read the patch in much more detail. -- Á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] Compatibility GUC for serializable
Josh Berkus wrote: Mainly, that it's not clear we need it. Nobody's pointed to a concrete failure mechanism that makes it necessary for an existing app to run under fake-SERIALIZABLE mode. I think it's quite possible that you're right, and nobody depends on current SERIALIZABLE behavior because it's undependable. However, we don't *know* that -- most of our users aren't on the mailing lists, especially those who use packaged vendor software. That being said, the case for a backwards-compatiblity GUC is weak, and I'd be ok with not having one barring someone complaining during beta, or survey data showing that there's more SERIALIZABLE users than we think. Oh, survey: http://www.postgresql.org/community/ That Survey's missing one important distinction for that discussion. Do you take the the current survey answer Yes, we depend on it for production code to imply Yes, we depend on actual real SERIALIZABLE transactions in production and will panic if you tell us we're not getting that or Yes, we depend on the legacy not-quite SERIALIZABLE transactions in production and don't want real serializable transactions -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
On Wed, Jan 12, 2011 at 9:23 AM, Stephen Frost sfr...@snowman.net wrote: Minor enhancement, but a valuable one imv. Hopefully there aren't any issues with it. :) 1. Why %o? That's not obviously mnemonic. Perhaps %U? 2. It won't be clear to people reading this what the difference is between %u and this. You probably need to reword the documentation for the existing option as well as documenting the new one. 3. Please attach the patch rather than including it inline, if possible. -- 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] Add support for logging the current role
* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 12, 2011 at 9:23 AM, Stephen Frost sfr...@snowman.net wrote: Minor enhancement, but a valuable one imv. Hopefully there aren't any issues with it. :) 1. Why %o? That's not obviously mnemonic. Perhaps %U? r was taken? :) I'm not sure I like %U, but in the end I don't *really* care. I'll update it to %U and wait for someone else to complain. 2. It won't be clear to people reading this what the difference is between %u and this. You probably need to reword the documentation for the existing option as well as documenting the new one. Fair enough. 3. Please attach the patch rather than including it inline, if possible. Hrm, I could have sworn that Tom had asked for the exact opposite in the past, but either way is fine by me. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Add support for logging the current role
On Wed, Jan 12, 2011 at 10:12 AM, Stephen Frost sfr...@snowman.net wrote: r was taken? :) I'm not sure I like %U, but in the end I don't *really* care. I'll update it to %U and wait for someone else to complain. The joys of community... 3. Please attach the patch rather than including it inline, if possible. Hrm, I could have sworn that Tom had asked for the exact opposite in the past, but either way is fine by me. Really? I don't remember that, but it's certainly possible. My problem is that cutting and pasting from a browser window into a patch file tends to be a little iffy. If you paste too much or too little or the whitespace doesn't come out quite right, the patch doesn't apply. -- 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
pg_ctl failover Re: [HACKERS] Latches, signals, and waiting
On Wed, Sep 15, 2010 at 11:14 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 15/09/10 16:55, Tom Lane wrote: So I'm wondering if we couldn't eliminate the five-second sleep requirement here too. It's problematic anyhow, since somebody looking for energy efficiency will still feel it's too short, while somebody concerned about fast failover will feel it's too long. Yep. Could the standby triggering protocol be modified so that it involves sending a signal, not just creating a file? Seems reasonable, at least if we still provide an option for more frequent polling and no need to send signal. (One issue is that it's not clear what that'd translate to on Windows.) pg_ctl failover ? At the moment, the location of the trigger file is configurable, but if we accept a constant location like $PGDATA/failover pg_ctl could do the whole thing, create the file and send signal. pg_ctl on Window already knows how to send the signal via the named pipe signal emulation. The attached patch implements the above-mentioned pg_ctl failover. Comments? Objections? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center pg_ctl_failover_v1.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] Allowing multiple concurrent base backups
On Wed, Jan 12, 2011 at 10:26:05AM +0100, marcin mank wrote: On Tue, Jan 11, 2011 at 8:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Tue, Jan 11, 2011 at 19:51, Tom Lane t...@sss.pgh.pa.us wrote: Seems like either one of these is fairly problematic in that you have to have some monstrous kluge to get the backup_label file to appear with the right name in the tarfile. How badly do we actually need this? I don't think the use-case for concurrent base backups is all that large in practice given the I/O hit it's going to involve. I think it can be done cleaner in the tar file injection - I've been chatting with Heikki offlist about that. Not sure, but I have a feeling it does. One point that I'm particularly interested to see how you'll kluge it is ensuring that the tarball contains only the desired temp data and not also the real $PGDATA/backup_label, should there be a normal base backup being done concurrently with the streamed one. The whole thing just seems too fragile and dangerous to be worth dealing with given that actual usage will be a corner case. *I* sure wouldn't trust it to work when the chips were down. Maybe if pg_start_backup() notices that there is another backup running should block waiting for another session to run pg_stop_backup() ? Or have a new function like pg_start_backup_wait() ? Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell That's not actually true. Backups at the moment are CPU-bound, and running them in parallel is one way to make them closer to I/O-bound, which is what they *should* be. There are other proposals out there, and some work being done, to make backups less dependent on CPU, among them: - Making the on-disk representation smaller - Making COPY more efficient As far as I know, none of this work is public yet. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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_depend explained
Joel Jacobson j...@gluefinance.com writes: Also, circular dependencies seems impossible for some object classes, such as functions, views, constraints and triggers. regression=# create table tt(f1 int, f2 int); CREATE TABLE regression=# create view v1 as select * from tt; CREATE VIEW regression=# create view v2 as select * from v1; CREATE VIEW regression=# create or replace view v1 as select * from v2; CREATE VIEW regression=# drop view v1; ERROR: cannot drop view v1 because other objects depend on it DETAIL: view v2 depends on view v1 HINT: Use DROP ... CASCADE to drop the dependent objects too. regression=# drop view v2; ERROR: cannot drop view v2 because other objects depend on it DETAIL: view v1 depends on view v2 HINT: Use DROP ... CASCADE to drop the dependent objects too. This isn't particularly *useful*, maybe, but it's hardly impossible. And if we analyzed function dependencies in any detail, circular dependencies among functions would be possible (and useful). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing multiple concurrent base backups
On 12.01.2011 17:15, David Fetter wrote: On Wed, Jan 12, 2011 at 10:26:05AM +0100, marcin mank wrote: Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell That's not actually true. Backups at the moment are CPU-bound, and running them in parallel is one way to make them closer to I/O-bound, which is what they *should* be. That's a different kind of parallel. We're talking about taking multiple backups in parallel, each using one process, and you're talking about taking one backup using multiple parallel processes or threads. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing multiple concurrent base backups
On Wed, Jan 12, 2011 at 05:17:38PM +0200, Heikki Linnakangas wrote: On 12.01.2011 17:15, David Fetter wrote: On Wed, Jan 12, 2011 at 10:26:05AM +0100, marcin mank wrote: Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell That's not actually true. Backups at the moment are CPU-bound, and running them in parallel is one way to make them closer to I/O-bound, which is what they *should* be. That's a different kind of parallel. We're talking about taking multiple backups in parallel, each using one process, and you're talking about taking one backup using multiple parallel processes or threads. Good point. The idea that IO/network bandwidth is always saturated by one backup just isn't true, though. Take the case of multiple independent NICs, e.g. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing multiple concurrent base backups
On Wed, Jan 12, 2011 at 10:15 AM, David Fetter da...@fetter.org wrote: Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell That's not actually true. Backups at the moment are CPU-bound, and running them in parallel is one way to make them closer to I/O-bound, which is what they *should* be. Remember, we're talking about filesystem base backups here. If you're CPU can't handle a stream from disk - network, byte for byte (maybe encrypting it), then you've spend *WY* to much on your storage sub-system, and way to little on CPU. I can see trying to parallize the base backup such that each table-space could be run concurrently, but that's about it. There are other proposals out there, and some work being done, to make backups less dependent on CPU, among them: - Making the on-disk representation smaller - Making COPY more efficient As far as I know, none of this work is public yet. pg_dump is another story. But it's not related to base backups for PIT Recovery/Replication. a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing multiple concurrent base backups
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 12.01.2011 17:15, David Fetter wrote: On Wed, Jan 12, 2011 at 10:26:05AM +0100, marcin mank wrote: Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell That's not actually true. Backups at the moment are CPU-bound, and running them in parallel is one way to make them closer to I/O-bound, which is what they *should* be. That's a different kind of parallel. We're talking about taking multiple backups in parallel, each using one process, and you're talking about taking one backup using multiple parallel processes or threads. Even more to the point, you're confusing pg_dump with a base backup. The reason pg_dump eats a lot of CPU is primarily COPY's data conversion and formatting requirements, none of which will happen in a base backup (streaming or otherwise). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
Greetings, * Robert Haas (robertmh...@gmail.com) wrote: 1. Why %o? That's not obviously mnemonic. Perhaps %U? 2. It won't be clear to people reading this what the difference is between %u and this. You probably need to reword the documentation for the existing option as well as documenting the new one. 3. Please attach the patch rather than including it inline, if possible. Updated patch attached- commit 7319e8ddc91d62addea25b85f7dbe2f95132cdc1 Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 10:23:13 2011 -0500 Use %U for role in log_line_prefix; improve docs Change the variable for logging the current role in log_line_prefix from %o to %U, to better reflect the 'user'-type mnemonic. Improve the documentation for the %U and %u log_line_prefix options to better differentiate them from each other. commit 3cb707aa9f228e629e7127625a76a223751a778b Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 09:17:31 2011 -0500 Add support for logging the current role This adds a '%o' option to the log_line_prefix GUC which will log the current role. The '%u' option only logs the Session user, which can be misleading, but it's valuable to have both options. Thanks! Stephen *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** *** 3504,3510 local0.*/var/log/postgresql /row row entryliteral%u/literal/entry ! entryUser name/entry entryyes/entry /row row --- 3504,3515 /row row entryliteral%u/literal/entry ! entryUser name which was used to authenticate to productnamePostgreSQL/productname with/entry ! entryyes/entry ! /row ! row ! entryliteral%U/literal/entry ! entryCurrent role name, set via commandSET ROLE/, the current role identifier is relevant for permission checking/entry entryyes/entry /row row *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *** *** 1826,1831 log_line_prefix(StringInfo buf, ErrorData *edata) --- 1826,1841 appendStringInfoString(buf, username); } break; + case 'U': + if (MyProcPort) + { + const char *rolename = GetUserNameFromId(GetUserId()); + + if (rolename == NULL || *rolename == '\0') + rolename = _([unknown]); + appendStringInfoString(buf, rolename); + } + break; case 'd': if (MyProcPort) { signature.asc Description: Digital signature
Re: [HACKERS] Add support for logging the current role
Robert Haas robertmh...@gmail.com writes: On Wed, Jan 12, 2011 at 10:12 AM, Stephen Frost sfr...@snowman.net wrote: Hrm, I could have sworn that Tom had asked for the exact opposite in the past, but either way is fine by me. Really? I don't remember that, but it's certainly possible. I don't remember saying exactly that either. The main point is to ensure the patch doesn't get mangled in transmission. I've seen people screw it up both ways: inline is much more vulnerable to mailers deciding to whack whitespace around, while attachments are vulnerable to being encoded in all sorts of weird ways, some of which come out nicely in the archives and some of which don't. I'm not in favor of gzipping small patches that could perfectly well be left in readable form. This particular patch looks fine here: http://archives.postgresql.org/pgsql-hackers/2011-01/msg00845.php so I'm thinking Stephen doesn't need to revisit his technique. +1 for choosing something more mnemonic than %o, btw. 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] Allowing multiple concurrent base backups
On Wed, Jan 12, 2011 at 10:24:31AM -0500, Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 12.01.2011 17:15, David Fetter wrote: On Wed, Jan 12, 2011 at 10:26:05AM +0100, marcin mank wrote: Considering that parallell base backups would be io-bound (or network-bound), there is little need to actually run them in parallell That's not actually true. Backups at the moment are CPU-bound, and running them in parallel is one way to make them closer to I/O-bound, which is what they *should* be. That's a different kind of parallel. We're talking about taking multiple backups in parallel, each using one process, and you're talking about taking one backup using multiple parallel processes or threads. Even more to the point, you're confusing pg_dump with a base backup. The reason pg_dump eats a lot of CPU is primarily COPY's data conversion and formatting requirements, none of which will happen in a base backup (streaming or otherwise). Oops. That'll teach me to post before coffee. :P Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
* Tom Lane (t...@sss.pgh.pa.us) wrote: +1 for choosing something more mnemonic than %o, btw. Alright, not to be *too* ridiculous about this, but I'm feeling like '%R' might be better than '%U', if we don't mind overloading a single letter based on case. I've always been annoyed at the lack of distinction between 'user' and 'role' in our docs and feel it does lead to some confusion. Updated patch attached, if people agree. Compiles, passes regressions, works as advertised, etc. commit bba27fe63702405514ed2c3bb72b70cc178f9ce1 Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 10:38:24 2011 -0500 Change log_line_prefix for current role to %R As we're going for a mnemonic, and this is really about roles instead of users, change log_line_prefix argument to %R from %U for current_role. Thanks, Stephen *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** *** 3504,3510 local0.*/var/log/postgresql /row row entryliteral%u/literal/entry ! entryUser name/entry entryyes/entry /row row --- 3504,3515 /row row entryliteral%u/literal/entry ! entryUser name which was used to authenticate to productnamePostgreSQL/productname with/entry ! entryyes/entry ! /row ! row ! entryliteral%R/literal/entry ! entryCurrent role name, set via commandSET ROLE/, the current role identifier is relevant for permission checking/entry entryyes/entry /row row *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *** *** 1826,1831 log_line_prefix(StringInfo buf, ErrorData *edata) --- 1826,1841 appendStringInfoString(buf, username); } break; + case 'R': + if (MyProcPort) + { + const char *rolename = GetUserNameFromId(GetUserId()); + + if (rolename == NULL || *rolename == '\0') + rolename = _([unknown]); + appendStringInfoString(buf, rolename); + } + break; case 'd': if (MyProcPort) { signature.asc Description: Digital signature
Re: [HACKERS] Add support for logging the current role
On Wed, Jan 12, 2011 at 10:43 AM, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: +1 for choosing something more mnemonic than %o, btw. Alright, not to be *too* ridiculous about this, but I'm feeling like '%R' might be better than '%U', if we don't mind overloading a single letter based on case. I've always been annoyed at the lack of distinction between 'user' and 'role' in our docs and feel it does lead to some confusion. Updated patch attached, if people agree. Compiles, passes regressions, works as advertised, etc. I was thinking that %u/%U would have the advantage of implying some connection between the two things which is in fact present. %r/%R seems not quite as good to me. Also, let's paint it tangerine. -- 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] Compatibility GUC for serializable
Ron Mayer rm...@cheapcomplexdevices.com wrote: That Survey's missing one important distinction for that discussion. Do you take the the current survey answer Yes, we depend on it for production code to imply Yes, we depend on actual real SERIALIZABLE transactions in production and will panic if you tell us we're not getting that or Yes, we depend on the legacy not-quite SERIALIZABLE transactions in production and don't want real serializable transactions Yeah, I was reluctant to reply to that survey because we rely on it to the extent that it works now, but it would not break anything if we dropped in a real SERIALIZABLE implementation. I fear that choosing the depend on it answer would imply don't want changes. -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] Add support for logging the current role
* Robert Haas (robertmh...@gmail.com) wrote: I was thinking that %u/%U would have the advantage of implying some connection between the two things which is in fact present. %r/%R seems not quite as good to me. Also, let's paint it tangerine. I figured that's where you were going. +1 for whatever the committer wants to commit. ;) Stephen signature.asc Description: Digital signature
[HACKERS] psql crashes on encoding mismatch
I found a crash case (assertion failure) when runing psql -f utf8_encoded_script.sql against client_encoding = shift_jis in postgresql.conf. Though encoding mismatch is obviously user's fault, a crash doesn't explain anything to him. The thing is, prepare_buffer() in psqlscan.l assumes PQmblen() always returns the appropriate length, but it actually isn't. newtxt can be overflowed on those cases into the following 2 byte NULLs, which is filled in the beginning of prepare_buffer(). It results in that yy_scan_buffer() returns NULL by design since the input buffer's following 2 bytes are not NULL. Then, psql_assert(state-scanbufhandle) in psql_scan() detects bug later. This bug can be occurred not only in shift_jis but also big5, bgk, etc. unsafe encodings. The attached is to fix it. Just double check not to pad overflowed 0xff for the input buffer. If you need unit case I'll send it, but the problem seems clear. Regards, -- Hitoshi Harada psql_encoding_bugfix.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] Add support for logging the current role
On Wed, Jan 12, 2011 at 11:00 AM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: I was thinking that %u/%U would have the advantage of implying some connection between the two things which is in fact present. %r/%R seems not quite as good to me. Also, let's paint it tangerine. I figured that's where you were going. +1 for whatever the committer wants to commit. ;) OK, done. :-) -- 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] WIP: Range Types
On Wed, Jan 12, 2011 at 2:16 AM, Martijn van Oosterhout klep...@svana.org wrote: On Tue, Jan 11, 2011 at 09:24:08PM -0500, Robert Haas wrote: commit 6c412f0605afeb809014553ff7ad28cf9ed5526b Author: Tom Lane t...@sss.pgh.pa.us Date: Sun May 1 18:56:19 2005 + Change CREATE TYPE to require datatype output and send functions to have only one argument. (Per recent discussion, the option to accept multiple arguments is pretty useless for user-defined types, and would be a likely source of security holes if it was used.) Simplify call sites of output/send functions to not bother passing more than one argument. ...but I don't understand the motivation behind it. IIRC, the issue is that a type output function has to interpret a Datum. Type output functions can also be called by users, so it is not guarenteed that the given OID would actually match the type of the Datum given. Hence the decision that the output function must be able to determine itself what kind of Datum it is dealing with. Thought experiment: the datum is an integer, but the oid says it's a pass-by-ref datum. Now the code may now to use the integer to derefence an arbitrary place in memory. I guess that begs the question of why we need to allow users to call type output functions directly. -- 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] libpq documentation cleanups (repost 3)
The attached patch is a collection of libpq documentation cleanups recommended in a list of changes emailed to me by Leslie S Satenstein. Leslie found a number of places our libpq documentation that were unclear or awkward, and this diff generated by me attempts to address them. I have already updated the documentation proofreading wiki: http://wiki.postgresql.org/wiki/Documentation_Proofreading -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + /pgpatches/libpq.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
Robert Haas robertmh...@gmail.com writes: On Wed, Jan 12, 2011 at 11:00 AM, Stephen Frost sfr...@snowman.net wrote: +1 for whatever the committer wants to commit. ;) OK, done. :-) Uh, did you actually stop to *think* about this patch? What you have just committed puts a syscache lookup into the elog output path. Quite aside from the likely performance hit, this will malfunction badly in any case where we're trying to log from an aborted transaction. Please revert and rethink. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
On Wed, Jan 12, 2011 at 11:53 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Jan 12, 2011 at 11:00 AM, Stephen Frost sfr...@snowman.net wrote: +1 for whatever the committer wants to commit. ;) OK, done. :-) Uh, did you actually stop to *think* about this patch? You have a valid point here, but this isn't the most tactful way of putting it. What you have just committed puts a syscache lookup into the elog output path. Quite aside from the likely performance hit, this will malfunction badly in any case where we're trying to log from an aborted transaction. Please revert and rethink. I think it's going to take more than a rethink - I don't see any way to salvage it. :-( -- 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] libpq documentation cleanups (repost 3)
On Wed, Jan 12, 2011 at 11:53 AM, Bruce Momjian br...@momjian.us wrote: The attached patch is a collection of libpq documentation cleanups recommended in a list of changes emailed to me by Leslie S Satenstein. Leslie found a number of places our libpq documentation that were unclear or awkward, and this diff generated by me attempts to address them. I have already updated the documentation proofreading wiki: http://wiki.postgresql.org/wiki/Documentation_Proofreading I don't think changing see below to refer below or call to execute is an improvement; even if we did that uniformly throughout our documentation, surely future editors are going to reuse those phrasings. A lot of these other changes look pretty dubious too, although some seem worthwhile. -- 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] Add support for logging the current role
* Tom Lane (t...@sss.pgh.pa.us) wrote: Uh, did you actually stop to *think* about this patch? Actually, I was worried about exactly that, but I didn't see anything at the top of elog.c that indicated if it'd be a problem or not (and the Syscache lookup issue was *exactly* what I was looking for). :( There was much discussion about recursion and memory commit and whatnot, but nothing about SysCache lookups. What you have just committed puts a syscache lookup into the elog output path. Quite aside from the likely performance hit, this will malfunction badly in any case where we're trying to log from an aborted transaction. I had been looking into storing the current role inside the Proc struct or in some new variable and then pulling it from there (updating it when needed during a SET ROLE, of course), but it seemed a bit of overkill if it wasn't necessary (which wasn't obvious to me). We could also just log the role's OID (%o anyone..?), since that doesn't need a syscache lookup to get at. I'd much rather log the role name if we can tho. I had looked through some of the other calls happening in log_line_prefix and didn't see any explicit syscache lookups but it seemed like we were doing quite a few other things that might have issues, so I had assumed it'd be alright. Sorry about that. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] WIP: RangeTypes
On Tue, 2011-01-11 at 11:13 -0800, David Fetter wrote: 3. Typmod -- There is still one annoyance about typmod remaining. I need to treat it like an array in find_typmod_coercion_function(), and then create a coercion expression. Is it worth it? Would typmod on a range be confusing, or should I just finish this item up? Probably not worth it for the first round. OK, I'll block typmods for range types for now. 4. Docs Happy to help evenings this week :) 5. Tests Same. What do you have so far? Great! I think the best tests would be around the ANYRANGE type mechanism to see if anything seems wrong or limiting. Particularly, its interaction with ANYELEMENT. 7. Right now the parse function is quite dumb. Is there some example code I should follow to make sure I get this right? KISS is a fine principle. Do you really need it smart on the first round? :) Well, it needs to be correct ;) Specifically, I think there will be a problem if there is a multibyte character following a backslash. There may be other problems, as well. I could probably get these fixed, but it might be better to follow patterns in other code. I'll look into it. 8. In order to properly support the various combinations of ANYRANGE and ANYELEMENT in a function definition (which are all important), we need to be able to determine the range type given a subtype. That means that each subtype can only have one associated range, which sounds somewhat limiting, but it can be worked around by using domains. I don't think this is a major limitation. Comments? As we get a more nuanced type system, this is one of the things that will need to get reworked, so I'd say it's better not to put too much effort into things that a refactor of the type system http://wiki.postgresql.org/wiki/Refactor_Type_System would make much better, at least right now. Sounds good. I don't think this is an actual problem, so I'll consider this a non-issue unless someone else has a comment. Also related to representation: * Right now I always align the subtypes within the range according to typalign. I could avoid that by packing the bytes tightly, and then copying them around later. Suggestions? And what should the overall alignment of the range type be? For the first cut, the simplest possible. OK. It's already about as simple as it can get, but might be fairly wasteful. * If it's a fixed-length type, we can save the varlena header byte on the overall range; but we lose the ability to save space when one of the boundaries of the range is missing (NULL or INF), and it would complicate the code a little. Thoughts? Probably not worth complicating the code at this stage. KISS again :) OK. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
* Tom Lane (t...@sss.pgh.pa.us) wrote: What you have just committed puts a syscache lookup into the elog output path. Quite aside from the likely performance hit, this will malfunction badly in any case where we're trying to log from an aborted transaction. Attached is my (admittedly horrible) attempt to add some comments to elog.c regarding this issue. Reviewing this, I'm not sure the performance concern is really an issue (given that the user could choose to enable it or not), but clearly the other issue is a concern. Thanks, Stephen commit 4dcf23e007967892557b7b113a9229cb9fc4575d Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 12:22:16 2011 -0500 Improve comments at the top of elog.c Add in some comments about how certain usually available backend systems may be unavailable or which won't function properly in elog.c due to the current transaction being in a failed state. signature.asc Description: Digital signature
Re: [HACKERS] Add support for logging the current role
On Wed, Jan 12, 2011 at 12:13 PM, Stephen Frost sfr...@snowman.net wrote: What you have just committed puts a syscache lookup into the elog output path. Quite aside from the likely performance hit, this will malfunction badly in any case where we're trying to log from an aborted transaction. I had been looking into storing the current role inside the Proc struct or in some new variable and then pulling it from there (updating it when needed during a SET ROLE, of course), but it seemed a bit of overkill if it wasn't necessary (which wasn't obvious to me). We could also just log the role's OID (%o anyone..?), since that doesn't need a syscache lookup to get at. I'd much rather log the role name if we can tho. Logging the OID seems to be of questionable value. I thought of the update-the-variable-when-it-changes approach too, but besides being a bit expensive if it's changing frequently, it's not necessarily safe to do the syscache lookup there either - see the comments for GetUserIdAndSecContext (which are really for SetUserIdAndSecContext, but they're in an odd place). -- 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] libpq documentation cleanups (repost 3)
2011/1/12 Robert Haas robertmh...@gmail.com On Wed, Jan 12, 2011 at 11:53 AM, Bruce Momjian br...@momjian.us wrote: The attached patch is a collection of libpq documentation cleanups recommended in a list of changes emailed to me by Leslie S Satenstein. Leslie found a number of places our libpq documentation that were unclear or awkward, and this diff generated by me attempts to address them. I have already updated the documentation proofreading wiki: http://wiki.postgresql.org/wiki/Documentation_Proofreading I don't think changing see below to refer below or call to execute is an improvement; even if we did that uniformly throughout our documentation, surely future editors are going to reuse those phrasings. A lot of these other changes look pretty dubious too, although some seem worthwhile. I propose to change backend server to backend or server. Robert, you mentioned that backend server phrase is suggest interchangeability of backend or server but there is no term backend server. -- 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 -- // Dmitriy.
Re: [HACKERS] Add support for logging the current role
On Wed, Jan 12, 2011 at 12:25 PM, Stephen Frost sfr...@snowman.net wrote: Attached is ... I don't see an attachment, other than signature.asc. -- 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] pg_depend explained
2011/1/12 Tom Lane t...@sss.pgh.pa.us: This isn't particularly *useful*, maybe, but it's hardly impossible. And if we analyzed function dependencies in any detail, circular dependencies among functions would be possible (and useful). Thanks Tom for clarifying, this makes me even more motivated into implementing the creation order-algorithm using only sql/plpgsql and pg_depend. If you have any ideas on how to do this, in addition to reading the dependency.c and pg_dump_sort.c source code, they would be highly appreciated. Any tips on articles on graph algorithms which not only take edges (node-node) as indata, but also a edge type as indata (i.e. the deptype in pg_depend) would also be very useful. I have only found algorithms to do sorting on normal directional graphs, where all edges are threated the same. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 12, 2011 at 12:25 PM, Stephen Frost sfr...@snowman.net wrote: Attached is ... I don't see an attachment, other than signature.asc. I suck, sorry about that, here it is.. See, inlining is better! I wouldn't have forgotten it! ;) Stephen *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *** *** 3,8 --- 3,17 * elog.c * error logging and reporting * + * A few comments about situations where error processing is called: + * + * We need to be cautious of both a performance hit when logging, since + * log messages can be generated at a huge rate if every command is being + * logged and we also need to watch out for what can happen when we are + * trying to log from an aborted transaction. Specifically, attempting to + * do SysCache lookups and possibly use other usually available backend + * systems will fail badly when logging from an aborted transaction. + * * Some notes about recursion and errors during error processing: * * We need to be robust about recursive-error scenarios --- for example, signature.asc Description: Digital signature
Re: [HACKERS] libpq documentation cleanups (repost 3)
Robert Haas wrote: On Wed, Jan 12, 2011 at 11:53 AM, Bruce Momjian br...@momjian.us wrote: The attached patch is a collection of libpq documentation cleanups recommended in a list of changes emailed to me by Leslie S Satenstein. Leslie found a number of places our libpq documentation that were unclear or awkward, and this diff generated by me attempts to address them. I have already updated the documentation proofreading wiki: ? ? ? ?http://wiki.postgresql.org/wiki/Documentation_Proofreading I don't think changing see below to refer below or call to execute is an improvement; even if we did that uniformly throughout our documentation, surely future editors are going to reuse those phrasings. A lot of these other changes look pretty dubious too, although some seem worthwhile. OK, that last part seems kind of vague. ;-) Can you hack up the diff to have just the changes you think are worthwhile? You can just remove the parts of the diff you don'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] Add support for logging the current role
* Robert Haas (robertmh...@gmail.com) wrote: Logging the OID seems to be of questionable value. I certainly disagree about this, not being able to figure out what's causing a 'permissions denied' error because you don't know which role the log is coming from is *very* annoying. Having to go look up the role from the OID in the log is also annoying, but less so, imv. :) I thought of the update-the-variable-when-it-changes approach too, but besides being a bit expensive if it's changing frequently, it's not necessarily safe to do the syscache lookup there either - see the comments for GetUserIdAndSecContext (which are really for SetUserIdAndSecContext, but they're in an odd place). Alright, here's a patch which adds the ability to log the current role's OID and which calls GetUserIdAndSecContext() directly and handles the possibility that CurrentUserId isn't valid. Perhaps we should just grab CurrentUserId directly rather than going through GetUserIdAndSecContext()? I could certainly do that instead. Also includes those additional comments in elog.c. Thanks, Stephen commit d9a7acd5ea1f5214b44875b6d257c5c59590167c Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 12:53:50 2011 -0500 Use GetUserIdAndSecContext to get role OID in elog We can't be sure that GetUserId() will be called when current_user is a valid Oid, per the comments in GetUserIdAndSecContext, when called from elog.c, so instead call GetUserIdAndSecContext directly and handle the possible invalid Oid ourselves. commit 605497b062298ea195d8999f8cefca10968ae22f Author: Stephen Frost sfr...@snowman.net Date: Wed Jan 12 12:29:44 2011 -0500 Change to logging role's OID instead of name Remove the SysCache lookup from elog.c/log_line_prefix by logging the role's OID instead, this addresses a concern where a SysCache lookup could malfunction badly due to logging from a failed transaction. Note that using SysCache from the elog routines could also be a performance hit, though this would only be the case if a user chose to enable that logging. *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** *** 3504,3510 local0.*/var/log/postgresql /row row entryliteral%u/literal/entry ! entryUser name/entry entryyes/entry /row row --- 3504,3517 /row row entryliteral%u/literal/entry ! entryUser name which was used to authenticate to productnamePostgreSQL/productname with/entry ! entryyes/entry ! /row ! row ! entryliteral%o/literal/entry ! entryCurrent role OID, set via commandSET ROLE/, the ! current role is relevant for permission checking, the mapping ! from OID to role can be found in the pg_authid catalog/entry entryyes/entry /row row *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *** *** 3,8 --- 3,17 * elog.c * error logging and reporting * + * A few comments about situations where error processing is called: + * + * We need to be cautious of both a performance hit when logging, since + * log messages can be generated at a huge rate if every command is being + * logged and we also need to watch out for what can happen when we are + * trying to log from an aborted transaction. Specifically, attempting to + * do SysCache lookups and possibly use other usually available backend + * systems will fail badly when logging from an aborted transaction. + * * Some notes about recursion and errors during error processing: * * We need to be robust about recursive-error scenarios --- for example, *** *** 1826,1831 log_line_prefix(StringInfo buf, ErrorData *edata) --- 1835,1852 appendStringInfoString(buf, username); } break; + case 'o': + { + Oid curr_role; + int curr_sec_context; + + GetUserIdAndSecContext(curr_role,curr_sec_context); + if (OidIsValid(curr_role)) + appendStringInfo(buf, %u, curr_role); + else + appendStringInfoString(buf, _([unknown])); + } + break; case 'd': if (MyProcPort) { signature.asc Description: Digital signature
Re: [HACKERS] pg_depend explained
Joel Jacobson j...@gluefinance.com writes: 2011/1/12 Tom Lane t...@sss.pgh.pa.us: This isn't particularly *useful*, maybe, but it's hardly impossible. And if we analyzed function dependencies in any detail, circular dependencies among functions would be possible (and useful). Thanks Tom for clarifying, this makes me even more motivated into implementing the creation order-algorithm using only sql/plpgsql and pg_depend. If you have any ideas on how to do this, in addition to reading the dependency.c and pg_dump_sort.c source code, they would be highly appreciated. I've sometimes found it useful to think of internal dependencies as acting like normal dependencies pointing in the other direction. I'm not sure that would do much to solve your problem, but it might be worth trying. 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] libpq documentation cleanups (repost 3)
Bruce Momjian wrote: Robert Haas wrote: On Wed, Jan 12, 2011 at 11:53 AM, Bruce Momjian br...@momjian.us wrote: The attached patch is a collection of libpq documentation cleanups recommended in a list of changes emailed to me by Leslie S Satenstein. Leslie found a number of places our libpq documentation that were unclear or awkward, and this diff generated by me attempts to address them. I have already updated the documentation proofreading wiki: ? ? ? ?http://wiki.postgresql.org/wiki/Documentation_Proofreading I don't think changing see below to refer below or call to execute is an improvement; even if we did that uniformly throughout our documentation, surely future editors are going to reuse those phrasings. A lot of these other changes look pretty dubious too, although some seem worthwhile. OK, that last part seems kind of vague. ;-) Can you hack up the diff to have just the changes you think are worthwhile? You can just remove the parts of the diff you don't like. Robert, here is a unified diff, which I think it easier to review for single-line documention changes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + /pgpatches/libpq.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compatibility GUC for serializable
Kevin, I think you overestimate what we can meaninfully put in a tiny radio-button survey. I'm only trying to get a straw poll idea of whether we have lots of people using SERIALIZABLE mode *at all*, or (as I suspect) almost none. If we get 5% or respondees saying we use it in production then I think we can assume that backwards compatibility isn't worth discussing. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 5:14 AM, Alexey Klyukin wrote: You mean packing both a string representation and a reference to a single SV * value? Dunno, I'm not a guts guy. I haven't considered that (lack of extensive perlgus-foo) although I think that's an interesting idea. One drawback would be that it would require both conversion to a string format and to a perl reference, performing unnecessary actions during every time arrays are passed to a pl/perl function. If there is a strong dislike of the proposed 'compatibility' GUC option - I think I can change the existing patch to incorporate array string representation into the reference-holding SV quite easily. Andrew's objections have merit. So perhaps just add this patch to the commit fest as is? Best, David -- 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] WIP: Range Types
On Jan 12, 2011, at 8:48 AM, Robert Haas wrote: I guess that begs the question of why we need to allow users to call type output functions directly. I've used them quite a lot in the past; less so on 8.4+ where casting everything to text became a lot easier. Best, David -- 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] libpq documentation cleanups (repost 3)
On Wed, Jan 12, 2011 at 1:12 PM, Bruce Momjian br...@momjian.us wrote: OK, that last part seems kind of vague. ;-) Can you hack up the diff to have just the changes you think are worthwhile? You can just remove the parts of the diff you don't like. Robert, here is a unified diff, which I think it easier to review for single-line documention changes. Here are the parts that seem like improvements to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company libpq-docs.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] WIP: Range Types
Excerpts from Robert Haas's message of mié ene 12 13:48:27 -0300 2011: I guess that begs the question of why we need to allow users to call type output functions directly. It used to be the case that that was the only way to run certain casts. For example, see the pre-8.2 version of this: http://wiki.postgresql.org/wiki/Pg_depend_display I haven't needed to use that in a long time, but I am not sure if the need has completely disappeared. -- Á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] Add support for logging the current role
On Wed, Jan 12, 2011 at 12:59 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: Logging the OID seems to be of questionable value. I certainly disagree about this, not being able to figure out what's causing a 'permissions denied' error because you don't know which role the log is coming from is *very* annoying. Interesting. I wonder if we shouldn't try to fix this by including the relevant role name in the error message. Or is that just going to be too messy to live? -- 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] pg_depend explained
On Wed, Jan 12, 2011 at 2:06 PM, Joel Jacobson j...@gluefinance.com wrote: Tom, you are a genious! No, seriously, I mean it, this is awesome, it worked! YES! You totally saved my day! Thank you! Finally! I'm so happy! :-) :-) :-) stage whisper Hey, guys, I think it worked...! -- 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] SSI patch version 8
Anssi Kääriäinenanssi.kaariai...@thl.fi wrote: So, count(*) queries are more than twice as slow compared to the old serializable transaction isolation level. I've looked at this enough to know that I can do something about that, but wanted to point out that this is a good example of why you should specify READ ONLY when possible. My numbers: begin transaction isolation level repeatable read; Time: 394.946 ms Time: 248.675 ms Time: 242.559 ms begin transaction isolation level serializable; Time: 494.676 ms Time: 494.036 ms Time: 491.712 ms begin transaction isolation level serializable, read only; Time: 234.075 ms Time: 234.050 ms Time: 234.057 ms begin transaction isolation level serializable, read only, deferrable; Time: 233.494 ms Time: 234.099 ms Time: 235.290 ms The slower times for REPEATABLE READ gave me pause, so I ran those again: begin transaction isolation level repeatable read; Time: 233.946 ms Time: 236.200 ms Time: 236.414 ms I guess the database just hadn't warmed up enough for the first few tests -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RowMarks versus child tables with varying column sets
I've been looking into Gordon Shannon's crash report here: http://archives.postgresql.org/pgsql-general/2010-12/msg01030.php After some groveling around in the core dump (thanks to Gordon for making that available), I figured out the cause of the problem. The missing piece of information that prevented anyone from reproducing the crash was that Gordon's master table, and many of its inheritance children, had a dropped column --- but the most-recently-added children did not. This meant that the child plans of the Update node didn't all have the same targetlists, which is expected. But then the resjunk columns added to track row identities for sharelocking didn't occur at the same column numbers in every subplan. The PlanRowMark representation built by the planner fails to account for this possibility, since it sends predetermined column numbers (ctidAttNo, toidAttNo, wholeAttNo) to the executor in the ModifyTable's rowMarks list. This would fail not only in the case of dropped columns, but any scenario where the child tables don't all have identical column sets. The problem only manifests when EvalPlanQual is run, though, and that requires concurrent updates to the same row in READ COMMITTED mode. (Which makes it impractical to test in the current regression test framework :-() I can see two basic ways of attacking this. Clearly we need to track the resjunk column numbers separately for each subplan. We could convert ModifyTable's rowMarks list into a list-of-lists of PlanRowMark, one per child plan. I'm thinking though that determining those column numbers at plan time might have been an overly cute idea. It might be better to remove the column numbers from PlanRowMark, restoring it to a form that's not dependent on the particular subplan. Then ModifyTable would need to build a list of ExecRowMark nodes for each child plan from the PlanRowMark representation, doing the lookup for resjunk column names during plan startup. This would take a few more cycles than the current way but it seems more robust. (Another possible objection is that we'd have to put back into the executor knowledge of the resjunk column naming conventions, something I'd been trying to decouple by adding these attno fields. But if it doesn't work, it doesn't work...) Another point that has to be considered is that currently the executor maintains a global list of ExecRowMarks (estate-es_rowMarks). In SELECT FOR UPDATE/SHARE that list is needed so that execCurrent.c (WHERE CURRENT OF) can pull out the current locked TID of a SELECT FOR UPDATE cursor. However, we don't support any such thing for ModifyTable. The other use of the global list is to keep track of which relations have been opened with a lock type higher than AccessShareLock. On the whole it seems that it might be best to split the data structure into two types of structs: * a global list carrying the same fields as current ExecRowMark except for the attno fields. * private lists in LockRows and ModifyTable nodes carrying attribute numbers plus pointers to the corresponding global-list entry. In ModifyTable we'd need such a list per child plan. Comments? 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] arrays as pl/perl input arguments [PATCH]
On Wed, Jan 12, 2011 at 06:34, Alexey Klyukin al...@commandprompt.com wrote: On Jan 12, 2011, at 4:06 AM, Robert Haas wrote: By the same token, I'm not convinced it's a good idea for this behavior to be off by default. Surely many people will altogether fail to notice that it's an option? If we're going to have a backward-compatibility GUC at all, ISTM that you ought to get the good stuff unless you ask for the old way. I think the number of people failing to notice the changes would be the same whenever we set the new or the old behavior by default. I decided to default to the the old behavior since it won't break the existing code as opposed to just hiding the good stuff, although it would slower the adoption of the new behavior. Personally, I think the point of a compatibility GUC is that at some point in the distant future we can get rid of it. If we default to the old behavior thats going to be harder to do. +1 for defaulting to the new behavior. [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] -- 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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 11:22 AM, Alex Hunsaker wrote: Personally, I think the point of a compatibility GUC is that at some point in the distant future we can get rid of it. If we default to the old behavior thats going to be harder to do. +1 for defaulting to the new behavior. +1 [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] Yeah, but the removal of Safe.pm actually *improved* compatibility. This patch, without the GUC, would break it. Best, David -- 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_depend explained
Excerpts from Joel Jacobson's message of mié ene 12 16:06:24 -0300 2011: The query below can both produce a DOT-format graph and a tsort of the creatable order of objects: WITH NewObjectOids AS ( SELECT * FROM pg_depend WHERE deptype 'p' EXCEPT SELECT * FROM pg_depend_before ), I think this code should live in the Wiki somewhere: http://wiki.postgresql.org/wiki/Snippets -- Á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] arrays as pl/perl input arguments [PATCH]
On Wed, Jan 12, 2011 at 12:22:55PM -0700, Alex Hunsaker wrote: On Wed, Jan 12, 2011 at 06:34, Alexey Klyukin al...@commandprompt.com wrote: On Jan 12, 2011, at 4:06 AM, Robert Haas wrote: By the same token, I'm not convinced it's a good idea for this behavior to be off by default. Surely many people will altogether fail to notice that it's an option? If we're going to have a backward-compatibility GUC at all, ISTM that you ought to get the good stuff unless you ask for the old way. I think the number of people failing to notice the changes would be the same whenever we set the new or the old behavior by default. I decided to default to the the old behavior since it won't break the existing code as opposed to just hiding the good stuff, although it would slower the adoption of the new behavior. Personally, I think the point of a compatibility GUC is that at some point in the distant future we can get rid of it. If we default to the old behavior thats going to be harder to do. +1 for defaulting to the new behavior. [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] +1 for changing the behavior to something sane with loud, specific warnings in the release notes about what will break and how. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Something fishy about the current Makefiles
Excerpts from Tom Lane's message of jue ene 06 13:57:11 -0300 2011: Whilst fooling around with GIN, I have repeatedly observed that doing make in src/backend/access/gin, followed by make install-bin in src/backend, fails to rebuild the postgres executable --- it just installs the existing one. A second execution of make install-bin does notice that postgres is out of date and rebuilds it. This procedure for rebuilding after changing one or two .c files has always worked for me before. I can't avoid the suspicion that the recent changes to make things more parallel-friendly broke something. FWIW this explains why I was having such a hard time testing the FOR KEY LOCK patch. Please commit whatever fix you have. -- Á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] WIP: Range Types
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié ene 12 13:48:27 -0300 2011: I guess that begs the question of why we need to allow users to call type output functions directly. It used to be the case that that was the only way to run certain casts. For example, see the pre-8.2 version of this: http://wiki.postgresql.org/wiki/Pg_depend_display I haven't needed to use that in a long time, but I am not sure if the need has completely disappeared. The general point is that any out-of-band data transmitted to an output function has to be trustworthy, and it has to be available at any place that is going to call the output function. The latter point tends to put a crimp in any ideas of this sort anyway: if you can derive the info you want at any arbitrary place in the system, why not derive it inside the output function 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] arrays as pl/perl input arguments [PATCH]
Excerpts from Alex Hunsaker's message of mié ene 12 16:22:55 -0300 2011: [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] I think the main question here is: how loudly is existing code going to break? If the breakage is silent, it's going to be very problematic. If functions fail to run at all, then we can live without the compatibility option. -- Á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] pg_depend explained
On Wed, Jan 12, 2011 at 08:06:24PM +0100, Joel Jacobson wrote: 2011/1/12 Tom Lane t...@sss.pgh.pa.us: I've sometimes found it useful to think of internal dependencies as acting like normal dependencies pointing in the other direction. I'm not sure that would do much to solve your problem, but it might be worth trying. Tom, you are a genious! No, seriously, I mean it, this is awesome, it worked! YES! You totally saved my day! Thank you! Finally! I'm so happy! :-) :-) :-) This was the little piece of code: CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN --- Swap edges ELSE -- Do not swap edges END Look at the attached svg graph how beautiful the automatically generated graph look like now! :-) The tsort of the objects now sort all the normal objects in a creatable order! Here is the result of the tsort (only including the normal objects (the one I care about (I don't have to create the internal/auto objects, nor drop them))): The query below can both produce a DOT-format graph and a tsort of the creatable order of objects: WITH NewObjectOids AS ( SELECT * FROM pg_depend WHERE deptype 'p' EXCEPT SELECT * FROM pg_depend_before To what does pg_depend_before refer? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 11:36 AM, Alvaro Herrera wrote: [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] I think the main question here is: how loudly is existing code going to break? If the breakage is silent, it's going to be very problematic. If functions fail to run at all, then we can live without the compatibility option. I suspect it'd be quiet, unfortunately, since there are a bazillion ad hoc implementations of a Perl SQL array parser, and many of them, I suspect, don't complain if the string doesn't look like an SQL array. They would just parse a string like ARRAY(0x118ee2a0) and return an empty array, or a NULL. Best, David -- 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] WIP: Range Types
On Wed, Jan 12, 2011 at 2:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié ene 12 13:48:27 -0300 2011: I guess that begs the question of why we need to allow users to call type output functions directly. It used to be the case that that was the only way to run certain casts. For example, see the pre-8.2 version of this: http://wiki.postgresql.org/wiki/Pg_depend_display I haven't needed to use that in a long time, but I am not sure if the need has completely disappeared. The general point is that any out-of-band data transmitted to an output function has to be trustworthy, and it has to be available at any place that is going to call the output function. The latter point tends to put a crimp in any ideas of this sort anyway: if you can derive the info you want at any arbitrary place in the system, why not derive it inside the output function to start with? Under what circumstances would it be necessary to call a type output function without knowing the data type? I mean, you had to decide which type output function you were going to call in the first place, so... -- 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] arrays as pl/perl input arguments [PATCH]
Excerpts from David E. Wheeler's message of mié ene 12 16:39:56 -0300 2011: On Jan 12, 2011, at 11:36 AM, Alvaro Herrera wrote: [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] I think the main question here is: how loudly is existing code going to break? If the breakage is silent, it's going to be very problematic. If functions fail to run at all, then we can live without the compatibility option. I suspect it'd be quiet, unfortunately, since there are a bazillion ad hoc implementations of a Perl SQL array parser, and many of them, I suspect, don't complain if the string doesn't look like an SQL array. They would just parse a string like ARRAY(0x118ee2a0) and return an empty array, or a NULL. I kinda doubt that a function failing in that way would pass any testing. -- Á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] WIP: Range Types
Robert Haas robertmh...@gmail.com writes: On Wed, Jan 12, 2011 at 2:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: The general point is that any out-of-band data transmitted to an output function has to be trustworthy, and it has to be available at any place that is going to call the output function. The latter point tends to put a crimp in any ideas of this sort anyway: if you can derive the info you want at any arbitrary place in the system, why not derive it inside the output function to start with? Under what circumstances would it be necessary to call a type output function without knowing the data type? I mean, you had to decide which type output function you were going to call in the first place, so... If the out-of-band info is going to be limited to the type OID, you might as well put it in the object, ie, follow the same solution we're already using for arrays. Jeff's problems are already plenty large enough without insisting that he invent a new, precedent-free solution for this problem (*and* break every single output-function call site in both core and third-party modules in order 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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 11:51 AM, Alvaro Herrera wrote: I suspect it'd be quiet, unfortunately, since there are a bazillion ad hoc implementations of a Perl SQL array parser, and many of them, I suspect, don't complain if the string doesn't look like an SQL array. They would just parse a string like ARRAY(0x118ee2a0) and return an empty array, or a NULL. I kinda doubt that a function failing in that way would pass any testing. What is this “testing” thing of which you speak? David -- 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] WIP: Range Types
On Wed, Jan 12, 2011 at 2:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Jan 12, 2011 at 2:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: The general point is that any out-of-band data transmitted to an output function has to be trustworthy, and it has to be available at any place that is going to call the output function. The latter point tends to put a crimp in any ideas of this sort anyway: if you can derive the info you want at any arbitrary place in the system, why not derive it inside the output function to start with? Under what circumstances would it be necessary to call a type output function without knowing the data type? I mean, you had to decide which type output function you were going to call in the first place, so... If the out-of-band info is going to be limited to the type OID, you might as well put it in the object, ie, follow the same solution we're already using for arrays. Jeff's problems are already plenty large enough without insisting that he invent a new, precedent-free solution for this problem (*and* break every single output-function call site in both core and third-party modules in order to do so...) In terms of solving the immediate problem, you're probably correct. But this isn't the first time this issue has come up, and it probably won't be the last. It's pretty lame to waste 4+ bytes on disk for every value of a given type due to a parameter-passing convention. And I suppose it's worth pointing out that if Jeff does adopt that solution, we'll be stuck with it for approximately forever due to pg_upgrade. -- 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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 8:52 PM, David E. Wheeler wrote: On Jan 12, 2011, at 5:14 AM, Alexey Klyukin wrote: You mean packing both a string representation and a reference to a single SV * value? Dunno, I'm not a guts guy. Well, neither me (I haven't used much of the guts api there). I haven't considered that (lack of extensive perlgus-foo) although I think that's an interesting idea. One drawback would be that it would require both conversion to a string format and to a perl reference, performing unnecessary actions during every time arrays are passed to a pl/perl function. If there is a strong dislike of the proposed 'compatibility' GUC option - I think I can change the existing patch to incorporate array string representation into the reference-holding SV quite easily. Andrew's objections have merit. So perhaps just add this patch to the commit fest as is? Already done :) /A -- Alexey Klyukin The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
(sorry for top posting, iPhone + drunk) pg_depend_before is a select * from pg_depend before creating the test db model Sent from my iPhone On 12 jan 2011, at 20:36, David Fetter da...@fetter.org wrote: On Wed, Jan 12, 2011 at 08:06:24PM +0100, Joel Jacobson wrote: 2011/1/12 Tom Lane t...@sss.pgh.pa.us: I've sometimes found it useful to think of internal dependencies as acting like normal dependencies pointing in the other direction. I'm not sure that would do much to solve your problem, but it might be worth trying. Tom, you are a genious! No, seriously, I mean it, this is awesome, it worked! YES! You totally saved my day! Thank you! Finally! I'm so happy! :-) :-) :-) This was the little piece of code: CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN --- Swap edges ELSE -- Do not swap edges END Look at the attached svg graph how beautiful the automatically generated graph look like now! :-) The tsort of the objects now sort all the normal objects in a creatable order! Here is the result of the tsort (only including the normal objects (the one I care about (I don't have to create the internal/auto objects, nor drop them))): The query below can both produce a DOT-format graph and a tsort of the creatable order of objects: WITH NewObjectOids AS ( SELECT * FROM pg_depend WHERE deptype 'p' EXCEPT SELECT * FROM pg_depend_before To what does pg_depend_before refer? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 9:36 PM, Alvaro Herrera wrote: Excerpts from Alex Hunsaker's message of mié ene 12 16:22:55 -0300 2011: [ Id actually vote for _not_ having a compatibility option at all, we change more major things than this IMHO every major release. (And even then some major things in minor releases, for example the removal of Safe.pm) ] I think the main question here is: how loudly is existing code going to break? If the breakage is silent, it's going to be very problematic. If functions fail to run at all, then we can live without the compatibility option. Not really loud. Perl won't even complain when you try to interpret a reference as a string. Since almost everyone votes for making the new behavior a default option I'm inclined to do that change, although I'm against throwing out the compatibility option. There are many other reasons except for PL/Perl for people to upgrade to 9.1, let's not force them to rewrite their Perl code if they were not planning to do that. /A -- Alexey Klyukin The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] arrays as pl/perl input arguments [PATCH]
Excerpts from David E. Wheeler's message of mié ene 12 16:55:17 -0300 2011: On Jan 12, 2011, at 11:51 AM, Alvaro Herrera wrote: I suspect it'd be quiet, unfortunately, since there are a bazillion ad hoc implementations of a Perl SQL array parser, and many of them, I suspect, don't complain if the string doesn't look like an SQL array. They would just parse a string like ARRAY(0x118ee2a0) and return an empty array, or a NULL. I kinda doubt that a function failing in that way would pass any testing. What is this “testing” thing of which you speak? Ha ha ha :-( I wonder if there's a way to overload the string representation of the array so that it throws an error. -- Á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] Add function dependencies
Peter Eisentraut pete...@gmx.net writes: What's a not-to-follow dependency? In case of extensions the code follows dependencies to walk on all objects. We already have the problem that an extension depending on another is not relocatable, because 'ALTER EXTENSION SET SCHEMA' would walk to objects of another extension (the one it depends on). We said inter-extension dependencies could wait until later, so what you do here is to declare your extension has not relocatable. Now, if there are some dependencies between objects that are not of the same extension, we have the exact same problem. That's what I called a not-to-follow dependency for lack of a better term. 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] libpq documentation cleanups (repost 3)
On ons, 2011-01-12 at 12:04 -0500, Robert Haas wrote: On Wed, Jan 12, 2011 at 11:53 AM, Bruce Momjian br...@momjian.us wrote: The attached patch is a collection of libpq documentation cleanups recommended in a list of changes emailed to me by Leslie S Satenstein. Leslie found a number of places our libpq documentation that were unclear or awkward, and this diff generated by me attempts to address them. I have already updated the documentation proofreading wiki: http://wiki.postgresql.org/wiki/Documentation_Proofreading I don't think changing see below to refer below or call to execute is an improvement; even if we did that uniformly throughout our documentation, surely future editors are going to reuse those phrasings. Agreed. A lot of these other changes look pretty dubious too, although some seem worthwhile. Agreed. :-/ -- 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] arrays as pl/perl input arguments [PATCH]
Alexey Klyukin al...@commandprompt.com writes: Since almost everyone votes for making the new behavior a default option I'm inclined to do that change, although I'm against throwing out the compatibility option. There are many other reasons except for PL/Perl for people to upgrade to 9.1, let's not force them to rewrite their Perl code if they were not planning to do that. IMO a GUC for this completely sucks, because if you do need to convert your Perl functions, the only way to do it is to have a flag day wherein they all change at once. And what about people writing Perl functions that they'd like to give to other people? If you have to have a flag, the only useful sort of flag is one that can be attached to individual functions. Compare what we did for plpgsql.variable_conflict in 9.0. I don't know how practical that will be in plperl, though. I thought the idea of overloading the string representation to look like the old style was a cute solution. If we don't have anyone at hand who knows how to do that, let's find someone who does. Not break our users' code because we're too lazy to find out how to do it properly. 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] arrays as pl/perl input arguments [PATCH]
On 01/12/2011 04:22 PM, Tom Lane wrote: Alexey Klyukinal...@commandprompt.com writes: Since almost everyone votes for making the new behavior a default option I'm inclined to do that change, although I'm against throwing out the compatibility option. There are many other reasons except for PL/Perl for people to upgrade to 9.1, let's not force them to rewrite their Perl code if they were not planning to do that. IMO a GUC for this completely sucks, because if you do need to convert your Perl functions, the only way to do it is to have a flag day wherein they all change at once. And what about people writing Perl functions that they'd like to give to other people? If you have to have a flag, the only useful sort of flag is one that can be attached to individual functions. Compare what we did for plpgsql.variable_conflict in 9.0. I don't know how practical that will be in plperl, though. I don't see why it should be terribly difficult. We have the source code and we have a couple of powerful regex engines. Determining it it has a string in some position like # pragma: plperl.arrays_as_strings doesn't seem onerous. It's just a SMOC. It's not too hard to imagine other things that might be useful for. I thought the idea of overloading the string representation to look like the old style was a cute solution. If we don't have anyone at hand who knows how to do that, let's find someone who does. Not break our users' code because we're too lazy to find out how to do it properly. What I was casting a bit of doubt on upthread was whether or not this would work without possibly breaking some code, in possibly silent or obscure ways. If I'm wrong about that, then by all means let's use some perl Magic (that's a technical term) to achieve this. IIRC Alex recently posted some code that might be instructive about this. 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] Bug in pg_describe_object, patch v2
Here is a very simple change of the patch to make the output look more like the syntax of ALTER OPERATOR FAMILY to improve consistency. Before patch: function 1 bttextcmp(text,text) of operator family array_ops for access method gin With the first version: function 1 bttextcmp(text,text) of operator family array_ops for access method gin for (text[],text[]) With this version: function 1 (text[],text[]) bttextcmp(text,text) of operator family array_ops for access method gin Andreas diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index ec8eb74..6bfb3c9 100644 *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *** getObjectDescription(const ObjectAddress *** 2389,2396 * textual form of the function with arguments, and the second * %s is the description of the operator family. */ ! appendStringInfo(buffer, _(function %d %s of %s), amprocForm-amprocnum, format_procedure(amprocForm-amproc), opfam.data); pfree(opfam.data); --- 2389,2398 * textual form of the function with arguments, and the second * %s is the description of the operator family. */ ! appendStringInfo(buffer, _(function %d (%s,%s) %s of %s), amprocForm-amprocnum, + format_type_be(amprocForm-amproclefttype), + format_type_be(amprocForm-amprocrighttype), format_procedure(amprocForm-amproc), opfam.data); pfree(opfam.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] arrays as pl/perl input arguments [PATCH]
On Wed, Jan 12, 2011 at 4:45 PM, Andrew Dunstan and...@dunslane.net wrote: I thought the idea of overloading the string representation to look like the old style was a cute solution. If we don't have anyone at hand who knows how to do that, let's find someone who does. Not break our users' code because we're too lazy to find out how to do it properly. What I was casting a bit of doubt on upthread was whether or not this would work without possibly breaking some code, in possibly silent or obscure ways. If I'm wrong about that, then by all means let's use some perl Magic (that's a technical term) to achieve this. IIRC Alex recently posted some code that might be instructive about this. I agree with your gut feeling. I suspect the Perl magic solution will make no one very happy. -- 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] arrays as pl/perl input arguments [PATCH]
On Jan 12, 2011, at 3:29 PM, Robert Haas wrote: What I was casting a bit of doubt on upthread was whether or not this would work without possibly breaking some code, in possibly silent or obscure ways. If I'm wrong about that, then by all means let's use some perl Magic (that's a technical term) to achieve this. IIRC Alex recently posted some code that might be instructive about this. I agree with your gut feeling. I suspect the Perl magic solution will make no one very happy. Could do both, I suppose… David -- 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] SSI patch version 8
Anssi Kääriäinenanssi.kaariai...@thl.fi wrote: So, count(*) queries are more than twice as slow compared to the old serializable transaction isolation level. I got this down from more than twice the run time to running 33% longer through remembering the last relation for which a search for a predicate lock held by the current transaction found a match at the coarsest (relation) level. It's a bit of a hack and 33% isn't very impressive, even for a worst case (and this is one type of worst case) -- especially given how often people use SELECT count(*) FROM table_x as a performance test. :-( I can see a way to improve on this if there's a low-cost way to determine from within the heapam.c:heapgettup_pagemode function whether it's returning tuples for a table scan. It seems likely that this is somehow contained in the HeapScanDesc structure, but I'm not seeing it. Can anyone point me in the right direction, or tell me that this avenue is a dead end? Thanks, -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] Fixing GIN for empty/null/full-scan cases
Tom, On Jan 8, 2011, at 9:41 PM, Tom Lane wrote: David E. Wheeler da...@kineticode.com writes: On Jan 7, 2011, at 4:19 PM, Tom Lane wrote: Well, actually, I just committed it. If you want to test, feel free. Note that right now only the anyarray @ @ operators are genuinely fixed ... I plan to hack on tsearch and contrib pretty soon though. Hrm, the queries I wrote for this sort of thing use intarray: WHERE blah @@ '(12|14)'::query_int That's not done yet though, right? intarray is done now, feel free to test ... Thanks, working on it now. I'm restoring a dump from 8.4, but got these erors: pg_restore: [archiver (db)] Error from TOC entry 3227; 2616 46485 OPERATOR CLASS gin__int_ops postgres pg_restore: [archiver (db)] could not execute query: ERROR: function ginarrayextract(anyarray, internal) does not exist Command was: CREATE OPERATOR CLASS gin__int_ops FOR TYPE integer[] USING gin AS STORAGE integer , OPERATOR 3 (integer[],int... pg_restore: [archiver (db)] could not execute query: ERROR: operator class contrib.gin__int_ops does not exist for access method gin Command was: ALTER OPERATOR CLASS contrib.gin__int_ops USING gin OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 3254; 3600 16245434 TEXT SEARCH DICTIONARY en_mls_20101103 postgres Did a signature change or something? Is there something that needs a compatibility interface of some kind? 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
Re: [HACKERS] Fixing GIN for empty/null/full-scan cases
David E. Wheeler da...@kineticode.com writes: Thanks, working on it now. I'm restoring a dump from 8.4, but got these erors: pg_restore: [archiver (db)] Error from TOC entry 3227; 2616 46485 OPERATOR CLASS gin__int_ops postgres pg_restore: [archiver (db)] could not execute query: ERROR: function ginarrayextract(anyarray, internal) does not exist Did a signature change or something? Yeah. I think if you just load up the current contrib/intarray first, you'll be fine (ignore all the object-already-exists errors). Is there something that needs a compatibility interface of some kind? No, what we need is a decent extension package manager ;-) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add function dependencies
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Peter Eisentraut pete...@gmx.net writes: What's a not-to-follow dependency? In case of extensions the code follows dependencies to walk on all objects. That seems pretty silly/broken. You should only be touching *direct* dependencies of the extension, IMO. If there's something that's missed by that algorithm, the way to fix it is to add more direct dependencies at extension creation time; not to start a tree walk that is pretty nearly guaranteed to land on things that don't belong to the extension. 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] Fixing GIN for empty/null/full-scan cases
On Jan 12, 2011, at 4:35 PM, Tom Lane wrote: Did a signature change or something? Yeah. I think if you just load up the current contrib/intarray first, you'll be fine (ignore all the object-already-exists errors). Oh, from 9.1devel? yeah, okay. Will do that tomorrow (finishing the current load to make sure that there are no other errors I can't ignore, but won't see among all the intarray stuff tomorrow). Is there something that needs a compatibility interface of some kind? No, what we need is a decent extension package manager ;-) Yeah. Maybe you can do that this weekend? Or, I dunno, while you “sleep” tonight? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug in pg_describe_object, patch v2
Andreas Karlsson andr...@proxel.se writes: Here is a very simple change of the patch to make the output look more like the syntax of ALTER OPERATOR FAMILY to improve consistency. IMO, what this patch needs is to not output the types unless they are actually different from the default (which can be inferred from the AM type and the function arguments). That would fix my concern about it emitting information that is 99.44% useless. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Jan 12, 2011 at 12:59 PM, Stephen Frost sfr...@snowman.net wrote: I certainly disagree about this, not being able to figure out what's causing a 'permissions denied' error because you don't know which role the log is coming from is *very* annoying. Interesting. I wonder if we shouldn't try to fix this by including the relevant role name in the error message. Or is that just going to be too messy to live? It might be possible to do and answer that specific question- but what about the obvious next question: which role was this command run with? iow, if I log dml, how do I know what the role was when the dml statement was run? ie- why was this command allowed? Let's ask another question- why do we provide a %u option in log_line_prefix instead of just logging it as part of each statement? When you have roles that aren't 'inherit' and have a lot of 'set role's happening, you end up asking the same questions about role that you would about user. As a side-note, CurrentUserId isn't actually exported (I'm not suprised, tbh, but I've actually checked now), so you have to go through GetUserIdAndSecContext(). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Fixing GIN for empty/null/full-scan cases
David E. Wheeler da...@kineticode.com writes: On Jan 12, 2011, at 4:35 PM, Tom Lane wrote: No, what we need is a decent extension package manager ;-) Yeah. Maybe you can do that this weekend? Or, I dunno, while you sleep tonight? Supposedly it's in the queue for the upcoming CF :-) 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