Re: [HACKERS] Re: pgsql: In HS, Startup process sets SIGALRM when waiting for buffer pin.

[Heikki Linnakangas]

Simon Riggs wrote:
> On Sat, 2010-01-23 at 21:40 +, Greg Stark wrote:
>> On Sat, Jan 23, 2010 at 8:28 PM, Simon Riggs  wrote:
>>> What is your proposed way of handling buffer pin deadlocks? That will be
>>> acceptable and working to some extent in the next week?
>>>
>>> Wait forever isn't always a good idea, anymore, if it ever was.
>> I've never said it was always a good idea. But killing correctly
>> running queries isn't always a good idea either. I'm interested in
>> using HS for running read-only replicas for load balancing. It would
>> pretty sad if queries dispatched to a read-only replica received a
>> spurious unpredictable errors for reasons the application programmer
>> cannot control.
> 
> I understand your concern and seek to provide the best way forwards in
> the time available. Hopefully you have a better way, but we can do
> little about the time. Your input is welcome, and your code also.

I just woke up to this thread too. I have to agree with Greg, we must
think harder.

Can you summarize the problem again? I don't immediately see how the
deadlock could happen.

Would this simple scheme work:

When the startup process has waited for a short while (ie
deadlock_timeout), it sends the signal "please check if you're holding a
pin on buffer X" to all backends. When a backend receives that signal,
it checks if it is holding a pin on the given buffer *and* waiting on a
lock. If it is, abort the transaction. Assuming that a backend can only
block waiting on a lock held by the startup process, deadlock detection
is as simple as that.

> Given the stop gap does what -1 says it will never do, ISTM that having
> -1 would be contradictory. I did not wish to remove it, but it seemed
> safer to do so. Putting it back is straightforward, if it makes sense.

For all practical purposes, INT_MAX, which is the upper limit for
max_standby_delay, is the same as infinity. So removing -1 doesn't
really get you out of jail. And no, let's not make the upper limit
smaller, there's no natural upper limit for that setting.

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

[Pavel Stehule]

2010/1/25 Peter Eisentraut :
> On mån, 2010-01-25 at 08:09 +0100, Pavel Stehule wrote:
>> 2010/1/25 Peter Eisentraut :
>> > On sön, 2010-01-24 at 20:32 +, Simon Riggs wrote:
>> >> Why do we have a parameter called "default_do_language" when we don't
>> >> have a parameter called "default_language"?
>> >
>> > According to the SQL standard, the default language for CREATE FUNCTION
>> > is SQL.  Should we implement that?
>> >
>>
>> isn't it SQL/PSM ?
>
> No, but if you implement the SQL/PSM part, then those statements become
> part of the "SQL" language.
>

ok.

i think so default_language could be potential risk for compatibility
with standard.

Pavel


>

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

[Peter Eisentraut]

On mån, 2010-01-25 at 08:09 +0100, Pavel Stehule wrote:
> 2010/1/25 Peter Eisentraut :
> > On sön, 2010-01-24 at 20:32 +, Simon Riggs wrote:
> >> Why do we have a parameter called "default_do_language" when we don't
> >> have a parameter called "default_language"?
> >
> > According to the SQL standard, the default language for CREATE FUNCTION
> > is SQL.  Should we implement that?
> >
> 
> isn't it SQL/PSM ?

No, but if you implement the SQL/PSM part, then those statements become
part of the "SQL" language.


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

[Pavel Stehule]

2010/1/25 Peter Eisentraut :
> On sön, 2010-01-24 at 20:32 +, Simon Riggs wrote:
>> Why do we have a parameter called "default_do_language" when we don't
>> have a parameter called "default_language"?
>
> According to the SQL standard, the default language for CREATE FUNCTION
> is SQL.  Should we implement that?
>

isn't it SQL/PSM ?

Pavel

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

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

Re: [HACKERS] default_language

[Peter Eisentraut]

On sön, 2010-01-24 at 20:32 +, Simon Riggs wrote:
> Why do we have a parameter called "default_do_language" when we don't
> have a parameter called "default_language"?

According to the SQL standard, the default language for CREATE FUNCTION
is SQL.  Should we implement that?


-- 
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] Streaming Replication on win32

[Heikki Linnakangas]

Tom Lane wrote:
> Magnus Hagander  writes:
>> 2010/1/24 Joe Conway :
>>> Sorry for being thick -- I'm still missing something. I don't understand
>>> why any user program using libpq/PQexec running on Windows does not have
>>> the same issue. Or to put it another way, why does this only apply to
>>> libpq calls from backend modules?
> 
>> Because Windows programs in general don't rely on working Unix signal
>> semantics - since Win32 doesn't *have* them.
> 
> The real question is why is it so critical for our emulated signals to
> be able to interrupt these operations.

The process won't react to a shutdown request otherwise, while it's
waiting for the response to PQexec(). It's not such a big deal for
walreceiver, actually, because it already uses select() (with emulation)
in the main loop, but it's theoretically possible for the connection to
be silently lost during the initial handshake, after sending the Query
message, before receiving a response.

dblink/contrib has the same issue, it might wait for a response forever.

Hmm, maybe we should just TCP_KEEPALIVE (if available) on the
connection. We should really be doing that in walreceiver anyway,
walreceiver won't otherwise notice if the connection is silently lost,
and won't know to reconnect.

> If you're trying to tell me that Hot Standby is too fragile to work
> unless it can interrupt them, then HS has got a serious problem, and
> it is not libpq's fault.  There is an enormous amount of code in the
> backend that can run for long periods of time without noticing signals,
> and not all of that is fixable.  Consider for example a plperl,
> plpython, or pltcl function that goes off and does a long computation.

No, it's not related to Hot Standby.

-- 
  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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[KaiGai Kohei]

(2010/01/25 14:08), Robert Haas wrote:
> 2010/1/24 KaiGai Kohei:
>> It seems to me the result is different from Bernd's report.
> 
> Well, you tested something different, so you got a different answer.
> Your case doesn't have any multiple inheritance.

If it tries ALTER TABLE xxx RENAME TO on any multiple inheritance column,
this patch will raise an error when it founds the first column unable to
rename. (Of course, it takes inconsistency in table definitions, so we
need to prevent it.) It does not make sense in performance comparison.

The issue is whether we need to check pg_inherits for each recursion
level in renameatt(), or not. So, I checked the case when we try to
rename the root of inheritance tree.

Or, are you saying to test diamond-inheritance cases?

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

-- 
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] Review: listagg aggregate

[Scott Bailey]

I don't think. When we have function, with same parameters, same
behave like some Oracle function, then I am strongly prefer Oracle
name. I don't see any benefit from different name. It can only confuse
developers and add the trable to people who porting applications.


Meh. If the name is terrible, we don't have to use it, and it's easy enough to 
create an alias in SQL for those who need it.


The corresponding function in Oracle is called wm_concat. In MySQL its 
called group_concat. I don't believe DB2 or SQL Server have built in 
equivalents. The Oracle name isn't really an option ("wm' stands for 
workspace manager)


I think listagg or string_agg would be the most appropriate names. Oh 
and before Oracle had wm_concat, Tom Kyte wrote a function called stragg 
that was pretty popular.


Scott

--
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] Mammoth in Core?

[Takahiro Itagaki]

Tom Lane  wrote:

> It's going to be a really, really, *really* hard sell to get us to
> export any sort of external API to the parser internals.  At least
> if by "API" you mean something other than "we will whack this around
> to an indefinite degree on no notice, and don't even think about
> complaining".
> 
> What exactly is the goal that you think such a thing would serve,
> anyway?  The fragments on the referenced web page don't leave me with
> any warm feelings about how well the idea has been thought through.

Some of items in the referenced web page are just voted results form cluster
projects. At this time, we should read them as "what is needed", but not
"how to do it". They have been not reviewd yet and not well-considered
to be official TODO items.

I知 not sure what pgpool team think about, but I do NOT intend to export the
existing internal functions as-is. As for my personal goal, I think pgpool
should be re-implemented on the layers of SQL/MED FDW or planner/executor hooks.
I'd say the SQL/MED FDW apporach is "one by one into core (from projects)",
and the hook apporach is "external API (from core)".

Regards,
---
Takahiro Itagaki
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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Robert Haas]

2010/1/24 KaiGai Kohei :
> It seems to me the result is different from Bernd's report.

Well, you tested something different, so you got a different answer.
Your case doesn't have any multiple inheritance.

...Robert

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

Re: [HACKERS] Mammoth in Core?

[Tom Lane]

Takahiro Itagaki  writes:
> Tatsuo Ishii  wrote:
>> For example, see below from above URL: This means that we expect
>> PostgreSQL exports it's parser so that existing cluster softwares can
>> use it. Not opposite direction.

> I think they says the same practically -- at least have the same impact.

> It says postgres need to export the the internal feature *only for* some
> of external cluster softwares. So, if you are thinking about exporting
> some features from the core, the exported features would better to be
> stable enough and shared by several third-party tools.

[ raised eyebrow... ]

It's going to be a really, really, *really* hard sell to get us to
export any sort of external API to the parser internals.  At least
if by "API" you mean something other than "we will whack this around
to an indefinite degree on no notice, and don't even think about
complaining".

What exactly is the goal that you think such a thing would serve,
anyway?  The fragments on the referenced web page don't leave me with
any warm feelings about how well the idea has been thought through.

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] Mammoth in Core?

[Takahiro Itagaki]

Tatsuo Ishii  wrote:

> "splitting existing projects into some 'modules', and getting the
> modules one by one into core" was not the concluion, actually.
> 
> For example, see below from above URL: This means that we expect
> PostgreSQL exports it's parser so that existing cluster softwares can
> use it. Not opposite direction.

I think they says the same practically -- at least have the same impact.

It says postgres need to export the the internal feature *only for* some
of external cluster softwares. So, if you are thinking about exporting
some features from the core, the exported features would better to be
stable enough and shared by several third-party tools.

Regards,
---
Takahiro Itagaki
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] RADIUS authentication

[KaiGai Kohei]

(2010/01/24 23:29), Magnus Hagander wrote:
> 2010/1/20 KaiGai Kohei:
>> (2010/01/20 0:19), Magnus Hagander wrote:
 * I think this comment is right.
