Re: [HACKERS] How can we tell how far behind the standby is?

[Rob Wultsch]

On Fri, Nov 5, 2010 at 5:39 PM, Robert Haas  wrote:
> On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus  wrote:
>> I'm continuing in my efforts now to document how to deploy and manage
>> replication on our wiki.  One of the things a DBA needs to do is to use
>> pg_current_xlog_location() (and related functions) to check how far
>> behind the master the standby is.
>>
>> However, there's some serious problems with that:
>>
>> (1) comparing these numbers is quite mathematically complex -- and, for
>> that matter, undocumented.
>>
>> (2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
>> xlog positions, quite a large one if it happens near the beginning of a
>> file.  There is no way for any monitoring on the standby to tell the
>> difference between a gap created by forced rotation as opposed to being
>> most of a file behind, until the next record shows up.  Hello, nagios
>> false alerts!
>>
>> (3) There is no easy way to relate a difference in log positions to an
>> amount of time.
>>
>> I'll work on some tools to make this a bit more palatable, but I
>> disagree with earlier assertions that we have the replication monitoring
>> "done".  There's still a *lot* of work to do.
>
> I've heard the same complaint, and I agree with your concerns.


"All this has happened before, and all of it will happen again."

At this point pg has the equivalent of MySQL's "show slave status" in
4.0. The output of that change significantly over time:
http://dev.mysql.com/doc/refman/4.1/en/show-slave-status.html
http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html

Also of interest
http://dev.mysql.com/doc/refman/4.1/en/show-binary-logs.html



-- 
Rob Wultsch
wult...@gmail.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] temporary functions (and other object types)

[Robert Haas]

On Fri, Nov 5, 2010 at 4:02 PM, Tom Lane  wrote:
> Alvaro Herrera  writes:
>> A customer of ours has the need for temporary functions.
>
> You can do that now:
>
> regression=# create function pg_temp.foo(f1 int) returns int
> regression-# as 'select $1+1' language sql;
> CREATE FUNCTION
> regression=# select pg_temp.foo(1);
>  foo
> -
>   2
> (1 row)
>
> You do have to qualify the name explicitly:
>
> regression=# select foo(1);
> ERROR:  function foo(integer) does not exist
>
> The latter is an intentional security feature and will not get changed.

I see that there could be a problem here with SECURITY DEFINER
functions, but I'm not clear whether it goes beyond that?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] timestamp of the last replayed transaction

[Robert Haas]

On Thu, Nov 4, 2010 at 9:00 PM, Fujii Masao  wrote:
> On Thu, Nov 4, 2010 at 10:27 AM, Fujii Masao  wrote:
>> On Tue, Nov 2, 2010 at 10:38 PM, Dimitri Fontaine
>>  wrote:
>>> Fujii Masao  writes:
 After 9.0 release, I've often heard that some people want to know
 how far transactions have been replayed in the standby in timestamp
 rather than LSN. So I'm thinking to include the function which returns
 the timestamp of the last applied transaction (i.e., commit/abort WAL
 record) in the core.

 Name: pg_last_replay_xact_timestamp (better name?)
 Return Type: timestamp with time zone

 Thought?
>>>
>>> How do you want to implement the tracking?
>>
>> I'm thinking to just expose GetLatestXTime(), i.e., 
>> XLogCtl->recoveryLastXTime.
>
> I attached the patch.

This looks good, but how about adding:

if (!RecoveryInProgress())
PG_RETURN_NULL();

Otherwise, if we're in Hot Standby mode for a while and then enter
normal running, wouldn't this still return a (stale) value?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] How can we tell how far behind the standby is?

[Robert Haas]

On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus  wrote:
> I'm continuing in my efforts now to document how to deploy and manage
> replication on our wiki.  One of the things a DBA needs to do is to use
> pg_current_xlog_location() (and related functions) to check how far
> behind the master the standby is.
>
> However, there's some serious problems with that:
>
> (1) comparing these numbers is quite mathematically complex -- and, for
> that matter, undocumented.
>
> (2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
> xlog positions, quite a large one if it happens near the beginning of a
> file.  There is no way for any monitoring on the standby to tell the
> difference between a gap created by forced rotation as opposed to being
> most of a file behind, until the next record shows up.  Hello, nagios
> false alerts!
>
> (3) There is no easy way to relate a difference in log positions to an
> amount of time.
>
> I'll work on some tools to make this a bit more palatable, but I
> disagree with earlier assertions that we have the replication monitoring
> "done".  There's still a *lot* of work to do.

I've heard the same complaint, and I agree with your concerns.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?

[Daniel Farina]

On Fri, Nov 5, 2010 at 4:20 PM, Robert Haas  wrote:
> Can you give us a self-contained example of the problem you're talking about?

Sure. Consider the following:

CREATE TABLE t1 (
id integer PRIMARY KEY
);

CREATE TABLE t2 (
id integer PRIMARY KEY,
fk integer
);

ALTER TABLE ONLY t2
ADD CONSTRAINT t2_constr FOREIGN KEY (fk) REFERENCES t1(id);

Try something like this:

createdb foo
psql -1f this_ddl.sql foo
pg_dump --clean foo > cleaning_backup.sql
# db wipe
dropdb foo
createdb foo
psql -1f cleaning_backup.sql foo

The last command will return non-zero and abort the xact early on,
because of the following stanza in pg_dump --clean's output:

ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_constr;
ALTER TABLE ONLY public.t2 DROP CONSTRAINT t2_pkey;
ALTER TABLE ONLY public.t1 DROP CONSTRAINT t1_pkey;
DROP TABLE public.t2;
DROP TABLE public.t1;

Since there's no public.t1/t2, it's not possible to ALTER them.

I'm not entirely sure why the DROPs CONSTRAINT on pkeys are being
done, as they only introduce an internal (or is it auto?) style
self-dependency. It is more obvious why foreign keys are dropped,
which is to break up the dependencies so that tables can be dropped
without CASCADE.

fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?

[Robert Haas]

On Fri, Nov 5, 2010 at 4:48 PM, Daniel Farina  wrote:
> On Fri, Nov 5, 2010 at 1:31 PM, Tom Lane  wrote:
>> Daniel Farina  writes:
>>> On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane  wrote:
 Daniel Farina  writes:
> pg_dump --clean will successfully and silently wipe out a foreign key
> right now, should it exist,

 No, it will not, because we don't use CASCADE in the drop commands.
>>
>>> I know it does not use CASCADE, but if I understand it correctly,
>>> foreign keys are dropped between tables, and then the tables are
>>> dropped. (effectively a manual cascade)
>>
>> You're missing the point.  The scenario I'm concerned about is:
>>
>>        source database contained table foo
>>
>>        target database contains table foo, and table bar, and
>>        bar has an FK reference to foo
>>
>
> I think that's intended and okay to fail, and would continue to fail
> post-patch, if I understand what I am doing correctly (always
> suspect).
>
> The only condition where this should be emitted is when all the
> dependent objects are going to be dropped anyway.

Dan,

Can you give us a self-contained example of the problem you're talking about?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] plpgsql execute vs. SELECT ... INTO

[Andrew Dunstan]

On 11/05/2010 06:54 PM, Tom Lane wrote:

Andrew Dunstan  writes:

The comment on the commit says:
 EXECUTE of a SELECT ... INTO now draws a 'not implemented' error,
 rather than executing the INTO clause with non-plpgsql semantics
 as it was doing for the last few weeks/months.  This keeps our options
 open for making it do the right plpgsql-ish thing in future without
 creating a backwards compatibility problem.  There is no loss of
 functionality since people can get the same behavior with CREATE TABLE AS.
Do we really still need to keep out options open on this after all that
time?

I think it's still a good idea that it won't do something that is very
much different from what a non-EXECUTE'd SELECT INTO will do.

I forget, is there a HINT there suggesting CREATE TABLE AS?  Maybe we
should add one if not.


No, (see below) we should certainly improve that and document the 
behavior, if we're going to keep it.


if (*ptr == 'S' || *ptr == 's')
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 errmsg("EXECUTE of SELECT ... INTO is not 
implemented"),
 errhint("You might want to use EXECUTE ... 
INTO instead.")));




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] plpgsql execute vs. SELECT ... INTO

[Tom Lane]

Andrew Dunstan  writes:
> The comment on the commit says:

> EXECUTE of a SELECT ... INTO now draws a 'not implemented' error,
> rather than executing the INTO clause with non-plpgsql semantics
> as it was doing for the last few weeks/months.  This keeps our options
> open for making it do the right plpgsql-ish thing in future without
> creating a backwards compatibility problem.  There is no loss of
> functionality since people can get the same behavior with CREATE TABLE AS.

> Do we really still need to keep out options open on this after all that 
> time?

I think it's still a good idea that it won't do something that is very
much different from what a non-EXECUTE'd SELECT INTO will do.

I forget, is there a HINT there suggesting CREATE TABLE AS?  Maybe we
should add one if not.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] plpgsql execute vs. SELECT ... INTO

[Andrew Dunstan]

(Prompted by a puzzled user on IRC)

Ten years ago, nearly, we made this commit 
 
(see what a good thing it is we carefully got all the history 
transferred to git?)


The comment on the commit says:

   EXECUTE of a SELECT ... INTO now draws a 'not implemented' error,
   rather than executing the INTO clause with non-plpgsql semantics
   as it was doing for the last few weeks/months.  This keeps our options
   open for making it do the right plpgsql-ish thing in future without
   creating a backwards compatibility problem.  There is no loss of
   functionality since people can get the same behavior with CREATE TABLE AS.

Do we really still need to keep out options open on this after all that 
time?


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] "Make" versus effective stack limit in regression tests

[Tom Lane]

Andrew Dunstan  writes:
> On 11/05/2010 05:45 PM, Tom Lane wrote:
>> Anyway, what this points up is that we are making a very conservative
>> assumption about what to do when getrlimit() returns RLIM_INFINITY.
>> It does not seem real reasonable to interpret that as 100kB on any
>> modern platform.  I'm inclined to interpret it as 4MB, which is the
>> same default stack limit that we use on Windows.

> +1.

After looking a bit closer, I think the real problem is that
get_stack_depth_rlimit's API fails to distinguish between "unknown" and
"unlimited".  In the first case we ought to have a conservative default,
whereas in the second case not.  It's already the case that (a)
max_stack_depth is a SUSET parameter, and (b) for either unknown or
unlimited RLIMIT_STACK, we will let a superuser set whatever value he
wants, and it's on his head whether that value is safe or not.  That
part of the behavior seems OK.  What's not OK is using the same
built-in default value in both cases.  We need to fix it so that
InitializeGUCOptions can tell the difference.  If it can, I think the
current default of 2MB is OK --- most people will be fine with that,
and those who aren't can select some other 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] Simplifying replication

[Hannu Krosing]

On Tue, 2010-10-19 at 10:31 +0200, Dimitri Fontaine wrote:

> > 4. I can start a new replica off the master by running a single command-line
> > utility on the standby and giving it connection information to the master.
> > Using this connection, it should be able to start a backup snapshot, copy
> > the entire database and any required logs, and then come up in standby mode.
> > All that should be required for this is one or two highport connections to
> > the master.  No recovery.conf file is required, or exists.
> 
> There's a prototype to stream a base backup from a libpq connection, I
> think someone here wanted to integrate that into the replication
> protocol itself. It should be doable with a simple libpq connection and
> all automated.
> 
> The pg_basebackup python client software is 100 lines of code. It's
> mainly a recursive query to get the list of files within the master,
> then two server side functions to get binary file chunks,
> compressed. Then client side, a loop to decompress and write the chunks
> at the right place. That's it.
> 
>   http://github.com/dimitri/pg_basebackup/blob/master/pg_basebackup.py
> 
> I could prepare a patch given some advice on the replication protocol
> integration. For one, is streaming a base backup something that
> walsender should care about?

To make pg_basebackup.py self-sufficient it should also open 2nd
connection to the same master and make sure that all WAL files are
copied for the duration of base copy.

This way you don't need to do anything extra to make sure you have
enough wal files. And if you fail because of filling up disks, you fail
on slave side, where it is less of a problem.

> > 
-- 
---
Hannu Krosing
PostgreSQL Infinite Scalability and Preformance Consultant
PG Admin Book: http://www.2ndQuadrant.com/books/



-- 
Sent 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] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Josh Berkus]

On 11/5/10 3:31 PM, Tom Lane wrote:
> Josh Berkus  writes:
>> What plaforms do we need to test to get a reasonable idea? Solaris,
>> FreeBSD, Windows?
> 
> At least.  I'm hoping that Greg Smith will take the lead on testing
> this, since he seems to have spent the most time in the area so far.

I could test at least 1 version of Solaris, I think.

Greg, any recommendations on pgbench parameters?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Tom Lane]

Josh Berkus  writes:
> What plaforms do we need to test to get a reasonable idea? Solaris,
> FreeBSD, Windows?

At least.  I'm hoping that Greg Smith will take the lead on testing
this, since he seems to have spent the most time in the area so 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] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Josh Berkus]

> I think the original idea was that if you had a dedicated WAL drive then
> sync-on-write would be reasonable.  But that was a very long time ago
> and I'm not sure that the system's behavior is anything like what it was
> then; for that matter I'm not sure we had proof that it was an optimal
> choice even back then.  That's why I want to revisit the choice of
> default and not just go for "minimum" change.

What plaforms do we need to test to get a reasonable idea? Solaris,
FreeBSD, Windows?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] "Make" versus effective stack limit in regression tests

[Andrew Dunstan]

On 11/05/2010 05:45 PM, Tom Lane wrote:

  Anyway, what this points up is that we are making a very conservative
assumption about what to do when getrlimit() returns RLIM_INFINITY.
It does not seem real reasonable to interpret that as 100kB on any
modern platform.  I'm inclined to interpret it as 4MB, which is the
same default stack limit that we use on Windows.


+1.

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] CREATE CONSTRAINT TRIGGER

[Tom Lane]

Alvaro Herrera  writes:
> Some time ago, CREATE CONSTRAINT TRIGGER was a deprecated command and it
> was slated for obsolence or removal.

> Recent developments have turned it back into non-deprecated mode; it's
> not going anywhere, and it needs to be fully documented.

> It seems to me that it makes more sense to merge its documentation into
> the CREATE TRIGGER page, where it belongs.

Seems reasonable.  I didn't read the patch in detail 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] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Andres Freund]

On Friday 05 November 2010 22:53:37 Greg Smith wrote:
> > If open_dsync is so bad for performance on Linux, maybe it's bad
> > everywhere?  Should we be rethinking the default preference order?
> >
> >   
> 
> And I've seen the expected sync write performance gain over fdatasync on 
> a system with a battery-backed cache running VxFS on Linux, because 
> working open_[d]sync means O_DIRECT writes bypassing the OS cache, and 
> therefore reducing cache pollution from WAL writes.
Which looks like a setup where you definitely need to know what you do. I.e. 
don't need support from wal_sync_method by default being open_fdatasync...

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] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Tom Lane]

Andres Freund  writes:
> On Friday 05 November 2010 19:13:47 Tom Lane wrote:
>> If open_dsync is so bad for performance on Linux, maybe it's bad
>> everywhere?  Should we be rethinking the default preference order?

> I fail to see how it could be beneficial on *any* non-buggy platform.
> Especially with small wal_buffers and larger commits (but also otherwise) it 
> increases the amount of synchronous writes the os has to do tremendously.

> * It removes about all benefits of XLogBackgroundFlush() 
> * It removes any chances of reordering after writing.
> * It makes AdvanceXLInsertBuffer synchronous if it has to write outy 

> Whats the theory about placing it so high in the preferences list?

I think the original idea was that if you had a dedicated WAL drive then
sync-on-write would be reasonable.  But that was a very long time ago
and I'm not sure that the system's behavior is anything like what it was
then; for that matter I'm not sure we had proof that it was an optimal
choice even back then.  That's why I want to revisit the choice of
default and not just go for "minimum" change.

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] Query Plan Columns

[Tom Lane]

Josh Berkus  writes:
>> 960 ExplainPropertyFloat("Plan Rows", plan->plan_rows, 0, es);
>> 
>> Should rows really be a float?

> Yeah, since PropertyInteger is 32-bit.

Yes, plan_rows is a float.  We might need to represent very large
values, and we do not need high accuracy, so it's really the right type.

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] CREATE CONSTRAINT TRIGGER

[Richard Broersma]

On Fri, Nov 5, 2010 at 2:37 PM, Alvaro Herrera  wrote:

> Recent developments have turned it back into non-deprecated mode; it's
> not going anywhere, and it needs to be fully documented.

>From what I recall, there isn't anything in the trigger documentation
or CREATE CONSTRAINT TRIGGER documentation that says the trigger
function must explicitly raise an exception to create the notification
that the custom constraint was violated.


Would this be a good place for it?

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent 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] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Greg Smith]

Tom Lane wrote:

If open_dsync is so bad for performance on Linux, maybe it's bad
everywhere?  Should we be rethinking the default preference order?
  


And I've seen the expected sync write performance gain over fdatasync on 
a system with a battery-backed cache running VxFS on Linux, because 
working open_[d]sync means O_DIRECT writes bypassing the OS cache, and 
therefore reducing cache pollution from WAL writes.  This doesn't work 
by default on Solaris because they have a special system call you have 
to execute for direct output, but if you trick the OS into doing that 
via mount options you can observe it there too.  The last serious tests 
of this area I saw on that platform were from Jignesh, and they 
certainly didn't show a significant performance regression running in 
sync mode.  I vaguely recall seeing a set once that showed a minor loss 
compared to fdatasync, but it was too close to make any definitive 
statement about reordering.


I haven't seen any report yet of a serious performance regression in the 
new Linux case that was written by someone who understands fully how 
fsync and drive cache flushing are supposed to interact.  It's been 
obvious for a year now that the reports from Phoronix about this had no 
idea what they were actually testing.  I didn't see anything from 
Marti's report that definitively answers whether this is anything other 
than Linux finally doing the right thing to flush drive caches out when 
sync writes happen.  There may be a performance regression here related 
to WAL data going out in smaller chunks than it used to, but in all the 
reports I've seen it that hasn't been isolated well enough to consider 
making any changes yet--to tell if it's a performance loss or a 
reliability gain we're seeing.


I'd like to see some output from the 9.0 test_fsync on one of these 
RHEL6 systems on a system without a battery backed write cache as a 
first step here.  That should start to shed some light on what's 
happening.  I just bumped up the priority on the pending upgrade of my 
spare laptop to the RHEL6 beta I had been trying to find time for, so I 
can investigate this further myself.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] "Make" versus effective stack limit in regression tests

[Tom Lane]

I wondered why some of the buildfarm machines were showing
max_stack_depth = 100kB, and Andrew Dunstan kindly lent me the
use of "dungbeetle" to check it out.  What I found out:

1. max_stack_depth has the expected value (equal to ulimit -s)
in any manually started postmaster.  It only drops to 100kB
in the "make check" environment.

2. postgres.c's getrlimit(RLIMIT_STACK) call returns the expected
values in a manual start:
rlim_cur = 10485760 rlim_max = -1
but in a "make check" run:
rlim_cur = -1 rlim_max = -1
ie, the soft limit has been reset to RLIM_INFINITY.
get_stack_depth_rlimit chooses to treat this as "unknown", resulting
in setting max_stack_depth to the minimal value.

3. Further experimentation proves that "make" is resetting the limit
for any program it invokes.

I couldn't reproduce this on my Fedora 13 machine, even though it
is nominally running the same gmake 3.81 as dungbeetle's Fedora 6.
So I took a look into Fedora git, and sure enough, there's a relevant
patch there.  It seems that gmake 3.81 tries to force up the
RLIMIT_STACK rlim_cur to rlim_max, because it relies heavily on alloca()
and so needs lots of stack space.  Fedora 7 and up have patched it to
restore the caller's setting before actually invoking any programs:
https://bugzilla.redhat.com/show_bug.cgi?id=214033

I haven't done the research to find out which gmake versions have this
behavior or which other Linux distros are carrying similar patches,
but I'm sure this explains why some of the buildfarm members report
max_stack_depth = 100kB when most others don't.

Anyway, what this points up is that we are making a very conservative
assumption about what to do when getrlimit() returns RLIM_INFINITY.
It does not seem real reasonable to interpret that as 100kB on any
modern platform.  I'm inclined to interpret it as 4MB, which is the
same default stack limit that we use on Windows.

Thoughts?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] CREATE CONSTRAINT TRIGGER

[Alvaro Herrera]

Some time ago, CREATE CONSTRAINT TRIGGER was a deprecated command and it
was slated for obsolence or removal.

Recent developments have turned it back into non-deprecated mode; it's
not going anywhere, and it needs to be fully documented.

It seems to me that it makes more sense to merge its documentation into
the CREATE TRIGGER page, where it belongs.  Right now, the documentation
page for CREATE CONSTRAINT TRIGGER is 50% a repetition of what's in the
CREATE TRIGGER page; and a significant portion of the rest is spent
telling the user to refer to the CREATE TRIGGER page.  The original
information to be found in CREATE CONSTRAINT TRIGGER is not all that
much.

Thus the attached patch.

-- 
Álvaro Herrera 


0001-Fold-the-CREATE-CONSTRAINT-TRIGGER-doc-page-into-CRE.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] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Marti Raudsepp]

On Fri, Nov 5, 2010 at 22:16, Tom Lane  wrote:
> I don't really think that "change the default on Linux" is that
> much nicer than "change the default everywhere" when it comes to
> what we ought to consider back-patching.  In any case, you're getting
> ahead of the game: we need to decide on the desired behavior first and
> then think about what to patch.

We should be trying to guarantee the stability of maintenance
releases. "Stability" includes consistent defaults. The fact that
Linux now distinguishes between these two flags has a very surprising
effect on PostgreSQL's defaults; an effect that wasn't intended by any
developer, is not documented anywhere, and certainly won't be
anticipated by users.

Do you reject this premise?

As newer distros are adopting 2.6.33+ kernels, more and more people
will shoot themselves in the foot by this change. I am also worried
that it will have a direct effect on PostgreSQL adoption.

Regards,
Marti

