Re: [HACKERS] Hot standby and removing VACUUM FULL

[Simon Riggs]

On Wed, 2009-11-25 at 03:12 +, Greg Stark wrote:
> On Wed, Nov 25, 2009 at 2:10 AM, Tom Lane  wrote:
> > As long as there's not anything the master actually does differently
> > then I can't see where there'd be any performance testing to do.  What's
> > bothering me about this is that it seems likely that we'll find places
> > where the master has to do things differently.  I'd rather we made the
> > status visible; if we get through a release cycle without needing to
> > check it, we can always take the function out again.  But if we don't,
> > and then find out midway through the 8.5 release cycle that we need to
> > be able to check it, things could be a bit sticky.
> 
> Well the only thing that's been discussed is having vacuum require a
> minimum age before considering a transaction visible to all to reduce
> the chance of conflicts on cleanup records. But that would require an
> actual tunable, not just a flag. And it's something that could
> conceivably be desirable even if you're not running a HS setup (if
> someone ever reimplements time travel for example).

I've added "vacuum_delay_cleanup_age = N", default 0 to implement this.
New name please.

This alters the return value of GetOldestXmin() and the setting of
RecentGlobalXmin by the value requested.

I've made this a SIGHUP, since it only has value if it affects all
users.

-- 
 Simon Riggs   www.2ndQuadrant.com


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

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Jeff Davis]

On Mon, 2009-11-30 at 14:14 +0100, Joachim Wieland wrote:
> I have a new version that deals with this problem but I need to clean
> it up a bit. I am planning to post it this week.

Are planning to send a new version soon?

As it is, we're 12 days from the end of this commitfest, so we don't
have much time to hand the patch back and forth before we're out of
time.

Regards,
Jeff Davis


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

Re: [HACKERS] [GENERAL] Installing PL/pgSQL by default

[Tom Lane]

Andrew Dunstan  writes:
> Before we go too far with this, I'd like to know how we will handle the 
> problems outlined here: 
> 

Hm, I think that's only a problem if we define it to be a problem,
and I'm not sure it's necessary to do so.  Currently, access to PL
languages is controlled by superusers.  You are suggesting that if
plpgsql is installed by default, then access to it should be controlled
by non-superuser DB owners instead.  Why do we have to move the
goalposts in that direction?  It's not like we expect that DB owners
should control access to other built-in facilities, like int8 or
pg_stat_activity for example.  The argument against having plpgsql
always available is essentially one of security risks, and I would
expect that most installations think that security risks are to be
managed by superusers.

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] operator exclusion constraints

[tomas]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Thu, Dec 03, 2009 at 08:38:06PM -0800, David E. Wheeler wrote:

[...]

> "Whatever constraints"? "Operator Whatevers"? "WhatEVER"s? I like it.

drigting serioulsy off-topic: there's precedent for that in the most
venerable piece of free software; TeX has a "whatsit node" (basically an
extension mechanism).

Regards
- -- tomás
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLGKM7Bcgs9XrR2kYRAuHFAJ0ZZYzlXHJEgwEbsraAlKVI58yLAgCfU4Cz
n+0vobY0HxROigSHUGog7QI=
=MWH+
-END PGP SIGNATURE-

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

[HACKERS] [PATCH] Largeobject Access Controls (r2460)

[KaiGai Kohei]

The attached patch is an updated revision of Largeobject Access Controls.

List of updates:
* rebased to the latest CVS HEAD

* SGML documentation fixes:
  - The future version number was replaced as:
"In the 8.4.x series and earlier release, ..."
  - Other strange English representations and typoes were fixed.

* Fixed OID conflicts in system catalog definition.
  The new TOAST relation for pg_trigger used same OID number with
  pg_largeobject_metadata.

* Fixed incorrect error code in pg_largeobject_ownercheck().
  It raised _UNDEFINED_FUNCTION, but should be _UNDEFINED_OBJECT.

* Renamed GUC parameter to "lo_compat_privileges" from
  "large_object_privilege_checks".

* pg_largeobject_aclmask() and pg_largeobject_aclcheck(), not
  take an argument of snapshot, were removed.
  Currently, the caller provide an appropriate snapshot them.

Thanks,

Jaime Casanova wrote:
> 2009/11/12 KaiGai Kohei :
>> The attached patch is a revised version of large object privileges
>> based on the feedbacks at the last commit fest.
>>
> 
> please update the patch, it's giving an error when 'make check' is
> trying to "create template1" in initdb:
> 
> creating template1 database in
> /home/postgres/pg_releases/pgsql/src/test/regress/./tmp_check/data/base/1
> ... TRAP: FailedAssertion("!(reln->md_fd[forkNum] == ((void *)0))",
> File: "md.c", Line: 254)
> child process was terminated by signal 6: Aborted
> 
> 
> Meanwhile, i will make some comments:
> 
> This manual will be specific for 8.5 so i think all mentions to the
> version should be removed
> for example;
> 
> +In this version, a large object has OID of its owner, access permissions
> +and OID of the largeobject itself.
> 
> + Prior to the version 8.5.x release does not have any
> privilege checks on
> +   large objects.
> 
> the parameter name (large_object_privilege_checks) is confusing enough
> that we have to make this statements to clarify... let's think in a
> better less confuse name
> + Please note that it is not equivalent to disable all the security
> + checks corresponding to large objects.
> + For example, the lo_import() and
> + lo_export need superuser privileges independent
> + from this setting as prior versions were doing.
> 
> this will not be off by default? it should be for compatibility
> reasons... i remember there was a discussion about that but can't
> remember the conclusion
> 
> Mmm... One of them? the first?
> + The one is SELECT.
> 
> + Even if a transaction modified access rights and commit it, it is
> + not invisible from other transaction which already opened the large
> + object.
> 
> The other one, the second
> + The other is UPDATE.
> 
> 
> it seems there is an "are" that should not be there :)
> +
> + These functions are originally requires database superuser privilege,
> + and it allows to bypass the default database privilege checks, so
> + we don't need to check an obvious test twice.
> 
> a typo, obviously
> +For largeo bjects, this privilege also allows to read from
> +the target large object.
> 
> 
> We have two versions of these functions one that a recieve an SnapShot
> parameter and other that don't...
> what is the rationale of this? AFAIU, the one that doesn't receive
> SnapShot is calling the other one with SnapShotNow, can't we simply
> call it that way and drop the version of the functions that doesn't
> have that parameter?
> + pg_largeobject_aclmask(Oid lobj_oid, Oid roleid,
> +  AclMode mask, AclMaskHow how)
> 
> + pg_largeobject_aclcheck(Oid lobj_oid, Oid roleid, AclMode mode)
> 


-- 
OSS Platform Development Division, NEC
KaiGai Kohei 


sepgsql-02-blob-8.5devel-r2460.patch.gz
Description: application/gzip

-- 
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] operator exclusion constraints

[David E. Wheeler]

On Dec 3, 2009, at 6:26 PM, Robert Haas wrote:

> Yeah, I don't remember any such consensus either, but it's not a dumb
> name.  I have been idly wondering throughout this process whether we
> should try to pick a name that conveys the fact that these constraints
> are inextricably tied to the opclass/index machinery - but I'm not
> sure it's possible to really give that flavor in a short phrase, or
> that it's actually important to do so.  IOW... "whatever".  :-)

"Whatever constraints"? "Operator Whatevers"? "WhatEVER"s? I like 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] Re: [COMMITTERS] pgsql: Rewrite GEQO's gimme_tree function so that it always finds a

[Robert Haas]

On Wed, Dec 2, 2009 at 10:53 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Well, when I was testing, I believe I observed that an n-way join with
>> 1 cross join was slower to plan than an n-way join with no cross
>> joins.  ISTM that it should actually be faster, because you should
>> plan it like an (n-1)-way join and then do the cross join at the end.
>
> It's not entirely clear to me what case you're describing, but I wonder
> whether this was a "flat" join problem or restricted by the collapse
> limits.