+   for (i = 0; ivector[i] = random() % 255;

The random seed is initialized at BackendRun() with MyProcPid and
the time of backend process launched.
Then, PostgresMain() ->InitPostgres() ->PerformAuthentication()
will be called, and this random() shall be the first call just after
initialization of the srandom().

Do you have any good idea?
Or, do you think it should be fixed with high priority?
>>>
>>> It does need a fairly good random number generator there to be secure,
>>> so it should probably be improved. OTOH, the whole thing can be more
>>> considered obfuscation rather than encryption, and those who really
>>> care about higher security will use ipsec or trusted networks.
>>>
>>> Maybe switching to erand48() would make this better, and good enough?
>>
>> As Tom pointed out, it is fundamentally same.
>> The matter is this random() invocation is the first time after
>> initialization of random seed by srandom(). It means an external observer
>> can estimate the random value uniquely using pid and startup time.
>>
>> In other representation, the "random" value is the result of function
>> which takes arguments of pid and startup time, without random factor.
>>
>>   for (i = 0; i>   packet->vector[i] = f(getpid(), port->SessionStartTime, i);
>>
>> One idea is to modify the logic to set up random seed in BackendRun().
>> In most of UNIX-like operating system, we can use /dev/random as a random
>> seed which is well randomized.
>>
>>   http://en.wikipedia.org/wiki//dev/random
>>
>> It seems to me PostmasterRandom() is a right place to set random seed,
>> and we can inject a block something like #ifdef HAVE_DEV_RANDOM ...
>>
>> Instead of such kind of efforts, we can also document that PostgreSQL and
>> RADIUS server should have communication using enough secure connection
>> explicitly. IMO, it will cover most of use cases.
> 
> There is one more option here - use OpenSSL if available. It has
> functions for secure random number generations
> (http://www.openssl.org/docs/crypto/RAND_bytes.html).  That seems easy
> enough when OpenSSL is available.

In just my opinion (so, committer may have different one), it is an
option to utilize openSSL library when available. However, it should
be moved to PostmasterRandom() and used to provide more randomness
for srandom(). And, srandom() in the head of BackendRun() should be
replaced by PostmasterRandom().

I also want any opinions from others.

> The question then becomes what do we do if we don't have OpenSSL
> available? Do we document that it's not secure, or refuse to run it?
> I'd vote for document it.. If you don't have SSL enabled, then you
> clearly don't use SSL for the libpq connection, which means the
> password goes in cleartext in that stream...

The seed of random is a different issue from safeness of password on
the stream between client and server. For example, if admin set up
IPsec/ESP between them, OpenSSL is not must-requirement.

Even if OpenSSL is not available, as long as both of PostgreSQL and
RADIUS server are set up in trusted network, we can consider it is
secure. So, all we can do is to introduce the risk, and the decisions
are depending on end-users.

 * It casts char array (such as radius_buffer) into radius_packet
structure. The radius_packet structure represents the format of
RADIUS network packet as is.
It may be preferable to give compiler a hint not to align this
structure.
In GCC, we can use "__attribute__((packed))" to suggest not to
align the member of structure. Is there any portable way for this?
>>>
>>> This I can't answer, I don't know this well enough. Somebody else?
>>
>> What manner is applied to handle network protocol in other part?
>>
>> The radius_packet is declared as follows:
>>
>> + typedef struct
>> + {
>> +   unsigned char   code;+0
>> +   unsigned char   id;  +1
>> +   unsigned short  length;  +2
>> +   unsigned char   vector[RADIUS_VECTOR_LENGTH];+4? +8?
>> + } radius_packet;
>>
>> It may be a bit nervous, except for possible alignment of the vector
>> on 64bit architecture.
>>
>> And, one more. It seems to me uint8 and uint16 are more preferable than
>> unsigned char/short in this context.
> 
> Yeha, that is probably right. I copied that off a reference
> implementation of the struct. Will change accordingly.
> 
> FWIW, I tested it on Win64 and it didn't have any issues there at least.

Just to be safe, could you inject an Assert() here?
If a minor compiler aligned it unintentionally, it wi

Re: [HACKERS] Mammoth in Core?

[Tatsuo Ishii]

> On 1/19/10 9:28 AM, Greg Smith wrote:
> > Takahiro Itagaki wrote:
> >> The conclusion is splitting existing projects into some 'modules',
> >> and getting the modules one by one into core. Voted features are here:
> >> http://wiki.postgresql.org/wiki/ClusterFeatures

"splitting existing projects into some 'modules', and getting the
modules one by one into core" was not the concluion, actually.

For example, see below from above URL: This means that we expect
PostgreSQL exports it's parser so that existing cluster softwares can
use it. Not opposite direction.

>  API into the Parser / Parser as an independent module
>
>* just first step, before going to the catalog
>* statement based replication need to reply certain constructs with 
> CONSTAnT values you provide
>* Figure out which you need to replace... quite difficult
>* but if we have the plan, could do better 
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> > This page was a bit messy for someone who didn't attend the meeting to
> > follow.  I just cleaned it up so that the features are listed in voting
> > order, and to have more inter-page links.  It's better, but could use
> > some more work still.
> 
> Yeah, we'll be cleaning it up and fleshing it out more from now until pgCon.
> 
> --Josh Berkus
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

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

Re: [HACKERS] commit fests

[Kevin Grittner]

> Greg Stark  wrote:
>> Tom Lane  wrote:
 
>> What I think we really need for beta, and could reasonably hope to
>> get, is a larger and better-organized beta testing effort. But we
>> are not going to get that if people are thinking about new
>> development and commit fests instead of testing what's already
>> there.
>
> Incidentally I'm not convinced that's true. The people we really
> want testing stuff are the people who have real-world test cases to
> throw at the new version and they're the people who will be most
> excited about a new release and the least interested in a
> commitfest for a version that they won't be able to run for another
> year.
 
I tend to agree with you there.  The primary risk seems to be that
such discussions could distract people who are already working on the
release process, not that there is a mob of people who could do much
to help with release who will be misdirected into new development.
 
> But I would be happy getting our current process working perfectly
> before trying experiments like that.
 
Seriously?  *Perfectly*?  I'm not even sure what objective metrics
you could effectively use to measure success in the process, much
less set targets for "perfection" which must be met before trying to
solve acknowledged existing problems.
 
Other posts have suggested that "review fests" might be helpful in
this period.  Again, it sounds to me, from other posts on this
thread, as though the primary risk is that people working on the
release could see something they couldn't resist getting drawn into
-- taking them off-task and delaying the release.  The obvious
solution to that would be to create a pgsql-journeyman-peer-review
list for review fests during the release window.  As long as we
we all realize that when the big dogs get around to their turn at
reviewing things after the release is out it might all have to be
redone from scratch, it seems like there could be significant benefit
at low risk.
 
-Kevin

-- 
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] Mammoth in Core?

[Josh Berkus]

On 1/19/10 9:28 AM, Greg Smith wrote:
> Takahiro Itagaki wrote:
>> The conclusion is splitting existing projects into some 'modules',
>> and getting the modules one by one into core. Voted features are here:
>> http://wiki.postgresql.org/wiki/ClusterFeatures
>>   
> This page was a bit messy for someone who didn't attend the meeting to
> follow.  I just cleaned it up so that the features are listed in voting
> order, and to have more inter-page links.  It's better, but could use
> some more work still.

Yeah, we'll be cleaning it up and fleshing it out more from now until pgCon.

--Josh Berkus

-- 
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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[KaiGai Kohei]

(2010/01/25 8:45), KaiGai Kohei wrote:
> (2010/01/25 4:01), Bernd Helmle wrote:
>>
>>
>> --On 24. Januar 2010 19:45:33 +0100 Bernd Helmle
>> wrote:
>>
>>> I don't see where this should be related to the number of tables not
>>> part of the inheritance tree (or inheritance at all).
>>
>> To answer that myself: it seems get_attname() introduces the overhead
>> here (forgot about that). Creating additional 16384 tables without any
>> connection to the inheritance increases the times on my Phenom-II Box to
>> round about 2 seconds:
>>
>>
>> Current -HEAD
>>
>> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
>> ALTER TABLE
>> Time: 409,045 ms
>>
>>
>> With KaiGai's recent patch:
>>
>> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
>> ALTER TABLE
>> Time: 2402,306 ms
> 
> Hmm
> 
> Bernd, could you try same test with previous patch?
>http://archives.postgresql.org/message-id/4b41bb04.2070...@ak.jp.nec.com
> 
> It computes an expected inhcount during find_all_inheritors(), and
> compares it with the target pg_attribute entry?
> 
> I'll also try to measure performance in three cases by myself.
> Please wait for a while...

I set up 11,111 of tables inherited from a common table.

(echo "CREATE TABLE t (a int);"
 for i in `seq 0 9`; do
 echo "CREATE TABLE s$i (b int) INHERITS(t);"
 for j in `seq 0 9`; do
 echo "CREATE TABLE v$i$j (c int) INHERITS(s$i);"
 for k in `seq 0 9`; do
 echo "CREATE TABLE w$i$j$k (d int) INHERITS(v$i$j);"
 for l in `seq 0 9`; do
 echo "CREATE TABLE x$i$j$k$l (e int) INHERITS(w$i$j$k);"
 done
 done
 done
 done) | psql test

And, I measured time to execute the following query using /usr/bin/time.

  ALTER TABLE t RENAME a TO aa;
  ALTER TABLE t RENAME aa TO aaa;
  ALTER TABLE t RENAME aaa TO ;
  ALTER TABLE t RENAME  TO a;
  ALTER TABLE t RENAME a TO a;

The platform is Pentium4 (3.20GHz) and generic SATA drive.

* CVS HEAD - does not care anything
  Avg: 25.840s (25.663s 24.214s 26.958s 26.525s)

* My rev.3 patch - find_all_inheritors_with_inhcount()
  Avg: 26.488s (25.197s 27.847s 25.487s 27.421s)

* My rev.4 patch - find_column_origin(), also fixes AT_AlterColumnType case
  Avg: 28.693s (27.936s 30.295s 29.385s 27.159s)

It seems to me the result is different from Bernd's report.

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

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

[HACKERS] Syntax supplements for SET options

[Takahiro Itagaki]

I found a couple of incomplete syntax when I was adjusting psql
automatic tab completion for HEAD.

1. We cannot specify tablespace options on "CREATE TABLESPACE"
   though "ALTER TABLESPACE" supports generic options by SET (...).

2. Should we still support "ALTER COLUMN SET STATISTICS"
   though we can set generic options with "ALTER COLUMN SET (...)" ?

3. We cannot specify column options on "CREATE TABLE"
   though "ALTER TABLE ALTER COLUMN SET ..." supports it.

Which item should we fix?

I think the 1st is simple to be fixed if needed.
Since the 2nd was added during alpha, we could revert it if needed.
The 3rd is relatively hard to fix because we need to adjust the syntax all
of the kinds of columns options - STATISTICS, STORAGE, and generic options -
without keywords confliction.

Regards,
---
Takahiro Itagaki
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] default_language

[Simon Riggs]

On Sun, 2010-01-24 at 23:59 +, Greg Stark wrote:
> On Sun, Jan 24, 2010 at 11:18 PM, Simon Riggs  wrote:
> > I would prefer having the option, but removing it completely does at
> > least solve the bizarre inconsistency I've highlighted.
> >
> 
> I don't see it as much of an inconsistency. The whole point of DO is
> to be convenient, whereas CREATE FUNCTION is DDL for defining what
> your database looks like and it should be well defined in perpetuity.

So you want your database to work declaratively, but the code that runs
against it doesn't need to be. Why would that be? What advantage is
gained from having DO blocks potentially fail sometime in the future?
Whatever that advantage is, why should functions not also be able to
take advantage of that same benefit?

> However it's also possible will write DO blocks into their application
> code in which case it might be preferable not to have a
> default_language GUC which would have to be set correctly for the code
> to work.

-- 
 Simon Riggs   www.2ndQuadrant.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] commit fests

[Kevin Grittner]

Dimitri Fontaine  wrote:
 
> And there's already pgfouine to get the scenarios from the logs and
> tsung to act as a recording proxy:
> http://pgfouine.projects.postgresql.org/tsung.html
> http://tsung.erlang-projects.org/user_manual.html#htoc34
>
> So maybe it would be a good idea to have dtester able to replay a
> tsung session file so that we already know how to get this input?
>
> Then dtester would be for validating queries output and tsung for
> testing the scalability, both working with the same format.
 
I will take a close look at that and see how the two might work
together or in complementary roles.  Thanks for reminding me about
this tool.
 
-Kevin

-- 
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-ism help patch for psql

[Baron Schwartz]

Hi Cédric,

On Sun, Jan 24, 2010 at 5:11 PM, Cédric Villemain
 wrote:
> 'psql --help mysql' (or 'psql --tips mysql' ) might be good to call a
> special helper : I don't see the point to introduce that kind of
> things when it is useless for most of our users.

I think it's good to go beyond what's useful for most users.  It's
good to help potential users, too.

- Baron

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

[Andrew Dunstan]

Greg Stark wrote:

On Sun, Jan 24, 2010 at 11:18 PM, Simon Riggs  wrote:
  

I would prefer having the option, but removing it completely does at
least solve the bizarre inconsistency I've highlighted.




I don't see it as much of an inconsistency. The whole point of DO is
to be convenient, whereas CREATE FUNCTION is DDL for defining what
your database looks like and it should be well defined in perpetuity.

However it's also possible will write DO blocks into their application
code in which case it might be preferable not to have a
default_language GUC which would have to be set correctly for the code
to work.
  


Yeah, it's the possibility of scripts or functions with embedded DO 
statements that makes me think we'd be better off without a setting for 
this.


cheers

andrew

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

Re: [HACKERS] default_language

[Greg Stark]

On Sun, Jan 24, 2010 at 11:18 PM, Simon Riggs  wrote:
> I would prefer having the option, but removing it completely does at
> least solve the bizarre inconsistency I've highlighted.
>

I don't see it as much of an inconsistency. The whole point of DO is
to be convenient, whereas CREATE FUNCTION is DDL for defining what
your database looks like and it should be well defined in perpetuity.

However it's also possible will write DO blocks into their application
code in which case it might be preferable not to have a
default_language GUC which would have to be set correctly for the code
to work.

-- 
greg

-- 
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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[KaiGai Kohei]

