Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert Haas wrote: On Tue, Jun 23, 2009 at 10:46 PM, Jeremy Kerr wrote: Robert, I'd still like to know the workload and exact numbers. From up-thread: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ - or were there other details you were looking for? Oh! Very nice, sorry, missed the original message. You probably want to add your patch here: http://wiki.postgresql.org/wiki/CommitFestOpen FWIW: I'm able to measure an even more significant improvement of around 10%: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01041.php on a specific query. Stefan -- 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] that picksplit debug message again
Peter Eisentraut writes:
> Is the %d actually in the right place here?
> errmsg("picksplit method for %d column of index \"%s\" failed",
> attno + 1, RelationGetRelationName(r))
No, any native speaker of English would say "for column %d". Putting
"failed" at the end seems a bit awkward as well, though I can't offhand
see a better phrasing. "picksplit method failed for ..." is *not*
better; it implies there is only one picksplit method for everything,
whereas the point of the message is that the one associated with this
column failed.
> And later in the file there is this, which might have the same problem:
> elog(LOG, "PickSplit method of %d columns of index '%s' doesn't support
> secondary split",
> attno + 1, RelationGetRelationName(r));
Should be "for column %d" also, AFAICS, plus '' -> "" and lowercase
"PickSplit" ... but this message isn't translatable anyway as an elog().
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] pg_listener attribute number #defines
It appears that, for no particularly good reason, pg_listener.h
deviates from the usual convention for declaring attribute number
constants. Normally, it's
#define Anum_{catalog-name}_{column-name} {attribute-number}
pg_listener.h, however substitutes a different string that is similar,
but not the same as, the column name.
Suggested patch attached.
...Robert
*** a/src/backend/commands/async.c
--- b/src/backend/commands/async.c
***
*** 486,493 Exec_Listen(Relation lRel, const char *relname)
namestrcpy(&condname, relname);
values[Anum_pg_listener_relname - 1] = NameGetDatum(&condname);
! values[Anum_pg_listener_pid - 1] = Int32GetDatum(MyProcPid);
! values[Anum_pg_listener_notify - 1] = Int32GetDatum(0); /* no notifies pending */
tuple = heap_form_tuple(RelationGetDescr(lRel), values, nulls);
--- 486,493
namestrcpy(&condname, relname);
values[Anum_pg_listener_relname - 1] = NameGetDatum(&condname);
! values[Anum_pg_listener_listenerpid - 1] = Int32GetDatum(MyProcPid);
! values[Anum_pg_listener_notification - 1] = Int32GetDatum(0); /* no notifies pending */
tuple = heap_form_tuple(RelationGetDescr(lRel), values, nulls);
***
*** 567,573 Exec_UnlistenAll(Relation lRel)
/* Find and delete all entries with my listenerPID */
ScanKeyInit(&key[0],
! Anum_pg_listener_pid,
BTEqualStrategyNumber, F_INT4EQ,
Int32GetDatum(MyProcPid));
scan = heap_beginscan(lRel, SnapshotNow, 1, key);
--- 567,573
/* Find and delete all entries with my listenerPID */
ScanKeyInit(&key[0],
! Anum_pg_listener_listenerpid,
BTEqualStrategyNumber, F_INT4EQ,
Int32GetDatum(MyProcPid));
scan = heap_beginscan(lRel, SnapshotNow, 1, key);
***
*** 598,606 Send_Notify(Relation lRel)
/* preset data to update notify column to MyProcPid */
memset(nulls, false, sizeof(nulls));
memset(repl, false, sizeof(repl));
! repl[Anum_pg_listener_notify - 1] = true;
memset(value, 0, sizeof(value));
! value[Anum_pg_listener_notify - 1] = Int32GetDatum(MyProcPid);
scan = heap_beginscan(lRel, SnapshotNow, 0, NULL);
--- 598,606
/* preset data to update notify column to MyProcPid */
memset(nulls, false, sizeof(nulls));
memset(repl, false, sizeof(repl));
! repl[Anum_pg_listener_notification - 1] = true;
memset(value, 0, sizeof(value));
! value[Anum_pg_listener_notification - 1] = Int32GetDatum(MyProcPid);
scan = heap_beginscan(lRel, SnapshotNow, 0, NULL);
***
*** 978,984 ProcessIncomingNotify(void)
/* Scan only entries with my listenerPID */
ScanKeyInit(&key[0],
! Anum_pg_listener_pid,
BTEqualStrategyNumber, F_INT4EQ,
Int32GetDatum(MyProcPid));
scan = heap_beginscan(lRel, SnapshotNow, 1, key);
--- 978,984
/* Scan only entries with my listenerPID */
ScanKeyInit(&key[0],
! Anum_pg_listener_listenerpid,
BTEqualStrategyNumber, F_INT4EQ,
Int32GetDatum(MyProcPid));
scan = heap_beginscan(lRel, SnapshotNow, 1, key);
***
*** 986,994 ProcessIncomingNotify(void)
/* Prepare data for rewriting 0 into notification field */
memset(nulls, false, sizeof(nulls));
memset(repl, false, sizeof(repl));
! repl[Anum_pg_listener_notify - 1] = true;
memset(value, 0, sizeof(value));
! value[Anum_pg_listener_notify - 1] = Int32GetDatum(0);
while ((lTuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
--- 986,994
/* Prepare data for rewriting 0 into notification field */
memset(nulls, false, sizeof(nulls));
memset(repl, false, sizeof(repl));
! repl[Anum_pg_listener_notification - 1] = true;
memset(value, 0, sizeof(value));
! value[Anum_pg_listener_notification - 1] = Int32GetDatum(0);
while ((lTuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
*** a/src/include/catalog/pg_listener.h
--- b/src/include/catalog/pg_listener.h
***
*** 48,55 typedef FormData_pg_listener *Form_pg_listener;
*/
#define Natts_pg_listener 3
#define Anum_pg_listener_relname1
! #define Anum_pg_listener_pid 2
! #define Anum_pg_listener_notify 3
/*
* initial contents of pg_listener are NOTHING.
--- 48,55
*/
#define Natts_pg_listener 3
#define Anum_pg_listener_relname1
! #define Anum_pg_listener_listenerpid 2
! #define Anum_pg_listener_notification 3
/*
* initial contents of pg_listener are NOTHING.
--
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] Extensions User Design
On Tue, Jun 23, 2009 at 12:44 PM, Dimitri Fontaine wrote: > > - a core team approved list of extensions (replacing contribs, are you aware of the enormous job that will imply for core team? maybe a community approved list of extensions or maybe we can have some kind of jury (just like patch reviewers) that could test and mark as tested... i remember a conversation about this very point -- 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] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 23, 2009 at 10:46 PM, Jeremy Kerr wrote: > Robert, > >> I'd still like to know the workload and exact numbers. > > From up-thread: > > http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ > > - or were there other details you were looking for? Oh! Very nice, sorry, missed the original message. You probably want to add your patch here: http://wiki.postgresql.org/wiki/CommitFestOpen Thanks, ...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] backend: compare word-at-a-time in bcTruelen
Robert, > I'd still like to know the workload and exact numbers. From up-thread: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ - or were there other details you were looking for? Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dblink for 8.4 should work without user-mappings
contrib/dblink in 8.4 supports a server name by CREATE SERVER for connection
string, but it always requires an user-mapping (by CREATE USER MAPPING).
However, I think it should work user-mappings because it works when
the connection string is passed directly.
=# SELECT * FROM dblink('dbname=postgres', 'SELECT current_user') AS t(i name);
(ok)
=# CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
=# CREATE SERVER server1 FOREIGN DATA WRAPPER postgresql OPTIONS (dbname
'postgres');
=# SELECT * FROM dblink('server1', 'SELECT 1') AS t(i integer);
ERROR: user mapping not found for "postgres"
The attached patch adds 'missing_ok' parameter to GetUserMapping() and
made dblink to use it. There should be no additional security issues here
because dblink's original security check works even for server name mode.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
dblink-no-user-mapping.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 23, 2009 at 9:05 PM, Jeremy Kerr wrote: > Results in a small performance increase; around 1-2% on my POWER6 test > box. I'd still like to know the workload and exact numbers. ...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] [PATCH] backend: compare word-at-a-time in bcTruelen
The current bcTruelen function uses a simple reverse array scan to
find the legth of a space-padded string. On some workloads (it shows
in sysbench), this can result in a lot of time spent in this function.
This change introduces a word-at-a-time comparison in bcTruelen, aiming
to reduce the number of compares where possible. Word-size compares
are performed on aligned sections of the string.
Results in a small performance increase; around 1-2% on my POWER6 test
box.
Signed-off-by: Jeremy Kerr
---
Resend: context diff this time
---
src/backend/utils/adt/varchar.c | 24 +---
1 file changed, 21 insertions(+), 3 deletions(-)
*** a/src/backend/utils/adt/varchar.c
--- b/src/backend/utils/adt/varchar.c
***
*** 624,639 varchartypmodout(PG_FUNCTION_ARGS)
static int
bcTruelen(BpChar *arg)
{
char *s = VARDATA_ANY(arg);
int i;
- int len;
! len = VARSIZE_ANY_EXHDR(arg);
! for (i = len - 1; i >= 0; i--)
{
if (s[i] != ' ')
break;
}
return i + 1;
}
--- 624,657
static int
bcTruelen(BpChar *arg)
{
+ const uint32_t spaces = 0x20202020;
+ const int wordsize = sizeof(spaces);
char *s = VARDATA_ANY(arg);
int i;
! i = VARSIZE_ANY_EXHDR(arg) - 1;
!
! /* compare down to an aligned boundary */
! for (; i > 0 && !PointerIsAligned(s + i - (wordsize - 1), uint32_t);
i--)
{
if (s[i] != ' ')
+ return i + 1;
+ }
+
+ /* now that we're aligned, compare word at a time */
+ for (; i >= wordsize - 1; i -= wordsize)
+ {
+ if (*(uint32_t *)(s + i - (wordsize - 1)) != spaces)
break;
}
+
+ /* check within the last non-matching word */
+ for (; i >= 0; i--)
+ {
+ if (s[i] != ' ')
+ break;
+ }
+
return i + 1;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] that picksplit debug message again
Is the %d actually in the right place here?
errmsg("picksplit method for %d column of index \"%s\" failed",
attno + 1, RelationGetRelationName(r))
And later in the file there is this, which might have the same problem:
elog(LOG, "PickSplit method of %d columns of index '%s' doesn't support
secondary split",
attno + 1, RelationGetRelationName(r));
--
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] Extensions User Design
David E. Wheeler wrote: On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote: It's "just" PostgreSQL reading an SQL file (foo.install.sql) and parsing each statement etc, so we obviously have the machinery to recognize SQL objects names and schema qualification. Replacing the schema on-the-fly should be a SMOP? (*cough*) Well, no. I might have written a function in PL/Perl. Is PostgreSQL going to parse my Perl function for unqualified function calls? Really? Hell, I don't think that PL/pgSQL is parsed until functions are loaded, either, though I may be wrong about that. Better is to have some magic so that functions in an extension magically have their schema put onto the front of search_path when they're called. Or when they're compiled. Or something. With the given example of extension "foo" depending on "bar" and "baz", I'd suggest: - Default search_path = ext:self, pg_catalog - ext:self = - ext:bar = - ext:baz = You *can't* have anything other than the current package in the search-path in case bar/baz have conflicting objects. I've no idea if ext: makes sense from a parser point of view, but the idea is to map extension name to a schema. If possible, this should work anywhere in PG that a schema can be specified. So - If extension foo is installed in schema1 then ext:foo.fn1() is the same as schema1.fn1() -- Richard Huxton Archonet Ltd -- 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] Extensions User Design
On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote:
If we happen to accept the debian policy versioning scheme, then the
hard work is already done for us, it seems:
http://packages.debian.org/fr/sid/postgresql-8.3-debversion
As long as we don't need to implement a new data type, fine.
Replace what? How would pg_extension or INSTALL EXTENSION know to
magically schema-qualify the function calls internal to an extension?
It's "just" PostgreSQL reading an SQL file (foo.install.sql) and
parsing each statement etc, so we obviously have the machinery to
recognize SQL objects names and schema qualification. Replacing the
schema on-the-fly should be a SMOP? (*cough*)
Well, no. I might have written a function in PL/Perl. Is PostgreSQL
going to parse my Perl function for unqualified function calls?
Really? Hell, I don't think that PL/pgSQL is parsed until functions
are loaded, either, though I may be wrong about that.
Better is to have some magic so that functions in an extension
magically have their schema put onto the front of search_path when
they're called. Or when they're compiled. Or something.
Oh, you want EAV already? Or maybe a supplementary hstore column
into the pg_extension catalog... but I guess we can't have this
dependancy :)
No, but a simple key/value table with an FK constraint should be
sufficient for non-core metadata.
The upgrade function stuff is what I understand least about this
proposal. Can you provide a real-world type example of how it will
be used?
You provide a function upgrade(old, new) where parameters are
version numbers. The body of the (typically plpgsql) function should
implement the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you
need to do, with some conditions on the version numbers.
Well, CREATE OR REPLACE FUNCTION is probably already in my install.sql
file. But I could see dropping deprecated functions and, of course,
altering tables.
I expect people would write a upgrade_10_to_11() function then call
it from upgrade() when old = 1.0 and new = 1.1, for example.
Okay, that makes sense.
Maybe we should also provide some support functions to run the
install and uninstall script, and some more facilities, so that you
could implement as follow:
BEGIN
-- loop over columns storing data from our type
FOR s, t, c IN SELECT nspname, relname, attname
FROM pg_find_columns('mytype'::regclass)
LOOP
EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN $3 TYPE text USING
mycast($3)'
USING s, t, c;
END LOOP;
PERFORM pg_extension_uninstall('foo', old);
PERFORM pg_extension_install('foo', new);
-- ALTER TYPE the other way round
END;
Some other stuff could be needed to check about indexes to, storing
a list of them in a temp table then recreating them, but it seems to
me you can already hand craft the catalog queries now. But as it
becomes common practise, we might want to offer them in a more ready
for public consumption way.
Yes, whatever tools we can provide to make things easier for extension
authors/maintainers, the better. But I recognize that we might have to
wait and see what cow paths develop.
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] Extensions User Design
[Skipping most of it as I'd like to read what other people think about
it before going in lengthy thread already] :)
Le 23 juin 09 à 23:41, David E. Wheeler a écrit :
Yes, although as I said before, version numbers are hard to get
right. We should keep them very simple, with a strict requirement as
to the simple format (perhaps /\d+[.]\d{2}/) or perhaps NUMERIC or
some other core data type, and then we'd be able to use simple
operators:
install extension foo with version = 1.2 OR version >= 1.4,
search_path = foo;
If we happen to accept the debian policy versioning scheme, then the
hard work is already done for us, it seems:
http://packages.debian.org/fr/sid/postgresql-8.3-debversion
I don't think we want to cancel user ability to choose schema where
to install, so an idea could be to ask extensions author to
systematically use pg_extension (or non-qualify), and PostgreSQL
could replace this with the INSTALL EXTENSION command schema.
Replace what? How would pg_extension or INSTALL EXTENSION know to
magically schema-qualify the function calls internal to an extension?
It's "just" PostgreSQL reading an SQL file (foo.install.sql) and
parsing each statement etc, so we obviously have the machinery to
recognize SQL objects names and schema qualification. Replacing the
schema on-the-fly should be a SMOP? (*cough*)
I think that people will want to be able to associate arbitrary
metadata. It'd be useful for configuration, too.
Oh, you want EAV already? Or maybe a supplementary hstore column into
the pg_extension catalog... but I guess we can't have this dependancy :)
The upgrade function stuff is what I understand least about this
proposal. Can you provide a real-world type example of how it will
be used?
You provide a function upgrade(old, new) where parameters are version
numbers. The body of the (typically plpgsql) function should implement
the ALTER TABLE or CREATE OR REPLACE FUNCTION stuff you need to do,
with some conditions on the version numbers.
I expect people would write a upgrade_10_to_11() function then call it
from upgrade() when old = 1.0 and new = 1.1, for example.
Maybe we should also provide some support functions to run the install
and uninstall script, and some more facilities, so that you could
implement as follow:
BEGIN
-- loop over columns storing data from our type
FOR s, t, c IN SELECT nspname, relname, attname
FROM pg_find_columns('mytype'::regclass)
LOOP
EXECUTE 'ALTER TABLE $1.$2 ALTER COLUMN $3 TYPE text USING
mycast($3)'
USING s, t, c;
END LOOP;
PERFORM pg_extension_uninstall('foo', old);
PERFORM pg_extension_install('foo', new);
-- ALTER TYPE the other way round
END;
Some other stuff could be needed to check about indexes to, storing a
list of them in a temp table then recreating them, but it seems to me
you can already hand craft the catalog queries now. But as it becomes
common practise, we might want to offer them in a more ready for
public consumption way.
Regards,
--
dim
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions User Design
On Jun 23, 2009, at 2:06 PM, Dimitri Fontaine wrote:
It'd be nice if it supported other core languages like PL/Perl, but
it's okay if it doesn't on the first round (I'd likely want to use
some CPAN modules in a PL/Perl extension, anyway).
At first sight I though you were talking about a non-issue, as I
meant that an extension should not have to be a .so (or dll) +
a .sql exposing it, but any SQL code PostgreSQL is able to
understand, plperl included.
Well, C code with /[.](?:so|dll|dylib)/ should be allowed as well.
But plpgsql and plperl are not available by default on databases, so
it makes sense to rise the question, and the right answer might be
to expose some (optional?) core components as extensions (version is
PG major version), in order for out-of-core extensions to be able to
depend on them being there.
Yes, and it could also be that a particular extension requires an
unsafe version of a PL. That would need to be included in the metadata
for the PL extension.
PL/pgSQL should be in core and enabled by default, IMHO. The other PLs
should be extensions.
I've been told pljava is complex in that it requires a part of
pljave to be there in order to be installable (like pseudo DDL coded
in pljava and needed in the installation procedure). So I'd prefer
not to go this far, just in case.
Well, if each PL aside from SQL, C, and PL/pgSQL is an extension, then
it's just a dependency, right?
In fact supporting custom classes GUCs seems to be part of what Tom
Dunstan did, so it should be ok to plan to have it?
http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27e...@mail.gmail.com
Ah, cool.
In fact PostGIS is a complex beast, in that it's registering typmod
like information about user columns into its own private tables
(think extension's catalog). Now that means circular dependancy of
some sort as restoring user data requires to have the PostGIS
private tables filled already, but as they refer user data (not sure
if it's targeting user tables ---DDL--- only), you have to already
have restored them.
Ouch. Must be a nightmare today, too.
Even if it's only targeting schema level stuff, you'd need to
restore the extension's data after the schema but before the data,
but the extension's itself (data types, indexes opclass, etc) BEFORE
the data.
I'm not sure you should target to support this level of complexity
(it has to be generic) in the first incantation of it, but if some
hacker tells me it's damn easy to get right with pg_depend, why not?
Yeah, we should KISS to start with.
Maybe it's just a (non native) misuse of vocabulary, I see contrib
as the current incarnation of the standard extension library and
would like to see it evolve into a list of reviewed and maintained
extensions, which in a later step you'll be able to remotely fetch
and install easily from source from postgresql.org services, or in
binary from your distribution package.
Oh, yeah, I'm on board with that.
But I think we'll still need a contrib/ like suite that core hackers
keep an eye on and maintain in minor branches and adapt in major
releases.
This says the same thing as the last paragraph, no? I don't think I'd
call such a distribution "contrib," though. Maybe standard extensions.
Now if we ever get to a point where we can setup an http repository
of easily installable extensions that you can point a built-in core
tool to, that means there will be the standard official one and a
myriad of others (pgfoundry, and self hosting).
Yes.
None of this, stay aboard :)
Register within the database where you'll want to install it. The
install step as shown below will then use the meta-data to do the
sanity checking (dependancies) and the installation (what script to
read?).
Oh, *that* kind of registration. Fine, of course!
It would need something to ensure an appropriate version, too, no?
So it's:
create schema foo;
install extension foo with version = 1.2, search_path = foo;
That's fine by me, but I'm not sure whether first extension's
implementation will support installing several versions of the same
extension in parallel, so I'm unsure what we get here... one more
sanity check? I buy it.
Yes, although as I said before, version numbers are hard to get right.
We should keep them very simple, with a strict requirement as to the
simple format (perhaps /\d+[.]\d{2}/) or perhaps NUMERIC or some other
core data type, and then we'd be able to use simple operators:
install extension foo with version = 1.2 OR version >= 1.4,
search_path = foo;
I don't think we want to cancel user ability to choose schema where
to install, so an idea could be to ask extensions author to
systematically use pg_extension (or non-qualify), and PostgreSQL
could replace this with the INSTALL EXTENSION command schema.
Replace what? How would pg_extension or INSTALL EXTENSION know to
magic
Re: [HACKERS] Extensions User Design
Le 23 juin 09 à 20:30, David E. Wheeler a écrit : On Jun 23, 2009, at 10:44 AM, Dimitri Fontaine wrote: - support for home grown SQL/PLpgSQL only extensions in order to make life easier for in-house PG based development (you don't have to code in C to benefit from extensions) It'd be nice if it supported other core languages like PL/Perl, but it's okay if it doesn't on the first round (I'd likely want to use some CPAN modules in a PL/Perl extension, anyway). At first sight I though you were talking about a non-issue, as I meant that an extension should not have to be a .so (or dll) + a .sql exposing it, but any SQL code PostgreSQL is able to understand, plperl included. But plpgsql and plperl are not available by default on databases, so it makes sense to rise the question, and the right answer might be to expose some (optional?) core components as extensions (version is PG major version), in order for out-of-core extensions to be able to depend on them being there. - support for procedural languages (a priori easily covered within basic modules but I'm not sure) like plproxy, pllolcode, pllua, plscheme, plsh et al. Oh, here it is. So this goes with the point above, and can be simplified to "support all procedural languages," yes? I've been told pljava is complex in that it requires a part of pljave to be there in order to be installable (like pseudo DDL coded in pljava and needed in the installation procedure). So I'd prefer not to go this far, just in case. - supporting more than one version of the same module installed in the same time, possibly (I suppose always but...) in different schemas Eh. This could be in 2.0 I think. Yeah, my point exactly. - custom variables? You mean GUC variables? That'd certainly be useful, but again, probably not necessary for 1.0. In fact supporting custom classes GUCs seems to be part of what Tom Dunstan did, so it should be ok to plan to have it? http://archives.postgresql.org/message-id/ca33c0a30804061349s41b4d8fcsa9c579454b27e...@mail.gmail.com Well, PostGIS is itself an extension, no? What we need, then, is dependency tracking. In fact PostGIS is a complex beast, in that it's registering typmod like information about user columns into its own private tables (think extension's catalog). Now that means circular dependancy of some sort as restoring user data requires to have the PostGIS private tables filled already, but as they refer user data (not sure if it's targeting user tables ---DDL--- only), you have to already have restored them. Even if it's only targeting schema level stuff, you'd need to restore the extension's data after the schema but before the data, but the extension's itself (data types, indexes opclass, etc) BEFORE the data. I'm not sure you should target to support this level of complexity (it has to be generic) in the first incantation of it, but if some hacker tells me it's damn easy to get right with pg_depend, why not? - a core team approved list of extensions (replacing contribs, maybe adding to it), where approved means code has been reviewed and the only reason why it's not in the core itself is that core team feels that it's not part of a RDBMS per-se, or feel like the code should be maintained and released separately until it gets some more field exposure... (think plproxy). I hate the idea of "approved" extensions, but would love to see a kind of "standard library" as a separate distribution that contains a bunch of stuff that's commonly used. I'd want to steer clear of blessing by the core team other than that, though, because then you start to get into politics. Maybe it's just a (non native) misuse of vocabulary, I see contrib as the current incarnation of the standard extension library and would like to see it evolve into a list of reviewed and maintained extensions, which in a later step you'll be able to remotely fetch and install easily from source from postgresql.org services, or in binary from your distribution package. But I think we'll still need a contrib/ like suite that core hackers keep an eye on and maintain in minor branches and adapt in major releases. Now if we ever get to a point where we can setup an http repository of easily installable extensions that you can point a built-in core tool to, that means there will be the standard official one and a myriad of others (pgfoundry, and self hosting). * later please Yes, this would be nice. Also, integrated testing as with CPAN. I happen to know of a really nice test framework we could use… hehe - complex support for ad-hoc bootstrap of uncommon modules such as pljava Not sure what this means; can you provide more detail? See above. - dependancy graph solving and automatic installation, with depends, recommends and suggest sections and with rules/setup to choose what to pull in by default... We'd l
Re: [HACKERS] 8.4RC2 is available
On Tue, 2009-06-23 at 09:25 -0400, Tom Lane wrote: > We spun an 8.4RC2 tarball, mainly to check that Peter's recent fixes > to the man page generation process actually worked. There will not be > binary builds of RC2, Actually RPMs will be available at yum repository sometimes around midnight PDT. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Extensions User Design
On Jun 23, 2009, at 10:44 AM, Dimitri Fontaine wrote: The contenders are extension, module, bundle and package. My vote is extension. +1 == v1.0 goals We're not trying to be feature complete on first round. * must have - dump & restore support (when upgrading a cluster or just restoring) - easy install and uninstall - support for home grown SQL/PLpgSQL only extensions in order to make life easier for in-house PG based development (you don't have to code in C to benefit from extensions) It'd be nice if it supported other core languages like PL/Perl, but it's okay if it doesn't on the first round (I'd likely want to use some CPAN modules in a PL/Perl extension, anyway). - support for "basic" modules, providing a type and its operators and indexing support, such as ip4r, hstore, temporal, prefix and many others, you name it, of even simpler things like preprepare or backports/min_update. - support for procedural languages (a priori easily covered within basic modules but I'm not sure) like plproxy, pllolcode, pllua, plscheme, plsh et al. Oh, here it is. So this goes with the point above, and can be simplified to "support all procedural languages," yes? - support for all what you find in contrib/ for 8.4 (covered already?) * would be great (target later commit fest) - versioning support with upgrade in place facility (hooks?) Yeah, we should standardize versioning somehow to make upgrading easier. It should be a simple as possible, IMHO. If it tries to do too much, you get stuck with great complexity. - supporting more than one version of the same module installed in the same time, possibly (I suppose always but...) in different schemas Eh. This could be in 2.0 I think. - custom variables? You mean GUC variables? That'd certainly be useful, but again, probably not necessary for 1.0. - PostGIS complete support, with user data dependancy, even if an extensible typmod system would certainly solve this problem in a better place. Maybe someone will come up with another existing extension sharing the problem and not the typmod solution? Well, PostGIS is itself an extension, no? What we need, then, is dependency tracking. - a core team approved list of extensions (replacing contribs, maybe adding to it), where approved means code has been reviewed and the only reason why it's not in the core itself is that core team feels that it's not part of a RDBMS per-se, or feel like the code should be maintained and released separately until it gets some more field exposure... (think plproxy). I hate the idea of "approved" extensions, but would love to see a kind of "standard library" as a separate distribution that contains a bunch of stuff that's commonly used. I'd want to steer clear of blessing by the core team other than that, though, because then you start to get into politics. * later please Yah. - CPAN or ports like infrastructure for auto downloading a more or less prepared "bundle", place it at the right place on the filesystem and install it in the database(s) of choice Yes, this would be nice. Also, integrated testing as with CPAN. I happen to know of a really nice test framework we could use… - complex support for ad-hoc bootstrap of uncommon modules such as pljava Not sure what this means; can you provide more detail? - dependancy graph solving and automatic installation, with depends, recommends and suggest sections and with rules/setup to choose what to pull in by default... We'd likely have to store this information in some sort of system table, too, yes? == dump & restore We want pg_dump to issue only one line per extension, the one installing the extension in the database, see syntax. == syntax Extensions will need metadata, and after reading several proposals, what I propose here is to have a first explicit step to register the extension name and metadata, then have "basic" tools to play with it. Register with whom? I have to say that, although there is namespace registration for CPAN, it's not required, and this is, in fact, a big part of the reason for CPAN's success. There is no approval process barrier to entry. === installing and removing an extension begin; install extension foo with search_path = foo; commit; It would need something to ensure an appropriate version, too, no? Extensions authors are asked not to bother about search_path in their sql scripts so that it's easy for DBAs to decide where to install them. The with strange syntax is there to allow for the "install extension" command to default to, e.g., pg_extension, which won't typically be the first schema in the search_path. And how will functions that call other functions within an extension know that they're calling those functions in the appropriate schema? I get this all the time with pgTAP: You can install it in its own schema
[HACKERS] Extensions User Design
Hi, = PostgreSQL Extensions Here's the first round of User Design about PostgreSQL Extensions. I tried to put together the ideas expressed by a lot of different people. The aim here is to first agree on the naming and the goals, then talk about what user design we propose. == name The contenders are extension, module, bundle and package. My vote is extension. The module is something else in the SQL standard, a bundle is an ok choice, a package would certainly make people think we're Oracle compatible (and we don't want to have Ada like skeleton and bodies), and extension is what PGXS is make for and what we -you-name-it- authors made. == v1.0 goals We're not trying to be feature complete on first round. * must have - dump & restore support (when upgrading a cluster or just restoring) - easy install and uninstall - support for home grown SQL/PLpgSQL only extensions in order to make life easier for in-house PG based development (you don't have to code in C to benefit from extensions) - support for "basic" modules, providing a type and its operators and indexing support, such as ip4r, hstore, temporal, prefix and many others, you name it, of even simpler things like preprepare or backports/min_update. - support for procedural languages (a priori easily covered within basic modules but I'm not sure) like plproxy, pllolcode, pllua, plscheme, plsh et al. - support for all what you find in contrib/ for 8.4 (covered already?) * would be great (target later commit fest) - versioning support with upgrade in place facility (hooks?) - supporting more than one version of the same module installed in the same time, possibly (I suppose always but...) in different schemas - custom variables? - PostGIS complete support, with user data dependancy, even if an extensible typmod system would certainly solve this problem in a better place. Maybe someone will come up with another existing extension sharing the problem and not the typmod solution? - a core team approved list of extensions (replacing contribs, maybe adding to it), where approved means code has been reviewed and the only reason why it's not in the core itself is that core team feels that it's not part of a RDBMS per-se, or feel like the code should be maintained and released separately until it gets some more field exposure... (think plproxy). * later please - CPAN or ports like infrastructure for auto downloading a more or less prepared "bundle", place it at the right place on the filesystem and install it in the database(s) of choice - complex support for ad-hoc bootstrap of uncommon modules such as pljava - dependancy graph solving and automatic installation, with depends, recommends and suggest sections and with rules/setup to choose what to pull in by default... == dump & restore We want pg_dump to issue only one line per extension, the one installing the extension in the database, see syntax. == syntax Extensions will need metadata, and after reading several proposals, what I propose here is to have a first explicit step to register the extension name and metadata, then have "basic" tools to play with it. === installing and removing an extension begin; install extension foo with search_path = foo; commit; Extensions authors are asked not to bother about search_path in their sql scripts so that it's easy for DBAs to decide where to install them. The with strange syntax is there to allow for the "install extension" command to default to, e.g., pg_extension, which won't typically be the first schema in the search_path. begin; drop extension foo [cascade]; commit; The "cascade" option is there to care about reverse depends. === creating extensions (authoring) The 'foo' extension author is meant to provide a +foo.sql+ file containing this: create extension foo with version 1.0 install [script] 'foo.install.sql' uninstall [script] 'foo.uninstall.sql' upgrade function upgrade_foo(old version, new version) [ custom_variable_classes 'a,b' configuration file 'foo.conf' ] depends on bar version 0.3 and on baz version >= 1.2; Here we suppose we have also a new datatype "version" to host the versionning information, with the associated operators. See http://packages.debian.org/sid/postgresql-8.3-debversion Doing it this way, we skip the need to provide a way of telling "next comands are meant for creating SQL objects which belongs to such extension", at the expense of forcing authors to manage upgrades to add objects. The upgrade function is mandatory, and has to return the installed version or null, meaning "please run the install script again, that's how I upgrade". The error management is to be made by means of RAISE EXCEPTION. If a specific function is to get called at install or uninstall time, it's easy enough to SELECT install_function(); from within the install script, aft
Re: [HACKERS] 8.4RC2 is available
On Jun 23, 2009, at 6:25 AM, Tom Lane wrote: We spun an 8.4RC2 tarball, mainly to check that Peter's recent fixes to the man page generation process actually worked. There will not be binary builds of RC2, but if anyone who's building from source wants to double check it, it should be on most mirrors by now. But not this one, I guess? ftp://ftp10.us.postgresql.org/pub/postgresql/source/ 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] PK not being restored
On Tue, Jun 23, 2009 at 2:11 AM, Rajdeep Das wrote: > Dear Sir/Madam, > > > > I am using postgresql db server for our production environment. I have > created a schema by the name “application”. The public schema is not being > used at all. I have noticed that suddenly, the public schema is having some > table definition and a whole lot of functions. I have no idea where it came > from. For you reference I will list the four tables and a couple of > functions that has crept in my database’s public schema. > > > > Tables: > > pg_ts_cfg > > pg_ts_cfgmap > > pg_ts_dict > > pg_ts_parser > what version postgresql? the 'ts' stands for 'text search'. how are the above tables blocking you from restoring your data? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PK not being restored
Dear Sir/Madam, I am using postgresql db server for our production environment. I have created a schema by the name "application". The public schema is not being used at all. I have noticed that suddenly, the public schema is having some table definition and a whole lot of functions. I have no idea where it came from. For you reference I will list the four tables and a couple of functions that has crept in my database's public schema. Tables: pg_ts_cfg pg_ts_cfgmap pg_ts_dict pg_ts_parser Functions: _get_parser_from_curcfg _int_contained The above extraneous db objects is interfering with the backup and restore of my application's database. As a result, when I restore my db, the primary keys are not being restored. Kindly advise. Rajdeep Ontrack Systems Ltd. Kolkata, India
[HACKERS] 8.4RC2 is available
We spun an 8.4RC2 tarball, mainly to check that Peter's recent fixes to the man page generation process actually worked. There will not be binary builds of RC2, but if anyone who's building from source wants to double check it, it should be on most mirrors by now. 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] Changed error message for blocks by prepared transactions
On Mon, 22 Jun 2009 19:24:28 -0400 Tom Lane wrote: > "Andreas 'ads' Scherbaum" writes: > > the small attached patch changes the error message for a blocked > > database in case there are prepared transactions. > > Isn't this duplicative of the errdetail_busy_db code? And anyway > I do not see a reason not to consider prepared transactions as > "other users". Because you know the details. Most other users check pg_stat_activity just to find out there is no other user connected. A prepared transaction is not a connected user, so the error message is still misleading. You are right with the errdetail_busy_db(), but that's only true for 8.4, not for earlier versions. In addition that's only true if you haven't supressed the hints like some scripts do (-q as example). Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project PGDay.eu 2009 in Paris, Nov. 6/7, http://www.pgday.eu/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