Argh.  I can't reproduce exactly what I thought I was seeing before.
However, with the attached schema, geqo off, and the collapse
thresholds set to 100, "explain select * from bar2_view" and "explain
select * from bar3_view" have roughly the same run time.  They are
identical except that one of the join clauses has been omitted in the
second case.  One would think that the second case could be planned
faster, if we plan to just leave the cross join to the end.

(And in fact on my system if you remove bar8 from the second view
entirely the plan time improves by a factor of six.)

...Robert


join2.sql
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] [PATCH] Largeobject Access Controls (r2432)

[KaiGai Kohei]

Itagaki Takahiro wrote:
> KaiGai Kohei  wrote:
> 
>>> creating template1 database in
>>> /home/postgres/pg_releases/pgsql/src/test/regress/./tmp_check/data/base/1
>>> ... TRAP: FailedAssertion("!(reln->md_fd[forkNum] == ((void *)0))",
>>> File: "md.c", Line: 254)
>>> child process was terminated by signal 6: Aborted
>> I could not reproduce it.
> 
> I had the same trap before when I mistakenly used duplicated oids.
> Don't you add a new catalog with existing oids?
> src/include/catalog/duplicate_oids might be a help.

Thanks, Bingo!

toasting.h:DECLARE_TOAST(pg_trigger, 2336, 2337);

pg_largeobject_metadata.h:CATALOG(pg_largeobject_metadata,2336)

-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

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

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Itagaki Takahiro]

KaiGai Kohei  wrote:

> > creating template1 database in
> > /home/postgres/pg_releases/pgsql/src/test/regress/./tmp_check/data/base/1
> > ... TRAP: FailedAssertion("!(reln->md_fd[forkNum] == ((void *)0))",
> > File: "md.c", Line: 254)
> > child process was terminated by signal 6: Aborted
> 
> I could not reproduce it.

I had the same trap before when I mistakenly used duplicated oids.
Don't you add a new catalog with existing oids?
src/include/catalog/duplicate_oids might be a help.

Regards,
---
ITAGAKI Takahiro
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] [PATCH] Largeobject Access Controls (r2432)

[KaiGai Kohei]

Jaime Casanova wrote:
> 2009/11/12 KaiGai Kohei :
>> The attached patch is a revised version of large object privileges
>> based on the feedbacks at the last commit fest.
>>
> 
> please update the patch, it's giving an error when 'make check' is
> trying to "create template1" in initdb:
> 
> creating template1 database in
> /home/postgres/pg_releases/pgsql/src/test/regress/./tmp_check/data/base/1
> ... TRAP: FailedAssertion("!(reln->md_fd[forkNum] == ((void *)0))",
> File: "md.c", Line: 254)
> child process was terminated by signal 6: Aborted

I could not reproduce it.
Could you run "make clean", then "make check"?
Various kind of patches are merged under the commit fest, so some of them
changes definition of structures. If *.o files are already built based on
older definitions, it may refers incorrect addresses.


> Meanwhile, i will make some comments:
> 
> This manual will be specific for 8.5 so i think all mentions to the
> version should be removed
> for example;
> 
> +In this version, a large object has OID of its owner, access permissions
> +and OID of the largeobject itself.
> 
> + Prior to the version 8.5.x release does not have any
> privilege checks on
> +   large objects.

The conclusion is unclear for me.

Is "In the 8.4.x and prior release, ..." an ambiguous expression?
   ^

> the parameter name (large_object_privilege_checks) is confusing enough
> that we have to make this statements to clarify... let's think in a
> better less confuse name
> + Please note that it is not equivalent to disable all the security
> + checks corresponding to large objects.
> + For example, the lo_import() and
> + lo_export need superuser privileges independent
> + from this setting as prior versions were doing.

In the last commit fest, it was named "largeobject_compat_acl",
but it is not preferable for Tom Lane, so he suggested to rename it
into "large_object_privilege_checks".

Other candidates:
 - lo_compat_privileges  (<- my preference in this four)
 - large_object_compat_privs
 - lo_compat_access_control
 - large_object_compat_ac

I think "_compat_" should be contained to emphasize it is a compatibility
option.


> this will not be off by default? it should be for compatibility
> reasons... i remember there was a discussion about that but can't
> remember the conclusion

IIRC, we have no discussion about its default value, although similar topics
were discussed:

* what should be checked on creation of a large object?
 -> No need to check permission on its creation. It allows everyone to create
a new large object like current implementation.
(Also note that this behavior may be changed in the future.)

* DELETE should be checked on deletion of a large object?
 -> No. PgSQL checks ownership of the database objects on its deletion such
as DROP TABLE. The DELETE permission is checked when we delete contents
of a certain database object, not the database object itself.

> Mmm... One of them? the first?
> + The one is SELECT.
> 
> + Even if a transaction modified access rights and commit it, it is
> + not invisible from other transaction which already opened the large
> + object.
> 
> The other one, the second
> + The other is UPDATE.

I have no arguments about English expression.

BTW, "The one is ..., the other is ..." was a statement on textbook
to introduce two things. :-)

> it seems there is an "are" that should not be there :)
> +
> + These functions are originally requires database superuser privilege,
> + and it allows to bypass the default database privilege checks, so
> + we don't need to check an obvious test twice.
> 
> a typo, obviously
> +For largeo bjects, this privilege also allows to read from
> +the target large object.

Thanks, I see.

> We have two versions of these functions one that a recieve an SnapShot
> parameter and other that don't...
> what is the rationale of this? AFAIU, the one that doesn't receive
> SnapShot is calling the other one with SnapShotNow, can't we simply
> call it that way and drop the version of the functions that doesn't
> have that parameter?
> + pg_largeobject_aclmask(Oid lobj_oid, Oid roleid,
> +  AclMode mask, AclMaskHow how)
> 
> + pg_largeobject_aclcheck(Oid lobj_oid, Oid roleid, AclMode mode)

We have no reason other than cosmetic rationale.

In the current implementation, all the caller of pg_largeobejct_aclcheck_*()
needs to provides correct snapshot including SnapshotNow when read-writable.
When pg_aclmask() calls pg_largeobject_aclmask(), it is the only case that
caller assumes SnapshotNow shall be applied implicitly.

On the other hand, all the case when pg_largeobject_ownercheck() is called,
the caller assumes SnapshotNow is applied, so we don't have multiple versions.

So, I'll reorganize these APIs as follows:
 - pg_largeobject_aclmask_snapshot()
 - pg_largeobject_aclcheck_snapshot()
 - pg_largeobject_

Re: [HACKERS] operator exclusion constraints

[Robert Haas]

On Thu, Dec 3, 2009 at 7:42 PM, Jeff Davis  wrote:
> On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote:
>> I'm starting to go through this patch now.  I thought the consensus
>> was to refer to them as just "exclusion constraints"?  I'm not seeing
>> that the word "operator" really adds anything.
>
> I assume you're referring to the name used in documentation and error
> messages. I didn't see a clear consensus, but the relevant thread is
> here:
>
> http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis
>
> "Exclusion Constraints" is fine with me, as are the other options listed
> in that email.

Yeah, I don't remember any such consensus either, but it's not a dumb
name.  I have been idly wondering throughout this process whether we
should try to pick a name that conveys the fact that these constraints
are inextricably tied to the opclass/index machinery - but I'm not
sure it's possible to really give that flavor in a short phrase, or
that it's actually important to do so.  IOW... "whatever".  :-)

...Robert

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

[HACKERS] Format Typmod?

[David E. Wheeler]

Hey Hackers,