-- 
Sent 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] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Andres Freund]

On Friday 05 November 2010 19:13:47 Tom Lane wrote:
> Marti Raudsepp  writes:
> > PostgreSQL's default settings change when built with Linux kernel
> > headers 2.6.33 or newer. As discussed on the pgsql-performance list,
> > this causes a significant performance regression:
> > http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php
> > 
> > NB! I am not proposing to change the default -- to the contrary --
> > this patch restores old behavior.
> 
> I'm less than convinced this is the right approach ...
> 
> If open_dsync is so bad for performance on Linux, maybe it's bad
> everywhere?  Should we be rethinking the default preference order?
I fail to see how it could be beneficial on *any* non-buggy platform.
Especially with small wal_buffers and larger commits (but also otherwise) it 
increases the amount of synchronous writes the os has to do tremendously.

* It removes about all benefits of XLogBackgroundFlush() 
* It removes any chances of reordering after writing.
* It makes AdvanceXLInsertBuffer synchronous if it has to write outy 

Whats the theory about placing it so high in the preferences list?

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] Query Plan Columns

[Josh Berkus]

>  960 ExplainPropertyFloat("Plan Rows", plan->plan_rows, 0, es);
> 
> Should rows really be a float?

Yeah, since PropertyInteger is 32-bit.


-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query Plan Columns

[David E. Wheeler]

On Nov 5, 2010, at 1:42 PM, David E. Wheeler wrote:

> On Nov 5, 2010, at 1:38 PM, Dimitri Fontaine wrote:
> 
>> It seems that you need to read through ExplainNode in
>> src/backend/commands/explain.c:
>> 
>> http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/commands/explain.c;h=f494ec98e510c23120e072bd5ee8821ea12738a4;hb=HEAD#l617
> 
> Ah, great, thanks.
> 
> David

Hrm…

 960 ExplainPropertyFloat("Plan Rows", plan->plan_rows, 0, es);

Should rows really be a float?

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] ALTER TABLE ... IF EXISTS feature?

[Daniel Farina]

On Fri, Nov 5, 2010 at 1:31 PM, Tom Lane  wrote:
> Daniel Farina  writes:
>> On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane  wrote:
>>> Daniel Farina  writes:
 pg_dump --clean will successfully and silently wipe out a foreign key
 right now, should it exist,
>>>
>>> No, it will not, because we don't use CASCADE in the drop commands.
>
>> I know it does not use CASCADE, but if I understand it correctly,
>> foreign keys are dropped between tables, and then the tables are
>> dropped. (effectively a manual cascade)
>
> You're missing the point.  The scenario I'm concerned about is:
>
>        source database contained table foo
>
>        target database contains table foo, and table bar, and
>        bar has an FK reference to foo
>

I think that's intended and okay to fail, and would continue to fail
post-patch, if I understand what I am doing correctly (always
suspect).

The only condition where this should be emitted is when all the
dependent objects are going to be dropped anyway.

fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query Plan Columns

[David E. Wheeler]

On Nov 5, 2010, at 1:38 PM, Dimitri Fontaine wrote:

> It seems that you need to read through ExplainNode in
> src/backend/commands/explain.c:
> 
>  
> http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/commands/explain.c;h=f494ec98e510c23120e072bd5ee8821ea12738a4;hb=HEAD#l617

Ah, great, thanks.

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query Plan Columns

[David E. Wheeler]

On Nov 5, 2010, at 1:36 PM, Andrew Dunstan wrote:

> Of course, there are containers too, which are not in your list at all. How 
> do you intend to represent the tree-ish structure in a flat table?

So far I see only two containers: Subplans and Sot Keys. The latter is 
represented as an array. The former I'm going to represent simply by having a 
parent_id column to point to a parent from a child node.

Are there others?

Thanks,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query Plan Columns

[Josh Berkus]

> Of course, there are containers too, which are not in your list at all.
> How do you intend to represent the tree-ish structure in a flat table?

Andrew: we'll use a proximity tree.


-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query Plan Columns

[Dimitri Fontaine]

"David E. Wheeler"  writes:
> * Is there some place in the source with a canonical list?

It seems that you need to read through ExplainNode in
src/backend/commands/explain.c:

  
http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/commands/explain.c;h=f494ec98e510c23120e072bd5ee8821ea12738a4;hb=HEAD#l617

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Query Plan Columns

[Andrew Dunstan]

On 11/05/2010 04:27 PM, David E. Wheeler wrote:

Fellow Hackers,

I'm writing a function to turn an EXPLAIN plan into a table with columns. As 
such, I need to have a complete list of the various bits of each plan node and 
their types for the table. Here's what I've got so far:

 "Node Type"   TEXT,
 "Strategy"TEXT,
 "Startup Cost"NUMERIC,
 "Total Cost"  NUMERIC,
 "Plan Rows"   INTEGER,
 "Plan Width"  INTEGER,
 "Actual Startup Time" NUMERIC,
 "Actual Total Time"   NUMERIC,
 "Actual Rows" INTEGER,
 "Actual Width"INTEGER,
 "Actual Loops"INTEGER,
 "Parent Relationship" TEXT
 "Sort Key"TEXT[],
 "Sort Method" TEXT,
 "Sort-Space-Used" TEXT,
 "Sort-Space-Type" TEXT,
 "Join Type"   TEXT,
 "Join Filter" TEXT,
 "Hash Cond"   TEXT,
 "Relation Name"   NAME,
 "Alias"   NAME,
 "Scan Direction"  TEXT,
 "Index Name"  NAME,
 "Index Cond"  TEXT,
 "Subplan Name"TEXT,
 "Function Name"   TEXT,
 "Filter"  TEXT

Questions:

* Am I missing any?
* Are the data types correct?
* Is there some place in the source with a canonical list?



src/backend/commands/explain.c

Of course, there are containers too, which are not in your list at all. 
How do you intend to represent the tree-ish structure in a flat table?


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] ALTER TABLE ... IF EXISTS feature?

[Tom Lane]

Daniel Farina  writes:
> On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane  wrote:
>> Daniel Farina  writes:
>>> pg_dump --clean will successfully and silently wipe out a foreign key
>>> right now, should it exist,
>> 
>> No, it will not, because we don't use CASCADE in the drop commands.

> I know it does not use CASCADE, but if I understand it correctly,
> foreign keys are dropped between tables, and then the tables are
> dropped. (effectively a manual cascade)

You're missing the point.  The scenario I'm concerned about is:

source database contained table foo

target database contains table foo, and table bar, and
bar has an FK reference to foo

The FK constraint is not known to pg_dump, and therefore there will not
be a DROP CONSTRAINT for it in the dump.  So the DROP on the referenced
table will fail.  The only way we could make it succeed would be to use
CASCADE, which we don't do specifically because this sort of thing seems
quite unsafe.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Query Plan Columns

[David E. Wheeler]

Fellow Hackers,

I'm writing a function to turn an EXPLAIN plan into a table with columns. As 
such, I need to have a complete list of the various bits of each plan node and 
their types for the table. Here's what I've got so far:

"Node Type"   TEXT,
"Strategy"TEXT,
"Startup Cost"NUMERIC,
"Total Cost"  NUMERIC,
"Plan Rows"   INTEGER,
"Plan Width"  INTEGER,
"Actual Startup Time" NUMERIC,
"Actual Total Time"   NUMERIC,
"Actual Rows" INTEGER,
"Actual Width"INTEGER,
"Actual Loops"INTEGER,
"Parent Relationship" TEXT
"Sort Key"TEXT[],  
"Sort Method" TEXT,
"Sort-Space-Used" TEXT,
"Sort-Space-Type" TEXT,
"Join Type"   TEXT,
"Join Filter" TEXT,
"Hash Cond"   TEXT,
"Relation Name"   NAME,
"Alias"   NAME,
"Scan Direction"  TEXT,
"Index Name"  NAME,
"Index Cond"  TEXT,
"Subplan Name"TEXT,
"Function Name"   TEXT,
"Filter"  TEXT

Questions:

* Am I missing any?
* Are the data types correct?
* Is there some place in the source with a canonical list?

Thanks,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Tom Lane]

Marti Raudsepp  writes:
> On Fri, Nov 5, 2010 at 21:20, Tom Lane  wrote:
>> What's that got to do with it?

> I'm not sure what you're asking.

> Surely changing the default wal_sync_method for all OSes in
> maintenance releases is out of the question, no?

Well, if we could leave well enough alone it would be fine with me,
but I think our hand is being forced by the Linux kernel hackers.
I don't really think that "change the default on Linux" is that
much nicer than "change the default everywhere" when it comes to
what we ought to consider back-patching.  In any case, you're getting
ahead of the game: we need to decide on the desired behavior first and
then think about what to patch.  Do the performance results that were
cited show that open_dsync is generally inferior to fdatasync?  If so,
why would we think that that conclusion is Linux-specific?

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] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Alvaro Herrera  writes:
> Basically you're saying that the owner of the schema in which the
> extension is installed can drop the extension ... even though, according
> to your previous argument, the extension is not "in" said schema :-)

Yeah it's a case of defining things. The extension is not in the schema,
it depends on it. So if you drop schema cascade, that cascades to drop
extension.

I'm not saying that the only way to do it sanely is the one I propose,
but I did consider some alternatives and I did not code the current
patch only by accident :)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Alvaro Herrera]

Excerpts from Dimitri Fontaine's message of vie nov 05 16:58:00 -0300 2010:

> dim=> drop schema bob cascade;
> NOTICE:  drop cascades to extension unaccent
> DROP SCHEMA
> 
> dim=> \c - dim
> You are now connected to database "dim" as user "dim".
> dim=# select installed from pg_extensions where name = 'unaccent';
>  installed 
> ---
>  f
> (1 row)

Basically you're saying that the owner of the schema in which the
extension is installed can drop the extension ... even though, according
to your previous argument, the extension is not "in" said schema :-)

-- 
Álvaro Herrera 
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] temporary functions (and other object types)

[Tom Lane]

Alvaro Herrera  writes:
> A customer of ours has the need for temporary functions.

You can do that now:

regression=# create function pg_temp.foo(f1 int) returns int
regression-# as 'select $1+1' language sql;
CREATE FUNCTION
regression=# select pg_temp.foo(1);
 foo 
-
   2
(1 row)

You do have to qualify the name explicitly:

regression=# select foo(1);
ERROR:  function foo(integer) does not exist

The latter is an intentional security feature and will not get changed.

The general security risks around this make me hesitant to accept the
idea of an explicit CREATE TEMP FUNCTION syntax: it's never going to
be better than a second-class feature, so it shouldn't have a
first-class syntax.

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] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Marti Raudsepp]

On Fri, Nov 5, 2010 at 21:20, Tom Lane  wrote:
> Marti Raudsepp  writes:
>> On Fri, Nov 5, 2010 at 20:13, Tom Lane  wrote:
>>> If open_dsync is so bad for performance on Linux, maybe it's bad
>>> everywhere?  Should we be rethinking the default preference order?
>
>> So I think we should aim to fix old versions first. Do you disagree?
>
> What's that got to do with it?

I'm not sure what you're asking.

Surely changing the default wal_sync_method for all OSes in
maintenance releases is out of the question, no?

