date:20090919

David Fetter da...@fetter.org writes:
 On Sat, Sep 19, 2009 at 11:50:35AM -0400, Andrew Dunstan wrote:
 What benefit would we  gain from making general crypto part of the
 core?

 People may wish to encrypt things in the database.

That is not an argument why it has to be in core rather than an add-on.

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] Standalone backends run StartupXLOG in an incorrect environment

I realized the truth of $SUBJECT while reading this report:
http://archives.postgresql.org/pgsql-general/2009-09/msg00712.php

In a standalone backend, postgres.c tries to run StartupXLOG after
having done only BaseInit(), which means that we don't have a PGPROC
(hence can't take LWLocks much less heavyweight locks) and we have not
totally finished initializing the bufmgr either.  This is apparently
enough for the normal case where there's no log replay to do; but
as the above report shows, it's completely inadequate for some of the
more complex code paths in replay.  I suspect this has been broken
from the beginning.

Fixing this will require rearranging things around InitPostgres
(in particular, I think InitBufferPoolBackend will have to be
called directly from postgres.c).  Since that code got rearranged
quite a bit last month, I'd be hesitant to try to back-patch whatever
fix we come up with for HEAD.  Seeing that we'd never noticed the
problem before, I think it's okay to fix it just in HEAD and not
risk back-patching ... comments?

Also, does this have any impact on the Hot Standby stuff?

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

2009-09-19 Thread Marcos Luis Ortiz Valmaseda

I think the same, These are not arguments to include crypto on the core, due to 
then all users have to think on this aspect to build a simple database, so we 
would be obligating to the users to use this module.

I´d let this module on the contrib 

Regards 

The hurry is enemy of the success: for that reason...Be patient

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

Linux User # 418229
PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/


- Mensaje original -
De: Tom Lane t...@sss.pgh.pa.us
Para: David Fetter da...@fetter.org
CC: Andrew Dunstan and...@dunslane.net, PG Hackers 
pgsql-hackers@postgresql.org
Enviados: Sábado, 19 de Septiembre 2009 6:33:36 GMT -10:00 Hawai
Asunto: Re: [HACKERS] Crypto 

David Fetter da...@fetter.org writes:
 On Sat, Sep 19, 2009 at 11:50:35AM -0400, Andrew Dunstan wrote:
 What benefit would we  gain from making general crypto part of the
 core?

 People may wish to encrypt things in the database.

That is not an argument why it has to be in core rather than an add-on.

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

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

Re: [HACKERS] updated join removal patch

Robert Haas robertmh...@gmail.com writes:
 On Fri, Sep 18, 2009 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 clause_sides_match_join?

 Yes, that's perfect.

Going once ... going twice ... sold.

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

2009-09-19 Thread Andrew Dunstan




David Fetter wrote:

As for the suggestion that we should put other crypto functions into
the  core, AIUI the reason not to is not to avoid problems with US
Export  Regulations (after all, we've shipped source tarballs with
it for many years, including from US repositories), but to make it
easier to use Postgres in places where use of crypto is illegal.



To date, I have not found an example of such a place.  For the record,
would you or anyone seeing this be so kind as to provide one, along
with some kind of evidence that somewhere, such a law has actually
been enforced?
  



There are significant controls in a number of countries. See 
http://rechten.uvt.nl/koops/cryptolaw/cls-sum.htm.


I am not going to do more research on this - I have better things to do 
with my time. The point has been made elsewhere that including general 
crypto in core is entirely unnecessary for any purpose we know of. That 
along with knowledge that its use is at least restricted in several 
countries should surely be argument enough.


This comes up often enough that I'm almost wondering if it deserves an 
FAQ entry.


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] WIP: generalized index constraints

I think we have a reasonable consensus around the name operator
exclusion constraints, Robert Haas's suggestion. I am OK with that
name, and it got support from David Fetter and Tom Lane. As David Fetter
said, it's useful for the name to hint at the API.

Peter had some reasonable objections to that name, but the word unique
just doesn't cut it for this feature. My feature allows constraints
which are more restrictive than a unique constraint; but the final straw
was after a discussion with Tomás in which we determined that you can
also define constraints which are the opposite of unique: all values
must be the same (by using  as the operator*).

I agree with Peter that we should support creating these constraints at
table creation time. This can be supported with the following syntax:

  CONSTRAINT foo_constr (a op, ...)
{ USING INDEX foo_idx | USING method }

and it's also a more declarative syntax for the ALTER TABLE case, and
prevents a series of other problems that Peter pointed out.

There's an important unresolved question with this patch that I need to
address, which just came to light: what about functional/expression
indexes?

Say you have a table foo(a text, b text) and an index on:
  ((a||b)::circle)

You could define an operator constraint like:
  ((a||b)::circle )

and that would be sane. But I suppose I should also allow any expression
with the same signature, like:
  ((b||a)::circle )

[ not a very realistic example, but it seems like it may be useful ]

Does that make sense? Does someone have a better idea? Am I missing
other issues here?

How do I test if two functions/expressions:
  a. are identical?
  b. have matching signatures?

Regards,
Jeff Davis

*: Understandably, there is no strategy for  for most data types.
However, if your constraint is that all values must be the same, it's
quite reasonable to add one and be able to use an index to quickly find
values that are different.


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

