Re: [HACKERS] Fix for Index Advisor related hooks

[Heikki Linnakangas]

On 17.02.2011 14:30, Gurjeet Singh wrote:

On Wed, Feb 16, 2011 at 6:37 PM, Tom Lane  wrote:


Gurjeet Singh  writes:

On Wed, Feb 16, 2011 at 10:25 AM, Tom Lane  wrote:

The only reason you'd need that code is if you were trying to construct
a fake Relation structure, which seems unnecessary and undesirable.



The planner requires IndexOptInfo, and for the planner to choose the
hypothetical index we need to fill in the fwdsortop, revsortop, opfamily

and

opcintype, and this is the information that IndexAdvisor populates using
IndexSupportInitialize() (at least until c0b5fac7 changed the function
signature.


Yeah, and the set of stuff you need in IndexOptInfo changed again last
week; see collations.  Direct access to IndexSupportInitialize is even
less useful today than it was a week ago.  This stuff has changed many
times before, and it will change again in the future, and exporting a
private function that has an unrelated purpose is not going to insulate
you from needing to deal with that.



I guess you are right.





What would be the best way to build an IndexOptInfo for a plain BTREE

index

for different data types?


Fetch the values you need and stuff 'em in the struct.  Don't expect
relcache to do it for you.  The only reason relcache is involved in the
current workflow is that we try to cache the information across queries
in order to save on planner startup time ... but I don't think that that
concern is nearly as pressing for something like Index Advisor.  You'll
have enough to do tracking changes in IndexOptInfo, you don't need to
have to deal with refactorings inside relcache as well.



I also wish to make Index Advisor faster by not having to lookup this info
every time a new query comes in and that's why I was trying to use the guts
of IndexSupportInitialize() where it does the caching.


I doubt performance matters much here. It's not like you're going to be 
explaining hundreds of queries per second with a hypotethical index 
installed. I think of this as a manual tool that you run from a GUI when 
you wonder if an index on column X would help.


The question is, can the Index Advisor easilt access all the information 
it needs to build the IndexOptInfo? If not, what's missing?


--
  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] pika failing since the per-column collation patch

[Tom Lane]

=?iso-8859-1?Q?R=E9mi_Zara?=  writes:
> Le 12 févr. 2011 à 18:51, Peter Eisentraut a écrit :
>> It's only failing on this one machine, but there isn't anything
>> platform-specific in this code, so I'd look for memory management faults
>> on the code or a compiler problem.  Try with lower optimization for a
>> start.

> Same failure with -O0 (and more shared memory).

Note that the query that is failing is an intentional probe of
robustness:

-- check that we can apply functions taking ANYARRAY to pg_stats ...
-- such functions must protect themselves if varying element type isn't OK
select max(histogram_bounds) from pg_stats;
ERROR:  cannot compare arrays of different element types

pika is instead showing

ERROR:  locale operation to be invoked, but no collation was derived

which some trawling through the code says is coming from varstr_cmp
when fn_collation didn't get set on the call.

Hypothesis: the platform-dependency here is just one of row ordering,
to wit, on most platforms the scan of pg_statistic fails before it gets
to the case where the collation issue is triggered.  I'm suspicious that
if two text arrays get compared via this code path, fn_collation fails
to get set, and it's not a platform-specific omission.

It'd be helpful if you could identify the specific values that are
getting compared at the moment of the failure.

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] About the performance of startup after dropping many tables

[Gan Jiadong]

Hi,

  Thanks for your reply. 
  Of course, we will think about whether 4 relations dropping is
reasonable. In fact, this happens in a very special scenario . 
  But when we analyzed this issue, we found the PG code can be rewritten to
achieve better performance. Or we can say the arithmetic of this part is not
good enough. 
  For example, by doing the refactoring as we done, the startup time can be
reduced from 3 minutes to 8 seconds, It is quite a great improvement,
especially for the systems with low TTR (time to recovery) requirement.

  There is any problem or risk to change this part of code as we suggested?
Thank you.

Best reguards,

甘嘉栋(Gan Jiadong)
E-MAIL: ga...@huawei.com
Tel：+86-755-289720578

*
This e-mail and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!

* 


-邮件原件-
发件人: Tom Lane [mailto:t...@sss.pgh.pa.us] 
发送时间: 2011年2月18日 11:37
收件人: Gan Jiadong
抄送: pgsql-hackers@postgresql.org; liyue...@huawei.com; yaoy...@huawei.com;
liuxin...@huawei.com; tianweng...@huawei.com
主题: Re: [HACKERS] About the performance of startup after dropping many
tables

Gan Jiadong  writes:
> we have PG 8.3.13 in our system. When running performance cases, we find
the
> startup recovery cost about 3 minutes. It is too long in our system. 

Maybe you should rethink the assumption that dropping 4 tables is a
cheap operation.  Why do you have that many in the first place, let
alone that many that you drop and recreate frequently?  Almost
certainly, you need a better-conceived schema.

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] About the performance of startup after dropping many tables

[Gan Jiadong]

Hi,

  Thanks for your reply. 
  Of course, we will think about whether 4 relations dropping is
reasonable. In fact, this happens in a very special scenario . 
  But when we analyzed this issue, we found the PG code can be rewritten to
achieve better performance. Or we can say the arithmetic of this part is not
good enough. 
  For example, by doing the refactoring as we done, the startup time can be
reduced from 3 minutes to 8 seconds, It is quite a great improvement,
especially for the systems with low TTR (time to recovery) requirement.

  There is any problem or risk to change this part of code as we suggested?
Thank you.

 
Best reguards,

甘嘉栋(Gan Jiadong)
E-MAIL: ga...@huawei.com
Tel：+86-755-289720578

*
This e-mail and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!

* 


-邮件原件-
发件人: Tom Lane [mailto:t...@sss.pgh.pa.us] 
发送时间: 2011年2月18日 11:37
收件人: Gan Jiadong
抄送: pgsql-hackers@postgresql.org; liyue...@huawei.com; yaoy...@huawei.com;
liuxin...@huawei.com; tianweng...@huawei.com
主题: Re: [HACKERS] About the performance of startup after dropping many
tables

Gan Jiadong  writes:
> we have PG 8.3.13 in our system. When running performance cases, we find
the
> startup recovery cost about 3 minutes. It is too long in our system. 

Maybe you should rethink the assumption that dropping 4 tables is a
cheap operation.  Why do you have that many in the first place, let
alone that many that you drop and recreate frequently?  Almost
certainly, you need a better-conceived schema.

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] knngist - 0.8

[Tom Lane]

Teodor Sigaev  writes:
>> I've applied all of this, and written documentation for all of it,
> Thank you a lot
>> except for the contrib/btree_gist additions which still need to be
>> redone for the revised API (and then documented!).  My patience ran out

> Done, btree_gist is reworked for a new API.

I did a quick look at this patch.  The major problem with it is of
course that it needs to be fixed for the recent extension-related
changes.  I transposed the .sql.in changes into additions to 
btree_gist--1.0.sql (attached), but haven't really sanity-checked
them beyond checking that the regression tests pass.  The same mods
would need to be made in btree_gist--unpackaged--1.0.sql.

However, I feel that this is not ready to apply even given those fixes.
Problems yet to solve:

1. oid_dist() returns oid ... really?  Oid is unsigned.  I'd be inclined
to argue though that distance between Oids is a meaningless concept, so
you should remove this not just mess with the result type.  Anybody who
actually wants to form a distance between Oids should have to cast them
to an arithmetic type first.  Let the user figure out how wraparound
cases should be handled.

2. Beyond that, none of the distance routines have given any thought to
avoiding overflow.  For instance, dist_int2 had better return something
wider than int2, and so on up.  It looks to me like the internal gist
distance functions also suffer overflow risks, in that they tend to form
the difference first (in the source datatype) and only afterwards cast
to float8.

3. I was surprised that there wasn't a distance implementation for
numeric.  I suppose that this might be difficult to do without risking
overflow in conversion to float8, though.

4. I didn't much care for changing the result type of gbt_num_consistent
from bool to float8; that's just messy, and I don't see any compensating
advantage.  I suggest you leave gbt_num_consistent and its callers
alone, and add a separate gbt_num_distance routine that only handles the
KNNDistance case.

There might be more issues, I haven't read the patch in detail.
But anyway, I'm going to set it to Waiting on Author.  I think it
needs at least a day or so's work, and I can't put in that kind of
time on it now.

regards, tom lane


diff --git a/contrib/btree_gist/btree_gist--1.0.sql b/contrib/btree_gist/btree_gist--1.0.sql
index 1ea5fa2db418d06f991f362a956fec07ccfba9e9..dd428995c185a09519ed65433081752b83b71bb7 100644
*** a/contrib/btree_gist/btree_gist--1.0.sql
--- b/contrib/btree_gist/btree_gist--1.0.sql
*** CREATE TYPE gbtreekey_var (
*** 81,86 
--- 81,231 
  	STORAGE = EXTENDED
  );
  
+ --distance operators
+ 
+ CREATE FUNCTION cash_dist(money, money)
+ RETURNS money
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = money,
+ 	RIGHTARG = money,
+ 	PROCEDURE = cash_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION date_dist(date, date)
+ RETURNS int4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = date,
+ 	RIGHTARG = date,
+ 	PROCEDURE = date_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION float4_dist(float4, float4)
+ RETURNS float4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = float4,
+ 	RIGHTARG = float4,
+ 	PROCEDURE = float4_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION float8_dist(float8, float8)
+ RETURNS float8
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = float8,
+ 	RIGHTARG = float8,
+ 	PROCEDURE = float8_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION int2_dist(int2, int2)
+ RETURNS int2
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = int2,
+ 	RIGHTARG = int2,
+ 	PROCEDURE = int2_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION int4_dist(int4, int4)
+ RETURNS int4
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = int4,
+ 	RIGHTARG = int4,
+ 	PROCEDURE = int4_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION int8_dist(int8, int8)
+ RETURNS int8
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = int8,
+ 	RIGHTARG = int8,
+ 	PROCEDURE = int8_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION interval_dist(interval, interval)
+ RETURNS interval
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = interval,
+ 	RIGHTARG = interval,
+ 	PROCEDURE = interval_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION oid_dist(oid, oid)
+ RETURNS oid
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = oid,
+ 	RIGHTARG = oid,
+ 	PROCEDURE = oid_dist,
+ 	COMMUTATOR = '<->'
+ );
+ 
+ CREATE FUNCTION time_dist(time, time)
+ RETURNS interval
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C IMMUTABLE STRICT;
+ 
+ CREATE OPERATOR <-> (
+ 	LEFTARG = time,
+ 	RIGHTARG

Re: [HACKERS] CommitFest 2011-01 as of 2011-02-04

[Itagaki Takahiro]

On Fri, Feb 18, 2011 at 13:15, Shigeru HANADA  wrote:
> When I've used COPY TO for testing file_fdw, I got wrong result.
> It would be because DR_copy's processed is not initialized in
> CreateCopyDestReceiver().  Please see attached patch.

Oops, thanks, applied.

-- 
Itagaki Takahiro

-- 
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] Transaction-scope advisory locks

[Itagaki Takahiro]

On Thu, Feb 17, 2011 at 17:05, Itagaki Takahiro
 wrote:
> I did a few cosmetic fixes, mainly lmgr/README and make a subroutine
> ReleaseLockForOwner() for LockReleaseSession and LockReleaseCurrentOwner.

Committed with a few typo fixes. Thanks, Marko!

-- 
Itagaki Takahiro

-- 
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] CommitFest 2011-01 as of 2011-02-04

[Shigeru HANADA]

On Wed, 16 Feb 2011 11:22:04 +0900
Itagaki Takahiro  wrote:
> Thanks comments. I've applied the COPY API patch.

When I've used COPY TO for testing file_fdw, I got wrong result.
# Actually csv_branches has only 10 rows.

postgres=# copy (select * from csv_branches) to 
'/home/hanada/DB/BINARY/branches.bin' with binary;
COPY 9187201950435737481

It would be because DR_copy's processed is not initialized in
CreateCopyDestReceiver().  Please see attached patch.

Regards,
--
Shigeru Hanada


20110218_initialize_processed.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] Initial review of xslt with no limits patch

[Tom Lane]

Bruce Momjian  writes:
> Tom Lane wrote:
>> I think we have a few TODO items here:
>> 
>> * Invent ... and document ... an API that permits safe assembly of a
>> parameter list from non-constant (and perhaps untrustworthy) values.
>> 
>> * Fix xslt_process' failure to report (some?) errors detected by libxslt.
>> 
>> * Move the functionality to a less deprecated place.
>> 
>> None of these are within the scope of the current patch though.

> Should any of these be added to our TODO list under XML?

Yes, all of them, since nothing's been done about any of 'em ...

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] COPY ENCODING revisited

[Itagaki Takahiro]

On Fri, Feb 18, 2011 at 04:04, Hitoshi Harada  wrote:
> FWIW, I finally found the good example to cache miscellaneous data in
> file local, namely regexp.c. It allocates compiled regular expressions
> up to 32 by using malloc().

I'm not exactly sure the cache usage in mbutils.c because it doesn't have
routine for cache invalidation at all.  Conversion procs are rarely
replaced, but we might not ought to keep cached functions unlimitedly.

Regexp cache is OK because the algorithm cannot be modified at runtime.

> We need only 4 or so for encoding
> conversion cache, in which cache search doesn't seem like overhead.

We need to research what we should cache for conversion procs.
We will need 4 bytes per conversion pair if we cache only OIDs,
but sizeof(FmgrInfo) bytes if we use the same way as ToXXXConvProc cache.

-- 
Itagaki Takahiro

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

Re: [HACKERS] Initial review of xslt with no limits patch

[Bruce Momjian]

Tom Lane wrote:
> Mike Fowler  writes:
> > On 06/08/10 17:50, Pavel Stehule wrote:
> >> attached updated patch with regression test
> 
> > Bravely ignoring the quotation/varidic/ 
> > conversations, I've taken a look at the patch as is. Thanks to Tom's 
> > input I can now correctly drive the function. I can also report that 
> > code is now behaving in the expected way.
> 
> I've gone ahead and applied this patch, since the subsequent discussion
> seemed to be getting *extremely* far afield from the expressed intent
> of the patch, and nobody had pointed out a reason not to fix the
> number-of-parameters limitation.
> 
> I think we have a few TODO items here:
> 
> * Invent ... and document ... an API that permits safe assembly of a
> parameter list from non-constant (and perhaps untrustworthy) values.
> 
> * Fix xslt_process' failure to report (some?) errors detected by libxslt.
> 
> * Move the functionality to a less deprecated place.
> 
> None of these are within the scope of the current patch though.

Should any of these be added to our TODO list under XML?

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

  + It's impossible for everything to be true. +

-- 
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] Add support for logging the current role

[Tom Lane]

Robert Haas  writes:
> On Thu, Feb 17, 2011 at 4:53 PM, Tom Lane  wrote:
>> In short, add a bit of overhead at SetUserId time in order to make this
>> cheap (and accurate) in elog.c.

> As Stephen says, I think this is utterly impractical; those routines
> can't ever throw any kind of error.

Why would they need to throw an error?  It'd be on the caller's head to
supply the role name along with OID.  We can keep the name in a static
buffer of size NAMEDATALEN, so don't tell me about palloc failures
either.

The logging design as it stands seems to me to be a Rube Goldberg device
that is probably going to have corner-case bugs quite aside from its
possible performance issues.

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] About the performance of startup after dropping many tables