Regards,
Marti

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Tom Lane  writes:
> You're mistaken, and this case definitely does need more thought.
> A schema owner is presumed to have the unconditional right to
> drop anything in his schema, whether he owns it or not.

Here a paste of how it works with current code.

dim=# create schema bob authorization bob;
CREATE SCHEMA

dim=# alter extension unaccent set schema bob;
ALTER EXTENSION

dim=# \c - bob
You are now connected to database "dim" as user "bob".

dim=> drop schema bob;
ERROR:  cannot drop schema bob because other objects depend on it
DETAIL:  extension unaccent depends on schema bob
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

dim=> drop schema bob cascade;
NOTICE:  drop cascades to extension unaccent
DROP SCHEMA

dim=> \c - dim
You are now connected to database "dim" as user "dim".
dim=# select installed from pg_extensions where name = 'unaccent';
 installed 
---
 f
(1 row)

Isn't that enough for you?
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] temporary functions (and other object types)

[David E. Wheeler]

On Nov 5, 2010, at 12:36 PM, Alvaro Herrera wrote:

> Hi,
> 
> A customer of ours has the need for temporary functions.  The use case
> is writing test cases for their databases: the idea being that their
> code creates a temp function which then goes away automatically at
> session end, just like a temp table.  It's said that this makes things
> generally easier for the test harness.

This is one reason why I recommend that pgTAP tests run inside a transaction.

> Other object types that would also be useful to have as temp-able are
> types, domains and casts; and perhaps (if someone sees a need)
> aggregates and operators.  Other objects are not necessary, but if
> someone thinks that some more stuff should be made temp-able, we'd try
> to go for as general a solution as possible.  But these aren't critical;
> functions are the main pain point.

Running the tests inside a transaction gives you this for free, right now -- 
*and* leaves the database in a known state at the end (modulo sequences).

Can you just use transactions?

Best,

David

PS: What test framework and harness are you using?
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] temporary functions (and other object types)

[Szymon Guz]

On 5 November 2010 20:36, Alvaro Herrera  wrote:

> Hi,
>
> A customer of ours has the need for temporary functions.  The use case
> is writing test cases for their databases: the idea being that their
> code creates a temp function which then goes away automatically at
> session end, just like a temp table.  It's said that this makes things
> generally easier for the test harness.
>
> Other object types that would also be useful to have as temp-able are
> types, domains and casts; and perhaps (if someone sees a need)
> aggregates and operators.  Other objects are not necessary, but if
> someone thinks that some more stuff should be made temp-able, we'd try
> to go for as general a solution as possible.  But these aren't critical;
> functions are the main pain point.
>
> I haven't looked at the code at all to see how this would be
> implemented; I'm basically asking whether there would be objections to
> having this feature in core.
>
>
Hi,
is that possible to create all that in one transaction? You could then make
rollback and all objects will just disappear.

regards
Szymon

[HACKERS] temporary functions (and other object types)

[Alvaro Herrera]

Hi,

A customer of ours has the need for temporary functions.  The use case
is writing test cases for their databases: the idea being that their
code creates a temp function which then goes away automatically at
session end, just like a temp table.  It's said that this makes things
generally easier for the test harness.

Other object types that would also be useful to have as temp-able are
types, domains and casts; and perhaps (if someone sees a need)
aggregates and operators.  Other objects are not necessary, but if
someone thinks that some more stuff should be made temp-able, we'd try
to go for as general a solution as possible.  But these aren't critical;
functions are the main pain point.

I haven't looked at the code at all to see how this would be
implemented; I'm basically asking whether there would be objections to
having this feature in core.

-- 
Álvaro Herrera 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Tom Lane]

Dimitri Fontaine  writes:
> Tom Lane  writes:
>> Here's another question: if an extension's objects live (mostly or
>> entirely) in schema X, what happens if the possibly-unprivileged owner
>> of schema X decides to drop it?  If the extension itself is considered
>> to live within the schema, then "the whole extension goes away" seems
>> like a natural answer.  If not, you've got some problems.

> Currently, creating an extension is superuser only. So the owner of
> those objects is a superuser. My understanding is that the drop schema
> will then fail without any more code.

You're mistaken, and this case definitely does need more thought.
A schema owner is presumed to have the unconditional right to
drop anything in his schema, whether he owns it or not.

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] ALTER TABLE ... IF EXISTS feature?

[Daniel Farina]

On Fri, Nov 5, 2010 at 11:04 AM, Tom Lane  wrote:
> Daniel Farina  writes:
>> I am somewhat sympathetic to this argument, except for one thing:
>
>> pg_dump --clean will successfully and silently wipe out a foreign key
>> right now, should it exist,
>
> No, it will not, because we don't use CASCADE in the drop commands.

I know it does not use CASCADE, but if I understand it correctly,
foreign keys are dropped between tables, and then the tables are
dropped. (effectively a manual cascade) In not-single-transaction
mode, this can cause a foreign key to get dropped, but then the DROP
TABLE could subsequently fail, leaving two tables that were once
connected no longer connected. I could be wrong about this, but I
think this is the case. If that is not the case, please correct me
before continuing to read...

If the referencing table is not scoped for dumping by pg_dump, then
wouldn't the transaction *correctly* (or perhaps I should say
"desirably") abort upon attempting to drop the PK? Right now pg_dump
--clean is falling down in very common cases where constraints are
being dropped prior to all the related objects being dropped.

I will also make an argument that, provided --clean is regarded as a
feature to have at all, that ensuring it can be used productively in a
well-formed case in single-transaction mode is going to prevent a lot
of mistakes. As-is the user is compelled -- should they opt to use
clean -- to not use single-transaction mode for restores because it's
practically guaranteed to fail all the time should they use foreign
key constraints anywhere, unless they are certain that all objects in
the database being restored into exist with the same relationships,
for sure. Right now to get it to do anything useful it *must* run in
multi-transaction mode so constraint drops can fail in their own
transaction, and that means that you will be left with a database with
some subset of constraints remaining (only discernable by the log).

I could be wrong, but I think the change I'm suggesting is very safe,
and quite potentially safer because users will be more inclined to
restore in a transaction.

fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Tom Lane]

Marti Raudsepp  writes:
> On Fri, Nov 5, 2010 at 20:13, Tom Lane  wrote:
>> If open_dsync is so bad for performance on Linux, maybe it's bad
>> everywhere?  Should we be rethinking the default preference order?

> So I think we should aim to fix old versions first. Do you disagree?

What's that got to do with it?

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] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Marti Raudsepp]

On Fri, Nov 5, 2010 at 20:13, Tom Lane  wrote:
> I'm less than convinced this is the right approach ...
>
> If open_dsync is so bad for performance on Linux, maybe it's bad
> everywhere?  Should we be rethinking the default preference order?

Sure, maybe for PostgreSQL 9.1

But the immediate problem is older releases (8.1 - 9.0) specifically
on Linux. Something as innocuous as re-building your DB on a newer
kernel will radically affect performance -- even when the DB kernel
didn't change.

So I think we should aim to fix old versions first. Do you disagree?

Regards,
Marti

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] How can we tell how far behind the standby is?

[Josh Berkus]

Folks,

I'm continuing in my efforts now to document how to deploy and manage
replication on our wiki.  One of the things a DBA needs to do is to use
pg_current_xlog_location() (and related functions) to check how far
behind the master the standby is.

However, there's some serious problems with that:

(1) comparing these numbers is quite mathematically complex -- and, for
that matter, undocumented.

(2) pg_rotate_xlog and/or archive_timeout will create a "gap" in the
xlog positions, quite a large one if it happens near the beginning of a
file.  There is no way for any monitoring on the standby to tell the
difference between a gap created by forced rotation as opposed to being
most of a file behind, until the next record shows up.  Hello, nagios
false alerts!

(3) There is no easy way to relate a difference in log positions to an
amount of time.

I'll work on some tools to make this a bit more palatable, but I
disagree with earlier assertions that we have the replication monitoring
"done".  There's still a *lot* of work to do.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Tom Lane  writes:
>> Are you proposing that an extension object is schema qualified?
>
> Dunno, I'm just asking the question.  If it isn't, why not?

Because extension are much like languages for stored procedure, on the
utility side rather than on the query side. The only queries that uses
language directly are utility statements, yet functions will depend on
them and live in some schema.

That's how I see extensions too, a new utility. Nothing I expect to be
visible in queries. They don't need schema, they are database globals.
The objects that are depending on the extension may or may not live in a
schema. A single extension script could create more than one schema and
have objects spread there.

So either we restrict an extension's to live in a single schema and we
have to forbid changing the schema of the objects in there on their own
(DEPENDENCY_INTERNAL should help), or we add a very simple check at
ALTER EXTENSION ... SET SCHEMA time to error out when the extension
depends on more than one schema. I'd do the later, obviously.

> Here's another question: if an extension's objects live (mostly or
> entirely) in schema X, what happens if the possibly-unprivileged owner
> of schema X decides to drop it?  If the extension itself is considered
> to live within the schema, then "the whole extension goes away" seems
> like a natural answer.  If not, you've got some problems.

Currently, creating an extension is superuser only. So the owner of
those objects is a superuser. My understanding is that the drop schema
will then fail without any more code.

>> Would we lower creating extension privileges to database owners, too,
>> rather than only superusers?
>
> That seems like an orthogonal question.  I can see people wanting both
> behaviors though.  Maybe an extension's config file should specify the
> privs needed to install it?

Orthogonal indeed, but it popped in my mind reading the previous mail,
and reading your previous question I guess you see why :)

I'm not for offering extension's author to control this behavior. As the
extension will more often than not come with a shared object lib, and as
the goal is to install SQL objects that will NOT be part of pg_dump
output, my feeling is that superuser only makes most sense.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Tom Lane]

Marti Raudsepp  writes:
> PostgreSQL's default settings change when built with Linux kernel
> headers 2.6.33 or newer. As discussed on the pgsql-performance list,
> this causes a significant performance regression:
> http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

> NB! I am not proposing to change the default -- to the contrary --
> this patch restores old behavior.

I'm less than convinced this is the right approach ...

If open_dsync is so bad for performance on Linux, maybe it's bad
everywhere?  Should we be rethinking the default preference order?

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] ALTER TABLE ... IF EXISTS feature?

[Tom Lane]

Daniel Farina  writes:
> I am somewhat sympathetic to this argument, except for one thing:

> pg_dump --clean will successfully and silently wipe out a foreign key
> right now, should it exist,

No, it will not, because we don't use CASCADE in the drop commands.

The case I'm thinking about is where we are trying to --clean the PK
table, and the referencing table is one we don't know about.  The other
way around, the FK constraint will disappear, but that's arguably less
problematic.

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] ALTER TABLE ... IF EXISTS feature?

[Daniel Farina]

On Fri, Nov 5, 2010 at 10:04 AM, Tom Lane  wrote:
> What you're proposing would maybe be useful for overwriting a database
> that contains portions of what is in the source database, but what's
> the use of that?  You could just as well dropdb and start fresh.  The
> interesting case here is where the target db has *more* objects than
> are in the dump, and I really doubt that there is any self-consistent
> behavior that pg_dump can automatically provide for such cases.  It
> can't drop objects it doesn't know about, and it also has no hope of
> ensuring that their relationships to the replaced objects remain
> consistent.  Silently wiping out FKs, for instance, seems like a pretty
> bad idea.

