Re: [HACKERS] Failback with log shipping

On Fri, May 28, 2010 at 7:58 PM, Heikki Linnakangas wrote: > At PGCon, several people asked me about restarting an old master as a > standby after failover has happened. And it wasn't the first time people ask > me about it, even before 9.0. We have no mention of that in the docs, which > is a pre

Re: [HACKERS] small exclusion constraints patch

Tom Lane wrote: > Jeff Davis writes: > > Currently, the check for exclusion constraints performs a sanity check > > that's slightly too strict -- it assumes that a tuple will conflict with > > itself. That is not always the case: the operator might be "<>", in > > which case it's perfectly valid f

Re: [HACKERS] Exposing the Xact commit order to the user

On May 28, 2010, at 7:19 PM, Bruce Momjian wrote: Jan Wieck wrote: Reading the entire WAL just to find all COMMIT records, then go back to the origin database to get the actual replication log you're looking for is simpler and more efficient? I don't think so. Agreed, but I think I've not

Re: [HACKERS] Exposing the Xact commit order to the user

Jan Wieck wrote: > >> Reading the entire WAL just to find all COMMIT records, then go back to > >> the origin database to get the actual replication log you're looking for > >> is simpler and more efficient? I don't think so. > > > > Agreed, but I think I've not explained myself well enough. > >

Re: [HACKERS] Clearing psql's input buffer after auto-reconnect

Tom Lane wrote: > We determined that $SUBJECT would be a good idea in this thread: > http://archives.postgresql.org/pgsql-bugs/2010-05/msg00159.php > > I looked a bit at what it would take to make this happen. The > difficulty is that the input buffer is a local variable in MainLoop(), > and so a

Re: [HACKERS] psql's is_select_command is naive

Robert Haas wrote: > On Wed, May 26, 2010 at 10:35 PM, Tom Lane wrote: > > Robert Haas writes: > >> It knows that queries beginning with "select" or "values" are select > >> commands, but it seems not to be clued in about "table" and "with". > > > > What we really ought to do IMO is throw out the

Re: [HACKERS] tsvector pg_stats seems quite a bit off.

On 28/05/10 22:22, Tom Lane wrote: > The idea that I was toying with is to assume a Zipfian distribution of > the input (with some reasonable parameter), and use that to estimate > what the frequency of the K'th element will be, where K is the target > number of MCV entries or perhaps a bit more.

Re: [HACKERS] How to pass around collation information

On Fri, May 28, 2010 at 10:32:34PM +0300, Peter Eisentraut wrote: > On fre, 2010-05-28 at 14:48 -0400, Tom Lane wrote: > > > SELECT * FROM test WHERE a COLLATE en > 'baz' ORDER BY b COLLATE sv; > > > > That seems fairly bizarre. What does this mean: > > > > WHERE a COLLATE en > b COLLATE de

Re: [HACKERS] List traffic

On Fri, May 28, 2010 at 3:44 PM, Josh Berkus wrote: > On 5/27/10 5:42 PM, Tom Lane wrote: >> Josh Berkus writes: >>> We do not have a problem.   The lists are fine the way they are. >> >> +1 ... wasn't the point I thought you were trying to make, but I'm >> good with not changing things. > > Yeah

Re: [HACKERS] How to pass around collation information

On 28/05/10 23:15, Robert Haas wrote: On Fri, May 28, 2010 at 3:20 PM, Peter Eisentraut wrote: On fre, 2010-05-28 at 15:03 -0400, Robert Haas wrote: I think we need to think of the comparison operators as ternary, and the COLLATE syntax applied to columns or present in queries as various ways

Re: [HACKERS] Failback with log shipping

On 28/05/10 22:20, Dimitri Fontaine wrote: Heikki Linnakangas writes: Not shipped before the first failover you mean? No, if any WAL records were created in the old master that were not shipped to the standby before failover, the corresponding changes to the data files might've been flushed to

Re: [HACKERS] List traffic