[Tom Lane]

Gan Jiadong  writes:
> we have PG 8.3.13 in our system. When running performance cases, we find the
> startup recovery cost about 3 minutes. It is too long in our system. 

Maybe you should rethink the assumption that dropping 4 tables is a
cheap operation.  Why do you have that many in the first place, let
alone that many that you drop and recreate frequently?  Almost
certainly, you need a better-conceived schema.

regards, tom lane

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

[HACKERS] WIP - Add ability to constrain backend temporary file space

[Mark Kirkwood]

Recently two systems here have suffered severely with excessive 
temporary file creation during query execution. In one case it could 
have been avoided by more stringent qa before application code release, 
whereas the other is an ad-hoc system, and err...yes.


In both cases it would have been great to be able to constrain the 
amount of temporary file space a query could use. In theory you can sort 
of do this with the various ulimits, but it seems pretty impractical as 
at that level all files look the same and you'd be just as likely to 
unexpectedly crippled the entire db a few weeks later when a table grows...


I got to wonder how hard this would be to do in Postgres, and attached 
is my (WIP) attempt. It provides a guc (max_temp_files_size) to limit 
the size of all temp files for a backend and amends fd.c cancel 
execution if the total size of temporary files exceeds this.


This is WIP, it does seem to work ok, but some areas/choices I'm not 
entirely clear about are mentioned in the patch itself. Mainly:


- name of the guc... better suggestions welcome
- datatype for the guc - real would be good, but at the moment the nice 
parse KB/MB/GB business only works for int


regards

Mark


temp-files-v1.patch.gz
Description: GNU Zip compressed data

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

Re: [HACKERS] Replication server timeout patch

[Robert Haas]

On Thu, Feb 17, 2011 at 9:10 PM, Fujii Masao  wrote:
> On Fri, Feb 18, 2011 at 7:55 AM, Josh Berkus  wrote:
>>> So, in summary, the position is that we have a timeout, but that timeout
>>> doesn't work in all cases. But it does work in some, so that seems
>>> enough for me to say "let's commit". Not committing gives us nothing at
>>> all, which is as much use as a chocolate teapot.
>>
>> Can someone summarize the cases where it does and doesn't work?
>> There's been a longish gap in this thread.
>
> The timeout doesn't work when walsender gets blocked during sending the
> WAL because the send buffer has been filled up, I'm afraid. IOW, it doesn't
> work when the standby becomes unresponsive while WAL is generated on
> the master one after another. Since walsender tries to continue sending the
> WAL while the standby is unresponsive, the send buffer gets filled up and
> the blocking send function (e.g., pq_flush) blocks the walsender.
>
> OTOH, if the standby becomes unresponsive when there is no workload
> which causes WAL, the timeout would work.

IMHO, that's so broken as to be useless.

I would really like to have a solution to this problem, though.
Relying on TCP keepalives is weak.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Add support for logging the current role

[Robert Haas]

On Thu, Feb 17, 2011 at 4:53 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> It seems there's at least one more thing to worry about here, which is
>> the overhead of this computation when CSV logging is in use.  If no
>> SET ROLE or SET SESSION AUTHORIZATION commands are in use, the code
>> will call show_role(), which will return "none".  We'll then strcmp()
>> that against "none" and decide to call show_session_authorization(),
>> which will call strtoul() to find the comma separator and then return
>> a pointer to the string that follows it.  Now, none of that is
>> enormously expensive, so maybe it's not worth worrying about, but
>> since logging can be a hotspot, I thought I'd mention it and solicit
>> an opinion on whether that's likely to be a problem in practice.
>
> Well, in the first place, going through two not-very-related APIs in
> order to reverse-engineer what miscinit.c already knows is pretty silly
> (not to mention full of possible bugs).  We ought to be looking at the
> GetUserId state directly.
>
> Now you will complain that elog.c mustn't try to map that OID back to
> string form, which is true.  But IIRC, we used to keep the current
> userid stored in both OID and string form.  The string form was removed
> as unnecessary overhead, but maybe it'd be a good idea to put that back.
>
> In short, add a bit of overhead at SetUserId time in order to make this
> cheap (and accurate) in elog.c.

As Stephen says, I think this is utterly impractical; those routines
can't ever throw any kind of error.  I was mostly wondering whether we
ought to create a function show_explicitly_set_role() or somesuch that
would do basically the same thing as the proposed code, but try to
avoid the strcmp in the common case where no set role has been done.
I'm not very certain it's worth worrying about, though.
write_csvlog() is not a trivial function as it is.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] About the performance of startup after dropping many tables

[Gan Jiadong]

 

Hello guys, 

 

we have PG 8.3.13 in our system. When running performance cases, we find the
startup recovery cost about 3 minutes. It is too long in our system. 

We diagnose the problem by adding timestamp. Finally, we find almost all 3
minutes were used by the relation dropping and buffer invalidation loop in
xact_redo_commit. 

Before the problem happens, we drop 4 tables and reboot linux. So the
out loop will run 4 times . And we have 13000 share buffer pages in PG.
But in DropRelFileNodeBuffers who is used to drop shared buffer associated
to the specified relation we will have to run through all the shared buffers
for each relation to check whether the buffer can be dropped, no matter how
many pages the relation has in shared buffer. 

In all, we will have 4 * 13000 LWLock acquire and release. Is this
necessary? How about building a hash to record all relfilenode to be
dropped, and run through the shared buffers once to check where the buffer's
relfilenode is going to be dropped! If we can do this, LWLock traffic will
be 13000 , we will have much better performance!

Does this work? And is there any risk to do so?

 

Thanks!

 

Best reguards,

 

甘嘉栋(Gan Jiadong)

E-MAIL: ga...@huawei.com

Tel：+86-755-289720578


*

This e-mail and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure, reproduction,
or dissemination) by persons other than the intended recipient(s) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!


* 

 

[HACKERS] Re: [COMMITTERS] pgsql: Hot Standby feedback for avoidance of cleanup conflicts on stand

[Fujii Masao]

On Thu, Feb 17, 2011 at 4:29 PM, Simon Riggs  wrote:
>> Something like the following description should be in the doc.
>>
>>      hot_standby_feedback has no effect if either hot_standby is off or
>>      wal_receiver_status_interval is zero.
>
> The docs are going to need some work after 3-4 related major changes hit
> them. I'm not picking up on individual sentences right now.

OK.

>> +     if (MyProc->xmin != newxmin)
>> +     {
>> +             LWLockAcquire(ProcArrayLock, LW_SHARED);
>> +             MyProc->xmin = newxmin;
>> +             LWLockRelease(ProcArrayLock);
>>
>> ProcArrayLock should be taken with LW_EXCLUSIVE since the shared
>> variable is changed. No?
>
> No, shared is sufficient for setting xmin, as we do in
> GetSnapshotData().

Hmm.. it's because setting uint32 variable (i.e., xmin) is an atomic operation?
I'd like to know why LW_SHARED is sufficient in that case, just for future
reference.

>> What about exposing the feedback xid and epoch in pg_stat_replication?
>> It's useful when we investigate which standby unexpectedly prevents
>> VACUUM on the primary.
>
> This begs the questions "what is the xmin of all the normal backends?"
> and "Whats is the xmin of prepared transactions?" as well. I wasn't sure
> that we should expose that information for walsenders when we don't do
> it for everybody else. If we do it would require major sections in the
> docs explaining it all, etc..

We can *presume* which backend (or prepared transaction) unexpectedly
prevents VACUUM by seeing pg_stat_activity (or pg_prepared_xacts) and
checking whether there is long-running transaction. But there is no way to
presume which standby does that, I'm concerned.

>> It seems too aggressive to calculate the oldest xmin and return it for
>> each WAL write and flush on the standby. I think this because calculation
>> of the oldest xmin is not light operation especially when there are many
>> concurrent backends. How about feeding back the xmin only when the
>> interval has passed?
>
> You may be correct. Some rearrangement following performance tuning is
> likely, though I've tried not to pre-guess the tuning.

Are you planning to do that in beta phase or another?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
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] contrib loose ends: 9.0 to 9.1 incompatibilities

[Tom Lane]

I wrote:
> Robert Haas  writes:
>> I think we should try to make the state match as closely as possible,
>> no matter how you got there.  Otherwise, I think we're storing up a
>> host of future pain for ourselves.

> Well, if you're willing to hold your nose for the "UPDATE pg_proc" hack,
> we can make it so.

I believe I've now fixed all the discrepancies between fresh installs
and 9.0 updates of contrib modules, except for these:

1. citext COLLATABLE option (see adjacent thread)

2. intarray and tsearch2 use some core support functions in their
GIN opclasses, and those support functions changed signatures in 9.1.
The current solution to this involves having stub functions in core
with the old signatures; when you do an upgrade from the 9.0 version
of one of these contrib modules, its opclass will be pointing at the
stub version instead of the preferred version.  I guess we could fix
that with a direct UPDATE on pg_amproc but I'm not sure that's a
good idea.  Note these functions aren't actually *members* of the
extensions, just things it references, so the odds of future trouble
seem pretty small.  On the other hand, if we don't do this, it's
unclear when we'll ever be able to get rid of the stubs.

Comments?

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] arrays as pl/perl input arguments [PATCH]

[Alvaro Herrera]

Excerpts from Alvaro Herrera's message of mié feb 16 19:54:07 -0300 2011:
> 
> I cleaned up the patch a bit -- result is v11, attached.  I'll give it
> another look tomorrow and hopefully commit it.

Applied.  Thanks.

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

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

Re: [HACKERS] Replication server timeout patch

[Fujii Masao]

On Fri, Feb 18, 2011 at 7:55 AM, Josh Berkus  wrote:
>> So, in summary, the position is that we have a timeout, but that timeout
>> doesn't work in all cases. But it does work in some, so that seems
>> enough for me to say "let's commit". Not committing gives us nothing at
>> all, which is as much use as a chocolate teapot.
>
> Can someone summarize the cases where it does and doesn't work?
> There's been a longish gap in this thread.

The timeout doesn't work when walsender gets blocked during sending the
WAL because the send buffer has been filled up, I'm afraid. IOW, it doesn't
work when the standby becomes unresponsive while WAL is generated on
the master one after another. Since walsender tries to continue sending the
WAL while the standby is unresponsive, the send buffer gets filled up and
the blocking send function (e.g., pq_flush) blocks the walsender.

OTOH, if the standby becomes unresponsive when there is no workload
which causes WAL, the timeout would work.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
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] Debian readline/libedit breakage

[Charles.McDevitt]

> On 02/17/2011 12:34 PM, Bruce Momjian wrote:
> > Andrew Dunstan wrote:
> >>
> >> On 02/17/2011 12:13 PM, Bruce Momjian wrote:
>  FWIW, the only interactively usable version of psql for windows I know
>  of is the one that runs under Cygwin. It can be build with readline and
>  works as expected.
> >>> Uh, don't we have a psql built via MSVC?  Doesn't it work interactively?
> >>
> >> Not if you want readline. And in my book that's a requirement of a psql
> >> that's usable interactively. It's pretty horrible to use without it.
> > Well, as horrible as other Windows apps.  I will leave others to decide
> > if that is usable.  ;-)  I am unclear if we would ship readline support
> > on Windows even if we didn't have a license issue (no OS readline
> > version on Windows).
> >
> 
> Windows developers almost universally work from GUIs and not using
> console apps (and that's true of many Unix developers also, particularly
> those who can't recall a time when X-Windows wasn't almost universally
> available). Microsoft has de-emphasized console apps for 15 years. So
> the only people who are likely to be interested in using an enhanced
> psql on Windows are old Unix-heads like you and me. It's not worth a lot
> of effort, IMNSHO.
> 
> cheers
> 
> andrew
> 

I think this is wrong...  There are plenty of people who use the command line 
in Windows, and Microsoft has been adding better support for this, including 
PowerShell and interfaces to every administrative operation from command line 
scripts.

Psql on Windows is ugly... Readline does (supposedly) run on Windows, but no 
one has done the work to get it to happen.
One of the problems with libedit is that it does not support Windows, as far as 
I know.

I don't know of a good solution, given the license for readline.  But it would 
sure be nice.


-- 
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] Sync Rep for 2011CF1

[Simon Riggs]

On Fri, 2011-01-21 at 14:45 +0200, Heikki Linnakangas wrote:

> * The UI differs from what was agreed on here: 
> http://archives.postgresql.org/message-id/4d1dcf5a.7070...@enterprisedb.com.

Patch to add server_name parameter, plus mechanism to send info from
standby to master. While doing that, refactor into 3 message types, not
just 1. This addresses Fujii's comment that we may not wish to send
feedback as often as other replies, but doesn't actually alter yet when
the feedback is sent (nor will I do that anytime soon).

Complete but rough hack, for comments, but nothing surprising.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services
 
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index ee09468..ff89035 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -95,6 +95,7 @@ static struct
 }	LogstreamResult;
 
 static StandbyReplyMessage	reply_message;
+static StandbyHSFeedbackMessage	feedback_message;
 
 /*
  * About SIGTERM handling:
@@ -123,6 +124,8 @@ static void XLogWalRcvProcessMsg(unsigned char type, char *buf, Size len);
 static void XLogWalRcvWrite(char *buf, Size nbytes, XLogRecPtr recptr);
 static void XLogWalRcvFlush(bool dying);
 static void XLogWalRcvSendReply(void);
+static void XLogWalRcvSendHSFeedback(void);
+static void XLogWalRcvSendInfo(void);
 
 /* Signal handlers */
 static void WalRcvSigHupHandler(SIGNAL_ARGS);
@@ -303,6 +306,7 @@ WalReceiverMain(void)
 		{
 			got_SIGHUP = false;
 			ProcessConfigFile(PGC_SIGHUP);
+			XLogWalRcvSendInfo();
 		}
 
 		/* Wait a while for data to arrive */
@@ -317,6 +321,7 @@ WalReceiverMain(void)
 
 			/* Let the master know that we received some data. */
 			XLogWalRcvSendReply();
+			XLogWalRcvSendHSFeedback();
 
 			/*
 			 * If we've written some records, flush them to disk and let the
@@ -331,6 +336,7 @@ WalReceiverMain(void)
 			 * the master anyway, to report any progress in applying WAL.
 			 */
 			XLogWalRcvSendReply();
+			XLogWalRcvSendHSFeedback();
 		}
 	}
 }
@@ -619,40 +625,84 @@ XLogWalRcvSendReply(void)
 	reply_message.apply = GetXLogReplayRecPtr();
 	reply_message.sendTime = now;
 
