Re: [HACKERS] deadlock while doing VACUUM and DROP

[Pavan Deolasee]

On Fri, May 16, 2008 at 5:24 AM, Jan Urbański
<[EMAIL PROTECTED]> wrote:
>
> ERROR:  deadlock detected
> DETAIL:  Process 25423 waits for AccessExclusiveLock on relation 16386 of
> database 1; blocked by process 25428.
>Process 25428 waits for AccessShareLock on relation 16390 of database
> 1; blocked by process 25423.
>Process 25423: drop table manuale ;
>Process 25428: autovacuum: VACUUM ANALYZE public.manuale

I looked at it briefly. ISTM that the main relation and the toast
relation is getting into a deadlock. VACUUM first vacuums the main
relation, commits the transaction but *keeps* the
ShareUpdateExclusiveLock on the main relation. It then vacuums the
toast relation which requires ShareUpdateExclusiveLock on the toast
relation.

If at the same time, another backend drops the main relation. Because
of dependency, the toast relation is dropped first. So the other
backend takes AccessExclusiveLock on the toast relation. It then tries
to drop the main relation, asks for AccessExclusiveLock on the main
relation and gets into a deadlock with the first process vacumming the
relation.

I think we can fix it by making sure that locks on the to-be-deleted
and all the dependent objects are taken first, in an order that would
prevent the deadlock. Alternatively, we can just acquire
AccessExclusiveLock on the main relation before proceeding with the
recursive deletion. That would solve this case, but may be there are
other similar deadlocks waiting to happen. Also I am not sure if the
issue is big enough to demand the change.


Thanks,
Pavan

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

[HACKERS] [GSoC08]some detail plan of improving hash index

[Xiao Meng]

Hi, hackers.

I'm reading the source codes of hash and reviewing Neil's old patch of
improving hash index.
Here is some detail plan. I'm trying to adjust Neil's patch to the current
version of PostgreSQL first. I'm not quite familar with the code yet, so
please make some comment.

* Phase 1. Just store hash value instead of hash keys

First, define a macro to make it optional.

Second, add a new function _hash_form_item to construct IndexTuple with hash
code to replace index_form_tuple uaed in hash access method. It seems easy
since we did'nt need to deal with TOAST.

Third, modify _hash_checkqual. We can first compare the hash value; if it's
the same, we compare the real key value.
Also, HashScanOpaqueData adds an element hashso_sk_hash to hold scan key's
hash value to support scan function.

Finally, it seems the system catalog pg_amop also need to be modified.
In Neil's patch, he set the amopreqcheck to be True.
In the documents, it means index hit must be rechecked in the document. But
I'm not so clear. Does it just means we need to recheck the value when use
_hash_chechqual?


* Phase 2. Sort the hash value when insert into the bucket and use binary
search when scan
Add a function _hash_binsearch to support binary search in a bucket;
It involved in all functions when we need to search, insert and delete.

* Phase 3. When it's necessary, store the real value.
When we insert a new index tuple , for example tp , to a bucket, we can
check whether there's the same hash code.
If there's already an index tuple with such a hash code, we store both the
hash code and real key of tp.
Alternatively, we add a flag to represent the tuple stores a real value or
just hash code. It seems a little complex.

Phase 1 seems extremely easy. I'm trying to do it first.
Additionally, I need a benchmark to test the performance. It seems there's
some tools list in http://wiki.postgresql.org/wiki/Performances_QA_testing .
Any advice?

-- 
Have a good day;-)
Best Regards,
Xiao Meng

━━━
Data and Knowledge Engineering Research Center
Harbin Institute of Technology, China
Gtalk: [EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]

Re: [HACKERS] What to do with inline warnings?

[Neil Conway]

On Wed, 2008-05-14 at 20:25 +0100, Gregory Stark wrote:
> The Linux kernel does have some macros meant to mark unlikely branches
> (usually assertion failures) but I'm not sure how they work. And Gcc also has
> a few optimizations which are driven by profiling data but I it doesn't sound
> like this is one of them.

GCC's profile-driven optimization can be used to guide decisions about
both branch prediction/likelihood and function inlining. IMHO it is
definitely worth building the infrastructure to get Postgres builds with
profile-driven optimization -- certainly more maintainable and less
arbitrary than builtin_expect() and friends to me.

-Neil



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

[HACKERS] deadlock while doing VACUUM and DROP

[Jan Urbański]

I got this on HEAD while doing lots of CREATE -> COPY -> DROP on table.

