Re: [HACKERS] string_to_array with an empty input string
On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
peter.geoghega...@gmail.com wrote:
On 10 August 2010 19:48, David E. Wheeler da...@kineticode.com wrote:
On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:
I, personally, would expect an empty array output given an empty
input, and a null output for a null input.
+1
Agreed. After all, the result isn't indeterminate - it's an empty
array. Some people might think that it's useful for the result to be
NULL, but they'd probably also think that it's useful for an empty
string to be NULL.
For what it's worth there are two reasonable return values for
string_to_array(''). It could be [] or it could be ['']. There are
applications where the former makes the most sense and there are
applications where the latter makes the most sense.
Ideally you really want string_to_array(array_to_string(x, ':'),':')
to return x. There's no safe return value to pick for the cases where
x=[''] and x=[] that will make this work.
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] string_to_array with an empty input string
There's already been one rather-long thread on this topic. http://thread.gmane.org/gmane.comp.db.postgresql.general/121450 In there I argue for the empty array interpretation and Tom goes back and forth a few times. I'm not sure where that thread ended though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cost of AtEOXact_Buffers in --enable-cassert
On Wed, Aug 11, 2010 at 12:51:36AM -0400, Greg Smith wrote: Andres Freund wrote: The most prohibitively expensive part is the AtEOXact_Buffers check of running through all buffers and checking their pin count. And it makes $app's regression tests take thrice their time... Have you tried reducing shared_buffers from the default the system found by probing to make this overhead smaller? Yes. Its getting slower just as you make them bigger. Which is not surprising... Using a smaller value than the default is painfull again as well though... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] assertions and constraint triggers
On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote: Thinking about SQL assertions (check constraints that are independent of one particular table), do you think it would be reasonable to implement those on top of constraint triggers? On creation you'd hook up a trigger to each of the affected tables. And the trigger function runs the respective check expression. Conceptually, this doesn't seem to be very far away from foreign key constraints after all. I thought the point of ASSERTIONs was that you could write a thing such as: CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4); Enforcing that kind of constraints without true serializability seems impractical. Regards, Marko Tiikkaja -- 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 / shortcoming in has_*_privilege
On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby j...@nasby.net wrote: So there's no way to see if a particular privilege has been granted to public. ISTM 'public' should be accepted, since you can't use it as a role name anyway... It's a bit sticky - you could make that work for has_table_privilege(name, oid, text) or has_table_privilege(name, text, text), but what would you do about the versions whose first argument is an oid? Nothing. The only reason to use those forms is in a join against pg_authid, and the public group doesn't have an entry there. ISTM this bug should be on the open items list... -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression tests versus the buildfarm environment
On Wed, Aug 11, 2010 at 06:42, Tom Lane t...@sss.pgh.pa.us wrote: I am not sure if there's anything very good we can do about the problem of pg_regress misidentifying the postmaster it's managed to connect to. A real solution would probably be much more trouble than it's worth, anyway. However, it does seem like we ought to be able to do something about two buildfarm critters defaulting to the same choice of port number. The buildfarm infrastructure goes to great lengths to pick nonconflicting port numbers for the installed postmasters it runs; but we're ignoring all that effort and just using a hardwired port number for make check. This is dumb. pg_regress does have a --port argument that can be used to override that default. I don't know whether the buildfarm script calls pg_regress directly or does make check. If the latter, we'd need to twiddle the Makefiles to allow a port number to get passed in. But this seems well worthwhile to me. Comments? We just put in the possibility to name the client connections. Would it be interesting to be able to name the server installation itself?
Re: [HACKERS] assertions and constraint triggers
On Wed, 2010-08-11 at 08:31 +0300, Peter Eisentraut wrote: Thinking about SQL assertions (check constraints that are independent of one particular table), do you think it would be reasonable to implement those on top of constraint triggers? On creation you'd hook up a trigger to each of the affected tables. And the trigger function runs the respective check expression. Conceptually, this doesn't seem to be very far away from foreign key constraints after all. I would be interested in virtual assertions, i.e. allowing the user to say it is true without it being enforced. The cost of executing enforced assertions is likely to be prohibitive. Most common use case if you do have them is the equivalent of minoccurs/maxoccurs constraints in an XML Schema definition (XSD). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] assertions and constraint triggers
2010/8/11 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi: On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote: Thinking about SQL assertions (check constraints that are independent of one particular table), do you think it would be reasonable to implement those on top of constraint triggers? On creation you'd hook up a trigger to each of the affected tables. And the trigger function runs the respective check expression. Conceptually, this doesn't seem to be very far away from foreign key constraints after all. I thought the point of ASSERTIONs was that you could write a thing such as: CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4); Enforcing that kind of constraints without true serializability seems impractical. Exactly what I thought when I read this. Without true serializability, the view of the database at any moment during a transaction doesn't have to be the same as the view that a newly started transaction gets. Therefore, checking that the assertion holds after changing something doesn't necessarily guarantee that it will hold for any other transactions. To elaborate on a variant of Marko's example, where the = is replaced with =. Assume non-true SERIALIZABLE transactions: * The table has 3 rows. * T1 inserts a row, and concurrently, T2 also inserts a row; after each statement, the assertion is not violated for the corresponding transaction's snapshot. * The assertion is now violated for a subsequent transaction T3 (because it sees 5 rows). Nicolas -- 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] MERGE Specification
On Wed, Aug 11, 2010 at 12:18 PM, Boxuan Zhai bxzhai2...@gmail.com wrote: On Wed, Aug 11, 2010 at 12:14 PM, Greg Smith g...@2ndquadrant.comwrote: Boxuan Zhai wrote: I just found that no Assert() works in my codes. I think it is because the assertion is no enabled. How to enable assertion. To define USE_ASSERT_CHECKING somewhere? When you run configure before make, use --enable-cassert. The normal trio for working on the PostgreSQL code is: ./configure --enable-depend --enable-cassert --enable-debug Generally the only reason to build as a developer without asserts on is to do performance testing. They will slow some portions of the code down significantly. Thanks. I will test MERGE under this new configuration. A new patch will be submitted once I fix all the asserting bugs. The new patch is done. I named it as merge_v102. (1 means it is the non-inheritance merge command, 02 means this is the second time of fixing reported bugs) -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us http://www.2ndquadrant.us/ merge_v102.tar.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] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)
On Sat, 2010-07-24 at 18:57 -0400, Joseph Adams wrote: I've been developing it as a contrib module because: * I'd imagine it's easier than developing it as a built-in datatype right away (e.g. editing a .sql.in file versus editing pg_type.h ). * As a module, it has PGXS support, so people can try it out right away rather than having to recompile PostgreSQL. I, for one, think it would be great if the JSON datatype were all in core :-) However, if and how much JSON code should go into core is up for discussion. Thoughts, anyone? As a GSoC piece of work, doing it as a contrib module gives an immediately useful deliverable. Good plan. Once that is available, we can then get some feedback on it and include it as an in-core datatype later in the 9.1 cycle. So lets do both: contrib and in-core. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] trace_recovery_messages
On Tue, 2010-08-10 at 23:28 +0900, Fujii Masao wrote: ISTM the right is * Categorized into DEVELOPER_OPTIONS * The default is DEBUG1 * The context is PGC_SIGHUP Don't think we should go live with default of DEBUG1. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE command for inheritance
On 10/08/10 12:38, Boxuan Zhai wrote: These days I am considering what else can be done for MERGE, And, I find inheritance tables in postgres is not supported by our MERGE command yet. I played with your latest patch version a bit, and actually, it seems to me that inherited tables work just fine. I ran into the assertion failures earlier while trying that, but that has now been fixed. Can you give an example of the kind of query that's not working yet? -- 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] trace_recovery_messages
On Wed, Aug 11, 2010 at 5:26 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-08-10 at 23:28 +0900, Fujii Masao wrote: ISTM the right is * Categorized into DEVELOPER_OPTIONS * The default is DEBUG1 * The context is PGC_SIGHUP Don't think we should go live with default of DEBUG1. You think the default should be WARNING as described, and guc.c should be changed accordingly? I have no objection to it. 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] MERGE command for inheritance
On Tue, 2010-08-10 at 17:15 +0300, Heikki Linnakangas wrote: On 10/08/10 12:38, Boxuan Zhai wrote: The difficult way is to generate the plans for children table in planner, as the other commands like UPDATE and DELETE. However, because the structure of MERGE plan is much more complex than the ordinary ModifyTable plans, this job may not as simple as we expected. We need to adjust both the main plan and the merge actions to fit the children tables, which is not straight forward. This the approach you'll have to take. But actually, I'm surprised it doesn't happen to just work already. It should be opaque to the merge facility that the reference to the parent target table has inherited child tables - expanding the inherited table to scans of all the children should already be handled by the planner. The support for UPDATE and SELECT of partitioned cases is very different in the planner and was handled as separate implementation projects. If we want a working MERGE in the next release, I suggest that we break down this project in the same way and look at partitioned target tables as a separate project. One reason for suggesting this is that all MERGE statements have a source table, whereas UPDATE and DELETEs did not always. The plan for a simple UPDATE and DELETE against a partitioned table is simple, but the plan (and performance) of a joined UPDATE or DELETE is not good: postgres=# explain update p set col2 = x.col2 from x where x.col1 = p.col1; QUERY PLAN --- Update (cost=299.56..1961.18 rows=68694 width=20) - Merge Join (cost=299.56..653.73 rows=22898 width=20) Merge Cond: (public.p.col1 = x.col1) - Sort (cost=149.78..155.13 rows=2140 width=10) Sort Key: public.p.col1 - Seq Scan on p (cost=0.00..31.40 rows=2140 width=10) - Sort (cost=149.78..155.13 rows=2140 width=14) Sort Key: x.col1 - Seq Scan on x (cost=0.00..31.40 rows=2140 width=14) - Merge Join (cost=299.56..653.73 rows=22898 width=20) Merge Cond: (public.p.col1 = x.col1) - Sort (cost=149.78..155.13 rows=2140 width=10) Sort Key: public.p.col1 - Seq Scan on p1 p (cost=0.00..31.40 rows=2140 width=10) - Sort (cost=149.78..155.13 rows=2140 width=14) Sort Key: x.col1 - Seq Scan on x (cost=0.00..31.40 rows=2140 width=14) - Merge Join (cost=299.56..653.73 rows=22898 width=20) Merge Cond: (public.p.col1 = x.col1) - Sort (cost=149.78..155.13 rows=2140 width=10) Sort Key: public.p.col1 - Seq Scan on p2 p (cost=0.00..31.40 rows=2140 width=10) - Sort (cost=149.78..155.13 rows=2140 width=14) Sort Key: x.col1 - Seq Scan on x (cost=0.00..31.40 rows=2140 width=14) Those plans could use some love and attention before forcing Boxuan to implement that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] string_to_array with an empty input string
2010/8/11 Greg Stark gsst...@mit.edu:
On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
peter.geoghega...@gmail.com wrote:
On 10 August 2010 19:48, David E. Wheeler da...@kineticode.com wrote:
On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:
I, personally, would expect an empty array output given an empty
input, and a null output for a null input.
+1
Agreed. After all, the result isn't indeterminate - it's an empty
array. Some people might think that it's useful for the result to be
NULL, but they'd probably also think that it's useful for an empty
string to be NULL.
For what it's worth there are two reasonable return values for
string_to_array(''). It could be [] or it could be ['']. There are
applications where the former makes the most sense and there are
applications where the latter makes the most sense.
you have a true. The safe solution is return NULL on empty string. But
this behave is pretty unpractical for all domains other than texts. On
numeric or date there are not possible described situation.
I have a two ideas, just ideas:
a) to create a text_to_array function as complement to string_to_array
function. This function is same as string_to_array, but empty string
can be a NULL. But I see it as too academical.
b) to create a functions explode for other than text domains. One
parameter can be a regtype of expected array (maybe element). Then we
can correctly to decide what is correct result for empty string, and
we can to safe a some memory/time because the result will not be a
short life text array but desired array.
explode('1,2,3,4,,5', ',', '', int[])
Similar function have to be implemented with parser and transformation
changes - so we can design this function more verbose if we want:
explode('1,2,3,4,5,,' TO int[] DELIMITER AS ',' NULL AS '')
delimiter and nullstr can be a optional.
c) do nothing and returns NULL for empty string :(
I like a variant b.
Regards
Pavel Stehule
Ideally you really want string_to_array(array_to_string(x, ':'),':')
to return x. There's no safe return value to pick for the cases where
x=[''] and x=[] that will make this work.
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On fre, 2010-08-06 at 10:28 +0300, Heikki Linnakangas wrote: IMO the UPDATE/DELETE/INSERT actions should fire the respective statement level triggers, but the MERGE itself should not. Yes, SQL defines the triggering of triggers as part of the modification of rows, not as part of any particular statement that causes the modification. -- 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] assertions and constraint triggers
On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote: Enforcing that kind of constraints without true serializability seems impractical. Yes, but that is being worked on, I understand. -- 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] assertions and constraint triggers
On 8/11/10 1:18 PM +0300, Peter Eisentraut wrote: On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote: Enforcing that kind of constraints without true serializability seems impractical. Yes, but that is being worked on, I understand. Correct. But you'd have to somehow make the constraints to be checked with true serializability, and that part of the original suggestion seemed to be completely missing. Not sure how hard that would be though. Regards, Marko Tiikkaja -- 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] MERGE command for inheritance
On 11/08/10 11:45, Simon Riggs wrote: On Tue, 2010-08-10 at 17:15 +0300, Heikki Linnakangas wrote: On 10/08/10 12:38, Boxuan Zhai wrote: The difficult way is to generate the plans for children table in planner, as the other commands like UPDATE and DELETE. However, because the structure of MERGE plan is much more complex than the ordinary ModifyTable plans, this job may not as simple as we expected. We need to adjust both the main plan and the merge actions to fit the children tables, which is not straight forward. This the approach you'll have to take. But actually, I'm surprised it doesn't happen to just work already. It should be opaque to the merge facility that the reference to the parent target table has inherited child tables - expanding the inherited table to scans of all the children should already be handled by the planner. The support for UPDATE and SELECT of partitioned cases is very different in the planner and was handled as separate implementation projects. Ok, thinking and experminting this some more I finally understand what the problem is. Yeah, the patch doesn't currently work when the target table has inherited child tables, it only takes the parent table into account and ignores all child tables. If we want a working MERGE in the next release, I suggest that we break down this project in the same way and look at partitioned target tables as a separate project. One reason for suggesting this is that all MERGE statements have a source table, whereas UPDATE and DELETEs did not always. The plan for a simple UPDATE and DELETE against a partitioned table is simple, but the plan (and performance) of a joined UPDATE or DELETE is not good: I don't think we can just leave it as it is. If the performance sucks, that's fine and can be handled in a future release, but it should at least produce the correct result. I concur that Boxuan's suggested difficult approach seems like the right one. -- 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] Bug / shortcoming in has_*_privilege
On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby j...@nasby.net wrote: So there's no way to see if a particular privilege has been granted to public. ISTM 'public' should be accepted, since you can't use it as a role name anyway... It's a bit sticky - you could make that work for has_table_privilege(name, oid, text) or has_table_privilege(name, text, text), but what would you do about the versions whose first argument is an oid? Nothing. The only reason to use those forms is in a join against pg_authid, and the public group doesn't have an entry there. ISTM this bug should be on the open items list... I don't think this is a bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Regression tests versus the buildfarm environment
On 08/11/2010 12:42 AM, Tom Lane wrote: There's an interesting buildfarm failure here: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=polecatdt=2010-08-10%2023:46:10 It appears to me that this was caused by the concurrent run of another buildfarm animal on the same physical machine, namely: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=colugosdt=2010-08-11%2000:02:58 Both animals are trying to test HEAD, which means that pg_regress defaults to the same postmaster port number in both builds: if (temp_install !port_specified_by_user) /* * To reduce chances of interference with parallel installations, use * a port number starting in the private range (49152-65535) * calculated from the version number. */ port = 0xC000 | (PG_VERSION_NUM 0x3FFF); We observe colugos successfully starting on that port: == starting postmaster== running on port 57332 with pid 47019 == creating database regression == CREATE DATABASE ALTER DATABASE ... etc etc ... polecat comes along what must be only moments later, and tries to use the same port for its temp install: == starting postmaster== running on port 57332 with pid 47022 == creating database regression == ERROR: duplicate key value violates unique constraint pg_database_datname_index DETAIL: Key (datname)=(regression) already exists. command failed: /usr/local/src/build-farm-3.2/builds/HEAD/pgsql.15278/src/test/regress/./tmp_check/install//usr/local/src/build-farm-3.2/builds/HEAD/inst/bin/psql -X -c CREATE DATABASE \regression\ TEMPLATE=template0 ENCODING='SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' postgres pg_ctl: PID file /usr/local/src/build-farm-3.2/builds/HEAD/pgsql.15278/src/test/regress/./tmp_check/data/postmaster.pid does not exist Is server running? pg_regress: could not stop postmaster: exit code was 256 Now the postmaster log shows that the second postmaster correctly recognized that the port number was already in use, so it bailed out: == pgsql.15278/src/test/regress/log/postmaster.log === [4c61f2d2.b7ae:1] FATAL: lock file /tmp/.s.PGSQL.57332.lock already exists [4c61f2d2.b7ae:2] HINT: Is another postmaster (PID 47019) using socket file /tmp/.s.PGSQL.57332? However, pg_regress failed to have a clue about what had happened, and bulled ahead trying to run the regression tests (against the postmaster started by the other pg_regress instance). A look at the code shows that it is merely trying to run psql, and if psql reports that it can connect to the specified port, then pg_regress thinks the postmaster started OK. Of course, psql was really reporting that it could connect to the other instance's postmaster. I've seen similar multiple-postmaster-interference symptoms before in the buildfarm, but never really understood the cause. I am not sure if there's anything very good we can do about the problem of pg_regress misidentifying the postmaster it's managed to connect to. A real solution would probably be much more trouble than it's worth, anyway. However, it does seem like we ought to be able to do something about two buildfarm critters defaulting to the same choice of port number. The buildfarm infrastructure goes to great lengths to pick nonconflicting port numbers for the installed postmasters it runs; but we're ignoring all that effort and just using a hardwired port number for make check. This is dumb. pg_regress does have a --port argument that can be used to override that default. I don't know whether the buildfarm script calls pg_regress directly or does make check. If the latter, we'd need to twiddle the Makefiles to allow a port number to get passed in. But this seems well worthwhile to me. Comments? The buildfarm calls make check. Why not just add the configured port (DEF_PGPORT) into the calculation of the port to run on? 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] MERGE command for inheritance
On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote: I concur that Boxuan's suggested difficult approach seems like the right one. Right, but you've completely ignored my proposal: lets do this in two pieces. Get what we have now ready to commit, then add support for partitioning later, as a second project. Two reasons for this: we endanger the current project by adding more to it in one go, plus work on other aspects of partitioning is happening concurrently and the two are likely to conflict and/or waste effort. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Develop item from TODO list
Hello! We have chosen another item from the list: Allow ALTER TABLE to change constraint deferrability and actions Is this already done? If yes, can you recommend any task which is appropriate for beginners in open-source software? Thanks in advance, Chris Viktor 2010/8/4 Bruce Momjian br...@momjian.us Tom Lane wrote: Viktor Valy vili0...@gmail.com writes: We are 2 Students from the Technical University of Vienna. At our internship we would like to develop the item of the TODO list: Allow SET CONSTRAINTS to be qualified by schema/table name. Is anyone working on it? Uh, it was done years ago, AFAICS, unless the Todo entry means something non-obvious. regression=# create schema foo; CREATE SCHEMA regression=# create table foo.bar (f1 int unique deferrable); NOTICE: CREATE TABLE / UNIQUE will create implicit index bar_f1_key for table bar CREATE TABLE regression=# set constraints foo.bar_f1_key deferred; SET CONSTRAINTS regression=# set constraints foo.bar_f1_key immediate; SET CONSTRAINTS regression=# Bruce, do you remember what that entry was really about? Yep, that was it. I have remove that TODO item. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
[HACKERS] pgstat_report_waiting() in hot standby
Hi, ResolveRecoveryConflictWithVirtualXIDs() calls pgstat_report_waiting(), but it seems useless (though harmless) since the startup process doesn't have the shared memory entry (i.e., MyBEEntry) for pg_stat_activity. We should remove it? 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
[HACKERS] micro bucket sort ...
hello all ... i am bugged with a small issue which is basically like this ... test=# create table t_test as select x, x % 5 as y from generate_series(1, 100) AS x; SELECT test=# create index idx_a on t_test (x) ; CREATE INDEX test=# ANALYZE ; ANALYZE test=# explain analyze select * from t_test order by x; QUERY PLAN Index Scan using idx_a on t_test (cost=0.00..30408.36 rows=100 width=8) (actual time=0.057..311.832 rows=100 loops=1) Total runtime: 392.943 ms (2 rows) we know that we get sorted output from the index and thus we do the index traversal here ... if you add a condition to the sorting you will naturally get a sort in postgres because y is clearly now known to be sorted. test=# explain analyze select * from t_test order by x, y; QUERY PLAN Sort (cost=141431.84..143931.84 rows=100 width=8) (actual time=1086.014..1271.257 rows=100 loops=1) Sort Key: x, y Sort Method: external sort Disk: 17608kB - Seq Scan on t_test (cost=0.00..14425.00 rows=100 width=8) (actual time=0.024..143.474 rows=100 loops=1) Total runtime: 1351.848 ms (5 rows) same with limit ... test=# explain analyze select * from t_test order by x, y limit 20; QUERY PLAN -- Limit (cost=41034.64..41034.69 rows=20 width=8) (actual time=317.939..317.943 rows=20 loops=1) - Sort (cost=41034.64..43534.64 rows=100 width=8) (actual time=317.934..317.936 rows=20 loops=1) Sort Key: x, y Sort Method: top-N heapsort Memory: 26kB - Seq Scan on t_test (cost=0.00..14425.00 rows=100 width=8) (actual time=0.019..144.109 rows=100 loops=1) Total runtime: 317.995 ms (6 rows) now, the problem is: i cannot easily create additional indexes as i have too many possible second conditions here. what makes it even more funny: i don't have enough space to do the resort of the entire thing (X TB). so, a more expensive index traversal is my only option. my question is: is there already a concept out there to make this work or does anybody know of a patch out there addressing an issue like that? some idea is heavily appreciated. it seems our sort key infrastructure is not enough for this. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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 / shortcoming in has_*_privilege
* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote: Nothing. The only reason to use those forms is in a join against pg_authid, and the public group doesn't have an entry there. ISTM this bug should be on the open items list... I don't think this is a bug. Agreed, and it's certainly not something that needs to be dealt with for 9.0.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] MERGE command for inheritance
On Wed, Aug 11, 2010 at 4:27 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10/08/10 12:38, Boxuan Zhai wrote: These days I am considering what else can be done for MERGE, And, I find inheritance tables in postgres is not supported by our MERGE command yet. I played with your latest patch version a bit, and actually, it seems to me that inherited tables work just fine. I ran into the assertion failures earlier while trying that, but that has now been fixed. Can you give an example of the kind of query that's not working yet? Well, in the patch I submitted, the target relation is forced not to scan any inheritance tables. That is, the command always acts like MERGE into *ONLY* foo USING bar So, the inheritance in current MERGE should not work, I think. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Bug / shortcoming in has_*_privilege
On Wed, 2010-08-11 at 06:48 -0400, Robert Haas wrote: On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby j...@nasby.net wrote: So there's no way to see if a particular privilege has been granted to public. ISTM 'public' should be accepted, since you can't use it as a role name anyway... It's a bit sticky - you could make that work for has_table_privilege(name, oid, text) or has_table_privilege(name, text, text), but what would you do about the versions whose first argument is an oid? Nothing. The only reason to use those forms is in a join against pg_authid, and the public group doesn't have an entry there. ISTM this bug should be on the open items list... I don't think this is a bug. It clearly rates higher in importance than most of the things on the open items list of late... -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE command for inheritance
On 11/08/10 14:44, Simon Riggs wrote: On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote: I concur that Boxuan's suggested difficult approach seems like the right one. Right, but you've completely ignored my proposal: lets do this in two pieces. Get what we have now ready to commit, then add support for partitioning later, as a second project. It seems like a pretty serious omission. What would you do, thrown a MERGE to inherited tables not implemented error? -- 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] micro bucket sort ...
On Wed, 2010-08-11 at 14:21 +0200, Hans-Jürgen Schönig wrote: my question is: is there already a concept out there to make this work or does anybody know of a patch out there addressing an issue like that? some idea is heavily appreciated. it seems our sort key infrastructure is not enough for this. Already discussed as a partial sort. Thinks its on the TODO. SMOP. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: psql: edit function, show function commands patch
On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: The \e patch definitely needs another read-through. I noticed a number of comments that were still pretty poor English, and one --- /* skip header lines */ --- that seems just plain wrong. The actual intent of that next bit is to increase lineno to account for header lines, which is not well conveyed by skip. Interestingly, I had already rewritten pretty much every comment in the patch, and the entirety of the documentation, but I found a very small number of stragglers this morning and made a few more adjustments. If you're still unhappy with it, you're going to need to be more specific, or hack on it yourself. BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag seems grossly overdesigned. It would be clearer, shorter, and faster if you just had a strncmp test for AS $function there. As far as I can see, the only purpose of that code is to support the desire to have \sf+ display rather than a line number for the lines that FOLLOW the function body. But I'm wondering if we should just forget about that and let the numbering run continuously from the first AS $function line to end of file. That would get rid of a bunch of rather grotty code in the \sf patch, also. Also, the entire thing is subject to misbehavior in the case of \e (as opposed to \ef), which really cannot safely assert() that it's reading the output of pg_get_functiondef(). My inclination is to pull that part out of do_edit and put it into \ef-specific code. Oh, for pity's sake. I had thought that code WAS \ef-specific (because it doesn't make any sense otherwise) but I see that you are correct. Also, there seemed to be some gratuitous inconsistency in the handling of tests on line number variables, eg some places lineno 0 and others lineno = 1. I think this is now fixed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company edit8-rmh-v2.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] Bug / shortcoming in has_*_privilege
On Wed, Aug 11, 2010 at 8:51 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-08-11 at 06:48 -0400, Robert Haas wrote: On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby j...@nasby.net wrote: So there's no way to see if a particular privilege has been granted to public. ISTM 'public' should be accepted, since you can't use it as a role name anyway... It's a bit sticky - you could make that work for has_table_privilege(name, oid, text) or has_table_privilege(name, text, text), but what would you do about the versions whose first argument is an oid? Nothing. The only reason to use those forms is in a join against pg_authid, and the public group doesn't have an entry there. ISTM this bug should be on the open items list... I don't think this is a bug. It clearly rates higher in importance than most of the things on the open items list of late... First, I don't think that's true. WALreceiver crashing on AIX, the backup procedure in the manual possibly being wrong, and the documentation failing to be installed sometimes all seem like they are clearly more serious issues than this. I am sort of wondering why no one is working on those issues; apparently, nobody other than me minds if it takes another three months to get 9.0 out the door. Frankly, I think the ExplainOnePlan bit is more important, too, although I'm starting to think we should fix that for 9.1 rather than 9.0. Second, even if it were true, the fact that something is important does not make it a bug fix. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Develop item from TODO list
On Wed, Aug 11, 2010 at 7:47 AM, Viktor Valy vili0...@gmail.com wrote: We have chosen another item from the list: Allow ALTER TABLE to change constraint deferrability and actions I believe that is not done. What does the TODO list item mean by and actions? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] MERGE command for inheritance
On Wed, 2010-08-11 at 15:53 +0300, Heikki Linnakangas wrote: On 11/08/10 14:44, Simon Riggs wrote: On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote: I concur that Boxuan's suggested difficult approach seems like the right one. Right, but you've completely ignored my proposal: lets do this in two pieces. Get what we have now ready to commit, then add support for partitioning later, as a second project. It seems like a pretty serious omission. What would you do, thrown a MERGE to inherited tables not implemented error? It's not a serious omission to do work in multiple phases. I have not proposed that we neglect that work, only that it happens afterwards. Phasing work often allows the whole to be delivered quicker and it reduces the risk that we end up with nothing at all or spaghetti code through rushing things. We have already split MERGE into two phases from its original scope, where the majority thought for many years that MERGE without concurrent locking was unacceptable. Splitting MERGE into 3 phases now is hardly an earth shaking proposal. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Develop item from TODO list
Robert Haas robertmh...@gmail.com wrote: What does the TODO list item mean by and actions? Things like ON DELETE CASCADE versus ON DELETE RESTRICT? -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] Regression tests versus the buildfarm environment
Andrew Dunstan and...@dunslane.net writes: On 08/11/2010 12:42 AM, Tom Lane wrote: ... However, it does seem like we ought to be able to do something about two buildfarm critters defaulting to the same choice of port number. Why not just add the configured port (DEF_PGPORT) into the calculation of the port to run on? No, that would be just about the worst possible choice. It'd be guaranteed to fail in the standard scenario that you are running make check before updating an existing installation. I think what we want to do here is arrange for the buildfarm script to select the same port that it's going to use later for an installed postmaster, but it has to go via a different path than DEF_PGPORT. The first thought that comes to mind is to adjust the makefiles like this: ifdef REGRESSION_TEST_PORT ... add --port $(REGRESSION_TEST_PORT) to pg_regress flags ... endif and then the buildfarm script could use make REGRESSION_TEST_PORT=nnn check But I'm not sure what the cleanest way is if we have to pass that down from the top-level makefile. Make doesn't pass down variables automatically does it? Another possibility is to allow a regression test port number to be configured via configure; though that seems like a slightly larger change than I'd want to push into the back branches. 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] Regression tests versus the buildfarm environment
Tom Lane t...@sss.pgh.pa.us wrote: A look at the code shows that it is merely trying to run psql, and if psql reports that it can connect to the specified port, then pg_regress thinks the postmaster started OK. Of course, psql was really reporting that it could connect to the other instance's postmaster. Clearly picking unique ports for `make check` is the ultimate solution, but I'm curious whether this would have been caught sooner with less effort if the pg_ctl TODO titled Have the postmaster write a random number to a file on startup that pg_ctl checks against the contents of a pg_ping response on its initial connection (without login) had been implemented. http://archives.postgresql.org/pgsql-bugs/2009-10/msg00110.php It sounds like it's related; but was curious to confirm. -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] RecordTransactionCommit() and SharedInvalidationMessages
On Wed, Aug 11, 2010 at 1:17 AM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Aug 10, 2010 at 9:30 AM, Robert Haas robertmh...@gmail.com wrote: It appears to me that RecordTransactionCommit() only needs to WAL-log shared invalidation messages when wal_level is hot_standby, but I don't see a guard to prevent it from doing it in all cases. Perhaps right. During not hot standby, there is no backend which the startup process should send invalidation message to in the standby. So, ISTM we don't need to log invalidation message when wal_level is not hot_standby. The fix looks pretty simple (see attached), although I don't have any clear idea how to test it. I guess the question is whether we should back-patch this to 9.0. It isn't technically necessary for correctness, but the whole point of introducing the wal_level GUC was to insulate people not running Hot Standby from possible bugs in the Hot Standby code, as well as to avoid unnecessary WAL bloat, so on balance I'm inclined to think we should go ahead and back-patch it. Other opinions? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company record_transaction_commmit.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
[HACKERS] Inconsistent ::bit(N) and get_bit()?
Hi, I just came across the following confusing thing. zozo=# create table bit_test(i integer); CREATE TABLE zozo=# insert into bit_test values (1), (2), (3); INSERT 0 3 zozo=# select i, i::bit(2), get_bit(i::bit(2), 1) as bit1, get_bit(i::bit(2), 0) as bit0 from bit_test; i | i | bit1 | bit0 ---++--+-- 1 | 01 |1 |0 2 | 10 |0 |1 3 | 11 |1 |1 (3 rows) So, conversion from int to bitstring creates a readable bitstring, i.e. the least significant bit is the rightmost one. But get_bit() on the same bit string works in the opposite order. The only description about get_bit I found in the 9.0beta docs are in http://www.postgresql.org/docs/9.0/static/functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER - FunctionReturn Type Description Example Result || |get_bit|(string, offset) int Extract bit from string get_bit(E'Th\\000omas'::bytea, 45) 1 - || and in http://www.postgresql.org/docs/9.0/static/functions-bitstring.html - ||The following functions work on bit strings as well as binary strings: |get_bit|, |set_bit|. - Shouldn't it at least be documented in more depth? Say, get_bit(, N) provides the Nth bit (0-based) counting from the leftmost bit? I would certainly appreciate a warning spelled out about this so if you convert a number to bitstring of length N and you want the Mth bit (according to any programming language) then you need to use get_bit(..., N-1-M). Best regards, Zoltán Böszörményi -- 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] Regression tests versus the buildfarm environment
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: A look at the code shows that it is merely trying to run psql, and if psql reports that it can connect to the specified port, then pg_regress thinks the postmaster started OK. Of course, psql was really reporting that it could connect to the other instance's postmaster. Clearly picking unique ports for `make check` is the ultimate solution, but I'm curious whether this would have been caught sooner with less effort if the pg_ctl TODO titled Have the postmaster write a random number to a file on startup that pg_ctl checks against the contents of a pg_ping response on its initial connection (without login) had been implemented. It would certainly make the failure more transparent. As I mentioned, there are previous buildfarm failures that look like they might be caused by a similar conflict, but it's seldom possible to be sure. A cross-check like that would be much safer. BTW, I don't know why anyone would think that a random number would offer any advantage here. I'd use the postmaster PID, which is guaranteed to be unique across the space that you're worried about. In fact, you could implement this off the existing postmaster.pid, no need for any new file. What's lacking is the pg_ping protocol. 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] Regression tests versus the buildfarm environment
On 08/11/2010 09:43 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 08/11/2010 12:42 AM, Tom Lane wrote: ... However, it does seem like we ought to be able to do something about two buildfarm critters defaulting to the same choice of port number. Why not just add the configured port (DEF_PGPORT) into the calculation of the port to run on? No, that would be just about the worst possible choice. It'd be guaranteed to fail in the standard scenario that you are running make check before updating an existing installation. One of us is missing something. I didn't say to run the checks using the configured port. I had in mind something like: port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) 0x3FFF); 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] Regression tests versus the buildfarm environment
Tom Lane t...@sss.pgh.pa.us wrote: BTW, I don't know why anyone would think that a random number would offer any advantage here. I'd use the postmaster PID, which is guaranteed to be unique across the space that you're worried about. Well, in the post I cited, it was you who argued that the PID was a bad choice, suggested a random number, and stated That would have a substantially lower collision probability than PID, if the number generation process were well designed; and it wouldn't risk exposing anything sensitive in the ping response. -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] MERGE command for inheritance
On Wed, Aug 11, 2010 at 4:45 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-08-10 at 17:15 +0300, Heikki Linnakangas wrote: On 10/08/10 12:38, Boxuan Zhai wrote: The difficult way is to generate the plans for children table in planner, as the other commands like UPDATE and DELETE. However, because the structure of MERGE plan is much more complex than the ordinary ModifyTable plans, this job may not as simple as we expected. We need to adjust both the main plan and the merge actions to fit the children tables, which is not straight forward. This the approach you'll have to take. But actually, I'm surprised it doesn't happen to just work already. It should be opaque to the merge facility that the reference to the parent target table has inherited child tables - expanding the inherited table to scans of all the children should already be handled by the planner. The support for UPDATE and SELECT of partitioned cases is very different in the planner and was handled as separate implementation projects. If we want a working MERGE in the next release, I suggest that we break down this project in the same way and look at partitioned target tables as a separate project. One reason for suggesting this is that all MERGE statements have a source table, whereas UPDATE and DELETEs did not always. The plan for a simple UPDATE and DELETE against a partitioned table is simple, but the plan (and performance) of a joined UPDATE or DELETE is not good: postgres=# explain update p set col2 = x.col2 from x where x.col1 = p.col1; QUERY PLAN --- Update (cost=299.56..1961.18 rows=68694 width=20) - Merge Join (cost=299.56..653.73 rows=22898 width=20) Merge Cond: (public.p.col1 = x.col1) - Sort (cost=149.78..155.13 rows=2140 width=10) Sort Key: public.p.col1 - Seq Scan on p (cost=0.00..31.40 rows=2140 width=10) - Sort (cost=149.78..155.13 rows=2140 width=14) Sort Key: x.col1 - Seq Scan on x (cost=0.00..31.40 rows=2140 width=14) - Merge Join (cost=299.56..653.73 rows=22898 width=20) Merge Cond: (public.p.col1 = x.col1) - Sort (cost=149.78..155.13 rows=2140 width=10) Sort Key: public.p.col1 - Seq Scan on p1 p (cost=0.00..31.40 rows=2140 width=10) - Sort (cost=149.78..155.13 rows=2140 width=14) Sort Key: x.col1 - Seq Scan on x (cost=0.00..31.40 rows=2140 width=14) - Merge Join (cost=299.56..653.73 rows=22898 width=20) Merge Cond: (public.p.col1 = x.col1) - Sort (cost=149.78..155.13 rows=2140 width=10) Sort Key: public.p.col1 - Seq Scan on p2 p (cost=0.00..31.40 rows=2140 width=10) - Sort (cost=149.78..155.13 rows=2140 width=14) Sort Key: x.col1 - Seq Scan on x (cost=0.00..31.40 rows=2140 width=14) Those plans could use some love and attention before forcing Boxuan to implement that. It seems that we have not decided whether to put the inheritance for MERGE off for a latter implementation. But, I think we can discuss how to do it now. First of all, the inheritance of MERGE should not be implemented in the rule-like way. I agree that the easy way I proposed is not consistent with the general inheritance process in postgres. The normal way of doing this is to handle it in planner, to be more specific, we need to extend the function inheritance_planner() for processing MERGE queries. For UPDATE and DELETE commands (INSERT is not an inheritable command), if inheritance_planner finds that the target table has children tables, it will generate a list of queries. These queries are almost the same as the original query input by user, except for the different target relations. Each child table has it corresponding query in this list. This list of queries will then be processed by grouping_planner() and transformed into a list of plans. One most important work finished in this function is to extend the target list of target relations to make sure that all attributes of a target relation appears in the final result tuple of its plan. As for MERGE command, we need to do the same thing. But, since the main query body is a LEFT JOIN query between source table and target table, the top-level target list is a combination of all the attributes from source table and target table. Thus, when we extend the target list, we should only extent the part of target relations, and keep the source table part untouched. Once a main query in this style has been transformed to plan, we need to prepare the merge actions for it too. That is, extend the target list of all UPDATE and INSERT actions for the corresponding target relation. In this way, each target relation will have its own
Re: [HACKERS] [ADMIN] postgres 9.0 crash when bringing up hot standby
On Fri, Aug 6, 2010 at 3:53 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So, loading libpqwalreceiver library crashes. It looks like it might be pthread-related. Perhaps something wrong with our makefiles, causing libpqwalreceiver to be built with wrong flags? Does contrib/dblink work? If you look at the build log, what is the command line used to compile libpqwalreceiver, and what is the command line used to build other libraries, like contrib/dblink? I haven't seen any response to this from the OP, but it seems worrisome. Has anyone else tested a Hot Standby configuraration - successfully or otherwise - on AIX? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] MERGE command for inheritance
On Wed, Aug 11, 2010 at 10:09 AM, Boxuan Zhai bxzhai2...@gmail.com wrote: PS: Since I have taken this project, I will do my best to make it perfect. I will keep working on MERGE until it is really finished, even after the gSoC. (unless you guys has other plans). That is great to hear! FWIW, I agree with Heikki that we should try to have the inheritance stuff working properly in the first version. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Regression tests versus the buildfarm environment
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: BTW, I don't know why anyone would think that a random number would offer any advantage here. I'd use the postmaster PID, which is guaranteed to be unique across the space that you're worried about. Well, in the post I cited, it was you who argued that the PID was a bad choice, suggested a random number, and stated That would have a substantially lower collision probability than PID, if the number generation process were well designed; and it wouldn't risk exposing anything sensitive in the ping response. Hmm. I don't remember why we'd think that the postmaster PID was sensitive information ... but if you take that as true, then yeah it couldn't be included in a pg_ping response. 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] Regression tests versus the buildfarm environment
Andrew Dunstan and...@dunslane.net writes: On 08/11/2010 09:43 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: Why not just add the configured port (DEF_PGPORT) into the calculation of the port to run on? No, that would be just about the worst possible choice. It'd be guaranteed to fail in the standard scenario that you are running make check before updating an existing installation. One of us is missing something. I didn't say to run the checks using the configured port. I had in mind something like: port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) 0x3FFF); Oh, I see, modify the DEF_PGPORT don't just use it as-is. OK, except that I think something like the above is still pretty risky for the buildfarm, because you would still have conflicts for assorted combinations of version numbers and branch_port settings. How about just this: port = 0xC000 | (DEF_PGPORT 0x3FFF); If anyone was actually using a DEF_PGPORT above 0xC000, this would mean that they couldn't run make check on the same machine as their running installation (at least not without adjusting pg_regress's port choice, which I still think we need to tweak the makefiles to make easier). But for ordinary buildfarm usage, this would be guaranteed not to conflict as long as you'd chosen nonconflicting branch_ports for all your branches and animals. Or we could do something like port = 0xC000 ^ (DEF_PGPORT 0x7FFF); which is absolutely guaranteed not to conflict with DEF_PGPORT, at the cost of possibly shifting into the 32K-48K port number range if you had set DEF_PGPORT above 48K. 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] Regression tests versus the buildfarm environment
On Wed, Aug 11, 2010 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: On 08/11/2010 09:43 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: Why not just add the configured port (DEF_PGPORT) into the calculation of the port to run on? No, that would be just about the worst possible choice. It'd be guaranteed to fail in the standard scenario that you are running make check before updating an existing installation. One of us is missing something. I didn't say to run the checks using the configured port. I had in mind something like: port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) 0x3FFF); Oh, I see, modify the DEF_PGPORT don't just use it as-is. OK, except that I think something like the above is still pretty risky for the buildfarm, because you would still have conflicts for assorted combinations of version numbers and branch_port settings. How about just this: port = 0xC000 | (DEF_PGPORT 0x3FFF); If anyone was actually using a DEF_PGPORT above 0xC000, this would mean that they couldn't run make check on the same machine as their running installation (at least not without adjusting pg_regress's port choice, which I still think we need to tweak the makefiles to make easier). But for ordinary buildfarm usage, this would be guaranteed not to conflict as long as you'd chosen nonconflicting branch_ports for all your branches and animals. Or we could do something like port = 0xC000 ^ (DEF_PGPORT 0x7FFF); which is absolutely guaranteed not to conflict with DEF_PGPORT, at the cost of possibly shifting into the 32K-48K port number range if you had set DEF_PGPORT above 48K. I like XOR a lot better than OR. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [ADMIN] postgres 9.0 crash when bringing up hot standby
On Wed, Aug 11, 2010 at 10:20 AM, Alanoly Andrews alano...@invera.com wrote: Ok..in response to the questions from Heikki, 1. Yes, contrib/dblink does work. Here's the output from the command used to make dblink: postgres:thimar /usr/bin/gmake -C contrib/dblink install gmake: Entering directory `/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink' /bin/sh ../../config/install-sh -c -d '/dinabkp/faouzis/local2/pgsql/lib' /bin/sh ../../config/install-sh -c -d '/dinabkp/faouzis/local2/pgsql/share/contrib' /bin/sh ../../config/install-sh -c -m 755 dblink.so '/dinabkp/faouzis/local2/pgsql/lib/dblink.so' /bin/sh ../../config/install-sh -c -m 644 ./uninstall_dblink.sql '/dinabkp/faouzis/local2/pgsql/share/contrib' /bin/sh ../../config/install-sh -c -m 644 dblink.sql '/dinabkp/faouzis/local2/pgsql/share/contrib' gmake: Leaving directory `/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink' Unfortunately that only shows the install, not the link - it must have been built earlier. Can you do make clean in that just that one directory, and then make install again? 2. I don't have records of the build logs for the regular postgres executables (which contains the libpqwalreceiver) but can do a new compile/make if that is required. But they were compiled and installed using the regular make files supplied along with the postgres source code. The following flags were added during the compilation: --without-readline --without-zlib --enable-debug --enable-cassert --enable-thread-safety It'd be nice to see the whole build log, if it's not too much trouble to regenerate it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] assertions and constraint triggers
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 8/11/10 1:18 PM +0300, Peter Eisentraut wrote: On ons, 2010-08-11 at 10:54 +0300, Marko Tiikkaja wrote: Enforcing that kind of constraints without true serializability seems impractical. Yes, but that is being worked on, I understand. Correct. But you'd have to somehow make the constraints to be checked with true serializability, and that part of the original suggestion seemed to be completely missing. Not sure how hard that would be though. I keep bumping into use cases where cool things could be done if you could be sure that *all* transactions were being run at the fully serializable transaction isolation level. Perhaps we could look at a GUC (or initdb option, if people fear the consequences of changes in an existing database) which not only defaults to serializable, but silently ignores requests for other levels. If we only allowed these constraints to be used in a database which was configured this way, they would work fine. Enforcing *part* of a transaction under full serializable isolation seems totally infeasible, unless someone has a clever idea I'm missing. -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] string_to_array with an empty input string
Greg Stark gsst...@mit.edu writes: Ideally you really want string_to_array(array_to_string(x, ':'),':') to return x. There's no safe return value to pick for the cases where x=[''] and x=[] that will make this work. It's easy to see that string_to_array/array_to_string are *not* usable as general-purpose serialize/deserialize operations, so sweating over corner cases like this one seems a bit pointless. The design center for 'em seems to be array elements that are numbers, so there's no issue with empty strings and no great difficulty in picking delimiters and null representations that can't appear in the data. I think they're essentially worthless for arrays of text. 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] RecordTransactionCommit() and SharedInvalidationMessages
On 11/08/10 16:46, Robert Haas wrote: On Wed, Aug 11, 2010 at 1:17 AM, Fujii Masaomasao.fu...@gmail.com wrote: On Tue, Aug 10, 2010 at 9:30 AM, Robert Haasrobertmh...@gmail.com wrote: It appears to me that RecordTransactionCommit() only needs to WAL-log shared invalidation messages when wal_level is hot_standby, but I don't see a guard to prevent it from doing it in all cases. Perhaps right. During not hot standby, there is no backend which the startup process should send invalidation message to in the standby. So, ISTM we don't need to log invalidation message when wal_level is not hot_standby. The fix looks pretty simple (see attached), although I don't have any clear idea how to test it. Should use XLogStandbyInfoActive() macro, for the sake of consistency. I guess the question is whether we should back-patch this to 9.0. It isn't technically necessary for correctness, but the whole point of introducing the wal_level GUC was to insulate people not running Hot Standby from possible bugs in the Hot Standby code, as well as to avoid unnecessary WAL bloat, so on balance I'm inclined to think we should go ahead and back-patch it. +1 for backpatching. Keeping the branches closer to each other makes backporting any future fixes easier too. -- 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] string_to_array with an empty input string
Greg Stark gsst...@mit.edu writes: There's already been one rather-long thread on this topic. http://thread.gmane.org/gmane.comp.db.postgresql.general/121450 In there I argue for the empty array interpretation and Tom goes back and forth a few times. I'm not sure where that thread ended though. I had forgotten that discussion. It looks like we trailed off without any real consensus: there was about equal sentiment for an array with zero elements and an array with one empty-string element. We ended up leaving it alone because (a) that wouldn't break anything and (b) you could use COALESCE() to substitute whichever behavior your application needed for the case. So maybe we need to revisit the issue. Pavel was claiming that switching to a zero-element array result was a no-brainer, but evidently it isn't so. Is anybody still excited about the alternatives? 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] MERGE command for inheritance
On Wed, 2010-08-11 at 22:09 +0800, Boxuan Zhai wrote: One more thing I want to point out is that, the INSERT is also an inheritable action in MERGE. For a plain INSERT command, all the inserted tuples are put in the target table ONLY. It is easy to understand. We don't want to duplicate all the new tuples in all children tables. However, in MERGE command, an INSERT action is activated by the tuples fitting its matching conditions. The main plan of a MERGE command will scan all the tuples in target relation and its children tables. If one tuple in a child table meets the requirements of INSERT actions, the insertion should be taken on the child table itself rather than its ancestor. It seems clear that your work in this area will interfere with the work on partitioning and insert routing. We've seen it time and time again that big projects that aim to deliver towards end of a release cycle interfere with dev of other projects and leave loose ends from unforeseen interactions. There's no need for that. PS: Since I have taken this project, I will do my best to make it perfect. I will keep working on MERGE until it is really finished, even after the gSoC. (unless you guys has other plans). You can make things perfect in more than one phase, as indeed you already are: concurrent locking has already been placed out of scope of your current work. I don't question your good intentions to both complete this work and do it on time. I question the need for us to rely on that. I also question the ability of the community to deliver super-size features in a single release. Breaking things down is always the best way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] postgres 9.0 crash when bringing up hot standby
Ok..in response to the questions from Heikki, 1. Yes, contrib/dblink does work. Here's the output from the command used to make dblink: postgres:thimar /usr/bin/gmake -C contrib/dblink install gmake: Entering directory `/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink' /bin/sh ../../config/install-sh -c -d '/dinabkp/faouzis/local2/pgsql/lib' /bin/sh ../../config/install-sh -c -d '/dinabkp/faouzis/local2/pgsql/share/contrib' /bin/sh ../../config/install-sh -c -m 755 dblink.so '/dinabkp/faouzis/local2/pgsql/lib/dblink.so' /bin/sh ../../config/install-sh -c -m 644 ./uninstall_dblink.sql '/dinabkp/faouzis/local2/pgsql/share/contrib' /bin/sh ../../config/install-sh -c -m 644 dblink.sql '/dinabkp/faouzis/local2/pgsql/share/contrib' gmake: Leaving directory `/dinabkp/faouzis/postgresql-9.0beta1/contrib/dblink' 2. I don't have records of the build logs for the regular postgres executables (which contains the libpqwalreceiver) but can do a new compile/make if that is required. But they were compiled and installed using the regular make files supplied along with the postgres source code. The following flags were added during the compilation: --without-readline --without-zlib--enable-debug --enable-cassert --enable-thread-safety Thanks. Alanoly. -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, August 11, 2010 10:13 AM To: Heikki Linnakangas Cc: Alanoly Andrews; pgsql-ad...@postgresql.org; PostgreSQL-development Subject: Re: [HACKERS] [ADMIN] postgres 9.0 crash when bringing up hot standby On Fri, Aug 6, 2010 at 3:53 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So, loading libpqwalreceiver library crashes. It looks like it might be pthread-related. Perhaps something wrong with our makefiles, causing libpqwalreceiver to be built with wrong flags? Does contrib/dblink work? If you look at the build log, what is the command line used to compile libpqwalreceiver, and what is the command line used to build other libraries, like contrib/dblink? I haven't seen any response to this from the OP, but it seems worrisome. Has anyone else tested a Hot Standby configuraration - successfully or otherwise - on AIX? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen. -- 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] assertions and constraint triggers
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote: Thinking about SQL assertions (check constraints that are independent of one particular table), do you think it would be reasonable to implement those on top of constraint triggers? On creation you'd hook up a trigger to each of the affected tables. And the trigger function runs the respective check expression. Conceptually, this doesn't seem to be very far away from foreign key constraints after all. I thought the point of ASSERTIONs was that you could write a thing such as: CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4); Enforcing that kind of constraints without true serializability seems impractical. Enforcing that kind of constraint seems impractical with or without serializability. You need some optimization method that avoids the need to do full-table scans after every update, or it's not going to be useful for any real-world situation. Without a scheme that can do incremental checking for some useful class of assertion expressions, this isn't going to go far. 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] string_to_array with an empty input string
On Wed, Aug 11, 2010 at 10:32 AM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: Ideally you really want string_to_array(array_to_string(x, ':'),':') to return x. There's no safe return value to pick for the cases where x=[''] and x=[] that will make this work. It's easy to see that string_to_array/array_to_string are *not* usable as general-purpose serialize/deserialize operations, so sweating over corner cases like this one seems a bit pointless. The design center for 'em seems to be array elements that are numbers, so there's no issue with empty strings and no great difficulty in picking delimiters and null representations that can't appear in the data. I think they're essentially worthless for arrays of text. array_to_string() is quite useful for arrays of text; I use it to generate human-readable output, by setting the delimiter to ', '. Whether string_to_array() is useful is another matter. It probably is in some cases, but putting parsing logic into the database layer tends to be a bit klunky, unless you know from context that you needn't worry about the error cases. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] MERGE command for inheritance
On 11/08/10 17:45, Simon Riggs wrote: It seems clear that your work in this area will interfere with the work on partitioning and insert routing. Nothing concrete has come out of that work yet. And we should have MERGE work with inherited tables, regardless of any future work that may happen with partitioning. We've seen it time and time again that big projects that aim to deliver towards end of a release cycle interfere with dev of other projects and leave loose ends from unforeseen interactions. There's no need for that. I don't understand what you're saying, we're not in the end of a release cycle. -- 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] Bug / shortcoming in has_*_privilege
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 11, 2010 at 8:51 AM, Simon Riggs si...@2ndquadrant.com wrote: It clearly rates higher in importance than most of the things on the open items list of late... First, I don't think that's true. WALreceiver crashing on AIX, the backup procedure in the manual possibly being wrong, and the documentation failing to be installed sometimes all seem like they are clearly more serious issues than this. I am sort of wondering why no one is working on those issues; apparently, nobody other than me minds if it takes another three months to get 9.0 out the door. Quite. At this point, the only things that should be on the open items list are things that would be release stoppers, which is to say things that are regressions from prior releases or design errors that we don't want to ever get into a release. This item is not a bug but a feature omission, and one of rather long standing. Frankly, I think the ExplainOnePlan bit is more important, too, although I'm starting to think we should fix that for 9.1 rather than 9.0. See above. We are not changing that in 9.0 anymore. 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] Regression tests versus the buildfarm environment
Vik Reykja vikrey...@gmail.com writes: We just put in the possibility to name the client connections. Would it be interesting to be able to name the server installation itself? Wouldn't do anything for this problem --- it would just introduce something else the buildfarm would have to worry about uniqueness of. 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] MERGE command for inheritance
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote: I concur that Boxuan's suggested difficult approach seems like the right one. Right, but you've completely ignored my proposal: lets do this in two pieces. Get what we have now ready to commit, then add support for partitioning later, as a second project. Do we really think this is anywhere near committable now? If it's committable in every other respect, I could see just having it throw a NOT_IMPLEMENTED error when the target table has children. I thought we were still a very long way from that though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MERGE Specification
On fre, 2010-08-06 at 08:12 +0100, Simon Riggs wrote: Given that Peter is now attending SQL Standards meetings, I would suggest we leave out my suggestion above, for now. We have time to raise this at standards meetings and influence the outcome and then follow later. I'm not actually attending any (further) meetings, because no one has agreed to fund it yet. -- 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] MERGE command for inheritance
On Wed, 2010-08-11 at 11:03 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2010-08-11 at 13:25 +0300, Heikki Linnakangas wrote: I concur that Boxuan's suggested difficult approach seems like the right one. Right, but you've completely ignored my proposal: lets do this in two pieces. Get what we have now ready to commit, then add support for partitioning later, as a second project. Do we really think this is anywhere near committable now? If it's committable in every other respect, I could see just having it throw a NOT_IMPLEMENTED error when the target table has children. I thought we were still a very long way from that though. Well, if we go off chasing this particular goose then we will set ourselves back at least one commitfest. I'd rather work towards having a fully committable patch without inheritance sooner than an even bigger patch arriving later in the cycle, which could make things difficult for us. I cite recent big patch experience as admissible evidence, m'lord. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] micro bucket sort ...
Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010: same with limit ... test=# explain analyze select * from t_test order by x, y limit 20; But if you put the limit in a subquery which is ordered by the known-indexed condition, it is very fast: alvherre=# explain analyze select * from (select * from t_test order by x limit 20) f order by x, y; QUERY PLAN ─ Sort (cost=1.24..1.29 rows=20 width=8) (actual time=0.252..0.296 rows=20 loops=1) Sort Key: t_test.x, t_test.y Sort Method: quicksort Memory: 26kB - Limit (cost=0.00..0.61 rows=20 width=8) (actual time=0.051..0.181 rows=20 loops=1) - Index Scan using idx_a on t_test (cost=0.00..30408.36 rows=100 width=8) (actual time=0.046..0.098 rows=20 loops=1) Total runtime: 0.425 ms (6 filas) I guess it boils down to being able to sort a smaller result set. -- Á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] MERGE command for inheritance
On Wed, Aug 11, 2010 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: Well, if we go off chasing this particular goose then we will set ourselves back at least one commitfest. I'd rather work towards having a fully committable patch without inheritance sooner than an even bigger patch arriving later in the cycle, which could make things difficult for us. Let's give Boxuan a little time to work and see what he comes up with. Maybe it won't be too bad. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] micro bucket sort ...
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Hans-Jürgen Schönig's message of mié ago 11 08:21:10 -0400 2010: test=# explain analyze select * from t_test order by x, y limit 20; But if you put the limit in a subquery which is ordered by the known-indexed condition, it is very fast: alvherre=# explain analyze select * from (select * from t_test order by x limit 20) f order by x, y; That's not guaranteed to give you the right 20 rows, though. Consider the case where there are 20 rows having the minimal x value. 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] Regression tests versus the buildfarm environment
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 11, 2010 at 10:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Or we could do something like port = 0xC000 ^ (DEF_PGPORT 0x7FFF); which is absolutely guaranteed not to conflict with DEF_PGPORT, at the cost of possibly shifting into the 32K-48K port number range if you had set DEF_PGPORT above 48K. I like XOR a lot better than OR. Yeah, on reflection that seems better. Barring objection I will see about making this happen in all live branches. 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] Regression tests versus the buildfarm environment
On 08/11/2010 10:23 AM, Robert Haas wrote: Or we could do something like port = 0xC000 ^ (DEF_PGPORT 0x7FFF); which is absolutely guaranteed not to conflict with DEF_PGPORT, at the cost of possibly shifting into the 32K-48K port number range if you had set DEF_PGPORT above 48K. I like XOR a lot better than OR. For years we told people to make sure they picked 4 digit port numbers for the buildfarm, and while I removed that note recently it can be put back. So I don't think there's much danger - let's got with XOR. 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] MERGE command for inheritance
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 11, 2010 at 11:23 AM, Simon Riggs si...@2ndquadrant.com wrote: Well, if we go off chasing this particular goose then we will set ourselves back at least one commitfest. I'd rather work towards having a fully committable patch without inheritance sooner than an even bigger patch arriving later in the cycle, which could make things difficult for us. Let's give Boxuan a little time to work and see what he comes up with. Maybe it won't be too bad. I tend to agree with Simon's argument here: if the patch is near committable then it'd be better to get it committed and work on correcting this omission afterwards. I'm not sure about the truth of the if part, though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regression tests versus the buildfarm environment
On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote: One of us is missing something. I didn't say to run the checks using the configured port. I had in mind something like: port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) 0x3FFF); Oh, I see, modify the DEF_PGPORT don't just use it as-is. OK, except that I think something like the above is still pretty risky for the buildfarm, because you would still have conflicts for assorted combinations of version numbers and branch_port settings. How about just this: port = 0xC000 | (DEF_PGPORT 0x3FFF); The version number was put in there intentionally, for developers who work on multiple branches at once. That's the whole reason this code exists. Please don't remove it. -- 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] Regression tests versus the buildfarm environment
On ons, 2010-08-11 at 09:55 -0400, Tom Lane wrote: BTW, I don't know why anyone would think that a random number would offer any advantage here. I'd use the postmaster PID, which is guaranteed to be unique across the space that you're worried about. In fact, you could implement this off the existing postmaster.pid, no need for any new file. What's lacking is the pg_ping protocol. Why not just compare pg_backend_pid() with postmaster.pid? -- 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] assertions and constraint triggers
On ons, 2010-08-11 at 13:23 +0300, Marko Tiikkaja wrote: But you'd have to somehow make the constraints to be checked with true serializability, and that part of the original suggestion seemed to be completely missing. Not sure how hard that would be though. I don't think somehow running the constraint checks at a different transaction isolation level than the rest of the transaction is sensible. I imagine the solution would look similar to how foreign keys do it: take a lock on the rows that are required for constraint satisfaction. For general assertions, this would require predicate locking. But also notice that for the (SELECT count(*) FROM tbl) = N case, this is the same as a table lock. I don't think there is any magic around it. -- 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] Regression tests versus the buildfarm environment
Peter Eisentraut pete...@gmx.net writes: On ons, 2010-08-11 at 09:55 -0400, Tom Lane wrote: BTW, I don't know why anyone would think that a random number would offer any advantage here. I'd use the postmaster PID, which is guaranteed to be unique across the space that you're worried about. In fact, you could implement this off the existing postmaster.pid, no need for any new file. What's lacking is the pg_ping protocol. Why not just compare pg_backend_pid() with postmaster.pid? How's that help? pg_backend_pid isn't going to return the postmaster's PID ... maybe we could add a new function that does return the postmaster's PID, though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Inconsistent ::bit(N) and get_bit()?
2010/8/11 Boszormenyi Zoltan z...@cybertec.at: Shouldn't it at least be documented in more depth? Say, get_bit(, N) provides the Nth bit (0-based) counting from the leftmost bit? I would certainly appreciate a warning spelled out about this so if you convert a number to bitstring of length N and you want the Mth bit (according to any programming language) then you need to use get_bit(..., N-1-M). The fact that bit-strings subscript from the left rather than from the right seems pretty odd to me, but it is documented. I wouldn't object to adding a note to somewhere around here, if we can think of a suitable way to word it: http://www.postgresql.org/docs/9.0/static/functions-bitstring.html -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] assertions and constraint triggers
On ons, 2010-08-11 at 10:47 -0400, Tom Lane wrote: I thought the point of ASSERTIONs was that you could write a thing such as: CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4); Enforcing that kind of constraints without true serializability seems impractical. Enforcing that kind of constraint seems impractical with or without serializability. You need some optimization method that avoids the need to do full-table scans after every update, or it's not going to be useful for any real-world situation. Without a scheme that can do incremental checking for some useful class of assertion expressions, this isn't going to go far. I'm not sure how great a use case there is for an assertion of the kind this table must contain at least 30 million rows. But I think there are many uses cases for checks like that on small and rarely changing tables. -- 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] Regression tests versus the buildfarm environment
Peter Eisentraut pete...@gmx.net writes: On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote: How about just this: port = 0xC000 | (DEF_PGPORT 0x3FFF); The version number was put in there intentionally, for developers who work on multiple branches at once. That's the whole reason this code exists. Please don't remove it. I work on multiple branches all day every day. This wouldn't hinder me in the slightest, because I use a different DEF_PGPORT for each branch. If you don't, it's hard to see how you manage to deal with multiple branches on one machine ... do you not ever actually install them? Even if you don't, changing this would only mean that you couldn't safely run make check concurrently in multiple branches. 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] Regression tests versus the buildfarm environment
Peter Eisentraut pete...@gmx.net wrote: On ons, 2010-08-11 at 09:55 -0400, Tom Lane wrote: BTW, I don't know why anyone would think that a random number would offer any advantage here. I'd use the postmaster PID, which is guaranteed to be unique across the space that you're worried about. In fact, you could implement this off the existing postmaster.pid, no need for any new file. What's lacking is the pg_ping protocol. Why not just compare pg_backend_pid() with postmaster.pid? See the prior discussion in the archives. We started with that and found problems, to which Tom suggested a random number as the best solution. Let's at least start any further discussion informed by what's gone before; if there was a flaw in the reasoning, please point that out. -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] Regression tests versus the buildfarm environment
On 08/11/2010 11:42 AM, Peter Eisentraut wrote: On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote: One of us is missing something. I didn't say to run the checks using the configured port. I had in mind something like: port = 0xC000 | ((PG_VERSION_NUM + DEF_PGPORT) 0x3FFF); Oh, I see, modify the DEF_PGPORT don't just use it as-is. OK, except that I think something like the above is still pretty risky for the buildfarm, because you would still have conflicts for assorted combinations of version numbers and branch_port settings. How about just this: port = 0xC000 | (DEF_PGPORT 0x3FFF); The version number was put in there intentionally, for developers who work on multiple branches at once. That's the whole reason this code exists. Please don't remove it. Do they run make check by hand simultaneously on multiple branches? That's the only way you'd get a collision here, I think. 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] Regression tests versus the buildfarm environment
On ons, 2010-08-11 at 11:48 -0400, Tom Lane wrote: How's that help? pg_backend_pid isn't going to return the postmaster's PID ... maybe we could add a new function that does return the postmaster's PID, though. Hmm, is there a portable way to find the parent PID of some other process, given its PID? -- 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] Regression tests versus the buildfarm environment
Kevin Grittner kevin.gritt...@wicourts.gov writes: Peter Eisentraut pete...@gmx.net wrote: Why not just compare pg_backend_pid() with postmaster.pid? See the prior discussion in the archives. We started with that and found problems, to which Tom suggested a random number as the best solution. I think Peter's idea is a bit different though. The previous concern was about what information would be okay to expose in a pg_ping response packet, which presumably would be available to anybody who could open a connection to the postmaster port. What he's suggesting is to crosscheck against data that is available after a successful login. That eliminates the security complaint. It strikes me we could do something without adding a postmaster-PID SQL function, too. What about doing SHOW DATA_DIRECTORY and comparing that to what pg_regress expects? 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] string_to_array with an empty input string
On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:
I had forgotten that discussion. It looks like we trailed off without
any real consensus: there was about equal sentiment for an array with
zero elements and an array with one empty-string element. We ended
up leaving it alone because (a) that wouldn't break anything and (b)
you could use COALESCE() to substitute whichever behavior your
application needed for the case.
So maybe we need to revisit the issue. Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so. Is anybody still excited about the alternatives?
% perl -E 'say q{}, join(,, ), q{}'
% ruby -e 'puts %q{} + [].join(,) + %q{}'
% python -c 'print \ + ,.join([]) + \'
I believe those are all , rather than '' + undef + ''.
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] review: psql: edit function, show function commands patch
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag seems grossly overdesigned. It would be clearer, shorter, and faster if you just had a strncmp test for AS $function there. As far as I can see, the only purpose of that code is to support the desire to have \sf+ display rather than a line number for the lines that FOLLOW the function body. But I'm wondering if we should just forget about that and let the numbering run continuously from the first AS $function line to end of file. That would get rid of a bunch of rather grotty code in the \sf patch, also. Oh? Considering that in the standard pg_get_functiondef output, the ending $function$ delimiter is always on the very last line, that sounds pretty useless. +1 for just numbering forward from the start line. BTW, the last I looked, \sf+ was using what I thought to be a quite ugly and poorly-considered formatting for the line number. I would suggest eight blanks for a header line and %-7d as the prefix format for a numbered line. The reason for making sure the prefix is 8 columns rather than some other width is to not mess up tab-based formatting of the function body. I would also prefer a lot more visual separation between the line number and the code than %4d will offer; and as for the stars, they're just useless and distracting. 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] string_to_array with an empty input string
David E. Wheeler da...@kineticode.com writes:
On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:
So maybe we need to revisit the issue. Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so. Is anybody still excited about the alternatives?
% perl -E 'say q{}, join(,, ), q{}'
% ruby -e 'puts %q{} + [].join(,) + %q{}'
% python -c 'print \ + ,.join([]) + \'
I believe those are all , rather than '' + undef + ''.
If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever. Returning a single empty string will
make those cases fail. So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.
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] review: psql: edit function, show function commands patch
On Wed, Aug 11, 2010 at 12:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, at least in the usage in that loop, get_functiondef_dollarquote_tag seems grossly overdesigned. It would be clearer, shorter, and faster if you just had a strncmp test for AS $function there. As far as I can see, the only purpose of that code is to support the desire to have \sf+ display rather than a line number for the lines that FOLLOW the function body. But I'm wondering if we should just forget about that and let the numbering run continuously from the first AS $function line to end of file. That would get rid of a bunch of rather grotty code in the \sf patch, also. Oh? Considering that in the standard pg_get_functiondef output, the ending $function$ delimiter is always on the very last line, that sounds pretty useless. +1 for just numbering forward from the start line. OK. BTW, the last I looked, \sf+ was using what I thought to be a quite ugly and poorly-considered formatting for the line number. I would suggest eight blanks for a header line and %-7d as the prefix format for a numbered line. The reason for making sure the prefix is 8 columns rather than some other width is to not mess up tab-based formatting of the function body. I would also prefer a lot more visual separation between the line number and the code than %4d will offer; and as for the stars, they're just useless and distracting. I don't have a strong preference, but that seems reasonable. I suggest that we punt the \sf portion of this patch back for rework for the next CommitFest, and focus on getting the \e and \ef changes committed. I think the \sf code can be a lot simpler if we get rid of the code that's intended to recognize the ending delimeter. Another thought is that we might want to add a comment to pg_get_functiondef() noting that anyone changing the output format should be careful not to break the line-number-finding form of \ef in the process. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] string_to_array with an empty input string
On Wed, Aug 11, 2010 at 12:36 PM, Tom Lane t...@sss.pgh.pa.us wrote:
David E. Wheeler da...@kineticode.com writes:
On Aug 11, 2010, at 7:41 AM, Tom Lane wrote:
So maybe we need to revisit the issue. Pavel was claiming that
switching to a zero-element array result was a no-brainer, but evidently
it isn't so. Is anybody still excited about the alternatives?
% perl -E 'say q{}, join(,, ), q{}'
% ruby -e 'puts %q{} + [].join(,) + %q{}'
% python -c 'print \ + ,.join([]) + \'
I believe those are all , rather than '' + undef + ''.
If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever. Returning a single empty string will
make those cases fail. So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.
Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do. I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want. That's a bit more difficult
if you use return any other value. But I think your argument that an
empty array is better than a one-element array containing an empty
string is very much correct, as between those options.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] review: xml_is_well_formed
On Mon, Aug 9, 2010 at 10:41 AM, Robert Haas robertmh...@gmail.com wrote: On Mon, Aug 9, 2010 at 10:20 AM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-08-07 at 16:47 +0100, Mike Fowler wrote: To be honest I'm happiest with returning a boolean, even if there is some confusion over content only being valid. Though changing the return value to DOCUMENT/CONTENT/NULL makes things a touch more explicit, the same results can be achieved by simply running: SELECT data::xml FROM mixed WHERE xml_is_well_formed(data) AND data::xml IS DOCUMENT; Note that this wouldn't necessarily work because it is not guaranteed that the well-formedness test is executed before the cast to xml. SQL doesn't short-circuit left to right. (A CASE expression could work.) There's also the fact that it would probably end up parsing the data twice. Given xmloption, I'm inclined to think Tom has it right: provided xml_is_well_formed() that follows xmloption, plus a specific version for each of content and document. Another reasonable option here would be to forget about having xml_is_well_formed() per se and ONLY offer xml_is_well_formed_content() and xml_is_well_formed_document(). As a project management note, this CommitFest is over in 4 days, so unless we have a new version of this patch real soon now we need to defer it to the September 15th CommitFest (of course not precluding the possibility that someone will pick it up and commit it sooner, but we're not going to postpone 9.1alpha1 for this patch). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] MERGE command for inheritance
Tom Lane wrote: Do we really think this is anywhere near committable now? There's a relatively objective standard for the first thing needed for commit--does it work?--in the form of the regression tests Simon put together before development. I just tried the latest merge_v102.patch (regression diff attached) to see how that's going. There are still a couple of errors in there. It looks to me like the error handling and related DO NOTHING support are the next pair of things that patch needs work on. I'd rather see that sorted out than to march onward to inheritance without the fundamentals even nailed down yet. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us *** /home/postgres/pgwork/repo/git/postgresql/src/test/regress/expected/merge.out 2010-08-11 12:23:50.0 -0400 --- /home/postgres/pgwork/repo/git/postgresql/src/test/regress/results/merge.out 2010-08-11 12:33:27.0 -0400 *** *** 44,57 WHEN MATCHED THEN UPDATE SET balance = t.balance + s.balance ; ! SELECT * FROM target; ! id | balance ! +- ! 1 | 10 ! 2 | 25 ! 3 | 50 ! (3 rows) ! ROLLBACK; -- do a simple equivalent of an INSERT SELECT BEGIN; --- 44,50 WHEN MATCHED THEN UPDATE SET balance = t.balance + s.balance ; ! NOTICE: one tuple is ERROR ROLLBACK; -- do a simple equivalent of an INSERT SELECT BEGIN; *** *** 61,66 --- 54,61 WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.balance) ; + NOTICE: one tuple is ERROR + NOTICE: one tuple is ERROR SELECT * FROM target; id | balance +- *** *** 102,107 --- 97,103 WHEN MATCHED THEN DELETE ; + NOTICE: one tuple is ERROR SELECT * FROM target; id | balance +- *** *** 165,176 ERROR: multiple actions on single target row ROLLBACK; ! -- This next SQL statement -- fails according to standard -- suceeds in PostgreSQL implementation by simply ignoring the second -- matching row since it activates no WHEN clause BEGIN; MERGE into target t USING (select * from source) AS s ON t.id = s.id --- 161,175 ERROR: multiple actions on single target row ROLLBACK; ! ERROR: syntax error at or near ERROR ! LINE 1: ERROR: multiple actions on single target row ! ^ -- This next SQL statement -- fails according to standard -- suceeds in PostgreSQL implementation by simply ignoring the second -- matching row since it activates no WHEN clause BEGIN; + ERROR: current transaction is aborted, commands ignored until end of transaction block MERGE into target t USING (select * from source) AS s ON t.id = s.id *** *** 179,184 --- 178,184 WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.balance) ; + ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; -- Now lets prepare the test data to generate 2 non-matching rows DELETE FROM source WHERE id = 3 AND balance = 5; *** *** 188,195 +- 2 | 5 3 | 20 - 4 | 5 4 | 40 (4 rows) -- This next SQL statement --- 188,195 +- 2 | 5 3 | 20 4 | 40 + 4 | 5 (4 rows) -- This next SQL statement *** *** 203,216 WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.balance) ; SELECT * FROM target; id | balance +- 1 | 10 2 | 20 3 | 30 - 4 | 5 4 | 40 (5 rows) ROLLBACK; --- 203,218 WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.balance) ; + NOTICE: one tuple is ERROR + NOTICE: one tuple is ERROR SELECT * FROM target; id | balance +- 1 | 10 2 | 20 3 | 30 4 | 40 + 4 | 5 (5 rows) ROLLBACK; *** *** 225,239 WHEN NOT MATCHED AND s.balance 100 THEN INSERT VALUES (s.id, s.balance) ; SELECT * FROM target; id | balance +- 1 | 10 2 | 20 3 | 30 ! | ! | ! (5 rows) ROLLBACK; -- This next SQL statement suceeds, but does nothing since there are --- 227,243 WHEN NOT MATCHED AND s.balance 100 THEN INSERT VALUES (s.id, s.balance) ; + NOTICE: one tuple is ERROR + NOTICE: one tuple is ERROR + NOTICE: one tuple is ERROR + NOTICE: one tuple is ERROR SELECT * FROM target; id | balance +- 1 | 10 2 | 20 3 | 30 ! (3 rows) ROLLBACK; -- This next SQL statement suceeds, but does nothing since there are *** *** 249,262 WHEN NOT MATCHED DO NOTHING ; SELECT * FROM target; ! id | balance ! +- ! 1 | 10 ! 2 | 20 ! 3 | 30
Re: [HACKERS] string_to_array with an empty input string
Yeah, I think David's examples are talking about the behavior of join, but we're trying to decide what split should do. I think the main argument for making it return NULL is that you can then fairly easily use COALESCE() to get whatever you want. That's a bit more difficult if you use return any other value. I think that there's a need for additional built-in array functions, including one to succinctly test if an array has no elements. Iterating through an array with plpgsql, for example, is more clunky than it should be. -- Regards, Peter Geoghegan -- 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] string_to_array with an empty input string
On Aug 11, 2010, at 9:36 AM, Tom Lane wrote:
I believe those are all , rather than '' + undef + ''.
If you believe my previous opinion that the design center for these
functions is arrays of numbers, then a zero-entry text[] array is what
you want, because you can successfully cast it to a zero-entry array of
integers or floats or whatever. Returning a single empty string will
make those cases fail. So at the moment I'm on the side of the fence
that says zero-entry array is the best answer.
Seems to be precedent for that:
% perl -E 'say scalar @{[ split ,, ]}'
0
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] string_to_array with an empty input string
On Aug 11, 2010, at 9:40 AM, Robert Haas wrote: Yeah, I think David's examples are talking about the behavior of join, but we're trying to decide what split should do. Right, sorry about that. I think the main argument for making it return NULL is that you can then fairly easily use COALESCE() to get whatever you want. That's a bit more difficult if you use return any other value. But I think your argument that an empty array is better than a one-element array containing an empty string is very much correct, as between those options. I prefer an empty array. 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] string_to_array with an empty input string
On Wed, Aug 11, 2010 at 1:32 PM, Peter Geoghegan
peter.geoghega...@gmail.com wrote:
Yeah, I think David's examples are talking about the behavior of join,
but we're trying to decide what split should do. I think the main
argument for making it return NULL is that you can then fairly easily
use COALESCE() to get whatever you want. That's a bit more difficult
if you use return any other value.
I think that there's a need for additional built-in array functions,
including one to succinctly test if an array has no elements.
What do you propose? I think the easiest ways to do it right now are:
array_length(arr, 1) is null
or just using an equality test, like this:
arr = '{}'::int[]
Iterating through an array with plpgsql, for example, is more clunky
than it should be.
Really?
FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP
I mean, doing everything is sort of clunky in PL/pgsql, but this
doesn't seem particularly bad as PL/pgsql idioms go.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] string_to_array with an empty input string
On Aug 11, 2010, at 10:53 AM, Robert Haas wrote: Iterating through an array with plpgsql, for example, is more clunky than it should be. Really? FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP I mean, doing everything is sort of clunky in PL/pgsql, but this doesn't seem particularly bad as PL/pgsql idioms go. That tends to over-flatten if you have nested arrays and just want to iterate over the top level. In that case you must use generate_subscripts(). 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] string_to_array with an empty input string
On 08/11/2010 01:54 PM, David E. Wheeler wrote: On Aug 11, 2010, at 10:53 AM, Robert Haas wrote: Iterating through an array with plpgsql, for example, is more clunky than it should be. Really? FOR var IN SELECT UNNEST(arr) LOOP ... END LOOP I mean, doing everything is sort of clunky in PL/pgsql, but this doesn't seem particularly bad as PL/pgsql idioms go. That tends to over-flatten if you have nested arrays and just want to iterate over the top level. In that case you must use generate_subscripts(). for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ... works well 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] string_to_array with an empty input string
On Aug 11, 2010, at 10:58 AM, Andrew Dunstan wrote: for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ... works well for i in select array_subscripts(myarray, 1) loop ... 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] Regression tests versus the buildfarm environment
On ons, 2010-08-11 at 11:53 -0400, Andrew Dunstan wrote: The version number was put in there intentionally, for developers who work on multiple branches at once. That's the whole reason this code exists. Please don't remove it. Do they run make check by hand simultaneously on multiple branches? That's the only way you'd get a collision here, I think. That is exactly what I'm talking about. -- 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] MERGE command for inheritance
Excerpts from Heikki Linnakangas's message of mié ago 11 10:52:24 -0400 2010: On 11/08/10 17:45, Simon Riggs wrote: We've seen it time and time again that big projects that aim to deliver towards end of a release cycle interfere with dev of other projects and leave loose ends from unforeseen interactions. There's no need for that. I don't understand what you're saying, we're not in the end of a release cycle. This patch still needs a lot of work before it's anywhere close to committable. I agree with Simon that it is preferrable to clean it up to make it committable *without* the burden of extra features. If Boxuan continues to add more features, it will be end-of-release before it is possible to think about committing it. It seems better to have merge-no-inheritance in 9.1 than nothing. If we can get the inheritance case working for 9.1, that's even better, but I don't think it needs to be a hard requirement. -- Á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] Regression tests versus the buildfarm environment
On ons, 2010-08-11 at 11:47 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On ons, 2010-08-11 at 10:15 -0400, Tom Lane wrote: How about just this: port = 0xC000 | (DEF_PGPORT 0x3FFF); The version number was put in there intentionally, for developers who work on multiple branches at once. That's the whole reason this code exists. Please don't remove it. I work on multiple branches all day every day. This wouldn't hinder me in the slightest, because I use a different DEF_PGPORT for each branch. If you don't, it's hard to see how you manage to deal with multiple branches on one machine ... do you not ever actually install them? No, not nearly as much as I run make check. Even if you don't, changing this would only mean that you couldn't safely run make check concurrently in multiple branches. That's exactly the point. The original discussion is here: http://archives.postgresql.org/message-id/491d9935.9010...@gmx.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 16/32/48/64 bytes integers
Hello, I wishing to create real big numbers, but I'm facing some difficulties. Is possible to setup an integer type of more than 8 bytes (i.e. 16/32/48/64 bytes)? Can I setup a value as large as I want? How I should acess them using PG_RETURN_xxx and PG_GETARG_xxx macros? Thanks in advance, Daniel Oliveira
Re: [HACKERS] 16/32/48/64 bytes integers
On 11/08/10 21:19, Daniel Oliveira wrote: I wishing to create real big numbers, but I'm facing some difficulties. Is possible to setup an integer type of more than 8 bytes (i.e. 16/32/48/64 bytes)? No. Not unless you write your own datatype. Use numeric, it scales up to ridiculously large numbers. -- 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] string_to_array with an empty input string
On 08/11/2010 01:59 PM, David E. Wheeler wrote: On Aug 11, 2010, at 10:58 AM, Andrew Dunstan wrote: for i in array_lower(myarray,1) .. array_upper(myarray,1) loop ... works well for i in select array_subscripts(myarray, 1) loop ... That's not a built-in function AFAIK. 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