+	elog(DEBUG2, "sending write %X/%X flush %X/%X apply %X/%X",
+ reply_message.write.xlogid, reply_message.write.xrecoff,
+ reply_message.flush.xlogid, reply_message.flush.xrecoff,
+ reply_message.apply.xlogid, reply_message.apply.xrecoff);
+
+	/* Prepend with the message type and send it. */
+	buf[0] = 'r';
+	memcpy(&buf[1], &reply_message, sizeof(StandbyReplyMessage));
+	walrcv_send(buf, sizeof(StandbyReplyMessage) + 1);
+}
+
+/*
+ * Send hot standby feedback message to primary, plus the current time,
+ * in case they don't have a watch.
+ */
+static void
+XLogWalRcvSendHSFeedback(void)
+{
+	char		buf[sizeof(StandbyHSFeedbackMessage) + 1];
+	TimestampTz	now;
+
+	/*
+	 * If the user doesn't want status to be reported to the master, be sure
+	 * to exit before doing anything at all.
+	 */
+	if (!hot_standby_feedback || !HotStandbyActive())
+		return;
+
+	/* Get current timestamp. */
+	now = GetCurrentTimestamp();
+
 	/*
 	 * Get the OldestXmin and its associated epoch
 	 */
-	if (hot_standby_feedback && HotStandbyActive())
 	{
 		TransactionId	nextXid;
 		uint32			nextEpoch;
 
-		reply_message.xmin = GetOldestXmin(true, false);
+		feedback_message.xmin = GetOldestXmin(true, false);
 
 		/*
 		 * Get epoch and adjust if nextXid and oldestXmin are different
 		 * sides of the epoch boundary.
 		 */
 		GetNextXidAndEpoch(&nextXid, &nextEpoch);
-		if (nextXid < reply_message.xmin)
+		if (nextXid < feedback_message.xmin)
 			nextEpoch--;
-		reply_message.epoch = nextEpoch;
-	}
-	else
-	{
-		reply_message.xmin = InvalidTransactionId;
-		reply_message.epoch = 0;
+		feedback_message.epoch = nextEpoch;
 	}
 
-	elog(DEBUG2, "sending write %X/%X flush %X/%X apply %X/%X xmin %u epoch %u",
- reply_message.write.xlogid, reply_message.write.xrecoff,
- reply_message.flush.xlogid, reply_message.flush.xrecoff,
- reply_message.apply.xlogid, reply_message.apply.xrecoff,
- reply_message.xmin,
- reply_message.epoch);
+	elog(DEBUG2, "sending xmin %u epoch %u",
+ feedback_message.xmin,
+ feedback_message.epoch);
 
 	/* Prepend with the message type and send it. */
-	buf[0] = 'r';
-	memcpy(&buf[1], &reply_message, sizeof(StandbyReplyMessage));
-	walrcv_send(buf, sizeof(StandbyReplyMessage) + 1);
+	buf[0] = 'h';
+	memcpy(&buf[1], &feedback_message, sizeof(StandbyHSFeedbackMessage));
+	walrcv_send(buf, sizeof(StandbyHSFeedbackMessage) + 1);
+}
+
+/*
+ * Send info message to primary.
+ */
+static void
+XLogWalRcvSendInfo(void)
+{
+	char		buf[sizeof(StandbyInfoMessage) + 1];
+	StandbyInfoMessage	info_message;
+
+	/* Get current timestamp. */
+	info_message.sendTime = GetCurrentTimestamp();
+	strncpy(info_message.servername, ServerName, strlen(ServerName));
+
+	elog(DEBUG2, "sending serverna

Re: [HACKERS] arrays as pl/perl input arguments [PATCH]

[Alex Hunsaker]

On Thu, Feb 17, 2011 at 16:18, Alvaro Herrera
 wrote:
> Excerpts from Alex Hunsaker's message of sáb feb 12 04:53:14 -0300 2011:
>
>> - make plperl.o depend on plperl_helpers.h (should have been done in
>> the utf8 patch)
>
> Incidentally, I think this bit was lost, no?

It was, yes.

-- 
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] Estimates not taking null_frac element into account with @@ operator? (8.4 .. git-head)

[Tom Lane]

Jesper Krogh  writes:
> Attached patch tries to align the behaviour

Applied with a bit of editorialization.

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] Estimates not taking null_frac element into account with @@ operator? (8.4 .. git-head)

[Tom Lane]

Jesper Krogh  writes:
> On 2011-02-17 23:20, Tom Lane wrote:
>> The proposed patch seems wrong to me: if we're estimating on the basis
>> of most-common-value fractions, the null_frac is already accounted for,
>> because it's not part of the MCV selectivity fractions.  IOW, aren't you
>> double-counting the null fraction?

> It might be the wrong place to fix, but here it seems like we're only
> counting MCE-freqs based on non-null elements:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/tsearch/ts_typanalyze.c;h=2654d644579fd1959282d83919474f42540ca703;hb=HEAD#l396

Hmm, you're right, and the specification in pg_statistic.h neglects to
say that.  This does need work.

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] Add support for logging the current role

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Robert Haas  writes:
> > It seems there's at least one more thing to worry about here, which is
> > the overhead of this computation when CSV logging is in use.  If no
> > SET ROLE or SET SESSION AUTHORIZATION commands are in use, the code
> > will call show_role(), which will return "none".  We'll then strcmp()
> > that against "none" and decide to call show_session_authorization(),
> > which will call strtoul() to find the comma separator and then return
> > a pointer to the string that follows it.  Now, none of that is
> > enormously expensive, so maybe it's not worth worrying about, but
> > since logging can be a hotspot, I thought I'd mention it and solicit
> > an opinion on whether that's likely to be a problem in practice.
> 
> Well, in the first place, going through two not-very-related APIs in
> order to reverse-engineer what miscinit.c already knows is pretty silly
> (not to mention full of possible bugs).  We ought to be looking at the
> GetUserId state directly.

GetUserId can end up being set in a number of places though, often in
places where we can't fail (SetUserIdAndSecContext has some nice
comments on this).

> Now you will complain that elog.c mustn't try to map that OID back to
> string form, which is true.  But IIRC, we used to keep the current
> userid stored in both OID and string form.  The string form was removed
> as unnecessary overhead, but maybe it'd be a good idea to put that back.

The OID and the string are kept in the role_string and
session_authorization_string GUCs respectively.  They're just not in a
terribly useful format, and because SetUserId() can change things w/o
the GUCs getting updated, there's a risk that they're wrong, which is
why show_role() does the stroul() dance to check if GetCurrentRoleId()
matches to what it stuffed into role_string.

> In short, add a bit of overhead at SetUserId time in order to make this
> cheap (and accurate) in elog.c.

We can't do the lookup in SetUserIDAndSecContext(), and I'm not
convinced we actually want to anyway, since that would end up returning
what the role is inside of security definer functions and the like.
We're already setting a variable in assign_session_authorization and
assign_role that has the information we need.  We could inspect
role_string ourselves (including the strcmp() and strtoul()) instead
of asking show_role() to do it for us but that doesn't strike me as all
*that* much of an improvement and goes around the API that at least
exists.

We could certainly have a second set of variables which are set by
assign_role/assign_session_authorization that are in a format we can
more easily use but what would that mean for the GUC variables..?  I
don't know that we'd want to keep them duplicating the data..  Would it
be possible to actually use a struct instead of a straight-up string
there?  Is there any particular reason we keep monkeying around with
storing the OID, superuser bit, role name, etc, as a string anyway..?

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] arrays as pl/perl input arguments [PATCH]

[Alvaro Herrera]

Excerpts from Alex Hunsaker's message of sáb feb 12 04:53:14 -0300 2011:

> - make plperl.o depend on plperl_helpers.h (should have been done in
> the utf8 patch)

Incidentally, I think this bit was lost, no?

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

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

Re: [HACKERS] ALTER TYPE COLLATABLE?

[David E. Wheeler]

On Feb 17, 2011, at 2:50 PM, Tom Lane wrote:

> I observe the following discrepancy between the 9.0 and 9.1 citext
> install scripts:
> 
> ***
> *** 52,58 
>  STORAGE= extended,
>  -- make it a non-preferred member of string type category
>  CATEGORY   = 'S',
> ! PREFERRED  = false
>  );
> 
>  --
> --- 49,56 
>  STORAGE= extended,
>  -- make it a non-preferred member of string type category
>  CATEGORY   = 'S',
> ! PREFERRED  = false,
> ! COLLATABLE = true
>  );

Oh, interesting. Would be nice if the docs has a link to the COLLATE clause 
docs.

  http://developer.postgresql.org/pgdocs/postgres/sql-createtype.html

So I can see how to use it. :-)

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] Replication server timeout patch

[Simon Riggs]

On Thu, 2011-02-17 at 16:42 -0500, Robert Haas wrote:
> >
> > So, in summary, the position is that we have a timeout, but that timeout
> > doesn't work in all cases. But it does work in some, so that seems
> > enough for me to say "let's commit". Not committing gives us nothing at
> > all, which is as much use as a chocolate teapot.
> >
> > I will be looking to commit this tomorrow morning, unless I hear some
> > clear No comments, with reasons.
> 
> I guess the question is whether it works in 10% of cases or 95% of
> cases.  In the first case there's probably no point in pretending we
> have a feature if it doesn't really work.  In the second case, it
> might make sense.  But I don't have a good feeling for which it is.

Well, I guess the people that wanted to wait forever may get their wish.

For sync rep, I intend to put in place a client timeout, which we do
have code for. The server side timeout still makes sense, but it's not a
requirement for sync rep.

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


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

Re: [HACKERS] Replication server timeout patch

[Josh Berkus]

> So, in summary, the position is that we have a timeout, but that timeout
> doesn't work in all cases. But it does work in some, so that seems
> enough for me to say "let's commit". Not committing gives us nothing at
> all, which is as much use as a chocolate teapot.

Can someone summarize the cases where it does and doesn't work?
There's been a longish gap in this thread.

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

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

[HACKERS] ALTER TYPE COLLATABLE?

[Tom Lane]

I observe the following discrepancy between the 9.0 and 9.1 citext
install scripts:

***
*** 52,58 
  STORAGE= extended,
  -- make it a non-preferred member of string type category
  CATEGORY   = 'S',
! PREFERRED  = false
  );
  
  --
--- 49,56 
  STORAGE= extended,
  -- make it a non-preferred member of string type category
  CATEGORY   = 'S',
! PREFERRED  = false,
! COLLATABLE = true
  );
  
  --

What are we going to do to allow the citext update script to fix this?
I see no sign that ALTER TYPE can fix it (and am unsure that we'd want
to add such a feature, particularly not right now).  Is it time for
a direct UPDATE on the pg_type row?  If so, to what?  I see
pg_type.typcollation is supposed to be an OID, so how the heck does
one map a bool CREATE TYPE parameter into the catalog entry?

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] Estimates not taking null_frac element into account with @@ operator? (8.4 .. git-head)

[Jesper Krogh]

On 2011-02-17 23:20, Tom Lane wrote:

Jesper Krogh  writes:

When something evaluates to "null" isn't included in the result,
shouldn't the query-planner
then take the null_frac into account when computing the estimate?

The proposed patch seems wrong to me: if we're estimating on the basis
of most-common-value fractions, the null_frac is already accounted for,
because it's not part of the MCV selectivity fractions.  IOW, aren't you
double-counting the null fraction?

It might be the wrong place to fix, but here it seems like we're only
counting MCE-freqs based on non-null elements:

http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/tsearch/ts_typanalyze.c;h=2654d644579fd1959282d83919474f42540ca703;hb=HEAD#l396

And the testdata confirms the behaviour.

--
Jesper


--
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] Estimates not taking null_frac element into account with @@ operator? (8.4 .. git-head)

[Tom Lane]

Jesper Krogh  writes:
> When something evaluates to "null" isn't included in the result, 
> shouldn't the query-planner
> then take the null_frac into account when computing the estimate?

The proposed patch seems wrong to me: if we're estimating on the basis
of most-common-value fractions, the null_frac is already accounted for,
because it's not part of the MCV selectivity fractions.  IOW, aren't you
double-counting the null fraction?

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] SSI bug?

[Kevin Grittner]

Dan Ports  wrote:
 
> Oops. Those are both definitely bugs (and my fault). Your patch
> looks correct. Thanks for catching that!
 
Could a committer please apply the slightly modified version here?:
 
http://archives.postgresql.org/message-id/4d5c46bb02250003a...@gw.wicourts.gov
 
It is a pretty straightforward bug fix to initialize some currently
uninitialized data which is causing occasional but severe problems,
especially during vacuum.
 
I'm still working on the other issues raised by YAMAMOTO Takashi and
Heikki.  I expect to have a solution for those issues this weekend,
but this bug fix is needed regardless of how those issues are
settled.
 
-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] Add support for logging the current role

[Tom Lane]

Robert Haas  writes:
> It seems there's at least one more thing to worry about here, which is
> the overhead of this computation when CSV logging is in use.  If no
> SET ROLE or SET SESSION AUTHORIZATION commands are in use, the code
> will call show_role(), which will return "none".  We'll then strcmp()
> that against "none" and decide to call show_session_authorization(),
> which will call strtoul() to find the comma separator and then return
> a pointer to the string that follows it.  Now, none of that is
> enormously expensive, so maybe it's not worth worrying about, but
> since logging can be a hotspot, I thought I'd mention it and solicit
> an opinion on whether that's likely to be a problem in practice.

Well, in the first place, going through two not-very-related APIs in
order to reverse-engineer what miscinit.c already knows is pretty silly
(not to mention full of possible bugs).  We ought to be looking at the
GetUserId state directly.

Now you will complain that elog.c mustn't try to map that OID back to
string form, which is true.  But IIRC, we used to keep the current
userid stored in both OID and string form.  The string form was removed
as unnecessary overhead, but maybe it'd be a good idea to put that back.

In short, add a bit of overhead at SetUserId time in order to make this
cheap (and accurate) in elog.c.

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] btree_gist (was: CommitFest progress - or lack thereof)

[Tom Lane]

I wrote:
> In any case, I was pointing to that as a reason that btree_gist wasn't
> ready to be in core.  It has nothing to do with KNN-ifying the module.
> I would like to see that happen before 9.1, else KNN will go out with
> not very many actual use-cases supported.

However, a larger reason for not applying that patch in a rush is that
it's almost certainly not up to speed for the recent extensions-related
changes.  Please note in particular that we are now expecting the
update-from-unpackaged script to produce the same catalog state as the
install script.  I just committed fixes to btree_gist's scripts to make
that actually true.  Any new additions to the opclasses will need to be
done in the same style.

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] Replication server timeout patch

[Robert Haas]

On Thu, Feb 17, 2011 at 4:21 PM, Simon Riggs  wrote:
> On Wed, 2011-02-16 at 11:34 +0900, Fujii Masao wrote:
>> On Tue, Feb 15, 2011 at 7:13 AM, Daniel Farina  wrote:
>> > On Mon, Feb 14, 2011 at 12:48 AM, Fujii Masao  
>> > wrote:
>> >> On Sat, Feb 12, 2011 at 8:58 AM, Daniel Farina  wrote:
>> >>> Context diff equivalent attached.
>> >>
>> >> Thanks for the patch!
>> >>
>> >> As I said before, the timeout which this patch provides doesn't work well
>> >> when the walsender gets blocked in sending WAL. At first, we would
>> >> need to implement a non-blocking write function as an infrastructure
>> >> of the replication timeout, I think.
>> >> http://archives.postgresql.org/message-id/AANLkTi%3DPu2ne%3DVO-%2BCLMXLQh9y85qumLCbBP15CjnyUS%40mail.gmail.com
>> >
>> > Interesting point...if that's accepted as required-for-commit, what
>> > are the perceptions of the odds that, presuming I can write the code
>> > quickly enough, that there's enough infrastructure/ports already in
>> > postgres to allow for a non-blocking write on all our supported
>> > platforms?
>>
>> I'm not sure if there's already enough infrastructure for a non-blocking
>> write. But the patch which I submitted before might help to implement that.
>> http://archives.postgresql.org/message-id/AANLkTinSvcdAYryNfZqd0wepyh1Pf7YX6Q0KxhZjas6a%40mail.gmail.com
>
> So, in summary, the position is that we have a timeout, but that timeout
> doesn't work in all cases. But it does work in some, so that seems
> enough for me to say "let's commit". Not committing gives us nothing at
> all, which is as much use as a chocolate teapot.
>
> I will be looking to commit this tomorrow morning, unless I hear some
> clear No comments, with reasons.