(...)
DEBUG:  ProcessUtility
DEBUG:  drop auto-cascades to toast table pg_toast.pg_toast_16774
DEBUG:  drop auto-cascades to type pg_toast.pg_toast_16774
DEBUG:  drop auto-cascades to index pg_toast.pg_toast_16774_index
DEBUG:  drop auto-cascades to type public.text
DEBUG:  drop auto-cascades to type public.text[]
DEBUG:  StartTransaction
DEBUG:  name: unnamed; blockState:   DEFAULT; state: INPROGR, 
xid/subid/cid: 0/1/0, nestlvl: 1, children:

ERROR:  deadlock detected
DETAIL:  Process 25423 waits for AccessExclusiveLock on relation 16386 
of database 1; blocked by process 25428.
Process 25428 waits for AccessShareLock on relation 16390 of 
database 1; blocked by process 25423.

Process 25423: drop table manuale ;
Process 25428: autovacuum: VACUUM ANALYZE public.manuale
HINT:  See server log for query details.
STATEMENT:  drop table text;
(...)

I was then able to recreate this using:

$ while true; do psql template1 < test-lock.sql; done
and in another terminal
$ while true; do psql template1 -c 'vacuum analyze text'; done

The file test-lock.sql contained:

create table text ( a text );
\copy text from stdin
 ... 1000 lines of random 20 character strings ...
\.
drop table text;

Almost instantly messages like the above crop up, sometimes it's 
AccessExclusiveLock/ShareUpdateExclusiveLock, sometimes 
AccessExclusiveLock/ShareUpdateExclusiveLock.


System:
Linux 2.6.23.9 Intel Core Duo 32bit

Configure switches:
./configure --enable-debug --enable-cassert --with-libxml --with-perl 
--with-python --with-openssl --with-tcl


Cheers,
--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

--
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] WAL file naming sequence definition

[Josh Berkus]

Andrew,

> Would this be reasonable and is there any community interest in
> open-sourcing the tool that I'm building?

yes, definitely.

We shoud find a way to bundle your tool together with other physical 
integrity checking tools.  Eventually we can have a "check crashed 
postgresql suite".

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

-- 
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] pg_standby for 8.2 (with last restart point)

[Gurjeet Singh]

On Fri, Mar 28, 2008 at 10:30 AM, Gurjeet Singh <[EMAIL PROTECTED]>
wrote:

>
> I am still looking for comments on the correctness of this script and above
> mentioned procedure for running it on an 8.2.x release.
>

Well, I came across a serious bug in the script. Here's the corrected
version of the perl script for anyone who might have picked up the script
from here:

(Am still looking for any feedback on the correctness of the script;
especially in the light of the recent possible bugs fixed on the server
side, related to recovery)


my @text = `pg_controldata .`; # here . represents the PGDATA, since the
server is executing here.
my $line;

my $time_line_id;
my $redo_log_id;
my $redo_rec_off;
my $wal_seg_bytes;
my $redo_log_seg;