On 5/27/10 5:42 PM, Tom Lane wrote: > Josh Berkus writes: >> We do not have a problem. The lists are fine the way they are. > > +1 ... wasn't the point I thought you were trying to make, but I'm > good with not changing things. Yeah, that's because I was responding to the suggestion that 5 of

Re: [HACKERS] How to pass around collation information

2010/5/28 alvherre : > Excerpts from Peter Eisentraut's message of vie may 28 12:27:52 -0400 2010: > >> Option 2, invent some new mechanism that accompanies a datum or a type >> whereever it goes.  Kind of like typmod, but not really.  Then the >> collation information would presumably be made avai

Re: [HACKERS] tsvector pg_stats seems quite a bit off.

=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= writes: > We follow the algorithm as written, the trouble starts when we want to > output the result. The paper says which items from the D structure > should be returned when the user asks for items that have frequencies > higher than a threshold s. What we want t

Re: [HACKERS] How to pass around collation information

On Fri, May 28, 2010 at 3:20 PM, Peter Eisentraut wrote: > On fre, 2010-05-28 at 15:03 -0400, Robert Haas wrote: >> I think we need to think of the comparison operators as ternary, and >> the COLLATE syntax applied to columns or present in queries as various >> ways of setting defaults or explicit

Re: [HACKERS] [PATCH] Add SIGCHLD catch to psql

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> I thought it might be about that simple once you went at it the right >> way ;-). However, I'd suggest checking ferror(pset.queryFout) as well >> as the fflush result. > Sure, I can add the ferror() check. Patch attached. This s

Re: [HACKERS] tsvector pg_stats seems quite a bit off.

On 28/05/10 04:47, Tom Lane wrote: > I re-scanned that paper and realized that there is indeed something > wrong with the way we are doing it. The paper says (last sentence in > the definition of the algorithm, section 4.2): > > When a user requests a list of items with threshold s, we outp

Re: [HACKERS] How to pass around collation information

Hi, Peter Eisentraut writes: > On fre, 2010-05-28 at 20:22 +0300, Heikki Linnakangas wrote: >> USING syntax). The behavior is exactly what we want, it's >> just completely inpractical, so we need something to do the same in a >> less cumbersome way. For an example, here is something I did to

Re: [HACKERS] functional call named notation clashes with SQL feature

Heikki Linnakangas writes: > On 28/05/10 19:19, Josh Berkus wrote: >> EXEC dbo.GetItemPrice @ItemCode = 'GXKP', @PriceLevel = 5 > > Once you solve the problem of finding the '='s in the source, replacing them > is exactly the same effort regardless of what you replace them with. I guess it would

Re: [HACKERS] How to pass around collation information

On fre, 2010-05-28 at 14:48 -0400, Tom Lane wrote: > > SELECT * FROM test WHERE a COLLATE en > 'baz' ORDER BY b COLLATE sv; > > That seems fairly bizarre. What does this mean: > > WHERE a COLLATE en > b COLLATE de > > ? If it's an error, why is this not an error > > WHERE a COLLAT

Re: [HACKERS] [COMMITTERS] pgsql: PGDLLEXPORT is __declspec (dllexport) only on MSVC, but is

itag...@postgresql.org (Takahiro Itagaki) writes: > Log Message: > --- > PGDLLEXPORT is __declspec (dllexport) only on MSVC, > but is __declspec (dllimport) on other compilers > because cygwin and mingw don't like dllexport. That probably explains why the code was the way it was before ...

Re: [HACKERS] How to pass around collation information

On fre, 2010-05-28 at 15:03 -0400, Robert Haas wrote: > I think we need to think of the comparison operators as ternary, and > the COLLATE syntax applied to columns or present in queries as various > ways of setting defaults or explicit overrides for what the third > argument will end up being. Ho

Re: [HACKERS] Failback with log shipping

Heikki Linnakangas writes: > Not shipped before the first failover you mean? No, if any WAL records were > created in the old master that were not shipped to the standby before > failover, the corresponding changes to the data files might've been flushed > to disk already, and you can't undo those

Re: [HACKERS] functional call named notation clashes with SQL feature