I guess the question is whether it works in 10% of cases or 95% of
cases.  In the first case there's probably no point in pretending we
have a feature if it doesn't really work.  In the second case, it
might make sense.  But I don't have a good feeling for which it is.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] Estimates not taking null_frac element into account with @@ operator? (8.4 .. git-head)

[Jesper Krogh]

Hi All.

The NULL element always suprises me in unpleasant ways..  my brain simply
cant really understand the logic, so please let me know if this is
one of the cases where I just should spend way more efforts into fixing 
that instead.


I have a table with a "null_frac" of 0.5 and i have tested that a where
clause that evaluates to null isnt included in the result:

testdb=# select id from testtable where  null @@ to_tsquery('testterm80');
 id

(0 rows)

Then I'd expect to have the null_fraq taken into account when computing the
estimates for the query:

testdb=# explain select id from testtable where fts @@ 
to_tsquery('testterm80');

  QUERY PLAN
---
 Seq Scan on testtable  (cost=0.00..1985.03 rows=1966 width=4)
   Filter: (fts @@ to_tsquery('testterm80'::text))
(2 rows)

Whereas it actually does it if I explicitly add the "fts is not null" 
clause to the query.


testdb=# explain select id from testtable where fts @@ 
to_tsquery('testterm80') and fts is not null;

 QUERY PLAN
-
 Bitmap Heap Scan on testtable  (cost=130.34..1735.19 rows=983 width=4)
   Recheck Cond: ((fts @@ to_tsquery('testterm80'::text)) AND (fts IS 
NOT NULL))
   ->  Bitmap Index Scan on testtable_fts_idx  (cost=0.00..130.09 
rows=983 width=0)
 Index Cond: ((fts @@ to_tsquery('testterm80'::text)) AND (fts 
IS NOT NULL))

(4 rows)

When something evaluates to "null" isn't included in the result, 
shouldn't the query-planner

then take the null_frac into account when computing the estimate?

Trying to do the same thing using integers and the < operator seem to 
take the null_frac into

account.

Below snippet allows to reproduce the dataset.


create table testtable (id serial primary key, document text, fts tsvector);
create index on testtable using gist(fts);
CREATE OR REPLACE FUNCTION public.filltable(rows integer)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
DECLARE
count integer;
BEGIN
count := 0;
LOOP
EXIT WHEN count = rows;
count := count +1;
insert into testtable(document,fts) select 
document,to_tsvector('english',document) from (select 
string_agg(concat,' ') as document from (select concat('testterm' || 
generate_series(1,floor(random()*100)::integer))) as foo) as bar;

END LOOP;
RETURN TRUE;
END;
$function$

select filltable(1);
testdb=# update testtable set fts = null where id % 2 = 0;
UPDATE 5001
testdb=# ANALYZE verbose testtable;
INFO:  analyzing "public.testtable"
INFO:  "testtable": scanned 1835 of 1835 pages, containing 10002 live 
rows and 5001 dead rows; 10002 rows in sample, 10002 estimated total rows

ANALYZE
testdb=# select null_frac from pg_stats where attname = 'fts';
 null_frac
---
   0.5
(1 row)

... trying with integers:

testdb=# ALTER  TABLE testtable add column testint integer;
ALTER TABLE
testdb=# update testtable set testint = floor(random()*100);
UPDATE 10002
testdb=# ANALYZE verbose testtable;
INFO:  analyzing "public.testtable"
INFO:  "testtable": scanned 2186 of 2186 pages, containing 10002 live 
rows and 10002 dead rows; 10002 rows in sample, 10002 estimated total rows

ANALYZE
testdb=# update testtable set testint = null where id %2 = 0;
UPDATE 5001
testdb=# ANALYZE verbose testtable;
INFO:  analyzing "public.testtable"
INFO:  "testtable": scanned 2282 of 2282 pages, containing 10002 live 
rows and 13335 dead rows; 10002 rows in sample, 10002 estimated total rows

analyzze ANALYZE
testdb=# explain select id from testtable where testint = 50;
 QUERY PLAN
-
 Seq Scan on testtable  (cost=0.00..2407.03 rows=64 width=4)
   Filter: (testint = 50)
(2 rows)

testdb=# explain select id from testtable where testint = 1;
 QUERY PLAN
-
 Seq Scan on testtable  (cost=0.00..2407.03 rows=48 width=4)
   Filter: (testint = 1)
(2 rows)

testdb=# explain select id from testtable where testint < 50;
  QUERY PLAN
---
 Seq Scan on testtable  (cost=0.00..2407.03 rows=2470 width=4)
   Filter: (testint < 50)
(2 rows)


(found on 8.4 and reproduced on git-head)

Attached patch tries to align the behaviour

Thanks.

--
Jesper
>From 4b4be27864f683a9b3464b86d6cfa567f8ab6bd2 Mon Sep 17 00:00:00 2001
From: Jesper Krogh 
Date: Thu, 17 Feb 2011 22:21:52 +0100
Subject: [PATCH] Take null_frac into account in calculating selectivity for @@ operator. This makes behaviour align with what > operator does for integers.

---
 src/backend/tsearch/ts_selfuncs.c |4 ++--
 1 files changed, 2 insertions(+), 2 deletions(-)

diff --git a/src/backend/tsearch/ts_selfuncs.c b/s

Re: [HACKERS] Replication server timeout patch

[Simon Riggs]

On Wed, 2011-02-16 at 11:34 +0900, Fujii Masao wrote:
> On Tue, Feb 15, 2011 at 7:13 AM, Daniel Farina  wrote:
> > On Mon, Feb 14, 2011 at 12:48 AM, Fujii Masao  wrote:
> >> On Sat, Feb 12, 2011 at 8:58 AM, Daniel Farina  wrote:
> >>> Context diff equivalent attached.
> >>
> >> Thanks for the patch!
> >>
> >> As I said before, the timeout which this patch provides doesn't work well
> >> when the walsender gets blocked in sending WAL. At first, we would
> >> need to implement a non-blocking write function as an infrastructure
> >> of the replication timeout, I think.
> >> http://archives.postgresql.org/message-id/AANLkTi%3DPu2ne%3DVO-%2BCLMXLQh9y85qumLCbBP15CjnyUS%40mail.gmail.com
> >
> > Interesting point...if that's accepted as required-for-commit, what
> > are the perceptions of the odds that, presuming I can write the code
> > quickly enough, that there's enough infrastructure/ports already in
> > postgres to allow for a non-blocking write on all our supported
> > platforms?
> 
> I'm not sure if there's already enough infrastructure for a non-blocking
> write. But the patch which I submitted before might help to implement that.
> http://archives.postgresql.org/message-id/AANLkTinSvcdAYryNfZqd0wepyh1Pf7YX6Q0KxhZjas6a%40mail.gmail.com

So, in summary, the position is that we have a timeout, but that timeout
doesn't work in all cases. But it does work in some, so that seems
enough for me to say "let's commit". Not committing gives us nothing at
all, which is as much use as a chocolate teapot.

I will be looking to commit this tomorrow morning, unless I hear some
clear No comments, with reasons.

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


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

Re: [HACKERS] Debian readline/libedit breakage

[Martijn van Oosterhout]

On Wed, Feb 16, 2011 at 04:33:19PM -0800, Joshua D. Drake wrote:
> Maybe we really should consider moving to NSS insread?
> 
> http://www.mozilla.org/projects/security/pki/nss/
> 
> If it solves the license problem, it is well supported etc..

For the record, which library you choose only matters for a fairly
small (and easy) part of the patch. Changing libpq to be SSL library
agnostic is more work.

For the people who aren't following, the issue is there are libraries
out there that use libpq to setup the connection to the postgres server
(so handing all authentication, et al) and then stealing the FD and
implementing the rest of the protocol themselves.

This is supported. Where it goes wonky is that this also has to work
when the connection is via SSL. So libpq provides a function to return
(via a void*) a pointer to the OpenSSL structure so that can be used to
communicate with the server.

As you can imagine, unless the library you use is *binary* compatable
with OpenSSL, you're kinda stuck. The idea I suggested way back was to
introduce a passthrough mode which would hide all the connection
details within libpq, simplifying the code on both sides. Then after a
few releases you could remove the old code and change the SSL library
at leasure.

I guess the painless option however is no longer available.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first. 
>   - Charles de Gaulle


signature.asc
Description: Digital signature

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Robert Haas]

On Thu, Feb 17, 2011 at 3:47 PM, Simon Riggs  wrote:
> The only difference in messages I got was
>
> dbsize.c: In function ‘pg_relation_filepath’:
> dbsize.c:570: warning: ‘rnode.dbNode’ may be used uninitialized in this
> function
> dbsize.c:570: warning: ‘rnode.spcNode’ may be used uninitialized in this
> function

Well, at least these are easily fixed.  Done.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Coding style guide

[Robert Haas]

On Thu, Feb 17, 2011 at 3:55 PM, Bruce Momjian  wrote:
> Daniel Loureiro wrote:
>> Is there any official style guide of PostgreSQL code ? Like the
>> "google-styleguide"
>> (http://google-styleguide.googlecode.com/svn/trunk/cppguide.xml) ?
>
> There is the developers FAQ.

There's also http://www.postgresql.org/docs/current/static/source.html

That section could probably use some expansion though.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function

[Lukas Eder]

Hi Oliver

There are two problems.
>
> The first problem is a plpgsql problem in that particular function. It's
> broken regardless of how you call it. Here's how to fix it [...]
>

Thanks for insisting! I missed that fact. In the end, it looked like the
same error, but you're right about the plpgsql syntax error.


> The second problem is that the JDBC driver always generates calls in the
> "SELECT * FROM ..." form, but this does not work correctly for
> one-OUT-parameter-that-is-a-UDT, as seen in the example immediately
> above. Here's how to do the call for that particular case [...]
>

Knowing these things, I think I can live with the status quo in my case. As
I'm writing a database abstraction library (http://jooq.sourceforge.net),
with generated source code, I can hide these Postgres-specific details from
end-user code easily and assemble the UDT myself when reading the 6 return
values.


> Any questions? (I'm sure there will be questions. Sigh.)
>

Thanks again for the patience! :-)

Re: [HACKERS] Coding style guide

[Bruce Momjian]

Daniel Loureiro wrote:
> Is there any official style guide of PostgreSQL code ? Like the
> "google-styleguide"
> (http://google-styleguide.googlecode.com/svn/trunk/cppguide.xml) ?

There is the developers FAQ.

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Simon Riggs]

On Thu, 2011-02-17 at 10:09 -0500, Tom Lane wrote:
> Simon Riggs  writes:
> > On Thu, 2011-02-17 at 00:53 +, Tom Lane wrote:
> >> Doesn't anybody around here pay attention to compiler warnings?
> 
> > If you see one, then I accept one was there. I didn't see one, and a
> > full make distclean and re-compile doesn't show a compiler warning for
> > that either. So I guess I'm doing something wrong, on this platform:
> 
> > I'm using Ubuntu 10.04 LTS, with commands for development:
> > ./configure --enable-cassert --enable-depend --enable-debug
> > make -j4
> 
> Hmm ... the only plausible reason I can think of for gcc not showing
> that warning would be building with -O0 (which disables the flow graph
> computations needed to detect uses of uninitialized values).  Your
> configure command doesn't betray any such thing, but maybe you've got
> some CFLAGS overrides you're not showing us?
> 
> I usually find that -O1 is the best compromise setting for development
> builds.  It enables uninitialized-variable warnings but doesn't produce
> code that's completely unfriendly to gdb.  (Sometimes I do recompile a
> specific file at -O0 if it's making no sense during single-stepping.)

Just recompiled with explicit CFLAGS=-O1 using my distro's gcc 4.4.3

The only difference in messages I got was

dbsize.c: In function ‘pg_relation_filepath’:
dbsize.c:570: warning: ‘rnode.dbNode’ may be used uninitialized in this
function
dbsize.c:570: warning: ‘rnode.spcNode’ may be used uninitialized in this
function

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


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

Re: [HACKERS] Rewrite, normal execution vs. EXPLAIN ANALYZE

[David E. Wheeler]

On Feb 17, 2011, at 11:28 AM, Tom Lane wrote:

>> The status of that patch is that Tom promised to look at it two months
>> ago and hasn't.  It would be nice if someone else could pick it up.
> 
> I'm pedaling as fast as I can, honestly

Tom leaves everything on the road.

  http://en.wiktionary.org/wiki/leave_everything_on_the_road

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_tablespace

[Alvaro Herrera]

Excerpts from Florian Pflug's message of jue feb 17 16:55:55 -0300 2011:

> You can, however, do ALTER USER  SET default_tablespace=, 
> which will
> cause default_tablespace to be set automatically upon login for that user.
> 
> I don't know of the top of my head how we store that in the system catalogs, 
> though.
> You'll have to check the documentation to find that out...

It's in pg_db_role_settings.

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

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

Re: [HACKERS] Add support for logging the current role

[Josh Berkus]

Robert,

> It seems there's at least one more thing to worry about here, which is
> the overhead of this computation when CSV logging is in use.  If no
> SET ROLE or SET SESSION AUTHORIZATION commands are in use, the code
> will call show_role(), which will return "none".  We'll then strcmp()
> that against "none" and decide to call show_session_authorization(),
> which will call strtoul() to find the comma separator and then return
> a pointer to the string that follows it.  Now, none of that is
> enormously expensive, so maybe it's not worth worrying about, but
> since logging can be a hotspot, I thought I'd mention it and solicit
> an opinion on whether that's likely to be a problem in practice.

That seems like enough to need a performance test.  No clear ideas here
on how we'd measure the overhead of that accurately, though.  Suggestions?

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

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

Re: [HACKERS] default_tablespace

[David Kerr]

On Thu, Feb 17, 2011 at 08:55:55PM +0100, Florian Pflug wrote:
- On Feb17, 2011, at 18:32 , David Kerr wrote:
- > On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote:
- > - After reviewing docs and searching web
- > - cannot find out how to determine the default tablespace
- > - of a user?
- > 
- > It doesn't appear to me that default tablespaces are assigned to a user, 
they're 
- > assigned to a database.
- > 
- > A user can set the variable default_tablespace in their session to 
over-ride the 
- > database default, but that wouldn't be stored anywhere in the database 
(it's a client
- > variable). 
- 
- 
- You can, however, do ALTER USER  SET default_tablespace=, 
which will
- cause default_tablespace to be set automatically upon login for that user.
- 
- I don't know of the top of my head how we store that in the system catalogs, 
though.
- You'll have to check the documentation to find that out...
- 
- best regards,
- Florian Pflug 

oh, you're right. my mistake.

and it looks like it's stored in useconfig in pg_user.

select * from pg_user where usename = 'david.kerr';
  usename   | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | 
valuntil | useconfig 
+--+-+--+---+--+--+---
 david.kerr |16482 | f   | t| t |  |
  | {default_tablespace=test}


Dave

-- 
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] tsearch Parser Hacking

[Oleg Bartunov]

David,