(2010/01/25 4:01), Bernd Helmle wrote:
> 
> 
> --On 24. Januar 2010 19:45:33 +0100 Bernd Helmle  
> wrote:
> 
>> I don't see where this should be related to the number of tables not
>> part of the inheritance tree (or inheritance at all).
> 
> To answer that myself: it seems get_attname() introduces the overhead 
> here (forgot about that). Creating additional 16384 tables without any 
> connection to the inheritance increases the times on my Phenom-II Box to 
> round about 2 seconds:
> 
> 
> Current -HEAD
> 
> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
> ALTER TABLE
> Time: 409,045 ms
> 
> 
> With KaiGai's recent patch:
> 
> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
> ALTER TABLE
> Time: 2402,306 ms

Hmm

Bernd, could you try same test with previous patch?
  http://archives.postgresql.org/message-id/4b41bb04.2070...@ak.jp.nec.com

It computes an expected inhcount during find_all_inheritors(), and
compares it with the target pg_attribute entry?

I'll also try to measure performance in three cases by myself.
Please wait for a while...

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

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

[Simon Riggs]

On Sun, 2010-01-24 at 17:04 -0500, Tom Lane wrote:

> > If we have a default (for DO and CREATE FUNCTION), why not hard-wire the
> > default to plpgsql?
> 
> I don't see any strong argument for having a default for CREATE
> FUNCTION.  The original argument for having a GUC for DO was that
> plpgsql wasn't built in; now that it is, I think a case could
> be made for dropping default_do_language in favor of a hardwired
> default.

I would prefer having the option, but removing it completely does at
least solve the bizarre inconsistency I've highlighted. 

-- 
 Simon Riggs   www.2ndQuadrant.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] Streaming Replication on win32

[Tom Lane]

Magnus Hagander  writes:
> 2010/1/24 Joe Conway :
>> Sorry for being thick -- I'm still missing something. I don't understand
>> why any user program using libpq/PQexec running on Windows does not have
>> the same issue. Or to put it another way, why does this only apply to
>> libpq calls from backend modules?

> Because Windows programs in general don't rely on working Unix signal
> semantics - since Win32 doesn't *have* them.

The real question is why is it so critical for our emulated signals to
be able to interrupt these operations.

If you're trying to tell me that Hot Standby is too fragile to work
unless it can interrupt them, then HS has got a serious problem, and
it is not libpq's fault.  There is an enormous amount of code in the
backend that can run for long periods of time without noticing signals,
and not all of that is fixable.  Consider for example a plperl,
plpython, or pltcl function that goes off and does a long computation.

So I'm thinking that proposing to kluge up libpq in this area isn't
solving the real problem anyway.

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] Streaming Replication on win32

[Magnus Hagander]

2010/1/24 Joe Conway :
> On 01/21/2010 11:19 PM, Heikki Linnakangas wrote:
>> Joe Conway wrote:
>>> OK, so now I see why we want this fixed for dblink and walreceiver, but
>>> doesn't this approach leave every other WIN32 libpq client out in the
>>> cold? Is there nothing that can be done for the general case, or is it a
>>> SMOP?
>>
>> The problem only applies to libpq calls from the backend. Client apps
>> are not affected, only backend modules. If there's any other modules out
>> there that use libpq, then yes, those have a problem too.
>
> Sorry for being thick -- I'm still missing something. I don't understand
> why any user program using libpq/PQexec running on Windows does not have
> the same issue. Or to put it another way, why does this only apply to
> libpq calls from backend modules?

Because Windows programs in general don't rely on working Unix signal
semantics - since Win32 doesn't *have* them. We faked them for
PostgreSQL so we didn't have to rewrite large parts of how the backend
deals with those things. I don't know any other software that does -
and especially not client side software.

So yeah, you could say they are affected insofar that their calls will
be blocked as well, but if they are Windows apps they are probably
designed to deal with it. It's the common way for such calls to behave
on the platform.


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

Re: [HACKERS] Streaming Replication on win32

[Joe Conway]

On 01/21/2010 11:19 PM, Heikki Linnakangas wrote:
> Joe Conway wrote:
>> OK, so now I see why we want this fixed for dblink and walreceiver, but
>> doesn't this approach leave every other WIN32 libpq client out in the
>> cold? Is there nothing that can be done for the general case, or is it a
>> SMOP?
> 
> The problem only applies to libpq calls from the backend. Client apps
> are not affected, only backend modules. If there's any other modules out
> there that use libpq, then yes, those have a problem too.

Sorry for being thick -- I'm still missing something. I don't understand
why any user program using libpq/PQexec running on Windows does not have
the same issue. Or to put it another way, why does this only apply to
libpq calls from backend modules?

> A generic fix would be nice. Putting the wrapper functions in a new
> header file that's included in all backend modules that want to use
> libpq would work. Maybe the new header file could even be #included from
> libpq-fe.h, when compiling backend code (#ifndef FRONTEND), so you
> wouldn't need to modify dblink, walreceiver, or any 3rd party modules
> that use libpq at all.

I'll go ahead and do this if there is consensus for it.

Joe



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Andrew Dunstan]

I wrote:


I don't actually have a horse in this race, I can live with either name.



In the interests of full disclosure, I should point out that I in fact 
do have a horse in the race, although I wasn't thinking of it when I 
wrote the above. As an officer in a corporation with "PostgreSQL" in its 
name I'd be more than annoyed if the project name were changed under us.


cheers

andrew

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

Re: [HACKERS] MySQL-ism help patch for psql

[Cédric Villemain]

2010/1/24 Baron Schwartz :
> David Fetter just pointed this thread out to me.  I think anything
> that makes PostgreSQL more accessible could be a good thing.  In some
> sense it's harder to learn a technology when you are very familiar
> with another similar one already.  Is it easier to learn to type on
> Dvorak, or to learn QWERTY and then switch to Dvorak?  Switching was
> harder for me than learning initially.
>
> So I guess my advice, since David asked me :-) is not to underestimate
> the pain of switching.  I don't know whether this patch is the Right
> Answer, but I think the sentiment is something to be encouraged.  If
> it's not the right answer, then maybe some brainstorming and user
> input will reveal creative alternatives.  I'll start:

'psql --help mysql' (or 'psql --tips mysql' ) might be good to call a
special helper : I don't see the point to introduce that kind of
things when it is useless for most of our users. (so 'psql'  won't
output any tips relative to mysql)

It offer then to have a 'psql --help oracle' or anyother special tips
we want to provide for our new users and following their knowledge.

And it will be able to provide usefull tips à la git for postgresql
regular users. (selcet --> did you mean select instead of selcet ?)


Regards,
Cédric

>
> == begin ==
>
> Welcome to the POSTGRESQL interactive sql monitor:
>  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
>
>   type \? for help on slash commands
>   type \mysql for a quick MySQL-to-PostgreSQL cheatsheet
>
> somedb=> \mysql
>
> psql uses backslash keywords instead of SHOW commands.  There is
> also a standard INFORMATION_SCHEMA if you're familiar with that.
> The following commands might be helpful as you learn how to use psql:
>
>  Command in mysql  Command in psql
>    ===
>  SHOW TABLES       \dt
>  DESCRIBE          \d
>  ... and so on.
>
> == end ==
>
> The full list of SHOW commands, should anyone be interested, is at
> http://dev.mysql.com/doc/en/show.html
>
> Cheers,
> Baron
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>



-- 
Cédric Villemain

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

[David E. Wheeler]

On Jan 24, 2010, at 2:04 PM, Tom Lane wrote:

> I don't see any strong argument for having a default for CREATE
> FUNCTION.  The original argument for having a GUC for DO was that
> plpgsql wasn't built in; now that it is, I think a case could
> be made for dropping default_do_language in favor of a hardwired
> default.

+1

David

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

Re: [HACKERS] default_language

[Tom Lane]

Jeff Davis  writes:
> I would actually lean the other way and say that we shouldn't be
> introducing behavior-changing GUCs (except for the special case of
> supporting legacy behavior, like standard_conforming_strings).

Yeah --- GUCs that affect semantics (as opposed to performance) of SQL
constructs have many downsides, as we have found out over the years.
I'm not seeing that this idea has enough usefulness to justify the
risks.

> If we have a default (for DO and CREATE FUNCTION), why not hard-wire the
> default to plpgsql?

I don't see any strong argument for having a default for CREATE
FUNCTION.  The original argument for having a GUC for DO was that
plpgsql wasn't built in; now that it is, I think a case could
be made for dropping default_do_language in favor of a hardwired
default.

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] further explain changes

[Tom Lane]

Robert Haas  writes:
> Care to suggest a format?

As much like the sort case as possible ...

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

[Dimitri Fontaine]

Simon Riggs  writes:
> Please can we have "default_language"? 

That sounds like something I could want to use someday.

> Or "language_path" so we can tell
> CREATE FUNCTION to try the languages in order? Or better still, try all
> the installed languages that the user has rights to in alphabetic
> order? 

That sounds like something I will regret we have anyday. Like you make a
typo on your plpgsql function so PostgreSQL will try to see how it fits
as a plperl one ? No thanks.

I guess I'm misunderstanding the behavior you have in mind.

Regards,
-- 
dim

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

[Jeff Davis]

On Sun, 2010-01-24 at 20:32 +, Simon Riggs wrote:
> Or "language_path" so we can tell
> CREATE FUNCTION to try the languages in order? Or better still, try all
> the installed languages that the user has rights to in alphabetic
> order? 

What do you mean "try"? It seems a little dangerous to just try to
compile until it doesn't throw an error. 

Consider the following program:

  len('aa')

Is that ruby or python? It's probably python, because python has a
built-in function "len". However, ruby doesn't know that the "len"
function doesn't exist until runtime -- so perhaps it's just a ruby
program that happens to throw a runtime error?

I would actually lean the other way and say that we shouldn't be
introducing behavior-changing GUCs (except for the special case of
supporting legacy behavior, like standard_conforming_strings). If we
have a default (for DO and CREATE FUNCTION), why not hard-wire the
default to plpgsql?

Regards,
Jeff Davis


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

Re: [HACKERS] further explain changes

[Robert Haas]

On Sun, Jan 24, 2010 at 12:30 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Sun, Jan 24, 2010 at 12:06 AM, Jaime Casanova
>>> why not let it go in ANALYZE, just as the sort info
>
>> It's kinda long-winded - it adds like 4 extra lines for each hash
>> join.  I don't think I want to add that much clutter to regular E-A
>> output.
>
> Well, that would only happen if you're deliberately obtuse about the
> formatting.  The sort code manages to fit all the extra on one line,
> and I don't see why hash couldn't.
>
> I'd vote for just adding it in the exact same cases that sort adds extra
> info.  -1 for either adding a new option or changing the meaning of the
> ones that are there.

Care to suggest a format?

...Robert

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

[HACKERS] default_language

[Simon Riggs]

Why do we have a parameter called "default_do_language" when we don't
have a parameter called "default_language"?

This is remarkably inconsistent. Why the difference? 5 years from now,
whatever reason we had will seem just strange.

Please can we have "default_language"? Or "language_path" so we can tell
CREATE FUNCTION to try the languages in order? Or better still, try all
the installed languages that the user has rights to in alphabetic
order? 

(The latter behaviour would be backpatchable to allow code to be more
portable across releases, but "backpatch" may be a Treadstone trigger
word).

-- 
 Simon Riggs   www.2ndQuadrant.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] tab completion for prepared transactions?

[Heikki Linnakangas]

Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>> Bruce Momjian wrote:
>>> Heikki Linnakangas wrote:
 Right, I vaguely recall that the idea of tab-completion for those
 commands was rejected when 2PC was added because of that. A user sitting
 at a psql terminal is not supposed to prepare a transaction. That's
 application server's business.
>>> I think we should add a C comment documenting that fact.
>> Hmm, I thought we explained that in the manual, but I can't see it. I
>> think we should add a note to the ref page of PREPARE TRANSACTION,
>> something like:
>>
>> "PREPARE TRANSACTION is not intended for use in applications or in
>> interactive sessions. It's purpose is to allow an external transaction
>> manager to perform atomic global transactions across multiple databases
>> or other transactional resources. Unless you're writing a transaction
>> manager, you probably shouldn't be using PREPARE TRANSACTION"
> 
> I was thinking of adding something in the psql C source code to explain
> why certain commands are not implemented for tab completion.