I just had reason to want the way that format_type converts type names (e.g., 
varchar => character varying) in pgTAP, but didn't want the namespace 
qualification (used by format_type() when the type in question is not visible). 
I figured out that I could get that conversion by simply casting the OID 
argument to regtype, but then to get the typmod, I still have to use 
format_type() like so:

CREATE OR REPLACE FUNCTION display_type ( OID, INTEGER )
RETURNS TEXT AS $$
SELECT $1::regtype || COALESCE(
substring(pg_catalog.format_type($1, $2), '[(][^)]+[)]$'),
'')
$$ LANGUAGE SQL;

Is there a saner way to do it than this? That is, is there a better way to get 
the typmod than by munging the value returned by format_type()?

Thanks,

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] First feature patch for plperl - draft [PATCH]

[David E. Wheeler]

On Dec 3, 2009, at 3:30 PM, Tim Bunce wrote:

> - New GUC plperl.on_perl_init='...perl...' for admin use.
> - New GUC plperl.on_trusted_init='...perl...' for plperl user use.
> - New GUC plperl.on_untrusted_init='...perl...' for plperlu user use.

Since there is no documentation yet, how do these work, exactly? Or should I 
just wait for the docs?

> - END blocks now run at backend exit (fixes bug #5066).
> - Stored procedure subs are now given names ($name__$oid).
> - More error checking and reporting.
> - Warnings no longer have an extra newline in the NOTICE text.
> - Various minor optimizations like pre-growing data structures.

Nice.

> I'm working on adding tests and documentation now, meanwhile I'd very
> much appreciate any feedback on the patch.
> 
> Tim.
> 
> p.s. Once this patch is complete I plan to work on patches that:
> - add quote_literal and quote_identifier functions in C.

I expect you can just use the C versions in PostgreSQL. They're in 
utils/builtins.h, along with quote_nullable(), which might also be useful to 
add.

> - generalize the Safe setup code to enable more control.
> - formalize namespace usage, moving things out of main::

Nice.

> - add a way to perform inter-sub calling (at least for simple cases).
> - possibly rewrite _plperl_to_pg_array in C.

Sounds great, Tim. I'm not really qualified to say anything about the C code, 
but I'd be happy to try it out once there are docs.

Best,

David



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

Re: [HACKERS] operator exclusion constraints

[Jeff Davis]

On Thu, 2009-12-03 at 19:00 -0500, Tom Lane wrote:
> I'm starting to go through this patch now.  I thought the consensus
> was to refer to them as just "exclusion constraints"?  I'm not seeing
> that the word "operator" really adds anything.

I assume you're referring to the name used in documentation and error
messages. I didn't see a clear consensus, but the relevant thread is
here:

http://archives.postgresql.org/message-id/1258227283.708.108.ca...@jdavis

"Exclusion Constraints" is fine with me, as are the other options listed
in that email.

Regards,
Jeff Davis


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

Re: [HACKERS] pgbench: new feature allowing to launch shell commands

[Michael Paquier]

I didn't send the good patch yesterday. => --;
Here is the latest version.
Regards,

-- 
Michael Paquier
NIPPON TELEGRAPH AND
TELEPHONE CORPORATION
NTT Open Source Software Center
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 8a6437f..0bc6bfe 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -159,6 +159,7 @@ typedef struct
 } Variable;
 
 #define MAX_FILES		128		/* max number of SQL script files allowed */
+#define SHELL_COMMAND_SIZE	256		/* maximum size allowed for shell command */
 
 /*
  * structures used in custom query mode
@@ -992,7 +993,168 @@ top:
 
 			st->listen = 1;
 		}
+		else if (pg_strcasecmp(argv[0], "setshell") == 0)
+		{
+			int		j,
+	retval,
+	retvalglob = 0;
+			char	res[64],
+	*var = NULL,
+	commandLoc[SHELL_COMMAND_SIZE];
+			FILE	*respipe = NULL;
+
+			/* construction of the command line with all the transmitted arguments */
+			retval = snprintf(commandLoc,SHELL_COMMAND_SIZE-1,"%s",argv[2]);
+			if (retval < 0
+|| retval > SHELL_COMMAND_SIZE-1)
+			{
+fprintf(stderr, "Error loading parameter by setshell: too many characters\n");
+st->ecnt++;
+return true;
+			}
+			if (argc > 2)
+			{
+for (j = 3; j < argc; j++)
+{
+	char *commandLoc2 = strdup(commandLoc);
 
+	/*
+	 * before adding the command argument, check if it is an existing :variable
+	 * and put the associated value
+	 */
+	if (*argv[j] == ':')
+	{
+		if ((var = getVariable(st, argv[j] + 1)) == NULL)
+		{
+			fprintf(stderr, "%s: undefined variable %s\n", argv[0], argv[j]);
+			st->ecnt++;
+			return true;
+		}
+		retval = snprintf(commandLoc,SHELL_COMMAND_SIZE-1,"%s %s", commandLoc2, var);
+	}
+	else
+	{
+		/* then continue building the command */
+		retval = snprintf(commandLoc,SHELL_COMMAND_SIZE-1,"%s %s", commandLoc2, argv[j]);
+	}
+	retvalglob += retval;
+	if (retval < 0
+		|| retvalglob > SHELL_COMMAND_SIZE-1)
+	{
+		fprintf(stderr, "Error loading parameter by setshell: too many characters\n");
+		free(commandLoc2);
+		st->ecnt++;
+		return true;
+	}
+	free(commandLoc2);
+}
+			}
+
+			/*
+			 * Data treatment
+			 * prototype: /setshell aid skewerand +additional arguments
+			 */
+			respipe = popen(commandLoc,"r");
+			if (respipe == NULL)
+			{
+fprintf(stderr, "%s: error launching shell script\n", argv[0]);
+st->ecnt++;
+return true;
+			}
+
+			if (fgets(res, sizeof(res), respipe) == NULL)
+			{
+fprintf(stderr, "%s: error getting parameter\n", argv[0]);
+st->ecnt++;
+return true;
+			}
+
+			retval = pclose(respipe);
+			if (retval == -1)
+			{
+fprintf(stderr, "%s: error closing shell script\n", argv[0]);
+st->ecnt++;
+return true;
+			}
+			/* Transform the parameter into an integer */
+			retval = atoi(res);
+			if (retval == 0)
+			{
+fprintf(stderr, "%s: error input integer\n", argv[0]);
+st->ecnt++;
+return true;
+			}
+			/* ready to put the variable */
+			snprintf(res, sizeof(res), "%d", retval);
+
+			if (putVariable(st, argv[1], res) == false)
+			{
+fprintf(stderr, "%s: out of memory\n", argv[0]);
+st->ecnt++;
+return true;
+			}
+#ifdef DEBUG
+			printf("shell parameter name: %s, value: %s\n", argv[1], res);
+#endif
+			st->listen = 1;
+		}
+		else if (pg_strcasecmp(argv[0], "shell") == 0)
+		{
+			int		j,
+	retval,
+	retvalglob;
+			char	commandLoc[SHELL_COMMAND_SIZE],
+	*var = NULL;
+
+			retval = snprintf(commandLoc,SHELL_COMMAND_SIZE-1,"%s",argv[1]);
+			if (retval < 0
+|| retval > SHELL_COMMAND_SIZE-1)
+			{
+fprintf(stderr, "Error launching shell command: too many characters\n");
+st->ecnt++;
+return true;
+			}
+			retvalglob = retval;
+
+			for (j = 2; j < argc; j++)
+			{
+char *commandLoc2 = strdup(commandLoc);
+/* before building the command analyze if it is a pgbench variable already defined */
+if (*argv[j] == ':')
+{
+	if ((var = getVariable(st, argv[j] + 1)) == NULL)
+	{
+		fprintf(stderr, "%s: undefined variable %s\n", argv[0], argv[j]);
+		st->ecnt++;
+		return true;
+	}
+	retval = snprintf(commandLoc,SHELL_COMMAND_SIZE-1,"%s %s", commandLoc2, var);
+}
+else
+{
+	/* Then build the command */
+	retval = snprintf(commandLoc,SHELL_COMMAND_SIZE-1,"%s %s", commandLoc2, argv[j]);
+}
+retvalglob += retval;
+if (retval < 0
+	|| retvalglob > SHELL_COMMAND_SIZE-1)
+{
+	fprintf(stderr, "Error launching shell command: too many characters\n");
+	free(commandLoc2);
+	st->ecnt++;
+	return true;
+}
+free(commandLoc2);
+			}
+			retval = system(commandLoc);
+			if (retval < 0)
+			{
+fprintf(stderr, "Error launching shell command: command not launched\n");
+st->ecnt++;
+return true;
+			}
+			st->listen = 1;
+		}
 		goto top;
 	}
 