I am somewhat sympathetic to this argument, except for one thing:

pg_dump --clean will successfully and silently wipe out a foreign key
right now, should it exist, especially considering the default mode is
not to run in a single transaction; a bunch of errors will be spit out
to the log (except in the case where it will, as you say, silently
wipe out a FK), but it'll probably be too late to do anything.

It only complains (and then fails to act anyway, as there is no object
to act upon) when no such object exists. One way to ensure it doesn't
delete a foreign key that makes less sense is to ensure that all
dependents are to-be-dumped DependentObjects in pg_dump.
Pattern-matching/partial pg_dumps have never been sound in the general
case anyway.

> In most cases you'd *better* study those error messages, to see what manual 
> cleanup you're going to need to undertake.

Of what possible use to me is a transaction-aborting error message and
state emitted by an ALTER that is then succeeded by a series of DROP
(TABLE|DOMAIN) statements that encompass the dependents anyway?

I think an error message saying "couldn't drop this table because
other objects that were not named by the cleaning-dump depend on it"
falls into the category of interesting error message that should abort
the transaction. An error condition/xact abort complaining about a
missing constraint for tables that are about to have DROP statements
execute for both of them is considerably less useful.

> So, basically, I've never seen any fully credible use case for pg_dump --clean

Then why is it not on the road to deprecation, or at least given a
strong warning to not use it in the --help message (or is it)? As-is I
think it may only exist to misguide people.

fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

[Marti Raudsepp]

Hi list,

PostgreSQL's default settings change when built with Linux kernel
headers 2.6.33 or newer. As discussed on the pgsql-performance list,
this causes a significant performance regression:
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

NB! I am not proposing to change the default -- to the contrary --
this patch restores old behavior. Users might be in for a nasty
performance surprise when re-building their Postgres with newer Linux
headers (as was I), so I propose that this change should be made in
all supported releases.

-- commit message --
Revert default wal_sync_method to fdatasync on Linux 2.6.33+

Linux kernel headers from 2.6.33 (and later) change the behavior of the
O_SYNC flag. Previously O_SYNC was aliased to O_DSYNC, which caused
PostgreSQL to use fdatasync as the default instead.

Starting with kernels 2.6.33 and later, the definitions of O_DSYNC and
O_SYNC differ. When built with headers from these newer kernels,
PostgreSQL will default to using open_datasync. This patch reverts the
Linux default to fdatasync, which has had much more testing over time
and also significantly better performance.
-- end commit message --

Earlier kernel headers defined O_SYNC and O_DSYNC to 0x1000
2.6.33 and later define O_SYNC=0x101000 and O_DSYNC=0x1000 (since old
behavior on most FS-es was always equivalent to POSIX O_DSYNC)

More details at:
http://git.kernel.org/?p=linux/kernel/git/torvalds/linux-2.6.git;a=commit;h=6b2f3d1f769be5779b479c37800229d9a4809fc3

Currently PostgreSQL's include/access/xlogdefs.h defaults to using
open_datasync when O_SYNC != O_DSYNC, otherwise fdatasync is used.

Since other platforms might want to default to fdatasync in the
future, too, I defined a new PLATFORM_DEFAULT_SYNC_METHOD constant in
include/port/linux.h. I don't know if this is the best way to do it.

Regards,
Marti
From fbf61c6536b7060e5c6745c8221a5a4fb9a53c92 Mon Sep 17 00:00:00 2001
From: Marti Raudsepp 
Date: Fri, 5 Nov 2010 17:40:22 +0200
Subject: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

Linux kernel headers from 2.6.33 (and later) change the behavior of the
O_SYNC flag. Previously O_SYNC was aliased to O_DSYNC, which caused
PostgreSQL to use fdatasync as the default instead.

Starting with kernels 2.6.33 and later, the definitions of O_DSYNC and
O_SYNC differ. When built with headers from these newer kernels,
PostgreSQL will default to using open_datasync. This patch reverts the
Linux default to fdatasync, which has had much more testing over time
and also significantly better performance.
---
 src/include/access/xlogdefs.h |5 +
 src/include/port/linux.h  |9 +
 2 files changed, 14 insertions(+), 0 deletions(-)

diff --git a/src/include/access/xlogdefs.h b/src/include/access/xlogdefs.h
index 18b214e..7ed1bde 100644
--- a/src/include/access/xlogdefs.h
+++ b/src/include/access/xlogdefs.h
@@ -13,6 +13,7 @@
 #define XLOG_DEFS_H
 
 #include /* need open() flags */
+#include "pg_config_os.h"
 
 /*
  * Pointer to a location in the XLOG.  These pointers are 64 bits wide,
@@ -123,6 +124,9 @@ typedef uint32 TimeLineID;
 #endif
 #endif
 
+#if defined(PLATFORM_DEFAULT_SYNC_METHOD)
+#define DEFAULT_SYNC_METHOD PLATFORM_DEFAULT_SYNC_METHOD
+#else			/* !defined(PLATFORM_DEFAULT_SYNC_METHOD) */
 #if defined(OPEN_DATASYNC_FLAG)
 #define DEFAULT_SYNC_METHOD		SYNC_METHOD_OPEN_DSYNC
 #elif defined(HAVE_FDATASYNC)
@@ -132,6 +136,7 @@ typedef uint32 TimeLineID;
 #else
 #define DEFAULT_SYNC_METHOD		SYNC_METHOD_FSYNC
 #endif
+#endif
 
 /*
  * Limitation of buffer-alignment for direct IO depends on OS and filesystem,
diff --git a/src/include/port/linux.h b/src/include/port/linux.h
index b9498b2..5a88590 100644
--- a/src/include/port/linux.h
+++ b/src/include/port/linux.h
@@ -12,3 +12,12 @@
  * to have a kernel version test here.
  */
 #define HAVE_LINUX_EIDRM_BUG
+
+/*
+ * The definition of O_SYNC changed in Linux kernel headers starting with
+ * 2.6.33.  This caused PostgreSQL to change the wal_sync_method default from
+ * fdatasync to open_datasync.  Since fdatasync is more tested on Linux and has
+ * better performance, default to fdatasync on Linux.
+ */
+#define PLATFORM_DEFAULT_SYNC_METHOD SYNC_METHOD_FDATASYNC
+
-- 
1.7.3.2


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Tom Lane]

Dimitri Fontaine  writes:
> Tom Lane  writes:
>> BTW, I'm not even 100% convinced that the schema shouldn't be part of
>> the extension's name, if we're going to make it work like this.  Is
>> there a reason I shouldn't be able to have both public.myextension
>> and testing.myextension?  If we're constraining all the objects owned by
>> the extension to live in a single schema, this seems perfectly feasible.

> Are you proposing that an extension object is schema qualified?

Dunno, I'm just asking the question.  If it isn't, why not?

Here's another question: if an extension's objects live (mostly or
entirely) in schema X, what happens if the possibly-unprivileged owner
of schema X decides to drop it?  If the extension itself is considered
to live within the schema, then "the whole extension goes away" seems
like a natural answer.  If not, you've got some problems.

> Would we lower creating extension privileges to database owners, too,
> rather than only superusers?

That seems like an orthogonal question.  I can see people wanting both
behaviors though.  Maybe an extension's config file should specify the
privs needed to install it?

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] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Tom Lane  writes:
> I'm with Alvaro on this.  If we're going to have an ALTER EXTENSION SET
> SCHEMA operation, then extensions must have a well-defined schema
> property, and it would be good if that connection were explicitly
> represented in the catalogs.  Digging stuff out of pg_depend sucks;
> we have to do it in some other cases where we didn't foresee the
> connection in advance, but we can see it well enough here.

Ok. So an extension using more than one schema is out, right? Not that I
can see a strong use case, just thinking out loud.

Also, do we keep the current notation or change it, or add to it:
  CREATE EXTENSION foo WITH SCHEMA utils;
  CREATE EXTENSION utils.foo;

I guess if you schema qualify the extension's name we could use that as
the schema name, but remember that the control file name would then be
different from the (qualified and given) extension's name. Surely we
would not try to find the utils.foo.control file, right?

The schema name is also used as a placeholder in the extension SQL
script, so it is somewhat weird to have it in the extension's name.

> BTW, I'm not even 100% convinced that the schema shouldn't be part of
> the extension's name, if we're going to make it work like this.  Is
> there a reason I shouldn't be able to have both public.myextension
> and testing.myextension?  If we're constraining all the objects owned by
> the extension to live in a single schema, this seems perfectly feasible.

Are you proposing that an extension object is schema qualified? Would we
lower creating extension privileges to database owners, too, rather than
only superusers?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?

[Tom Lane]

Daniel Farina  writes:
> Well, why not? I was in the middle of writing it. But I'm open to
> other approaches, the real motivating issue could be stated simply as:

> "pg_dump --clean should produce output that can be run on empty, full
> or partially-full databases in a transaction without a non-zero error
> code"

[ shrug... ]  I'm less than convinced that that's an important goal.
If you don't know what it is you're overwriting, maybe you should
stop and think what you're doing, instead of expecting pg_dump to
silently stomp on portions of whatever you already have.

What you're proposing would maybe be useful for overwriting a database
that contains portions of what is in the source database, but what's
the use of that?  You could just as well dropdb and start fresh.  The
interesting case here is where the target db has *more* objects than
are in the dump, and I really doubt that there is any self-consistent
behavior that pg_dump can automatically provide for such cases.  It
can't drop objects it doesn't know about, and it also has no hope of
ensuring that their relationships to the replaced objects remain
consistent.  Silently wiping out FKs, for instance, seems like a pretty
bad idea.

So, basically, I've never seen any fully credible use case for pg_dump
--clean, and this proposal does nothing to fix that.  I don't see the
argument for putting a large amount of work down that rathole.
Especially not if the only benefit you get out of it is not looking
at error messages.  In most cases you'd *better* study those error
messages, to see what manual cleanup you're going to need to undertake.

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] ALTER OBJECT any_name SET SCHEMA name

[Tom Lane]

Dimitri Fontaine  writes:
> Alvaro Herrera  writes:
>> Frankly, the get_extension_namespace bit still feels wrong to me.  I
>> would have the namespace be present in the pg_extension catalog, even if
>> it's not part of the primary key.

> Well, I'm thinking that:
>  - namespace columns in the catalogs are actually all for objects that
>live in a schema and extension do not

I'm with Alvaro on this.  If we're going to have an ALTER EXTENSION SET
SCHEMA operation, then extensions must have a well-defined schema
property, and it would be good if that connection were explicitly
represented in the catalogs.  Digging stuff out of pg_depend sucks;
we have to do it in some other cases where we didn't foresee the
connection in advance, but we can see it well enough here.

BTW, I'm not even 100% convinced that the schema shouldn't be part of
the extension's name, if we're going to make it work like this.  Is
there a reason I shouldn't be able to have both public.myextension
and testing.myextension?  If we're constraining all the objects owned by
the extension to live in a single schema, this seems perfectly feasible.

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] ALTER TABLE ... IF EXISTS feature?