Yeah, that's a good idea too.

-- 
  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] MySQL-ism help patch for psql

[Baron Schwartz]

David Fetter just pointed this thread out to me.  I think anything
that makes PostgreSQL more accessible could be a good thing.  In some
sense it's harder to learn a technology when you are very familiar
with another similar one already.  Is it easier to learn to type on
Dvorak, or to learn QWERTY and then switch to Dvorak?  Switching was
harder for me than learning initially.

So I guess my advice, since David asked me :-) is not to underestimate
the pain of switching.  I don't know whether this patch is the Right
Answer, but I think the sentiment is something to be encouraged.  If
it's not the right answer, then maybe some brainstorming and user
input will reveal creative alternatives.  I'll start:

== begin ==

Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \mysql for a quick MySQL-to-PostgreSQL cheatsheet

somedb=> \mysql

psql uses backslash keywords instead of SHOW commands.  There is
also a standard INFORMATION_SCHEMA if you're familiar with that.
The following commands might be helpful as you learn how to use psql:

  Command in mysql  Command in psql
    ===
  SHOW TABLES   \dt
  DESCRIBE  \d
  ... and so on.

== end ==

The full list of SHOW commands, should anyone be interested, is at
http://dev.mysql.com/doc/en/show.html

Cheers,
Baron

-- 
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] tab completion for prepared transactions?

[Heikki Linnakangas]

Peter Eisentraut wrote:
> What is the
> difference between a transaction manager and an application that opens
> multiple connections and does PREPARE + COMMIT PREPARED across them?

It's what happens if your application crashes after issuing the PREPARE.
A correctly configured transaction manager will know to reconnect to all
the resource managers (ie. databases), check its own transaction log,
decide which global transaction have committed, and issue the
COMMIT/ROLLBACK PREPARED commands to the resource managers to finish the
global transactions.

Sure, you can do all that in an application too, but I have yet to see
one that does (correctly). At the point that you do, you can say that
your application has a built-in transaction manager. Really, if you know
what you're doing, you'll know that note in the manual doesn't apply to you.

-- 
  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] commit fests

[Dimitri Fontaine]

"Kevin Grittner"  writes:

> Andrew Dunstan  wrote:
>  
>> As for getting more coverage of Beta, part of my trouble in
>> testing applications on Beta is that there aren't good tools I
>> know of for capture and replay of usage scenarios. Now there would
>> be a good project for someone, and it would be unaffected by the
>> Beta cycle.
>  
> Did you follow any of the dtester discussion?  That would allow
> replay of complex usage scenarios, although they would be scripted,
> rather than captured.  Would that help any?

And there's already pgfouine to get the scenarios from the logs and
tsung to act as a recording proxy:
  http://pgfouine.projects.postgresql.org/tsung.html
  http://tsung.erlang-projects.org/user_manual.html#htoc34

So maybe it would be a good idea to have dtester able to replay a tsung
session file so that we already know how to get this input?

Then dtester would be for validating queries output and tsung for
testing the scalability, both working with the same format.

Regards,
-- 
dim

-- 
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] tab completion for prepared transactions?

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> Hmm, that's a good point though. Maybe it would make sense to add tab
>> completion for ROLLBACK/COMMIT PREPARED, but not for PREPARE TRANSACTION?
> 
> Huh?  PREPARE TRANSACTION creates a new gxid, so there's nothing for it
> to complete against.

Well, you could still complete "PREPARE TR" to "PREPARE TRANSACTION",
but I agree it's not very useful. But I was arguing for not having
tab-completion for PREPARE TRANSACTION, so the point is moot anyway.

-- 
  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] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Andrew Dunstan]

Robert Treat wrote:

On Saturday 23 January 2010 16:19:11 Andrew Dunstan wrote:
  

Robert Treat wrote:


I'm not saying there aren't
downsides, but having a name the community can unify on is a definite
plus, and imho that name has to be Postgres.
  

Translation: "we'll only be unified if everyone agrees with me."




Wow Andrew, that's kind of a dick thing to say.  This really isn't about 
agreeing with me except maybe that I've watched the issue for years and I 
think I have come to the most reasonable conclusion. If there is a more 
reasonable conclusion, I'm happy to switch to that, but of course we'd be back 
to people agreeing with me...


  



I'm sorry if I offended you, it seems to be my week for that. But that's 
how what you said came across to me.


I don't actually have a horse in this race, I can live with either name. 
But there was a discussion on it not long ago (in which I did not take 
part) and a decision was made. I think bringing it up again now is 
unfortunate, and a serious distraction. And clearly there are reasonable 
counter-arguments to your position, as evidenced by this most recent 
discussion.


I honestly do not believe that the future of the project depends on the 
outcome of this issue to any significant extent.


cheers

andrew

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

Re: [HACKERS] tab completion for prepared transactions?

[Peter Eisentraut]

On sön, 2010-01-24 at 19:43 +0200, Heikki Linnakangas wrote:
> "PREPARE TRANSACTION is not intended for use in applications or in
> interactive sessions. It's purpose is to allow an external transaction
> manager to perform atomic global transactions across multiple
> databases
> or other transactional resources. Unless you're writing a transaction
> manager, you probably shouldn't be using PREPARE TRANSACTION" 

I have never had a chance to use one of these "transaction managers".
I'm probably using it in a non-orthodox way then.  What is the
difference between a transaction manager and an application that opens
multiple connections and does PREPARE + COMMIT PREPARED across them?


-- 
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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Robert Haas]

On Sun, Jan 24, 2010 at 2:01 PM, Bernd Helmle  wrote:
> --On 24. Januar 2010 19:45:33 +0100 Bernd Helmle 
> wrote:
>>  I don't see where this should be related to the number of tables not
>> part of the inheritance tree (or inheritance at all).
> To answer that myself: it seems get_attname() introduces the overhead here
> (forgot about that). Creating additional 16384 tables without any connection
> to the inheritance increases the times on my Phenom-II Box to round about 2
> seconds:
>
> Current -HEAD
>
> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
> ALTER TABLE
> Time: 409,045 ms
>
> With KaiGai's recent patch:
>
> bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
> ALTER TABLE
> Time: 2402,306 ms

Hmm, so adding those tables slowed down HEAD by <50%, but the time
with KaiGai's patch more than trippled.  So I think we definitely need
KaiGai to try it the other way and see how that shakes out...

...Robert

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

Re: [HACKERS] Resetting a single statistics counter

[Magnus Hagander]

2010/1/24 Euler Taveira de Oliveira :
> Tom Lane escreveu:
>> That implies that the operations wouldn't work against system tables;
>> which they do.  I think a bigger problem is that "reset_single_table"
>> seems like it might be talking about something like a TRUNCATE, ie,
>> it's not clear that it means to reset counters rather than data.
>> The pg_stat_ prefix is some help but not enough IMO.  So I suggest
>> pg_stat_reset_table_counters and pg_stat_reset_function_counters.
>>
> Sure, much better. +1.
>
>> (BTW, a similar complaint could be made about the previously committed
>> patch: reset shared what?)
>>
> BTW, what about that idea to overload pg_stat_reset()? The
> pg_stat_reset_shared should be renamed to pg_stat_reset('foo') [1] where foo
> is the class of objects that it is resetting. pg_stat_reset is not a so
> suggestive name but that's one we already have; besides, it will be intuitive
> for users.

I think it's easier to use the way it is now. But yes, we could
overload it to make it:
pg_stat_reset()   : everything, like now
pg_stat_reset('bgwriter')   : what pg_stat_reset_shared() does
now. Can take more params.
pg_stat_reset('table', 'foo'::regclass); : what
pg_stat_reset_single_table_counters does now

The advantage would be fewer functions, but I still think it's easier
to use the way we have it now.


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

Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL

[Robert Treat]

On Saturday 23 January 2010 16:19:11 Andrew Dunstan wrote:
> Robert Treat wrote:
> > I'm not saying there aren't
> > downsides, but having a name the community can unify on is a definite
> > plus, and imho that name has to be Postgres.
>
> Translation: "we'll only be unified if everyone agrees with me."
>

Wow Andrew, that's kind of a dick thing to say.  This really isn't about 
agreeing with me except maybe that I've watched the issue for years and I 
think I have come to the most reasonable conclusion. If there is a more 
reasonable conclusion, I'm happy to switch to that, but of course we'd be back 
to people agreeing with me...

> Sorry, that is quite clearly not going to happen.
>

People said that about win32 and people said that about git; the former has 
happened, the latter hasn't, but I suspect it will. Given the problems with 
the name PostgreSQL aren't just going to magically disappear, eventually I 
believe a name change will be made (though I've no doubt people will try to 
dig themselves in deeper in opposition to it in the mean time). 

> Can we please get on with actually making a better product? Raising this
> issue again is simply an unnecessary distraction.
>

A strong and growing community is arguably the most important feature of any 
software project; to that extent this *is* the work of making a better 
product. 

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Bernd Helmle]

--On 24. Januar 2010 19:45:33 +0100 Bernd Helmle  
wrote:



 I don't see where this should be related to the number of tables not
part of the inheritance tree (or inheritance at all).


To answer that myself: it seems get_attname() introduces the overhead here 
(forgot about that). Creating additional 16384 tables without any 
connection to the inheritance increases the times on my Phenom-II Box to 
round about 2 seconds:



Current -HEAD

bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
ALTER TABLE
Time: 409,045 ms


With KaiGai's recent patch:

bernd=# ALTER TABLE a1 RENAME COLUMN acol1 TO xyz;
ALTER TABLE
Time: 2402,306 ms


--
Thanks

Bernd

--
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] Resetting a single statistics counter

[Euler Taveira de Oliveira]

Tom Lane escreveu:
> That implies that the operations wouldn't work against system tables;
> which they do.  I think a bigger problem is that "reset_single_table"
> seems like it might be talking about something like a TRUNCATE, ie,
> it's not clear that it means to reset counters rather than data.
> The pg_stat_ prefix is some help but not enough IMO.  So I suggest
> pg_stat_reset_table_counters and pg_stat_reset_function_counters.
> 
Sure, much better. +1.

> (BTW, a similar complaint could be made about the previously committed
> patch: reset shared what?)
> 
BTW, what about that idea to overload pg_stat_reset()? The
pg_stat_reset_shared should be renamed to pg_stat_reset('foo') [1] where foo
is the class of objects that it is resetting. pg_stat_reset is not a so
suggestive name but that's one we already have; besides, it will be intuitive
for users.


[1] http://archives.postgresql.org/pgsql-hackers/2010-01/msg01317.php


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Bernd Helmle]

--On 24. Januar 2010 13:23:14 -0500 Tom Lane  wrote:


I think my concern about the original proposal was that the time to
perform an ALTER RENAME would increase with the number of tables in the
database, even if they were entirely unrelated to the one you're trying
to rename.


Uhm, find_column_origin() scans pg_inherits recursively by looking up 
inhparent from the given inhrelid. I don't see where this should be related 
to the number of tables not part of the inheritance tree (or inheritance at 
all).



--
Thanks

Bernd

--
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] Review: listagg aggregate

[Pavel Stehule]

2010/1/24 David E. Wheeler :
> On Jan 24, 2010, at 1:19 AM, Pavel Stehule wrote:
>
>> can I help with it, please. My English is terrible.
>
> Yes, I added a bit in the patch I submitted.
>
>> array user functions are used more time in pg core. The complexity of
>> array functions are much higher, so I don't think we need special
>> file.
>
> Okay. Should have tried it in PL/pgSQL then, perhaps.
>

:) - I'll look on it again.

>> I'll recheck it. I am sure so all parameters should be a text.
>
> Probably shouldn't go into varchar.c then, yes?
>
>> We can, but it isn't good way. Processing of arrays is little bit more
>> expensive then processing plain text. It is reason why listagg is
>> faster, than your custom aggregate. More, the final function could be
>> faster - the content is final.
>
> Understood.
>
>> It normal for aggregate functions. We need more transfn function,
>> because we need two two variant: listagg(col), listagg(col, sep). Our
>> coding guidlines doesn't advice share C functions - but these
>> functions are +/- wrapper for accumStringResult - so there is zero
>> overhead.
>
> Ah, okay, it's because of the second argument. Now I understand.
>
>> I don't think. When we have function, with same parameters, same
>> behave like some Oracle function, then I am strongly prefer Oracle
>> name. I don't see any benefit from different name. It can only confuse
>> developers and add the trable to people who porting applications.
>
> Meh. If the name is terrible, we don't have to use it, and it's easy enough 
> to create an alias in SQL for those who need it.
>

