date:20120227

[HACKERS] Re: [pgsql-cluster-hackers] 3rd Cluster Hackers Summit, May 15th in Ottawa

2012-02-27 Thread Koichi Suzuki

Josh;

I belive this is an important agenda too, though I'm not sure if we can assing 
90minutes to him.   We need to organize the schedule shortly.

From XC, we will present progress, release schedule and further technical 
challanges, including adding/removing nodes on-the-fly and HA feature.   If 
the summit goes like usual conference and presentation, I can present these.

If not, like last year, I think it's better if bi-directional replication is 
divided into several technical issues and use cases.   I believe there will be 
several technical issues to be addressed separately.

Regards;
---
Koichi

More 

On Sun, 26 Feb 2012 09:59:35 +
Simon Riggs si...@2ndquadrant.com wrote:

 On Sun, Feb 12, 2012 at 8:33 PM, Joshua Berkus j...@agliodbs.com wrote:
 
  = Project Reports: 5 minutes from each project
    * Hot Standby/Binary Replication
    * pgPoolII
    * PostgresXC
    * Your Project Here
 
 I'd like some time to discuss my new project: Bi-Directional
 Replication for Core. I don't have all the answers for it yet, but
 expect to have something solid to discuss in May.
 
 If I could have 90 minutes, that would be useful.
 
 Thanks.
 
 -- 
  Simon Riggs   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training  Services
 
 -- 
 Sent via pgsql-cluster-hackers mailing list 
 (pgsql-cluster-hack...@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-cluster-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] Speed dblink using alternate libpq tuple storage

2012-02-27 Thread Kyotaro HORIGUCHI

Hello, 

 On OOM, the old result is freed to have higher chance that
 constructing new result succeeds.  But if we want to transport
 error message, we need to keep old PGresult around.  Thus
 two separate paths.

Ok, I understood the aim. But now we can use non-local exit to do
that for not only asynchronous reading (PQgetResult()) but
synchronous (PQexec()). If we should provide a means other than
exceptions to do that, I think it should be usable for both
syncronous and asynchronous reading. conn-asyncStatus seems to
be used for the case.

Wow is the modification below?

- getAnotherTuple() now returns 0 to continue as before, and 1
  instead of EOF to signal EOF state, and 2 to instruct to exit
  immediately.

- pqParseInput3 set conn-asyncStatus to PGASYNC_BREAK for the
  last case,

- then PQgetResult() returns immediately when
  asyncStatus == PGASYNC_TUPLES_BREAK after parseInput() retunes.

- and PQexecFinish() returns immediately if PQgetResult() returns
  with aysncStatys == PGASYNC_TUPLES_BREAK.

- PGgetResult() sets asyncStatus = PGRES_TUPLES_OK if called with
  asyncStatus == PGRES_TUPLES_BREAK

- New libpq API PQisBreaked(PGconn*) returns if asyncStatus ==
  PGRES_TUPLES_BREAK can be used to check if the transfer is breaked.

 Instead use (%s, errmsg) as argument there.  libpq code
 is noisy enough, no need to add more.

Ok. I will do so.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

Re: [HACKERS] Initial 9.2 pgbench write results

2012-02-27 Thread Simon Riggs

On Mon, Feb 27, 2012 at 5:13 AM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Feb 24, 2012 at 5:35 AM, Simon Riggs si...@2ndquadrant.com wrote:
 On Thu, Feb 23, 2012 at 11:59 PM, Robert Haas robertmh...@gmail.com wrote:
 this doesn't feel like the right time to embark on a bunch of new
 engineering projects.

 IMHO this is exactly the right time to do full system tuning. Only
 when we have major projects committed can we move towards measuring
 things and correcting deficiencies.

 Ideally we should measure things as we do them.  Of course there will
 be cases that we fail to test which slip through the cracks, as Greg
 is now finding, and I agree we should try to fix any problems that we
 turn up during testing.  But, as I said before, so far Greg hasn't
 turned up anything that can't be fixed by adjusting settings, so I
 don't see a compelling case for change on that basis.

That isn't the case. We have evidence that the current knobs are
hugely ineffective in some cases.

Turning the bgwriter off is hardly adjusting a setting, its
admitting that there is no useful setting.

I've suggested changes that aren't possible by tuning the current knobs.


 As a side point, there's no obvious reason why the problems Greg is
 identifying here couldn't have been identified before committing the
 background writer/checkpointer split.  The fact that we didn't find
 them then suggests to me that we need to be more not less cautious in
 making further changes in this area.

The split was essential to avoid the bgwriter action being forcibly
turned off during checkpoint sync. The fact that forcibly turning it
off is in some cases a benefit doesn't alter the fact that it was in
many cases a huge negative. If its on you can always turn it off, but
if it was not available at all there was no tuning option. I see no
negative aspect to the split.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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

Re: [HACKERS] CLOG contention, part 2

2012-02-27 Thread Simon Riggs

On Sun, Feb 26, 2012 at 10:53 PM, Robert Haas robertmh...@gmail.com wrote:
 On Sat, Feb 25, 2012 at 2:16 PM, Simon Riggs si...@2ndquadrant.com wrote:
 On Wed, Feb 8, 2012 at 11:26 PM, Robert Haas robertmh...@gmail.com wrote:
 Given that, I obviously cannot test this at this point,

 Patch with minor corrections attached here for further review.

 All right, I will set up some benchmarks with this version, and also
 review the code.

Thanks.

 As a preliminary comment, Tom recently felt that it was useful to
 reduce the minimum number of CLOG buffers from 8 to 4, to benefit very
 small installations.  So I'm guessing he'll object to an
 across-the-board doubling of the amount of memory being used, since
 that would effectively undo that change.  It also makes it a bit hard
 to compare apples to apples, since of course we expect that by using
 more memory we can reduce the amount of CLOG contention.  I think it's
 really only meaningful to compare contention between implementations
 that use approximately the same total amount of memory.  It's true
 that doubling the maximum number of buffers from 32 to 64 straight up
 does degrade performance, but I believe that's because the buffer
 lookup algorithm is just straight linear search, not because we can't
 in general benefit from more buffers.

I'm happy if you want to benchmark this against simply increasing clog
buffers. We expect downsides to that, but it is worth testing
nonetheless.

 pgbench loads all the data in one go, then pretends the data got their
 one transaction at a time. So pgbench with no mods is actually the
 theoretically most unreal imaginable. You have to run pgbench for 1
 million transactions before you even theoretically show any gain from
 this patch, and it would need to be a long test indeed before the
 averaged effect of the patch was large enough to avoid the zero
 contribution from the first million transacts.

 Depends on the scale factor.  At scale factor 100, the first million
 transactions figure to have replaced a sizeable percentage of the rows
 already.  But I can use your other patch to set up the run.  Maybe
 scale factor 300 would be good?

Clearly if too much I/O is induced by the test we will see the results
swamped. The patch is aimed at people with bigger databases and lots
of RAM, which is many, many people because RAM is cheap.

So please use a scale factor that the hardware can cope with.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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

Re: [HACKERS] Speed dblink using alternate libpq tuple storage

2012-02-27 Thread Marko Kreen

On Mon, Feb 27, 2012 at 05:20:30PM +0900, Kyotaro HORIGUCHI wrote:
 Hello, 
 
  On OOM, the old result is freed to have higher chance that
  constructing new result succeeds.  But if we want to transport
  error message, we need to keep old PGresult around.  Thus
  two separate paths.
 
 Ok, I understood the aim. But now we can use non-local exit to do
 that for not only asynchronous reading (PQgetResult()) but
 synchronous (PQexec()). If we should provide a means other than
 exceptions to do that, I think it should be usable for both
 syncronous and asynchronous reading. conn-asyncStatus seems to
 be used for the case.
 
 Wow is the modification below?
 
 - getAnotherTuple() now returns 0 to continue as before, and 1
   instead of EOF to signal EOF state, and 2 to instruct to exit
   immediately.
 
 - pqParseInput3 set conn-asyncStatus to PGASYNC_BREAK for the
   last case,
 
 - then PQgetResult() returns immediately when
   asyncStatus == PGASYNC_TUPLES_BREAK after parseInput() retunes.
 
 - and PQexecFinish() returns immediately if PQgetResult() returns
   with aysncStatys == PGASYNC_TUPLES_BREAK.
 
 - PGgetResult() sets asyncStatus = PGRES_TUPLES_OK if called with
   asyncStatus == PGRES_TUPLES_BREAK
 
 - New libpq API PQisBreaked(PGconn*) returns if asyncStatus ==
   PGRES_TUPLES_BREAK can be used to check if the transfer is breaked.

I don't get where are you going with such changes.  Are you trying to
make V2 code survive row-processor errors?  (Only V2 has concept of
EOF state.)  Then forget about it.  It's more important to not
destabilize V3 code.

And error from row processor is not something special from other errors.
Why does it need special state?  And note that adding new PGASYNC or
PGRES state needs review of all if() and switch() statements in the
code that use those fields...  So there must serious need for it.
At the moment I don't see such need.

I just asked you to replace -rowProcessorErrMsg with -errMsg
to get rid of unnecessary field.

But if you want to do bigger cleanup, then you can instead make
PQsetRowProcessorErrMsg() more generic:

  PQsetErrorMessage(PGconn *conn, const char *msg)

Current code has the tiny problem that it adds new special state between
PQsetRowProcessorErrMsg() and return from callback to getAnotherTuple()
where getAnotherTuple() sets actual error state.  When the callback
exits via exception, the state can leak to other code.  It should not
break anything, but it's still annoying.

Also, with the PQgetRow() patch, the need for doing complex processing
under callback has decreased and the need to set error outside callback
has increased.

As a bonus, such generic error-setting function would lose any extra
special state introduced by row-processor patch.

Previously I mentioned that callback would need to have additional
PGconn* argument to make connection available to callback to use such
generic error setting function, but now I think it does not need it -
simple callbacks don't need to set errors and complex callback can make
the PGconn available via Param.  Eg. the internal callback should set
Param to PGconn, instead keeping NULL there.

-- 
marko


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

Re: [HACKERS] pgstat documentation tables

2012-02-27 Thread Magnus Hagander

On Mon, Feb 27, 2012 at 04:44, Robert Haas robertmh...@gmail.com wrote:
 On Sat, Feb 25, 2012 at 9:33 AM, Magnus Hagander mag...@hagander.net wrote:
 On Mon, Jan 16, 2012 at 02:03, Greg Smith g...@2ndquadrant.com wrote:
 On 01/15/2012 12:20 PM, Tom Lane wrote:

 Please follow the style already used for system catalogs; ie I think
 there should be a summary table with one entry per view, and then a
 separate description and table-of-columns for each view.


 Yes, that's a perfect precedent.  I think the easiest path forward here is
 to tweak the updated pg_stat_activity documentation, since that's being
 refactoring first anyway.  That can be reformatted until it looks just like
 the system catalog documentation.  And then once that's done, the rest of
 them can be converted over to follow the same style.  I'd be willing to work
 on doing that in a way that improves what is documented, too.  The
 difficulty of working with the existing tables has been the deterrent for
 improving that section to me.

 I've applied a patch that does this now. Hopefully, I didn't create
 too many spelling errors or such :-)

 I also applied a separate patch that folded the list of functions into
 the list of views, since that's where they are called, as a way to
 reduce duplicate documentation. I did it as a spearate patch to make
 it easier to back out if people think that was a bad idea...

 I think it's a little awkward this way; maybe it would be better as a
 separate table column.  Or maybe it was better the way it was; I'm not

The problem with a separate column is that it makes the table very
wide (some of those functions have very long name). And it won't be
possible to get an auto-linebreak in the function names, because
they're too long.

 sure.  Or maybe we could have a separate table that just gives the
 equivalences between stats table-column pairs and functions.  Of those
 ideas, I think I like separate table in the same column the best.

That one would at least work. You mean basically:

|pg_stat_database.xact_commit|pg_stat_get_db_xact_commit|
|pg_stat_database.xact_rollback|pg_stat_get_db_xact_rollback|

etc etc for each column/function, right?


 Also, I wonder if we should promote section 27.2.2.1. Other Statistics
 Functions to 27.2.3.

I was considering that, but given that 27.2.2 is viewing statistics,
it does seem like a sub-section to that.. Though maybe if we make the
lis tof views into their own section *as well*, so we have one sectoin
for how to view it, one for views and one for other functions it
would make more sense.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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

[HACKERS] restrict modification of column values in BR triggers

2012-02-27 Thread Miroslav Šimulčík

Hi,

is there any way to prevent role from modifing values of some columns of
NEW row in before row triggers? I revoked insert privilege from these
columns to ensure that only default value can be inserted, but it is still
posible to modify values being inserted using before row triggers. I can't
revoke trigger privilege on that table, because this role must be able to
create triggers on this table.

Thank you.

Best regards
Miroslav Simulcik

[HACKERS] [PATCH] Cleanup: use heap_open/heap_close consistently

2012-02-27 Thread Marti Raudsepp

Hi,

Here's a tiny cleanup: currently get_tables_to_cluster uses
heap_open() to open the relation, but then closes it with
relation_close(). Currently relation_close=heap_close, but it seems
like good idea to be consistent -- in case these functions need to
diverge in the future.

Regards,
Marti

diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 349d130..a10ec2d 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1551,7 +1551,7 @@ get_tables_to_cluster(MemoryContext cluster_context)
}
heap_endscan(scan);

-   relation_close(indRelation, AccessShareLock);
+   heap_close(indRelation, AccessShareLock);

return rvs;
 }
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 349d130..a10ec2d 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1551,7 +1551,7 @@ get_tables_to_cluster(MemoryContext cluster_context)
 	}
 	heap_endscan(scan);
 
-	relation_close(indRelation, AccessShareLock);
+	heap_close(indRelation, AccessShareLock);
 
 	return rvs;
 }

-- 
Sent 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 to allow domains over composite types

2012-02-27 Thread Asif Rehman

Hi Yeb Havinga,

I was digging archives to see anyone worked on supporting domain's over
composite type and found your patch, but that was pulled back. According to
commitfest comments it needs some more work...

Are you going to submit the updated patch?

Regards,
--Asif

On Wed, May 11, 2011 at 5:07 PM, Yeb Havinga yebhavi...@gmail.com wrote:

 typecmds.c says:
 Domains over composite types might be made to work in the future, but not
 today.

 Attached is a patch that allows domains over composite types, together
 with test cases in domaincomp.sql. A domain over a composite type has
 typtype TYPTYPE_DOMAIN, but typrelid and typrelkind are empty: that
 information is only available in the pg_type record of the base type. The
 remainder of the patch follows from that choice. While parsing a record
 expression into a row type, an extra coercion node had to be inserted to
 ensure that the domain checks are called.

 All regression tests are ok, comments are highly appreciated.

 --

 Yeb Havinga
 http://www.mgrid.net/
 Mastering Medical 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] [COMMITTERS] pgsql: Add new keywords SNAPSHOT and TYPES to the keyword list in gram.

2012-02-27 Thread Peter Eisentraut

On tor, 2012-02-09 at 13:30 -0500, Tom Lane wrote:
  Also, we should at least do the attached to simplify the process.
  +check:
  + $(top_srcdir)/src/tools/check_keywords.pl $(top_srcdir)
 
 Actually, what would make sense in that line is to attach it to the
 existing maintainer-check target, no?  I don't think top-level
 make check descends into this directory.

I have hooked this into maintainer-check now.


-- 
Sent 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 to know a table has been modified?

2012-02-27 Thread Tatsuo Ishii

 For TRIGGER, I cannot thinking of any way. Any idea will be
 welcome.
  
 It would require creating cooperating triggers in the database and
 having a listener, but you might consider the
 triggered_change_notifications() trigger function included in 9.2. 
 It works at least as far back as 9.0; I haven't tried it any further
 back.

Thanks for the info. It's a little bit overkill for my purpose though.
(on busy systems, the notification would be too frequent).

I would think that creating a small routine periodically consults
pg_stat_all_tables view and records the last update datetime for each
table (unfortunately the view does not have last modification date).
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

Re: [HACKERS] foreign key locks, 2nd attempt

2012-02-27 Thread Heikki Linnakangas

On 23.02.2012 18:01, Alvaro Herrera wrote:

Excerpts from Tom Lane's message of jue feb 23 12:28:20 -0300 2012:

Alvaro Herreraalvhe...@commandprompt.com writes:

Sure. The problem is that we are allowing updated rows to be locked (and
locked rows to be updated). This means that we need to store extended
Xmax information in tuples that goes beyond mere locks, which is what we
were doing previously -- they may now have locks and updates simultaneously.

(In the previous code, a multixact never meant an update, it always
signified only shared locks. After a crash, all backends that could
have been holding locks must necessarily be gone, so the multixact info
is not interesting and can be treated like the tuple is simply live.)

Ugh. I had not been paying attention to what you were doing in this
patch, and now that I read this I wish I had objected earlier.

Uhm, yeah, a lot earlier -- I initially blogged about this in August
last year:
http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/

and in several posts in pgsql-hackers.

This
seems like a horrid mess that's going to be unsustainable both from a
complexity and a performance standpoint. The only reason multixacts
were tolerable at all was that they had only one semantics. Changing
it so that maybe a multixact represents an actual updater and maybe
it doesn't is not sane.

As far as complexity, yeah, it's a lot more complex now -- no question
about that.

How about assigning a new, real, transaction id, to represent the group
of transaction ids. The new transaction id would be treated as a
subtransaction of the updater, and the xids of the lockers would be
stored in the multixact-members slru. That way the multixact structures
wouldn't need to survive a crash; you don't care about the shared
lockers after a crash, and the xid of the updater would be safely stored
as is in the xmax field.

That way you wouldn't need to handle multixact wraparound, because we
already handle xid wraparound, and you wouldn't need to make multixact
slrus crash-safe.

Not sure what the performance implications would be. You would use up
xids more quickly, which would require more frequent anti-wraparound
vacuuming. And if we just start using real xids as the key to
multixact-offsets slru, we would need to extend that a lot more often.
But I feel it would probably be acceptable.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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

Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-02-27 Thread Peter Geoghegan

On 27 February 2012 06:23, Tom Lane t...@sss.pgh.pa.us wrote:
 I think that what Peter is on about in
 http://archives.postgresql.org/pgsql-hackers/2012-02/msg01152.php
 is the question of what location to use for the *result* of
 'literal string'::typename, assuming that the type's input function
 doesn't complain.  Generally we consider that we should use the
 leftmost token's location for the location of any expression composed
 of more than one input token.  This is of course the same place for
 'literal string'::typename, but not for the alternate syntaxes
 typename 'literal string' and cast('literal string' as typename).
 I'm not terribly impressed by the proposal to put in an arbitrary
 exception to that general rule for the convenience of this patch.

Now, you don't have to be. It was a mistake on my part to bring the
current user-visible behaviour into this. I don't see that there is
necessarily a tension between your position that we should blame the
leftmost token's location, and my contention that the Const location
field shouldn't misrepresent the location of certain Consts involved
in coercion post-analysis.

Let me put that in concrete terms. In my working copy of the patch, I
have made some more changes to the core system (mostly reverting
things that turned out to be unnecessary), but I have also made the
following change:

*** a/src/backend/parser/parse_coerce.c
--- b/src/backend/parser/parse_coerce.c
*** coerce_type(ParseState *pstate, Node *no
*** 280,293 
newcon-constlen = typeLen(targetType);
newcon-constbyval = typeByVal(targetType);
newcon-constisnull = con-constisnull;
!   /* Use the leftmost of the constant's and coercion's locations 
*/
!   if (location  0)
!   newcon-location = con-location;
!   else if (con-location = 0  con-location  location)
!   newcon-location = con-location;
!   else
!   newcon-location = location;
!
/*
 * Set up to point at the constant's text if the input routine 
throws
 * an error.
--- 280,286 
newcon-constlen = typeLen(targetType);
newcon-constbyval = typeByVal(targetType);
newcon-constisnull = con-constisnull;
!   newcon-location = con-location;
/*
 * Set up to point at the constant's text if the input routine 
throws
 * an error.
*

This does not appear to have any user-visible effect on caret position
for all variations in coercion syntax, while giving me everything that
I need. I had assumed that we were relying on things being this way,
but apparently this is not the case. The system is correctly blaming
the coercion token when it finds the coercion is at fault, and the
const token when it finds the Const node at fault, just as it did
before. So this looks like a case of removing what amounts to dead
code.

 Especially not when the only reason it's needed is that Peter is
 doing the fingerprinting at what is IMO the wrong place anyway.
 If he were working on the raw grammar output it wouldn't matter
 what parse_coerce chooses to do afterwards.

Well, I believe that your reason for preferring to do it at that stage
was that we could not capture all of the system's normalisation
smarts, like the fact that the omission of noise words isn't a
differentiator, so we might as well not have any. This was because
much of it - like the recognition of the equivalence of explicit joins
and queries with join conditions in the where clause - occurs within
the planner. We can't have it all, so we might as well not have any.
My solution here is that we be sufficiently vague about the behaviour
of normalisation that the user has no reasonable basis to count on
that kind of more advanced reduction occurring.

I did very seriously consider hashing the raw parse tree, but I have
several practical reasons for not doing so. Whatever way you look at
it, hashing there is going to result in more code, that is more ugly.
There is no uniform parent node that I can tag with a query_id. There
has to be more modifications to the core system so that queryId value
is carried around more places and persists for longer. The fact that
I'd actually be hashing different structs at different times (that
tree is accessed through a Node pointer) would necessitate lots of
redundant code that operated on each of the very similar structs in an
analogous way. The fact is that waiting until after parse analysis has
plenty of things to recommend it, and yes, the fact that we already
have working code with extensive regression tests is one of them.

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make

Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread Peter Eisentraut

On tor, 2012-02-23 at 10:54 -0800, David E. Wheeler wrote:
 I create a mock schema, add the function to it, and then put it in the 
 search_path ahead of pg_catalog.

That doesn't work for current_timestamp and similar built-in functions,
because they are always mapped to the pg_catalog schema.


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

Re: [HACKERS] overriding current_timestamp

2012-02-27 Thread Peter Eisentraut

On tor, 2012-02-23 at 10:55 -0800, David E. Wheeler wrote:
 On Feb 23, 2012, at 10:54 AM, David E. Wheeler wrote:
 
  I create a mock schema, add the function to it, and then put it in the 
  search_path ahead of pg_catalog. See the example starting at slide 48 on 
  http://www.slideshare.net/justatheory/pgtap-best-practices.
 
 Sorry, starting at slide 480.

That presentation only goes to slide 394.


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

Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread hubert depesz lubaczewski

On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
 Hello
 
 I rechecked Depesz's article -
 http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
 
 The behave of current HEAD is different than behave described in article.
 
 alter table a validate constraint a_a_check needs a access exclusive
 locks and blocks table modification - I tested inserts.
 
 Is it expected behave.
 
 session one:
 
 postgres=# create table a(a int);
 CREATE TABLE
 postgres=# alter table a add check (a  0) not valid;
 ALTER TABLE
 postgres=# begin;
 BEGIN
 postgres=# alter table a validate constraint a_a_check;
 ALTER TABLE
 
 session two:
 
 postgres=# update a set a = 100; -- it waits to commit in session one

yes, looks like we have revert to access exclusive lock:

$ begin;
BEGIN
Time: 0.352 ms

*$ ALTER TABLE test2 ADD CHECK ( field = 0 ) NOT VALID;
ALTER TABLE
Time: 0.662 ms

*$ select * from pg_locks where pid = pg_backend_pid();
   locktype│ database │ relation │  page  │ tuple  │ virtualxid │ 
transactionid │ classid │ objid  │ objsubid │ virtualtransaction │ pid  │   
 mode │ granted │ fastpath
───┼──┼──┼┼┼┼───┼─┼┼──┼┼──┼─┼─┼──
 relation  │16387 │11070 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ 
AccessShareLock │ t   │ t
 virtualxid│   [null] │   [null] │ [null] │ [null] │ 2/174  │
[null] │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ 
ExclusiveLock   │ t   │ t
 transactionid │   [null] │   [null] │ [null] │ [null] │ [null] │   
854 │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ ExclusiveLock   
│ t   │ f
 relation  │16387 │18653 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/174  │ 8975 │ 
AccessExclusiveLock │ t   │ f
(4 rows)

Time: 0.921 ms

Relation 18653 is table test2, of course.

*$ commit;
COMMIT

$ begin;
BEGIN
Time: 0.271 ms

*$ ALTER TABLE test2 VALIDATE CONSTRAINT test2_field_check;
ALTER TABLE
Time: 286.035 ms

*$ select * from pg_locks where pid = pg_backend_pid();
   locktype│ database │ relation │  page  │ tuple  │ virtualxid │ 
transactionid │ classid │ objid  │ objsubid │ virtualtransaction │ pid  │   
 mode │ granted │ fastpath
───┼──┼──┼┼┼┼───┼─┼┼──┼┼──┼─┼─┼──
 relation  │16387 │11070 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ 
AccessShareLock │ t   │ t
 virtualxid│   [null] │   [null] │ [null] │ [null] │ 2/175  │
[null] │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ 
ExclusiveLock   │ t   │ t
 transactionid │   [null] │   [null] │ [null] │ [null] │ [null] │   
855 │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ ExclusiveLock   
│ t   │ f
 relation  │16387 │18653 │ [null] │ [null] │ [null] │
[null] │  [null] │ [null] │   [null] │ 2/175  │ 8975 │ 
AccessExclusiveLock │ t   │ f
(4 rows)

Time: 0.631 ms

And it clearly shows that validation of constraint did lock the table
using AccessExclusiveLock, which kinda defeats the purpose of
INVALID/VALIDATE.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] Initial 9.2 pgbench write results

2012-02-27 Thread Robert Haas

On Mon, Feb 27, 2012 at 3:50 AM, Simon Riggs si...@2ndquadrant.com wrote:
 That isn't the case. We have evidence that the current knobs are
 hugely ineffective in some cases.

 Turning the bgwriter off is hardly adjusting a setting, its
 admitting that there is no useful setting.

 I've suggested changes that aren't possible by tuning the current knobs.

OK, fair point.  But I don't think any of us - Greg included - have an
enormously clear idea why turning the background writer off is
improving performance in some cases.  I think we need to understand
that better before we start changing things.

-- 
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] check constraint validation takes access exclusive locks

2012-02-27 Thread Alvaro Herrera


Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 -0300 
2012:
 On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
  Hello
  
  I rechecked Depesz's article -
  http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
  
  The behave of current HEAD is different than behave described in article.
  
  alter table a validate constraint a_a_check needs a access exclusive
  locks and blocks table modification - I tested inserts.

 yes, looks like we have revert to access exclusive lock:

See commits
2c3d9db56d5d49bdc777b174982251c01348e3d8
and
a195e3c34f1eeb6a607c342121edf48e49067ea9

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] restrict modification of column values in BR triggers

2012-02-27 Thread Robert Haas

On Mon, Feb 27, 2012 at 5:35 AM, Miroslav Šimulčík
simulcik.m...@gmail.com wrote:
 is there any way to prevent role from modifing values of some columns of NEW
 row in before row triggers? I revoked insert privilege from these columns to
 ensure that only default value can be inserted, but it is still posible to
 modify values being inserted using before row triggers. I can't revoke
 trigger privilege on that table, because this role must be able to create
 triggers on this table.

No, or at least I don't think so.  If you give someone trigger
privileges on your table, that's pretty much game over.  The trigger
functions they create will run as you.

-- 
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] pgstat documentation tables

2012-02-27 Thread Robert Haas

On Mon, Feb 27, 2012 at 5:22 AM, Magnus Hagander mag...@hagander.net wrote:
 The problem with a separate column is that it makes the table very
 wide (some of those functions have very long name).

Yeah, that's one thing I don't like about what you actually did,
either - it made some of the tables much wider.

 sure.  Or maybe we could have a separate table that just gives the
 equivalences between stats table-column pairs and functions.  Of those
 ideas, I think I like separate table in the same column the best.

 That one would at least work. You mean basically:

 |pg_stat_database.xact_commit|pg_stat_get_db_xact_commit|
 |pg_stat_database.xact_rollback|pg_stat_get_db_xact_rollback|

 etc etc for each column/function, right?

Yeah.

 Also, I wonder if we should promote section 27.2.2.1. Other Statistics
 Functions to 27.2.3.

 I was considering that, but given that 27.2.2 is viewing statistics,
 it does seem like a sub-section to that.. Though maybe if we make the
 lis tof views into their own section *as well*, so we have one sectoin
 for how to view it, one for views and one for other functions it
 would make more sense.

My thought was that a good half of those other statistics functions
don't have all that much to do with viewing anything, so the logical
argument for that section to need to be under viewing statistics
seems a bit weak.

-- 
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] check constraint validation takes access exclusive locks

2012-02-27 Thread Pavel Stehule

2012/2/27 Alvaro Herrera alvhe...@commandprompt.com:

 Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 
 -0300 2012:
 On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
  Hello
 
  I rechecked Depesz's article -
  http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
 
  The behave of current HEAD is different than behave described in article.
 
  alter table a validate constraint a_a_check needs a access exclusive
  locks and blocks table modification - I tested inserts.

 yes, looks like we have revert to access exclusive lock:

 See commits
 2c3d9db56d5d49bdc777b174982251c01348e3d8
 and
 a195e3c34f1eeb6a607c342121edf48e49067ea9

 this block a sense of NOT VALIDATE constraints. Is it final behave or
will be fixed on 9.2?

Regards

Pavel




 --
 Álvaro Herrera alvhe...@commandprompt.com
 The PostgreSQL Company - Command Prompt, Inc.
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread Alvaro Herrera


Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012:
 
 2012/2/27 Alvaro Herrera alvhe...@commandprompt.com:
 
  Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 
  -0300 2012:
  On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
   Hello
  
   I rechecked Depesz's article -
   http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
  
   The behave of current HEAD is different than behave described in article.
  
   alter table a validate constraint a_a_check needs a access exclusive
   locks and blocks table modification - I tested inserts.
 
  yes, looks like we have revert to access exclusive lock:
 
  See commits
  2c3d9db56d5d49bdc777b174982251c01348e3d8
  and
  a195e3c34f1eeb6a607c342121edf48e49067ea9
 
  this block a sense of NOT VALIDATE constraints.

Yeah :-(

 Is it final behave or will be fixed on 9.2?

It's final for 9.2 AFAIK.  It's supposed to get fixed during the 9.3
timeframe.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] VACUUM ANALYZE is faster than ANALYZE?

2012-02-27 Thread Cédric Villemain

Le mercredi 22 février 2012 20:12:35, Pavel Stehule a écrit :
 2012/2/22 Kevin Grittner kevin.gritt...@wicourts.gov:
  Pavel Stehule pavel.steh...@gmail.com wrote:
  usual pattern in our application is
  
  create table xx1 as select 
  analyze xx1
  create table xx2 as select  from xx1, 
  analyze xx2
  create table xx3 as select ... from xx3, 
  analyze xx3
  create table xx4 as select ... from xx1, ...
  
  tables xx** are use as cache.
  
  so we have to refresh statistic early.
  
  in this situation - and I found so in this case VACUUM ANALYZE is
  faster (30%) than ANALYZE. Size of xx** is usually between 500Kb
  and 8Kb
  
  This is not usual pattern for OLTP - Application is strictly OLAP.
  
  Is the VACUUM ANALYZE step faster, or is the overall job faster if
  VACUUM ANALYZE is run?  You may be running into the need to rewrite
  pages at an inopportune time or order without the VACUUM.  Have you
  tried getting a time VACUUM FREEZE ANALYZE on these cache tables
  instead of plain VACUUM ANALYZE?
  
  -Kevin
 
 vacuum freeze analyze is slower as expected. vacuum analyze is little
 bit faster or same in any step then analyze.
 
 I expected so just analyze should be significantly faster and it is not.
 
 Tom's demonstration is enough for me. ANALYZE doesn't read complete
 table, but uses random IO. VACUUM ANALYZE reads complete table, but it
 uses seq IO and vacuum is fast (because it does nothing) in our case.

VACUUM does read the 1st block to be sure readahead is done when ANALYSE does 
not.
For ANALYZE, maybe it is interesting to issue a read on the first block or use 
POSIX_FADVISE to (try) to force a readahead of the table when it is small 
enough (so ANALYSE can start working while blocks are read and put in cache).

That's being said, I am surprised that the pattern create table...analyze 
create table analyze of such smalls ones make the data being flush from OS 
cache so quickly that they need to be read again from disk.
Pavel, can you check the cache status of the tables just before the analyze ? 
(you can use OS tools or pgfincore extension for that)

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

-- 
Sent 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: URI connection string support for libpq

2012-02-27 Thread Cédric Villemain

Le vendredi 24 février 2012 14:18:44, Florian Weimer a écrit :
 * Alex Shulgin:
  It's ugly, but it's standard practice, and seems better than a separate
  -d parameter (which sort of defeats the purpose of URIs).
  
  Hm, do you see anything what's wrong with ?dbname=other if you don't
  like a separate -d?
 
 It's not nice URI syntax, but it's better than an out-of-band mechanism.

I've not followed all the mails about this feature but I don't find it is a 
nice syntax too.

?dbname=other looks like dbname is an argument, but dbname is a requirement 
for postgresql connexion.

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

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

Re: [HACKERS] check constraint validation takes access exclusive locks

2012-02-27 Thread Pavel Stehule

2012/2/27 Alvaro Herrera alvhe...@commandprompt.com:

 Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012:

 2012/2/27 Alvaro Herrera alvhe...@commandprompt.com:
 
  Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 
  -0300 2012:
  On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
   Hello
  
   I rechecked Depesz's article -
   http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
  
   The behave of current HEAD is different than behave described in 
   article.
  
   alter table a validate constraint a_a_check needs a access exclusive
   locks and blocks table modification - I tested inserts.
 
  yes, looks like we have revert to access exclusive lock:
 
  See commits
  2c3d9db56d5d49bdc777b174982251c01348e3d8
  and
  a195e3c34f1eeb6a607c342121edf48e49067ea9

  this block a sense of NOT VALIDATE constraints.

 Yeah :-(

 Is it final behave or will be fixed on 9.2?

 It's final for 9.2 AFAIK.  It's supposed to get fixed during the 9.3
 timeframe.

ok

thank you for info

Pavel


 --
 Álvaro Herrera alvhe...@commandprompt.com
 The PostgreSQL Company - Command Prompt, Inc.
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Patch to allow domains over composite types

2012-02-27 Thread Yeb Havinga


On 2012-02-27 12:49, Asif Rehman wrote:

Hi Yeb Havinga,

I was digging archives to see anyone worked on supporting domain's 
over composite type and found your patch, but that was pulled back. 
According to commitfest comments it needs some more work...


There were some issues with using the domains from pl/pgsql, which could 
probably made to work, but I didn't investigate it because at the time 
the use case for which is was needed was solved in a different way.


Are you going to submit the updated patch?


There is no updated patch, sorry.

regards,
Yeb


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

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-27 Thread Sandro Santilli

On Fri, Feb 24, 2012 at 08:21:05PM +0200, Peter Eisentraut wrote:
 On fre, 2012-02-24 at 17:26 +0100, Sandro Santilli wrote:
  We don't initdb with PostGIS regression testing framework
  but I've considered doing it for this specific case and it stroke me
  that even then we couldn't control SHAREDIR.
 
 I would always create a new instance using initdb for test runs.  It's
 easy and avoids all these problems.

Doesn't avoid the SHAREDIR problem, that's what I'm saying.
SHAREDIR is a compile-time setting in PostgreSQL.

--strk;

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

Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-27 Thread Sandro Santilli

On Sun, Feb 26, 2012 at 09:50:04PM -0500, Robert Haas wrote:
 On Sun, Feb 26, 2012 at 10:36 AM, Peter Eisentraut pete...@gmx.net wrote:
  On lör, 2012-02-25 at 14:21 +0100, Christoph Berg wrote:
  Well, I'm trying to invoke the extension's make check target at
  extension build time. I do have a temporary installation I own
  somehwere in my $HOME, but that is still trying to find extensions in
  /usr/share/postgresql/9.1/extension/*.control, because I am using the
  system's postgresql version. The build process is not running as root,
  so I cannot do an install of the extension to its final location.
  Still it would be nice to run regression tests. All that seems to be
  missing is the ability to put
 
  extension_control_path = /home/buildd/tmp/extension
 
  into the postgresql.conf of the temporary PG installation, or some
  other way like CREATE EXTENSION foobar WITH CONTROL
  '/home/buildd/...'.
 
  Yeah, of course, the extension path is not related to the data
  directory.  So we do need some kind of path setting, just like
  dynamic_library_path.
 
 That logic seems sound to me, so +1.

+1 here as well. I may attempt to produce a patch if this gets consensus.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net - http://vizzuality.com
  `-o--'


-- 
Sent 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 to know a table has been modified?

2012-02-27 Thread Lennin Caro

check the log of postgresql, there you can take the table name and the date of 
the modification


Ing. Lennin Caro Pérez

Usuario:GNU/LINUX

PHP Developer

PostgreSQL DBA

Oracle DBA

Linux  counter id 474393

--- On Mon, 2/27/12, Tatsuo Ishii is...@postgresql.org wrote:

From: Tatsuo Ishii is...@postgresql.org
Subject: Re: [HACKERS] How to know a table has been modified?
To: kevin.gritt...@wicourts.gov
Cc: pgsql-hackers@postgresql.org
Date: Monday, February 27, 2012, 12:04 PM

 For TRIGGER, I cannot thinking of any way. Any idea will be
 welcome.
  
 It would require creating cooperating triggers in the database and
 having a listener, but you might consider the
 triggered_change_notifications() trigger function included in 9.2. 
 It works at least as far back as 9.0; I haven't tried it any further
 back.

Thanks for the info. It's a little bit overkill for my purpose though.
(on busy systems, the notification would be too frequent).

I would think that creating a small routine periodically consults
pg_stat_all_tables view and records the last update datetime for each
table (unfortunately the view does not have last modification date).
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
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] Documentation: remove confusing paragraph about backslash escaping

2012-02-27 Thread Hannes Frederic Sowa


Hi!

As with recent changes to `standard_conforming_strings' the paragraph 
about backslash escaping in the description of `LIKE' is only confusing. 
Thus I attached a patch to remove it.


Greetings,

  Hannes

--
Hannes Sowa   hs...@bfk.de
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e8e637b..4b582f7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3383,18 +3383,6 @@ cast(-44 as bit(12))   lineannotation11010100/lineannotation
/para
 
para
-Note that the backslash already has a special meaning in string literals,
-so to write a pattern constant that contains a backslash you must write two
-backslashes in an SQL statement (assuming escape string syntax is used, see
-xref linkend=sql-syntax-strings).  Thus, writing a pattern that
-actually matches a literal backslash means writing four backslashes in the
-statement.  You can avoid this by selecting a different escape character
-with literalESCAPE/literal; then a backslash is not special to
-functionLIKE/function anymore. (But backslash is still special to the
-string literal parser, so you still need two of them to match a backslash.)
-   /para
-
-   para
 It's also possible to select no escape character by writing
 literalESCAPE ''/literal.  This effectively disables the
 escape mechanism, which makes it impossible to turn off the

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

1 2 >

1 - 100 of 111 matches

Mail list logo