[Daniel Farina]

On Fri, Nov 5, 2010 at 8:44 AM, Tom Lane  wrote:
> That spells "large maintenance burden" to me, even if any one command
> would be relatively simple to fix.  And we haven't even reached the
> question of whether pg_dump could use these things usefully; I suspect
> that the bottom-line issue there might be something else entirely.

Well, why not? I was in the middle of writing it. But I'm open to
other approaches, the real motivating issue could be stated simply as:

"pg_dump --clean should produce output that can be run on empty, full
or partially-full databases in a transaction without a non-zero error
code"

ALTER IF EXISTS just struck me as one such mechanism that is familiar
looking, at least.

Also take note that I suffered from some syntactic dyslexia above, IF
EXISTS doesn't appear to ever occur after the identifier, so the
example would be:

ALTER TABLE IF EXISTS foo DROP CONSTRAINT IF EXISTS bar;

To me this reads fine, although I agree that it could be annoying to
main this optional-ness on a large series of commands.

One might be able to imagine a special backend function to do this
missing-identifier guarding, that may shorten the whole thing up a
bit...any suggestions, in that line of thinking or others?

fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Alvaro Herrera  writes:
> Frankly, the get_extension_namespace bit still feels wrong to me.  I
> would have the namespace be present in the pg_extension catalog, even if
> it's not part of the primary key.  This would let you answer the
> question: what schema did I install this extension in? (and display it
> in \dx commands etc)

dim=# \dx
   List of extensions
 Schema |Name| Version  |  Description
++--+--
 utils  | lo | 9.1devel | managing Larg
 utils  | unaccent   | 9.1devel | text search d
 utils  | adminpack  | 9.1devel | Administrativ
 utils  | moddatetime| 9.1devel | functions for
 utils  | isn| 9.1devel | data types fo
...

I've cut in there obviously, but you get the idea.

> If you don't know that, then the ability to change
> it to another schema looks incomplete.  Since we're now saying that
> moving the extension to another schema is a first-class operation, I
> think the data should be represented more explicitely in the catalog
> rather than being derived from pg_depend contents.

Well, I'm thinking that:

 - namespace columns in the catalogs are actually all for objects that
   live in a schema and extension do not

 - pg_depend is a good source here, as it is for get_constraint_index
   and some other functions

 - maybe the problem is that parts of this patch should go into the main
   extension's one, where there's already the with schema foo feature,
   rather than be introduced in the alter extension set schema patch?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Fix for seg picksplit function

[Alexander Korotkov]

Hello Yeb,

Thank you for review and code refactoring.

PS: when comparing with gbt_num_picksplit, I noticed that that one does not
> update v->spl_ldatum and spl_rdatum to the union datums, but initializes
> these to 0 at the beginning and never seems to update them. Not sure if this
> is a problem since the num_picksplit stuff seems to work well.
>
Actually gbt_num_picksplit updates v->spl_ldatum and spl_rdatum
inside gbt_num_bin_union function.


With best regards,
Alexander Korotkov.

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

[Alvaro Herrera]

Excerpts from Jan Urbański's message of vie nov 05 04:19:07 -0300 2010:

> PS: I'm wondering if there's any noticable slowdown from always starting
> a subxact before doing SPI. Plperl users seemed not to notice, so I
> guess I shouldn't worry.

I think it's more "plperl users have to put up with it" rather than "not
notice".

-- 
Álvaro Herrera 
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] ALTER OBJECT any_name SET SCHEMA name

[Alvaro Herrera]

Excerpts from Dimitri Fontaine's message of vie nov 05 06:49:34 -0300 2010:
> Alvaro Herrera  writes:
> > Hmm, seeing the amount of new includes in extension.c, I wonder if it'd
> > be better to move AlterExtensionNamespace to alter.c.
> 
> It was mainly missing includes cleanup. The guts of the function is now
> so short I can inline it in this mail:

Ah, good.

> Please find attached v3 patch with #include cleanup.

Frankly, the get_extension_namespace bit still feels wrong to me.  I
would have the namespace be present in the pg_extension catalog, even if
it's not part of the primary key.  This would let you answer the
question: what schema did I install this extension in? (and display it
in \dx commands etc) If you don't know that, then the ability to change
it to another schema looks incomplete.  Since we're now saying that
moving the extension to another schema is a first-class operation, I
think the data should be represented more explicitely in the catalog
rather than being derived from pg_depend contents.

-- 
Álvaro Herrera 
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] Fix for seg picksplit function

[Yeb Havinga]

Hello Alexander,

Here follows a review of your patch.

Hackers,

Seg contrib module contains the same bug in  picksplit function as 
cube  contrib module.

Good catch! :-)
Also, Guttman's split algorithm is not needed in unidimensional case, 
because sorting based algorithm is good in this case.
I had some doubts whether this is true in the general case, instead of 
the given example. I increased the interval width in your example to 
0.25*b instead of 0.5*b, with the purpose to increase overlaps 
between intervals. Though the performance gain was less, it was still 
faster than Guttmans algorithm. To make things worse I also tested with 
an interval with of 1*b, resulting in a lot of overlaps and compared 
several overlap queries. The sorting algorithm was 25% to 40% faster on 
searches. Index creation time with the sorting algorithm is also a 
fraction of the original creation time.


Since this testing could be part of a review, I looked at the code as 
well and listed myself as reviewer on the commitfest.


Comparing with gbt_num_picksplit reveals some differences with sort 
array intialization and size, the former's sort array starts at index 1 
(FirstOffsetNumber), your implementation starts at 0 for sorting and 
hence the size of the sorting array can be one element less. I prefer 
your way of sort array initialization; gbt_num_pickplits's use of 
FirstOffsetNumber of the qsort array seems to mix a define from the 
gist/btree namespace for no reason and might even lead to confusion.


The remaining part of the new picksplit function puts the segs into left 
or right, I think the code is easier to understand if there was only one 
for loop from i=1 to 1 < maxoff, for the current code I had to verify 
that all sort array entries were really used with the two seperate loops 
that also skipped the first value. I edited the code a bit, and also 
used seg_union to initialize/palloc the datum values. Finally, waste and 
firsttime variables were initialized but not used anymore, so removed.


Attached is a revised patch.

regards,
Yeb Havinga

PS: when comparing with gbt_num_picksplit, I noticed that that one does 
not update v->spl_ldatum and spl_rdatum to the union datums, but 
initializes these to 0 at the beginning and never seems to update them. 
Not sure if this is a problem since the num_picksplit stuff seems to 
work well.


diff --git a/contrib/seg/seg.c b/contrib/seg/seg.c
index 930a35b..93895ef 100644
--- a/contrib/seg/seg.c
+++ b/contrib/seg/seg.c
@@ -292,38 +292,42 @@ gseg_penalty(GISTENTRY *origentry, GISTENTRY *newentry, float *result)
 	return (result);
 }
 
+/*
+ * Auxiliary structure for picksplit method.
+ */
+typedef struct
+{
+	int index;
+	SEG *data;
+} PickSplitSortItem;
 
+/*
+ * Compare function for PickSplitSortItem based on seg_cmp.
+ */
+static int
+sort_item_cmp(const void *a, const void *b)
+{
+	PickSplitSortItem *i1 = (PickSplitSortItem *)a;
+	PickSplitSortItem *i2 = (PickSplitSortItem *)b;
+	return seg_cmp(i1->data, i2->data);
+}
 
 /*
 ** The GiST PickSplit method for segments
-** We use Guttman's poly time split algorithm
+** Algorithm based on sorting. Incoming array of segs is sorting using seg_cmp
+** function. After that first half of segs goes to the left datum, and the
+** second half of segs goes to the right datum.
 */
 GIST_SPLITVEC *
 gseg_picksplit(GistEntryVector *entryvec,
 			   GIST_SPLITVEC *v)
 {
-	OffsetNumber i,
-j;
-	SEG		   *datum_alpha,
-			   *datum_beta;
+	OffsetNumber i;
 	SEG		   *datum_l,
 			   *datum_r;
-	SEG		   *union_d,
-			   *union_dl,
-			   *union_dr;
-	SEG		   *inter_d;
-	bool		firsttime;
-	float		size_alpha,
-size_beta,
-size_union,
-size_inter;
-	float		size_waste,
-waste;
-	float		size_l,
-size_r;
+	PickSplitSortItem	*sortItems;
 	int			nbytes;
-	OffsetNumber seed_1 = 1,
-seed_2 = 2;
+	OffsetNumber seed_2;
 	OffsetNumber *left,
 			   *right;
 	OffsetNumber maxoff;
@@ -332,111 +336,52 @@ gseg_picksplit(GistEntryVector *entryvec,
 	fprintf(stderr, "picksplit\n");
 #endif
 
-	maxoff = entryvec->n - 2;
-	nbytes = (maxoff + 2) * sizeof(OffsetNumber);
+	maxoff = entryvec->n - 1;
+	nbytes = (maxoff + 1) * sizeof(OffsetNumber);
+	sortItems = (PickSplitSortItem *)palloc(maxoff * sizeof(PickSplitSortItem));
 	v->spl_left = (OffsetNumber *) palloc(nbytes);
 	v->spl_right = (OffsetNumber *) palloc(nbytes);
 
-	firsttime = true;
-	waste = 0.0;
-
-	for (i = FirstOffsetNumber; i < maxoff; i = OffsetNumberNext(i))
+	/*
+	 * Preparing auxiliary array and sorting.
+	 */
+	for (i = FirstOffsetNumber; i <= maxoff; i = OffsetNumberNext(i))
 	{
-		datum_alpha = (SEG *) DatumGetPointer(entryvec->vector[i].key);
-		for (j = OffsetNumberNext(i); j <= maxoff; j = OffsetNumberNext(j))
-		{
-			datum_beta = (SEG *) DatumGetPointer(entryvec->vector[j].key);
-
-			/* compute the wasted space by unioning these guys */
-			/* size_waste = size_union - size_inter; */
-			union_d = seg_union(datum_alpha, datum_beta);
-			rt_seg_size(

Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?

[Tom Lane]

Robert Haas  writes:
> On Nov 5, 2010, at 10:49 AM, Tom Lane  wrote:
>> I think you've phrased the question backwards.  Why *should* we support
>> that large increment of complexity?  The use-cases seem pretty few and
>> far between.

> Obviously we have different definitions of "a large increment in complexity".

Well,

(1) the proposal affects a large number of commands

(2) in some cases the correct behavior isn't obvious (I note Daniel's
example had *two* IF EXISTS options in one command...)

(3) it raises the bar of expectation for every future ALTER command

That spells "large maintenance burden" to me, even if any one command
would be relatively simple to fix.  And we haven't even reached the
question of whether pg_dump could use these things usefully; I suspect
that the bottom-line issue there might be something else entirely.

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] ALTER TABLE ... IF EXISTS feature?

[Robert Haas]

On Nov 5, 2010, at 10:49 AM, Tom Lane  wrote:
> Daniel Farina  writes:
>> Is there any reason why Postgres should not support an "ALTER TABLE
>> tablename [IF EXISTS]" feature?
> 
> I think you've phrased the question backwards.  Why *should* we support
> that large increment of complexity?  The use-cases seem pretty few and
> far between.

Obviously we have different definitions of "a large increment in complexity".

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER TABLE ... IF EXISTS feature?

[Tom Lane]

Daniel Farina  writes:
> Is there any reason why Postgres should not support an "ALTER TABLE
> tablename [IF EXISTS]" feature?

I think you've phrased the question backwards.  Why *should* we support
that large increment of complexity?  The use-cases seem pretty few and
far between.

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] SQL/MED estimated time of arrival?

[Tom Lane]

Shigeru HANADA  writes:
> Thanks, now I see your point.  Current FdwRoutine has no appropriate
> function because Open is called from ExecutorStart which is used by
> EXPLAIN too.

> But then we have mismatch between executor node interface and FDW
> interface about BeginScan.  Should we add new function such as
> ExecBeginNode and call ExecBeginXXX for each plan node?

That seems like a massive amount of new code, and wasted cycles during
every query startup, to fix a very small problem.

There's a flag EXEC_FLAG_EXPLAIN_ONLY that tells node Init functions
whether the query is going to be run "for real" or only EXPLAINed.
Use that to decide whether to do any real work.

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] SQL/MED estimated time of arrival?

[Shigeru HANADA]

On Fri, 5 Nov 2010 16:27:49 +0900
Itagaki Takahiro  wrote:
> On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
>  wrote:
> >> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
> >> It might be good to have a separated "beginscan" method if we use
> >> asynchronous scans in multiple foreign servers in one query
> >
> > You mean that separated beginscan (FdwRoutine.BeginScan?) starts
> > asynchronous query and returns immediately, and FdwRoutine.Iterate
> > returns result of that query?
> 
> Yes. Each BeginScan() in the executor node tree will be called at
> the beginning of executor's run. The callback should not block
> the caller. OTOH, Iterate() are called at the first time tuples
> in the node are required.

Thanks, now I see your point.  Current FdwRoutine has no appropriate
function because Open is called from ExecutorStart which is used by
EXPLAIN too.

But then we have mismatch between executor node interface and FDW
interface about BeginScan.  Should we add new function such as
ExecBeginNode and call ExecBeginXXX for each plan node?

New Query Processing Control Flow would be:
# based on README of executor directory

CreateQueryDesc

ExecutorStart
CreateExecutorState
creates per-query context
switch to per-query context to run ExecInitNode
ExecInitNode --- recursively scans plan tree
CreateExprContext
creates per-tuple context
ExecInitExpr

ExecutorRun
ExecBeginNode(new) --- recursively scans plan tree
call ExecBeginXXXS for each plan node
ExecProcNode --- recursively called in per-query context
ExecEvalExpr --- called in per-tuple context
ResetExprContext --- to free memory

ExecutorEnd
ExecEndNode --- recursively releases resources
FreeExecutorState
frees per-query context and child contexts

FreeQueryDesc

> PL/Proxy has a similar functionality with RUN ON ALL to start queries
> in parallel. So, I think it's a infrastructure commonly required.
I noticed the lack of consideration about cache invalidation from
reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)

