date:20080331

Re: [HACKERS] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]

2008-03-31 Thread NikhilS

Hi Alex,

On Sun, Mar 30, 2008 at 7:10 AM, Alex Hunsaker [EMAIL PROTECTED] wrote:

 (trimmed cc's)

 Find attached inherited_constraint_v2.patch

 Changes since v1:
 -rebased against latest HEAD
 -changed enum { Anum_pg_constraint_... } back into #define
 Anum_pg_constraint_...
 -remove whitespace damage I added
 -fixed regression tests I added to be more robust
 -fixed
   create table ac (a int constraint check_a check (a  0));
   create table bc (a int constraint check_a check (a  0)) inherits (ac);
   so it properly works (removed crud I put into
 AddRelationRawConstraints and created a proper fix in DefineRelation)


I was taking a look at this patch to add the pg_dump related changes. Just
wanted to give you a heads up as this patch crashes if we run make
installcheck. Seems there is an issue introduced in the CREATE TABLE
REFERENCES code path due to your patch (this is without my pg_dump changes
just to be sure).  Looks like some memory overwrite issue. The trace is as
follows:

Core was generated by `postgres: nikhils regression [local] CREATE
TABLE '.
Program terminated with signal 11, Segmentation fault.
#0  0x08378024 in AllocSetCheck (context=0xa060368) at aset.c:1112
1112if (dsize  0  dsize  chsize 
*chdata_end != 0x7E)
(gdb) bt
#0  0x08378024 in AllocSetCheck (context=0xa060368) at aset.c:1112
#1  0x0837704f in AllocSetDelete (context=0xa060368) at aset.c:487
#2  0x083783c2 in MemoryContextDelete (context=0xa060368) at mcxt.c:196
#3  0x083797fb in PortalDrop (portal=0xa0845bc, isTopCommit=0 '\0') at
portalmem.c:448
#4  0x08281939 in exec_simple_query (
query_string=0xa07e564 CREATE TABLE enumtest_child (parent rainbow
REFERENCES enumtest_parent);) at postgres.c:992
#5  0x082857d4 in PostgresMain (argc=4, argv=0x9ffbe28, username=0x9ffbcc4
nikhils) at postgres.c:3550
#6  0x0824917b in BackendRun (port=0xa003180) at postmaster.c:3204
#7  0x082486a2 in BackendStartup (port=0xa003180) at postmaster.c:2827
#8  0x08245e9c in ServerLoop () at postmaster.c:1271
#9  0x082457fd in PostmasterMain (argc=3, argv=0x9ff9c60) at postmaster.c
:1019
#10 0x081e1c03 in main (argc=3, argv=0x9ff9c60) at main.c:188


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

Re: [HACKERS] Commit fest status

2008-03-31 Thread Gregory Stark

Tom Lane [EMAIL PROTECTED] writes:

 Well, it's the end of March, and I'm already starting to feel like we've
 been commit-festing forever :-(.  At this point I see only one remaining
 patch that seems likely to go in without any further discussion --- that's
 Pavel's plpgsql EXECUTE USING thing.  

A huge *thank you* for all your efforts. I know it's not the fun part of
your work.

 However, we've got boatloads of stuff that needs discussion and
 consensus-achievement. Please take a look at the queue
 http://momjian.us/cgi-bin/pgpatches

Alvaro tried to dump this list into:

http://wiki.postgresql.org/wiki/CommitFest:March

 and comment where you can.  Remember that substantive comments or
 reviews should go to the mailing lists --- you can add annotations to
 that page if you want, but they'll be ephemeral.

And the patch authors are unlikely to see them unless they're also doing
reviews.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

-- 
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] Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in P

2008-03-31 Thread Heikki Linnakangas

sanjay sharma wrote:

However there are certain fetures which are becoming key for putting postgres
in areas where strong regulatory compliance is required.TDE is very helpful in
storing data where there is strict privacy compliance requirement for example
e.Government and e.Health. All columns of personal profile/health data do not
need same level of security for all users and applications. Selective data
encryption is very handy in an architecture where different applications are
pulling data from a central data repository for processing and presenting to
their users or where different users are changing different part of data set in
central repository. These departmental applications may contain keys for
decrypting and looking at only those columns needed by their users. Encrypting
just needed column takes care of compliance requirement down the line in
backups and archives.

You could implement that using views and contrib/pgcrypto. Create a view
on the underlying table that encrypts/decrypts the data on access.

I'm not sure who the encryption is supposed to protect from in this
scenario. From the superuser of the database server? It isn't really
suitable for that: the way you describe it, the encryption/decryption is
done in the server, so a malicious superuser that has full access to the
server can still capture the data before it's encrypted, and can also
recover the key from the running server, by crawling through system
memory or installing hacked software to print it out.

It's better than nothing, as it does protect from a casual non-malicious
observer, and it does protect the backups, but what I'd rather see is a
system where the database server never sees the data in plaintext. You
could do the encryption/decryption in the client, perhaps in the driver
so that it's transparent to the application.

I'm not familiar with the compliance requirements you refer to. What
exactly is required?

Another area where I would like to put a RFC is Auditing. A flag at the database level (conf file) or in DDL which puts audit columns ( created_by, creation_date, last_updated_by, last_update_date) on tables and automatically populates them would be a very nice standard feature. Currently this needs code/trigger to be duplicated at each table which is a big grunt. At furthur higher level a way to audit data access/view for regulatory complinace like HIPPA is also needed.This should not be copy of Oracle FGA which has its own limitations.

This could be implemented fairly easily as an external tool that queries
the system catalogs, and adds the required columns and triggers.

--
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] jaguar is failing

2008-03-31 Thread ohp

On Sun, 30 Mar 2008, Tom Lane wrote:

 Date: Sun, 30 Mar 2008 15:37:51 -0400
 From: Tom Lane [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Cc: pgsql-hackers list pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] jaguar is failing

 [EMAIL PROTECTED] writes:
  As you can see here:
  http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=jaguarbr=HEAD
  Jaguar has been failing for two days while centaur is ok.

 I asked you to look into that yesterday
 http://archives.postgresql.org/pgsql-hackers/2008-03/msg01216.php
I saw it thid moring in the digest

 The copy of that mail that went directly to you bounced, with an error
 suggesting that your mail filtering setup has been broken for more
 than a year (you're depending on a blacklist server that ceased
 service in Dec 2006).  If you want, I'll post the bounce I expect
 to get from this.

Found the gulty and fired him :)
  The only diff is that jaguar is compiling with -DCLOBBER_CACHE_ALWAYS
  Is that related?

 Hmm, that might be the needed clue --- thanks for the reminder.

   regards, tom lane

-- 
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE   +33-6-07-63-80-64 (GSM)
FRANCE  Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)

-- 
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] [PATCHES] Minimum selectivity estimate for LIKE 'prefix%'

2008-03-31 Thread Peter Eisentraut

Am Donnerstag, 6. März 2008 schrieb Tom Lane:
 What I propose doing about this is a small variant on Peter's original
 suggestion: compute the estimated selectivity for
 col = 'prefix'
 and clamp the result of prefix_selectivity to be at least that.

OK, first results with this patch are in: The selectivity estimations are 
adjusted nicely, but the cost calculation doesn't change at all.  Before:

Index Scan using foo_idx_3 on foo foo (cost=0.01..6.03 rows=1 width=8)

After:

Index Scan using foo_idx_3 on foo foo (cost=0.01..6.03 rows=627 width=8)

How is that possible?

Btw., the corresponding query plan for the LIKE 'constant' case is:

Index Scan using foo_idx_3 on foo foo (cost=0.00..2527.84 rows=627 width=8)

This is what we had hoped to get in the after case.

-- 
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] jaguar is failing

2008-03-31 Thread ohp

On Sun, 30 Mar 2008, Tom Lane wrote:

 Date: Sun, 30 Mar 2008 23:36:24 -0400
 From: Tom Lane [EMAIL PROTECTED]
 To: [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org
 Subject: Re: [HACKERS] jaguar is failing

 I wrote:
  [EMAIL PROTECTED] writes:
  The only diff is that jaguar is compiling with -DCLOBBER_CACHE_ALWAYS
  Is that related?

  Hmm, that might be the needed clue --- thanks for the reminder.

 Yup, that was it --- was able to duplicate the problem with
 -DCLOBBER_CACHE_ALWAYS.  So I think jaguar's efforts already paid
 for themselves ...

   regards, tom lane

Great!
Did you commit a patch already, do you want me to rerun the build?
-- 
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE   +33-6-07-63-80-64 (GSM)
FRANCE  Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)

-- 
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] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong

2008-03-31 Thread Pavan Deolasee

On Mon, Mar 31, 2008 at 1:33 PM, Stuart Brooks [EMAIL PROTECTED] wrote:
 I have a table with about 15 million rows which is constantly having
  tuples added to the head and deleted in blocks from the tail to maintain
  the size. The dead tuple count in pg_stat_user_tables tracks the deleted
  rows fairly accurately until an auto-ANALYZE is done in the background
  at which point the value it calculates is wrong by a factor of 2-3 times
  (calculated value is 30-50% of the correct value)

(copying -hackers)

Seems like the redirected-dead line pointers are playing spoil-sport here.
In this particular example, the deleted tuples may get truncated to
redirected-dead line pointers. Analyze would report them as empty
slots and not as dead tuples. So in the worst case, if all the deleted
tuples are already truncated to redirected-dead line pointers, analyze
may report zero dead tuple count.

This is a slightly tricky situation because in normal case we might want
to delay autovacuum to let subsequent UPDATEs in the page to reuse
the space released by  the deleted tuples. But in this particular example,
delaying autovacuum is not a good thing because the relation would
just keep growing.

I think we should check for redirected-dead line pointers in analyze.c
and report them as dead tuples. The other longer term alternative
could be to track redirected-dead line pointers and give them some
weightage while deciding on autovacuum. We can also update the
FSM information of a page when its pruned/defragged so that the page
can also be used for subsequent INSERTs or non-HOT UPDATEs in
other pages. This might be easier said than done.


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] WIP: CASE statement for PL/pgSQL

2008-03-31 Thread Pavel Stehule

Hello

I finished this patch.

Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php

It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).

CASE statements is parsed and transformed to CASE expression and
statements paths. Result of CASE expression is used as index to array
of statements paths. It's fast but I have to once time reparse SQL
queries - it generate about 150 lines code, because I need to get all
parameter's positions. It's one disadvantage. On second hand, this
statement needs only one expression evaluation.

Sample:

CREATE OR REPLACE FUNCTION foo(int)
RETURNS void AS $$
BEGIN
  CASE $1
  WHEN 1,2,3 THEN
 RAISE NOTICE '1,2';
 RAISE NOTICE '3';
  WHEN 4 THEN
 RAISE NOTICE '4';
  ELSE
 RAISE NOTICE 'other than 1,2,3,4';
  END CASE;
  RETURN;
END;
$$ LANGUAGE plpgsql;

This statement is transformated to:
three statement paths:
[0]
 RAISE NOTICE 'other than 1,2,3,4';
[1]
 RAISE NOTICE '1,2';
 RAISE NOTICE '3';
[2]
 RAISE NOTICE '4';

and case expression
CASE $1
  WHEN 1 THEN 1
  WHEN 2 THEN 1
  WHEN 3 THEN 1
  WHEN 4 THEN 2
END;

When result is NULL then it uses 0 path.

Questions:
a) is possible to use SQL scanner? Now, scanner isn't directly used everywhere.

any notes and comments are welcome

Regards
Pavel Stehule
*** ./gram.y.orig	2008-03-28 17:33:45.0 +0100
--- ./gram.y	2008-03-31 13:46:08.0 +0200
***
*** 15,23 
   */
  
  #include plpgsql.h
- 
  #include parser/parser.h
! 
  
  static PLpgSQL_expr		*read_sql_construct(int until,
  			int until2,
--- 15,24 
   */
  
  #include plpgsql.h
  #include parser/parser.h
! #include errno.h
! #include ctype.h
! #include string.h
  
  static PLpgSQL_expr		*read_sql_construct(int until,
  			int until2,
***
*** 46,52 
  static	char			*check_label(const char *yytxt);
  static	void			 check_labels(const char *start_label,
  	  const char *end_label);
! 
  %}
  
  %name-prefix=plpgsql_yy
--- 47,54 
  static	char			*check_label(const char *yytxt);
  static	void			 check_labels(const char *start_label,
  	  const char *end_label);
! static PLpgSQL_stmt	*make_case(int lineno, PLpgSQL_expr *case_expr, 
! List *when_clause_list, List *else_stmts);
  %}
  
  %name-prefix=plpgsql_yy
***
*** 79,84 
--- 81,91 
  			char *end_label;
  			List *stmts;
  		}		loop_body;
+ 		struct
+ 		{	
+ 			List *expr_list;
+ 			PLpgSQL_expr	*expr;
+ 		}		when_expr;
  		List	*list;
  		PLpgSQL_type			*dtype;
  		PLpgSQL_datum			*scalar;	/* a VAR, RECFIELD, or TRIGARG */
***
*** 95,100 
--- 102,108 
  		PLpgSQL_nsitem			*nsitem;
  		PLpgSQL_diag_item		*diagitem;
  		PLpgSQL_stmt_fetch		*fetch;
+ 		PLpgSQL_when_clause		*whenclause;
  }
  
  %type declhdr decl_sect
***
*** 109,115 
  %type str		decl_stmts decl_stmt
  
  %type expr	expr_until_semi expr_until_rightbracket
! %type expr	expr_until_then expr_until_loop
  %type expr	opt_exitcond
  
  %type ival	assign_var
--- 117,123 
  %type str		decl_stmts decl_stmt
  
  %type expr	expr_until_semi expr_until_rightbracket
! %type expr	expr_until_then expr_until_loop opt_expr_until_when
  %type expr	opt_exitcond
  
  %type ival	assign_var
***
*** 128,133 
--- 136,145 
  %type stmt	stmt_return stmt_raise stmt_execsql stmt_execsql_insert
  %type stmt	stmt_dynexecute stmt_for stmt_perform stmt_getdiag
  %type stmt	stmt_open stmt_fetch stmt_move stmt_close stmt_null
+ %type stmt	stmt_case
+ %type when_expr case_when_expr
+ %type whenclause when_clause
+ %type list	when_clause_list opt_case_default
  
  %type list	proc_exceptions
  %type exception_block exception_sect
***
*** 154,159 
--- 166,172 
  %token	K_ASSIGN
  %token	K_BEGIN
  %token	K_BY
+ %token	K_CASE
  %token	K_CLOSE
  %token	K_CONSTANT
  %token	K_CONTINUE
***
*** 611,616 
--- 624,631 
  		{ $$ = $1; }
  | stmt_if
  		{ $$ = $1; }
+ | stmt_case
+ 		{ $$ = $1; }
  | stmt_loop
  		{ $$ = $1; }
  | stmt_while
***
*** 809,814 
--- 824,869 
  	}
  ;
  
+ stmt_case		: K_CASE lno opt_expr_until_when when_clause_list opt_case_default K_END K_CASE ';'
+ 	{
+ 		$$ = make_case($2, $3, $4, $5);
+ 	}
+ ;
+ 
+ opt_case_default	:
+ 	{
+ 		$$ = NIL;
+ 	}
+ | K_ELSE proc_stmts
+ 	{
+ 		$$ = $2;
+ 	}
+ ;
+ 
+ when_clause_list	: when_clause_list when_clause
+ 	{
+ 		$$ = lappend($1, $2);
+ 	}
+ | when_clause
+ 	{
+ 		$$ = list_make1($1);
+ 	}
+ ;
+ 
+ when_clause		: K_WHEN lno case_when_expr proc_stmts
+ 	{
+ 		PLpgSQL_when_clause *new = palloc0(sizeof(PLpgSQL_when_clause));
+ 
+ 		new-cmd_type		= PLPGSQL_STMT_WHEN_CLAUSE;
+ 		new-lineno	= $2;
+ 		new-when_expr	= $3.expr;
+ 		new-when_expr_list	= $3.expr_list;
+

Re: [HACKERS] first time hacker ;) messing with prepared statements

2008-03-31 Thread PFC


* Server restart and assorted like failover (you need to redo a
  global prepare).


Hmm?  He's proposing storing the info in a system catalog.  That hardly
seems volatile; it'll certainly survive a server restart.


Yes, it's in a system catalog.


I agree with the point that this isn't completely transparent to
applications, but if an app is already using named prepared statements
it would surely be a pretty small matter to make it use this feature.
The app code would likely get simpler instead of more complex, since
you'd stop worrying about whether a given statement had been prepared
yet in the current session.


Thanks. That was the idea behing this hack...


I'm having a problem with the terminology here, since AFAICT what your
patch does is exactly not a global prepare --- there is no permanently
stored cached plan.  That's a good thing probably, but it seems like
the feature needs to be described differently.


	Sure, but I couldn't come up with a suitable name at the time... perhaps  
CREATE STATEMENT (and DROP STATEMENT) ? This would describe it better  
(especially the DROP, because GLOBAL DEALLOCATE is a rather bad name,  
since it doesn't actually deallocate anything...)



I'm also pretty dubious about storing raw text in that catalog.  In the
first place, while I've not looked at your patch, I expect you are
pulling the raw text from debug_query_string.  That won't work in cases
where multiple SQL commands were submitted in one query string.


	LOL, you are right, I had tested with multiple queries on the same line  
from psql, but psql apparently splits the queries, when I feed multiple  
queries from PHP, one of them being GLOBAL PREPARE, it fails.



In the
second place, raw-text SQL commands will be subject to a whole lot of
ambiguity at parse time.  If for instance another session tries to use
the command with a different search_path or standard_conforming_string
setting, it'll get different results.  While I can think of use-cases
for that sort of behavior, it seems like mostly a bad idea.


You're right.


I'm thinking that a more appropriate representation would use stored
parse trees, the same as we do in pg_rewrite, and with the same
dependency information so that a stored statement couldn't outlive the
objects it depends on.


	Do the parse tree store fully qualified schema.table or  
schema.function ?
	I mean, if table T is mentioned in a parse tree which is stored, and the  
table is later dropped and recreated... or a column dropped... what  
happens ? Dropping the statement would seem more logical, since it would  
probably no longer be valid...



Another area that could do with more thought is the hard-wired
association between statement ownership and accessibility.  That's
likely to be pretty inconvenient in a lot of cases, particularly
systems that use role membership heavily.


Yes, need to think about that.


I also wonder whether statements should belong to schemas...


Since they are basically an extremely simple form of a function, why 
not ?
	(but since part of the goodness on prepared statements is that they are  
stored in a fast hash cache, wouldn't that add too much overhead ?)


Thanks for the helpful advice.

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

Re: Status of GIT mirror (Was [HACKERS] having problem in rsync'ing cvs)

2008-03-31 Thread Aidan Van Dyk

* Brendan Jurd [EMAIL PROTECTED] [080328 11:16]:
 On 29/03/2008, Aidan Van Dyk [EMAIL PROTECTED] wrote:
  * Brendan Jurd [EMAIL PROTECTED] [080327 16:36]:
   
Looking at the CVS logs, there was definitely commit action in that
timeframe, but none of it is showing up on the git shortlog.
 
  OK, so it should all be valid again.
 
 
 Looks good to me.  Thanks Aidan.

And, of course, after fixing the missing one, I forgot to enable the
cronjob *again*...

It's running now again.

Really.

a.


-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

49 matches

Mail list logo