Re: [HACKERS] Bug / shortcoming in has_*_privilege

[Robert Haas]

On Wed, Aug 11, 2010 at 8:51 AM, Simon Riggs  wrote:
> On Wed, 2010-08-11 at 06:48 -0400, Robert Haas wrote:
>> On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs  wrote:
>> > On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote:
>> >> Robert Haas  writes:
>> >> > On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby  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] review: psql: edit function, show function commands patch

[Robert Haas]

On Tue, Aug 10, 2010 at 11:58 PM, Tom Lane  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] "micro bucket sort" ...

[Simon Riggs]

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] MERGE command for inheritance

[Heikki Linnakangas]

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] Bug / shortcoming in has_*_privilege

[Simon Riggs]

On Wed, 2010-08-11 at 06:48 -0400, Robert Haas wrote:
> On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs  wrote:
> > On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote:
> >> Robert Haas  writes:
> >> > On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby  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

[Boxuan Zhai]

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

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs  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

[HACKERS] "micro bucket sort" ...

[Hans-Jürgen Schönig]

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

[HACKERS] pgstat_report_waiting() in hot standby

[Fujii Masao]

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

Re: [HACKERS] Develop item from TODO list

[Viktor Valy]

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 

> Tom Lane wrote:
> > Viktor Valy  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  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
>
>  + It's impossible for everything to be true. +
>

Re: [HACKERS] MERGE command for inheritance

[Simon Riggs]

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] Regression tests versus the buildfarm environment

[Andrew Dunstan]

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=polecat&dt=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=colugos&dt=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] Bug / shortcoming in has_*_privilege

[Robert Haas]

On Wed, Aug 11, 2010 at 3:57 AM, Simon Riggs  wrote:
> On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote:
>> Robert Haas  writes:
>> > On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby  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] MERGE command for inheritance

[Heikki Linnakangas]

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] assertions and constraint triggers

[Marko Tiikkaja]

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] assertions and constraint triggers

[Peter Eisentraut]

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] MERGE Specification

[Peter Eisentraut]

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] string_to_array with an empty input string

[Pavel Stehule]

2010/8/11 Greg Stark :
> On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
>  wrote:
>> On 10 August 2010 19:48, David E. Wheeler  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 command for inheritance

[Simon Riggs]

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] trace_recovery_messages

[Fujii Masao]

On Wed, Aug 11, 2010 at 5:26 PM, Simon Riggs  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

[Heikki Linnakangas]

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

[Simon Riggs]

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] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)

[Simon Riggs]

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] MERGE Specification

[Boxuan Zhai]

On Wed, Aug 11, 2010 at 12:18 PM, Boxuan Zhai  wrote:

>
>
>  On Wed, Aug 11, 2010 at 12:14 PM, Greg Smith wrote:
>
>> 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 
>>
>>
>


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] assertions and constraint triggers

[Nicolas Barbier]

2010/8/11 Marko Tiikkaja :

> 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] assertions and constraint triggers

[Simon Riggs]

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] Regression tests versus the buildfarm environment

[Vik Reykja]

On Wed, Aug 11, 2010 at 06:42, Tom Lane  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] Bug / shortcoming in has_*_privilege

[Simon Riggs]

On Thu, 2010-06-10 at 23:18 -0400, Tom Lane wrote:
> Robert Haas  writes:
> > On Thu, Jun 10, 2010 at 5:54 PM, Jim Nasby  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] assertions and constraint triggers

[Marko Tiikkaja]

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] Cost of AtEOXact_Buffers in --enable-cassert

[Andres Freund]

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] string_to_array with an empty input string

[Greg Stark]

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] string_to_array with an empty input string

[Greg Stark]

On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
 wrote:
> On 10 August 2010 19:48, David E. Wheeler  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