Regards,
--
Shigeru Hanada



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Alvaro Herrera  writes:
> Hmm, seeing the amount of new includes in extension.c, I wonder if it'd
> be better to move AlterExtensionNamespace to alter.c.

It was mainly missing includes cleanup. The guts of the function is now
so short I can inline it in this mail:

targetObjects = listDependentObjects(object);

for (i = 0; i < targetObjects->numrefs; i++)
{
ObjectAddress *thisobj = targetObjects->refs + i;

elog(DEBUG1, "SET SCHEMA on %u: %s",
 thisobj->objectId, getObjectDescription(thisobj));

AlterObjectNamespace_internal(thisobj, nspOid);
}

So really, I don't think moving it to alter.c would do any better,
considering that you would then have this file include dependency
related function.

Please find attached v3 patch with #include cleanup.
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

*** a/src/backend/catalog/pg_depend.c
--- b/src/backend/catalog/pg_depend.c
***
*** 20,25 
--- 20,27 
  #include "catalog/indexing.h"
  #include "catalog/pg_constraint.h"
  #include "catalog/pg_depend.h"
+ #include "catalog/pg_extension.h"
+ #include "catalog/pg_namespace.h"
  #include "miscadmin.h"
  #include "utils/fmgroids.h"
  #include "utils/lsyscache.h"
***
*** 643,645  get_index_constraint(Oid indexId)
--- 645,699 
  
  	return constraintId;
  }
+ 
+ /*
+  * get_extension_namespace
+  *		Given the OID of an extension, return the OID of the schema it
+  *		depends on, or InvalidOid when not found
+  */
+ Oid
+ get_extension_namespace(Oid extensionId)
+ {
+ 	Oid			nspId = InvalidOid;
+ 	Relation	depRel;
+ 	ScanKeyData key[3];
+ 	SysScanDesc scan;
+ 	HeapTuple	tup;
+ 
+ 	/* Search the dependency table for the index */
+ 	depRel = heap_open(DependRelationId, AccessShareLock);
+ 
+ 	ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(ExtensionRelationId));
+ 	ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(extensionId));
+ 	ScanKeyInit(&key[2],
+ Anum_pg_depend_objsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(0));
+ 
+ 	scan = systable_beginscan(depRel, DependDependerIndexId, true,
+ 			  SnapshotNow, 3, key);
+ 
+ 	while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ 	{
+ 		Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
+ 
+ 		if (deprec->refclassid == NamespaceRelationId &&
+ 			deprec->refobjsubid == 0 &&
+ 			deprec->deptype == DEPENDENCY_NORMAL)
+ 		{
+ 			nspId = deprec->refobjid;
+ 			break;
+ 		}
+ 	}
+ 
+ 	systable_endscan(scan);
+ 	heap_close(depRel, AccessShareLock);
+ 
+ 	return nspId;
+ }
*** a/src/backend/commands/alter.c
--- b/src/backend/commands/alter.c
***
*** 22,27 
--- 22,28 
  #include "commands/conversioncmds.h"
  #include "commands/dbcommands.h"
  #include "commands/defrem.h"
+ #include "commands/extension.h"
  #include "commands/proclang.h"
  #include "commands/schemacmds.h"
  #include "commands/tablecmds.h"
***
*** 189,194  ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt)
--- 190,199 
  			AlterConversionNamespace(stmt->object, stmt->newschema);
  			break;
  
+ 		case OBJECT_EXTENSION:
+ 			AlterExtensionNamespace(stmt->object, stmt->newschema);
+ 			break;
+ 
  		case OBJECT_FUNCTION:
  			AlterFunctionNamespace(stmt->object, stmt->objarg, false,
     stmt->newschema);
***
*** 334,339  AlterObjectNamespace(Relation rel, int cacheId,
--- 339,436 
  		NamespaceRelationId, oldNspOid, nspOid);
  }
  