On Fri, May 28, 2010 at 10:08 AM, Pavel Stehule wrote: > 2010/5/28 Tom Lane : >> Heikki Linnakangas writes: Peter Eisentraut  writes: > How about > select myfunc(a := 7, b := 6); >> >>> If we go with that, should we make some preparations to allow => in the >>> future? Like provide a

Re: [HACKERS] How to pass around collation information

On Fri, May 28, 2010 at 2:48 PM, Tom Lane wrote: > Peter Eisentraut writes: >> So while it's true that the collation is used by the operations (> and >> ORDER BY), the information which collation to use comes with the data >> values.  It's basically saying, a is in language "de", so sort it like

Re: [HACKERS] How to pass around collation information

Peter Eisentraut writes: > So while it's true that the collation is used by the operations (> and > ORDER BY), the information which collation to use comes with the data > values. It's basically saying, a is in language "de", so sort it like > that unless told otherwise. There is also an overrid

Re: [HACKERS] functional call named notation clashes with SQL feature

Andrew Dunstan writes: > Yeah. Whether or not we ever implement it really doesn't matter, IMO. We > should not be in the business of taking an SQL standard piece of syntax > and using it for some other purpose. Evidently the 201x SQL standard has blindsided us twice: first by defining a syntax

Re: [HACKERS] functional call named notation clashes with SQL feature

Josh Berkus writes: > Since former SQL Server / Sybase apps are the most likely to use named > parameter notation in PostgreSQL, having a syntax which could be ported > using only "sed" would be nice. I fear you're vastly overestimating the ability of sed to distinguish between = used in this w

Re: [HACKERS] VPATH docs

Patch applied. Thanks. --- David Fetter wrote: > Folks, > > Andrew Dunstan posted some instructions on his blog, and I'm thinking > they clarify things a great deal for people who want to learn how to > do VPATH builds. >

Re: [HACKERS] How to pass around collation information

On fre, 2010-05-28 at 20:22 +0300, Heikki Linnakangas wrote: > It's also fundamentally wrong, collation is not a property of a datum > but of the operation. > One way to approach this is to realize that it's already possible to > use > multiple collations in a database. You just have to define s

Re: [HACKERS] [BUGS] dividing money by money

I'm not quite sure how to go about changing it from an add-on function to a built-in one. So if you want to do that, go ahead. If you'd rather I did, just tell me how it's done. Andy Balholm (509) 276-2065 a...@balholm.com On May 26, 2010, at 11:18 AM, Kevin Grittner wrote: > Hi Andy, > > Do

Re: [HACKERS] functional call named notation clashes with SQL feature

On 28/05/10 19:19, Josh Berkus wrote: ( parameter := value ) notation is not only consistent with what is used inside pl/pgsql, it's also more consistent than "AS" with SQL Server's named parameter notation, which is: EXEC dbo.GetItemPrice @ItemCode = 'GXKP', @PriceLevel = 5 Since former SQL Se

Re: [HACKERS] How to pass around collation information

On 28/05/10 19:27, Peter Eisentraut wrote: I have been thinking about this collation support business a bit. Ignoring for the moment where we would get the actual collation routines from, I wonder how we are going to pass this information around in the system. Someone declares a collation on a c

Re: [HACKERS] How to pass around collation information

Excerpts from Peter Eisentraut's message of vie may 28 12:27:52 -0400 2010: > Option 2, invent some new mechanism that accompanies a datum or a type > whereever it goes. Kind of like typmod, but not really. Then the > collation information would presumably be made available to functions > throug

Re: [HACKERS] functional call named notation clashes with SQL feature

Bruce Momjian wrote: Josh Berkus wrote: Since former SQL Server / Sybase apps are the most likely to use named parameter notation in PostgreSQL, having a syntax which could be ported using only "sed" would be nice. Relevant to the whole discussion, though ... is the conflicting SQL stan

Re: [HACKERS] functional call named notation clashes with SQL feature