@@ -1313,6 +1475,22 @@ process_commands(char *buf)
 			

Re: [HACKERS] Installing PL/pgSQL by default

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> Tom Lane wrote:
> > Bruce Momjian  writes:
> >   
> >> One problem is that because system oids are used, it isn't possible to
> >> drop the language:
> >> I assume we still want to allow the language to be uninstalled, for
> >> security purposes.
> >> 
> >
> > Yes.  That behavior is not acceptable.  Why aren't you just adding
> > a CREATE LANGUAGE call in one of the initdb scripts?
> >
> > 
> >   
> 
> Before we go too far with this, I'd like to know how we will handle the 
> problems outlined here: 
> 

Oh, I forgot about that issue.  FYI, I believe several packages of
Postgres already pre-install plpgsql, or at least allow it as an option.

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

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

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

Re: [HACKERS] Installing PL/pgSQL by default

[Andrew Dunstan]

Tom Lane wrote:

Bruce Momjian  writes:
  

One problem is that because system oids are used, it isn't possible to
drop the language:
I assume we still want to allow the language to be uninstalled, for
security purposes.



Yes.  That behavior is not acceptable.  Why aren't you just adding
a CREATE LANGUAGE call in one of the initdb scripts?


  


Before we go too far with this, I'd like to know how we will handle the 
problems outlined here: 



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] Installing PL/pgSQL by default

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > One problem is that because system oids are used, it isn't possible to
> > drop the language:
> > I assume we still want to allow the language to be uninstalled, for
> > security purposes.
> 
> Yes.  That behavior is not acceptable.  Why aren't you just adding
> a CREATE LANGUAGE call in one of the initdb scripts?

Which scripts?  initdb.c?

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

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

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

Re: [HACKERS] Installing PL/pgSQL by default

[Tom Lane]

Bruce Momjian  writes:
> One problem is that because system oids are used, it isn't possible to
> drop the language:
> I assume we still want to allow the language to be uninstalled, for
> security purposes.

Yes.  That behavior is not acceptable.  Why aren't you just adding
a CREATE LANGUAGE call in one of the initdb scripts?

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] Installing PL/pgSQL by default

[Bruce Momjian]

Tom Lane wrote:
> But actually I thought we had more or less concluded that CREATE OR
> REPLACE LANGUAGE would be acceptable (perhaps only if it's given
> without any extra args?).  Or for that matter there seems to be enough
> opinion on the side of just installing plpgsql by default.  CINE is
> a markedly inferior alternative to either of those.

Based on research done as part of this thread, it seems plpgsql has
similar risks to recursive queries, so the idea of installing plpgsql by
default now makes more sense.

The attached patch installs plpgsql language by default, as well as the
three plpgsql helper functions.  The language is installed just like it
was before, but now automatically, e.g. still a separate shared object. 
One problem is that because system oids are used, it isn't possible to
drop the language:

$ droplang plpgsql test
droplang: language removal failed: ERROR:  cannot drop language plpgsql
because it is required by the database system

I assume we still want to allow the language to be uninstalled, for
security purposes.  Right?  Any suggestions?

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

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/installation.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/installation.sgml,v
retrieving revision 1.328
diff -c -c -r1.328 installation.sgml
*** doc/src/sgml/installation.sgml	2 Dec 2009 14:07:25 -	1.328
--- doc/src/sgml/installation.sgml	3 Dec 2009 23:09:59 -
***
*** 2257,2270 
   is createlang failing with unusual errors.
   For example, running as the owner of the PostgreSQL installation:
  
! -bash-3.00$ createlang plpgsql template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plpgsql.so": A memory address is not in the address space for the process.
  
  Running as a non-owner in the group posessing the PostgreSQL
  installation:
  
! -bash-3.00$ createlang plpgsql template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plpgsql.so": Bad address
  
   Another example is out of memory errors in the PostgreSQL server
   logs, with every memory allocation near or greater than 256 MB
--- 2257,2270 
   is createlang failing with unusual errors.
   For example, running as the owner of the PostgreSQL installation:
  
! -bash-3.00$ createlang plperl template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plperl.so": A memory address is not in the address space for the process.
  
  Running as a non-owner in the group posessing the PostgreSQL
  installation:
  
! -bash-3.00$ createlang plperl template1
! createlang: language installation failed: ERROR:  could not load library "/opt/dbs/pgsql748/lib/plperl.so": Bad address
  
   Another example is out of memory errors in the PostgreSQL server
   logs, with every memory allocation near or greater than 256 MB
Index: src/include/catalog/pg_language.h
===
RCS file: /cvsroot/pgsql/src/include/catalog/pg_language.h,v
retrieving revision 1.35
diff -c -c -r1.35 pg_language.h
*** src/include/catalog/pg_language.h	22 Sep 2009 23:43:41 -	1.35
--- src/include/catalog/pg_language.h	3 Dec 2009 23:09:59 -
***
*** 75,79 
--- 75,82 
  DATA(insert OID = 14 ( "sql"		PGUID f t 0 0 2248 _null_ ));
  DESCR("SQL-language functions");
  #define SQLlanguageId 14
+ DATA(insert OID = 9 ( "plpgsql"		PGUID t t 2995 2996 2997 _null_ ));
+ DESCR("SQL-language functions");
+ 
  
  #endif   /* PG_LANGUAGE_H */
Index: src/include/catalog/pg_proc.h
===
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.554
diff -c -c -r1.554 pg_proc.h
*** src/include/catalog/pg_proc.h	29 Nov 2009 18:14:30 -	1.554
--- src/include/catalog/pg_proc.h	3 Dec 2009 23:10:03 -
***
*** 4722,4727 
--- 4722,4734 
  DATA(insert OID = 3114 (  nth_value		PGNSP PGUID 12 1 0 0 f t f t f i 2 0 2283 "2283 23" _null_ _null_ _null_ _null_ window_nth_value _null_ _null_ _null_ ));
  DESCR("fetch the Nth row value");
  