Re: [HACKERS] WIP: generalized index constraints

Jeff Davis pg...@j-davis.com writes:
 There's an important unresolved question with this patch that I need to
 address, which just came to light: what about functional/expression
 indexes?

What about them?  It's not clear why you think this requires anything
special.

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

2009-09-19 Thread Mark Mielke

On the subject of crypto law - the laws have relaxed significantly in 
the last decade to the point where it is now generally safe to export 
symmetric encryption up to 128 bits (example: AES), and assymetric 
encryption up to 1024 bits (example: RSA). Many countries still require 
some sort of license, though, which takes the form of a formal request 
may I export this? yes. As a for example, I received approval from 
our company lawyers to re-export the Java runtime with a program we have 
which uses exactly 128 bit symmetric and 1024 bit assymetric to all 
countries except embargoed countries. Since it makes no sense to do 
business in embargoed countries anyways, there is no point in asking at all.


For free / open source software in general, the US has explicit 
exemptions for freely available software on the Internet, for the most 
part because it is impossible for them to control it. In this situation, 
PostgreSQL has a lot more freedom than, say, Oracle, to distribute 
crypto. As a for example, Firefox includes crypto to support SSL and 
certificate checking. Now, many countries also have *import* 
restrictions, so while it's safe to freely export Firefox from the 
United States over the Internet, in some countries, it is *illegal* for 
their own citizens to encrypt their data beyond a certain level. If such 
rules are enforced (I think Australia even had such a rule for a time), 
then it would be the citizen doing the import that is affected. At 
present, I wonder about the status of such things in China. While in 
China, they didn't prevent me from using my high encryption strength VPN 
software to access work - was I breaking the law by importing the 
technology and using it? I don't know, and I didn't really think much 
about it at the time.


All this being said - laws change all the time, and the number of 
countries involved in the equation each which may or may not have rules 
that apply to PostgreSQL at various times, that I still agree with 
Andrew - to go from no-crypto to crypto is a huge change that MAY result 
in downstream consequences which would adversely effect the success of 
PostgreSQL, or may even end up with some PostgreSQL representative in 
the chain defending themselves in a court room.


I think it would be best to leave crypto *outside* of core, but make it 
an extremely easy to add plugin with download at your own risk - if you 
are unsure whether you are allowed to import crypto into your country, 
you are responsible for seeking your own legal counsel.


Java did this with their main software being generally exportable, and 
their unlimited strength crypto libraries requiring a separate 
download with appropriate warnings to keep Sun happy that they would not 
be held legally responsible if somebody did misuse the software.


I work for a telecommunications company which requires crypto in most 
software components, so this stuff is taken very seriously. The last 
thing you want to see on television is a terrorist using an untraceable 
secure line with your company's brand name on the front, as they lop 
off the head of a reporter. There is a level of responsibility required 
for such things both from a business perspective and from a ethics 
perspective.


Cheers,
mark


On 09/19/2009 01:55 PM, Andrew Dunstan wrote:


David Fetter wrote:

As for the suggestion that we should put other crypto functions into
the  core, AIUI the reason not to is not to avoid problems with US
Export  Regulations (after all, we've shipped source tarballs with
it for many years, including from US repositories), but to make it
easier to use Postgres in places where use of crypto is illegal.


To date, I have not found an example of such a place.  For the record,
would you or anyone seeing this be so kind as to provide one, along
with some kind of evidence that somewhere, such a law has actually
been enforced?


There are significant controls in a number of countries. See 
http://rechten.uvt.nl/koops/cryptolaw/cls-sum.htm.


I am not going to do more research on this - I have better things to 
do with my time. The point has been made elsewhere that including 
general crypto in core is entirely unnecessary for any purpose we know 
of. That along with knowledge that its use is at least restricted in 
several countries should surely be argument enough.


This comes up often enough that I'm almost wondering if it deserves an 
FAQ entry.





--
Mark Mielkem...@mielke.cc


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

Re: [HACKERS] WIP: generalized index constraints

On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
 What about them?  It's not clear why you think this requires anything
 special.

From a syntax standpoint, I need to represent one operator for every
index column involved in the constraint. So, if there's a functional
index on ((a||b)::circle), I clearly can't have an exclusion constraint
like (a =, b =).

I see two options:

 1. (expr op), where expr is an expression over table attributes 
that must have the exact signature as the expression for the index.
 2. (index_col op), and then read the expression from the index

and in either case, use that expression for the extra checking that I
need to do: I need to check whether the input heap tuple conflicts with
concurrently inserting heap tuples, and I also need to do a recheck
step.