Josh Berkus wrote: > Since former SQL Server / Sybase apps are the most likely to use named > parameter notation in PostgreSQL, having a syntax which could be ported > using only "sed" would be nice. > > Relevant to the whole discussion, though ... is the conflicting SQL > standard syntax somet

[HACKERS] How to pass around collation information

I have been thinking about this collation support business a bit. Ignoring for the moment where we would get the actual collation routines from, I wonder how we are going to pass this information around in the system. Someone declares a collation on a column in a table, and somehow this informatio

Re: [HACKERS] functional call named notation clashes with SQL feature

What's poor about it? It probably comes from PLSQL which in turn got it from Ada, so they aren't just making this up. I agree it's inconvenient for us, but that's a different issue. Further, the ( parameter := value ) notation is not only consistent with what is used inside pl/pgsql, it's als

Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr

Peter Eisentraut wrote: > On fre, 2010-05-28 at 10:21 -0400, Bruce Momjian wrote: > > Tom Lane wrote: > > > Bruce Momjian writes: > > > > Tom Lane wrote: > > > >> ... indeed. Is it worth burdening the pg_stats mechanism with this? > > > >> The use case seems vanishingly thin. > > > > > > > I am

Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr

On fre, 2010-05-28 at 10:21 -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian writes: > > > Tom Lane wrote: > > >> ... indeed. Is it worth burdening the pg_stats mechanism with this? > > >> The use case seems vanishingly thin. > > > > > I am confused how this is different from inet

Re: [HACKERS] functional call named notation clashes with SQL feature

Tom Lane wrote: Heikki Linnakangas writes: Peter Eisentraut writes: How about select myfunc(a := 7, b := 6); If we go with that, should we make some preparations to allow => in the future? Like provide an alternative operator name for hstore's =>, and add a note so

Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr

Tom Lane wrote: > Bruce Momjian writes: > > Tom Lane wrote: > >> ... indeed. Is it worth burdening the pg_stats mechanism with this? > >> The use case seems vanishingly thin. > > > I am confused how this is different from inet_server_addr() and > > inet_server_port(). > > I think the point is

Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr

Bruce Momjian writes: > Tom Lane wrote: >> ... indeed. Is it worth burdening the pg_stats mechanism with this? >> The use case seems vanishingly thin. > I am confused how this is different from inet_server_addr() and > inet_server_port(). I think the point is to let someone find out *from ano

Re: [HACKERS] functional call named notation clashes with SQL feature

2010/5/28 Tom Lane : > Heikki Linnakangas writes: >>> Peter Eisentraut  writes: How about select myfunc(a := 7, b := 6); > >> If we go with that, should we make some preparations to allow => in the >> future? Like provide an alternative operator name for hstore's =>, and >> add a note so

Re: [HACKERS] Working with PostgreSQL enums in C code

Robert Haas writes: > On Fri, May 28, 2010 at 12:07 AM, Joseph Adams > wrote: >> I learned that to return an enum value from C, one needs to return the >> OID of the right row of the pg_enum table.  I eventually managed to >> write the code below, which is mostly based on the enum_in function in

Re: [HACKERS] [9.1] pg_stat_get_backend_server_addr

Tom Lane wrote: > Peter Eisentraut writes: > > There are functions pg_stat_get_backend_client_addr and > > pg_stat_get_backend_client_port, which are exposed through the > > pg_stat_activity view, but there is no straightforward way to get the > > server-side address and port of a connection. Thi

Re: [HACKERS] functional call named notation clashes with SQL feature

Heikki Linnakangas writes: >> Peter Eisentraut writes: >>> How about >>> select myfunc(a := 7, b := 6); > If we go with that, should we make some preparations to allow => in the > future? Like provide an alternative operator name for hstore's =>, and > add a note somewhere in the docs to disco

Re: [HACKERS] Failback with log shipping

On 28/05/10 16:11, Dimitri Fontaine wrote: Heikki Linnakangas writes: Assuming controlled shutdown and that the standby received all WAL from the old master before it was promoted, I think you can simply create a recovery.conf in the old master's data directory to turn it into a standby server,

Re: [HACKERS] Failback with log shipping