+ /* PL/pgSQL support functions */
+ DATA(insert OID = 2995 (  plpgsql_call_handler	PGNSP PGUID 13 1 0 0 f f f f f v 0 0 2280 "" _null_ _null_ _null_ _null_ plpgsql_call_handler "$libdir/plpgsql" _null_ _null_ ));
+ DESCR("PL/pgSQL function/trigger manager");
+ DATA(insert OID = 2996 (  plpgsql_inline_handler	PGNSP PGUID 13 1 0 0 f f f t f v 1 0 2278 2281 _null_ _null_ _null_ _null_ plpgsql_inline_handler "$libdir/plpgsql" _null_ _null_ ));
+ DESCR("PL/pgSQL anonymous code block executor");
+ DATA(insert OID = 2997 (  plpgsql_validato

Re: [HACKERS] operator exclusion constraints

[Tom Lane]

Jeff Davis  writes:
> On Tue, 2009-12-01 at 23:19 -0500, Robert Haas wrote:
>> For parity with unique constraints, I think that the message:
>> 
>> operator exclusion constraint violation detected: %s
>> 
>> should be changed to:
>> 
>> conflicting key value violates operator exclusion constraint "%s"

> Done, and updated tests.

I'm starting to go through this patch now.  I thought the consensus
was to refer to them as just "exclusion constraints"?  I'm not seeing
that the word "operator" really adds anything.

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] Initial refactoring of plperl.c - rebased [PATCH]

[Josh Berkus]

Tim,

Since there's a commitfest on right now, meaningful feedback on your
patch could be delayed.  Just so you know.

--Josh Berkus


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

[HACKERS] First feature patch for plperl - draft [PATCH]

[Tim Bunce]

Building on my earlier plperl refactoring patch, here's a draft of my
first plperl feature patch.

Significant changes in this patch:

- New GUC plperl.on_perl_init='...perl...' for admin use.
- New GUC plperl.on_trusted_init='...perl...' for plperl user use.
- New GUC plperl.on_untrusted_init='...perl...' for plperlu user use.
- END blocks now run at backend exit (fixes bug #5066).
- Stored procedure subs are now given names ($name__$oid).
- More error checking and reporting.
- Warnings no longer have an extra newline in the NOTICE text.
- Various minor optimizations like pre-growing data structures.

I'm working on adding tests and documentation now, meanwhile I'd very
much appreciate any feedback on the patch.

Tim.

p.s. Once this patch is complete I plan to work on patches that:
- add quote_literal and quote_identifier functions in C.
- generalize the Safe setup code to enable more control.
- formalize namespace usage, moving things out of main::
- add a way to perform inter-sub calling (at least for simple cases).
- possibly rewrite _plperl_to_pg_array in C.

diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile
index 8989b14..5a9ad2f 100644
*** a/src/pl/plperl/GNUmakefile
--- b/src/pl/plperl/GNUmakefile
*** include $(top_srcdir)/src/Makefile.shlib
*** 48,54 
  plperl.o: perlchunks.h
  
  perlchunks.h: plc_*.pl
! 	$(PERL) text2macro.pl --strip='^(\#.*|\s*)$$' plc_*.pl > perlchunks.htmp
  	mv perlchunks.htmp perlchunks.h
  
  all: all-lib
--- 48,54 
  plperl.o: perlchunks.h
  
  perlchunks.h: plc_*.pl
! 	$(PERL) text2macro.pl --strip='^\s*(\#.*|)$$' plc_*.pl > perlchunks.htmp
  	mv perlchunks.htmp perlchunks.h
  
  all: all-lib
diff --git a/src/pl/plperl/expected/plperl_elog.out b/src/pl/plperl/expected/plperl_elog.out
index 1791d3c..89497e3 100644
*** a/src/pl/plperl/expected/plperl_elog.out
--- b/src/pl/plperl/expected/plperl_elog.out
*** create or replace function perl_warn(tex
*** 21,27 
  $$;
  select perl_warn('implicit elog via warn');
  NOTICE:  implicit elog via warn at line 4.
- 
  CONTEXT:  PL/Perl function "perl_warn"
   perl_warn 
  ---
--- 21,26 
diff --git a/src/pl/plperl/plc_perlboot.pl b/src/pl/plperl/plc_perlboot.pl
index d2d5518..b9c6878 100644
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 1,8 
--- 1,12 
  SPI::bootstrap();
+ 
+ use strict;
+ use warnings;
  use vars qw(%_SHARED);
  
  sub ::plperl_warn {
  	(my $msg = shift) =~ s/\(eval \d+\) //g;
+ 	chomp $msg;
  	&elog(&NOTICE, $msg);
  }
  $SIG{__WARN__} = \&::plperl_warn;
*** sub ::plperl_die {
*** 13,28 
  }
  $SIG{__DIE__} = \&::plperl_die;
  
! sub ::mkunsafefunc {
! 	my $ret = eval(qq[ sub { $_[0] $_[1] } ]);
! 	$@ =~ s/\(eval \d+\) //g if $@;
! 	return $ret;
! }
  
! use strict;
  
! sub ::mk_strict_unsafefunc {
! 	my $ret = eval(qq[ sub { use strict; $_[0] $_[1] } ]);
  	$@ =~ s/\(eval \d+\) //g if $@;
  	return $ret;
  }
--- 17,44 
  }
  $SIG{__DIE__} = \&::plperl_die;
  
! sub ::mkfuncsrc {
! 	my ($name, $imports, $prolog, $src) = @_;
  
! 	my $BEGIN = join "\n", map {
! 		my $names = $imports->{$_} || [];
! 		"$_->import(qw(@$names));"
! 	} keys %$imports;
! 	$BEGIN &&= "BEGIN { $BEGIN }";
  
! 	$name =~ s/\\//g;
! 	$name =~ s/::|'/_/g; # avoid package delimiters
! 
! 	my $funcsrc;
! 	$funcsrc .= qq[ undef *{'$name'}; *{'$name'} = sub { $BEGIN $prolog $src } ];
! 	#warn "plperl mkfuncsrc: $funcsrc\n";
! 	return $funcsrc;
! }
! 
! # see also mksafefunc() in plc_safe_ok.pl
! sub ::mkunsafefunc {
! 	no strict; # default to no strict for the eval
! 	my $ret = eval(::mkfuncsrc(@_));
  	$@ =~ s/\(eval \d+\) //g if $@;
  	return $ret;
  }
*** sub ::_plperl_to_pg_array {
*** 39,46 
  }
  elsif (defined($elem)) {
my $str = qq($elem);
!   $str =~ s/([\"\\])/\\$1/g;
!   $res .= qq(\"$str\");
  }
  else {
$res .= 'NULL' ;
--- 55,62 
  }
  elsif (defined($elem)) {
my $str = qq($elem);
!   $str =~ s/(["\\])/\\$1/g;
!   $res .= qq("$str");
  }
  else {
$res .= 'NULL' ;
diff --git a/src/pl/plperl/plc_safe_bad.pl b/src/pl/plperl/plc_safe_bad.pl
index 838ccc6..da47341 100644
*** a/src/pl/plperl/plc_safe_bad.pl
--- b/src/pl/plperl/plc_safe_bad.pl
***
*** 1,15 
! use vars qw($PLContainer);
! 
! $PLContainer = new Safe('PLPerl');
! $PLContainer->permit_only(':default');
! $PLContainer->share(qw[&elog &ERROR]);
  
  my $msg = 'trusted Perl functions disabled - please upgrade Perl Safe module to version 2.09 or later';
- sub ::mksafefunc {
-   return $PLContainer->reval(qq[sub { elog(ERROR,'$msg') }]);
- }
  
! sub ::mk_strict_safefunc {
!   return $PLContainer->reval(qq[sub { elog(ERROR,'$msg') }]);
  }
- 
--- 1,13 
! # Minimal version of plc_safe_ok.pl
! # Executed if Safe is too old or doesn't load for any reason
  
  my $msg = 'trusted Perl functions disabled - please upgrade Perl Sa

Re: [HACKERS] Ragged CSV import

[Josh Berkus]

Douglas,

> 1. Copy to non-existent table - create it from the data type using most 
> generic datatypes

Yes, that would be nice.  Types chosen would pretty much have to be
TEXT, NUMERIC, and TIMESTAMP for everything though; anything else is too
variable.

> 2. provide column mapping function  - from what I understand that the column 
> list on a copy from is the target columns to load.  I assume there must be a 
> header record and the same columns in the 

So, like:

COPY table1 ( cola, colb, colc ) FROM 'somecsv.csv' ( 'name', 'place',
'date') WITH CSV HEADER

... which would copy 'name' to cola, 'place' to colb, 'date' to colc,
and ignore any other columns present in the file?

--Josh Berkus

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

Re: [HACKERS] Adding support for SE-Linux security

[Josh Berkus]

> In words of one syllable: I do not care at all whether the NSA would use
> Postgres, if they're not willing to come and help us build it. 

There's several 2-syllable words there.  ;-)

 If we
> tried to build it without their input, we'd probably not produce what
> they want anyway.

Yeah, the *complete* lack of input/help from the security community
aside from the occasional "SE Linux good" posts we've gotten is
troubling.  We could end up with a SQL-J.

Kaigai, you've said that you could get SELinux folks involved in the
patch review.  I think it's past time that they were; please solicit them.

--Josh Berkus


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

Re: [HACKERS] Adding support for SE-Linux security

[Bruce Momjian]

Andrew Dunstan wrote:
> I think you have been remarkably good about our caution in accepting
> this. You certainly have my admiration for your patience.

Agreed.

> What would probably help us a lot would be to know some names of large
> users who want and will support this. NEC's name is a good start, but if
> a few other enterprise users spoke up it would help to make the decision
> a lot easier.

I think the open questions we have now are:

o  Is SE-Linux appropriate technology for Postgres?
o  Does SE-Linux have a sufficient user base or potential
   user base to justify the additional code?
o  Can the code be maintained?

And we have some partial answers.  SE-Linux seems like the most popular
of the security frameworks.  There are a number of identified potential
users, though we are looking to hear about more of them.  Third, KaiGai
is being paid by NEC to do this work and has shown to be extraordinarily
dedicated to this feature.  He has also offered to get other SE-Linux
people involved in any patch review.

I think the PostGIS example mentioned earlier is a good one.  We did
make some minor adjustments years ago to make things easier for them,
but we had the luxury of having PostGIS be able to be developed outside
of our main tree.  I think with the current posted patch we have some of
that benefit in that most of the code is in SE-Linux-specific
directories, but the code outside those directories does have to be
maintained.

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

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

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

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Greg Smith]

Robert Haas wrote:

I agree that search and replace isn't that hard, but I don't find the
proposed construction awkward, and we have various uses of it in the
docs already.  Actually the COPY one is not quite clear whether it
means <= 7.3 or < 7.3. 
  
Yeah, I wouldn't have suggested it if it made the wording particularly 
difficult in the process.  I don't know what your issue with the COPY 
one is:


"The following syntax was used before PostgreSQL version 7.3 and is 
still supported"


I can't parse that as anything other than "<7.3"; now sure how can 
someone read that to be "<="?


In any case, the two examples you gave are certainly good for showing 
the standard practices used here.  Specific version numbers are strewn 
all about, and if there's commits mentioning 8.5 already in there one  
more won't hurt.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com


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

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Robert Haas]

On Thu, Dec 3, 2009 at 3:33 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> I agree that search and replace isn't that hard, but I don't find the
>> proposed construction awkward, and we have various uses of it in the
>> docs already.  Actually the COPY one is not quite clear whether it
>> means <= 7.3 or < 7.3.  I think we're just aiming for consistency here
>> as much as anything.
>
> Well, the problem is that "<= 8.4" is confusing as to whether it
> includes 8.4.n.  You and I know that it does because we know we
> don't make feature changes in minor releases, but this is not
> necessarily obvious to everyone.  "< 8.5" is much less ambiguous.

Ah.  I would not have considered that, but it does make sense.

...Robert

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

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Tom Lane]

Robert Haas  writes:
> I agree that search and replace isn't that hard, but I don't find the
> proposed construction awkward, and we have various uses of it in the
> docs already.  Actually the COPY one is not quite clear whether it
> means <= 7.3 or < 7.3.  I think we're just aiming for consistency here
> as much as anything.

Well, the problem is that "<= 8.4" is confusing as to whether it
includes 8.4.n.  You and I know that it does because we know we
don't make feature changes in minor releases, but this is not
necessarily obvious to everyone.  "< 8.5" is much less ambiguous.

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] [PATCH] Largeobject Access Controls (r2432)

[Robert Haas]

On Thu, Dec 3, 2009 at 2:25 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Dec 3, 2009 at 1:23 PM, Greg Smith  wrote:
>>> In this particular example, it's bad form because it's even possible that
>>> 8.5 will actually be 9.0.  You don't want to refer to a version number that
>>> doesn't even exist for sure yet, lest it leave a loose end that needs to be
>>> cleaned up later if that number is changed before release.
>
>> Ah, yes, I like "In 8.4 and earlier versions", or maybe "earlier
>> releases".  Compare:
>
> Please do *not* resort to awkward constructions just to avoid one
> mention of the current version number.  If we did decide to call the
> next version 9.0, the search-and-replace effort involved is not going
> to be measurably affected by any one usage.  There are plenty already.
>
> (I did the work when we decided to call 7.5 8.0, so I know whereof
> I speak.)

I agree that search and replace isn't that hard, but I don't find the
proposed construction awkward, and we have various uses of it in the
docs already.  Actually the COPY one is not quite clear whether it
means <= 7.3 or < 7.3.  I think we're just aiming for consistency here
as much as anything.

...Robert

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

Re: [HACKERS] Block-level CRC checks

[Jonah H. Harris]

On Tue, Dec 1, 2009 at 1:27 PM, Joshua D. Drake wrote:

> On Tue, 2009-12-01 at 13:20 -0500, Robert Haas wrote:
> > Does $COMPETITOR offer this feature?
> >
>
> My understanding is that MSSQL does. I am not sure about Oracle. Those
> are the only two I run into (I don't run into MySQL at all). I know
> others likely compete in the DB2 space.
>

To my knowledge, MySQL, InnoDB, BerkeleyDB, solidDB, Oracle, SQL Server,
Sybase, DB2, eXtremeDB, RDB, and Teradata all checksum pages.

-- 
Jonah H. Harris, Senior DBA
myYearbook.com

Re: [HACKERS] Aggregate ORDER BY patch

[Alvaro Herrera]

Hitoshi Harada escribió:

> I found only trivial favors such like that a blank line is added
> around line 595 in the patch, and "proj" in peraggstate sounds a
> little weird to me because of surrounding "evaldesc" and "evalslot"
> ("evalproj" seems better to me). Also catversion update doesn't mean
> anything for this feature. But these are not what prevent it from
> review by a committer. So, although I'm going to look more on this
> patch, I mark this item as "Ready for Committer" for now.

AFAICS the catversion bump is needed because of the change in a parser
node.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

[Tom Lane]

Robert Haas  writes:
> On Thu, Dec 3, 2009 at 1:23 PM, Greg Smith  wrote:
>> In this particular example, it's bad form because it's even possible that
>> 8.5 will actually be 9.0.  You don't want to refer to a version number that
>> doesn't even exist for sure yet, lest it leave a loose end that needs to be
>> cleaned up later if that number is changed before release.

> Ah, yes, I like "In 8.4 and earlier versions", or maybe "earlier
> releases".  Compare:

Please do *not* resort to awkward constructions just to avoid one
mention of the current version number.  If we did decide to call the
next version 9.0, the search-and-replace effort involved is not going
to be measurably affected by any one usage.  There are plenty already.

(I did the work when we decided to call 7.5 8.0, so I know whereof
I speak.)

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] [PATCH] Largeobject Access Controls (r2432)

[Robert Haas]

On Thu, Dec 3, 2009 at 1:23 PM, Greg Smith  wrote:
> Robert Haas wrote:
> On Thu, Dec 3, 2009 at 12:49 PM, Jaime Casanova
>  wrote:
>
> This manual will be specific for 8.5 so i think all mentions to the
> version should be removed
>
> Not sure I agree on this point.  We have similar mentions elsewhere.
>
> In this particular example, it's bad form because it's even possible that
> 8.5 will actually be 9.0.  You don't want to refer to a version number that
> doesn't even exist for sure yet, lest it leave a loose end that needs to be
> cleaned up later if that number is changed before release.
>
> Rewriting in terms like "in earlier versions..." instead is one approach.
> Then people will have to manually scan earlier docs to sort that out, I know
> I end up doing that all the time.  If you want to keep the note specific,
> saying "in 8.4 and earlier versions [old behavior]" is better than "before
> 8.5 [old behavior]" because it only mentions version numbers that are
> historical rather than future.

Ah, yes, I like "In 8.4 and earlier versions", or maybe "earlier
releases".  Compare:

http://www.postgresql.org/docs/8.4/static/sql-copy.html#AEN55855
http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html#GUC-LOG-FILENAME

...Robert

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

Re: [HACKERS] Catastrophic changes to PostgreSQL 8.4

[Josh Berkus]

> On Wed, Dec 2, 2009 at 10:49 PM, Adrian Klaver  wrote:
>> When I tried the above Postgres did not ignore the command, instead it gave 
>> me
>> the following error and did not create the database:
>> CREATE DATABASE bacula ENCODING 'SQL_ASCII';
>> ERROR:  new encoding (SQL_ASCII) is incompatible with the encoding of the
>> template database (UTF8)
>> HINT:  Use the same encoding as in the template database, or use template0 as

Boy, this looks like a reason why app developers should do some beta
testing of new Postgres versions ...

--Josh Berkus


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

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Greg Smith]

Robert Haas wrote:

On Thu, Dec 3, 2009 at 12:49 PM, Jaime Casanova
 wrote:
  

This manual will be specific for 8.5 so i think all mentions to the
version should be removed



Not sure I agree on this point.  We have similar mentions elsewhere.
  
In this particular example, it's bad form because it's even possible 
that 8.5 will actually be 9.0.  You don't want to refer to a version 
number that doesn't even exist for sure yet, lest it leave a loose end 
that needs to be cleaned up later if that number is changed before release.


Rewriting in terms like "in earlier versions..." instead is one 
approach.  Then people will have to manually scan earlier docs to sort 
that out, I know I end up doing that all the time.  If you want to keep 
the note specific, saying "in 8.4 and earlier versions [old behavior]" 
is better than "before 8.5 [old behavior]" because it only mentions 
version numbers that are historical rather than future.


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  www.2ndQuadrant.com

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Robert Haas]

On Thu, Dec 3, 2009 at 12:49 PM, Jaime Casanova
 wrote:
> This manual will be specific for 8.5 so i think all mentions to the
> version should be removed

Not sure I agree on this point.  We have similar mentions elsewhere.

...Robert

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

Re: [HACKERS] [PATCH] Largeobject Access Controls (r2432)

[Jaime Casanova]

2009/11/12 KaiGai Kohei :
> The attached patch is a revised version of large object privileges
> based on the feedbacks at the last commit fest.
>

please update the patch, it's giving an error when 'make check' is
trying to "create template1" in initdb:

creating template1 database in
/home/postgres/pg_releases/pgsql/src/test/regress/./tmp_check/data/base/1
... TRAP: FailedAssertion("!(reln->md_fd[forkNum] == ((void *)0))",
File: "md.c", Line: 254)
child process was terminated by signal 6: Aborted


Meanwhile, i will make some comments:

This manual will be specific for 8.5 so i think all mentions to the
version should be removed
for example;

+In this version, a large object has OID of its owner, access permissions
+and OID of the largeobject itself.

+ Prior to the version 8.5.x release does not have any
privilege checks on
+   large objects.

the parameter name (large_object_privilege_checks) is confusing enough
that we have to make this statements to clarify... let's think in a
better less confuse name
+ Please note that it is not equivalent to disable all the security
+ checks corresponding to large objects.
+ For example, the lo_import() and
+ lo_export need superuser privileges independent
+ from this setting as prior versions were doing.

this will not be off by default? it should be for compatibility
reasons... i remember there was a discussion about that but can't
remember the conclusion

Mmm... One of them? the first?
+ The one is SELECT.

+ Even if a transaction modified access rights and commit it, it is
+ not invisible from other transaction which already opened the large
+ object.

The other one, the second
+ The other is UPDATE.


it seems there is an "are" that should not be there :)
+
+ These functions are originally requires database superuser privilege,
+ and it allows to bypass the default database privilege checks, so
+ we don't need to check an obvious test twice.

a typo, obviously
+For largeo bjects, this privilege also allows to read from
+the target large object.


We have two versions of these functions one that a recieve an SnapShot
parameter and other that don't...
what is the rationale of this? AFAIU, the one that doesn't receive
SnapShot is calling the other one with SnapShotNow, can't we simply
call it that way and drop the version of the functions that doesn't
have that parameter?
+ pg_largeobject_aclmask(Oid lobj_oid, Oid roleid,
+  AclMode mask, AclMaskHow how)

+ pg_largeobject_aclcheck(Oid lobj_oid, Oid roleid, AclMode mode)

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
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] Deleted WAL files held open by backends in Linux