#1 seems like extra work and complexity, because I need to test for the
correct signature (maybe that's not difficult), and that extra
flexibility is pretty marginal -- I can't think of an obvious case where
you'd want different expressions. Also, it complicates the simple case
of wanting the expressions to match.

#2 is awkward because the expression columns of an index have generated
names, and you would have to write things like (pg_expression_1 ).
Also, it makes the constraint too tied to the index, which is a valid
complaint Peter had.

Perhaps you can point me in the right direction to see if two
expressions/functions have matching signatures? Or, if that is too much
of a pain, perhaps I should just test for equal expressions?

Regards,
Jeff Davis



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

Re: [HACKERS] generic copy options

Emmanuel Cecchet m...@asterdata.com writes:
 [ latest patch version ]

Do we have consensus on the syntax for this patch?  In particular,
what about the question of adding CSV_ to all the CSV-specific option
names?  Emmanuel argued that this is necessary to avoid confusion if
we someday introduce other copy formats that have similar options.
However, I think you could easily turn that argument around.  Any one
COPY command will surely use just one format, and it seems to me that
forcing different formats to use different names for equivalent options
won't simplify life for anybody.  So I'm inclined to think we should
not have the CSV_ prefixes.  (I seem to recall that we had exactly
this discussion when the options were introduced the first time, and
settled on not using format-specific option names.)

One other minor point is that the patch introduces an empty-list
syntax for individual option values, but then treats it the same
as specifying nothing:

 + | '(' ')'{ $$ = NULL; }
 + | /* EMPTY */{ $$ = NULL; }

I'm not convinced this is a a good idea, and in any case I don't see
it documented.  I'm inclined to omit the '(' ')' 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] WIP: generalized index constraints

Jeff Davis pg...@j-davis.com writes:
 On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
 What about them?  It's not clear why you think this requires anything
 special.

 From a syntax standpoint, I need to represent one operator for every
 index column involved in the constraint. So, if there's a functional
 index on ((a||b)::circle), I clearly can't have an exclusion constraint
 like (a =, b =).

 I see two options:

  1. (expr op), where expr is an expression over table attributes 
 that must have the exact signature as the expression for the index.
  2. (index_col op), and then read the expression from the index

You need to do (1), I think, because (2) seems to require using the
index column name.  We have generally felt that the names assigned
to index columns are implementation artifacts that the user ought not
rely on in SQL commands.

 and in either case, use that expression for the extra checking that I
 need to do: I need to check whether the input heap tuple conflicts with
 concurrently inserting heap tuples, and I also need to do a recheck
 step.

I haven't read the patch, but this whole discussion sounds to me like
it means you're trying to plug things in at the wrong level.  Indexes
generally don't care where the values they are storing came from ---
whether it's a simple column or a expression result, it's all the same
to the index.  I don't see why that shouldn't be true for exclusion
constraints too.

BTW, further betraying that I've not read the patch: what exactly are
you doing about the information_schema views?  If we are treating these
things as SQL constraints, one would expect them to show up in
information_schema; but I don't see how to represent them there in any
adequate fashion, even without the expression-index angle.  On the whole
I think we'd be a lot better off to NOT consider them to be constraints,
but just another option for CREATE INDEX.

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

2009-09-19 Thread Peter Eisentraut

On Fri, 2009-09-18 at 18:14 -0700, David Fetter wrote:
 Here's what came out for Mozilla, which, I hope you'll pardon my
 saying so, is a teensy tad more widely used than PostgreSQL has any
 plans to become.
 
 http://www.internetnews.com/government/article.php/3839831/Mozilla+Firefox+Cleared+of+US+Export+Rules.htm
 
 I suggest that we start by putting secure hashing algorithms into the
 core distribution so, should MD5 ever break, we have real
 alternatives, and not done in a panic.

OK, it's late here, but how do you get from that article to a need to
replace MD5?


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

Re: [HACKERS] COPY enhancements

2009-09-19 Thread Bruce Momjian

Tom Lane wrote:
 Josh Berkus j...@agliodbs.com writes:
  It's not as if we don't have the ability to measure performance impact.
   It's reasonable to make a requirement that new options to COPY
  shouldn't slow it down noticeably if those options aren't used.  And we
  can test that, and even make such testing part of the patch review.
 
 Really?  Where is your agreed-on, demonstrated-to-be-reproducible
 benchmark for COPY speed?
 
 My experience is that reliably measuring performance costs in the
 percent-or-so range is *hard*.  It's only after you've added a few of
 them and they start to mount up that it becomes obvious that all those
 insignificant additions really did cost something.
 
 But in any case, I think that having a clear distinction between
 straight data import and data transformation features is a good
 thing.  COPY is already pretty much of an unmanageable monstrosity,
 and continuing to accrete features into it without any sort of structure
 is something we are going to regret.

I have read up on this thread and the new copy syntax thread. I think
there is clearly documented demand for such extensions to COPY.

We are definitely opening the floodgates by allowing COPY to process
invalid data.  I think everyone admits COPY is already quite
complicated, both in its API and C code.

If we are going to add to COPY, I think we need to do it in a way that
has a clean user API, doesn't make the C code any more complicated, and
doesn't introduce a performance impact for people not using these new
features.  If we don't do that, we are going to end up like 'bcp' that
is perpetually buggy, as someone explained.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5

2009-09-19 Thread Jeff Janes

On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at wrote:

 Boszormenyi Zoltan írta:
  Alvaro Herrera írta:
 
  Boszormenyi Zoltan wrote:
 
 
 
  The vague consensus for syntax options was that the GUC
  'lock_timeout' and WAIT [N] extension (wherever NOWAIT
  is allowed) both should be implemented.
 
  Behaviour would be that N seconds timeout should be
  applied to every lock that the statement would take.
 
 
  In
 http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us
  Tom argues that lock_timeout should be sufficient.  I'm not sure what
  does WAIT [N] buy


I disagree with Tom on this point.  *If* I was trying to implement  a server
policy, then sure, it should not be done by embedding the timeout in the SQL
statement.  But I don't think they want this to implement a server policy.
(And if we do, why would we thump the poor victims that are waiting on the
lock, rather than the rogue who decided to take a lock and then camp out on
it?)  The use case for WAIT [N] is not a server policy, but a UI policy.  I
have two ways to do this task.  The preferred way needs to lock a row, but
waiting for it may take too long.  So if I can't get the lock within a
reasonable time, I fall back on a less-preferred but still acceptable way of
doing the task, one that doesn't need the lock.  If we move to a new server,
the appropriate value for the time out does not change, because the
appropriate level is the concern of the UI and the end users, not the
database server.  This wouldn't be scattered all over the application,
either.  In my experience, if you have an application that could benefit
from this, you might have 1 or 2 uses for WAIT [N] out of 1,000+ statements
in the application.  (From my perspective, if there were to be a WAIT [N]
option, it could plug into the statement_timeout mechanism rather than the
proposed lock_timeout mechanism.)

I think that if the use case for a GUC is to set it, run a single very
specific statement, and then unset it, that is pretty clear evidence that
this should not be a GUC in the first place.

Maybe I am biased in this because I am primarily thinking about how I would
use such a feature, rather than how Hans-Juergen intends to use it, and
maybe those uses differ.  Hans-Juergen, could you describe your use case a
little bit more?   Who do is going to be getting these time-out errors, the
queries run by the web-app, or longer running back-office queries?  And when
they do get an error, what will they do about it?



 
  Okay, we implemented only the lock_timeout GUC.
  Patch attached, hopefully in an acceptable form.
  Documentation included in the patch, lock_timeout
  works the same way as statement_timeout, takes
  value in milliseconds and 0 disables the timeout.
 
  Best regards,
  Zoltán Böszörményi
 

 New patch attached. It's only regenerated for current CVS
 so it should apply cleanly.



In addition to the previously mentioned seg-fault issues when attempting to
use this feature (confirmed in another machine, linux, 64 bit, and
--enable-cassert does not offer any help), I have some more concerns about
the patch.  From the docs:

doc/src/sgml/config.sgml

Abort any statement that tries to lock any rows or tables and the
lock
has to wait more than the specified number of milliseconds, starting
from the time the command arrives at the server from the client.
If varnamelog_min_error_statement/ is set to literalERROR/
or
lower, the statement that timed out will also be logged.
A value of zero (the default) turns off the limitation.

This suggests that all row locks will have this behavior.  However, my
experiments show that row locks attempted to be taken for ordinary UPDATE
commands do not time out.  If this is only intended to apply to SELECT 
FOR UPDATE, that should be documented here.  It is documented elsewhere that
this applies to SELECT...FOR UPDATE, but it is not documented that this the
only row-locks it applies to.

from the time the command arrives at the server.  I am pretty sure this is
not the desired behavior, otherwise how does it differ from
statement_timeout?  I think it must be a copy and paste error for the doc.


For the implementation, I think the patch touches too much code.  In
particular, lwlock.c.  Is the time spent waiting on ProcArrayLock
significant enough that it needs all of that code to support timing it out?
I don't think it should ever take more than a few microseconds to obtain
that light-weight lock.  And if we do want to time all of the light weight
access, shouldn't those times be summed up, rather than timing out only if
any single one of them exceeds the threshold in isolation?  (That is my
interpretation of how the code works currently, I could be wrong on that.)

If the seg-faults are fixed, I am still skeptical that this patch is
acceptable, because the problem it solves seems to be poorly or incompletely
specified.

Cheers,

Jeff

Re: [HACKERS] WIP: generalized index constraints

On Sat, 2009-09-19 at 15:26 -0400, Tom Lane wrote:
 I haven't read the patch, but this whole discussion sounds to me like
 it means you're trying to plug things in at the wrong level.  Indexes
 generally don't care where the values they are storing came from ---
 whether it's a simple column or a expression result, it's all the same
 to the index.  I don't see why that shouldn't be true for exclusion
 constraints too.

The design is that one backend needs to be able to see values being
inserted by other backends before commit. There are two ways I can see
to do this:

(a) have all concurrent inserters serialize doing something like:
  1. acquire exclusive LWLock
  2. search index for conflicts with dirty snapshot and recheck if
 necessary
  3. insert into index
  4. release exclusive LWLock

(b) do what I do now, which is to:
  1. acquire exlusive LWLock
  2. put self in table of concurrent inserters, along with TID of heap 
 tuple I'm inserting
  3. release exclusive LWLock
  4. acquire shared LWLock
  5. copy potential conflicts to local memory
  6. release shared LWLock
  7. test for real conflicts between my heap tuple and the potentially 
 conflicting heap tuple (which can be found by TID).
  8. search index with dirty snapshot for conflicts and recheck if 
 necessary
  9. insert tuple into index
 10. acquire exclusive LWLock
 11. remove self from table of concurrent inserters
 12. release exclusive LWLock

Design (b) offers better concurrency because all conflict testing, index
searching, and index insertion take place without a lock at all. So, I
chose design (b). This has been out there for quite a long time[1][2],
and if it is an unacceptable design I need to know soon in order for
this feature to make it.

However, the consequence of (b) is that ExecInsertIndexTuples needs to
know about the translation from a heap tuple to an index tuple so that
the conflicts can be checked.

 BTW, further betraying that I've not read the patch: what exactly are
 you doing about the information_schema views?  If we are treating these
 things as SQL constraints, one would expect them to show up in
 information_schema; but I don't see how to represent them there in any
 adequate fashion, even without the expression-index angle.

Nothing right now. I think they should just be omitted from
information_schema, which can only (almost by definition) represent the
lowest common denominator features.

 On the whole
 I think we'd be a lot better off to NOT consider them to be constraints,
 but just another option for CREATE INDEX.

You suggested allowing an ALTER TABLE representation[3] and that design
has floated around for quite some time as well.

ALTER TABLE also has a major advantage: multiple constraints can use the
same index. For instance, an index on (a, b, c) can be used to enforce
both (a =, b =) and (a =, c =). You can't do that with btree, and it
could be a powerful feature that might cause some people to choose my
mechanism for a regular UNIQUE constraint over btree's existing
uniqueness mechanism.

So, I actually switched over the ALTER TABLE as my primary syntactic
representation, and dropped the CREATE INDEX variant (I think that would
be worthwhile to bring back as an extra option, but I haven't yet). If I
need to drop ALTER TABLE, I need to know soon.

Regards,
Jeff Davis

[1] http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php
[2] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php
[3] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00406.php




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

[HACKERS] operator exclusion constraints [was: generalized index constraints]

On Sat, 2009-09-19 at 10:48 -0700, Jeff Davis wrote:
   CONSTRAINT foo_constr (a op, ...)
 { USING INDEX foo_idx | USING method }

I am updating the syntax to be:

  CONSTRAINT foo_constr
EXCLUSION (a op, ...) { USING method | INDEX foo_idx };

First, I think EXCLUSION makes a perfect noun to fit in that place (like
FOREIGN KEY).

Second, this makes it possible to avoid specifying the index, and the
system can create one for you by knowing the access method. That makes
the feature a little more declarative.

However, it still doesn't provide a way to express two constraints using
one index all within CREATE TABLE, because the index would need to be
defined before the constraints in that case. I don't see that as a
problem, but Peter had the following concern:

Another problem this would lead to is that a say dump of a table
definition wouldn't actually contain all the constraints that apply to
the table anymore, because there might be additional stuff such as this
that can't be expressed that way. [1]

I don't think that's a serious problem, I just need to ensure that
indexes referenced by a constraint are dumped before the constraint
itself. Then, I can dump the operator exclusion constraints (OXCs) as
ALTER TABLEs. The -t option to pg_dump appears to already dump
constraints as separate ALTER TABLEs. Is there something that I'm
missing?

Regards,
Jeff Davis

[1] http://archives.postgresql.org/pgsql-hackers/2009-09/msg01018.php
   


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

Re: [HACKERS] WIP: generalized index constraints

Jeff Davis pg...@j-davis.com writes:
 The design is that one backend needs to be able to see values being
 inserted by other backends before commit.

I don't understand why this isn't handled exactly the way unique
constraints are done now.  Frankly, the amount of added complexity you
propose below is enough to make me want to reject the patch forthwith;
given that it's going to be a relatively little-used feature, the bugs
are never going to be out of it completely if we do it like this.

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] WIP: generalized index constraints

On Sat, 2009-09-19 at 16:43 -0400, Tom Lane wrote:
 I don't understand why this isn't handled exactly the way unique
 constraints are done now.  Frankly, the amount of added complexity you
 propose below is enough to make me want to reject the patch forthwith;
 given that it's going to be a relatively little-used feature, the bugs
 are never going to be out of it completely if we do it like this.

Unique constraints lock the index page while the insert is happening.
How am I supposed to do that, when the conflicting values might be
anywhere in the index (circles have no total order)?

It may sound complex, but it basically boils down to a two stage
process:
 1. test for conflicts with concurrently-inserting backends
 2. test for conflicts that already exist in the index (dirty or not)

I don't think that it's ridiculously complex. In fact, I think there are
relatively few scenarios that will make any real difference, and those
scenarios can be tested with gdb pretty thoroughly.

Regards,
Jeff Davis


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

Re: [HACKERS] generic copy options

Emmanuel Cecchet m...@asterdata.com writes:
 [ generic copy options patch ]

I went ahead and applied the psql \copy part of this, since that saves
us a couple hundred lines of code regardless of what may or may not
happen on the backend side.  There were a couple of minor bugs, and
I also found a few other simplifications we could make in the same
area, eg if we're not going to parse the options exactly then we
needn't be too picky about the column list syntax either.

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] WIP: generalized index constraints

Jeff Davis pg...@j-davis.com writes:
 On Sat, 2009-09-19 at 16:43 -0400, Tom Lane wrote:
 I don't understand why this isn't handled exactly the way unique
 constraints are done now.  Frankly, the amount of added complexity you
 propose below is enough to make me want to reject the patch forthwith;
 given that it's going to be a relatively little-used feature, the bugs
 are never going to be out of it completely if we do it like this.

 Unique constraints lock the index page while the insert is happening.
 How am I supposed to do that, when the conflicting values might be
 anywhere in the index (circles have no total order)?

Well, you can't do it *exactly* the same way btree does, but what
I would envision is first insert the index tuple and then do a
dirty-snapshot search for conflicting tuples.  The interlock against
conflicting concurrent inserts doesn't need all this new infrastructure
you propose; just wait to see if conflicting transactions commit, same
as we do now.  And I do maintain that that sort of code has a high risk
of undetected bugs.

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] operator exclusion constraints [was: generalized index constraints]

Jeff Davis pg...@j-davis.com writes:
 I am updating the syntax to be:

   CONSTRAINT foo_constr
 EXCLUSION (a op, ...) { USING method | INDEX foo_idx };

I'm still acutely uncomfortable with using CONSTRAINT syntax for this.
It is not a constraint per standard, because it's not going to be
displayable in information_schema.  Furthermore, by extending
standardized syntax you run the risk of being blindsided by future
additions to the standard.

 ... Peter had the following concern:

 Another problem this would lead to is that a say dump of a table
 definition wouldn't actually contain all the constraints that apply to
 the table anymore, because there might be additional stuff such as this
 that can't be expressed that way. [1]

 I don't think that's a serious problem,

That objection is completely bogus.  pg_dump does not, and AFAIR never
has, promised to emit everything in the CREATE TABLE command.  It's far
more efficient and practical to emit indexes and constraints as separate
commands later, after the data has been loaded.  In the case of say
foreign key constraints, it's absolutely necessary to do it separately,
else you can't implement circular constraint references.  Besides, we
already have many cases where indexes have to be emitted separately
because they don't fit into the CONSTRAINT syntax: expression indexes
and nondefault opclasses to name two.

The point about being able to support multiple constraints with one
index is kind of interesting, but I don't actually think that that's
so useful that it should override all other considerations about what
syntax we should pick.  I think we should drop the whole thing and
just treat this as an extension to the CREATE INDEX 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] WIP: generalized index constraints

On Sat, 2009-09-19 at 18:00 -0400, Tom Lane wrote:
 Well, you can't do it *exactly* the same way btree does, but what
 I would envision is first insert the index tuple and then do a
 dirty-snapshot search for conflicting tuples.  The interlock against
 conflicting concurrent inserts doesn't need all this new infrastructure
 you propose; just wait to see if conflicting transactions commit, same
 as we do now.  And I do maintain that that sort of code has a high risk
 of undetected bugs.

How do you prevent deadlocks in the following case?

T1: inserts into index
T2: inserts into index
T1: checks index for conflicts, finds T2
T2: checks index for conflicts, finds T1

We can't say only wait if your xid is higher because xid 200 may both
insert and check the index before xid 100 even inserts.

The way I solve this in my current patch is by assigning a sequence
number in a shared memory table for each insert. The sequence number
works because a higher sequence number will always be able to see a
lower sequence number's tuple, so we can safely say only wait if you
have a higher sequence number.

I can tack the same solution onto your idea, but I would need to keep my
shared memory table and probably some other infrastructure. It may be
less complex than it is currently, however. Simpler ideas welcome.

And to clarify the syntax issue, I assume this means that:
  ((a||b)::circle )

would look for the column in the index that matches that expression, and
then use that attribute number when scanning the index? I'm OK with
that; I don't see a lot of obvious value in having separate expressions
for the constraint and the index (even if it did have value, it would
take some real creativity to find it ;)

Regards,
Jeff Davis


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

Re: [HACKERS] operator exclusion constraints [was: generalized index constraints]

On Sat, 2009-09-19 at 18:35 -0400, Tom Lane wrote:
 I'm still acutely uncomfortable with using CONSTRAINT syntax for this.
 It is not a constraint per standard, because it's not going to be
 displayable in information_schema.  Furthermore, by extending
 standardized syntax you run the risk of being blindsided by future
 additions to the standard.

Ok.

 The point about being able to support multiple constraints with one
 index is kind of interesting, but I don't actually think that that's
 so useful that it should override all other considerations about what
 syntax we should pick.  I think we should drop the whole thing and
 just treat this as an extension to the CREATE INDEX syntax.

Perhaps ALTER INDEX ADD EXCLUSION CONSTRAINT or some other command? And
CREATE INDEX can offer the ability as a shorthand?

I would still really like to decouple this from CREATE INDEX because of
two reasons:
  1. Cannot support multiple constraints per index very easily. I think 
 this is a significant feature.
  2. Must decide to make constraint at the same time as making the 
 index, and once it's there, you can't remove it without dropping 
 the index.

I think either of these still tie the concept to implementation, because
creating the index is always explicit. Peter seemed concerned about
that, and I think that concern is valid, but I can live with it. If we
really want them to be declarative, we could invent a new command.

Regards,
Jeff Davis




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

Re: [HACKERS] Anonymous code blocks

2009-09-19 Thread Dimitri Fontaine

Hi,

Petr Jelinek pjmo...@pjmodos.net writes:
 I attached current version of the patch. I don't expect this to get
 committed or anything, but I'd like other eyes to take a look at it.

I'm reviewing this patch, and have early questions that might allow for
a quick returned with little feedback and much work...

Patch applies cleanly and build cleanly too, basic examples are working
fine. The problem is the following:

dim=# do $$begin select 'foo'; end;$$;
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline line 1 at SQL statement

Here's an example not so simple as to being meaningless:

  do $$
  declare v text := current_setting('server_version'); 
  begin
   case when v ~ '8.5' then select 'foo'; else select 'bar'; end case; 
  end;$$;

And while this works:

  dim=# do $$ declare i integer; begin for i in 1..10 loop raise notice '%', i; 
end loop; end;$$;

One might want to have this working too:

  do returns setof integer as $$declare i integer; begin for i in 1..10 loop 
return next; end;$$;

So here are the major points about this patch:

 - it's missing the returns declaration syntax (default value could be
   returns void?)

 - it would be much more friendly to users if it had a default output
   for queries, the returned object seems a good fit

Regards,
-- 
dim

PS: I'll go mark as returned with feedback but intend to complete this
review in the following days, by having a look at the code and
documentation. Unless beaten to it, as I won't be able to give accurate
guidance for pursuing effort.

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

Re: [HACKERS] Anonymous code blocks

2009-09-19 Thread Andrew Dunstan




Dimitri Fontaine wrote:

So here are the major points about this patch:

 - it's missing the returns declaration syntax (default value could be
   returns void?)

 - it would be much more friendly to users if it had a default output
   for queries, the returned object seems a good fit


  


Really? That wasn't my expectation at all. I expected that the code 
would in effect be always returning void. I think you're moving the 
goalposts a bit here. I don't think we need a RETURNS clause on it for 
it to be useful.


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] PATCH: make plpgsql IN args mutable (v1)

Steve Prentice prent...@cisco.com writes:
 This patch changes plpgsql IN parameters so they are mutable.  

I've applied this, since the consensus seemed to be in favor of it.

 I decided not to update the docs for this change because the docs  
 don't currently indicate that an IN parameter is constant and I didn't  
 want to encourage it because it isn't universally considered good  
 programming practice to assign to an IN parameter. If others think we  
 need a doc change for this, I'll update the patch.

I agree, no need to say anything one way or the other in the plpgsql docs.
We'll want to mention it in the release notes of course.

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] updated hstore patch

David E. Wheeler da...@kineticode.com writes:
 ... I think that this patch is ready for committer review and, perhaps,  
 committing. The code looks clean (though mainly over my head) and the  
 functionality is top-notch.

Given the number of questions in your review, I don't think this is
actually ready to commit.  I'm marking it waiting on author instead.

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] updated hstore patch

2009-09-19 Thread David E. Wheeler


On Sep 19, 2009, at 7:03 PM, Tom Lane wrote:


Given the number of questions in your review, I don't think this is
actually ready to commit.  I'm marking it waiting on author instead.


Yes, actually, I had second thoughts about that and meant to change it  
myself. Thanks Tom.


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] Anonymous code blocks

Andrew Dunstan and...@dunslane.net writes:
 Dimitri Fontaine wrote:
 So here are the major points about this patch:
 - it's missing the returns declaration syntax (default value could be
 returns void?)
 - it would be much more friendly to users if it had a default output
 for queries, the returned object seems a good fit

 Really? That wasn't my expectation at all. I expected that the code 
 would in effect be always returning void. I think you're moving the 
 goalposts a bit here. I don't think we need a RETURNS clause on it for 
 it to be useful.

Yeah.  The presented examples aren't tremendously convincing, as they
both beg the question why not just do a SELECT?.  It's also not
exactly apparent to me why redefining the behavior of SELECT in a
plpgsql function would be a better thing than having RETURN do it.

I think adding onto DO capabilities is something we could do later
if demand warrants.  I'd prefer to underdesign it for starters than
to encrust it with features that might not be needed.

BTW, what happens with the current patch if you try to do a RETURN?

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] operator exclusion constraints [was: generalized index constraints]

2009-09-19 Thread David Fetter

On Sat, Sep 19, 2009 at 04:40:19PM -0700, Jeff Davis wrote:
 On Sat, 2009-09-19 at 18:35 -0400, Tom Lane wrote:
  I'm still acutely uncomfortable with using CONSTRAINT syntax for this.
  It is not a constraint per standard, because it's not going to be
  displayable in information_schema.  Furthermore, by extending
  standardized syntax you run the risk of being blindsided by future
  additions to the standard.
 
 Ok.

It just occurred to me that SQL:2008 ASSERTION might already fit this
feature. :)

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [HACKERS] Anonymous code blocks

2009-09-19 Thread Petr Jelinek


Tom Lane napsal(a):

Andrew Dunstan and...@dunslane.net writes:
  

Dimitri Fontaine wrote:


So here are the major points about this patch:
- it's missing the returns declaration syntax (default value could be
returns void?)
- it would be much more friendly to users if it had a default output
for queries, the returned object seems a good fit
  


  
Really? That wasn't my expectation at all. I expected that the code 
would in effect be always returning void. I think you're moving the 
goalposts a bit here. I don't think we need a RETURNS clause on it for 
it to be useful.



I think adding onto DO capabilities is something we could do later
if demand warrants.  I'd prefer to underdesign it for starters than
to encrust it with features that might not be needed.
  


Right, RETURNS can be added later without breaking any existing code for 
users so no problem there (same goes for removing the requirement of 
BEGIN ... END for example).



BTW, what happens with the current patch if you try to do a RETURN?
  


Throws same error as function defined with RETURNS void.

--
Regards
Petr Jelinek (PJMODOS)

Re: [HACKERS] generic copy options

On Sat, Sep 19, 2009 at 3:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Emmanuel Cecchet m...@asterdata.com writes:
[ latest patch version ]

Do we have consensus on the syntax for this patch? In particular,
what about the question of adding CSV_ to all the CSV-specific option
names? Emmanuel argued that this is necessary to avoid confusion if
we someday introduce other copy formats that have similar options.
However, I think you could easily turn that argument around. Any one
COPY command will surely use just one format, and it seems to me that
forcing different formats to use different names for equivalent options
won't simplify life for anybody. So I'm inclined to think we should
not have the CSV_ prefixes. (I seem to recall that we had exactly
this discussion when the options were introduced the first time, and
settled on not using format-specific option names.)

Agreed. It doesn't seem inconceivable that some other format could
have a header or quote option. A related question is whether we
should replace the CSV option with a FORMAT option for which one
of the possible choices is CSV (much as we did with EXPLAIN).

One other minor point is that the patch introduces an empty-list
syntax for individual option values, but then treats it the same
as specifying nothing:

+ | '(' ')' { $$ = NULL; }
+ | /* EMPTY */ { $$ = NULL; }

I'm not convinced this is a a good idea, and in any case I don't see
it documented. I'm inclined to omit the '(' ')' syntax.

It seemed like a good idea because if you can do force_quote (a, b, c)
and force_quote (a, b) you might think that you could also do
force_quote (). Particularly for the sake of people generating SQL
automatically by some means, it seems like this might simplify life.
But possibly it shouldn't evaluate to the same value, so that you
can't write OIDS () to mean the same thing as OIDS ON.

...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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5

On Sat, Sep 19, 2009 at 4:17 PM, Jeff Janes jeff.ja...@gmail.com wrote:
 On Thu, Sep 3, 2009 at 6:47 AM, Boszormenyi Zoltan z...@cybertec.at wrote:
 Boszormenyi Zoltan írta:
  Alvaro Herrera írta:
  Boszormenyi Zoltan wrote:
  The vague consensus for syntax options was that the GUC
  'lock_timeout' and WAIT [N] extension (wherever NOWAIT
  is allowed) both should be implemented.
 
  Behaviour would be that N seconds timeout should be
  applied to every lock that the statement would take.
 
  In
  http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us
  Tom argues that lock_timeout should be sufficient.  I'm not sure what
  does WAIT [N] buy

 I disagree with Tom on this point.  *If* I was trying to implement  a server
 policy, then sure, it should not be done by embedding the timeout in the SQL
 statement.  But I don't think they want this to implement a server policy.
 (And if we do, why would we thump the poor victims that are waiting on the
 lock, rather than the rogue who decided to take a lock and then camp out on
 it?)  The use case for WAIT [N] is not a server policy, but a UI policy.  I
 have two ways to do this task.  The preferred way needs to lock a row, but
 waiting for it may take too long.  So if I can't get the lock within a
 reasonable time, I fall back on a less-preferred but still acceptable way of
 doing the task, one that doesn't need the lock.  If we move to a new server,
 the appropriate value for the time out does not change, because the
 appropriate level is the concern of the UI and the end users, not the
 database server.  This wouldn't be scattered all over the application,
 either.  In my experience, if you have an application that could benefit
 from this, you might have 1 or 2 uses for WAIT [N] out of 1,000+ statements
 in the application.  (From my perspective, if there were to be a WAIT [N]
 option, it could plug into the statement_timeout mechanism rather than the
 proposed lock_timeout mechanism.)

 I think that if the use case for a GUC is to set it, run a single very
 specific statement, and then unset it, that is pretty clear evidence that
 this should not be a GUC in the first place.

+1 to all of the above.

...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] WIP: generalized index constraints

On Sat, Sep 19, 2009 at 2:51 PM, Jeff Davis pg...@j-davis.com wrote:
 On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
 What about them?  It's not clear why you think this requires anything
 special.

 From a syntax standpoint, I need to represent one operator for every
 index column involved in the constraint. So, if there's a functional
 index on ((a||b)::circle), I clearly can't have an exclusion constraint
 like (a =, b =).

 I see two options:

  1. (expr op), where expr is an expression over table attributes
    that must have the exact signature as the expression for the index.
  2. (index_col op), and then read the expression from the index

I was wondering if we couldn't introduce a dummy tuple name similar to
OLD and NEW, called, say, OTHER.  Then instead of writing a =, you
could write a = OTHER.a ... or perhaps a = OTHER.b ... although that
might also open the door to more things than you want to support at
this point.

...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] Anonymous code blocks