foreach $line ( @text )
{
$line = mychomp( $line );

if( $line =~ m/Latest checkpoint's TimeLineID:\s*(([0-9])+)/ )
{
# decimal number
$time_line_id = 0 + $1;
}
if( $line =~ m/Latest checkpoint's REDO
location:\s*(([0-9]|[A-F])+)\/(([0-9]|[A-F])+)/
)
{
# hexadecimal numbers
$redo_log_id = hex( $1 );
$redo_rec_off = hex( $3 );
}

if( $line =~ m/Bytes per WAL segment:\s*([0-9]+)/ )
{
# decimal number
$wal_seg_bytes = $1;
}
}

$redo_log_seg = sprintf( "%d", $redo_rec_off / $wal_seg_bytes );

print "" . sprintf( "%08X%08X%08X", $time_line_id, $redo_log_id,
$redo_log_seg ) . "\n";

# Wrapper around Perl's chomp function
sub mychomp
{
my ( $tmp ) = @_;
chomp( $tmp );
return $tmp;
}





-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [HACKERS] [rfc,patch] PL/Proxy in core

[Marko Kreen]

On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Marko Kreen" <[EMAIL PROTECTED]> writes:
>  > On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
>  >> "Marko Kreen" <[EMAIL PROTECTED]> writes:
>  >>> Eg. how does src/backend/parser/gram.c not leak memory on syntax error?
>  >>
>  >> It's not a leak because the memory can be re-used during the next
>  >> command.
>
>  > I may be blind, but I don't see any static variables there.
>
> Sorry, I was confusing bison with flex --- there are static variables
>  pointing at buffers within a flex scanner.
>
>  For bison it looks like defining YYSTACK_ALLOC/YYSTACK_FREE as
>  palloc/pfree might be a sane thing to do, but I haven't tested it.

Ok, so parser.y is now fine.

Now I must admit I do the same hack in scanner.l, but because it keeps
static references, there is always call to plproxy_yylex_destroy() in
places that throw exceptions (all of flex/bison/plproxy exceptions
go through single function).

Reason for that is again the fact that I could not wrap my brain around
flex memory handling.  And the hacks in src/backend/parser/scan.l are also
somethat mystery to me.  When using palloc() I can be sure of the flow,
and if something goes wrong it crashes instead leaking, so it can
be fixed immidately.

But now that I think about it, the scheme fails if palloc() itself
throws exception.  It can be fixed by calling following function
on parser entry:

void plproxy_yylex_startup(void)
{
#if FLXVER < 2005031
(YY_CURRENT_BUFFER) = NULL;
#else
(yy_buffer_stack) = NULL;
#endif
plproxy_yylex_destroy();
}

This is pretty hairy, but anything near flex is hairy.  Such function
also would drop the requirement that plproxy_yylex_destroy() must always
be called.  Then we could keep current simple always-from-scratch allocation
pattern but more robust.


Or we could go back to default malloc usage.  I somewhat doubt it will
be much cleaner, it needs lot more faith in sanity of flex which I dont
have.
OTOH, considering that now here the possibility of others reviewing the
result is lot higher than before it can be attempted.

-- 
marko

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

[HACKERS] SSL and USER_CERT_FILE patch

[pgsql]

I have submitted a patch that does two things: (1) fixes a bug in the
client SSL code that never appended the home directory to the root
revocation list. and (2) adds 4 new fields to the connect string:

sslkey=fullepath_to_file
sslcert=fullpath_to_cert
ssltrustcrt=fullpath_to_trusted_cert_file
sslcrl=fullpath_to_revocation_list

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

Re: [HACKERS] Bug 3883 revisited

[Alvaro Herrera]

Heikki Linnakangas wrote:
> The "TRUNCATE table while we're holding references to it" bug (3883), is  
> causing an assertion failure on 8.2, when the TRUNCATE is called in a  
> trigger:

[...]

> I think we need to backpatch the fix for this...

So, what's the patch that needs the be backpatched?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] SSL and USER_CERT_FILE

[pgsql]

> [EMAIL PROTECTED] wrote:
>> > [EMAIL PROTECTED] writes:
>> >> Maybe we need to go even further and add it to the PQconnect API
>> >> sslkey=filename and sslcrt=filename in addition to sslmode?
>> >
>> > If there's a case to be made for this at all, it should be handled
>> > the same way as all other libpq connection parameters.
>> >
>> >regards, tom lane
>> >
>>
>> Here's the use case:
>>
>> I have an application that must connect to multiple PostgreSQL
>> databases and must use secure communications and the SSL keys are
>> under the control of the business units the administer the databases,
>> not me. In addition my application also communicates with other SSL
>> enabled versions of itself.
>>
>> I think you would agree that a hard coded immutable location for
>> "client" interface is problematic.
>
> I agree fully with the use-case. Most of the other things we allow both
> as connection parameters and as environment variables, so we should do
> that IMHO. What could be debated is if we should also somehow allow it
> to be specified in .pgpass for example?
>


I am testing a patch that is currently against the 8.2 series.

It implements in PQconnectdb(...)

sslmode=require sslkey=client.key sslcert=client.crt ssltrustcrt=certs.pem
sslcrl=crl.pem"

BTW: the revocation list probably never worked in the client.

-- 
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] SSL and USER_CERT_FILE

[Magnus Hagander]

[EMAIL PROTECTED] wrote:
> > [EMAIL PROTECTED] writes:
> >> Maybe we need to go even further and add it to the PQconnect API
> >> sslkey=filename and sslcrt=filename in addition to sslmode?
> >
> > If there's a case to be made for this at all, it should be handled
> > the same way as all other libpq connection parameters.
> >
> > regards, tom lane
> >
> 
> Here's the use case:
> 
> I have an application that must connect to multiple PostgreSQL
> databases and must use secure communications and the SSL keys are
> under the control of the business units the administer the databases,
> not me. In addition my application also communicates with other SSL
> enabled versions of itself.
> 
> I think you would agree that a hard coded immutable location for
> "client" interface is problematic.

I agree fully with the use-case. Most of the other things we allow both
as connection parameters and as environment variables, so we should do
that IMHO. What could be debated is if we should also somehow allow it
to be specified in .pgpass for example?

//Magnus

-- 
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] SSL and USER_CERT_FILE round 2

[Andrew Dunstan]

[EMAIL PROTECTED] wrote:

I think if you're going to provide for these then you should also

provide for the CA cert and CRL.

Otherwise, it seems sensible.