Heikki Linnakangas writes: > Assuming controlled shutdown and that the standby received all WAL from the > old master before it was promoted, I think you can simply create a > recovery.conf in the old master's data directory to turn it into a standby > server, and restart. Am I missing something?

Re: [HACKERS] Specification for Trusted PLs?

Sam Mason wrote: On Thu, May 27, 2010 at 11:09:26PM -0400, Tom Lane wrote: David Fetter writes: I don't know about a *good* idea, but here's the one I've got. 1. Make a whitelist. This is what needs to work in order for a language to be a fully functional trusted PL.

Re: [HACKERS] Specification for Trusted PLs?

On fre, 2010-05-28 at 13:03 +0100, Sam Mason wrote: > That's not normally a problem. The conventional way would be to place > the interpreter in its own sandbox, similar to how Chrome has each tab > running in its own process. These processes are protected in a way > so that the code running insi

Re: [HACKERS] Specification for Trusted PLs?

On Thu, May 27, 2010 at 11:09:26PM -0400, Tom Lane wrote: > David Fetter writes: > > I don't know about a *good* idea, but here's the one I've got. > > > 1. Make a whitelist. This is what needs to work in order for a > > language to be a fully functional trusted PL. > > Well, I pretty much los

[HACKERS] Re: [COMMITTERS] pgsql: Mark PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1 with PGDLLEXPORT

On 27/05/10 10:59, Takahiro Itagaki wrote: Log Message: --- Mark PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1 with PGDLLEXPORT independently from BUILDING_DLL. It is always __declspec(dllexport). It looks like the Windows buildfarm members are not happy about this change... -- Heikki Li

Re: [HACKERS] mingw initdb failure on HEAD

Takahiro Itagaki wrote: Andrew Dunstan wrote: Several buildfarm mingw members are getting failures like this, when running initdb: Could it have been caused by the PGDLLIMPORT/PGDLLEXPORT changes?

Re: [HACKERS] Working with PostgreSQL enums in C code

On Fri, May 28, 2010 at 12:07 AM, Joseph Adams wrote: > I learned that to return an enum value from C, one needs to return the > OID of the right row of the pg_enum table.  I eventually managed to > write the code below, which is mostly based on the enum_in function in > src/backend/utils/adt/enum

[HACKERS] Failback with log shipping

At PGCon, several people asked me about restarting an old master as a standby after failover has happened. And it wasn't the first time people ask me about it, even before 9.0. We have no mention of that in the docs, which is a pretty serious oversight. What can we say about it? I believe the

Re: [HACKERS] Patch submission deadline for CommitFest 2010-07

On Fri, May 28, 2010 at 4:08 PM, Heikki Linnakangas wrote: > On 28/05/10 09:26, Fujii Masao wrote: >> >> When is the patch submission deadline for CommitFest 2010-07? >> July 14? or June 14 (before review fest)? Sorry, I'm not sure >> what is actually different between CF and RF. > > July 14. But

Re: [HACKERS] tsvector pg_stats seems quite a bit off.

On 28/05/10 04:47, Tom Lane wrote: > =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= writes: >> On 19/05/10 21:01, Jesper Krogh wrote: >>> In practice, just cranking the statistics estimate up high enough seems >>> to solve the problem, but doesn't >>> there seem to be something wrong in how the statistics are c

Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages

On 27/05/10 22:56, Robert Haas wrote: On Thu, May 27, 2010 at 3:52 PM, Kevin Grittner wrote: Robert Haas wrote: On Thu, May 27, 2010 at 3:15 PM, Kevin Grittner (a) The tuples were written within the same transaction which created or truncated the table. In case (a), you mess up visibi

Re: [HACKERS] Patch submission deadline for CommitFest 2010-07

On 28/05/10 09:26, Fujii Masao wrote: When is the patch submission deadline for CommitFest 2010-07? July 14? or June 14 (before review fest)? Sorry, I'm not sure what is actually different between CF and RF. July 14. But if you finish the patch before June 14, it will get reviewed earlier, bet