The "list" is common name for this content - it is usual in Microsoft
SQL Server, it is usual in Oracle. Maybe we can vote about the name

Regards
Pavel Stehule

> Best,
>
> David
>
>

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

Re: [HACKERS] Review: listagg aggregate

[Tom Lane]

Simon Riggs  writes:
> On Fri, 2010-01-22 at 11:14 -0800, David E.Wheeler wrote:
>> No performance issues

> ISTM that this class of function is inherently dangerous performance
> wise. 

> * It looks incredibly easy to construct enormous lists. We should test
> the explosion limit of this to see how it is handled. Perhaps we need
> some parameter limits to control that, depending upon results.

> * Optimizer doesn't consider whether the result type of an aggregate get
> bigger as the aggregate processes more rows. If we're adding this
> function we should give some thought in that area also, or at least a
> comment to note that it can and will cause the optimizer problems in
> complex queries.

We have that problem already with array_agg(), and I don't recall many
complaints about it.  It might be worth worrying about at some point,
but I don't think it's reasonable to insist that it be fixed before
any more such aggregates are created.

I agree that testing-to-failure would be a good idea just to be sure it
fails cleanly.

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] Resetting a single statistics counter

[Magnus Hagander]

2010/1/24 Tom Lane :
> Magnus Hagander  writes:
>> 2010/1/24 Tom Lane :
>>> The pg_stat_ prefix is some help but not enough IMO.  So I suggest
>>> pg_stat_reset_table_counters and pg_stat_reset_function_counters.
>
>> Doesn't the pg_stat_ part already say this?
>
> My objection is that "reset_table" sounds like something you do to a
> table, not something you do to stats.  No, I don't think the prefix is
> enough to clarify that.

Fair enough, I'll just add the _counters to all three functions then.


>>> (BTW, a similar complaint could be made about the previously committed
>>> patch: reset shared what?)
>
>> Well, it could also be made about the original pg_stat_reset()
>> function - reset what?
>
> In that case, there's nothing but the "stat" to suggest what gets
> reset, so I think it's less likely to be misleading than the current
> proposals.  But if we'd been designing all of these at once, yeah,
> I'd have argued for a more verbose name for that one too.

Ok.

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

Re: [HACKERS] Review: listagg aggregate

[Pavel Stehule]

2010/1/24 Simon Riggs :
> On Fri, 2010-01-22 at 11:14 -0800, David E.Wheeler wrote:
>> No performance issues
>
> ISTM that this class of function is inherently dangerous performance
> wise.

there is potencial risk, but this risk isn't new. The almost all what
you say is true for array aggregates.

>
> * It looks incredibly easy to construct enormous lists. We should test
> the explosion limit of this to see how it is handled. Perhaps we need
> some parameter limits to control that, depending upon results.

There are no limit for generating large values - like bytea, xml, or
text. There are not limit for array_accum or array(query). So, I don't
think we need some special mechanism for listagg. If somebody will
generate too large a value, then he will get "out of memory"
exception.

>
> * Optimizer doesn't consider whether the result type of an aggregate get
> bigger as the aggregate processes more rows. If we're adding this
> function we should give some thought in that area also, or at least a
> comment to note that it can and will cause the optimizer problems in
> complex queries.
>

this is true, but this isn't some new. array_accum working well
without optimizer problems.

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

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

Re: [HACKERS] Resetting a single statistics counter

[Tom Lane]

Magnus Hagander  writes:
> 2010/1/24 Tom Lane :
>> The pg_stat_ prefix is some help but not enough IMO.  So I suggest
>> pg_stat_reset_table_counters and pg_stat_reset_function_counters.

> Doesn't the pg_stat_ part already say this?

My objection is that "reset_table" sounds like something you do to a
table, not something you do to stats.  No, I don't think the prefix is
enough to clarify that.

>> (BTW, a similar complaint could be made about the previously committed
>> patch: reset shared what?)

> Well, it could also be made about the original pg_stat_reset()
> function - reset what?

In that case, there's nothing but the "stat" to suggest what gets
reset, so I think it's less likely to be misleading than the current
proposals.  But if we'd been designing all of these at once, yeah,
I'd have argued for a more verbose name for that one too.

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] ECPG patch 4.1, out-of-scope cursor support in native mode

[Boszormenyi Zoltan]

Michael Meskes írta:
> On Fri, Jan 22, 2010 at 06:11:51PM +0100, Boszormenyi Zoltan wrote:
>   
>>> Why does the preproc spit out ECPGset_var's but no ECPGget_var's in this 
>>> test case? 
>>>   
>>>   
>> Because there's no ECPGget_var()s emitted for
>> - global variables
>> - variables in the same function
>>
>> ECPGget_var() is only used in case the cursor declaration
>> used INTO/USING and it's in a different function from
>> the one where OPEN/FETCH/CLOSE reside. But this
>> cannot be determined easily, e.g. short of making ECPG
>> a two-pass precompiler, so ECPGset_var() is always used.
>> 
>
> This shows some well thought implementation.

Thanks. But the truth is that your comment about "this
adjust_informix() hack goes into native mode only over
my dead body" made me come up with this change and
the reasoning. :-) Thus if some code with cursors does
everything in one function then only some useless
ECPGset_var() calls are added by the preprocessor,
making the change the least intrusive for old regression tests.

>  But what I was wondering about was
> why this is used as test case. While I see the need to test this part of ecpg 
> I
> thought this test case was added because it showed a problem without your
> changes.
>   

The problem that popped up first was that adjust_informix()
didn't properly deal with structs, remember? I tried some
small changes to fix that but they turned out to be improper
ones. The small changes worked for the initial problem,
i.e. IIRC in some cases adjust_informix() was bypassed
and the struct/union members were unrolled properly
for Informix compat mode, too. The regression test
"compat_informix/struct.pgc" shows this case. Then our
client showed a much larger programme which actually
used cursors in an event driver way. E.g. PgUp/PgDn
called different functions that contained only FETCH NEXT
or FETCH PRIOR. This was a curses based terminal programme
and my first little patch that solved only the struct unrolling case
failed in this case, because either ECPG segfaulted in
adjust_informix() or the code that ECPG produced for the
FETCH statements didn't have the proper output variables.
This is lead to the changes in the patch, which can be
summarized as:
1. track function names via the lexer (to minimize the impact
   of ECPGget_var(), now we can compare the function name
   where the cursor was DECLAREd and is used)
2. track type names for structs/unions to be able to emit
(* ( typename *) &var) in adjust_informix(), and
3. rewriting adjust_informix() to handle struct/union

The result is that cursors handling statements (OPEN/FETCH/MOVE)
can now be safely put into a different function from where
it was DECLAREd. And this makes possible to use ECPG
in event driven code, which is a feature that I think worth
considering making available in native mode, too. This is
why adjust_informix() was also renamed. And while I agree
with your comment about that "this can lead to dangerous
programming", I think the only thing needed is to document
the safety borders, not to prevent crossing them. C is about
"living la vida loca". ;-)

The regression test introduced (preproc/outofscope.pgc) tries
to show this functionality. Currently (without the patch) this
test makes ECPG abort in ecpg_type_name().

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


-- 
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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Tom Lane]

Bernd Helmle  writes:
> --On 24. Januar 2010 08:37:13 -0500 Robert Haas  
> wrote:
>> I think the problem case here might be something like this...

> Did that with a crude pl/pgsql script, and got the following numbers:

I think my concern about the original proposal was that the time to
perform an ALTER RENAME would increase with the number of tables in the
database, even if they were entirely unrelated to the one you're trying
to rename.  It's not clear to me whether the present coding of the patch
avoids that.  But in any case, the alternative implementation I
suggested would have added essentially zero runtime, so even a 50%
slowdown seems like sacrificing a lot.

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] Resetting a single statistics counter

[Magnus Hagander]

2010/1/24 Tom Lane :
> Euler Taveira de Oliveira  writes:
>> Magnus Hagander escreveu:
>>> Off to make it two separate functions.. (seems much more user-friendly
>>> than a single function with an extra argument, IMHO)
>
>> +1. But as Simon said _single_ is too ugly. What about
>> pg_stat_reset_user_{function,relation}?
>
> That implies that the operations wouldn't work against system tables;
> which they do.  I think a bigger problem is that "reset_single_table"
> seems like it might be talking about something like a TRUNCATE, ie,
> it's not clear that it means to reset counters rather than data.
> The pg_stat_ prefix is some help but not enough IMO.  So I suggest
> pg_stat_reset_table_counters and pg_stat_reset_function_counters.

Doesn't the pg_stat_ part already say this?


> (BTW, a similar complaint could be made about the previously committed
> patch: reset shared what?)

Well, it could also be made about the original pg_stat_reset()
function - reset what?


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

Re: [HACKERS] commit fests

[Andrew Dunstan]

Kevin Grittner wrote:

Andrew Dunstan  wrote:
 
  

As for getting more coverage of Beta, part of my trouble in
testing applications on Beta is that there aren't good tools I
know of for capture and replay of usage scenarios. Now there would
be a good project for someone, and it would be unaffected by the
Beta cycle.

 
Did you follow any of the dtester discussion?  That would allow

replay of complex usage scenarios, although they would be scripted,
rather than captured.  Would that help any?
 

  


Yes, I saw some, and I think it's useful. A capture tool, even if its 
output required some massaging, would be terrific, though.


cheers

andrew

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

Re: [HACKERS] tab completion for prepared transactions?

[Simon Riggs]

On Sun, 2010-01-24 at 12:54 -0500, Tom Lane wrote:
> Simon Riggs  writes:
> > I would like a mechanism in the server to prevent a session from
> > accepting anything other than COMMIT or ROLLBACK PREPARED after a
> > PREPARE. Otherwise it's possible to screw up your prepared xact and
> > leave it hanging there.
> 
> Uh, what?  That makes no sense at all.

Agreed

-- 
 Simon Riggs   www.2ndQuadrant.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] Resetting a single statistics counter

[Tom Lane]

Euler Taveira de Oliveira  writes:
> Magnus Hagander escreveu:
>> Off to make it two separate functions.. (seems much more user-friendly
>> than a single function with an extra argument, IMHO)

> +1. But as Simon said _single_ is too ugly. What about
> pg_stat_reset_user_{function,relation}?

That implies that the operations wouldn't work against system tables;
which they do.  I think a bigger problem is that "reset_single_table"
seems like it might be talking about something like a TRUNCATE, ie,
it's not clear that it means to reset counters rather than data.
The pg_stat_ prefix is some help but not enough IMO.  So I suggest
pg_stat_reset_table_counters and pg_stat_reset_function_counters.

(BTW, a similar complaint could be made about the previously committed
patch: reset shared what?)

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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Bernd Helmle]

--On 24. Januar 2010 08:37:13 -0500 Robert Haas  
wrote:




I agree - the requirements here are much looser than for, say, SELECT
or UPDATE.  But it still has to not suck.



Yeah, i think the meaning of "suck" can be much weakier than for a DML
command. However, if it would degrade the performance of a formerly well 
running command in a way, that it would be unusable, that would be annoying.



I think the problem case here might be something like this...  create
ten tables A1 through A10.  Now create 10 more tables B1 through B10
each of which inherits from all of A1 through A10.  Now create 10 more
tables C1 through C10 that inherit from B1 through B10.  Now create
1000 tables D1 through D1000 that inherit from C1 through C10.  Now
drop a column from A1.


Er... rename a column from A1, not drop.



Did that with a crude pl/pgsql script, and got the following numbers:

Current -HEAD:

Phenom-II 2.6 GHz: Time: 282,471 ms
MacBook: Time: 499,866 ms

With KaiGais recent patch (which covers the TYPE case, too):

Phenom-II 2.6 GHz: Time: 476,800 ms
MacBook: Time: 753,161 ms


--
Thanks

Bernd

--
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] Largeobject Access Controls (r2460)

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> It might be better to try a test case with lighter-weight objects,
>> say 5 million simple functions.
 
> So the current database is expendable?

Yeah, I think it was a bad experimental design anyway...

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] Resetting a single statistics counter

[Euler Taveira de Oliveira]