I thought about that, but the root and crl are for the server, and that
makes sense that the keys would be in the server directory. The server
needs to protect its data against clients wishing to connect.  The client
on the other hand, needs to access one or more postgresql servers.
  
It makes sense that the server keys and credentials be hard coded to its

protected data directory, while the client needs the ability to have
multiple keys.

Think of it this way, a specific lock only takes one key while a person
needs to carry multiple keys on a ring.
  



This is completely wrong. Why do you think your web browser has CA keys 
embedded in it? So it can know which server keys to trust. As 
documented, if a CA certificate set is present on the libpq client, the 
client will only trust server keys signed with a chain starting from 
that set.


CA certificates and CRLs can in general be used on both sides of an SSL 
connection, and we make explicit provision for them on both sides.


See http://www.postgresql.org/docs/current/static/libpq-ssl.html

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] SSL and USER_CERT_FILE round 2

[pgsql]

>
>
> [EMAIL PROTECTED] wrote:
>> Adding "sslkey" and "sslcert" to the PQconnectdb connection string.
>>
>> After some discussion, I think it is more appropriate to add the
>> key/cert
>> file for SSL into the connect string. For example:
>>
>> PQconnectdb("host=foo dbname=bar sslmode=require
>> sslkey=/opt/myapp/share/keys/client.key
>> sslcert=/opt/myapp/share/keys/client.crt");
>>
>>
>> Any comments?
>>
>>
>
> I think if you're going to provide for these then you should also
> provide for the CA cert and CRL.
>
> Otherwise, it seems sensible.

I thought about that, but the root and crl are for the server, and that
makes sense that the keys would be in the server directory. The server
needs to protect its data against clients wishing to connect.  The client
on the other hand, needs to access one or more postgresql servers.

It makes sense that the server keys and credentials be hard coded to its
protected data directory, while the client needs the ability to have
multiple keys.

Think of it this way, a specific lock only takes one key while a person
needs to carry multiple keys on a ring.

-- 
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] [rfc,patch] PL/Proxy in core

[Tom Lane]

"Marko Kreen" <[EMAIL PROTECTED]> writes:
> On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
>> "Marko Kreen" <[EMAIL PROTECTED]> writes:
>>> Eg. how does src/backend/parser/gram.c not leak memory on syntax error?
>> 
>> It's not a leak because the memory can be re-used during the next
>> command.

> I may be blind, but I don't see any static variables there.

Sorry, I was confusing bison with flex --- there are static variables
pointing at buffers within a flex scanner.

For bison it looks like defining YYSTACK_ALLOC/YYSTACK_FREE as
palloc/pfree might be a sane thing to do, but I haven't tested it.

regards, tom lane

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

Re: [HACKERS] bloated heapam.h

[Zdenek Kotala]

Alvaro Herrera wrote:

Zdenek Kotala wrote:

Alvaro Herrera napsal(a):


I try to play with lint now if it gets better results.

Ok, good.
Hmm, It generates a lot of unnecessary includes in *.c files. I have 
checked only couple of them and it seems that they are really 
unnecessary. A attach output. Unfortunately, it does not detect missing 
heapam.h from bufpage.h. However, I have not tested all magic switches 
yet :-).  There are also several reports about system headers file, but 
it could be platform specific warning.


Interesting.  It seems that Bruce's script could be replaced by lint.
Please share the switches you used.



I used following switches which only shows unused included file. I run command 
in backend directory.


lint -I ../include -errtags -errhdr=%all -Ncheck=%none -Nlevel=1 -erroff=%all 
-erroff=no%E_INCL_NUSD -c  `find . -name "*.c"` > include.out


Good to mention that I use lint from Sun Studio 12.


Zdenek

--
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] SSL and USER_CERT_FILE round 2

[Andrew Dunstan]

[EMAIL PROTECTED] wrote:

Adding "sslkey" and "sslcert" to the PQconnectdb connection string.

After some discussion, I think it is more appropriate to add the key/cert
file for SSL into the connect string. For example:

PQconnectdb("host=foo dbname=bar sslmode=require
sslkey=/opt/myapp/share/keys/client.key
sslcert=/opt/myapp/share/keys/client.crt");


Any comments?

  


I think if you're going to provide for these then you should also 
provide for the CA cert and CRL.


Otherwise, it seems sensible.

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] [rfc,patch] PL/Proxy in core

[Marko Kreen]

On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Marko Kreen" <[EMAIL PROTECTED]> writes:
> > Eg. how does src/backend/parser/gram.c not leak memory on syntax error?
>
> It's not a leak because the memory can be re-used during the next
>  command.

I may be blind, but I don't see any static variables there.