[Kevin Grittner]

"Kevin Grittner"  wrote:
 
> Any objections to my putting it on the TODO list?
 
Hearing none, added.  (Apologies for missing the box where I should
have commented on the what the change did.)
 
-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] RFC for adding typmods to functions

[Pavel Stehule]

2009/12/3 Alvaro Herrera :
> Pavel Stehule escribió:
>> 2009/11/18 Peter Eisentraut :
>> > On ons, 2009-11-18 at 11:46 +0100, Pavel Stehule wrote:
>> >> I am not sure if SQL standard is good inspiration in this case.
>> >
>> > I'm not sure either, but I think it's premature to make a conclusion
>> > about that without having checked at all.
>>
>> ok, I recheck SQL/PSM part again :)
>
> So, did this go anywhere?
>

I am read a documentation - and I am not sure. It is not part of
SQL/PSM. It is part of Foundation (SQL/Foundation) - see CD
9075-2:200x(E)
11.50 .  Important is section 18.d.

one parameter from all parameter list have to be unique (types are not
compatible)

Section 9.18 Data type identity speaking what are identity types, but
I am missing definition what are not compatible types.

so I if I respect 18.d, then I have not a functions foo(varchar(30))
and foo(varchar(40)).

But i am not native speaker and SQL standard is cryptographic for me.
Please, can somebody check it again.