Magnus Hagander escreveu:
>> Off to make it two separate functions.. (seems much more user-friendly
>> than a single function with an extra argument, IMHO)
> 
+1. But as Simon said _single_ is too ugly. What about
pg_stat_reset_user_{function,relation}?

Another thing that is not a problem of your patch but it needs to be fixed is
that resetting functions remove the line from pg_stat_user_functions; that a
different behavior from other pg_stat_user_* functions.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] Resetting a single statistics counter

[Tom Lane]

Magnus Hagander  writes:
> Here goes.

Looks much saner.  One minor stylistic gripe:

+Datum
+pg_stat_reset_single_table(PG_FUNCTION_ARGS)
+{
+   pgstat_reset_single_counter(PG_GETARG_OID(0), RESET_TABLE);
+
+   PG_RETURN_VOID();
+}

I don't like sticking PG_GETARG calls inline in the body of a V1-protocol
function, even in trivial cases like this.  I think better style is

Oid taboid = PG_GETARG_OID(0);

pgstat_reset_single_counter(taboid, RESET_TABLE);

This approach associates a clear name and type with each argument,
thereby helping to buy back some of the readability we lose by not
being able to use regular C function declarations.  When we designed
the V1 call protocol, I had hoped we might someday have scripts that
would crosscheck such declarations against the pg_proc contents, and
I still haven't entirely given up that idea ...

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] Review: listagg aggregate

[Simon Riggs]

On Fri, 2010-01-22 at 11:14 -0800, David E.Wheeler wrote:
> No performance issues

ISTM that this class of function is inherently dangerous performance
wise. 

* It looks incredibly easy to construct enormous lists. We should test
the explosion limit of this to see how it is handled. Perhaps we need
some parameter limits to control that, depending upon results.

* Optimizer doesn't consider whether the result type of an aggregate get
bigger as the aggregate processes more rows. If we're adding this
function we should give some thought in that area also, or at least a
comment to note that it can and will cause the optimizer problems in
complex queries.

-- 
 Simon Riggs   www.2ndQuadrant.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] Largeobject Access Controls (r2460)

[Kevin Grittner]

Tom Lane  wrote:
 
> It might be better to try a test case with lighter-weight objects,
> say 5 million simple functions.
 
So the current database is expendable?  I'd just as soon delete it
before creating the other one, if you're fairly confident the other
one will do it.
 
-Kevin

-- 
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] tab completion for prepared transactions?

[Tom Lane]

Simon Riggs  writes:
> I would like a mechanism in the server to prevent a session from
> accepting anything other than COMMIT or ROLLBACK PREPARED after a
> PREPARE. Otherwise it's possible to screw up your prepared xact and
> leave it hanging there.

Uh, what?  That makes no sense at all.

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] Largeobject Access Controls (r2460)

[Tom Lane]

"Kevin Grittner"  writes:
> I'm afraid pg_dump didn't get very far with this before:
 
> pg_dump: WARNING:  out of shared memory
> pg_dump: SQL command failed
 
> Given how fast it happened, I suspect that it was 2672 tables into
> the dump, versus 26% of the way through 5.5 million tables.

Yeah, I didn't think about that.  You'd have to bump
max_locks_per_transaction up awfully far to get to where pg_dump
could dump millions of tables, because it wants to lock each one.

It might be better to try a test case with lighter-weight objects,
say 5 million simple functions.

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] tab completion for prepared transactions?

[Tom Lane]

Heikki Linnakangas  writes:
> Peter Eisentraut wrote:
>> The scenario that I encountered is that you go around manually cleaning
>> them up when the XA software fails for some reason.

> Hmm, that's a good point though. Maybe it would make sense to add tab
> completion for ROLLBACK/COMMIT PREPARED, but not for PREPARE TRANSACTION?

Huh?  PREPARE TRANSACTION creates a new gxid, so there's nothing for it
to complete against.

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] tab completion for prepared transactions?

[Simon Riggs]

On Sun, 2010-01-24 at 19:29 +0200, Heikki Linnakangas wrote:
> Peter Eisentraut wrote:
> > On lör, 2010-01-23 at 12:42 -0500, Tom Lane wrote:
> >> Peter Eisentraut  writes:
> >>> Was there a designed-in reason not to have psql tab completion for
> >>> COMMIT/ROLLBACK PREPARED ...?  It does complete the "PREPARED" but not
> >>> the transaction identifiers.  Maybe it's not a common use case, but
> >>> these transaction identifiers sure can be nontrivial to type.
> >> Hmm, what's the use scenario?  I would think that painfully long
> >> gxids would come from some XA manager software, which would be
> >> responsible for committing or canceling them.  Manual override
> >> of that would usually be a bad idea.
> 
> Right, I vaguely recall that the idea of tab-completion for those
> commands was rejected when 2PC was added because of that. A user sitting
> at a psql terminal is not supposed to prepare a transaction. That's
> application server's business.

Maybe so, but that doesn't actually stop people doing it.

I would like a mechanism in the server to prevent a session from
accepting anything other than COMMIT or ROLLBACK PREPARED after a
PREPARE. Otherwise it's possible to screw up your prepared xact and
leave it hanging there.

-- 
 Simon Riggs   www.2ndQuadrant.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] Resetting a single statistics counter

[Magnus Hagander]

2010/1/24 Magnus Hagander :
> 2010/1/24 Tom Lane :
>> Magnus Hagander  writes:
>>> In the spirit of finishing off small patches, attached is the one that
>>> implements pg_stat_reset_single(), to be able to reset stats for a
>>> single table or function. I kind of thought it would be included in
>>> the patch from Greg Smith for shared counters so I put it aside, but I
>>> guess I misunderstood him there. Anyway, I polished off the final
>>> part, and here it is.
>>
>> This is bogus; it assumes tables and functions will not have the same
>> OIDs.
>
> Gah... *faceinpalms*
>
> Off to make it two separate functions.. (seems much more user-friendly
> than a single function with an extra argument, IMHO)

Here goes.


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


resetsingle.patch
Description: Binary data

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

Re: [HACKERS] tab completion for prepared transactions?

[Bruce Momjian]

Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > Heikki Linnakangas wrote:
> >> Right, I vaguely recall that the idea of tab-completion for those
> >> commands was rejected when 2PC was added because of that. A user sitting
> >> at a psql terminal is not supposed to prepare a transaction. That's
> >> application server's business.
> > 
> > I think we should add a C comment documenting that fact.
> 
> Hmm, I thought we explained that in the manual, but I can't see it. I
> think we should add a note to the ref page of PREPARE TRANSACTION,
> something like:
> 
> "PREPARE TRANSACTION is not intended for use in applications or in
> interactive sessions. It's purpose is to allow an external transaction
> manager to perform atomic global transactions across multiple databases
> or other transactional resources. Unless you're writing a transaction
> manager, you probably shouldn't be using PREPARE TRANSACTION"

I was thinking of adding something in the psql C source code to explain
why certain commands are not implemented for tab completion.

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

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

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

Re: [HACKERS] tab completion for prepared transactions?

[Heikki Linnakangas]

Bruce Momjian wrote:
> Heikki Linnakangas wrote:
>> Right, I vaguely recall that the idea of tab-completion for those
>> commands was rejected when 2PC was added because of that. A user sitting
>> at a psql terminal is not supposed to prepare a transaction. That's
>> application server's business.
> 
> I think we should add a C comment documenting that fact.

Hmm, I thought we explained that in the manual, but I can't see it. I
think we should add a note to the ref page of PREPARE TRANSACTION,
something like:

"PREPARE TRANSACTION is not intended for use in applications or in
interactive sessions. It's purpose is to allow an external transaction
manager to perform atomic global transactions across multiple databases
or other transactional resources. Unless you're writing a transaction
manager, you probably shouldn't be using PREPARE TRANSACTION"

-- 
  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] commit fests

[Kevin Grittner]

Andrew Dunstan  wrote:
 
> As for getting more coverage of Beta, part of my trouble in
> testing applications on Beta is that there aren't good tools I
> know of for capture and replay of usage scenarios. Now there would
> be a good project for someone, and it would be unaffected by the
> Beta cycle.
 
Did you follow any of the dtester discussion?  That would allow
replay of complex usage scenarios, although they would be scripted,
rather than captured.  Would that help any?
 
-Kevin

-- 
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] Resetting a single statistics counter

[Simon Riggs]

On Sun, 2010-01-24 at 18:25 +0100, Magnus Hagander wrote:
> 2010/1/24 Tom Lane :
> > Magnus Hagander  writes:
> >> In the spirit of finishing off small patches, attached is the one that
> >> implements pg_stat_reset_single(), to be able to reset stats for a
> >> single table or function. I kind of thought it would be included in
> >> the patch from Greg Smith for shared counters so I put it aside, but I
> >> guess I misunderstood him there. Anyway, I polished off the final
> >> part, and here it is.
> >
> > This is bogus; it assumes tables and functions will not have the same
> > OIDs.
> 
> Gah... *faceinpalms*
> 
> Off to make it two separate functions.. (seems much more user-friendly
> than a single function with an extra argument, IMHO)

And a much better name also :-)

-- 
 Simon Riggs   www.2ndQuadrant.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] Largeobject Access Controls (r2460)

[Kevin Grittner]

Tom Lane  wrote:
 
> I'm not so worried about the amount of RAM needed as whether
> pg_dump's internal algorithms will scale to large numbers of TOC
> entries.  Any O(N^2) behavior would be pretty painful, for
> example.  No doubt we could fix any such problems, but it might
> take more work than we want to do right now.
 
I'm afraid pg_dump didn't get very far with this before:
 
pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
pg_dump: The command was: LOCK TABLE public.test2672 IN ACCESS SHARE
MODE
 
Given how fast it happened, I suspect that it was 2672 tables into
the dump, versus 26% of the way through 5.5 million tables.
 
A sampling of the vmstat 1 output lines in "baseline" state --
before the dump started:
 
procs ---memory-- ---swap-- -io -system--
-cpu--
 1  0 319804 583656  23372 12447324800 1722410 1742 18995 
9  1 88  2  0
 3  1 319804 595840  23368 12445885600 1701610 2014 22965 
9  1 89  1  0
 1  0 319804 586912  23376 12446912800 16808   158 1807 19181 
8  1 89  2  0
 2  0 319804 576304  23368 12447941600 16840 5 1764 19136 
8  1 90  1  0
 
 0  1 319804 590480  23364 12445988800  1488   130 3449 13844 
2  1 93  3  0
 0  1 319804 589476  23364 12446091200  1456   115 3328 11800 
2  1 94  4  0
 1  0 319804 588468  23364 12446194400  1376   146 3156 11770 
2  1 95  2  0
 1  1 319804 587836  23364 12446502400  1576   133 3599 14797 
3  1 94  3  0
 
While it was running:
 
procs ---memory-- ---swap-- -io -system--
-cpu--
 2  1 429080 886244  23308 11124246400 2568438 2920 18847 
7  3 85  5  0
 2  1 429080 798172  23308 11129797600 4002426 1342 16967
13  2 82  4  0
 2  1 429080 707708  23308 11135760000 4252034 1588 19148
13  2 81  4  0
 0  5 429080 620700  23308 11141414400 40272 73863 1434 18077
12  2 80  6  0
 1  5 429080 605616  23308 11142544800  6920 131232  729 5187 
3  1 66 31  0
 0  6 429080 582852  23316 11144291200 10840 131248  665 4987 
3  1 66 30  0
 2  4 429080 584976  23308 11143367200  9776 139416  693 7890 
4  1 66 29  0
 0  5 429080 575752  23308 11143675200 10776 131217  647 6157 
3  1 66 30  0
 1  3 429080 583768  23308 11142030400 13616 90352 1043 13047 
6  1 68 25  0
 4  0 429080 57  23300 11139769600 444 1347 25329
12  2 79  6  0
 2  1 429080 582368  23292 11136789600 4032076 1517 28628
13  2 80  5  0
 2  0 429080 584960  23276 11133809600 40240   163 1374 26988
13  2 80  5  0
 6  0 429080 576176  23268 11131960000 40328   170 1465 27229
13  2 80  5  0
 4  0 429080 583212  23212 11128881600 39568   138 1418 27296
13  2 80  5  0
 