>  I believe you'll find that trying to make it use palloc is a failure
>  because it keeps static pointers that it expects will stay valid across
>  calls.

Thats true, I need to drop the redefines if the allocations may be reused.

-- 
marko

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

Re: [HACKERS] [rfc,patch] PL/Proxy in core

[Tom Lane]

"Marko Kreen" <[EMAIL PROTECTED]> writes:
> Eg. how does src/backend/parser/gram.c not leak memory on syntax error?

It's not a leak because the memory can be re-used during the next
command.

I believe you'll find that trying to make it use palloc is a failure
because it keeps static pointers that it expects will stay valid across
calls.

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] SSL and USER_CERT_FILE round 2

[pgsql]

Adding "sslkey" and "sslcert" to the PQconnectdb connection string.

After some discussion, I think it is more appropriate to add the key/cert
file for SSL into the connect string. For example:

PQconnectdb("host=foo dbname=bar sslmode=require
sslkey=/opt/myapp/share/keys/client.key
sslcert=/opt/myapp/share/keys/client.crt");


Any comments?




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

[HACKERS] Adding variables for segment_size, wal_segment_size and block sizes

[Bernd Helmle]

Now that we have customizable segment sizes for heap and WAL at compilation 
time i would like to have some runtime variables to query that information 
(besides pg_controldata). I can imagine to have the following names:


segment_size: Reports heap segment size
wal_segment_size: Reports wal segment size
block_size: Available yet
wal_block_size: wal block size

I'd like to implement them if we agree on them

--
 Thanks

   Bernd

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

Re: [HACKERS] [rfc,patch] PL/Proxy in core

[Marko Kreen]

On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Marko Kreen" <[EMAIL PROTECTED]> writes:
> > How about following patch?  I have bison 2.3 and it seems not to do
>  > global allocation, so it should be fine.  There may be early exit
>  > with elog(ERRROR) inside so I'd like to avoid malloc() itself.
>
> None of our other parsers fool with bison's memory allocation;
>  why does yours need to?

Because that way I can be sure I understand their allocation behaviour.

Eg. how does src/backend/parser/gram.c not leak memory on syntax error?
I don't understand it.

But if I force them use palloc(), always, I can be sure memore is freed.

-- 
marko

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

Re: [HACKERS] SSL and USER_CERT_FILE

[pgsql]

>
> On May 15, 2008, at 6:31 AM, [EMAIL PROTECTED] wrote:
>
>>> Mark Woodward wrote:
 I am using PostgreSQL's SSL support and the conventions for the
 key and
 certifications don't make sense from the client perspective.
 Especially
 under Windows.

 I am proposing a few simple changes:

 Adding two API
 void PQsetSSLUserCertFileName(char *filename)
 {
user_crt_filename = strdup(filename);
 }
 PQsetSSLUserKeyFileName(char *filename)
 {
user_key_filename = strdup(filename);
 }



>>> [snip]
 Any comments?


>>>
>>>
>>> I think it would probably be much better to allow for some
>>> environment
>>> variables to specify the locations of the client certificate and key
>>> (and the CA cert and CRL) - c.f. PGPASSFILE.
>>>
>>> That way not only could these be set by C programs but by any libpq
>>> user
>>> (I'm sure driver writers who use libpq don't want to have to bother
>>> with
>>> this stuff.) And we wouldn't need to change the API at all.
>>>
>>
>> The problem I have with environment variables is that they tend not
>> to be
>> application specific and almost always lead to configuration issues.
>> As a
>> methodology for default configuration, it adds flexibility. Also, the
>> current configuration does not easily take in to consideration the
>> idea
>> that different databases with different keys can be used from the same
>> system the same user.
>
> Environment variables don't have to be set in your shell.
>
> This would seem to give the same functionality you suggest above,
> given support for environment variables:
>
> void PQsetSSLUserCertFileName(char * filename)
> {
>setenv("PGCERTFILE", filename);
> }
>
> void PQsetSSLUserKeyFileName(char *filename)
> {
>setenv("PGKEYFILE", filename);
> }
>
> Or, in perl, $ENV{PGKEYFILE} = $file and so on. It seems
> less intrusive than adding new API calls.
>
> Cheers,
>Steve

Doesn't it make sense that the connection be configured in one place? I
agree with Tom, if it should be done, it should be done in PQconnectdb.

-- 
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] SSL and USER_CERT_FILE

[pgsql]

> [EMAIL PROTECTED] writes:
>> Maybe we need to go even further and add it to the PQconnect API
>> sslkey=filename and sslcrt=filename in addition to sslmode?
>
> If there's a case to be made for this at all, it should be handled the
> same way as all other libpq connection parameters.
>
>   regards, tom lane
>