Regards
Pavel Stehule

> --
> Alvaro Herrera                                http://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] patch - per-tablespace random_page_cost/seq_page_cost

[Robert Haas]

On Sat, Nov 28, 2009 at 9:54 PM, David Rowley  wrote:
> Robert Haas Wrote:
>> Hmm.  I'm not able to reliably detect a performance difference between
>> unpatched CVS HEAD (er... git master branch) and same with spcoptions-
>> v2.patch applied.  I figured that if there were going to be an impact,
>> it would be most likely to manifest itself in a query that touches lots
>> and lots of tables but does very little actual work.  So I used the
>> attached script to create 200 empty tables, 100 in the default
>> tablespace and 100 in tablespace "dork" (also known as, why I am
>> working on this at 11 PM on Thanksgiving).  Then I did:
>>
>> SELECT * FROM a1, a2, a3, ..., a100;
>
> (I've not read the patch, but I've just read the thread)
> If you're just benchmarking the planner times to see if the extra lookups
> are affecting the planning times, would it not be better to benchmark
> EXPLAIN SELECT * FROM a1, a2, a3, ..., a100; ?
> Otherwise any small changes might be drowned out in the execution time.
> Scanning 100 relations even if they are empty could account for quite a bit
> of that time, right?

Possibly, but even if I can measure a difference doing it that way,
it's not clear that it matters.  It's fairly certain that there will
be a performance degradation if we measure carefully enough, but if
that difference is imperceptible in real-world scanerios, then it's
not worth worrying about.  Still, I probably will test it just to see.