This box has 16 CPUs, so the jump from 3% user CPU to 13% with an
increase of I/O wait from 3% to 5% suggests that pg_dump was
primarily CPU bound in user code before the crash.
 
I can leave this database around for a while if there are other
things you would like me to try.
 
-Kevin

-- 
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] tab completion for prepared transactions?

[Bruce Momjian]

Heikki Linnakangas wrote:
> Peter Eisentraut wrote:
> > On l?r, 2010-01-23 at 12:42 -0500, Tom Lane wrote:
> >> Peter Eisentraut  writes:
> >>> Was there a designed-in reason not to have psql tab completion for
> >>> COMMIT/ROLLBACK PREPARED ...?  It does complete the "PREPARED" but not
> >>> the transaction identifiers.  Maybe it's not a common use case, but
> >>> these transaction identifiers sure can be nontrivial to type.
> >> Hmm, what's the use scenario?  I would think that painfully long
> >> gxids would come from some XA manager software, which would be
> >> responsible for committing or canceling them.  Manual override
> >> of that would usually be a bad idea.
> 
> Right, I vaguely recall that the idea of tab-completion for those
> commands was rejected when 2PC was added because of that. A user sitting
> at a psql terminal is not supposed to prepare a transaction. That's
> application server's business.

I think we should add a C comment documenting that fact.

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

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

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

Re: [HACKERS] further explain changes

[Tom Lane]

Robert Haas  writes:
> On Sun, Jan 24, 2010 at 12:06 AM, Jaime Casanova
>> why not let it go in ANALYZE, just as the sort info

> It's kinda long-winded - it adds like 4 extra lines for each hash
> join.  I don't think I want to add that much clutter to regular E-A
> output.

Well, that would only happen if you're deliberately obtuse about the
formatting.  The sort code manages to fit all the extra on one line,
and I don't see why hash couldn't.

I'd vote for just adding it in the exact same cases that sort adds extra
info.  -1 for either adding a new option or changing the meaning of the
ones that are there.

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] tab completion for prepared transactions?

[Heikki Linnakangas]

Peter Eisentraut wrote:
> On lör, 2010-01-23 at 12:42 -0500, Tom Lane wrote:
>> Peter Eisentraut  writes:
>>> Was there a designed-in reason not to have psql tab completion for
>>> COMMIT/ROLLBACK PREPARED ...?  It does complete the "PREPARED" but not
>>> the transaction identifiers.  Maybe it's not a common use case, but
>>> these transaction identifiers sure can be nontrivial to type.
>> Hmm, what's the use scenario?  I would think that painfully long
>> gxids would come from some XA manager software, which would be
>> responsible for committing or canceling them.  Manual override
>> of that would usually be a bad idea.

Right, I vaguely recall that the idea of tab-completion for those
commands was rejected when 2PC was added because of that. A user sitting
at a psql terminal is not supposed to prepare a transaction. That's
application server's business.

> The scenario that I encountered is that you go around manually cleaning
> them up when the XA software fails for some reason.

Hmm, that's a good point though. Maybe it would make sense to add tab
completion for ROLLBACK/COMMIT PREPARED, but not for PREPARE TRANSACTION?

-- 
  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] Resetting a single statistics counter

[Magnus Hagander]

2010/1/24 Tom Lane :
> Magnus Hagander  writes:
>> In the spirit of finishing off small patches, attached is the one that
>> implements pg_stat_reset_single(), to be able to reset stats for a
>> single table or function. I kind of thought it would be included in
>> the patch from Greg Smith for shared counters so I put it aside, but I
>> guess I misunderstood him there. Anyway, I polished off the final
>> part, and here it is.
>
> This is bogus; it assumes tables and functions will not have the same
> OIDs.

Gah... *faceinpalms*

Off to make it two separate functions.. (seems much more user-friendly
than a single function with an extra argument, IMHO)


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

Re: [HACKERS] commit fests

[Andrew Dunstan]

Kevin Grittner wrote:

You've as much as said that it's a given that many of the
contributors will continue to work on new patches during this period
to earn a living, while hopefully volunteering to help with getting
the release out the door on their own time.  As I understand the
arguments, having that occur as a "guilty secret", with no community
discussion or review during that period, versus trying to find a way
to organizationally admit the fact and try to manage the available
resources in a real versus pretend way is the issue here.
 
  


It's not a guilty secret, it's not really a secret at all. People have 
in the past submitted patches during beta, and there has been extensive 
discussion about them. You just have to wait to get patches into the 
tree. In the past I suggested that we should branch much earlier to 
allow such patches to get into the tree. The argument against it, which 
I think is probably valid, is that we don't have the resources to manage 
both trees, and it could be a distraction for those working on polishing 
the release.


As for getting more coverage of Beta, part of my trouble in testing 
applications on Beta is that there aren't good tools I know of for 
capture and replay of usage scenarios. Now there would be a good project 
for someone, and it would be unaffected by the Beta cycle.


cheers

andrew





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

Re: [GENERAL] [HACKERS] Sugerencia de opcion

[Tom Lane]

Robert Haas  writes:
> 2009/1/22 Informatica-Cooperativa Cnel. Oviedo :
>>     SELECT id, sum(salario) as SumaSalario
>>     FROM salarios
>>     GROUP BY id
>>     HAVING SumaSalario>500;

> I've wished for that syntax once or twice myself, but I'm assuming
> there's a reason we haven't implemented it?

It's contrary to standard.  There are some other reasons you can find
in the archives, too.

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] Resetting a single statistics counter

[Tom Lane]

Magnus Hagander  writes:
> In the spirit of finishing off small patches, attached is the one that
> implements pg_stat_reset_single(), to be able to reset stats for a
> single table or function. I kind of thought it would be included in
> the patch from Greg Smith for shared counters so I put it aside, but I
> guess I misunderstood him there. Anyway, I polished off the final
> part, and here it is.

This is bogus; it assumes tables and functions will not have the same
OIDs.

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] Review: listagg aggregate

[David E. Wheeler]

On Jan 24, 2010, at 1:19 AM, Pavel Stehule wrote:

> can I help with it, please. My English is terrible.

Yes, I added a bit in the patch I submitted.

> array user functions are used more time in pg core. The complexity of
> array functions are much higher, so I don't think we need special
> file.

Okay. Should have tried it in PL/pgSQL then, perhaps.

> I'll recheck it. I am sure so all parameters should be a text.

Probably shouldn't go into varchar.c then, yes?

> We can, but it isn't good way. Processing of arrays is little bit more
> expensive then processing plain text. It is reason why listagg is
> faster, than your custom aggregate. More, the final function could be
> faster - the content is final.

Understood.

> It normal for aggregate functions. We need more transfn function,
> because we need two two variant: listagg(col), listagg(col, sep). Our
> coding guidlines doesn't advice share C functions - but these
> functions are +/- wrapper for accumStringResult - so there is zero
> overhead.

Ah, okay, it's because of the second argument. Now I understand.

> I don't think. When we have function, with same parameters, same
> behave like some Oracle function, then I am strongly prefer Oracle
> name. I don't see any benefit from different name. It can only confuse
> developers and add the trable to people who porting applications.

Meh. If the name is terrible, we don't have to use it, and it's easy enough to 
create an alias in SQL for those who need it.

Best,

David


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

[HACKERS] Resetting a single statistics counter

[Magnus Hagander]

In the spirit of finishing off small patches, attached is the one that
implements pg_stat_reset_single(), to be able to reset stats for a
single table or function. I kind of thought it would be included in
the patch from Greg Smith for shared counters so I put it aside, but I
guess I misunderstood him there. Anyway, I polished off the final
part, and here it is.

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


resetsingle.patch
Description: Binary data

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

Re: [HACKERS] commit fests

[Bruce Momjian]

Kevin Grittner wrote:
> Greg Smith  wrote:
>  
> > Developing new features is fun and tends to attract sponsorship
> > dollars. Testing a frozen release, finding bugs, and resolving them
> > is boring, and no one sponsors it. Therefore, if you let both
> > things go on at once, I guarantee you almost all of the community
> > attention will be diverted toward new development during any period
> > where both are happening at the same time.
>  
> Frankly, part of the problem is that it's hard for many of us to see
> how to contribute effectively for most of these five months or so, in
> general.  In particular, if someone *is* willing to pay you to work
> on developing a feature during these months, but not to work on any
> other PostgreSQL development, what do you recommend?

Yea, that is a problem.  Years ago the commit process was all driven by
a few individuals, and our group has been successful at distributing
that workload, so my hope is that the stabilization phase will also lend
itself to distributing the workload someday too.

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

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

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

Re: [HACKERS] handling contrib directories as modules not shared libraries

[Peter Eisentraut]

On lör, 2010-01-23 at 18:00 -0700, Brook Milligan wrote:
> It seems that src/Makefile.shlib has special cases for several
> directories that build loadable modules rather than shared libraries.
> The contrib/adminpack is one of the special cases, but none of the
> other contrib directories are.  As a result, they get built as shared
> libraries (i.e., as libXXX.so rather than XXX.so) and the
> corresponding *.sql.in files that load them refer to the wrong file
> and therefore fail.  The following patch (against the 8.3 release)
> fixes this by expanding the set of special cases to include all the
> contrib directories, not just contrib/adminpack.  I only have tested
> this with the uuid module, but it appears that all of them have the
> same organization.

This code has been reworked quite extensively since 8.3.  Please check
CVS head to see if it works for you.



-- 
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] RADIUS authentication

[Magnus Hagander]

2010/1/24 Magnus Hagander :
> 2010/1/20 KaiGai Kohei :
>> As Tom pointed out, it is fundamentally same.
>> The matter is this random() invocation is the first time after
>> initialization of random seed by srandom(). It means an external observer
>> can estimate the random value uniquely using pid and startup time.
>>
>> In other representation, the "random" value is the result of function
>> which takes arguments of pid and startup time, without random factor.
>>
>>  for (i = 0; i<  RADIUS_VECTOR_LENGTH; i++)
>>      packet->vector[i] = f(getpid(), port->SessionStartTime, i);
>>
>> One idea is to modify the logic to set up random seed in BackendRun().
>> In most of UNIX-like operating system, we can use /dev/random as a random
>> seed which is well randomized.
>>
>>  http://en.wikipedia.org/wiki//dev/random
>>
>> It seems to me PostmasterRandom() is a right place to set random seed,
>> and we can inject a block something like #ifdef HAVE_DEV_RANDOM ...
>>
>> Instead of such kind of efforts, we can also document that PostgreSQL and
>> RADIUS server should have communication using enough secure connection
>> explicitly. IMO, it will cover most of use cases.
>
> There is one more option here - use OpenSSL if available. It has
> functions for secure random number generations
> (http://www.openssl.org/docs/crypto/RAND_bytes.html).  That seems easy
> enough when OpenSSL is available.
>
> The question then becomes what do we do if we don't have OpenSSL
> available? Do we document that it's not secure, or refuse to run it?
> I'd vote for document it.. If you don't have SSL enabled, then you
> clearly don't use SSL for the libpq connection, which means the
> password goes in cleartext in that stream...

Updated patch attached.


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


radius.patch
Description: Binary data

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

Re: [HACKERS] Red-black tree for GIN

[Teodor Sigaev]

Would it be OK if I went through here and hacked on the comments and
sent this back to you?

Feel free to edit the patch.
--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
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] commit fests

[Kevin Grittner]

Greg Smith  wrote:
 
> Developing new features is fun and tends to attract sponsorship
> dollars. Testing a frozen release, finding bugs, and resolving them
> is boring, and no one sponsors it. Therefore, if you let both
> things go on at once, I guarantee you almost all of the community
> attention will be diverted toward new development during any period
> where both are happening at the same time.
 
Frankly, part of the problem is that it's hard for many of us to see
how to contribute effectively for most of these five months or so, in
general.  In particular, if someone *is* willing to pay you to work
on developing a feature during these months, but not to work on any
other PostgreSQL development, what do you recommend?
 
> That means that there's no possible way you can keep new
> development open without hurting the dreary work around stabilizing
> the beta in the process. 
 
Most of my time on stabilizing new releases has, in the past, been
volunteer, in spite of getting some time approved for testing some
releases on the clock.  This time around, *all* time I spend on this
phase will be volunteer -- it's just a question of whether my *paid*
time is spent on new development for PostgreSQL, or things entirely
unrelated to PostgreSQL.  If I'm working on PostgreSQL for pay, it
doesn't make me either less likely to volunteer time, or less skilled
at contributing during my volunteer efforts.
 