Here's the use case:

I have an application that must connect to multiple PostgreSQL databases
and must use secure communications and the SSL keys are under the control
of the business units the administer the databases, not me. In addition my
application also communicates with other SSL enabled versions of itself.

I think you would agree that a hard coded immutable location for "client"
interface is problematic.

-- 
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] SSL and USER_CERT_FILE

[Steve Atkins]

On May 15, 2008, at 6:31 AM, [EMAIL PROTECTED] wrote:


Mark Woodward wrote:
I am using PostgreSQL's SSL support and the conventions for the  
key and
certifications don't make sense from the client perspective.  
Especially

under Windows.

I am proposing a few simple changes:

Adding two API
void PQsetSSLUserCertFileName(char *filename)
{
   user_crt_filename = strdup(filename);
}
PQsetSSLUserKeyFileName(char *filename)
{
   user_key_filename = strdup(filename);
}




[snip]

Any comments?





I think it would probably be much better to allow for some  
environment

variables to specify the locations of the client certificate and key
(and the CA cert and CRL) - c.f. PGPASSFILE.

That way not only could these be set by C programs but by any libpq  
user
(I'm sure driver writers who use libpq don't want to have to bother  
with

this stuff.) And we wouldn't need to change the API at all.



The problem I have with environment variables is that they tend not  
to be
application specific and almost always lead to configuration issues.  
As a

methodology for default configuration, it adds flexibility. Also, the
current configuration does not easily take in to consideration the  
idea

that different databases with different keys can be used from the same
system the same user.


Environment variables don't have to be set in your shell.

This would seem to give the same functionality you suggest above,
given support for environment variables:

void PQsetSSLUserCertFileName(char * filename)
{
  setenv("PGCERTFILE", filename);
}

void PQsetSSLUserKeyFileName(char *filename)
{
  setenv("PGKEYFILE", filename);
}

Or, in perl, $ENV{PGKEYFILE} = $file and so on. It seems
less intrusive than adding new API calls.

Cheers,
  Steve


--
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] SSL and USER_CERT_FILE

[Tom Lane]

[EMAIL PROTECTED] writes:
> Maybe we need to go even further and add it to the PQconnect API
> sslkey=filename and sslcrt=filename in addition to sslmode?

If there's a case to be made for this at all, it should be handled the
same way as all other libpq connection parameters.

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] SSL and USER_CERT_FILE

[pgsql]

> Mark Woodward wrote:
>> I am using PostgreSQL's SSL support and the conventions for the key and
>> certifications don't make sense from the client perspective. Especially
>> under Windows.
>>
>> I am proposing a few simple changes:
>>
>> Adding two API
>> void PQsetSSLUserCertFileName(char *filename)
>> {
>> user_crt_filename = strdup(filename);
>> }
>> PQsetSSLUserKeyFileName(char *filename)
>> {
>> user_key_filename = strdup(filename);
>> }
>>
>>
>>
> [snip]
>> Any comments?
>>
>>
>
>
> I think it would probably be much better to allow for some environment
> variables to specify the locations of the client certificate and key
> (and the CA cert and CRL) - c.f. PGPASSFILE.
>
> That way not only could these be set by C programs but by any libpq user
> (I'm sure driver writers who use libpq don't want to have to bother with
> this stuff.) And we wouldn't need to change the API at all.
>

The problem I have with environment variables is that they tend not to be
application specific and almost always lead to configuration issues. As a
methodology for default configuration, it adds flexibility. Also, the
current configuration does not easily take in to consideration the idea
that different databases with different keys can be used from the same
system the same user.

Maybe we need to go even further and add it to the PQconnect API
sslkey=filename and sslcrt=filename in addition to sslmode?



-- 
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] [rfc,patch] PL/Proxy in core

[Tom Lane]

"Marko Kreen" <[EMAIL PROTECTED]> writes:
> How about following patch?  I have bison 2.3 and it seems not to do
> global allocation, so it should be fine.  There may be early exit
> with elog(ERRROR) inside so I'd like to avoid malloc() itself.

None of our other parsers fool with bison's memory allocation;
why does yours need to?

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] SSL and USER_CERT_FILE

[Andrew Dunstan]

Mark Woodward wrote:

I am using PostgreSQL's SSL support and the conventions for the key and
certifications don't make sense from the client perspective. Especially
under Windows.

I am proposing a few simple changes:

Adding two API
void PQsetSSLUserCertFileName(char *filename)
{
user_crt_filename = strdup(filename);
}
PQsetSSLUserKeyFileName(char *filename)
{
user_key_filename = strdup(filename);
}


  

[snip]

Any comments?

  



I think it would probably be much better to allow for some environment 
variables to specify the locations of the client certificate and key 
(and the CA cert and CRL) - c.f. PGPASSFILE.


That way not only could these be set by C programs but by any libpq user 
(I'm sure driver writers who use libpq don't want to have to bother with 
this stuff.) And we wouldn't need to change the API at all.


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

[HACKERS] SSL and USER_CERT_FILE

[Mark Woodward]

I am using PostgreSQL's SSL support and the conventions for the key and
certifications don't make sense from the client perspective. Especially
under Windows.

I am proposing a few simple changes:

Adding two API
void PQsetSSLUserCertFileName(char *filename)
{
user_crt_filename = strdup(filename);
}
PQsetSSLUserKeyFileName(char *filename)
{
user_key_filename = strdup(filename);
}

Adding two static vars in fe-secure.c

char *user_key_filename=NULL;
char *user_crt_filename=NULL;

In client_cert_cb(...)

Add:
if(user_crt_filename)
strncpy(fnbuf, sizeof(fnbuf), user_crt_filename);
else
snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);

and:

if(user_key_filename)
strncpy(fnbuf, sizeof(fnbuf), user_key_filename);
else
snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_KEY_FILE);


The purpose of these changes is to make it easier to configure SSL in an
application which uses libpq.

Any comments?

-- 
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] Can't t compile current HEAD

[Nikhils]

Hi,

On Thu, May 15, 2008 at 11:59 AM, Pavel Stehule <[EMAIL PROTECTED]>
wrote:

> > I always use a ~/.cvsrc containing
> >
> > cvs -z3
> > update -d -P
> > checkout -P
> >
>

My .cvsrc also includes:

diff -c

So that a patch I submit does not get bounced back for a non-context diff :)

Regards,
Nikhils
-- 
EnterpriseDB http://www.enterprisedb.com

Re: [HACKERS] [rfc,patch] PL/Proxy in core

[Marko Kreen]

On 5/15/08, Marko Kreen <[EMAIL PROTECTED]> wrote:
> On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
>  > "Marko Kreen" <[EMAIL PROTECTED]> writes:
>  >  > Hmm.. Now that I think about it, in my effort to remove malloc() calls
>  >  > in both scanner and parser I told bison to use alloca().  Is it 
> portability
>  >  > concern?
>  >
>  > Yes.
>
>
> How about following patch?  I have bison 2.3 and it seems not to do
>  global allocation, so it should be fine.  There may be early exit
>  with elog(ERRROR) inside so I'd like to avoid malloc() itself.
>
>  Is there some older bison that keeps allocations around?
>  They would need bit more work...
>
>  --- src/parser.y14 May 2008 12:25:00 -  1.7
>  +++ src/parser.y15 May 2008 07:34:53 -
>  @@ -24,7 +24,9 @@
>   void plproxy_yy_scan_bytes(const char *bytes, int len);
>
>   /* avoid permanent allocations */
>  -#define YYSTACK_USE_ALLOCA 1
>  +#define YYMALLOC palloc
>  +#define YYFREE pfree
>  +
>   /* remove unused code */
>   #define YY_LOCATION_PRINT(File, Loc) (0)
>   #define YY_(x) (x)
>
>  I will roll new full patch when more comments have appeared.

Checked bison 1.875, and it does not use YYMALLOC/YYFREE.
But luckily its allocation pattern seems sane, so following should work:

--- src/parser.y14 May 2008 12:25:00 -  1.7
+++ src/parser.y15 May 2008 08:18:14 -
@@ -24,7 +24,9 @@
 void plproxy_yy_scan_bytes(const char *bytes, int len);

 /* avoid permanent allocations */
-#define YYSTACK_USE_ALLOCA 1
+#define malloc palloc
+#define free pfree
+
 /* remove unused code */
 #define YY_LOCATION_PRINT(File, Loc) (0)
 #define YY_(x) (x)

-- 
marko

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

Re: [HACKERS] [rfc,patch] PL/Proxy in core

[Marko Kreen]

On 5/15/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Marko Kreen" <[EMAIL PROTECTED]> writes:
>  > Hmm.. Now that I think about it, in my effort to remove malloc() calls
>  > in both scanner and parser I told bison to use alloca().  Is it portability
>  > concern?
>
> Yes.

How about following patch?  I have bison 2.3 and it seems not to do
global allocation, so it should be fine.  There may be early exit
with elog(ERRROR) inside so I'd like to avoid malloc() itself.

Is there some older bison that keeps allocations around?
They would need bit more work...

--- src/parser.y14 May 2008 12:25:00 -  1.7
+++ src/parser.y15 May 2008 07:34:53 -
@@ -24,7 +24,9 @@
 void plproxy_yy_scan_bytes(const char *bytes, int len);

 /* avoid permanent allocations */