+ /*
+  * Do the SET SCHEMA depending on the object class.
+  *
+  * We only consider objects that have a namespace and that can exist
+  * without depending on another object (like a table) which will
+  * have its dependencies follow the SET SCHEMA operation.
+  */
+ void
+ AlterObjectNamespace_internal(ObjectAddress *thisobj, Oid nspOid)
+ {
+ 	switch (getObjectClass(thisobj))
+ 	{
+ 		case OCLASS_CLASS:
+ 		{
+ 			Relation classRel;
+ 			Relation rel = relation_open(thisobj->objectId, RowExclusiveLock);
+ 
+ 			switch (rel->rd_rel->relkind)
+ 			{
+ case RELKIND_COMPOSITE_TYPE:
+ 	/*
+ 	 * just skip the pg_class entry, we have a pg_type
+ 	 * entry too
+ 	 */
+ 	break;
+ 
+ default:
+ 	classRel = heap_open(RelationRelationId, RowExclusiveLock);
+ 	AlterRelationNamespaceInternal(classRel,
+    RelationGetRelid(rel),
+    RelationGetNamespace(rel),
+    nspOid,
+    true);
+ 	heap_close(classRel, RowExclusiveLock);
+ 	break;
+ 			}
+ 			relation_close(rel, RowExclusiveLock);
+ 			break;
+ 		}
+ 
+ 		case OCLASS_PROC:
+ 			AlterFunctionNamespace_oid(thisobj->objectId, nspOid);
+ 			break;
+ 
+ 		case OCLASS_TYPE:
+ 		{
+ 			/* don't allow direct alteration of array types, skip */
+

Re: [HACKERS] ALTER OBJECT any_name SET SCHEMA name

[Dimitri Fontaine]

Alvaro Herrera  writes:
> The has_privs_of_role() call has the wrong ACL_KIND argument in the
> error report.

Ah yes, I missed the acl_kind. It's a parameter of the function in the
v7 patch, attached.

> (Nitpick: don't use "e.g." at the end of the phrase.  It seems strange
> to me.)

Fixed too. I also added documentation of the new forms of the ALTER
commands, as it seems we're heading to something which needs it :)

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

*** a/doc/src/sgml/ref/alter_conversion.sgml
--- b/doc/src/sgml/ref/alter_conversion.sgml
***
*** 23,28  PostgreSQL documentation
--- 23,29 
  
  ALTER CONVERSION name RENAME TO new_name
  ALTER CONVERSION name OWNER TO new_owner
+ ALTER CONVERSION name SET SCHEMA new_schema
  
   

***
*** 75,80  ALTER CONVERSION name OWNER TO new_owner
--- 76,90 
   
  
 
+ 
+
+ new_schema
+ 
+  
+   The new schema of the conversion.
+  
+ 
+

   
  
*** a/doc/src/sgml/ref/alter_opclass.sgml
--- b/doc/src/sgml/ref/alter_opclass.sgml
***
*** 23,28  PostgreSQL documentation
--- 23,29 
  
  ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
  ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner
+ ALTER OPERATOR CLASS name USING index_method SET SCHEMA new_schema
  
   

***
*** 85,90  ALTER OPERATOR CLASS name USING new_schema
+ 
+  
+   The new schema for the operator class.
+  
+ 
+
   
   
  
*** a/doc/src/sgml/ref/alter_operator.sgml
--- b/doc/src/sgml/ref/alter_operator.sgml
***
*** 22,27  PostgreSQL documentation
--- 22,28 
   
  
  ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) OWNER TO new_owner
+ ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) SET SCHEMA new_schema
  
   

***
*** 85,90  ALTER OPERATOR name ( { left_type
  
 
+ 
+
+ new_schema
+ 
+  
+   The new schema of the operator.
+  
+ 
+

   
  
*** a/doc/src/sgml/ref/alter_opfamily.sgml
--- b/doc/src/sgml/ref/alter_opfamily.sgml
***
*** 31,36  ALTER OPERATOR FAMILY name USING index_method RENAME TO new_name
  ALTER OPERATOR FAMILY name USING index_method OWNER TO new_owner
+ ALTER OPERATOR FAMILY name USING index_method SET SCHEMA new_schema
  
   

***
*** 200,205  ALTER OPERATOR FAMILY name USING new_schema
+ 
+  
+   The new schema for the operator family.
+  
+ 
+
   
  

*** a/doc/src/sgml/ref/alter_tsconfig.sgml
--- b/doc/src/sgml/ref/alter_tsconfig.sgml
***
*** 33,38  ALTER TEXT SEARCH CONFIGURATION name
--- 33,39 
  DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ]
  ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name
  ALTER TEXT SEARCH CONFIGURATION name OWNER TO new_owner
+ ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema
  
   

***
*** 123,128  ALTER TEXT SEARCH CONFIGURATION name OWNER TO 
  
 
+ 
+
+ new_schema
+ 
+  
+   The new schema for the text search configuration.
+  
+ 
+
   
  

*** a/doc/src/sgml/ref/alter_tsdictionary.sgml
--- b/doc/src/sgml/ref/alter_tsdictionary.sgml
***
*** 26,31  ALTER TEXT SEARCH DICTIONARY name (
--- 26,32 
  )
  ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name
  ALTER TEXT SEARCH DICTIONARY name OWNER TO new_owner
+ ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema
  
   

***
*** 96,101  ALTER TEXT SEARCH DICTIONARY name OWNER TO 
  
 
+ 
+
+ new_schema
+ 
+  
+   The new schema of the text search dictionary.
+  
+ 
+
   
  

*** a/doc/src/sgml/ref/alter_tsparser.sgml
--- b/doc/src/sgml/ref/alter_tsparser.sgml
***
*** 22,27  PostgreSQL documentation
--- 22,28 
   
  
  ALTER TEXT SEARCH PARSER name RENAME TO new_name
+ ALTER TEXT SEARCH PARSER name SET SCHEMA new_schema
  
   

***
*** 60,65  ALTER TEXT SEARCH PARSER name RENAME TO 
--- 61,75 
   
  
 
+ 
+
+ new_schema
+ 
+  
+   The new schema of the text search parser.
+  
+ 
+
   
   
  
*** a/doc/src/sgml/ref/alter_tstemplate.sgml
--- b/doc/src/sgml/ref/alter_tstemplate.sgml
***
*** 22,27  PostgreSQL documentation
--- 22,28 
   
  
  ALTER TEXT SEARCH TEMPLATE name RENAME TO new_name
+ ALTER TEXT SEARCH TEMPLATE name SET SCHEMA new_schema
  
   

***
*** 60,65  ALTER TEXT SEARCH TEMPLATE name RENAME TO 
  
 
+ 
+
+ new_schema
+ 
+  
+   The new schema of the text search template.
+  
+ 
+
   
   
  
*** a/src/backend/catalog/dependency.c
--- b/src/backend/catalog/dependency.c
***
*** 2706,2711  getOb

Re: [HACKERS] todo: plpgsql - tool to track code coverage

[Cédric Villemain]

2010/11/5 Pavel Stehule :
> Hello
>
> I am looking on http://kputnam.github.com/piggly/ site. I am thinking,
> so can be very easy write low level support to plpgsql. And this can
> to solve a some issue of plpgsql with lazy SQL full checking.
>
> A raising syntax error of some SQL inside plpgsql after a months of
> production usage is still problem. This or similar tool can minimalize
> risks.

Yes, this is interesting.

-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ALTER TABLE ... IF EXISTS feature?

[Daniel Farina]

Hello List,

Is there any reason why Postgres should not support an "ALTER TABLE
tablename [IF EXISTS]" feature? (And similar for other ALTER
OBJECTTYPE)

For example, a hypothetical statement that attempts to drop a
constraint in a *completely* optional manner would look like the
following:

  ALTER TABLE IF EXISTS foo DROP CONSTRAINT bar IF EXISTS;

If you think this is already a reasonable idea, you can stop reading
now and express approval at the general idea. If you are curious as to
why *I* encountered it, continue reading.


It would be very useful if "pg_dump --clean" produced output that
could be run on an empty database without errors so that it is easier
to monitor the process exit status of pg_restore to check for problems
as well as enabling the use with one-transaction restore. The former
is a vital feature for organizations that manage the creation and
monitoring of many backups.

In addition, these semantics are pretty familiar: they are not
dissimilar from "make clean".

As-is, you are very likely to hit errors upon restoring a dump with
--clean in most schemas. The degree of "most" here includes "schemas
that use any form of foreign key" as a subset, so this is a rather
large set. I encountered this problem through first hoping that
meeting this requirement was entirely possible by changing pg_dump
alone. The first hurdle was:

  DROP TABLE foo;

A straightforward pg_dump change can change this to:

  DROP TABLE foo IF EXISTS;

Things then got tricky when dealing with the constraints on domains
and tables. In order to clean tables, for example, pg_dump will
reverse its topological sort on the dependencies and emit drop
statements for the dumpable objects individually. This ensures that
within the scope of objects to be dumped that all dependent objects
(the constraints, in this case) are dropped first, then finally the
table. Thus, one will see the following prior to dropping the table:

  ALTER TABLE foo DROP CONSTRAINT bar;

While one can easily fix part of a problem by emitting the following instead:

  ALTER TABLE foo DROP CONSTRAINT bar IF EXISTS;

One is still stuck on not being able to not-error when there's a
missing "foo" table. And so there is an impasse that requires some
grammar and DDL machinery changes on the server side.

fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/MED estimated time of arrival?

[Itagaki Takahiro]

On Fri, Nov 5, 2010 at 4:00 PM, Shigeru HANADA
 wrote:
>> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
>> It might be good to have a separated "beginscan" method if we use
>> asynchronous scans in multiple foreign servers in one query
>
> You mean that separated beginscan (FdwRoutine.BeginScan?) starts
> asynchronous query and returns immediately, and FdwRoutine.Iterate
> returns result of that query?

Yes. Each BeginScan() in the executor node tree will be called at
the beginning of executor's run. The callback should not block
the caller. OTOH, Iterate() are called at the first time tuples
in the node are required.

PL/Proxy has a similar functionality with RUN ON ALL to start queries
in parallel. So, I think it's a infrastructure commonly required.

-- 
Itagaki Takahiro

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] todo: plpgsql - tool to track code coverage

[Pavel Stehule]

Hello

I am looking on http://kputnam.github.com/piggly/ site. I am thinking,
so can be very easy write low level support to plpgsql. And this can
to solve a some issue of plpgsql with lazy SQL full checking.

A raising syntax error of some SQL inside plpgsql after a months of
production usage is still problem. This or similar tool can minimalize
risks.

Regards

Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] why does plperl cache functions using just a bool for is_trigger

[Jan Urbański]

On 04/11/10 20:43, Hannu Krosing wrote:
> On Thu, 2010-11-04 at 11:07 -0600, Alex Hunsaker wrote:
>> On Thu, Nov 4, 2010 at 03:54, Hannu Krosing  wrote:
> try:
> plpy.execute("insert into foo values(1)")
> except plpy.UniqueViolation, e:
> plpy.notice("Ooops, you got yourself a SQLSTATE %d", e.sqlstate)

 Are you sure that having each try/except use a subtransaction is the
 right way to do it ?
>>
>> I assumed the try was purely so you could 'catch' things.  And did not
>> mean run it in a subtransaction (without the try block it still runs
>> in one).

Nice, lots of input before I was able to read my email :o)

I'm planning to make plpython work just like plperl with regards to
trapping errors from SPI. As Tom noticed, messing with the error
handling mechanism of Python is not a good way of implementing this.

So, basically each plpy.execute() would be internally executed inside a
subtransaction and if SPI would return an error, it would be transformed
into a Python exception and returned to the Python runtime, which will
then handle it as it would handle and exception coming form a C extension.

It would be interesting to provide an explicit way to start
subtransactions, like Hannu proposed:

with plpy.subtransaction():
   plpy.execute("select foo()")
   plpy.execute("select bar()")
   plpy.execute("select baz()")

(of course that would only work for Python 2.6+, where with blocks were
introduced, we'd have to also provide the primitive functions of
plpy.enter_subxact() and plpy.commit_subxact() (names took at random))

It would set a flag somewhere and start an explicit subtransaction -
after that plpy.execute() would just go ahead and call SPI. This way you
would be sure that you executed a bunch of statements atomically.
Implementing that iss not very high on my priority list, though, as you
can always just wrap foo() bar() and baz() in a separate stored
procedure and call that, thus achieving atomicity (or am I wrong here?).

Cheers,
Jan

PS: I'm wondering if there's any noticable slowdown from always starting
a subxact before doing SPI. Plperl users seemed not to notice, so I
guess I shouldn't worry.

J

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SQL/MED estimated time of arrival?

[Shigeru HANADA]

On Thu, 4 Nov 2010 18:22:52 +0900
Itagaki Takahiro  wrote:
> On Thu, Nov 4, 2010 at 6:04 PM, Shigeru HANADA
>  wrote:
> > For example:
> > * PRIMARY ACCESS_METHOD -> HANDLER of FOREIGN DATA WRAPPER
> > * am_scancost()         -> FdwRoutine.EstimateCosts()
> > * am_open()             -> FdwRoutine.Open()
> > * am_beginscan()        -> first call of FdwRoutine.Iterate()?
> 
> It might be good to have a separated "beginscan" method if we use
> asynchronous scans in multiple foreign servers in one query
> because multiple foreign servers can run their queries in parallel.
> (Imagine that pushing-down aggregate function into each foreign server.)

You mean that separated beginscan (FdwRoutine.BeginScan?) starts
asynchronous query and returns immediately, and FdwRoutine.Iterate
returns result of that query?

Pushing aggregate down to foreign server would be efficient, but need
another hook which can create one ForeignScan node which have "Agg +
ForeignScan" functionality.  Same optimization would be able to apply
for Sort and Limit.  Such optimization should be done in optimizer
with estimated costs?  Or FDW's hook function may change plan tree
which was created by planner?

> I think it is different from "open" because it is called
> before query execution, for example by EXPLAIN.

Right, I've misunderstood.

VTI programmer's guide says that am_open is called before processing
SQL to initialize input or output, and called for not only SELECT but
also other queries using a virtual table such as INSERT and DROP TABLE. 
The am_open would have no counterpart in SQL/MED.

> Do you think you have all counterpart methods for VTI AMs?
> If so, it's a good news ;-)  We could support foreign table
> features as same level as Informix.

Not all, but most of them for read-only access.

VTI supports updating external data and various management tasks via
SQL, but SQL/MED supports (at least in standard) only read access. 
The full set of ACCESS_METHOD functions are:

am_create   CREATE FOREIGN TABLE
am_drop DROP TABLE

am_statsgather statistics (ANALYZE)
am_checkverify data structure and index consistency

am_open initialize access to a virtual table
(might connect to external server)
am_closefinalize access to a virtual table

am_scancost estimate cost of a scan
am_beginscaninitialize scan
am_getbyid  get a tuple by row-id
am_getnext  get next tuple(s)
am_rescan   reset state of scanning
am_endscan  finalize scan

am_insert   insert a tuple and return row-id
am_update   update a tuple by row-id
am_delete   delete a tuple by row-id
am_truncate truncate table

VTI might be similar to storage engine of MySQL or heap-am of PG,
rather than SQL/MED of PG.

Like FOREIGN INDEX of HiRDB, Informix has Virtual Index Interface, and
am_getbyid is used to get a tuple by row-id.  I'll research more about
VTI and VII for revising design of SQL/MED.

Regards,
--
Shigeru Hanada



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers