Re: [HACKERS] fix ecpg core dump when there's a very long struct variable name in .pgc file

[Chen Huajun]

(2012/11/25 22:18), Michael Meskes wrote:
> On Sun, Nov 25, 2012 at 08:02:33PM +0800, Chen Huajun wrote:
>> Thanks for your comment,I will add the patch into commitfest later.
>
> No need for that, the patch is already committed to the archive.

Oh,I got it,Thanks!


--
Regards,
Chen Huajun




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

[HACKERS] Anybody using mutate_eclass_expressions() from add-on code?

[Tom Lane]

I looked into the problem reported in bug #7703, namely that queries
such as
select distinct min(x) from tab;
fail if "tab" is an inheritance tree and an index-optimized plan
using MergeAppend is possible.  What's happening is that
(1) the use of DISTINCT causes us to create an EquivalenceClass
containing min(x), which is needed since we may have to reason about
sorting on that expression;
(2) expansion of the inheritance tree causes us to add child
EquivalenceClass members representing min() applied to each of tab's
child tables;
(3) when planagg.c tries to replace min(x) with a Param, the
mutate_eclass_expressions(..., replace_aggs_with_params_mutator, ...)
call spits up because it doesn't know what to do with the child-table
min() expressions.

I thought about fixing this by just changing
replace_aggs_with_params_mutator to not complain about unmatched
aggregate expressions, but that seems awfully likely to obscure future
bugs.  What seems like a better fix is to change the processing so that
child EquivalenceClass members aren't processed when doing the Param
replacement --- this should be OK since we won't need them after this
point.  However, that requires changing the behavior of 
mutate_eclass_expressions().  In the attached proposed patch I added a
new bool parameter to control whether child expressions are mutated.

This needs to be back-patched to 9.1, but I'm slightly worried about
whether changing the function's API in back branches would break any
add-on code.  It seems fairly unlikely that anything outside the core
planner is calling this function, but I thought I'd better ask.

Comments?

regards, tom lane

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 58553df3140f03278b8f6e8effdfe7e01615367a..632295197a9b08e35d3f8bb44377c26143a043c2 100644
*** a/src/backend/optimizer/path/equivclass.c
--- b/src/backend/optimizer/path/equivclass.c
*** add_child_rel_equivalences(PlannerInfo *
*** 1973,1984 
  /*
   * mutate_eclass_expressions
   *	  Apply an expression tree mutator to all expressions stored in
!  *	  equivalence classes.
   *
   * This is a bit of a hack ... it's currently needed only by planagg.c,
   * which needs to do a global search-and-replace of MIN/MAX Aggrefs
   * after eclasses are already set up.  Without changing the eclasses too,
!  * subsequent matching of ORDER BY clauses would fail.
   *
   * Note that we assume the mutation won't affect relation membership or any
   * other properties we keep track of (which is a bit bogus, but by the time
--- 1973,1984 
  /*
   * mutate_eclass_expressions
   *	  Apply an expression tree mutator to all expressions stored in
!  *	  equivalence classes (but ignore child exprs unless include_child_exprs).
   *
   * This is a bit of a hack ... it's currently needed only by planagg.c,
   * which needs to do a global search-and-replace of MIN/MAX Aggrefs
   * after eclasses are already set up.  Without changing the eclasses too,
!  * subsequent matching of ORDER BY and DISTINCT clauses would fail.
   *
   * Note that we assume the mutation won't affect relation membership or any
   * other properties we keep track of (which is a bit bogus, but by the time
*** add_child_rel_equivalences(PlannerInfo *
*** 1988,1994 
  void
  mutate_eclass_expressions(PlannerInfo *root,
  		  Node *(*mutator) (),
! 		  void *context)
  {
  	ListCell   *lc1;
  
--- 1988,1995 
  void
  mutate_eclass_expressions(PlannerInfo *root,
  		  Node *(*mutator) (),
! 		  void *context,
! 		  bool include_child_exprs)
  {
  	ListCell   *lc1;
  
*** mutate_eclass_expressions(PlannerInfo *r
*** 2001,2006 
--- 2002,2010 
  		{
  			EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
  
+ 			if (cur_em->em_is_child && !include_child_exprs)
+ continue;		/* ignore children unless requested */
+ 
  			cur_em->em_expr = (Expr *)
  mutator((Node *) cur_em->em_expr, context);
  		}
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c
index 55a5ed7b4c6320886e1e0d36d7a6aa857efc25e2..658a4abc31570f6046721602c6eed0fcaf10a6e5 100644
*** a/src/backend/optimizer/plan/planagg.c
--- b/src/backend/optimizer/plan/planagg.c
*** optimize_minmax_aggregates(PlannerInfo *
*** 257,263 
  
  	/*
  	 * We have to replace Aggrefs with Params in equivalence classes too, else
! 	 * ORDER BY or DISTINCT on an optimized aggregate will fail.
  	 *
  	 * Note: at some point it might become necessary to mutate other data
  	 * structures too, such as the query's sortClause or distinctClause. Right
--- 257,266 
  
  	/*
  	 * We have to replace Aggrefs with Params in equivalence classes too, else
! 	 * ORDER BY or DISTINCT on an optimized aggregate will fail.  We don't
! 	 * need to process child eclass members though, since they aren't of
! 	 * interest anymore --- and replace_a

Re: [HACKERS] Removing PD_ALL_VISIBLE

[Tom Lane]

Jeff Davis  writes:
> Now it tries to keep the VM buffer pinned during scans, inserts,
> updates, and deletes. This should avoid increased contention pinning the
> VM pages, but performance tests are required.
> ...
> Then again, if a 5GB table is being randomly accessed, an extra pin is
> unlikely to matter. Also, without locality, the contention would not be
> nearly as bad either. I'm still pretty unclear what the "worst case" for
> this patch is supposed to look like.

I'd be worried about the case of a lot of sessions touching a lot of
unrelated tables.  This change implies doubling the number of buffers
that are held pinned by any given query, and the distributed overhead
from that (eg, adding cycles to searches for free buffers) is what you
ought to be afraid of.

Another possibly important point is that reducing the number of
pin/unpin cycles for a given VM page might actually hurt the chances of
it being found in shared buffers, because IIRC the usage_count is bumped
once per pin/unpin.  That algorithm is based on the assumption that
buffer pins are not drastically different in lifespan, but I think you
just broke that for pins on VM pages.

I'm not particularly concerned about devising solutions for these
problems, though, because I think this idea is a loser from the get-go;
the increase in contention for VM pages is alone going to destroy any
possible benefit.

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] Removing PD_ALL_VISIBLE

[Jeff Davis]

On Wed, 2012-11-21 at 18:25 -0800, Jeff Davis wrote:
> Follow up to discussion:
> http://archives.postgresql.org/pgsql-hackers/2012-11/msg00817.php
> 
> I worked out a patch that replaces PD_ALL_VISIBLE with calls to
> visibilitymap_test. It rips out a lot of complexity, with a net drop of
> about 300 lines (not a lot, but some of that code is pretty complex).

Updated patch attached.

Now it tries to keep the VM buffer pinned during scans, inserts,
updates, and deletes. This should avoid increased contention pinning the
VM pages, but performance tests are required.

For updates, it currently only tries to hold a pin on the VM buffer for
the page of the original tuple. For HOT updates, that's always the same
as the new buffer anyway. For cold updates, we could also try to keep a
pin on the buffer for the new tuple, but right now I don't see an
obvious need for that complexity. It may plausibly be a problem when
doing a bulk update on a freshly-loaded table.

It occurred to me that it might be difficult to test this patch without
a fairly large test case. A big assumption of my patch is that there
will be locality of access (and the VM page you already have a pin on is
likely to be needed the next time), which is obvious during a scan but
not so obvious during I/U/D. But a single 8K VM page represents some 60K
pages, or about 500MB of data. So anything less than that means that
there is only one VM page, and locality is trivial... it seems like any
test on a table less than 5GB would not be fair.

Then again, if a 5GB table is being randomly accessed, an extra pin is
unlikely to matter. Also, without locality, the contention would not be
nearly as bad either. I'm still pretty unclear what the "worst case" for
this patch is supposed to look like.

Regards,
    Jeff Davis



rm-pd-all-visible-20121125.patch.gz
Description: GNU Zip compressed data

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

Re: [HACKERS] Enabling Checksums

[Jeff Davis]

Updated both patches.

Changes:
  * Moved the changes to pageinspect into the TLI patch, because it
makes more sense to be a part of that patch and it also reduces the size
of the main checksums patch.
  * Fix off-by-one bug in checksum calculation
  * Replace "VerificationInfo" in the function names with "Checksum",
which is shorter.
  * Make the checksum algorithm process 4 bytes at a time and sum into a
signed 64-bit int, which is faster than byte-at-a-time. Also, forbid
zero in either byte of the checksum, because that seems like a good
idea.

I've done quite a bit of testing at this point, and everything seems
fine to me. I've tested various kinds of errors (bytes being modified or
zeroed at various places of the header and data areas, transposed pages)
at 8192 and 32768 page sizes. I also looked at the distribution of
checksums in various ways (group by checksum %  for various
primes, and not seeing any skew), and I didn't see any worrying
patterns.

Regards,
Jeff Davis



replace-tli-with-checksums-20121125.patch.gz
Description: GNU Zip compressed data


checksums-20121125.patch.gz
Description: GNU Zip compressed data

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

Re: [HACKERS] [WIP] pg_ping utility

[Michael Paquier]

On Mon, Nov 26, 2012 at 11:17 AM, Phil Sorber  wrote:

> I am going to be unavailable until Wednesday, so maybe gives us a few
> more days for feedback.
>
Sure no problem. Thanks.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [HACKERS] [WIP] pg_ping utility

[Phil Sorber]

I am going to be unavailable until Wednesday, so maybe gives us a few
more days for feedback.

On Fri, Nov 23, 2012 at 9:48 AM, Michael Paquier
 wrote:
>
>
> On Sat, Nov 17, 2012 at 2:48 AM, Phil Sorber  wrote:
>>
>> On Thu, Nov 15, 2012 at 10:55 PM, Michael Paquier
>>  wrote:
>> > On Fri, Nov 16, 2012 at 12:34 PM, Phil Sorber  wrote:
>> >> On Thu, Nov 15, 2012 at 9:23 PM, Michael Paquier
>> >>  wrote:
>> >> > 3) Having an output close to what ping actually does would also be
>> >> > nice,
>> >> > the
>> >> > current output like Accepting/Rejecting Connections are not that
>> >>
>> >> Could you be more specific? Are you saying you don't want to see
>> >> accepting/rejecting info output?
>> >
>> > OK sorry.
>> >
>> > I meant something like that for an accessible server:
>> > $ pg_ping -c 3 -h server.com
>> > PING server.com (192.168.1.3)
>> > accept from 192.168.1.3: icmp_seq=0 time=0.241 ms
>> > accept from 192.168.1.3: icmp_seq=0 time=0.240 ms
>> > accept from 192.168.1.3: icmp_seq=0 time=0.242 ms
>> >
>> > Like that for a rejected connection:
>> > reject from 192.168.1.3: icmp_seq=0 time=0.241 ms
>> >
>> > Like that for a timeout:
>> > timeout from 192.168.1.3: icmp_seq=0
>> > Then print 1 line for each ping taken to stdout.
>>
>> How does icmp_seq fit into this? Or was that an oversight?
>>
>> Also, in standard ping if you don't pass -c it will continue to loop
>> until interrupted. Would you suggest that pg_ping mimic that, or that
>> we add an additional flag for that behavior?
>>
>> FWIW, I would use 'watch' with the existing output for cases that I
>> would need something like that.
>
> We waited a couple of days for feedback for this feature. So based on all
> the comments provided by everybody on this thread, perhaps we should move on
> and implement the options that would make pg_ping a better wrapper for
> PQPing. Comments?
> --
> Michael Paquier
> http://michael.otacoo.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] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Sun, Nov 25, 2012 at 7:39 PM, Tom Lane  wrote:
> I think this is ignoring the fact that we have an extensible type
> system, and thus a lot more room for problems if we allow too many
> implicit casts.

I don't deny that.

> It might also be worth noting that some of this complexity comes from
> the SQL standard.  It is at least arguable that the three-way coercion
> distinction exists in the standard: they have got different rules for
> what happens in an explicit CAST, in an assignment context, and in plain
> expressions.  So it's not that relevant whether other mainstream
> programming languages have comparable constructs.
>
> Having said that, though, I think you could make an argument that
> there's some support for this idea in the SQL standard.  In SQL99
> 10.4 , it appears that once you've identified
> a target routine to be called, you're supposed to use the "store
> assignment" rules to decide how to convert the supplied expression(s)
> to the parameter data type(s).  However, it's not clear to me that
> that should be taken as conclusive, because the $64 question here
> is exactly how sure you are in your identification of the target
> routine.  SQL99 doesn't seem to allow anywhere near as much function
> overloading as we do --- and of course they have no notion of
> overloaded or user-defined operators at all.  As far as I can tell
> from 10.4, you are supposed to be able to identify the target routine
> without any consideration of the actual parameters' types.

FWIW, neither MySQL nor Oracle supports function overloading for plain
functions, so the question doesn't arise for them in the context of
something like LPAD().  Oracle does support overloading for package
functions, and I'm not sure exactly how they identify candidate
functions in that context, but they do complain about ambiguous calls
in some circumstances.

Personally, I'm not sure that anyone has come up with an altogether
satisfactory solution to the function overloading problem.  If you
have an exact type match in every argument position for one of the
possible candidate functions, then surely any system that permits
overloading at all is going to pick that candidate.  Conversely, if
you have one or many candidates all of which are completely
incompatible with the actual argument types, then any system is going
to fail.  The tension is all around what to do when you have several
candidates which are about equally good.  You can either reject the
call as ambiguous (which will sometimes annoy users who don't feel
that a cast should be needed) or you can use some sort of tiebreak
system to pick a candidate (which risks picking a different function
than the user expected).  I tend to think it's better to err on the
side of the former, and I think we do, but there might nonetheless be
some for improvement in that area, with due regard for the possibility
of breaking currently-working applications.

That, however, is a separate question from what's under discussion
here, because the case at issue for the proposed patch is the one in
which only one possible candidate exists, and the question is whether
we ought to allow the use of assignment casts to allow the call to
work rather than fail, NOT which of several overloaded functions we
ought to pick.  In any situation in which overloading is in use, the
patch as proposed changes nothing.  I'm not generally very good at
interpreting the SQL standard text, but if it says that you ought to
use assignment casts to match actual argument types to the chosen
candidate function, then that seems like it's advocating for
essentially the same position that you arrived at independently and
that the patch also takes, which furthermore happens to be compatible
with what other RDBMS systems do, at least in the no-overloading case.

-- 
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] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Sun, Nov 25, 2012 at 6:46 PM, Tom Lane  wrote:
> Robert Haas  writes:
> I think we're talking past each other here.  It is unarguable that
> (as long as there's only one LPAD function) there is only one possible
> non-error interpretation.  However, you are ignoring the real
> possibility that perhaps the situation *is* an error: maybe the user
> typed the wrong function name, or the wrong field name, or simply
> misunderstands what the function is meant to do.

Yeah, but of course all of that could also be true even if the
argument types match exactly, too.  I mean, I won't deny that the
presence of an exact argument-type match lends a little bit of
additional confidence that the call is the one the user intended, but
a user is more likely to confuse LPAD with RPAD than they are to
confuse either of them with a function that does something completely
different but is spelled almost the same (LDAP?).

I think it's also worth reiterating that, whatever you may think of
the LPAD case, there is a significant win here in allowing things like
foo(3) to match foo(smallint) in the absence of any other
foo-of-one-argument.  Nobody's even attempted to argue that the
current behavior in that situation is desirable, and the fact that
this would fix it in nearly all of the cases that anyone cares about
strikes me as a distinct point in its favor.

-- 
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] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Sun, Nov 25, 2012 at 7:36 PM, Peter Geoghegan  wrote:
> On 26 November 2012 00:24, Robert Haas  wrote:
>> I remember this sort of thing un-fondly from my C++ days, but it
>> doesn't make me like our current behavior any better.
>
> You can also make a constructor with a single argument "explicit", and
> thereby prevent implicit conversions. So yes, C++ distinguishes
> between
> explicit, assignment, and implicit casting in a way that is, in broad
> strokes, at least as sophisticated as PostgreSQL.

OK, I stand corrected.

-- 
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] MySQL search query is not executing in Postgres DB

[Tom Lane]

Robert Haas  writes:
> So I think the answer to your question is probably no, purely on the
> grounds that we have set a new world record for byzantine casting
> systems.   A more fair comparison might be to look at what other SQL
> systems allow.  Oracle, MySQL, and SQL Server all permit implicit
> casting between integer and text, and a call to LPAD with an integer
> first argument works just fine in both Oracle and MySQL.  It doesn't
> work in SQL server, but that's only because SQL server doesn't have it
> as a built-in function.  FWICT, there's no general problem with
> passing an integer to a function that expects varchar in any
> mainstream RDBMS other than PostgreSQL.

I think this is ignoring the fact that we have an extensible type
system, and thus a lot more room for problems if we allow too many
implicit casts.

It might also be worth noting that some of this complexity comes from
the SQL standard.  It is at least arguable that the three-way coercion
distinction exists in the standard: they have got different rules for
what happens in an explicit CAST, in an assignment context, and in plain
expressions.  So it's not that relevant whether other mainstream
programming languages have comparable constructs.

Having said that, though, I think you could make an argument that
there's some support for this idea in the SQL standard.  In SQL99
10.4 , it appears that once you've identified
a target routine to be called, you're supposed to use the "store
assignment" rules to decide how to convert the supplied expression(s)
to the parameter data type(s).  However, it's not clear to me that
that should be taken as conclusive, because the $64 question here
is exactly how sure you are in your identification of the target
routine.  SQL99 doesn't seem to allow anywhere near as much function
overloading as we do --- and of course they have no notion of
overloaded or user-defined operators at all.  As far as I can tell
from 10.4, you are supposed to be able to identify the target routine
without any consideration of the actual parameters' types.

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] MySQL search query is not executing in Postgres DB

[Peter Geoghegan]

On 26 November 2012 00:24, Robert Haas  wrote:
> I remember this sort of thing un-fondly from my C++ days, but it
> doesn't make me like our current behavior any better.

You can also make a constructor with a single argument "explicit", and
thereby prevent implicit conversions. So yes, C++ distinguishes
between
explicit, assignment, and implicit casting in a way that is, in broad
strokes, at least as sophisticated as PostgreSQL.

-- 
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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Materialized views WIP patch

[Marko Tiikkaja]

Hi Kevin,

On 15/11/2012 03:28, Kevin Grittner wrote:

Attached is a patch that is still WIP but that I think is getting
pretty close to completion.


I've been looking at this, but I unfortunately haven't had as much time 
as I had hoped for, and have not looked at the code in detail yet.  It's 
also a relatively big patch, so I wouldn't mind another pair of eyes on it.



I have been testing the patch a bit, and I'm slightly disappointed by 
the fact that it still doesn't solve this problem (and I apologize if I 
have missed discussion about this in the docs or in this thread):




T1: BEGIN;
T1: LOAD MATERIALIZED VIEW foo;

T2: SELECT * FROM foo;

T1: COMMIT;




As others have pointed out, replacing the contents of a table is 
something which people have been wanting to do for a long time, and I 
think having this ability would make this patch a lot better; now it 
just feels like syntactic sugar.



1.  CREATE MATERIALIZED VIEW syntax is stolen directly from CREATE
 TABLE AS, with all the same clauses supported. That includes
 declaring a materialized view to be temporary or unlogged.
2.  MVs don't support inheritance.
3.  MVs can't define foreign keys.
4.  MVs can't be the target of foreign keys.
5.  MVs can't have triggers.
6.  Users can't create rules which reference MVs (although MVs
 [ab]use the rules mechanism internally, similar to how views do).
7.  MVs can't be converted to views, nor vice versa.
8.  Users may not directly use INSERT/UPDATE/DELETE on an MV.
9.  MVs can't directly be used in a COPY statement, but can be the
 source of data using a SELECT.
10. MVs can't own sequences.
11. MVs can't be the target of LOCK statements, although other
 statements get locks just like a table.
12. MVs can't use data modifying CTEs in their definitions.
13. pg_class now has a relisvalid column, which is true if an MV is
 truncated or created WITH NO DATA. You can not scan a relation
 flagged as invalid.
14. ALTER MATERIALIZED VIEW is supported for the options that seemed
 to make sense. For example, you can change the tablespace or
 schema, but you cannot add or drop column with ALTER.
16. To get new data into the MV, the command is LOAD MATERIALIZED
 VIEW mat view_name. This seemed more descriptive to me that the
 alternatives and avoids declaring any new keywords beyond
 MATERIALIZED. If the MV is flagged as relisvalid == false, this
 will change it to true.
17. Since the data viewed in an MV is not up-to-date with the latest
 committed transaction, it didn't seem to make any sense to try to
 apply SERIALIZABLE transaction semantics to queries looking at
 the contents of an MV, although if LMV is run in a SERIALIZABLE
 transaction the MV data is guaranteed to be free of serialization
 anomalies. This does leave the transaction running the LOAD
 command vulnerable to serialization failures unless it is also
 READ ONLY DEFERRABLE.
18. Bound parameters are not supported for the CREATE MATERIALIZED
 VIEW statement.


I believe all of these points have been under discussion, and I don't 
have anything to add to the ongoing discussions.



19. LMV doesn't show a row count. It wouldn't be hard to add, it just
 seemed a little out of place to do that, when CLUSTER, etc.,
 don't.


This sounds like a useful feature, but your point about CLUSTER and 
friends still stands.



In the long term, we will probably need to separate the
implementation of CREATE TABLE AS and CREATE MATERIALIZED VIEW, but
for now there is so little that they need to do differently it seemed
less evil to have a few "if" clauses that that much duplicated code.


Seems sensible.

I'll get back when I manage to get a better grasp of the code.


Regards,
Marko Tiikkaja


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

Re: [HACKERS] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Sun, Nov 25, 2012 at 7:05 PM, Peter Geoghegan  wrote:
> Well, you can make your class copy-constructable by providing a
> constructor (and a copy-assignment operator) whose only argument is,
> say, an int. In additional to that, you could potentially define a
> conversion operator, which will make the class implicitly cast back
> into an int. That is kind of a big distinction, because it doesn't
> have to go both ways, and in fact it usually doesn't - plenty of
> working C++ programmers don't know what a conversion operator is, but
> they could all tell you how to get this behaviour:
>
> MyClass foo = 5; // actually calls copy constructor - equivalent to
> MyClass foo(5);
> foo = 4; // This calls copy assignment operator

I remember this sort of thing un-fondly from my C++ days, but it
doesn't make me like our current behavior any better.  As in C++, we
seem to have created a system where the only way to get even locally
sensible behavior is to throw large piles of hackery at the problem.
Getting the behavior you want globally cannot be obtained at any
price.

-- 
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] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Sun, Nov 25, 2012 at 6:46 PM, Tom Lane  wrote:
> I think we're talking past each other here.  It is unarguable that
> (as long as there's only one LPAD function) there is only one possible
> non-error interpretation.  However, you are ignoring the real
> possibility that perhaps the situation *is* an error: maybe the user
> typed the wrong function name, or the wrong field name, or simply
> misunderstands what the function is meant to do.  If it is a typo then
> complaining about the datatype mismatch is a good thing to do.  If it
> is intentional, then requiring an explicit cast makes it clear to all
> concerned that what's wanted is to convert the non-string value to a
> string and then perform a string-ish operation on it.

Sure, in theory that is true, but no other RDBMS that I know about
feels a need to error out in that situation.  I'm skeptical of the
contention that we're smarter than everyone else.  Moreover, if
implicit casts to string are a categorically bad idea, why do we allow
them (via various evil hacks) for quote_literal(), concat(), and ||?

-- 
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] MySQL search query is not executing in Postgres DB

[Peter Geoghegan]

On 25 November 2012 23:31, Robert Haas  wrote:
> The only other programming
> language I know of in which you can define what it means to cast
> between two data types is C++, and it's not generally considered one
> of that languages better features.  AFAICT, they have implicit casts
> and explicit casts, but nothing intermediate.

Well, you can make your class copy-constructable by providing a
constructor (and a copy-assignment operator) whose only argument is,
say, an int. In additional to that, you could potentially define a
conversion operator, which will make the class implicitly cast back
into an int. That is kind of a big distinction, because it doesn't
have to go both ways, and in fact it usually doesn't - plenty of
working C++ programmers don't know what a conversion operator is, but
they could all tell you how to get this behaviour:

MyClass foo = 5; // actually calls copy constructor - equivalent to
MyClass foo(5);
foo = 4; // This calls copy assignment operator

-- 
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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] MySQL search query is not executing in Postgres DB

[Tom Lane]

Robert Haas  writes:
> On Thu, Nov 22, 2012 at 10:17 AM, Tom Lane  wrote:
>> The argument here is basically between ease of use and ability to detect
>> common programming mistakes.  It's not clear to me that there is any
>> principled way to make such a tradeoff, because different people can
>> reasonably put different weights on those two goals.

> I think that is true.  But for whatever it's worth, and at the risk of
> beating a horse that seems not to be dead yet in spite of the fact
> that I feel I've already administered one hell of a beating, the LPAD
> case is unambiguous, and therefore it is hard to see what sort of
> programming mistake we are protecting users against.

I think we're talking past each other here.  It is unarguable that
(as long as there's only one LPAD function) there is only one possible
non-error interpretation.  However, you are ignoring the real
possibility that perhaps the situation *is* an error: maybe the user
typed the wrong function name, or the wrong field name, or simply
misunderstands what the function is meant to do.  If it is a typo then
complaining about the datatype mismatch is a good thing to do.  If it
is intentional, then requiring an explicit cast makes it clear to all
concerned that what's wanted is to convert the non-string value to a
string and then perform a string-ish operation on it.

regards, tom lane


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

Re: [HACKERS] MySQL search query is not executing in Postgres DB

[Josh Berkus]

> I'd be fine with that if we had a system that allows users to pick the
> behavior that they want in their particular environment, but in fact
> our existing system is extremely inflexible.  If you start adding
> additional implicit casts to the system, you get failures trying to
> invoke commonly-used system functions, because we've got overloaded
> versions of them precisely to work around the fact that our casting
> rules are more restrictive than real-world users want them to be.  If
> that's not prima facie evidence that the system doesn't work well in
> the real world, I'm not sure what would qualify.

They don't even work particularly well for db hackers.  I went down the
rabbit hole of trying to make TEXT = CITEXT be a default cast to CITEXT,
and after several days of struggling with breaking system functions, I
gave up.

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


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

Re: [HACKERS] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Thu, Nov 22, 2012 at 10:17 AM, Tom Lane  wrote:
> The argument here is basically between ease of use and ability to detect
> common programming mistakes.  It's not clear to me that there is any
> principled way to make such a tradeoff, because different people can
> reasonably put different weights on those two goals.

I think that is true.  But for whatever it's worth, and at the risk of
beating a horse that seems not to be dead yet in spite of the fact
that I feel I've already administered one hell of a beating, the LPAD
case is unambiguous, and therefore it is hard to see what sort of
programming mistake we are protecting users against.  If there's only
one function called bob, and the user says bob(x), it is hard to see
what behavior, other than calling bob with x as an argument, would be
even mildly sensible.  (Yes, OK, there are two lpad functions, but as
you pointed out previously, they take different numbers of arguments,
so the point still stands.)

-- 
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] MySQL search query is not executing in Postgres DB

[Robert Haas]

On Wed, Nov 21, 2012 at 5:10 PM, Peter Eisentraut  wrote:
> Because a strongly-typed system should not cast numbers to strings
> implicitly.  Does the equivalent of the lpad case work in any other
> strongly-typed programming language?

Does any other strongly-typed programming language distinguish between
explicit, assignment, and implicit casting the way that PostgreSQL
does?  In order for the equivalent of the lpad case to exist in some
other programming language, I think they'd need to make that
distinction, and AFAICT no one does that.  The only other programming
language I know of in which you can define what it means to cast
between two data types is C++, and it's not generally considered one
of that languages better features.  AFAICT, they have implicit casts
and explicit casts, but nothing intermediate.  There are dynamic_cast,
static_cast, and reinterpret_cast as well, but those trade-off
efficiency for the possibility of a segmentation fault, and have
nothing to do with the context in which the cast can be applied
automatically.

So I think the answer to your question is probably no, purely on the
grounds that we have set a new world record for byzantine casting
systems.   A more fair comparison might be to look at what other SQL
systems allow.  Oracle, MySQL, and SQL Server all permit implicit
casting between integer and text, and a call to LPAD with an integer
first argument works just fine in both Oracle and MySQL.  It doesn't
work in SQL server, but that's only because SQL server doesn't have it
as a built-in function.  FWICT, there's no general problem with
passing an integer to a function that expects varchar in any
mainstream RDBMS other than PostgreSQL.

>> 2. What's your counter-proposal?
>
> Leave things as they are.

I'd be fine with that if we had a system that allows users to pick the
behavior that they want in their particular environment, but in fact
our existing system is extremely inflexible.  If you start adding
additional implicit casts to the system, you get failures trying to
invoke commonly-used system functions, because we've got overloaded
versions of them precisely to work around the fact that our casting
rules are more restrictive than real-world users want them to be.  If
that's not prima facie evidence that the system doesn't work well in
the real world, I'm not sure what would qualify.

-- 
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] Doc patch: Document names of automatically created constraints and indexes

[Robert Haas]

On Sat, Nov 24, 2012 at 6:01 PM, Peter Eisentraut  wrote:
> On Wed, 2012-11-21 at 15:12 -0500, Robert Haas wrote:
>> On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut  wrote:
>> > On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote:
>> >> Could ALTER TABLE use an option to drop the
>> >> primary key constraint?  I needed to do that,
>> >> found it was not obvious, and this lead me to
>> >> try to improve things.
>> >
>> > That could be useful, I think.  But it might open a can of worms.
>>
>> Would the new option be syntactic sugar around ALTER TABLE ... DROP
>> CONSTRAINT "put_the_name_of_the_primary_key_here"?
>
> Yes, I think so.  We already have DROP NOT NULL, which is a similar case
> (except, of course, that it was born more out of necessity, because
> not-null constraints don't have a name, but that's being worked on).

Yeah.  As usability issues go I think the lack of this syntax is a
fairly minor one, but I confess to having wanted to be able to type
ALTER TABLE foo DROP PRIMARY KEY more than once, so I wouldn't argue
if someone wanted to go make that happen.

-- 
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] WIP: index support for regexp search

[Alexander Korotkov]

Hi!

On Wed, Nov 21, 2012 at 12:51 AM, Pavel Stehule wrote:

> do you plan to support GiST?
>

At first, I would note that pg_trgm GiST opclass is quite ridiculous for
support regex search (and, actually for LIKE/ILIKE search which is already
implemented too). Because in GiST opclass we store set of trigrams in leaf
pages. In was designed for trigram similarity search and have sense for it
because of elimination of trigram set computation. But for regex or
LIKE/ILIKE search this representation is both lossy and bigger than just
original string. Probably we could think about another opclass for GiST
focusing on regex and LIKE/ILIKE search?

However, amyway I can create additional patch for current GiST opclass.

--
With best regards,
Alexander Korotkov.

Re: [HACKERS] WIP: index support for regexp search

[Alexander Korotkov]

On Tue, Nov 20, 2012 at 1:43 PM, Heikki Linnakangas  wrote:

> Glad to see this patch hasn't been totally forgotten. Being able to use
> indexes for regular expressions would be really cool!
>
> Back in January, I asked for some high-level description of how the
> algorithm works (http://archives.postgresql.**
> org/message-id/4F187D5C.30701@**enterprisedb.com).
> That's still sorely needed. Googling around, I found the slides for your
> presentation on this from PGConf.EU - it would be great to have the
> information from that presentation included in the patch.


New version of patch is attached. The changes are following:
1) A big comment with high-level description of what is going on.
2) Regression tests.
3) Documetation update.
4) Some more refactoring.

--
With best regards,
Alexander Korotkov.


trgm-regexp-0.5.patch.gz
Description: GNU Zip compressed data

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

Re: [HACKERS] fix ecpg core dump when there's a very long struct variable name in .pgc file

[Michael Meskes]

On Sun, Nov 25, 2012 at 08:02:33PM +0800, Chen Huajun wrote:
> Thanks for your comment,I will add the patch into commitfest later.

No need for that, the patch is already committed to the archive.

> It maybe my first patch for open source.

It definitely is then.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL


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

Re: [HACKERS] fix ecpg core dump when there's a very long struct variable name in .pgc file

[Chen Huajun]

> How on earth did you run into this? :)

ooh, first I saw the code accidentally,it looks a bit dangerous and differents 
from the function ECPGdump_a_simple() above,
And then I tried to write a test to raise some errors.

Thanks for your comment,I will add the patch into commitfest later.
It maybe my first patch for open source.
And I am glad if I can do more for PostgreSQL which is so fine.:)

Regards,
Chen Huajun
(2012/11/23 21:42), Michael Meskes wrote:

On Thu, Nov 22, 2012 at 06:09:20PM +0800, Chen Huajun wrote:

When use a struct variable whose name length is very very long such as 12KB in 
.pgc source,
ecpg will core dump because of buffer overflow if precompile the .pgc file.


How on earth did you run into this? :)

I absolutely agree that this is better be fixed and cjust committed the second
version of your patch.

Thanks.

Michael






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