-Kevin


-- 
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] Red-black tree for GIN

[Robert Haas]

2010/1/24 Teodor Sigaev :
>> 3. This code could really use some more comments, and maybe some of
>> the variable names could be better chosen, too.  It's far from obvious
>> what is going on here.  I studied rbtrees in college and I still
>> remember more or less how they work, but, boy, this is hard to follow.
>>  The names of the iterator states are truly horrible, at least IMO.
>
> I changed names and slightly improve comments.

Would it be OK if I went through here and hacked on the comments and
sent this back to you?

...Robert

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

Re: [HACKERS] commit fests

[Kevin Grittner]

Andrew Dunstan  wrote:
 
>>> Perhaps it isn't that five months is outrageous, but that it
>>> doesn't really benefit from an unorganized swarm of activity by
>>> all the developers, and we've not worked out a reasonable
>>> framework for who should do what during that time to best benefit
>>> the project while giving all these volunteer and sponsored
>>> developers something they are willing to put effort into.
 
> And what are they sponsored for? I can't speak for others, but with
> one exception the only sponsorship I have received is for actual
> development work, not release finishing (and the exception ended up
> being mostly development anyway). Sponsors almost always want to
> provide money for actual features.
 
That was one of the points I was intending to convey.  After they
confirm that the beta release works on their software, what do they
currently do for the next five months?  Currently, as things now
stand.
 
> And as for volunteers, they have a fantastic resistance to
> being organized in some prescriptive way. We need to achieve what
> we can by persuasion. It's sometimes a pain in the neck, but it's
> the reality.
 
And what do we want *them* to do after spending a couple days effort
on beta testing.  (Even if you're going to let it run for months
parallel to production, how long does it take to *set that up*?)
 
> The real problem is that we take a long time between the end of the
> development phase and the release. That is often not something you
> can just throw bodies at ("Nine women can't make a baby in a
> month.").
 
Again, kinda my point.  So what *are* the other eight *currently*
doing?  (I guess we don't want to get too graphic about that if we're
going to follow that analogy out)
 
> Sadly, some things do just take time to work out. It's frustrating,
> but shortening the time could simply result in our making less
> polished releases.
 
I thought the quote at the top was specifically about *not*
shortening the time, but trying to figure out how we can best keep
resources working to the benefit of the project during that time.
You've as much as said that it's a given that many of the
contributors will continue to work on new patches during this period
to earn a living, while hopefully volunteering to help with getting
the release out the door on their own time.  As I understand the
arguments, having that occur as a "guilty secret", with no community
discussion or review during that period, versus trying to find a way
to organizationally admit the fact and try to manage the available
resources in a real versus pretend way is the issue here.
 
-Kevin

-- 
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] Sugerencia de opcion

[Robert Haas]

2009/1/22 Informatica-Cooperativa Cnel. Oviedo :
> Buenos Dias todos,
>
>                             Soy un usuario de postgres de Paraguay, consulto
> sobre la posibilidad de inclucion en la futura version la siguiente
> sentencia(Uso de alias en la condicion HAVING ):
>
>
>     SELECT id, sum(salario) as SumaSalario
>     FROM salarios
>     GROUP BY id
>     HAVING SumaSalario>500;

I've wished for that syntax once or twice myself, but I'm assuming
there's a reason we haven't implemented it?  Part of the problem is
it's inheritantly ambiguous if salarios happens to contain a column
called sumasalario, which is a problem that seems to arise for me
fairly regularly in practice.  Still, it would be nice for WHERE/GROUP
BY/HAVING clauses to have an explicit way to reference "the target
list column called foo".

...Robert

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

Re: [HACKERS] Red-black tree for GIN

[Teodor Sigaev]

1. I think rb_free_recursive is missing a pfree().


Fixed, thank you


2. We already have a definition of NIL in the PG source base.  I think
this one needs to be named something else.  RBNIL, maybe.

fixed



3. This code could really use some more comments, and maybe some of
the variable names could be better chosen, too.  It's far from obvious
what is going on here.  I studied rbtrees in college and I still
remember more or less how they work, but, boy, this is hard to follow.
  The names of the iterator states are truly horrible, at least IMO.

I changed names and slightly improve comments.



4. It would be nice if you could do a better job conforming to project
indentation style.

Did pgindent.
--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/


rbtree-0.6.gz
Description: Unix tar archive

-- 
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] RADIUS authentication

[Magnus Hagander]

2010/1/20 KaiGai Kohei :
> (2010/01/20 0:19), Magnus Hagander wrote:
>>> * I think this comment is right.
>>>   +   for (i = 0; i<  RADIUS_VECTOR_LENGTH; i++)
>>>   +       /* XXX: Generate a more secure random string? */
>>>   +       packet->vector[i] = random() % 255;
>>>
>>>   The random seed is initialized at BackendRun() with MyProcPid and
>>>   the time of backend process launched.
>>>   Then, PostgresMain() ->  InitPostgres() ->  PerformAuthentication()
>>>   will be called, and this random() shall be the first call just after
>>>   initialization of the srandom().
>>>
>>>   Do you have any good idea?
>>>   Or, do you think it should be fixed with high priority?
>>
>> It does need a fairly good random number generator there to be secure,
>> so it should probably be improved. OTOH, the whole thing can be more
>> considered obfuscation rather than encryption, and those who really
>> care about higher security will use ipsec or trusted networks.
>>
>> Maybe switching to erand48() would make this better, and good enough?
>
> As Tom pointed out, it is fundamentally same.
> The matter is this random() invocation is the first time after
> initialization of random seed by srandom(). It means an external observer
> can estimate the random value uniquely using pid and startup time.
>
> In other representation, the "random" value is the result of function
> which takes arguments of pid and startup time, without random factor.
>
>  for (i = 0; i<  RADIUS_VECTOR_LENGTH; i++)
>      packet->vector[i] = f(getpid(), port->SessionStartTime, i);
>
> One idea is to modify the logic to set up random seed in BackendRun().
> In most of UNIX-like operating system, we can use /dev/random as a random
> seed which is well randomized.
>
>  http://en.wikipedia.org/wiki//dev/random
>
> It seems to me PostmasterRandom() is a right place to set random seed,
> and we can inject a block something like #ifdef HAVE_DEV_RANDOM ...
>
> Instead of such kind of efforts, we can also document that PostgreSQL and
> RADIUS server should have communication using enough secure connection
> explicitly. IMO, it will cover most of use cases.

There is one more option here - use OpenSSL if available. It has
functions for secure random number generations
(http://www.openssl.org/docs/crypto/RAND_bytes.html).  That seems easy
enough when OpenSSL is available.

The question then becomes what do we do if we don't have OpenSSL
available? Do we document that it's not secure, or refuse to run it?
I'd vote for document it.. If you don't have SSL enabled, then you
clearly don't use SSL for the libpq connection, which means the
password goes in cleartext in that stream...


>>> * It casts char array (such as radius_buffer) into radius_packet
>>>   structure. The radius_packet structure represents the format of
>>>   RADIUS network packet as is.
>>>   It may be preferable to give compiler a hint not to align this
>>>   structure.
>>>   In GCC, we can use "__attribute__((packed))" to suggest not to
>>>   align the member of structure. Is there any portable way for this?
>>
>> This I can't answer, I don't know this well enough. Somebody else?
>
> What manner is applied to handle network protocol in other part?
>
> The radius_packet is declared as follows:
>
> + typedef struct
> + {
> +   unsigned char   code;                            +0
> +   unsigned char   id;                              +1
> +   unsigned short  length;                          +2
> +   unsigned char   vector[RADIUS_VECTOR_LENGTH];    +4? +8?
> + } radius_packet;
>
> It may be a bit nervous, except for possible alignment of the vector
> on 64bit architecture.
>
> And, one more. It seems to me uint8 and uint16 are more preferable than
> unsigned char/short in this context.

Yeha, that is probably right. I copied that off a reference
implementation of the struct. Will change accordingly.

FWIW, I tested it on Win64 and it didn't have any issues there at least.

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

Re: [HACKERS] ECPG patch 4.1, out-of-scope cursor support in native mode

[Michael Meskes]

On Fri, Jan 22, 2010 at 06:11:51PM +0100, Boszormenyi Zoltan wrote:
> > Why does the preproc spit out ECPGset_var's but no ECPGget_var's in this 
> > test case? 
> >   
> 
> Because there's no ECPGget_var()s emitted for
> - global variables
> - variables in the same function
> 
> ECPGget_var() is only used in case the cursor declaration
> used INTO/USING and it's in a different function from
> the one where OPEN/FETCH/CLOSE reside. But this
> cannot be determined easily, e.g. short of making ECPG
> a two-pass precompiler, so ECPGset_var() is always used.

This shows some well thought implementation. But what I was wondering about was
why this is used as test case. While I see the need to test this part of ecpg I
thought this test case was added because it showed a problem without your
changes.

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
ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org
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] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Robert Haas]

On Sun, Jan 24, 2010 at 8:36 AM, Robert Haas  wrote:
> On Sat, Jan 23, 2010 at 10:48 PM, KaiGai Kohei  wrote:
>> (2010/01/24 12:29), Robert Haas wrote:
>>> I don't think this is ready for committer, becauseTom previously
>>> objected to the approach taken by this patch here, and no one has
>>> answered his objections:
>>>
>>> http://archives.postgresql.org/pgsql-hackers/2010-01/msg00144.php
>>>
>>> I think someone needs to figure out what the worst-case scenario for
>>> this is performance-wise and submit a reproducible test case with
>>> benchmark results.  In the meantime, I'm going to set this to Waiting
>>> on Author.
>>
>> Basically, I don't think it is not a performance focused command,
>> because we may be able to assume ALTER TABLE RENAME TO is not much
>> frequent operations.
>
> I agree - the requirements here are much looser than for, say, SELECT
> or UPDATE.  But it still has to not suck.
>
> I think the problem case here might be something like this...  create
> ten tables A1 through A10.  Now create 10 more tables B1 through B10
> each of which inherits from all of A1 through A10.  Now create 10 more
> tables C1 through C10 that inherit from B1 through B10.  Now create
> 1000 tables D1 through D1000 that inherit from C1 through C10.  Now
> drop a column from A1.

Er... rename a column from A1, not drop.

...Robert

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

Re: [HACKERS] [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

[Robert Haas]

On Sat, Jan 23, 2010 at 10:48 PM, KaiGai Kohei  wrote:
> (2010/01/24 12:29), Robert Haas wrote:
>> I don't think this is ready for committer, becauseTom previously
>> objected to the approach taken by this patch here, and no one has
>> answered his objections:
>>
>> http://archives.postgresql.org/pgsql-hackers/2010-01/msg00144.php
>>
>> I think someone needs to figure out what the worst-case scenario for
>> this is performance-wise and submit a reproducible test case with
>> benchmark results.  In the meantime, I'm going to set this to Waiting
>> on Author.
>
> Basically, I don't think it is not a performance focused command,
> because we may be able to assume ALTER TABLE RENAME TO is not much
> frequent operations.

I agree - the requirements here are much looser than for, say, SELECT
or UPDATE.  But it still has to not suck.

I think the problem case here might be something like this...  create
ten tables A1 through A10.  Now create 10 more tables B1 through B10
each of which inherits from all of A1 through A10.  Now create 10 more
tables C1 through C10 that inherit from B1 through B10.  Now create
1000 tables D1 through D1000 that inherit from C1 through C10.  Now
drop a column from A1.

...Robert

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

Re: [HACKERS] further explain changes

[Robert Haas]

On Sun, Jan 24, 2010 at 12:06 AM, Jaime Casanova
 wrote:
> On Sat, Jan 23, 2010 at 10:08 PM, Robert Haas  wrote:
>>
>> I was also thinking about the possibility of adding a new option
>> called "output" and making that control whether the "Output" line gets
>> printed.  It's kind of annoying to use EXPLAIN (ANALYZE, VERBOSE)
>
> why not let it go in ANALYZE, just as the sort info

It's kinda long-winded - it adds like 4 extra lines for each hash
join.  I don't think I want to add that much clutter to regular E-A
output.

If people don't like making it controlled by VERBOSE, then I vote for
a new option, as Greg Stark suggested.

...Robert

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