as a cool perl guy you can easily take OpenFTS (openfts.sourceforge.net),
which provides perl interface to tsearch datatypes, and develop a
plperl version. That would be interesting for many people, who like flexibility
of perl. We personally use openfts in our web projects,i.e., we use tsearch as
a storage and we prepare tsvector externally. Openfts distribution contains
tests, examples of dictionaries, parser. Current interface of configuration
is ugly, but it should be not difficult to write table driven configuration.

What do you think ?

Oleg

On Wed, 16 Feb 2011, David E. Wheeler wrote:


On Feb 14, 2011, at 11:44 PM, Oleg Bartunov wrote:


IMO, sooner or later we need to trash that code and replace it with
something a bit more modification-friendly.


We thought about configurable parser, but AFAIR, we didn't get any support for 
this at that time.


What would it take to change the requirement such that *any* SQL function could 
be a parser, not only C functions? Maybe require that they turn a nested array 
of tokens? That way I could just write a function in PL/Perl quite easily.

Best,

David



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

[Florian Pflug]

On Feb17, 2011, at 18:32 , David Kerr wrote:
> On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote:
> - After reviewing docs and searching web
> - cannot find out how to determine the default tablespace
> - of a user?
> 
> It doesn't appear to me that default tablespaces are assigned to a user, 
> they're 
> assigned to a database.
> 
> A user can set the variable default_tablespace in their session to over-ride 
> the 
> database default, but that wouldn't be stored anywhere in the database (it's 
> a client
> variable). 


You can, however, do ALTER USER  SET default_tablespace=, which 
will
cause default_tablespace to be set automatically upon login for that user.

I don't know of the top of my head how we store that in the system catalogs, 
though.
You'll have to check the documentation to find that out...

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

[HACKERS] Coding style guide

[Daniel Loureiro]

Is there any official style guide of PostgreSQL code ? Like the
"google-styleguide"
(http://google-styleguide.googlecode.com/svn/trunk/cppguide.xml) ?

Regards,
--
Daniel Loureiro

-- 
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] Add support for logging the current role

[Robert Haas]

On Thu, Feb 17, 2011 at 11:45 AM, Stephen Frost  wrote:
> Robert, if you say this has to be punted to 9.2 again, I'm giving up. ;)

Frankly, this patch has already consumed more than its fair share of
my attention.  Having said that, I've just spent some more time on it.
 I tightened up both the code and the docs a bit.  I fixed
log_line_prefix so that it doesn't needlessly compute the value to be
used for %U when %U isn't used.  I fixed the CSV logging code to do
proper escaping.  Updated patch attached.

It seems there's at least one more thing to worry about here, which is
the overhead of this computation when CSV logging is in use.  If no
SET ROLE or SET SESSION AUTHORIZATION commands are in use, the code
will call show_role(), which will return "none".  We'll then strcmp()
that against "none" and decide to call show_session_authorization(),
which will call strtoul() to find the comma separator and then return
a pointer to the string that follows it.  Now, none of that is
enormously expensive, so maybe it's not worth worrying about, but
since logging can be a hotspot, I thought I'd mention it and solicit
an opinion on whether that's likely to be a problem in practice.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


logrole-rmh.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] SSI bug?

[Dan Ports]

On Wed, Feb 16, 2011 at 10:13:35PM +, YAMAMOTO Takashi wrote:
> i got the following SEGV when runnning vacuum on a table.
> (the line numbers in predicate.c is different as i have local modifications.)
> oldlocktag.myTarget was NULL.
> it seems that TransferPredicateLocksToNewTarget sometimes use stack garbage
> for newpredlocktag.myTarget.  vacuum on the table succeeded with the attached
> patch.  the latter part of the patch was necessary to avoid targetList
> corruption, which later seems to make DeleteChildTargetLocks loop inifinitely.

Oops. Those are both definitely bugs (and my fault). Your patch looks
correct. Thanks for catching that!

Dan

-- 
Dan R. K. Ports  MIT CSAILhttp://drkp.net/

-- 
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] Rewrite, normal execution vs. EXPLAIN ANALYZE

[Tom Lane]

Robert Haas  writes:
> The status of that patch is that Tom promised to look at it two months
> ago and hasn't.  It would be nice if someone else could pick it up.

I'm pedaling as fast as I can, honestly.

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] remove upsert example from docs

[Bruce Momjian]

Marko Tiikkaja wrote:
> On 2011-02-17 8:37 PM +0200, Bruce Momjian wrote:
> > Marko Tiikkaja wrote:
> >> The problem with the "safe" way is that it's not safe if called in a
> >> transaction with isolation level set to SERIALIZABLE.
> >
> > Good analysis.  Documentation patch attached and applied.
> 
> The "safe way" I was referring to above was the LOCK TABLE method, not 
> the one described in the documentation, so the remark about READ 
> COMMITTED in the patch should be removed.  The first part looks fine though.

OK, sentence removed.  Thanks.

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

  + It's impossible for everything to be true. +
commit 3472a2b0565ad0302e5ea47e49a95305c2b07f64
Author: Bruce Momjian 
Date:   Thu Feb 17 14:24:14 2011 -0500

Remove doc mention about read committed in upsert example.

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index d2e74dc..d0672bb 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2476,8 +2476,7 @@ SELECT merge_db(1, 'dennis');
 
  This example assumes the unique_violation error is caused by
  the INSERT, and not by an INSERT trigger function
- on the table.  Also, this example only works in the default Read
- Committed transaction mode.
+ on the table.
 
 
   

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Andrew Dunstan]

On 02/17/2011 12:54 PM, Kevin Grittner wrote:

Andrew Dunstan  wrote:


Ugh. Isn't there some sort of pragma or similar we can use to shut
it up?


If that fails, maybe use some function like the below?  That would
also have the advantage of not relying on assumptions beyond the
documented API, which I tend to feel good about no matter how sure I
am that the implementation details upon which I'm relying won't
change.

No claims that this is good final form, especially when it comes to
using ssize_t, but just trying to get across the general idea:

void
write_completely_ignore_errors(int filedes, const void *buffer,
size_t size)
{
 size_t t = 0;
 while (t<  size)
 {
 ssize_t n = write(filedes, buffer, size - t);
 if (n<= 0)
 break;
 t += n;
 }
}



In a very modern gcc, where we seem to be getting the errors from, maybe

   |#pragma GCC diagnostic push
   |#pragma GCC diagnostic ignored "-Wunused-result"
   write( ...);
   #pragma GCC diagnostic pop


would work. See 



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] COPY ENCODING revisited

[Hitoshi Harada]

2011/2/17 Itagaki Takahiro :
> Base on the latest patch,
>  http://archives.postgresql.org/pgsql-hackers/2011-01/msg02903.php
> I added pg_any_to_server() and pg_server_to_any() functions instead of
> exposing FmgrInfo in pg_wchar.h.  They are same as pg_client_to_server()
> and pg_server_to_client(), but accept any encoding. They use cached
> conversion procs only if the specified encoding matches the client encoding.

It sounds good to me since the approach doesn't make any overhead to
the current behavior, although additional ENCODING option usage gets a
bit slower. Nothing lost.

FWIW, I finally found the good example to cache miscellaneous data in
file local, namely regexp.c. It allocates compiled regular expressions
up to 32 by using malloc(). We need only 4 or so for encoding
conversion cache, in which cache search doesn't seem like overhead. I
don't have time to make it as patch, but in a few days I'll try it.

Regards,


-- 
Hitoshi Harada

-- 
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] contrib loose ends: 9.0 to 9.1 incompatibilities

[Robert Haas]

On Thu, Feb 17, 2011 at 1:53 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Feb 17, 2011 at 12:16 PM, Tom Lane  wrote:
>>> It's worth noting that both versions still leave the pg_trgm opclasses a
>>> bit different from a fresh install, because the added operators are
>>> "loose" in the opfamily rather than being bound into the opclass.  This
>>> hasn't got any real functional effect, but if you were feeling paranoid
>>> you could worry about whether the two different states could cause
>>> problems for future versions of the update script.  As far as I can see,
>>> the only thing we could realistically do about this with the tools at
>>> hand is to change pg_trgm's install script so that it also creates the
>>> new-in-9.1 entries "loose".  That seems a tad ugly, but depending on
>>> where you stand on the paranoia scale you might think it's a good idea.
>>> There is definitely no point in that refinement unless we update the
>>> function parameter lists, though.
>>>
>>> Comments?
>
>> I think we should try to make the state match as closely as possible,
>> no matter how you got there.  Otherwise, I think we're storing up a
>> host of future pain for ourselves.
>
> Well, if you're willing to hold your nose for the "UPDATE pg_proc" hack,
> we can make it so.

Yes, I think that's better than leaving things in a different state.
It's not my first choice, but it's better than the alternative.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] COPY ENCODING revisited

[Robert Haas]

On Wed, Feb 16, 2011 at 10:45 PM, Itagaki Takahiro
 wrote:
> COPY ENCODING patch was returned with feedback,
>  https://commitfest.postgresql.org/action/patch_view?id=501
> but we still need it for file_fdw.  Using client_encoding at runtime
> is reasonable for one-time COPY command, but logically nonsense for
> persistent file_fdw tables.
>
> Base on the latest patch,
>  http://archives.postgresql.org/pgsql-hackers/2011-01/msg02903.php
> I added pg_any_to_server() and pg_server_to_any() functions instead of
> exposing FmgrInfo in pg_wchar.h.  They are same as pg_client_to_server()
> and pg_server_to_client(), but accept any encoding. They use cached
> conversion procs only if the specified encoding matches the client encoding.
>
> According to Harada's research,
>  http://archives.postgresql.org/pgsql-hackers/2011-01/msg02397.php
> non-cached conversions are slower than cached ones. This version provides
> the same performance before when file and client encoding are same,
> but would be a bit slower on other cases. We could improve the performance
> in future versions, for example, caching each used conversion proc in
> pg_do_pg_do_encoding_conversion().
>
> file_fdw will support ENCODING option. Also, if not specified it might
> have to store the client_encoding at CREATE FOREIGN TABLE. Even if we use
> a different client_encoding at SELECT, the encoding at definition is used.
>
> ENCODING 'quoted name' issue is also fixed; it always requires quoted names.
> I think we only accept non-quoted text as identifier names. Unquoted text
> should be treated as "double quoted", but encoding names are not identifiers.

I am not qualified to fully review this patch because I'm not all that
familiar with the encoding stuff, but it looks reasonably sensible on
a quick read-through.  I am supportive of making a change in this area
even at this late date, because it seems to me that if we're not going
to change this then we're pretty much giving up on having a usable
file_fdw in 9.1.  And since postgresql_fdw isn't in very good shape
either, that would mean we may as well give up on SQL/MED.  We might
have to do that anyway, but I don't think we should do it just because
of this issue, if there's a reasonable fix.

I don't think the fact that the performance bites is a reason not to
do this.  As you say, that can always be improved in the future.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] contrib loose ends: 9.0 to 9.1 incompatibilities

[Tom Lane]

Robert Haas  writes:
> On Thu, Feb 17, 2011 at 12:16 PM, Tom Lane  wrote:
>> It's worth noting that both versions still leave the pg_trgm opclasses a
>> bit different from a fresh install, because the added operators are
>> "loose" in the opfamily rather than being bound into the opclass.  This
>> hasn't got any real functional effect, but if you were feeling paranoid
>> you could worry about whether the two different states could cause
>> problems for future versions of the update script.  As far as I can see,
>> the only thing we could realistically do about this with the tools at
>> hand is to change pg_trgm's install script so that it also creates the
>> new-in-9.1 entries "loose".  That seems a tad ugly, but depending on
>> where you stand on the paranoia scale you might think it's a good idea.
>> There is definitely no point in that refinement unless we update the
>> function parameter lists, though.
>> 
>> Comments?

> I think we should try to make the state match as closely as possible,
> no matter how you got there.  Otherwise, I think we're storing up a
> host of future pain for ourselves.

Well, if you're willing to hold your nose for the "UPDATE pg_proc" hack,
we can make it so.

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] remove upsert example from docs

[Marko Tiikkaja]

On 2011-02-17 8:37 PM +0200, Bruce Momjian wrote:

Marko Tiikkaja wrote:

The problem with the "safe" way is that it's not safe if called in a
transaction with isolation level set to SERIALIZABLE.


Good analysis.  Documentation patch attached and applied.


The "safe way" I was referring to above was the LOCK TABLE method, not 
the one described in the documentation, so the remark about READ 
COMMITTED in the patch should be removed.  The first part looks fine though.



Regards,
Marko Tiikkaja

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

Re: [HACKERS] contrib loose ends: 9.0 to 9.1 incompatibilities

[Robert Haas]

On Thu, Feb 17, 2011 at 12:16 PM, Tom Lane  wrote:
> So, after some testing, attached are two different fixed-up versions of
> pg_tgrm's update-from-unpackaged script.  The first one leaves the
> parameter lists of some GIN support functions different from what they
> would be if you installed pg_trgrm fresh in 9.1.  The second one fixes
> the parameter lists too, by means of really ugly direct UPDATEs on
> pg_proc.  I'm unsure which one to apply --- any opinions?
>
> It's worth noting that both versions still leave the pg_trgm opclasses a
> bit different from a fresh install, because the added operators are
> "loose" in the opfamily rather than being bound into the opclass.  This
> hasn't got any real functional effect, but if you were feeling paranoid
> you could worry about whether the two different states could cause
> problems for future versions of the update script.  As far as I can see,
> the only thing we could realistically do about this with the tools at
> hand is to change pg_trgm's install script so that it also creates the
> new-in-9.1 entries "loose".  That seems a tad ugly, but depending on
> where you stand on the paranoia scale you might think it's a good idea.
> There is definitely no point in that refinement unless we update the
> function parameter lists, though.
>
> Comments?

I think we should try to make the state match as closely as possible,
no matter how you got there.  Otherwise, I think we're storing up a
host of future pain for ourselves.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] remove upsert example from docs

[Bruce Momjian]

Marko Tiikkaja wrote:
> On 8/5/2010 9:44 PM, Merlin Moncure wrote:
> > On Thu, Aug 5, 2010 at 2:09 PM, Tom Lane  wrote:
> >> I was not persuaded that there's a real bug in practice.  IMO, his
> >> problem was a broken trigger not broken upsert logic.  Even if we
> >> conclude this is unsafe, simply removing the example is of no help to
> >> anyone.
> >
> > Well, the error handler is assuming that the unique_volation is coming
> > from the insert made within the loop.  This is obviously not a safe
> > assumption in an infinite loop context.  It should be double checking
> > where the error was being thrown from -- but the only way I can think
> > of to do that is to check sqlerrm.
> 
> Yeah, this is a known problem with our exception system.  If there was 
> an easy and reliable way of knowing where the exception came from, I'm 
> sure the example would include that.
> 
> > Or you arguing that if you're
> > doing this, all dependent triggers must not throw unique violations up
> > the exception chain?
> 
> If he isn't, I am.  I'm pretty sure you can break every example in the 
> docs with a trigger (or a rule) you haven't thought through.
> 
> >> A more useful response would be to supply a correct example.
> > Agree: I'd go further I would argue to supply both the 'safe' and
> > 'high concurrency (with caveat)' way.  I'm not saying the example is
> > necessarily bad, just that it's maybe not a good thing to be pointing
> > as a learning example without qualifications.  Then you get a lesson
> > both on upsert methods and defensive error handling (barring
> > objection, I'll provide that).
> 
> The problem with the "safe" way is that it's not safe if called in a 
> transaction with isolation level set to SERIALIZABLE.

Good analysis.  Documentation patch attached and applied.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c342916..d2e74dc 100644
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** BEGIN
*** 2464,2470 
  INSERT INTO db(a,b) VALUES (key, data);
  RETURN;
  EXCEPTION WHEN unique_violation THEN
! -- do nothing, and loop to try the UPDATE again
  END;
  END LOOP;
  END;
--- 2464,2470 
  INSERT INTO db(a,b) VALUES (key, data);
  RETURN;
  EXCEPTION WHEN unique_violation THEN
! -- Do nothing, and loop to try the UPDATE again.
  END;
  END LOOP;
  END;
*** LANGUAGE plpgsql;
*** 2474,2480 
  SELECT merge_db(1, 'david');
  SELECT merge_db(1, 'dennis');
  
! 
  
  

--- 2474,2483 
  SELECT merge_db(1, 'david');
  SELECT merge_db(1, 'dennis');
  
!  This example assumes the unique_violation error is caused by
!  the INSERT, and not by an INSERT trigger function
!  on the table.  Also, this example only works in the default Read
!  Committed transaction mode.
  
  


-- 
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] Rewrite, normal execution vs. EXPLAIN ANALYZE

[Bruce Momjian]

Robert Haas wrote:
> On Thu, Feb 17, 2011 at 1:04 PM, Bruce Momjian  wrote:
> > Tom Lane wrote:
> >> Robert Haas  writes:
> >> > On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane  wrote:
> >> >> I seriously doubt that there are many applications out there that are
> >> >> actually depending on this aspect of rule execution; if anything, there
> >> >> are probably more that will see it as a bug.
> >>
> >> > Changing EXPLAIN ANALYZE seems a bit less likely to break things for
> >> > anyone depending on current behavior;
> >>
> >> Well, the point I was trying to make is that there may well be fewer
> >> people depending on the current behavior than there are people for whom
> >> the current behavior is wrong, only they don't know it because they've
> >> not seen a failure (or not seen one often enough to diagnose what's
> >> happening).
> >>
> >> This is of course merest speculation either way. ?But I don't feel that
> >> we need to necessarily treat rule behavior as graven in stone.
> >
> > Where are we on this? ?It seems it is an issue independent of writable
> > common table expressions (wCTEs).
> 
> I believe that it's the same issue as this patch:
> 
> https://commitfest.postgresql.org/action/patch_view?id=377
> 
> The status of that patch is that Tom promised to look at it two months
> ago and hasn't.  It would be nice if someone else could pick it up.
> It's not good for our community to ignore patches that people have
> taken the trouble to write and submit.

Oh, at least it is in the current commit-fest and was not lost.

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

  + It's impossible for everything to be true. +

-- 
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] Debian readline/libedit breakage

[Andrew Dunstan]

On 02/17/2011 12:34 PM, Bruce Momjian wrote:

Andrew Dunstan wrote:


On 02/17/2011 12:13 PM, Bruce Momjian wrote:

FWIW, the only interactively usable version of psql for windows I know
of is the one that runs under Cygwin. It can be build with readline and
works as expected.

Uh, don't we have a psql built via MSVC?  Doesn't it work interactively?


Not if you want readline. And in my book that's a requirement of a psql
that's usable interactively. It's pretty horrible to use without it.

Well, as horrible as other Windows apps.  I will leave others to decide
if that is usable.  ;-)  I am unclear if we would ship readline support
on Windows even if we didn't have a license issue (no OS readline
version on Windows).



Windows developers almost universally work from GUIs and not using 
console apps (and that's true of many Unix developers also, particularly 
those who can't recall a time when X-Windows wasn't almost universally 
available). Microsoft has de-emphasized console apps for 15 years. So 
the only people who are likely to be interested in using an enhanced 
psql on Windows are old Unix-heads like you and me. It's not worth a lot 
of effort, IMNSHO.


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] Debian readline/libedit breakage

[Joshua D. Drake]

On Thu, 2011-02-17 at 10:49 +, Dave Page wrote:
> On Thu, Feb 17, 2011 at 10:36 AM, Magnus Hagander  wrote:

> >> Probably readline but does it matter? We distribute the source to the
> >> click installers.
> >
> > Actually, we don't. We used to, but we don't at this point.
> 
> Depends on your definition of "distribute" (and what part you are
> specifically referring to). There's no tarball, but the installer
> sources are on git.postgresql.org.

Right, and that qualifies.

JD

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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] Rewrite, normal execution vs. EXPLAIN ANALYZE

[Robert Haas]

On Thu, Feb 17, 2011 at 1:04 PM, Bruce Momjian  wrote:
> Tom Lane wrote:
>> Robert Haas  writes:
>> > On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane  wrote:
>> >> I seriously doubt that there are many applications out there that are
>> >> actually depending on this aspect of rule execution; if anything, there
>> >> are probably more that will see it as a bug.
>>
>> > Changing EXPLAIN ANALYZE seems a bit less likely to break things for
>> > anyone depending on current behavior;
>>
>> Well, the point I was trying to make is that there may well be fewer
>> people depending on the current behavior than there are people for whom
>> the current behavior is wrong, only they don't know it because they've
>> not seen a failure (or not seen one often enough to diagnose what's
>> happening).
>>
>> This is of course merest speculation either way.  But I don't feel that
>> we need to necessarily treat rule behavior as graven in stone.
>
> Where are we on this?  It seems it is an issue independent of writable
> common table expressions (wCTEs).

I believe that it's the same issue as this patch:

https://commitfest.postgresql.org/action/patch_view?id=377

The status of that patch is that Tom promised to look at it two months
ago and hasn't.  It would be nice if someone else could pick it up.
It's not good for our community to ignore patches that people have
taken the trouble to write and submit.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Rewrite, normal execution vs. EXPLAIN ANALYZE

[Bruce Momjian]

Tom Lane wrote:
> Robert Haas  writes:
> > On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane  wrote:
> >> I seriously doubt that there are many applications out there that are
> >> actually depending on this aspect of rule execution; if anything, there
> >> are probably more that will see it as a bug.
> 
> > Changing EXPLAIN ANALYZE seems a bit less likely to break things for
> > anyone depending on current behavior;
> 
> Well, the point I was trying to make is that there may well be fewer
> people depending on the current behavior than there are people for whom
> the current behavior is wrong, only they don't know it because they've
> not seen a failure (or not seen one often enough to diagnose what's
> happening).
> 
> This is of course merest speculation either way.  But I don't feel that
> we need to necessarily treat rule behavior as graven in stone.

Where are we on this?  It seems it is an issue independent of writable
common table expressions (wCTEs).

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Kevin Grittner]

Andrew Dunstan  wrote:
 
> Ugh. Isn't there some sort of pragma or similar we can use to shut
> it up?
 
If that fails, maybe use some function like the below?  That would
also have the advantage of not relying on assumptions beyond the
documented API, which I tend to feel good about no matter how sure I
am that the implementation details upon which I'm relying won't
change.
 
No claims that this is good final form, especially when it comes to
using ssize_t, but just trying to get across the general idea:
 
void
write_completely_ignore_errors(int filedes, const void *buffer,
   size_t size)
{
size_t t = 0;
while (t < size)
{
ssize_t n = write(filedes, buffer, size - t);
if (n <= 0)
break;
t += n;
}
}
 
-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] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname

[Bruce Momjian]

Boszormenyi Zoltan wrote:
> Kevin Grittner ?rta:
> > Michael Meskes  wrote:
> >   
> >> All prior ECPG versions were fine because dynamic cursor names
> >> were only added in 9.0.  Apparently only this one place was
> >> missed. So this is a bug in the new feature, however not such a
> >> major one that it warrants the complete removal IMO. I'd prefer to
> >> fix this in 9.0.1.
> >> 
> 
> As we are so late in the beta phase, we can live with that, hopefully
> you will find time by then to review the patch which is actually not
> that complex, only a bit large. The part of ECPGdo() that deals with
> auto-preparing statements is moved closer to calling ecpg_execute(),
> after the varargs are converted to stmt->inlist and ->outlist.
> 
> >> Hope this cleans it up a bit.
> >> 
> >  
> > Thanks.  I think I get it now.
> >  
> > To restate from another angle, to confirm my understanding: UPDATE
> > WHERE CURRENT OF is working for cursors with the name hard-coded in
> > the embedded statement, which is the only way cursor names were
> > allowed to be specified prior to 9.0; 9.0 implements dynamic cursor
> > names, allowing you to use a variable for the cursor name; but this
> > one use of a cursor name isn't allowing a variable yet.  Do I have
> > it right?  (If so, I now see why it would be considered a bug.)
> >   
> 
> Yes, you understand it correctly.

Did this ever get applied?  If so, I can't find it.

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] patch (for 9.1) string functions ( correct patch attached )

[Bruce Momjian]

Erik Rijkers wrote:
> On Thu, July 29, 2010 22:43, Erik Rijkers wrote:
> > Hi Pavel,
> >
> > In xfunc.sgml, I came across a function example (for use of VARIADIC in 
> > polymorphic functions),
> > where the function name is concat():  (in the manual: 35.4.10. Polymorphic 
> > SQL Functions).
> > Although that is not strictly wrong, it seems better to change that name 
> > when concat goes into
> > core, as seems to be the plan.
> >
> > If you agree, it seems best to include this change in your patch and change 
> > that example
> > function's name when the stringfunc patch gets applied.
> >
> 
> My apologies, the previous email had the wrong doc-patch attached.
> 
> Here is the correct one.

Applied for 9.1.  Thanks.

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

  + It's impossible for everything to be true. +

-- 
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] rewrite of RECENTLY DEAD tuples

[Noah Misch]

On Thu, Feb 17, 2011 at 09:38:51AM +0100, Benjamin S. wrote:
> ATRewriteTable in tablecmds.c uses SnapshotNow to rewrite and thus
> does not copy RECENTLY DEAD tuples. But copy_heap_data in cluster.c
> uses SnapshotAny and copys RECENTLY DEAD tuples to the new heap
> file.
> 
> I don't understand why it is not needed in the first case. In the
> second case I guess that there may be still a transaction with an
> older snapshot running which should be able to open the rewrited
> table and see the older (RECENTLY DEAD) tuple. Why must a
> transaction in the first case not be able to do so also?

Unlike VACUUM FULL and CLUSTER, ALTER TABLE rewrites are not MVCC-safe.  No
visibility-relevant information is preserved; all tuples get the xmin of the
ALTER TABLE transaction.  The table will look empty to snapshots predating the
commit of the ALTER TABLE transaction.  Compare TRUNCATE.

All other things being equal, it would be nice for ALTER TABLE rewrites to
become MVCC-safe.  The main implication is that tuples not visible to us can
cause the operation to fail.  Example:

CREATE TABLE t (x int);
CREATE DOMAIN int_notnull AS int NOT NULL;
INSERT INTO t VALUES (NULL);
-- acquire a snapshot in some other session here
DELETE FROM t;
ALTER TABLE t ALTER x TYPE int_notnull; -- error converting deleted tuple

If we also made the error message sufficiently informative, I'd wager this would
be a net improvement for the average user.

Thanks,
nm

-- 
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] Debian readline/libedit breakage

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> On 02/17/2011 12:13 PM, Bruce Momjian wrote:
> >
> >> FWIW, the only interactively usable version of psql for windows I know
> >> of is the one that runs under Cygwin. It can be build with readline and
> >> works as expected.
> > Uh, don't we have a psql built via MSVC?  Doesn't it work interactively?
> 
> 
> Not if you want readline. And in my book that's a requirement of a psql 
> that's usable interactively. It's pretty horrible to use without it.

Well, as horrible as other Windows apps.  I will leave others to decide
if that is usable.  ;-)  I am unclear if we would ship readline support
on Windows even if we didn't have a license issue (no OS readline
version on Windows).

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

  + It's impossible for everything to be true. +

-- 
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] Debian readline/libedit breakage

[Bruce Momjian]

Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (br...@momjian.us) wrote:
> > I just posted on this.  The risk is to people using the packages --- the
> > packages themselves include the source as an option, so they are fine,
> > but everyone using those packages would also be required to distribute
> > source, which is a restriction we have avoided in the past.
> 
> I think you may want to reread the GPL on this.  They're not actually
> required to distribute source *with* the binaries (hell, Debian
> doesn't..) as long as they are willing to produce it if asked.  And
> that's the source for the binaries which actually depend on the GPL'd
> code, eg, the community-built psql binary that you're talking about
> here..  They don't have to provide source for everything on the CD or
> something (again, Debian has a "non-free" section also..).

In summary, our click-through installers and hopefully other packaged
versions of Postgres don't distribute libreadline and rely on the
OS-supplied version, which is a FSF exception.  As I mentioned,
distributing the installer source doesn't exempt others from having to
GPL when they add to it (again, kind of rare because you can't link
something to psql, but anyway, it is mostly perception).

Debian distributes both Postgres and libreadline, so my guess is they
don't think they can use that exception and therefore have a problem.

And, again, libedit is not as hard as many of the problems we have
solved.  Since we have grown strong, I would love to see us reaching out
to those satellite communities and helping them solve some of their
problems, though, as people pointed out, in a way that does not take
energy away from Postgres development.

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

  + It's impossible for everything to be true. +

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

[David Kerr]

On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote:
- Hi Hackers,
- 
- After reviewing docs and searching web
- cannot find out how to determine the default tablespace
- of a user?
- 
- Like:
- 
- select spcname from blab where roloid = ;
- 
- Is this possible?
- 
- Thank you for your time.
- 

It doesn't appear to me that default tablespaces are assigned to a user, 
they're 
assigned to a database.

A user can set the variable default_tablespace in their session to over-ride 
the 
database default, but that wouldn't be stored anywhere in the database (it's a 
client
variable). 

you can find the OID for the default tablespace for a specific database in 
pg_database.

more info:
http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-DEFAULT-TABLESPACE

Dave

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Andrew Dunstan]

On 02/17/2011 12:19 PM, Tom Lane wrote:

"Kevin Grittner"  writes:

Tom Lane  wrote:

Would you check whether just casting the function result to (void)
shuts it up?



Casting the result to (void) didn't change the warning.  It shut up
when I declared a local variable and assigned the value to it (which
was then never used).

Too bad.  I believe gcc 4.6 will warn about *that*, so it's not going to
be much of an improvement for long.




Ugh. Isn't there some sort of pragma or similar we can use to shut it up?

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] Debian readline/libedit breakage

[Andrew Dunstan]

On 02/17/2011 12:13 PM, Bruce Momjian wrote:



FWIW, the only interactively usable version of psql for windows I know
of is the one that runs under Cygwin. It can be build with readline and
works as expected.

Uh, don't we have a psql built via MSVC?  Doesn't it work interactively?



Not if you want readline. And in my book that's a requirement of a psql 
that's usable interactively. It's pretty horrible to use without it.


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] Debian readline/libedit breakage

[Stephen Frost]

* Bruce Momjian (br...@momjian.us) wrote:
> OK, I was only responding to Stephen Frost who said psql did not behave
> like other Windows apps.

I don't actually run psql or PG on Windows at all, I just presumed it
did since you were bringing up concerns about it in the Windows
installers.  Ah well, sorry for the confusion. :)

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Tom Lane]

"Kevin Grittner"  writes:
> I know that in Java you can get a positive number less than the full
> size as an indication that part of the block was written, and you
> must loop to write until you get all of it written (or get an error
> return).  At this page, it appears that the same is true of the
> write function in C:

This is appropriate when writing to sockets etc, where the kernel is
willing to reflect details like packet boundaries back to userspace.
I have never seen nor heard of it being true for writes to disk files,
except for the case of out-of-disk-space, in which it is quite unlikely
that looping is a good thing to do.

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] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Tom Lane]

"Kevin Grittner"  writes:
> Tom Lane  wrote:
>> Would you check whether just casting the function result to (void)
>> shuts it up?
 
> Casting the result to (void) didn't change the warning.  It shut up
> when I declared a local variable and assigned the value to it (which
> was then never used).

Too bad.  I believe gcc 4.6 will warn about *that*, so it's not going to
be much of an improvement for long.

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] contrib loose ends: 9.0 to 9.1 incompatibilities

[Tom Lane]

So, after some testing, attached are two different fixed-up versions of
pg_tgrm's update-from-unpackaged script.  The first one leaves the
parameter lists of some GIN support functions different from what they
would be if you installed pg_trgrm fresh in 9.1.  The second one fixes
the parameter lists too, by means of really ugly direct UPDATEs on
pg_proc.  I'm unsure which one to apply --- any opinions?

It's worth noting that both versions still leave the pg_trgm opclasses a
bit different from a fresh install, because the added operators are
"loose" in the opfamily rather than being bound into the opclass.  This
hasn't got any real functional effect, but if you were feeling paranoid
you could worry about whether the two different states could cause
problems for future versions of the update script.  As far as I can see,
the only thing we could realistically do about this with the tools at
hand is to change pg_trgm's install script so that it also creates the
new-in-9.1 entries "loose".  That seems a tad ugly, but depending on
where you stand on the paranoia scale you might think it's a good idea.
There is definitely no point in that refinement unless we update the
function parameter lists, though.

Comments?

regards, tom lane

/* contrib/pg_trgm/pg_trgm--unpackaged--1.0.sql */

ALTER EXTENSION pg_trgm ADD function set_limit(real);
ALTER EXTENSION pg_trgm ADD function show_limit();
ALTER EXTENSION pg_trgm ADD function show_trgm(text);
ALTER EXTENSION pg_trgm ADD function similarity(text,text);
ALTER EXTENSION pg_trgm ADD function similarity_op(text,text);
ALTER EXTENSION pg_trgm ADD operator %(text,text);
ALTER EXTENSION pg_trgm ADD type gtrgm;
ALTER EXTENSION pg_trgm ADD function gtrgm_in(cstring);
ALTER EXTENSION pg_trgm ADD function gtrgm_out(gtrgm);
ALTER EXTENSION pg_trgm ADD function gtrgm_consistent(internal,text,integer,oid,internal);
ALTER EXTENSION pg_trgm ADD function gtrgm_compress(internal);
ALTER EXTENSION pg_trgm ADD function gtrgm_decompress(internal);
ALTER EXTENSION pg_trgm ADD function gtrgm_penalty(internal,internal,internal);
ALTER EXTENSION pg_trgm ADD function gtrgm_picksplit(internal,internal);
ALTER EXTENSION pg_trgm ADD function gtrgm_union(bytea,internal);
ALTER EXTENSION pg_trgm ADD function gtrgm_same(gtrgm,gtrgm,internal);
ALTER EXTENSION pg_trgm ADD operator family gist_trgm_ops using gist;
ALTER EXTENSION pg_trgm ADD operator class gist_trgm_ops using gist;
ALTER EXTENSION pg_trgm ADD operator family gin_trgm_ops using gin;
ALTER EXTENSION pg_trgm ADD operator class gin_trgm_ops using gin;

-- These functions had different names/signatures in 9.0.  We can't just
-- drop and recreate them because they are linked into the GIN opclass,
-- so we need some hacks.

-- First, absorb them into the extension under their old names.

ALTER EXTENSION pg_trgm ADD function gin_extract_trgm(text, internal);
ALTER EXTENSION pg_trgm ADD function gin_extract_trgm(text, internal, int2, internal, internal);
ALTER EXTENSION pg_trgm ADD function gin_trgm_consistent(internal,smallint,text,integer,internal,internal);

-- Fix the names, and then do CREATE OR REPLACE to adjust the function
-- bodies to be correct (ie, reference the correct C symbol).  We do not
-- attempt to change the parameter lists, however.  It's not necessary
-- since GIN doesn't care, and there's no clean way to do it.

ALTER FUNCTION gin_extract_trgm(text, internal)
  RENAME TO gin_extract_value_trgm;
CREATE OR REPLACE FUNCTION gin_extract_value_trgm(text, internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

ALTER FUNCTION gin_extract_trgm(text, internal, int2, internal, internal)
  RENAME TO gin_extract_query_trgm;
CREATE OR REPLACE FUNCTION gin_extract_query_trgm(text, internal, int2, internal, internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

-- gin_trgm_consistent didn't change name, so nothing more to do for it.

-- These were not in 9.0:

CREATE FUNCTION similarity_dist(text,text)
RETURNS float4
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR <-> (
LEFTARG = text,
RIGHTARG = text,
PROCEDURE = similarity_dist,
COMMUTATOR = '<->'
);

CREATE FUNCTION gtrgm_distance(internal,text,int,oid)
RETURNS float8
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

-- Add new stuff to the operator classes.  Note this will result in the
-- added stuff being "loose" in the operator family, rather than bound
-- into the operator class as it would be when creating the extension
-- from scratch.  That shouldn't be problematic.

ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
OPERATOR2   <-> (text, text) FOR ORDER BY pg_catalog.float_ops,
OPERATOR3   pg_catalog.~~ (text, text),
OPERATOR4   pg_catalog.~~* (text, text),
FUNCTION8 (text, text)   gtrgm_distance (internal, text, int, oid);

ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD
OPERATOR3

Re: [HACKERS] Debian readline/libedit breakage

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> On 02/17/2011 11:58 AM, Bruce Momjian wrote:
> > Andrew Dunstan wrote:
> >>
> >> On 02/17/2011 11:44 AM, Bruce Momjian wrote:
> >>> Andrew Dunstan wrote:
>  On 02/17/2011 11:22 AM, Bruce Momjian wrote:
> > psql used to use the native Windows line editing ability --- has that
> > changed?
>  When did it? Ad what "native" windows line editing ability are you
>  referring to?
> >>> There is native Windows editing like arrows, etc and history, though the
> >>> history is not kept between sessions.  If windows is now using readline,
> >>> then odds are we are shipping libreadline to make that happen, and we
> >>> are then linking using a supplied GPL library (and we don't have the
> >>> OS-installed exception).  I hope I am wrong.
> >>
> >> Readline has always been disabled on Windows builds AFAIK. Just look at
> >> the buildfarm traces. Here's an example from
> >> 
> >>
> >>  configure: WARNING: *** Readline does not work on MinGW --- disabling
> >>
> >>
> >> It's not used in MSVC either, IIRC.
> >
> > OK, I was only responding to Stephen Frost who said psql did not behave
> > like other Windows apps.
> 
> I think both of you have possibly been under a misapprehension. All this 
> discussion about the Windows installers is entirely irrelevant to this 
> thread, ISTM, since there is no readline use in them.

OK, that's what I thought.

> FWIW, the only interactively usable version of psql for windows I know 
> of is the one that runs under Cygwin. It can be build with readline and 
> works as expected.

Uh, don't we have a psql built via MSVC?  Doesn't it work interactively?

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

  + It's impossible for everything to be true. +

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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Kevin Grittner]

Tom Lane  wrote:
 
> In at least some of these cases, I think ignoring the write()
> result is intentional, because there's really nothing useful we
> can do about it if it fails (oh, you wish we'd log a failure to
> write to the log?).
 
I know that in Java you can get a positive number less than the full
size as an indication that part of the block was written, and you
must loop to write until you get all of it written (or get an error
return).  At this page, it appears that the same is true of the
write function in C:
 
http://www.gnu.org/s/libc/manual/html_node/I_002fO-Primitives.html
 
Quoting from that page:
 
| The return value is the number of bytes actually written. This may
| be size, but can always be smaller. Your program should always
| call write in a loop, iterating until all the data is written. 
 
Isn't that the write function we're calling?  If so, are you
maintaining that the gnu.org documentation of this function is
wrong?
 
-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] Debian readline/libedit breakage

[Andrew Dunstan]

On 02/17/2011 11:58 AM, Bruce Momjian wrote:

Andrew Dunstan wrote:


On 02/17/2011 11:44 AM, Bruce Momjian wrote:

Andrew Dunstan wrote:

On 02/17/2011 11:22 AM, Bruce Momjian wrote:

psql used to use the native Windows line editing ability --- has that
changed?

When did it? Ad what "native" windows line editing ability are you
referring to?

There is native Windows editing like arrows, etc and history, though the
history is not kept between sessions.  If windows is now using readline,
then odds are we are shipping libreadline to make that happen, and we
are then linking using a supplied GPL library (and we don't have the
OS-installed exception).  I hope I am wrong.


Readline has always been disabled on Windows builds AFAIK. Just look at
the buildfarm traces. Here's an example from


 configure: WARNING: *** Readline does not work on MinGW --- disabling


It's not used in MSVC either, IIRC.


OK, I was only responding to Stephen Frost who said psql did not behave
like other Windows apps.


I think both of you have possibly been under a misapprehension. All this 
discussion about the Windows installers is entirely irrelevant to this 
thread, ISTM, since there is no readline use in them.


FWIW, the only interactively usable version of psql for windows I know 
of is the one that runs under Cygwin. It can be build with readline and 
works as expected.


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] pika failing since the per-column collation patch

[Rémi Zara]

Le 14 févr. 2011 à 19:27, Rémi Zara a écrit :

> 
> Le 12 févr. 2011 à 18:51, Peter Eisentraut a écrit :
> 
>> 
>> It's only failing on this one machine, but there isn't anything
>> platform-specific in this code, so I'd look for memory management faults
>> on the code or a compiler problem.  Try with lower optimization for a
>> start.
>> 
> 
> 
> Same failure with -O0 (and more shared memory).
> 

Hi,

Without me changing anything (still at -O0), the same error occurred but at the 
installCheck step, rather than the check step (which passed)

Anything else to try ?

Regards,

Rémi Zara
-- 
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_tablespace

[carl clemens]

Hi Hackers,

After reviewing docs and searching web
cannot find out how to determine the default tablespace
of a user?

Like:

select spcname from blab where roloid = ;

Is this possible?

Thank you for your time.




  

-- 
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] SSI bug?

[YAMAMOTO Takashi]

hi,

might be unrelated to the loop problem, but...

i got the following SEGV when runnning vacuum on a table.
(the line numbers in predicate.c is different as i have local modifications.)
oldlocktag.myTarget was NULL.
it seems that TransferPredicateLocksToNewTarget sometimes use stack garbage
for newpredlocktag.myTarget.  vacuum on the table succeeded with the attached
patch.  the latter part of the patch was necessary to avoid targetList
corruption, which later seems to make DeleteChildTargetLocks loop inifinitely.

YAMAMOTO Takashi

#0  0x0823cf6c in PredicateLockAcquire (targettag=0xbfbfa734)
at predicate.c:1835
#1  0x0823f18a in PredicateLockPage (relation=0x99b4dcf0, blkno=1259)
at predicate.c:2206
#2  0x080ac978 in _bt_search (rel=0x99b4dcf0, keysz=2, scankey=0x99a05040, 
nextkey=0 '\0', bufP=0xbfbfa894, access=1) at nbtsearch.c:97
#3  0x080a996d in _bt_pagedel (rel=0x99b4dcf0, buf=, 
stack=0x0) at nbtpage.c:1059
#4  0x080aacc2 in btvacuumscan (info=0xbfbfbcc4, stats=0x99a01328, 
callback=0x8184d50 , callback_state=0x99a012e0, 
cycleid=13675) at nbtree.c:981
#5  0x080ab15c in btbulkdelete (fcinfo=0xbfbfb9e0) at nbtree.c:573
#6  0x082fde74 in FunctionCall4 (flinfo=0x99b86958, arg1=3217013956, arg2=0, 
arg3=135810384, arg4=2577404640) at fmgr.c:1437
#7  0x080a4fd0 in index_bulk_delete (info=0xbfbfbcc4, stats=0x0, 
callback=0x8184d50 , callback_state=0x99a012e0)
at indexam.c:738
#8  0x08184cd4 in lazy_vacuum_index (indrel=0x99b4dcf0, stats=0x99a023e0, 
vacrelstats=0x99a012e0) at vacuumlazy.c:938
#9  0x081854b6 in lazy_vacuum_rel (onerel=0x99b47650, vacstmt=0x99b059d0, 
bstrategy=0x99a07018, scanned_all=0xbfbfcfd8 "") at vacuumlazy.c:762
#10 0x08184265 in vacuum_rel (relid=16424, vacstmt=0x99b059d0, 
do_toast=1 '\001', for_wraparound=0 '\0', scanned_all=0xbfbfcfd8 "")
at vacuum.c:978
#11 0x081845ea in vacuum (vacstmt=0x99b059d0, relid=0, do_toast=1 '\001', 
bstrategy=0x0, for_wraparound=0 '\0', isTopLevel=1 '\001') at vacuum.c:230
#12 0xbbab50c3 in pgss_ProcessUtility (parsetree=0x99b059d0, 
queryString=0x99b05018 "vacuum (verbose,analyze) pgfs.dirent;", 
params=0x0, isTopLevel=1 '\001', dest=0x99b05b80, 
completionTag=0xbfbfd21a "") at pg_stat_statements.c:603
#13 0x082499ea in PortalRunUtility (portal=0x99b33018, utilityStmt=0x99b059d0, 
isTopLevel=1 '\001', dest=0x99b05b80, completionTag=0xbfbfd21a "")
at pquery.c:1191
#14 0x0824a79e in PortalRunMulti (portal=0x99b33018, isTopLevel=4 '\004', 
dest=0x99b05b80, altdest=0x99b05b80, completionTag=0xbfbfd21a "")
at pquery.c:1298
#15 0x0824b21a in PortalRun (portal=0x99b33018, count=2147483647, 
isTopLevel=1 '\001', dest=0x99b05b80, altdest=0x99b05b80, 
completionTag=0xbfbfd21a "") at pquery.c:822
#16 0x08247dc7 in exec_simple_query (
query_string=0x99b05018 "vacuum (verbose,analyze) pgfs.dirent;")
at postgres.c:1059
#17 0x08248a79 in PostgresMain (argc=2, argv=0xbb912650, 
username=0xbb9125c0 "takashi") at postgres.c:3943
#18 0x0820e231 in ServerLoop () at postmaster.c:3590
#19 0x0820ef88 in PostmasterMain (argc=3, argv=0xbfbfe59c) at postmaster.c:1110
#20 0x081b6439 in main (argc=3, argv=0xbfbfe59c) at main.c:199
(gdb) list
1830 
offsetof(PREDICATELOCK, xactLink));
1831
1832oldlocktag = predlock->tag;
1833Assert(oldlocktag.myXact == sxact);
1834oldtarget = oldlocktag.myTarget;
1835oldtargettag = oldtarget->tag;
1836
1837if (TargetTagIsCoveredBy(oldtargettag, *newtargettag))
1838{
1839uint32  oldtargettaghash;
(gdb) 
diff --git a/src/backend/storage/lmgr/predicate.c 
b/src/backend/storage/lmgr/predicate.c
index 722d0f8..4dde6ae 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -2537,8 +2558,8 @@ TransferPredicateLocksToNewTarget(const 
PREDICATELOCKTARGETTAG oldtargettag,
if (!found)
{
SHMQueueInit(&(newtarget->predicateLocks));
-   newpredlocktag.myTarget = newtarget;
}
+   newpredlocktag.myTarget = newtarget;
 
oldpredlock = (PREDICATELOCK *)
SHMQueueNext(&(oldtarget->predicateLocks),
@@ -2588,10 +2609,12 @@ TransferPredicateLocksToNewTarget(const 
PREDICATELOCKTARGETTAG oldtargettag,
outOfShmem = true;
goto exit;
}
-   SHMQueueInsertBefore(&(newtarget->predicateLocks),
-
&(newpredlock->targetLink));
-   
SHMQueueInsertBefore(&(newpredlocktag.myXact->predicateLocks),
-
&(newpredlock->xactLink));
+ 

Re: [HACKERS] SSI bug?

[YAMAMOTO Takashi]

hi,

> YAMAMOTO Takashi  wrote:
>  
>> might be unrelated to the loop problem, but...
>  
> Aha!  I think it *is* related.  There were several places where data
> was uninitialized here; mostly because Dan was working on this piece
> while I was working on separate issues which added the new fields.
> I missed the interaction on integrating the two efforts.  :-(
> The uninitialized fields could lead to all the symptoms you saw.
> I've reviewed, looking for other similar issues and didn't find any.
>  
> Could you try the attached patch and see if this fixes the issues
> you've seen?

with your previous patch or not?

YAMAMOTO Takashi

>  
> -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] SSI bug?

[YAMAMOTO Takashi]

hi,

> YAMAMOTO Takashi  wrote:
>  
>> with your previous patch or not?
>  
> With, thanks.

i tried.  unfortunately i can still reproduce the original loop problem.

WARNING:  [0] target 0xbb51ef18 tag 4000:4017:7e3:78:0 prior 0xbb51f148 next 0xb
b51edb0
WARNING:  [1] target 0xbb51f148 tag 4000:4017:7e3:77:0 prior 0xbb51ef90 next 0xb
b51ef18
WARNING:  [2] target 0xbb51ef90 tag 4000:4017:7e3:74:0 prior 0xbb51edb0 next 0xb
b51f148
WARNING:  [3] target 0xbb51edb0 tag 4000:4017:7e3:71:0 prior 0xbb51ef18 next 0xb
b51ef90
WARNING:  found a loop

YAMAMOTO Takashi

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

[HACKERS] rewrite of RECENTLY DEAD tuples

[Benjamin S.]

Hello list,

ATRewriteTable in tablecmds.c uses SnapshotNow to rewrite and thus
does not copy RECENTLY DEAD tuples. But copy_heap_data in cluster.c
uses SnapshotAny and copys RECENTLY DEAD tuples to the new heap
file.

I don't understand why it is not needed in the first case. In the
second case I guess that there may be still a transaction with an
older snapshot running which should be able to open the rewrited
table and see the older (RECENTLY DEAD) tuple. Why must a
transaction in the first case not be able to do so also?

Regards
Benjamin

-- 
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] Fwd: [JDBC] Weird issues when reading UDT from stored function

[Lukas Eder]

2011/2/17 Florian Pflug 

> On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
> > Any suggestions about how the JDBC driver can express the query to get
> > the behavior that it wants? Specifically, the driver wants to call a
> > particular function with N OUT or INOUT parameters (and maybe some other
> > IN parameters too) and get a resultset with N columns back.
>
> There's no sane way to do that, I fear. You could of course look up the
> function definition in the catalog before actually calling it, but with
> overloading and polymorphic types finding the right pg_proc entry seems
> awfully complex.
>
> Your best option is probably to just document this caveat...
>

But there still is a bug in the JDBC driver as I originally documented it.
Even if you say it's not simple to know whether the signature is actually a
single UDT with 6 attributes or just 6 OUT parameters, the result is wrong
(as stated in my original mail):

The nested UDT structure completely screws up fetching results. This
> is what I get with JDBC:
> 
>
>PreparedStatement stmt = connection.prepareStatement("select *
> from p_enhance_address2()");
>ResultSet rs = stmt.executeQuery();
>
>while (rs.next()) {
>System.out.println("# of columns: " +
> rs.getMetaData().getColumnCount());
>System.out.println(rs.getObject(1));
>}
> 
> Output:
> # of columns: 6
> ("(""Parliament Hill"",77)",NW31A9)
>

The result set meta data correctly state that there are 6 OUT columns. But
only the first 2 are actually fetched (because of a nested UDT)...

Re: [HACKERS] sepgsql contrib module

[Kohei Kaigai]

The attached patch removes rules to build a policy package for regression
test and modifies documentation part to introduce steps to run the test.

Thanks,
--
NEC Europe Ltd, Global Competence Center
KaiGai Kohei 


> -Original Message-
> From: Kohei Kaigai
> Sent: 15 February 2011 18:27
> To: 'Robert Haas'; Tom Lane
> Cc: Andrew Dunstan; Stephen Frost; KaiGai Kohei; PgHacker
> Subject: RE: [HACKERS] sepgsql contrib module
> 
> 
> 
> > -Original Message-
> > From: Robert Haas [mailto:robertmh...@gmail.com]
> > Sent: 15 February 2011 16:52
> > To: Tom Lane
> > Cc: Andrew Dunstan; Kohei Kaigai; Stephen Frost; KaiGai Kohei; PgHacker
> > Subject: Re: [HACKERS] sepgsql contrib module
> >
> > On Tue, Feb 15, 2011 at 11:41 AM, Tom Lane  wrote:
> > > Robert Haas  writes:
> > >> On Tue, Feb 15, 2011 at 11:01 AM, Tom Lane  wrote:
> > >>> Robert Haas  writes:
> >  Those are good points.  My point was just that you can't actually
> >  build that file at the time you RUN the regression tests, because
> you
> >  have to build it first, then install it, then run the regression
> >  tests.  It could be a separate target, like 'make policy', but I
> don't
> >  think it works to make it part of 'make installcheck'.
> > >
> > >>> So?  Once you admit that you can do that, it's a matter of a couple
> > more
> > >>> lines to make the installcheck target depend on the policy target
> iff
> > >>> selinux was enabled.
> > >
> > >> Sure, you could do that, but I don't see what problem it would fix.
> > >> You'd still have to build and manually install the policy before you
> > >> could run make installcheck.  And once you've done that, you don't
> > >> need to rebuild it every future time you run make installcheck.
> > >
> > > Oh, I see: you're pointing out the root-only "semodule" step that has
> > to
> > > be done in between there.  Good point.  But the current arrangement
> is
> > > still a mistake: the required contents of sepgsql-regtest.pp depend
> on
> > > the configuration of the test system, which can't be known at build
> > > time.
> > >
> > > So what we should do is offer a "make policy" target and alter the test
> > > instructions to say you should do that and then run semodule.  Or maybe
> > > just put the whole "make -f /usr/share/selinux/devel/Makefile" dance
> > > into the instructions --- it doesn't look to me like our makefile
> > > infrastructure really has anything useful to add to that.
> >
> > Yeah, agreed.
> >
> I also agree with this direction. The policy type depends on individual
> installations,
> it is not easy to assume on build time.
> Please wait for a small patch to remove this rule from Makefile and update
> documentation.
> 
> As a side note, we can have a build option that does not require selinux
> enabled.
> The reason why Makefile of selinux tries to /selinux/mls is that we don't
> specify
> MLS=1 or MLS=0 explicitly.
> IIRC, the specfile of RHEL/Fedora gives all the Makefile parameters
> explicitly, thus,
> selinux does not need to be enabled on the build server.
> However, it is not a solution in this case. It is not easy to estimate the
> required
> policy type and existence of MLS support on build time.
> 
> Thanks,
> --
> NEC Europe Ltd, Global Competence Center
> KaiGai Kohei 


sepgsql-policy.1.patch
Description: sepgsql-policy.1.patch

-- 
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] Debian readline/libedit breakage

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> On 02/17/2011 11:44 AM, Bruce Momjian wrote:
> > Andrew Dunstan wrote:
> >>
> >> On 02/17/2011 11:22 AM, Bruce Momjian wrote:
> >>> psql used to use the native Windows line editing ability --- has that
> >>> changed?
> >>
> >> When did it? Ad what "native" windows line editing ability are you
> >> referring to?
> > There is native Windows editing like arrows, etc and history, though the
> > history is not kept between sessions.  If windows is now using readline,
> > then odds are we are shipping libreadline to make that happen, and we
> > are then linking using a supplied GPL library (and we don't have the
> > OS-installed exception).  I hope I am wrong.
> 
> 
> Readline has always been disabled on Windows builds AFAIK. Just look at 
> the buildfarm traces. Here's an example from 
> 
> 
> configure: WARNING: *** Readline does not work on MinGW --- disabling
> 
> 
> It's not used in MSVC either, IIRC.


OK, I was only responding to Stephen Frost who said psql did not behave
like other Windows apps.

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

  + It's impossible for everything to be true. +

-- 
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] Debian readline/libedit breakage

[Andrew Dunstan]

On 02/17/2011 11:44 AM, Bruce Momjian wrote:

Andrew Dunstan wrote:


On 02/17/2011 11:22 AM, Bruce Momjian wrote:

psql used to use the native Windows line editing ability --- has that
changed?


When did it? Ad what "native" windows line editing ability are you
referring to?

There is native Windows editing like arrows, etc and history, though the
history is not kept between sessions.  If windows is now using readline,
then odds are we are shipping libreadline to make that happen, and we
are then linking using a supplied GPL library (and we don't have the
OS-installed exception).  I hope I am wrong.



Readline has always been disabled on Windows builds AFAIK. Just look at 
the buildfarm traces. Here's an example from 



   configure: WARNING: *** Readline does not work on MinGW --- disabling


It's not used in MSVC either, IIRC.

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] Add support for logging the current role

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> Ugg, wait a minute.  This not only adds %U; it also changes the
> behavior of %u, which I don't think we've agreed on.  Also, emitting
> 'none' when not SET ROLE has been done is pretty ugly.  I'm back to
> thinking we need to push this out to 9.2 and take more time to think
> about this.

%u, user_name, etc changes reverted.

%U now always returns the role currently being used for permissions
checks, by using show_session_authorization() when show_role() returns
'none'.  Ditto for CSV updates.

git log below, re-based patch attached.  All regression tests passed,
tested with log_line_prefix and csvlog also, all looks good to me.

Robert, if you say this has to be punted to 9.2 again, I'm giving up. ;)

Thanks,

Stephen
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 3562,3568  local0.*/var/log/postgresql
  
  
   %u
!  User name
   yes
  
  
--- 3562,3579 
  
  
   %u
!  Authenticated user name, the user name that the user used
!  to authenticate to PostgreSQL with.
!  yes
! 
! 
!  %U
!  Current role being used for permissions checking, can be
!  set with SET ROLE or SET SESSION
!  AUTHORIZATION (only allowed for superusers);
!  Note: Log messages from inside SECURITY DEFINER
!  functions will show the calling role, not the effective role
!  inside the SECURITY DEFINER function
   yes
  
  
***
*** 3790,3795  FROM pg_stat_activity;
--- 3801,3807 
  with these columns:
  timestamp with milliseconds,
  user name,
+ current role name,
  database name,
  process ID,
  client host:port number,
***
*** 3820,3825  CREATE TABLE postgres_log
--- 3832,3838 
  (
log_time timestamp(3) with time zone,
user_name text,
+   curr_role text,
database_name text,
process_id integer,
connection_from text,
*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***
*** 847,852  assign_session_authorization(const char *value, bool doit, GucSource source)
--- 847,857 
  	return result;
  }
  
+ /*
+  * function to return the stored session username, needed because we
+  * can't do catalog lookups when possibly being called after an error,
+  * eg: from elog.c or part of GUC handling.
+  */
  const char *
  show_session_authorization(void)
  {
***
*** 972,977  assign_role(const char *value, bool doit, GucSource source)
--- 977,987 
  	return result;
  }
  
+ /*
+  * function to return the stored role username, needed because we
+  * can't do catalog lookups when possibly being called after an error,
+  * eg: from elog.c or part of GUC handling.
+  */
  const char *
  show_role(void)
  {
*** a/src/backend/utils/error/elog.c
--- b/src/backend/utils/error/elog.c
***
*** 65,70 
--- 65,71 
  
  #include "access/transam.h"
  #include "access/xact.h"
+ #include "commands/variable.h"
  #include "libpq/libpq.h"
  #include "libpq/pqformat.h"
  #include "mb/pg_wchar.h"
***
*** 1777,1782  log_line_prefix(StringInfo buf, ErrorData *edata)
--- 1778,1795 
  	int			format_len;
  	int			i;
  
+ 	/* gather current session and role names */
+ 	const char *session_auth = show_session_authorization();
+ 	const char *role_auth = show_role();
+ 
+ 	/* what we'll actually log as current role, based on if
+ 	 * a set role has been done or not */
+ 	char	   *curr_role = role_auth;
+ 
+ 	/* if show_role() returns 'none', then we log the session user instead */
+ 	if (strcmp(role_auth,"none") == 0)
+ 		curr_role = session_auth;
+ 
  	/*
  	 * This is one of the few places where we'd rather not inherit a static
  	 * variable's value from the postmaster.  But since we will, reset it when
***
*** 1832,1837  log_line_prefix(StringInfo buf, ErrorData *edata)
--- 1845,1853 
  	appendStringInfoString(buf, username);
  }
  break;
+ 			case 'U':
+ appendStringInfoString(buf, curr_role);
+ break;
  			case 'd':
  if (MyProcPort)
  {
***
*** 1967,1972  write_csvlog(ErrorData *edata)
--- 1983,2000 
  	/* has counter been reset in current process? */
  	static int	log_my_pid = 0;
  
+ 	/* pull the session authorization */
+ 	const char *session_auth = show_session_authorization();
+ 	const char *role_auth = show_role();
+ 
+ 	/* what we'll actually log as current role, based on if
+ 	 * a set role has been done or not */
+ 	char	   *curr_role = role_auth;
+ 
+ 	/* if show_role() returns 'none', then we log the session user instead */
+ 	if (strcmp(role_auth,"none") == 0)
+ 		curr_r

Re: [HACKERS] Debian readline/libedit breakage

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> On 02/17/2011 11:22 AM, Bruce Momjian wrote:
> > psql used to use the native Windows line editing ability --- has that
> > changed?
> 
> 
> When did it? Ad what "native" windows line editing ability are you 
> referring to?

There is native Windows editing like arrows, etc and history, though the
history is not kept between sessions.  If windows is now using readline,
then odds are we are shipping libreadline to make that happen, and we
are then linking using a supplied GPL library (and we don't have the
OS-installed exception).  I hope I am wrong.

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

  + It's impossible for everything to be true. +

-- 
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] Debian readline/libedit breakage

[Andrew Dunstan]

On 02/17/2011 11:22 AM, Bruce Momjian wrote:

psql used to use the native Windows line editing ability --- has that
changed?



When did it? Ad what "native" windows line editing ability are you 
referring to?


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] Re: [COMMITTERS] pgsql: Fix blatantly uninitialized variable in recent commit.

[Kevin Grittner]

Tom Lane  wrote:
 
> Would you check whether just casting the function result to (void)
> shuts it up?
 
Casting the result to (void) didn't change the warning.  It shut up
when I declared a local variable and assigned the value to it (which
was then never used).
 
-Kevin

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