-#define YYSTACK_USE_ALLOCA 1
+#define YYMALLOC palloc
+#define YYFREE pfree
+
 /* remove unused code */
 #define YY_LOCATION_PRINT(File, Loc) (0)
 #define YY_(x) (x)

I will roll new full patch when more comments have appeared.

-- 
marko

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

[HACKERS] Buildfarm: cardinal down for maintenance.

[Gevik Babakhani]

The "cardinal" in pgbuildfarm has been taken down for server/hardware
maintenance.

Regards,
Gevik 


-- 
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] [rfc,patch] PL/Proxy in core

[Tom Lane]

"Marko Kreen" <[EMAIL PROTECTED]> writes:
> Hmm.. Now that I think about it, in my effort to remove malloc() calls
> in both scanner and parser I told bison to use alloca().  Is it portability
> concern?

Yes.

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] Would like to sponsor implementation of MATERIALIZED VIEWS

[js]

Hi,

as I posted already in the general newsgroup our company has decided
that we would like to sponsor the implementation of materialized views
for Postgres.
However at the moment we have no idea about the complexity of the
implementation and therefore what the cost would be. Since the point
is already on the TODO List, are there already any (rough) estimates?

The TODO List reads:
"Right now materialized views require the user to create triggers on
the main table to keep the summary table current. SQL syntax should be
able to manager the triggers and summary table automatically."
And this is what we need.

"A more sophisticated implementation would automatically retrieve from
the summary table when the main table is referenced, if possible."
If this means that e.g. a query would "know by itself" that it could
get the data from the view instead of from the main table, then we
don't need this feature at the moment. Otherwise: Could anyone
explain?

Regards,
Jan

--
Jan Strube
www.deriva.de

-- 
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] missing $PostgreSQL:$

[Tom Lane]

Andrew Dunstan <[EMAIL PROTECTED]> writes:
> second pass. There are 130 files in this list. Offhand I'd say the vast 
> majority should have markers.

Yeah, that list looks reasonably sane.  The main thing I was worried
about was not plastering PostgreSQL markers on files that are simply
imported from an upstream source, like the zic database and the
libstemmer files.

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] Can't t compile current HEAD

[Pavel Stehule]

2008/5/15 Tom Lane <[EMAIL PROTECTED]>:
> "Pavel Stehule" <[EMAIL PROTECTED]> writes:
>> I got some errors:
>
>> In file included from gistget.c:20:
>> ../../../../src/include/pgstat.h:15:36: error:
>> portability/instr_time.h: není souborem ani adresářem
>
> I'll bet you forgot -d in your last cvs update command.

yes, I forgot it

>
> I always use a ~/.cvsrc containing
>
> cvs -z3
> update -d -P
> checkout -P
>

good advice,

thank you
Pavel

> The -z3 might be an obsolete habit depending on what your network
> connection is like ... but the other options are pretty much *required*
> for sane behavior.
>
>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] Can't t compile current HEAD

[Tom Lane]

"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> I got some errors:

> In file included from gistget.c:20:
> ../../../../src/include/pgstat.h:15:36: error:
> portability/instr_time.h: není souborem ani adresářem

I'll bet you forgot -d in your last cvs update command.

I always use a ~/.cvsrc containing

cvs -z3
update -d -P
checkout -P

The -z3 might be an obsolete habit depending on what your network
connection is like ... but the other options are pretty much *required*
for sane behavior.

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] [rfc,patch] PL/Proxy in core

[Marko Kreen]

On 5/15/08, Josh Berkus <[EMAIL PROTECTED]> wrote:
> On Wednesday 14 May 2008 13:29, Marko Kreen wrote:
>  > - SGML documentation.
>  > - Makefile review.
>  > - Integrate regression tests into main test framework.
>
> Has PL/proxy been tested on other OSes?  FreeBSD/Solaris/Windows?

It definitely works on Linux and MacOS.  I've seen ports for *BSD.
I think any unix-like OS-es should work fine.

In fact, only syscalls it does on its own are gettimeofday() and poll()
[or select()], otherwise it calls either core or libpq functions.
So I see no reason why it shouldnt already work on Windows.

The biggest portability problem thus far has been scanner.l, which at
the beginning was written for flex 2.5.33+, but 2.5.4 is still pretty
widespread.  But this I fixed in 2.0.3.

Hmm.. Now that I think about it, in my effort to remove malloc() calls
in both scanner and parser I told bison to use alloca().  Is it portability
concern?  Easy to fix, just need to be careful not to create memleaks.

-- 
marko

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