...Robert

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

Re: [HACKERS] Catastrophic changes to PostgreSQL 8.4

[Tom Lane]

Greg Stark  writes:
>> CREATE DATABASE bacula ENCODING 'SQL_ASCII';
>> ERROR:  new encoding (SQL_ASCII) is incompatible with the encoding of the
>> template database (UTF8)
>> HINT:  Use the same encoding as in the template database, or use template0 as
>> template.

> Actually I'm kind of surprised at this. I don't see a reason not to
> allow converting a template to SQL_ASCII from any encoding given that
> we're going to allow them to put random bytes into the database
> afterwards. Why not let them start with random bytes?

Hm, that's a good point, although we should still enforce that the new
database's LC_xxx settings be C/POSIX if it's SQL_ASCII.  My guess is
that the initdb environment had some non-C locale, so this example would
have failed the next error check anyway.

regards, tom lane

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

Re: [HACKERS] [GENERAL] pg_attribute.attnum - wrong column ordinal?

[Tom Lane]

Alvaro Herrera  writes:
> Should we recast the attributes and columns views in information_schema?
> I notice they still use attnum.

I'd vote against it, at least until we have something better than a
row_number solution.  That would create another huge performance penalty
on views that are already ungodly slow.

When and if we get around to separating physical from logical column
position, the issue might go away "for free".

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] I need some help from you

[toto toto]

I sow that you have knoladge about advance programing softwere ,  I need an 
EBAY program ...
 
 
Please reply me back so I can give you more info about  what I need
 
Thank you 


  

Re: [HACKERS] RFC for adding typmods to functions

[Alvaro Herrera]

Pavel Stehule escribió:
> 2009/11/18 Peter Eisentraut :
> > On ons, 2009-11-18 at 11:46 +0100, Pavel Stehule wrote:
> >> I am not sure if SQL standard is good inspiration in this case.
> >
> > I'm not sure either, but I think it's premature to make a conclusion
> > about that without having checked at all.
> 
> ok, I recheck SQL/PSM part again :)

So, did this go anywhere?

-- 
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] Catastrophic changes to PostgreSQL 8.4

[Greg Stark]

[moving this point to -hackers]

On Wed, Dec 2, 2009 at 10:49 PM, Adrian Klaver  wrote:
> When I tried the above Postgres did not ignore the command, instead it gave me
> the following error and did not create the database:
> CREATE DATABASE bacula ENCODING 'SQL_ASCII';
> ERROR:  new encoding (SQL_ASCII) is incompatible with the encoding of the
> template database (UTF8)
> HINT:  Use the same encoding as in the template database, or use template0 as
> template.

Actually I'm kind of surprised at this. I don't see a reason not to
allow converting a template to SQL_ASCII from any encoding given that
we're going to allow them to put random bytes into the database
afterwards. Why not let them start with random bytes?


-- 
greg

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

Re: [HACKERS] [GENERAL] pg_attribute.attnum - wrong column ordinal?

[Alvaro Herrera]

Greg Stark escribió:
> On Wed, Nov 25, 2009 at 1:03 AM, Konstantin Izmailov  wrote:
> 
> > My question: can pg_attribute.attnum be used to determine the sequential
> > ordinal positions of columns in a table? What is a right way to get the
> > ordinal numbers?
> 
> You could use something like:
> 
> row_number() over (partition by T.schemaname,T.viewname order by
> attnum) as "ORDINAL_POSITION"

Should we recast the attributes and columns views in information_schema?
I notice they still use attnum.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] set the cost of an aggregate function

[Simon Riggs]

On Mon, 2009-11-30 at 11:53 -0500, Jaime Casanova wrote:
> 2009/11/30 Jaime Casanova :
> > Hi,
> >
> > why we can't do $subject? it could have any benefit on the planner?
> >
> 
> seems like while we can set the cost of the state transition function,
> that cost is not propagated...

The cost of comparison functions is not considered in cost_sort()
either.

The answer is nobody got round to enhancing this yet and well considered
proposals and subsequent patches would be welcome. 

-- 
 Simon Riggs   www.2ndQuadrant.com


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

Re: [HACKERS] Cost of sort/order by not estimated by the query planner

[Laurent Laborde]

The table is clustered by by blog_id.
So, for testing purpose, i tried an ORDER BY blog_id.

limit 500 :
-
explain analyze SELECT *
FROM   _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.blog_id ASC
LIMIT 500;

 Limit  (cost=66229.90..66231.15 rows=500 width=1099) (actual
time=9.368..9.580 rows=500 loops=1)
   ->  Sort  (cost=66229.90..66273.25 rows=17341 width=1099) (actual
time=9.367..9.443 rows=500 loops=1)
 Sort Key: blog_id
 Sort Method:  top-N heapsort  Memory: 660kB
 ->  Bitmap Heap Scan on _article  (cost=138.67..65365.82
rows=17341 width=1099) (actual time=0.905..4.042 rows=6729 loops=1)
   Recheck Cond: (bitfield && B'1'::bit varying)
   ->  Bitmap Index Scan on idx_article_bitfield
(cost=0.00..134.33 rows=17341 width=0) (actual time=0.772..0.772
rows=6729 loops=1)
 Index Cond: (bitfield && B'1'::bit varying)
 Total runtime: 9.824 ms

Limit 5 :
--
explain analyze SELECT *
FROM   _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.blog_id ASC
LIMIT 5;

 Limit  (cost=0.00..1419.22 rows=5 width=1099) (actual
time=125076.420..280419.143 rows=5 loops=1)
   ->  Index Scan using idx_article_blog_id on _article
(cost=0.00..4922126.37 rows=17341 width=1099) (actual
time=125076.419..280419.137 rows=5 loops=1)
 Filter: (bitfield && B'1'::bit varying)
 Total runtime: 280419.241 ms


-- 
Laurent "ker2x" Laborde
Sysadmin & DBA at http://www.over-blog.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] Cost of sort/order by not estimated by the query planner

[Laurent Laborde]

'morning !

And here is the query plan for :
---
explain analyze SELECT *
FROM   _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.id ASC
LIMIT 5;

 Limit  (cost=0.00..2238.33 rows=5 width=1099) (actual
time=17548636.326..17548837.082 rows=5 loops=1)
   ->  Index Scan using _article_pkey on _article
(cost=0.00..7762964.53 rows=17341 width=1099) (actual
time=17548636.324..17548837.075 rows=5 loops=1)
 Filter: (bitfield && B'1'::bit varying)
 Total runtime: 17548837.154 ms


Versus the "limit 500" query plan :
---
explain analyze SELECT *
FROM   _article
WHERE (_article.bitfield && getbit(0))
ORDER BY _article.id ASC
LIMIT 500;

 Limit  (cost=66229.90..66231.15 rows=500 width=1099) (actual
time=1491.905..1492.146 rows=500 loops=1)
   ->  Sort  (cost=66229.90..66273.25 rows=17341 width=1099) (actual
time=1491.904..1492.008 rows=500 loops=1)
 Sort Key: id
 Sort Method:  top-N heapsort  Memory: 603kB
 ->  Bitmap Heap Scan on _article  (cost=138.67..65365.82
rows=17341 width=1099) (actual time=777.489..1487.120 rows=6729
loops=1)
   Recheck Cond: (bitfield && B'1'::bit varying)
   ->  Bitmap Index Scan on idx_article_bitfield
(cost=0.00..134.33 rows=17341 width=0) (actual time=769.799..769.799
rows=6729 loops=1)
 Index Cond: (bitfield && B'1'::bit varying)
 Total runtime: 1630.690 ms


I will read (and try to understand) all you said yesterday and reply
as soon as i can :)
Thank you !

-- 
Laurent "ker2x" Laborde
Sysadmin & DBA at http://www.over-blog.com/

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