Re: [HACKERS] md.c should not call files relations
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Want me to change those or are you on it already? I'm going to bed --- if you wanna do it, have at it ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha releases: How to tag
On Wednesday 05 August 2009 06:00:19 David Fetter wrote: If I'm understanding you correctly, you're saying that pg_migrator (or whatever actually does this) needs to be an official PostgreSQL project in order for us to be able to require that people use it. For what it's worth, I agree. Is it strictly necessary that its release cycles match exactly those of the database engine, or would it be OK for it to release as needed, not triggering a major PostgreSQL release? Right now, anything is possible. It mainly needs people to make something happen. The current maintainers of pg_migrator are still focused on making the 8.3 - 8.4 path working robustly. And after that, they will likely take a long rest. If people want pg_migrator to stay current with 8.5devel, they need to take it upon themselves to create repeatable tests and code up the necessary changes. And then later if that turns out to be a viable undertaking, we can consider whether we merge pg_migrator and make updating it a requirement for any patch. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Shipping documentation untarred
So the next step to documentation bliss is to get rid of the man.tar.gz and postgres.tar.gz tarballs that are shipped inside the tarball. These are historical artifacts from the era when building the documentation for release required manual interference, and that era ended yesterday at the latest. Here is how I would like to set this up: * Man pages are built into doc/src/sgml/man1 and doc/src/sgml/man7. This is already happening. * HTML files are built into doc/src/sgml/html. On installation, we just copy that directory. * In doc/src/sgml/Makefile, put distprep: man html so that both documentation formats are built when the tarball is built. An added twist is that derived files that are shipped in the release tarball must be built in the source directory, not in the build directory (cf. gram.c etc.). This is not a problem with a few options on the respective tools (famous last words ...), but I just want to warn about it. If people would find this behavior too weird for their personal development workflow, we could add another target or other option to get the behavior you want. Let me know. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG support for struct in INTO list
On Fri, Jul 31, 2009 at 11:42:33AM +0200, Boszormenyi Zoltan wrote:
made me look around more. Find the attached patch I came up with.
Now my previous test code works and produces similar C code
as without -C INFORMIX. Can it be this simple?
Unfortunately it is not.
Can you see anything wrong with this approach?
Yes, please look at the slightly changed test version that is attached to this
email.
If you use e.g. int instead of MYTYPE, it works nicely, but not with MYTYPE.
Please see the comments in adjust_informix to see what this function is
supposed to do.
Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
/*
* Test DECLARE ... SELECT ... INTO ...
* with string
* Does make ecpg segfault when run with -C INFORMIX
*/
#include stdio.h
#include stdlib.h
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL include test28.h;
EXEC SQL END DECLARE SECTION;
get_var(void)
{
EXEC SQL BEGIN DECLARE SECTION;
MYTYPE myvar;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE mycur CURSOR FOR SELECT * INTO :myvar FROM a1 WHERE id
= 1;
}
int main(int argc, char **argv) {
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL connect to test;
if (sqlca.sqlcode)
{
printf (connect error = %ld\n, sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL CREATE TABLE a1 (id int, t text, d2 numeric, c text);
EXEC SQL INSERT INTO a1 values(1, 'text1', 14.7, 'text2');
get_var();
EXEC SQL OPEN mycur;
EXEC SQL WHENEVER NOT FOUND GOTO out;
EXEC SQL FETCH FROM mycur;
printf(c = '%s'\n, myvar.c);
out:
EXEC SQL CLOSE mycur2;
EXEC SQL CLOSE mycur;
EXEC SQL DISCONNECT;
return 0;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG support for struct in INTO list
Michael Meskes írta:
On Fri, Jul 31, 2009 at 11:42:33AM +0200, Boszormenyi Zoltan wrote:
made me look around more. Find the attached patch I came up with.
Now my previous test code works and produces similar C code
as without -C INFORMIX. Can it be this simple?
Unfortunately it is not.
Can you see anything wrong with this approach?
Yes, please look at the slightly changed test version that is attached to
this email.
I have looked at it. The code seems to be invalid.
get_var(void)
{
EXEC SQL BEGIN DECLARE SECTION;
MYTYPE myvar;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE mycur CURSOR FOR SELECT * INTO :myvar FROM a1 WHERE id
= 1;
}
myvar is lost as soon as the function returns
and is not visible to calling functions.
I tried to compile your code (with my previous fix
in place, so at least :myvar is processed and C code is output):
$ make test28
ecpg -C INFORMIX test28.pgc
cc -g -O2 -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
-fwrapv -g -I/home/zozo/pgc84pre/include
-I/home/zozo/pgc84pre/include/postgresql/internal -c -o test28.o test28.c
test28.pgc:15: warning: return type defaults to ‘int’
test28.pgc:14: warning: no previous prototype for ‘get_var’
test28.pgc: In function ‘get_var’:
test28.pgc:17: warning: unused variable ‘myvar’
test28.pgc: In function ‘main’:
test28.pgc:45: error: ‘myvar’ undeclared (first use in this function)
test28.pgc:45: error: (Each undeclared identifier is reported only once
test28.pgc:45: error: for each function it appears in.)
make: *** [test28.o] Error 1
rm test28.c
Line 45 in the modified code sent by you is:
printf(c = '%s'\n, myvar.c);
and the compiler correctly complains.
If you use e.g. int instead of MYTYPE, it works nicely, but not with MYTYPE.
I modified getvar this way:
get_var(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int myid;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE mycur CURSOR FOR SELECT id INTO :myid FROM a1 WHERE id
= 1;
}
And the preprocessed code via ecpg -C INFORMIX is:
get_var(void)
{
/* exec sql begin declare section */
#line 17 test28.pgc
int myid ;
/* exec sql end declare section */
#line 18 test28.pgc
ECPG_informix_set_var( 0, ( myid ), __LINE__);\
/* declare mycur cursor for select id from a1 where id = 1 */
#line 20 test28.pgc
}
Some systems (stack-protector extensions to GCC, etc)
make the code segfault immediately as soon as the first
FETCH statement tries to touch the lost memory area.
Just because ECPG does some tricks with ECPG_informix_set_var()
and ECPG_informix_get_var() converting variable reference
to runtime pointer values, the code wouldn't get magically valid.
Please see the comments in adjust_informix to see what this function is
supposed to do.
I did and I don't understand. I think it's just a bug
in ESQL/C to accept such constructs.
Michael
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG support for struct in INTO list
On Wed, Aug 05, 2009 at 11:08:26AM +0200, Boszormenyi Zoltan wrote: I have looked at it. The code seems to be invalid. Yes, it is, I was too lazy to make it valid. If you just allocate the memory for the variable in get_var() it becomes valid. I tried to compile your code (with my previous fix in place, so at least :myvar is processed and C code is output): Yes, but incorrect one. Some systems (stack-protector extensions to GCC, etc) make the code segfault immediately as soon as the first FETCH statement tries to touch the lost memory area. Just because ECPG does some tricks with ECPG_informix_set_var() and ECPG_informix_get_var() converting variable reference to runtime pointer values, the code wouldn't get magically valid. Again, this doesn't matter in this case as we try to get the preprocessor to work with a test case as small as possible. I did and I don't understand. I think it's just a bug in ESQL/C to accept such constructs. I do not like this feature either, but it's there and therefore should work in our compatibility mode. And it does with non-struct variables. Just look at test/compat_informix/test_informix.pgc for a real and working example. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- 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] 8.4 win32 shared memory patch
On Tue, Aug 4, 2009 at 19:13, Kevin Fieldkevinjamesfi...@gmail.com wrote: On Sat, Aug 1, 2009 at 20:30, Kevin Fieldkevinjamesfi...@gmail.com wrote: The event viewer says: The description for Event ID ( 0 ) in Source ( PostgreSQL ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: pg_ctl: could not find postgres program executable And yes, I renamed it correctly... Check permissions on it. If you moved it at some point, it may have the wrong permissions. They should be the same as for the other .EXEs in that directory. The two files (new and old exe) have identical permissions. That's just weird. It could be that the postgres executable won't work - maybe because of some DLL issue. Can you run postgres -V on the executable, or does that give you some error? It reports the version correctly. Sorry...any other ideas? Irrk. I think it's time to break out Process Monitor (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) and get a trace of exactly what call is failing, and how. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] ECPG support for struct in INTO list
Michael Meskes írta: On Wed, Aug 05, 2009 at 11:08:26AM +0200, Boszormenyi Zoltan wrote: I have looked at it. The code seems to be invalid. Yes, it is, I was too lazy to make it valid. If you just allocate the memory for the variable in get_var() it becomes valid. With allocated memory, yes, the code would be valid. This means that what I did in my first patch for this problem in add_struct_to_head() (unrolling members of the struct) has to be done in adjust_informix(), turning it into a recursive function. I think this would be a good solution. What do you think? I tried to compile your code (with my previous fix in place, so at least :myvar is processed and C code is output): Yes, but incorrect one. Some systems (stack-protector extensions to GCC, etc) make the code segfault immediately as soon as the first FETCH statement tries to touch the lost memory area. Just because ECPG does some tricks with ECPG_informix_set_var() and ECPG_informix_get_var() converting variable reference to runtime pointer values, the code wouldn't get magically valid. Again, this doesn't matter in this case as we try to get the preprocessor to work with a test case as small as possible. I did and I don't understand. I think it's just a bug in ESQL/C to accept such constructs. I do not like this feature either, but it's there and therefore should work in our compatibility mode. And it does with non-struct variables. Just look at test/compat_informix/test_informix.pgc for a real and working example. Michael -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] the case for machine-readable error fields
Right now, I do this like this:
if ($err =~ /name_of_first_foreign_key/) {
$r-error_exit('First error message.')
}
elsif ($err =~ /name_of_second_foreign_key/) {
...
As an aside comment, a bit more regex foo with \b is indicated here :-)
if ($err =~ /\bname_of_first_foreign_key\b/) {
$r-error_exit('First error message.')
}
Andreas
--
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] DefaultACLs
Tom Lane wrote: So my feeling is that adding GRANT ON VIEW is a bad idea. The main argument for doing it seemed to be that the author wanted to be able to grant different default privileges for tables and views, but I'm unconvinced that there's a strong use-case for that. You could very Yes that was the intention. I do have users in my databases with access privileges to VIEWs but not to underlying TABLEs so it seemed like a good idea to be able to do that with DefaultACLs and GRANT ON ALL. Second: both this patch and GRANT ON ALL are built on the assumption that the only way to filter/classify objects is by schema membership. Now I don't object to that as an initial implementation restriction, but I don't like hard-wiring it into the syntax. It is very clear to me that we'll want other filter rules in the future --- an immediate example is being able to say that new children of an inheritance parent table should inherit its GRANTs. So to my mind, designing the syntax around ALTER SCHEMA is right out. Maybe we could do something like ALTER DEFAULT PRIVILEGES ON TABLES IN SCHEMA foo GRANT ... where the IN SCHEMA foo part would be subject to generalization later. This also matches up a bit better with the proposed syntax for GRANT ON ALL (which also uses IN SCHEMA foo). Actually I was planning to extend GRANT ON ALL - if it was accepted - to include more filters (something like OWNED BY for example). Third: speaking of syntax, I don't like the way that this is gratituously different from GRANT/REVOKE. I don't like using ADD/DROP instead of GRANT/REVOKE, nor the unnecessary AND business. I think we should minimize confusion by using something that is spelled as nearly like GRANT/REVOKE as possible. ADD/DROP was side product of having SET and that unnecessary AND business. If we went with that syntax you proposed we could just put exact same syntax as GRANT and REVOKE after the filtering option, that should be close enough :). I remember Stephen being against having GRANT in ALTER SCHEMA but I doubt he would be against having it in completely new ALTER DEFAULT PRIVILEGES statement. Fourth: the system's existing treatment of default permissions is owner-dependent, that is the implied set of permissions is typically GRANT ALL TO owner (from himself, with grant option). I do not understand how schema-level default ACLs will play nicely with that, except in the special case where the schema owner also owns every contained object. If you copy the schema-level ACL directly to a contained object with a different owner it will definitely be the wrong thing, but if you try to translate the ownership it will confuse people too. And confusion in a security-related behavior is a Bad Thing. Furthermore, having the schema owner able to control the grants made for objects not owned by him is a huge security hole. We were actually discussing this with Stephen yesterday as something similar occurred to me too. The patch as submitted just does the copy, after the discussion I added post-processing on object creation which translates everything to owner but I guess there is no point in submitting that now. What I suggest as a way to resolve this last point is that a default ACL should apply only to objects owned by the user who creates/modifies the default ACL. In this view, the question of which schema the objects are in is just an additional filter condition, not the primary determinant of which objects a default ACL applies to. Every user has his own set of default ACLs. We could certainly do that. I wonder what we should do about inheritance of default privileges between the roles if we did this - should it just be what I set is mine and my parent roles do not affect me or should it get default privs from parent roles and merge them with mine when I create the object ? Also when creating new default privileges entry should we use some template which would give owner all privileges like GRANT does when there are no existing privileges on object or should we just use blank and leave it to user to grant himself default privileges on objects he will create ? I don't think there is any point in you looking at code since DefaultACLs might need serious rewriting. GRANT ON ALL is a bit different story, there I can just remove all that VIEW stuff, although I would very much like to have the ability to affect only VIEWs. On the other hand removing VIEW as separate object type would remove my biggest problem with how both patches are implemented (I mentioned it few times in the previous discussion) so from that standpoint it might be a good thing. -- Regards Petr Jelinek (PJMODOS) -- 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] the case for machine-readable error fields
On Tue, Aug 04, 2009 at 01:12:10PM -0400, Alvaro Herrera wrote: First we need several new error message fields: table name, function name, constraint name, and so on. One possible way to go about this would be to give each new field its own start letter (see http://www.postgresql.org/docs/8.4/static/protocol-error-fields.html); say T for table, f for function (F is taken), c for constraint (C is taken), and so on. Another possibility would be to use a single letter, say N, and add a subtype to it; so table name would be NT followed by the table name, NF for functions, etc. As pointed out downstream this seems somewhat open-ended and arbitrary; I would start with just making the constraint name easy to get to--I hope this doesn't happen already as I can't see anything obvious. My rational is that everything (short of syntax errors and strange things in the procedural languages) is already associated with a constraint. Syntax errors seem awkward to get standardized reporting for, the syntax keeps changing meaning that reporting anything more than what we do now doesn't seem practically useful. The calling code isn't going to be able to generate different SQL depending on error messages we give back, a human is needed there and can still interpret the text as well as we've always done. Constraints failing are a useful thing that calling code can do useful things with and it makes sense to give this back. These would seem to capture everything you mentioned elsewhere except UNIQUE indexes that weren't created as a constraint. Maybe this could be fixed by turning them into a constraint? as they seem like one to me. What are people doing with parsing error messages for column names for datatype mismatches? I can't imagine any of my code being able to do anything sensible in such a case. If it's things like people giving dates to the database in an incorrect format then that's what they get for not doing input validation isn't it? -- Sam http://samason.me.uk/ -- 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: Use DocBook XSL stylesheets for man page building This switches
On Wednesday 05 August 2009 02:43:19 Alvaro Herrera wrote: I'm wondering if dropping SPI_* manpages is really what we want. Maybe we could add them to man section 3? I know I've wanted to have them a couple of times. Not sure about dblink. This was just the status quo. We could add more manpages, sure. (I'm interested in pgbench, in particular.) It just needs someone to go through the generated output and verify that it is sane. -- 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] machine-readable explain output v4
On Sun, Aug 2, 2009 at 7:57 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
The reason for this regression is that Tom asked me to change
ExplainStmt to just carry a list of nodes and to do all the parsing in
ExplainQuery. Unfortunately, the TupleDesc is constructed by
ExplainResultDesc() which can't trivially be changed to take an
ExplainState, because UtilityTupleDescriptor() also wants to call it.
We could possibly fix this by a hack similar to the one we already
added to GetCommandLogLevel(), but I haven't done that here.
I don't see anything particularly wrong with having ExplainResultDesc
do the same kind of thing GetCommandLogLevel is doing.
After I did this, I thought it would be useful to add a regression
test to make sure that it is doing the right thing. So I came up with
this:
CREATE OR REPLACE FUNCTION test_explain_format(text) RETURNS text AS $$
DECLARE
x RECORD;
BEGIN
EXECUTE 'explain (format ' || $1 || ') select 1' INTO x;
RETURN pg_typeof(x.QUERY PLAN);
END
$$ LANGUAGE plpgsql;
This works the first time you run it in a particular session, but then
if change $1 so as to get a different answer, it fails:
rhaas=# select test_explain_format('text');
test_explain_format
-
text
(1 row)
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=# discard
ALLPLANS TEMP
rhaas=# discard plans;
DISCARD PLANS
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=# discard all;
DISCARD ALL
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=#
If I quit psql and start back up again, then it works:
rhaas=# select test_explain_format('xml');
test_explain_format
-
xml
(1 row)
So I guess that leads me to -
(1) How do I make this work?
(2) Is it worth making this work?
...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] machine-readable explain output v4
Robert Haas wrote:
On Sun, Aug 2, 2009 at 7:57 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
The reason for this regression is that Tom asked me to change
ExplainStmt to just carry a list of nodes and to do all the parsing in
ExplainQuery. Unfortunately, the TupleDesc is constructed by
ExplainResultDesc() which can't trivially be changed to take an
ExplainState, because UtilityTupleDescriptor() also wants to call it.
We could possibly fix this by a hack similar to the one we already
added to GetCommandLogLevel(), but I haven't done that here.
I don't see anything particularly wrong with having ExplainResultDesc
do the same kind of thing GetCommandLogLevel is doing.
After I did this, I thought it would be useful to add a regression
test to make sure that it is doing the right thing. So I came up with
this:
CREATE OR REPLACE FUNCTION test_explain_format(text) RETURNS text AS $$
DECLARE
x RECORD;
BEGIN
EXECUTE 'explain (format ' || $1 || ') select 1' INTO x;
RETURN pg_typeof(x.QUERY PLAN);
END
$$ LANGUAGE plpgsql;
This works the first time you run it in a particular session, but then
if change $1 so as to get a different answer, it fails:
rhaas=# select test_explain_format('text');
test_explain_format
-
text
(1 row)
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=# discard
ALLPLANS TEMP
rhaas=# discard plans;
DISCARD PLANS
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=# discard all;
DISCARD ALL
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=#
If I quit psql and start back up again, then it works:
rhaas=# select test_explain_format('xml');
test_explain_format
-
xml
(1 row)
So I guess that leads me to -
(1) How do I make this work?
(2) Is it worth making this work?
You could have the function create an inner function which it then runs
and drops. I have had to perform such gymnastics in the past to get
around similar problems. And yes, it's ugly as hell.
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] the case for machine-readable error fields
What are people doing with parsing error messages for column names for datatype mismatches? I can't imagine any of my code being able to do anything sensible in such a case. If it's things like people giving dates to the database in an incorrect format then that's what they get for not doing input validation isn't it? When you have a full set of constraint, then you don't need to validate input. Just you will execute statement. When execution is correct, then all is ok, when not, then you have to recheck message, err code, ... and you have to verify, so some exception is expected or not. This is programming based on exceptions. Some better structured information helps. And what's more - this should be in conformity with ANSI SQL. regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Adding error message source
Since Alvaro is talking about error messages in another thread, I figured I should post this idea now as well. Something that keeps annoying me a lot when trying to look through what comes out of PostgreSQL logs is that errors generated by the user (syntax errors in queries, warnings due to incorrect string escaping, statements terminated due to timeout etc etc) are intermixed completely with warnings and errors from internal server functions like checkpoints and log archiving. This makes it much more harder than it should be to find the important messages. I'd like to add another field to messages called source (not wedded to the name). Initially, this could just be user and internal, but I can see a use-case in the future for it to differ between for example archiver and bgwriter (it's certainly not unheard of that one would want to look at all output from the archiver, but ignore all other output). This could then be written as a field in log_line_prefix, and obviously included as it's own column in CVS output, to allow for further processing outside the database. As for the source, I think we'd just decorate the error messages with errsource(ERRSOURCE_USER) or something like that at places where needed, and have it default to internal - so we don't have to touch each and every error message in the backend. Sometime in the future I am considering implementing the ability to filter messages to different targets (files, syslog facilities, whatever), and this would also be a very interesting field to do such filtering on. But that's for sometime much further in the future, I haven't even started thinking about *how* to do that. But it's another possible use-case for this decoration. Thoughts? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] ECPG dynamic cursor, SQLDA support
On Sat, Jul 25, 2009 at 04:38:25PM -0500, Jaime Casanova wrote: 1) This is in /src/interfaces/ecpg/ecpglib/sqlda.c, and doesn't seems something we want in our files... looking at actual code seems like ecpg.c have something similar but at least specify that it has the same license as PostgreSQL Oops, didn't notice that this still had the old text, fixed in CVS. + * (C) 2009 Cybertec GmbH + * Zoltán Böszörményi z...@cybertec.at + * Hans-Jürgen Schönig h...@cybertec.at + */ So am I right to assume that the consensus is to not accept this kind of notice? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- 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] machine-readable explain output v4
On Wed, Aug 5, 2009 at 7:20 AM, Andrew Dunstanand...@dunslane.net wrote:
Robert Haas wrote:
On Sun, Aug 2, 2009 at 7:57 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
The reason for this regression is that Tom asked me to change
ExplainStmt to just carry a list of nodes and to do all the parsing in
ExplainQuery. Unfortunately, the TupleDesc is constructed by
ExplainResultDesc() which can't trivially be changed to take an
ExplainState, because UtilityTupleDescriptor() also wants to call it.
We could possibly fix this by a hack similar to the one we already
added to GetCommandLogLevel(), but I haven't done that here.
I don't see anything particularly wrong with having ExplainResultDesc
do the same kind of thing GetCommandLogLevel is doing.
After I did this, I thought it would be useful to add a regression
test to make sure that it is doing the right thing. So I came up with
this:
CREATE OR REPLACE FUNCTION test_explain_format(text) RETURNS text AS $$
DECLARE
x RECORD;
BEGIN
EXECUTE 'explain (format ' || $1 || ') select 1' INTO x;
RETURN pg_typeof(x.QUERY PLAN);
END
$$ LANGUAGE plpgsql;
This works the first time you run it in a particular session, but then
if change $1 so as to get a different answer, it fails:
rhaas=# select test_explain_format('text');
test_explain_format
-
text
(1 row)
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=# discard
ALL PLANS TEMP
rhaas=# discard plans;
DISCARD PLANS
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=# discard all;
DISCARD ALL
rhaas=# select test_explain_format('xml');
ERROR: type of x.QUERY PLAN does not match that when preparing the plan
CONTEXT: PL/pgSQL function test_explain_format line 5 at RETURN
rhaas=#
If I quit psql and start back up again, then it works:
rhaas=# select test_explain_format('xml');
test_explain_format
-
xml
(1 row)
So I guess that leads me to -
(1) How do I make this work?
(2) Is it worth making this work?
You could have the function create an inner function which it then runs and
drops. I have had to perform such gymnastics in the past to get around
similar problems. And yes, it's ugly as hell.
hurls
Well, I guess I could do it like this:
CREATE OR REPLACE FUNCTION test_explain_format() RETURNS text[] AS $$
DECLARE
xt RECORD;
xx RECORD;
xj RECORD;
BEGIN
EXPLAIN (FORMAT TEXT) SELECT 1 INTO xt;
EXPLAIN (FORMAT XML) SELECT 1 INTO xx;
EXPLAIN (FORMAT JSON) SELECT 1 INTO xj;
RETURN ARRAY[
pg_typeof(xt.QUERY PLAN),
pg_typeof(xx.QUERY PLAN),
pg_typeof(xj.QUERY PLAN)
];
END
$$ LANGUAGE plpgsql;
Fortunately there is not an unlimited space to probe here...
...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] ECPG support for struct in INTO list
On Wed, Aug 05, 2009 at 11:52:57AM +0200, Boszormenyi Zoltan wrote: This means that what I did in my first patch for this problem in add_struct_to_head() (unrolling members of the struct) has to be done in adjust_informix(), turning it into a recursive function. I think this would be a good solution. What do you think? No, this doesn't seem right. There should be no need to unroll a struct. Instead I would think struct support should be added to the get_var/set_var functions. At least that's my guess without really digging into it. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- 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] the case for machine-readable error fields
On Wed, Aug 05, 2009 at 01:29:13PM +0200, Pavel Stehule wrote: What are people doing with parsing error messages for column names for datatype mismatches? I can't imagine any of my code being able to do anything sensible in such a case. If it's things like people giving dates to the database in an incorrect format then that's what they get for not doing input validation isn't it? When you have a full set of constraint, then you don't need to validate input. Just you will execute statement. OK, then we mean different things when we say validate input. I was just meaning simple things like checking dates are well formed and that you're not passing things like 'sam's test' into the database (i.e. that you're actually escaping things correctly). Constraints are different from input validation as they rely on state that the database's client by definition doesn't have (otherwise it would be able to do the constraint checking just as well as the database). When execution is correct, then all is ok, when not, then you have to recheck message, err code, ... and you have to verify, so some exception is expected or not. This is programming based on exceptions. Some better structured information helps. And what's more - this should be in conformity with ANSI SQL. Humans can interpret the current error messages just fine, I don't believe that code could do with better structured information. It would be possible to have the *Params libpq functions (not sure where this lives in the underlying protocols) give back errors when its inputs can't be parsed, but that seems like a different problem. Describing where problems are in a machine readable format from arbitrary code seems very fragile. -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] status of ECPG patches?
Michael, I confess I haven't been following the ECPG threads real closely, but I'm confused as to the status of the following two patches. Have you reviewed these? If so, what was the outcome? If not, do you plan to? When? ECPG dynamic cursor, SQLDA support ECPG support for string pseudo-type v2 See: https://commitfest.postgresql.org/action/commitfest_view?id=2 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] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on
On Tue, Jul 28, 2009 at 15:45, Tom Lanet...@sss.pgh.pa.us wrote:
Magnus Hagander mag...@hagander.net writes:
On Mon, Jul 27, 2009 at 16:14, Tom Lanet...@sss.pgh.pa.us wrote:
I'm not really insisting on a redesign. I'm talking about the places
where the code author appears not to have understood that ERROR means
FATAL, because the code keeps plugging on after it anyway. As far as
I can see, using ERROR at lines 3630, 3657, 3674, and 3693 is just
plain bogus, and changing to LOG there requires no other fixing.
But. I'll look into cleaning those up for HEAD anyway, but due to lack
of reports I think we should skip backpatch. Reasonable?
Fair enough.
Here's what I came up with. Seems ok?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
*** a/src/backend/postmaster/postmaster.c
--- b/src/backend/postmaster/postmaster.c
***
*** 3627,3633 internal_forkexec(int argc, char *argv[], Port *port)
* mess with the half-started process
*/
if (!TerminateProcess(pi.hProcess, 255))
! ereport(ERROR,
(errmsg_internal(could not terminate unstarted process: error code %d,
(int) GetLastError(;
CloseHandle(pi.hProcess);
--- 3627,3633
* mess with the half-started process
*/
if (!TerminateProcess(pi.hProcess, 255))
! ereport(LOG,
(errmsg_internal(could not terminate unstarted process: error code %d,
(int) GetLastError(;
CloseHandle(pi.hProcess);
***
*** 3654,3660 internal_forkexec(int argc, char *argv[], Port *port)
* process and give up.
*/
if (!TerminateProcess(pi.hProcess, 255))
! ereport(ERROR,
(errmsg_internal(could not terminate process that failed to reserve memory: error code %d,
(int) GetLastError(;
CloseHandle(pi.hProcess);
--- 3654,3660
* process and give up.
*/
if (!TerminateProcess(pi.hProcess, 255))
! ereport(LOG,
(errmsg_internal(could not terminate process that failed to reserve memory: error code %d,
(int) GetLastError(;
CloseHandle(pi.hProcess);
***
*** 3671,3677 internal_forkexec(int argc, char *argv[], Port *port)
{
if (!TerminateProcess(pi.hProcess, 255))
{
! ereport(ERROR,
(errmsg_internal(could not terminate unstartable process: error code %d,
(int) GetLastError(;
CloseHandle(pi.hProcess);
--- 3671,3677
{
if (!TerminateProcess(pi.hProcess, 255))
{
! ereport(LOG,
(errmsg_internal(could not terminate unstartable process: error code %d,
(int) GetLastError(;
CloseHandle(pi.hProcess);
***
*** 3680,3686 internal_forkexec(int argc, char *argv[], Port *port)
}
CloseHandle(pi.hProcess);
CloseHandle(pi.hThread);
! ereport(ERROR,
(errmsg_internal(could not resume thread of unstarted process: error code %d,
(int) GetLastError(;
return -1;
--- 3680,3686
}
CloseHandle(pi.hProcess);
CloseHandle(pi.hThread);
! ereport(LOG,
(errmsg_internal(could not resume thread of unstarted process: error code %d,
(int) GetLastError(;
return -1;
***
*** 4430,4437 extern int pgStatSock;
#define write_inheritable_socket(dest, src, childpid) (*(dest) = (src))
#define read_inheritable_socket(dest, src) (*(dest) = *(src))
#else
! static void write_duplicated_handle(HANDLE *dest, HANDLE src, HANDLE child);
! static void write_inheritable_socket(InheritableSocket *dest, SOCKET src,
pid_t childPid);
static void read_inheritable_socket(SOCKET *dest, InheritableSocket *src);
#endif
--- 4430,4437
#define write_inheritable_socket(dest, src, childpid) (*(dest) = (src))
#define read_inheritable_socket(dest, src) (*(dest) = *(src))
#else
! static bool write_duplicated_handle(HANDLE *dest, HANDLE src, HANDLE child);
! static bool write_inheritable_socket(InheritableSocket *dest, SOCKET src,
pid_t childPid);
static void read_inheritable_socket(SOCKET *dest, InheritableSocket *src);
#endif
***
*** 4448,4454 save_backend_variables(BackendParameters *param, Port *port,
#endif
{
memcpy(param-port, port, sizeof(Port));
! write_inheritable_socket(param-portsocket, port-sock, childPid);
strlcpy(param-DataDir, DataDir, MAXPGPATH);
--- 4448,4455
#endif
{
memcpy(param-port, port, sizeof(Port));
! if (!write_inheritable_socket(param-portsocket, port-sock, childPid))
! return false;
strlcpy(param-DataDir, DataDir, MAXPGPATH);
***
*** 4469,4475 save_backend_variables(BackendParameters *param, Port *port,
param-ProcGlobal = ProcGlobal;
param-AuxiliaryProcs = AuxiliaryProcs;
param-PMSignalState = PMSignalState;
! write_inheritable_socket(param-pgStatSock, pgStatSock, childPid);
param-PostmasterPid = PostmasterPid;
param-PgStartTime =
Re: [HACKERS] status of ECPG patches?
I confess I haven't been following the ECPG threads real closely, but I'm confused as to the status of the following two patches. Have you reviewed these? If so, what was the outcome? If not, do you plan to? I did a first review and then left for my vacation. Now Zoltan provided an updated version which I have to get into again. When? ECPG dynamic cursor, SQLDA support ECPG support for string pseudo-type v2 As soon as I find the time, but you probably know that the first days after coming back from vacation are kind of crazy. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG support for struct in INTO list
Michael Meskes írta:
On Wed, Aug 05, 2009 at 11:52:57AM +0200, Boszormenyi Zoltan wrote:
This means that what I did in my first patch for this
problem in add_struct_to_head() (unrolling members
of the struct) has to be done in adjust_informix(),
turning it into a recursive function.
I think this would be a good solution. What do you think?
No, this doesn't seem right. There should be no need to unroll a struct.
Instead I would think struct support should be added to the get_var/set_var
functions. At least that's my guess without really digging into it.
Michael
My question is: why not unroll the struct in the preprocessor?
This adjust_informix() issue aside, if I do this in the same function:
EXEC SQL BEGIN DECLARE SECTION;
MYTYPE myvar;
MYNULLTYPE mynullvar;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE mycur CURSOR FOR SELECT * INTO :myvar
:mynullvar FROM a1;
EXEC SQL OPEN mycur;
EXEC SQL WHENEVER NOT FOUND DO BREAK;
while (1) {
EXEC SQL FETCH FROM mycur;
...
}
then the preprocessed code for DECLARE and FETCH look like below:
{ ECPGdo(__LINE__, 1, 1, NULL, 0, ECPGst_normal, declare mycur
cursor for select * from a1 where id = 1, ECPGt_EOIT,
ECPGt_int,(myvar.id),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(myvar.t),(long)64,(long)1,(64)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_double,(myvar.d1),(long)1,(long)1,sizeof(double),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_double,(myvar.d2),(long)1,(long)1,sizeof(double),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(myvar.c),(long)30,(long)1,(30)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 39 test28.pgc
...
{ ECPGdo(__LINE__, 1, 1, NULL, 0, ECPGst_normal, fetch from
mycur, ECPGt_EOIT,
ECPGt_int,(myvar.id),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(myvar.t),(long)64,(long)1,(64)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_double,(myvar.d1),(long)1,(long)1,sizeof(double),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_double,(myvar.d2),(long)1,(long)1,sizeof(double),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
ECPGt_char,(myvar.c),(long)30,(long)1,(30)*sizeof(char),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 43 test28.pgc
These are done by ECPGdump_a_struct(), when the struct's members
and their type, size, etc are known. It's unrolled by the ecpg preprocessor.
Your idea about pushing struct support into set_var/get_var seems
not appropriate. With the current set_var scheme, you pass one variable's
properties. Why not keep this simplicity and unroll the struct in the
preprocessor into multiple set_var() calls for simple types?
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
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] status of ECPG patches?
Michael Meskes írta: I confess I haven't been following the ECPG threads real closely, but I'm confused as to the status of the following two patches. Have you reviewed these? If so, what was the outcome? If not, do you plan to? I did a first review and then left for my vacation. Now Zoltan provided an updated version which I have to get into again. You can do a diff between patches. I also wrote what changed in the patches since the last round. When? ECPG dynamic cursor, SQLDA support ECPG support for string pseudo-type v2 As soon as I find the time, but you probably know that the first days after coming back from vacation are kind of crazy. Yeah, browsing through 3000 mails is not easy, I was on vacation two weeks ago, too. :-) Take your time. Michael -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] Shipping documentation untarred
On Wed, Aug 5, 2009 at 09:24, Peter Eisentrautpete...@gmx.net wrote: So the next step to documentation bliss is to get rid of the man.tar.gz and postgres.tar.gz tarballs that are shipped inside the tarball. These are historical artifacts from the era when building the documentation for release required manual interference, and that era ended yesterday at the latest. Here is how I would like to set this up: * Man pages are built into doc/src/sgml/man1 and doc/src/sgml/man7. This is already happening. * HTML files are built into doc/src/sgml/html. On installation, we just copy that directory. * In doc/src/sgml/Makefile, put distprep: man html so that both documentation formats are built when the tarball is built. Just to verify, there is not going to be any changes in the actual format of the generated files, right? Since we pre-parse those before we load them on the website, we'd have to change the loader in that case. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Re: [COMMITTERS] pgsql: Use DocBook XSL stylesheets for man page building This switches
Peter Eisentraut wrote: On Wednesday 05 August 2009 02:43:19 Alvaro Herrera wrote: I'm wondering if dropping SPI_* manpages is really what we want. Maybe we could add them to man section 3? I know I've wanted to have them a couple of times. Not sure about dblink. This was just the status quo. We could add more manpages, sure. (I'm interested in pgbench, in particular.) It just needs someone to go through the generated output and verify that it is sane. Oh, great. I'll have a look at SPI pages. -- 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] md.c should not call files relations
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Want me to change those or are you on it already? I'm going to bed --- if you wanna do it, have at it ... Ok. I note that many of the messages currently print the relpath() of the relation, and don't include the affected segment suffix. For example: could not read block 14 of relation base/11566/24614: read only 1 of 8192 bytes If we change them to point to the exactly right filename including segment suffix, then the block number becomes confusing, since that would still refer block number within the relation, not the segment. Right now, the relation xxx is referring to the segmented virtual file as whole, not to any specific segment. One option is to revert those messages to 8.3 style: could not read block 14 of relation 1663/11566/24614: read only 1 of 8192 bytes We'd need to include the fork there, so at least for forks other than the main one it would become something like could not read block 14 of relation 1663/11566/24614/fsm: read only 1 of 8192 bytes Another option is to print the byte offset within segment file instead of block number: could not read 8129 bytes at offset 73138176 of file base/11566/24614.1: read only 1 bytes That feels more concise and describes accurately what the failing OS call was. However, it doesn't fit these two messages: cannot extend relation %s beyond %u blocks could not truncate relation %s to %u blocks: it's only %u blocks now since those genuinely don't refer to any particular segment. Also, if we want to support RELSEG_SIZE 4GB, we'd have to use INT64_FORMAT in the format strings, and I don't think that works nicely with translations. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] problem with splitting a string
Hi,
I'm trying to develop a contrib module in order to parse sqlf queries, I'm
using lemon as a LALR parser generator (because I think it's easier than
bison) and re2c (because I think it's easier than flex) but when I try to
split the string into words postgres add some weird characters (this works
in pure gcc), I write something like CREATE FUZZY PREDICATE joven ON 0..120
AS (0,0,35,120);, but postgresql adds a character like at the end of
joven and the others words.
The code I use to split the string is:
void parse_query(char *str,const char **sqlf){
parse_words(str);
*sqlf=fuzzy_query;
}
void parse_words(char *str){
char *word;
int token;
const char semicolon =';';
const char dot='.';
const char comma=',';
const char open_bracket='(';
const char close_bracket=')';
struct Token sToken;
int i = 0;
void* pParser = ParseAlloc (malloc);
while(str[i] !='\0'){
int c=0;
word=(char *)malloc(sizeof(char));
if(isspace(str[i]) || str[i]==semicolon){
i++;
continue;
}
if (str[i]==open_bracket || str[i]==close_bracket ||
str[i]==dot || str[i]==comma){
word[c] = str[i];
i++;
token=scan(word, strlen(word));
Parse(pParser, token, sToken);
continue;
}else{
while(!isspace(str[i]) str[i]!=semicolon str[i]!='\0'
str[i]!=open_bracket str[i]!=close_bracket
str[i]!=dot str[i]!=comma){
word[c++] = str[i++];
}
}
token=scan(word, strlen(word));
if (token==PARAMETRO){
//TODO: I don't know why it needs the malloc function again, all
I know is it's working
const char *param=word;
word= (char *)malloc(sizeof(char));
sToken.z=param;
}
Parse(pParser, token, sToken);
free(word);
}
Parse(pParser, 0, sToken);
ParseFree(pParser, free );
}
Header:
#ifndef SQLF_H_
#define SQLF_H_
typedef struct Token {
const char *z;
int value;
unsigned n;
} Token;
void parse_query(char *str,const char **sqlf);
void parse_words(char *str);
int scan(char *s, int l);
#endif /* SQLF_H_ */
Screen:
postgres=# select * from fuzzy.sqlf('CREATE FUZZY PREDICATE joven ON 0..120
AS (0,0,35,120);'::text);
ERROR: syntax error at or near
LINE 1: INSERT INTO fuzzydb.pg_fuzzypredicate VALUES(joven,0�
�,120
...
^
QUERY: INSERT INTO fuzzydb.pg_fuzzypredicate VALUES(joven,0�
�,120
�,0�
�,0�
�,35
�,120
�);
Thanks for any help
Re: [HACKERS] ECPG support for struct in INTO list
On Wed, Aug 05, 2009 at 03:04:00PM +0200, Boszormenyi Zoltan wrote: My question is: why not unroll the struct in the preprocessor? The problem is not that the struct is unrolled in the preprocessor. I just don't like the idea of having two places where structs are unrolled when one could be sufficient. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- 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 error message source
Magnus Hagander mag...@hagander.net writes: I'd like to add another field to messages called source (not wedded to the name). Initially, this could just be user and internal, but I can see a use-case in the future for it to differ between for example archiver and bgwriter (it's certainly not unheard of that one would want to look at all output from the archiver, but ignore all other output). I think you'd have great difficulty making this work reliably --- there are lots of messages that could be emitted in multiple contexts. Just about all of bufmgr.c, for instance. 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] status of ECPG patches?
On Wed, Aug 5, 2009 at 9:02 AM, Michael Meskesmes...@postgresql.org wrote: I confess I haven't been following the ECPG threads real closely, but I'm confused as to the status of the following two patches. Have you reviewed these? If so, what was the outcome? If not, do you plan to? I did a first review and then left for my vacation. Now Zoltan provided an updated version which I have to get into again. When? ECPG dynamic cursor, SQLDA support ECPG support for string pseudo-type v2 As soon as I find the time, but you probably know that the first days after coming back from vacation are kind of crazy. Yep, thanks for the update. ...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] Adding error message source
Magnus Hagander wrote: Something that keeps annoying me a lot when trying to look through what comes out of PostgreSQL logs is that errors generated by the user (syntax errors in queries, warnings due to incorrect string escaping, statements terminated due to timeout etc etc) are intermixed completely with warnings and errors from internal server functions like checkpoints and log archiving. This makes it much more harder than it should be to find the important messages. I'd like to add another field to messages called source (not wedded to the name). Initially, this could just be user and internal, but I can see a use-case in the future for it to differ between for example archiver and bgwriter (it's certainly not unheard of that one would want to look at all output from the archiver, but ignore all other output). This could then be written as a field in log_line_prefix, and obviously included as it's own column in CVS output, to allow for further processing outside the database. As for the source, I think we'd just decorate the error messages with errsource(ERRSOURCE_USER) or something like that at places where needed, and have it default to internal - so we don't have to touch each and every error message in the backend. Are you suggesting that all messages would have source of internal, until we get around to change them? That doesn't seem nice. There is a *lot* of messages that are not internal. I think we should classify all messages correctly, or not at all. Decorating every ereport() seems like a daunting effort, both up-front and to maintain. And it could not easily differentiate between e.g write failed error coming from bgwriter and a backend. Could we deduce the category through some other means? Messages related to bgwriter or archiver, for example, would be differentiate by looking at what the current process is. Differentiating between write failed because disk is full and syntax error because you typoed a query would be harder. Maybe we could classify all messages coming from md.c and smgr.c into a storage category, but you'd likely need a lot of exceptions to that rule. Would you like to propose a concrete list sources that we would have? The implementation effort depends a lot on the categorization. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Robert Haas robertmh...@gmail.com writes: (2) Is it worth making this work? No, I don't think so. The odds of such a test ever showing anything interesting seem minimal. plpgsql's inability to cope with the case would be nice to fix, but I'm not holding my breath for it... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG support for struct in INTO list
Michael Meskes írta:
On Wed, Aug 05, 2009 at 11:08:26AM +0200, Boszormenyi Zoltan wrote:
I have looked at it. The code seems to be invalid.
Yes, it is, I was too lazy to make it valid. If you just allocate the memory
for the variable in get_var() it becomes valid.
If you meant like this below, then ECPG segfaults on this too:
int *
get_var(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int *myvar;
EXEC SQL END DECLARE SECTION;
myvar = malloc(sizeof(int));
EXEC SQL DECLARE mycur CURSOR FOR SELECT id INTO :myvar FROM a1
WHERE id = 1;
return myvar;
}
ecpg_type_name() aborts, ECPGt_array is unhandled
besides struct and union, it's called at the same place
in adjust_informix() as ECPGt_struct.
Attached is my modified test28.pgc. Compiling it
*without* -C INFORMIX makes it unusable, the variable
or the address where the data should be fetched into
doesn't even gets emitted in neither the DECLARE/OPEN
nor the FETCH callsites. I think this code should be valid
even in non-Informix-compatible mode.
... Just look at
test/compat_informix/test_informix.pgc for a real and working example.
The example there is the other way around.
The variable, the DECLARE and FETCH commands
are in the outer main() function, and it calls a function called
openit() where the OPEN command is emitted, so that
example doesn't help here too much.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
/*
* Test DECLARE ... SELECT ... INTO ...
* with string
* Does make ecpg segfault when run with -C INFORMIX
*/
#include stdio.h
#include stdlib.h
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL include test28.h;
EXEC SQL END DECLARE SECTION;
EXEC SQL BEGIN DECLARE SECTION;
int *myvar1;
EXEC SQL END DECLARE SECTION;
int *
get_var(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int *myvar;
EXEC SQL END DECLARE SECTION;
myvar = malloc(sizeof(int));
EXEC SQL DECLARE mycur CURSOR FOR SELECT id INTO :myvar FROM a1 WHERE
id = 1;
return myvar;
}
int main(int argc, char **argv) {
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL connect to test;
if (sqlca.sqlcode)
{
printf (connect error = %ld\n, sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL CREATE TABLE a1 (id int, t text, d2 numeric, c text);
EXEC SQL INSERT INTO a1 values(1, 'text1', 14.7, 'text2');
myvar1 = get_var();
EXEC SQL OPEN mycur;
EXEC SQL WHENEVER NOT FOUND GOTO out;
EXEC SQL FETCH FROM mycur;
printf(id = %d\n, *myvar1);
out:
EXEC SQL CLOSE mycur2;
EXEC SQL CLOSE mycur;
EXEC SQL DISCONNECT;
return 0;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG support for struct in INTO list
Michael Meskes írta:
On Wed, Aug 05, 2009 at 03:04:00PM +0200, Boszormenyi Zoltan wrote:
My question is: why not unroll the struct in the preprocessor?
The problem is not that the struct is unrolled in the preprocessor. I just
don't like the idea of having two places where structs are unrolled when one
could be sufficient.
Yes, one place should be sufficient. Read my other mail about
the modified get_var() function. I start to get convinced that
adjust_informix() and ECPG_informix_{get|set}_var() should not
be Informix compat specific at all.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
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] the case for machine-readable error fields
Sam Mason s...@samason.me.uk wrote: On Wed, Aug 05, 2009 at 01:29:13PM +0200, Pavel Stehule wrote: When you have a full set of constraint, then you don't need to validate input. Just you will execute statement. Constraints are different from input validation as they rely on state that the database's client by definition doesn't have (otherwise it would be able to do the constraint checking just as well as the database). Just because something *can* also be checked within the front end doesn't mean it's best if it *is* checked there. When we were using a client/server model, we couldn't put the validations just in the client software, because there might be many places which could cause a violation of the business rule, and it was not reliable to count on all programmers knowing every rule and where it would need to be enforced. On top of that, there are cases where data is modified outside of the normal application software, and constraints only enforced in the application obviously provide no protection for data integrity in those cases. Attempting to put enforcement just in the RDBMS layer was tricky, though, because the messages tend to be written from the perspective of a database hacker, and tended to confuse or frighten the less computer-savvy staff using the software. I won't get into the all details of how we've dealt with this; primarily I want to chime in that it is a real problem. Briefly, though, our solution in the multi-tier environment did involve creating the ability to associate unique SQLSTATE values with failure of individual constraints for which there weren't well defined values (like there are for duplicate keys, for example). We could then have business write a friendly message for each such SQLSTATE. The more general ones were trickier, and I can say from experience that the ability to reliably pick off a table name or two when there's a duplicate key or a foreign key violation is critical to user-friendly behavior. Trying to enforce identical constraints in both the client code (for friendly behavior) and the database side (for better data integrity) is fraught with obvious problems. Anyway, the upshot is -- I think that it would be beneficial to allow, to the extent we can confirm it's not a violation of any applicable standard, a user-defined SQLSTATE to be associated with a constraint. I also think that it would be valuable to provide a mechanism for PostgreSQL-specific application code to be able to pick off one or two table names related to a standard constraint violation. I'm less convinced at the column or data value level, but I can see where it might be useful. Oh, and I've got nothing against XML as long as it's not exposed to a human being or application code. -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] problem with splitting a string
Werner Echezuria werc...@gmail.com writes: I'm trying to develop a contrib module in order to parse sqlf queries, I'm using lemon as a LALR parser generator (because I think it's easier than bison) and re2c (because I think it's easier than flex) but when I try to split the string into words postgres add some weird characters (this works in pure gcc), I write something like CREATE FUZZY PREDICATE joven ON 0..120 AS (0,0,35,120);, but postgresql adds a character like at the end of joven and the others words. Maybe you are expecting 'text' values to be null-terminated? They are not. You might look into using TextDatumGetCString or related functions to convert. regards, tom lane PS: the chances of us accepting a contrib module that requires significant unusual infrastructure to build seem pretty low from where I sit. You're certainly free to do whatever you want for private work, or even for a pgfoundry project --- but if you do have ambitions of this eventually becoming contrib, it's easier is not going to be sufficient rationale to not use bison/flex. -- 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] md.c should not call files relations
On Aug 4, 2009, at 11:10 PM, Tom Lane wrote: Want me to change those or are you on it already? I'm going to bed --- if you wanna do it, have at it ... Oh please. Everyone knows that you don't sleep, Tom. You just sit back in your chair and power nap for five minutes once in a while, perhaps between reading the -hackers and -general mail lists. ;-P 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] ECPG support for struct in INTO list
Boszormenyi Zoltan írta:
Michael Meskes írta:
On Wed, Aug 05, 2009 at 11:08:26AM +0200, Boszormenyi Zoltan wrote:
I have looked at it. The code seems to be invalid.
Yes, it is, I was too lazy to make it valid. If you just allocate the memory
for the variable in get_var() it becomes valid.
If you meant like this below, then ECPG segfaults on this too:
int *
get_var(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int *myvar;
EXEC SQL END DECLARE SECTION;
myvar = malloc(sizeof(int));
EXEC SQL DECLARE mycur CURSOR FOR SELECT id INTO :myvar FROM a1
WHERE id = 1;
return myvar;
}
And another problem that we have run into already.
ECPG is a one-stage preprocessor, instead of a two-stage one.
If the above function is located later in the source file than
the OPEN mycur or FETCH mycur, then ECPG complains
about an unknown cursor. Not a big annoyance, but ESQL/C
supports that.
ecpg_type_name() aborts, ECPGt_array is unhandled
besides struct and union, it's called at the same place
in adjust_informix() as ECPGt_struct.
Attached is my modified test28.pgc. Compiling it
*without* -C INFORMIX makes it unusable, the variable
or the address where the data should be fetched into
doesn't even gets emitted in neither the DECLARE/OPEN
nor the FETCH callsites. I think this code should be valid
even in non-Informix-compatible mode.
... Just look at
test/compat_informix/test_informix.pgc for a real and working example.
The example there is the other way around.
The variable, the DECLARE and FETCH commands
are in the outer main() function, and it calls a function called
openit() where the OPEN command is emitted, so that
example doesn't help here too much.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
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 error message source
2009/8/5 Magnus Hagander mag...@hagander.net: Since Alvaro is talking about error messages in another thread, I figured I should post this idea now as well. Something that keeps annoying me a lot when trying to look through what comes out of PostgreSQL logs is that errors generated by the user (syntax errors in queries, warnings due to incorrect string escaping, statements terminated due to timeout etc etc) are intermixed completely with warnings and errors from internal server functions like checkpoints and log archiving. This makes it much more harder than it should be to find the important messages. I'd like to add another field to messages called source (not wedded to the name). Initially, this could just be user and internal, but I can see a use-case in the future for it to differ between for example archiver and bgwriter (it's certainly not unheard of that one would want to look at all output from the archiver, but ignore all other output). This could then be written as a field in log_line_prefix, and obviously included as it's own column in CVS output, to allow for further processing outside the database. As for the source, I think we'd just decorate the error messages with errsource(ERRSOURCE_USER) or something like that at places where needed, and have it default to internal - so we don't have to touch each and every error message in the backend. Sometime in the future I am considering implementing the ability to filter messages to different targets (files, syslog facilities, whatever), and this would also be a very interesting field to do such filtering on. But that's for sometime much further in the future, I haven't even started thinking about *how* to do that. But it's another possible use-case for this decoration. I agree with some new error attribs, but about this, I don't know. Can you show some use case? SQLCODE could carry enough information about user or system exception. There are reserved space for custom codes. Maybe for administration should be interesting, if error is system error or application error - but this should be described by SQLCODE well too. Pavel Thoughts? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] md.c should not call files relations
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I note that many of the messages currently print the relpath() of the relation, and don't include the affected segment suffix. For example: could not read block 14 of relation base/11566/24614: read only 1 of 8192 bytes If we change them to point to the exactly right filename including segment suffix, then the block number becomes confusing, since that would still refer block number within the relation, not the segment. Hmm, good point. I don't think the byte-offset solution is usable, because of the INT64_FORMAT problem. What I would vote for is just continuing to show the block number relative to the whole relation, while (as much as possible) showing the actual filesystem pathname of the file being mentioned. This would mean that anyone trying to interpret the block number would have to be aware of what it meant and do the appropriate modulo calculation, but frankly I doubt that all that many people will care about exactly what offset is implied. BTW, I wonder whether it would be worth adding an entry point to fd.c to return the path name associated with a logical fd, rather than sprinkling extra relpath() calls throughout these messages. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on
Magnus Hagander mag...@hagander.net writes: But. I'll look into cleaning those up for HEAD anyway, but due to lack of reports I think we should skip backpatch. Reasonable? Fair enough. Here's what I came up with. Seems ok? Works for me. 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] DefaultACLs
Petr Jelinek pjmo...@pjmodos.net writes: Tom Lane wrote: What I suggest as a way to resolve this last point is that a default ACL should apply only to objects owned by the user who creates/modifies the default ACL. In this view, the question of which schema the objects are in is just an additional filter condition, not the primary determinant of which objects a default ACL applies to. Every user has his own set of default ACLs. We could certainly do that. I wonder what we should do about inheritance of default privileges between the roles if we did this - should it just be what I set is mine and my parent roles do not affect me or should it get default privs from parent roles and merge them with mine when I create the object ? I don't believe there is any inheritance needed or involved. A default ACL would only be looked up for use at the instant of creating an object, and what you'd look for is one owned by the same userID that is going to own the object being created. Anything else will be too complicated to be understandable. The commands that actually create/alter a default ACL would work on those belonging to whatever the effective userID is. Also when creating new default privileges entry should we use some template which would give owner all privileges like GRANT does when there are no existing privileges on object or should we just use blank and leave it to user to grant himself default privileges on objects he will create ? It should start from the same initial state you'd have if you didn't have a default ACL. Anything else violates the principle of least astonishment. 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] md.c should not call files relations
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I note that many of the messages currently print the relpath() of the relation, and don't include the affected segment suffix. For example: could not read block 14 of relation base/11566/24614: read only 1 of 8192 bytes If we change them to point to the exactly right filename including segment suffix, then the block number becomes confusing, since that would still refer block number within the relation, not the segment. Hmm, good point. I don't think the byte-offset solution is usable, because of the INT64_FORMAT problem. What I would vote for is just continuing to show the block number relative to the whole relation, while (as much as possible) showing the actual filesystem pathname of the file being mentioned. This would mean that anyone trying to interpret the block number would have to be aware of what it meant and do the appropriate modulo calculation, but frankly I doubt that all that many people will care about exactly what offset is implied. Ok. The most likely scenario where it would be confusing would be if you get an error along the lines of read error on block 20 in file XXX.1: you look at file XXX.1 and conclude that the file must be truncated because the file is much shorter than 20 blocks. Some low-level knowledge is indeed needed to interpret that correctly, but then again knowing to multiply by 8192 to get the offset is low-level knowledge to begin with. BTW, I wonder whether it would be worth adding an entry point to fd.c to return the path name associated with a logical fd, rather than sprinkling extra relpath() calls throughout these messages. Yes. I was going to add a function to md.c to construct the filename from (SmgrRelation, ForkNumber, segment number), but that's an even better idea. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] problem with splitting a string
Hi,
Well, I use TextDatumGetCString in the main file, but it remains with the
weird characters.
this is the main file:
#include postgres.h
#include fmgr.h
#include gram.h
#include sqlf.h
#include utils/builtins.h
extern Datum sqlf(PG_FUNCTION_ARGS);
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(sqlf);
Datum
sqlf(PG_FUNCTION_ARGS){
char*query = TextDatumGetCString(PG_GETARG_DATUM(0));
const char*parse_str;
char *result;
parse_query(query,parse_str);
result=parse_str;
PG_RETURN_TEXT_P(cstring_to_text(result));
}
About the PS: Ok, I understand that if I want that you include this as a
contrib module I need to use bison/flex, I never thought about it, but I now
have a couple of questions:
What are the chances to really include it in PostgreSQL as a contrib module?
Are there any requirement I have to follow?
2009/8/6 Tom Lane t...@sss.pgh.pa.us
Werner Echezuria werc...@gmail.com writes:
I'm trying to develop a contrib module in order to parse sqlf queries,
I'm
using lemon as a LALR parser generator (because I think it's easier than
bison) and re2c (because I think it's easier than flex) but when I try to
split the string into words postgres add some weird characters (this
works
in pure gcc), I write something like CREATE FUZZY PREDICATE joven ON
0..120
AS (0,0,35,120);, but postgresql adds a character like at the end of
joven and the others words.
Maybe you are expecting 'text' values to be null-terminated? They are
not. You might look into using TextDatumGetCString or related functions
to convert.
regards, tom lane
PS: the chances of us accepting a contrib module that requires
significant unusual infrastructure to build seem pretty low from
where I sit. You're certainly free to do whatever you want for
private work, or even for a pgfoundry project --- but if you do
have ambitions of this eventually becoming contrib, it's easier
is not going to be sufficient rationale to not use bison/flex.
Re: [HACKERS] problem with splitting a string
Werner Echezuria werc...@gmail.com writes: Well, I use TextDatumGetCString in the main file, but it remains with the weird characters. Hmm, no ideas then. Your interface code looks fine (making parse_str const seems a bit strange, but it's not related to the problem at hand). Given that the problems appear at token boundaries I'd guess that re2c isn't behaving the way you expect, but I'm not familiar with that tool so I can't give any specific advice. About the PS: Ok, I understand that if I want that you include this as a contrib module I need to use bison/flex, I never thought about it, but I now have a couple of questions: What are the chances to really include it in PostgreSQL as a contrib module? Are there any requirement I have to follow? Well, it'd mainly be a question of whether there's enough interest out there, which I can't judge. From a project standpoint we just require that it be BSD-licensed and not impose any undue new burden on maintainers (thus not wanting new build tools), but beyond that it's a matter of how many people might use it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] problem with splitting a string
Tom Lane escribió: Well, it'd mainly be a question of whether there's enough interest out there, which I can't judge. From a project standpoint we just require that it be BSD-licensed and not impose any undue new burden on maintainers (thus not wanting new build tools), but beyond that it's a matter of how many people might use it. What use is there for fuzzy predicates? I think it would mainly be to stop more students from coming up with new implementations of the same thing over and over. -- 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] Executor Material
Thanks for help Edson Ramiro On Tue, Aug 4, 2009 at 17:49, Tom Lane t...@sss.pgh.pa.us wrote: Edson Ramiro erlfi...@gmail.com writes: Does someone has some material which explain how the executor works? Did you read http://developer.postgresql.org/pgdocs/postgres/overview.html and src/backend/executor/README? Once you get through those, reading the source code is the next step. regards, tom lane
Re: [HACKERS] the case for machine-readable error fields
On Wed, Aug 05, 2009 at 09:30:02AM -0500, Kevin Grittner wrote: Anyway, the upshot is -- I think that it would be beneficial to allow, to the extent we can confirm it's not a violation of any applicable standard, a user-defined SQLSTATE to be associated with a constraint. I also think that it would be valuable to provide a mechanism for PostgreSQL-specific application code to be able to pick off one or two table names related to a standard constraint violation. I'm less convinced at the column or data value level, but I can see where it might be useful. Not sure if overloading SQLSTATE is the right way of doing this is it? It already has things like 23514 for a check violation and any other client code relying in this would break if it started getting different things back. -- Sam http://samason.me.uk/ p.s. I think you were agreeing with everything else I was saying, even if I didn't explain myself well enough for you to understand me! -- 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] async notification patch for dblink
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256
Tom Lane wrote:
Could you do something like
be_pid = pg_backend_pid() AS is_self_notify
instead, to verify that it's a self-notify? (This is not quite right
because you'd need to execute pg_backend_pid() at the remote end, but
I'm not awake enough to remember the dblink syntax for that. Maybe
it's too complex to be worth it, but I think demonstrating how to
check for self-notify would be a useful bit of doco.)
Otherwise it looks ok to me.
Committed. Final version attached.
Joe
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iQIcBAEBCAAGBQJKea/ZAAoJEDfy90M199hlZP8P/RcOrp9nHjAqVilQlHbRdlGO
7xTq2e9LU6tY2V0mzLcLZQ5SY6m12gNbQSIE6/8cNO3nWTjbm0TOYWpwKohBLvBt
0QQMUxi4JWapJcplaE10pt3xOT5+Kqn1mDG97Id92DwHTT7JtIBciDGWTuVWyQu3
8YJcbPzcTtGzL3lOTMFbZss10Lr7bLEMx0UmPJiMWDMqKIpmgC1cegIL2M54jol/
/fFx3mlz52O2F/maPtm4noBWsrDP6x/T7K8hspsqyWP0Xv52xPZ5qRorXRK3mDMb
U1jpAi/jqWqJ3X3riTeda39dg2wxZY1feOn42NBFTilbwHnpT+a6nSaR55/ZgnMp
7rlnSMOZTHCxgPOGZFXUNZsgf0HNME/2jFpfRhtbGzIre/iXcHUfhAbOiD72Gxdv
so+IOwbAEDXtIIwCAufAVZG/OvweEH8y9M3MytY82ozfmLiVwd6MvHuvjAixWrK/
/rVhH3d3j3oZh6dnjwOpZPvdQuqPJdl0sU5vvatHBMH8Af2gfSKFdmBlG47D9LGn
Brish7KmQb9u4hBinVNyMac9V/VVmUbY0K4fLQru8DtElWZzCyTRylkmiUqFtKeu
0OSx/vO/csR2Wa83hgyYLWGR8ShgYNX5Fws/BMtdzxnD4mjguJ2FI2FyduXGZ3GK
EyVBHi8/NBXt6DNoOhbS
=uKPZ
-END PGP SIGNATURE-
Index: contrib/dblink/dblink.c
===
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.82
diff -c -r1.82 dblink.c
*** contrib/dblink/dblink.c 11 Jun 2009 14:48:50 - 1.82
--- contrib/dblink/dblink.c 4 Aug 2009 13:41:26 -
***
*** 1635,1640
--- 1635,1723
PG_RETURN_DATUM(current_query(fcinfo));
}
+ /*
+ * Retrieve async notifications for a connection.
+ *
+ * Returns an setof record of notifications, or an empty set if none recieved.
+ * Can optionally take a named connection as parameter, but uses the unnamed connection per default.
+ *
+ */
+ #define DBLINK_NOTIFY_COLS 3
+
+ PG_FUNCTION_INFO_V1(dblink_get_notify);
+ Datum
+ dblink_get_notify(PG_FUNCTION_ARGS)
+ {
+ PGconn *conn = NULL;
+ remoteConn *rconn = NULL;
+ PGnotify *notify;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo-resultinfo;
+ TupleDesc tupdesc;
+ Tuplestorestate *tupstore;
+ MemoryContext per_query_ctx;
+ MemoryContext oldcontext;
+
+ DBLINK_INIT;
+ if (PG_NARGS() == 1)
+ DBLINK_GET_NAMED_CONN;
+ else
+ conn = pconn-conn;
+
+ /* create the tuplestore */
+ per_query_ctx = rsinfo-econtext-ecxt_per_query_memory;
+ oldcontext = MemoryContextSwitchTo(per_query_ctx);
+
+ tupdesc = CreateTemplateTupleDesc(DBLINK_NOTIFY_COLS, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, notify_name,
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, be_pid,
+ INT4OID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, extra,
+ TEXTOID, -1, 0);
+
+ tupstore = tuplestore_begin_heap(true, false, work_mem);
+ rsinfo-returnMode = SFRM_Materialize;
+ rsinfo-setResult = tupstore;
+ rsinfo-setDesc = tupdesc;
+
+ MemoryContextSwitchTo(oldcontext);
+
+ PQconsumeInput(conn);
+ while ((notify = PQnotifies(conn)) != NULL)
+ {
+ Datum values[DBLINK_NOTIFY_COLS];
+ bool nulls[DBLINK_NOTIFY_COLS];
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, 0, sizeof(nulls));
+
+ if (notify-relname != NULL)
+ values[0] = CStringGetTextDatum(notify-relname);
+ else
+ nulls[0] = true;
+
+ values[1] = Int32GetDatum(notify-be_pid);
+
+ if (notify-extra != NULL)
+ values[2] = CStringGetTextDatum(notify-extra);
+ else
+ nulls[2] = true;
+
+ /* switch to appropriate context while storing the tuple */
+ MemoryContextSwitchTo(per_query_ctx);
+ tuplestore_putvalues(tupstore, tupdesc, values, nulls);
+ MemoryContextSwitchTo(oldcontext);
+
+ PQfreemem(notify);
+ PQconsumeInput(conn);
+ }
+
+ /* clean up and return the tuplestore */
+ tuplestore_donestoring(tupstore);
+
+ return (Datum) 0;
+ }
+
/*
* internal functions
*/
Index: contrib/dblink/dblink.h
===
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.h,v
retrieving revision 1.22
diff -c -r1.22 dblink.h
*** contrib/dblink/dblink.h 9 Jun 2009 17:41:02 - 1.22
--- contrib/dblink/dblink.h 4 Aug 2009 13:41:26 -
***
*** 57,61
--- 57,62
extern Datum dblink_build_sql_delete(PG_FUNCTION_ARGS);
extern Datum dblink_build_sql_update(PG_FUNCTION_ARGS);
extern Datum dblink_current_query(PG_FUNCTION_ARGS);
+ extern Datum dblink_get_notify(PG_FUNCTION_ARGS);
#endif /* DBLINK_H */
Index: contrib/dblink/dblink.sql.in
Re: [HACKERS] the case for machine-readable error fields
Sam Mason s...@samason.me.uk wrote: Not sure if overloading SQLSTATE is the right way of doing this is it? It already has things like 23514 for a check violation and any other client code relying in this would break if it started getting different things back. If that's the standard SQLSTATE, I agree -- it suggests a need for some user-controllable field which could be set to a value to indicate a particular problem. Does the standard have anything like that, or would that be an extension? p.s. I think you were agreeing with everything else I was saying, even if I didn't explain myself well enough for you to understand me! It's good to see convergence, then. Sorry I misunderstood. -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] GRANT ON ALL IN schema
On Mon, Jul 20, 2009 at 2:12 AM, Nikhil Sontakkenikhil.sonta...@enterprisedb.com wrote: The review is complete from my side. There is this question about consistency between this patch and the Defaultacls patch. But am ok with this patch on its own. So ready for committer from my side. My understanding is that this patch will need to be reworked as well based on Tom's comments on DefaultACLs. Does that sound right? Should we expect a new version this week, or defer this until the September CommitFest? ...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] Prefix support for synonym dictionary
On Sun, Aug 2, 2009 at 3:05 PM, Jeff Davispg...@j-davis.com wrote: The patch looks good. Comments: 1. The docs should be clarified a little. For instance, it should have a link back to the definition of a prefix search (12.3.2). I included my doc suggestions as an attachment. 2. dsynonym_init() uses findwrd() in a slightly confusing (and perhaps fragile) way. After calling findwrd(), the end pointer is pointing at either the end of the string, or the *; depending on whether the string ends in * and whether flags is NULL. I only mention this because I had to take a more careful look to see what was happening. Perhaps add a comment to make it more clear? 3. The patch looks for the special byte '*'. I think that's fine, because we depend on the files being in UTF-8 encoding, where it's the same byte. However, I thought it was worth mentioning in case we want to support other encodings for text search files later. Oleg, Are you planning to update this patch this week? If not I will set it to Returned with Feedback. 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] GRANT ON ALL IN schema
Robert Haas robertmh...@gmail.com writes: My understanding is that this patch will need to be reworked as well based on Tom's comments on DefaultACLs. Does that sound right? Should we expect a new version this week, or defer this until the September CommitFest? I was planning to go review that patch too, even though it's presumably not committable yet. I'm not sure whether there is consensus on not using GRANT ON VIEW (ie, having these patches treat tables and views alike). I was waiting to see if Stephen would put forward a convincing counterargument ... 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] CommitFest 2009-07: Closing Soon
Folks, We now have just 10 days left in this CommitFest and I think it is time to start thinking about closing up shop. My tentative plan, absent strong objections, is to wait until Friday night and then move most or all of the patches that are still Waiting on Author to Returned with Feedback. I say most or all because there are a few patches that have not been thoroughly reviewed yet, and if those get reviewed between now and then, I'm not going to hair-trigger move them over to Returned with Feedback without giving the author a chance to update. But anything that has been going back-and-forth for a while, I think we should close out, so that we can focus all of our effort for the last week on patches that haven't yet had a full review, or that just need to be committed. For the sake of precision, let's define Friday night as at or after Sat Aug 8 00:00:00 UTC 2009. Between now and then, I'm going to be working on identifying which patches are not marked as Waiting on Author but should be, or which are marked as Waiting on Author but shouldn't be. ...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] GRANT ON ALL IN schema
Tom Lane wrote: I'm not sure whether there is consensus on not using GRANT ON VIEW (ie, having these patches treat tables and views alike). I was waiting to see if Stephen would put forward a convincing counterargument ... Conceptually it is right, I think. A view is a virtual table, so the counter-argument would need to be pretty good ISTM. 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] GRANT ON ALL IN schema
On Wed, Aug 5, 2009 at 12:40 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: My understanding is that this patch will need to be reworked as well based on Tom's comments on DefaultACLs. Does that sound right? Should we expect a new version this week, or defer this until the September CommitFest? I was planning to go review that patch too, even though it's presumably not committable yet. OK, that's good information, thanks. I'm not sure whether there is consensus on not using GRANT ON VIEW (ie, having these patches treat tables and views alike). I was waiting to see if Stephen would put forward a convincing counterargument ... The argument is better for defaults that it is for grant on all, I think, though we also don't want the two to be asymmetric. Defaults need to be really simple to have any value, I think, and avoid violating the POLA. But bulk-grant could be based on object type, object name (with wildcard or regexp pattern), schema membership, or maybe other things, and I think that would be quite useful if we can figure out how to make it clean and elegant. ...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] GRANT ON ALL IN schema
Robert Haas robertmh...@gmail.com writes: ... bulk-grant could be based on object type, object name (with wildcard or regexp pattern), schema membership, or maybe other things, and I think that would be quite useful if we can figure out how to make it clean and elegant. Yeah. In the end you can always write a plpgsql function that filters on anything at all. The trick is to pick some useful subset of functionality that can be exposed in a less messy way. Or maybe we are going at this the wrong way? Would it be better to try harder to support the write-a-plpgsql-function approach? I don't think the documentation even mentions that approach, let alone provides any concrete examples. It might be interesting to document it and see if there are any simple things we could do to file off rough edges in doing grants that way, rather than implementing what must ultimately be a limited solution directly in GRANT. 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] the case for machine-readable error fields
On Wed, Aug 05, 2009 at 11:32:06AM -0500, Kevin Grittner wrote: Sam Mason s...@samason.me.uk wrote: Not sure if overloading SQLSTATE is the right way of doing this is it? It already has things like 23514 for a check violation and any other client code relying in this would break if it started getting different things back. If that's the standard SQLSTATE, I agree -- it suggests a need for some user-controllable field which could be set to a value to indicate a particular problem. Does the standard have anything like that, or would that be an extension? Not sure how standard it is, but the docs[1] would suggest that it's trying to following something. Microsoft's MSDN docs on ODBC[2] show a reasonable similarity, the first Oracle doc I found[3] where similar as well. It just looks like a fixed set of numbers for a fixed set of conditions, can't find any canonical definition about what it's really for though. -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/errcodes-appendix.html [2] http://msdn.microsoft.com/en-us/library/ms714687(VS.85).aspx [3] http://download.oracle.com/docs/cd/B19306_01/appdev.102/a58231/appd.htm I think I prefer PG's urls! -- 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] GRANT ON ALL IN schema
Petr Jelinek pjmo...@pjmodos.net writes: One more typo fix in docs I took a quick look at this version of the patch. Other than the already-mentioned question of whether we really want to create a distinction between tables and views in GRANT, there's not that much there to criticize. I do have a feeling that the implementation is a bit too narrowly focused on the stuff IN SCHEMA foo case; if we were ever to add other filtering options it seems like we'd have to rip all this code out and start over. But I don't have any immediate ideas on what it should look like instead. You mentioned that you weren't having any luck making SCHEMA optional in the syntax. I'm inclined to think it should be required rather than leave it out entirely. Leaving it out seems like it risks foreclosing future expansion --- are we sure there will never be another selection option that we'd want to start with IN? Putting the search functions (getNamespacesObjectsOids and getRelationsInNamespace) into aclchk.c doesn't seem quite right. I'd have been inclined to put them in namespace.c instead, I think. On the other hand objectNamesToOids hasn't been abstracted at all, so maybe this is fine as-is. Other than that I don't have much to say. I wonder though if this approach isn't sort of a dead-end, and we should instead look at making it easier to build sql or plpgsql functions for doing bulk grants with arbitrary selection conditions. 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] the case for machine-readable error fields
Sam Mason s...@samason.me.uk wrote: It just looks like a fixed set of numbers for a fixed set of conditions, can't find any canonical definition about what it's really for though. Sorry, I'm familiar with the SQLSTATE's role in the spec, I just wasn't sure how specific they got in their table of standard values regarding particular constraints. From the spec: The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to digits and simple Latin upper case letters. Table 32, *SQLSTATE class and subclass values*, specifies the class value for each condition and the subclass value or values for each class value. and: If a subclass value is not specified for a condition, then either subclass '000' or an implementation-defined subclass is returned. From the table, the 23xxx series is for integrity constraint violations, but they appear not to have gotten too specific about breaking that down; thereby leaving it as an implementation choice: integrity constraint violation 23 (no subclass) 000 restrict violation 001 Anyway, it was a bad suggestion that we provide a way to specify a SQLSTATE to use for a constraint failure. I do think that some field which could be used for that purpose would be good. Preferably something which could be specified in the declaration of the constraint. -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] CommitFest 2009-07: Closing Soon
Robert Haas robertmh...@gmail.com writes: Between now and then, I'm going to be working on identifying which patches are not marked as Waiting on Author but should be, or which are marked as Waiting on Author but shouldn't be. Er, shouldn't you first work on finishing your own patches? The EXPLAIN output patch is still Waiting on Author ... I'm going to go ahead and commit the dict_xsyn patch. There isn't anything obviously wrong with it, and although I'd have preferred to get Teodor's input, he's evidently too busy with other work to comment on it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] slow commits with heavy temp table usage in 8.4.0
Hi, I've noticed that on 8.4.0, commits can take a long time when a temp table is repeatedly filled and truncated within a loop. A very contrived example is begin; create or replace function commit_test_with_truncations() returns void language 'plpgsql' as $_func_$ declare i integer; begin create temp table t1 (x integer) on commit drop ; for i in 1 .. 22000 loop insert into t1 select s from generate_series(1,1000) s ; truncate t1 ; end loop; end; $_func_$; select commit_test_with_truncations() ; commit ; On may laptop (Core2 Duo with 3.5GB and a disk dedicated to PG), the function call takes about 124 seconds, and the commit takes about 43 seconds. The function execution generates a lot of I/O activity, but the commit is entirely CPU bound. By contrast, the same test on an 8.2.13 system (2 older Xeons and 8GB) had times of 495 and 19 seconds. In this case, both the function execution and the commit were entirely CPU bound. The overall process in 8.4 is much faster than 8.2.13, but the commit time is somewhat surprising to me. Is that to be expected? 8.4 version(): PostgreSQL 8.4.0 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-27), 64-bit 8.2.13 version(): PostgreSQL 8.2.13 on x86_64-suse-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 20050117 (prerelease) (SUSE Linux) -- todd -- 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] improvements for dict_xsyn extended synonym dictionary - RRR
I wrote: kar...@sao.ru (Sergey V. Karpov) writes: Andres Freund and...@anarazel.de writes: Looks nice. The only small gripe I have is that the patch adds trailing whitespaces at a lot of places... My fault. Please check the patch version attached - I've tried to fix all those. I did some minor cleanup on this patch: I've committed this version. 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] the case for machine-readable error fields
On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote: From the spec: The character string value returned in an SQLSTATE parameter comprises a 2-character class value followed by a 3-character subclass value, each with an implementation-defined character set that has a one-octet character encoding form and is restricted to digits and simple Latin upper case letters. Table 32, *SQLSTATE class and subclass values*, specifies the class value for each condition and the subclass value or values for each class value. and: If a subclass value is not specified for a condition, then either subclass '000' or an implementation-defined subclass is returned. Thanks, I'd not found that specified--it matches up to what I'd found PG and other databases doing. Still doesn't really describe the engineering rational behind it though. From the table, the 23xxx series is for integrity constraint violations, but they appear not to have gotten too specific about breaking that down; thereby leaving it as an implementation choice: integrity constraint violation 23 (no subclass) 000 restrict violation 001 Yes; but somewhere along the line we've got exactly the same integrity constraint violation sqlcodes as DB2 (and Derby, but that's not very surprising as they're both IBM). Can't find anybody else trying very hard though. Anyway, it was a bad suggestion that we provide a way to specify a SQLSTATE to use for a constraint failure. I do think that some field which could be used for that purpose would be good. Preferably something which could be specified in the declaration of the constraint. I still stand by my assertion that the constraint name is sufficient for the original purpose. -- Sam http://samason.me.uk/ -- 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] the case for machine-readable error fields
Sam Mason s...@samason.me.uk writes: On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote: Anyway, it was a bad suggestion that we provide a way to specify a SQLSTATE to use for a constraint failure. I do think that some field which could be used for that purpose would be good. Preferably something which could be specified in the declaration of the constraint. I still stand by my assertion that the constraint name is sufficient for the original purpose. Yeah. Changing the SQLSTATE for a given error seems much more likely to break things than to be helpful. It does make sense to be able to extract the constraint name for a constraint-related error without having to make unsafe assumptions about the spelling of the human-readable error message, though. Peter pointed out upthread that the SQL standard already calls out some things that should be available in this way --- has anyone studied that yet? 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] the case for machine-readable error fields
Sam Mason s...@samason.me.uk writes: On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote: From the table, the 23xxx series is for integrity constraint violations, but they appear not to have gotten too specific about breaking that down; thereby leaving it as an implementation choice: Yes; but somewhere along the line we've got exactly the same integrity constraint violation sqlcodes as DB2 (and Derby, but that's not very surprising as they're both IBM). Can't find anybody else trying very hard though. BTW, that's because we deliberately borrowed as much as we could from DB2. See the notes near the top of errcodes.h. As you say, nobody else seems to care much, so that was the only precedent we could find. 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] GRANT ON ALL IN schema
Tom Lane wrote: I do have a feeling that the implementation is a bit too narrowly focused on the stuff IN SCHEMA foo case; if we were ever to add other filtering options it seems like we'd have to rip all this code out and start over. But I don't have any immediate ideas on what it should look like instead. It is, I was thinking about making that bool is_schema something more useful like int search_option with enum associated with it. But if I do that it would be better to have more then one filter implemented in initial commit - maybe I could add that OWNED BY I was talking about, or do you have better suggestions ? You mentioned that you weren't having any luck making SCHEMA optional in the syntax. I'm inclined to think it should be required rather than leave it out entirely. Leaving it out seems like it risks foreclosing future expansion --- are we sure there will never be another selection option that we'd want to start with IN? Ok I'll make it mandatory. Putting the search functions (getNamespacesObjectsOids and getRelationsInNamespace) into aclchk.c doesn't seem quite right. I'd have been inclined to put them in namespace.c instead, I think. On the other hand objectNamesToOids hasn't been abstracted at all, so maybe this is fine as-is. I wanted to be consistent with existing code there (the objectNamesToOids you mentioned) and I also didn't want to export those functions needlessly. Other than that I don't have much to say. I wonder though if this approach isn't sort of a dead-end, and we should instead look at making it easier to build sql or plpgsql functions for doing bulk grants with arbitrary selection conditions. The whole reason for me to implement this thing is that I see something like How can I grant rights to all existing objects in database? question asked on irc channel like once a week. Most of the time those people only want to use that particular feature once after importing/creating schema so making function you'll only use once is not the optimal way to do it. And more importantly they expect this to be possible using standard SQL. -- Regards Petr Jelinek (PJMODOS) -- 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] the case for machine-readable error fields
Sam Mason s...@samason.me.uk wrote: Still doesn't really describe the engineering rational behind it though. Well, the distinctions in many cases would be mostly of interest to a DBA managing a large shop who was trying to characterize the reasons for query failure. Some codes, however, are particularly valuable. At the low end, classes '00' (information), '01' (warning), and '02' (no rows affected) can be used for useful, if mundane, purposes. A really interesting one is '40001' -- which indicates that your transaction was rolled back because of conflicts with concurrent transactions. Our framework, for example, resubmits transactions which fail with this SQL state; the user, and indeed the application code, never have any indication that the transaction was rolled back and restarted -- it appears just the same as a delay caused by blocking. (Our logs, of course, track these, so we can look to reduce conflicts.) I still stand by my assertion that the constraint name is sufficient for the original purpose. After thinking about that some more, I think I'm sold. -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] the case for machine-readable error fields
2009/8/5 Tom Lane t...@sss.pgh.pa.us: Sam Mason s...@samason.me.uk writes: On Wed, Aug 05, 2009 at 12:41:30PM -0500, Kevin Grittner wrote: Anyway, it was a bad suggestion that we provide a way to specify a SQLSTATE to use for a constraint failure. I do think that some field which could be used for that purpose would be good. Preferably something which could be specified in the declaration of the constraint. I still stand by my assertion that the constraint name is sufficient for the original purpose. Yeah. Changing the SQLSTATE for a given error seems much more likely to break things than to be helpful. It does make sense to be able to extract the constraint name for a constraint-related error without having to make unsafe assumptions about the spelling of the human-readable error message, though. Peter pointed out upthread that the SQL standard already calls out some things that should be available in this way --- has anyone studied that yet? yes - it's part of GET DIAGNOSTICS statement http://savage.net.au/SQL/sql-2003-2.bnf.html#condition%20information%20item%20name regards Pavel Stehule 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 -- 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] GRANT ON ALL IN schema
Tom, I took a quick look at this version of the patch. Other than the already-mentioned question of whether we really want to create a distinction between tables and views in GRANT, there's not that much there to criticize. It's pretty common to have a database where there are some users who have permissions on views but not on the base tables. So that would be an argument for separating the two. On the other hand, it's not a very persuasive argument; in general, such databases have complex enough security rules that GRANT ALL ON is too simple for them. So, overall, I'd tend to say that we're better off including views and tables in the same GRANT ALL. The purpose of this is to be a simple approach for simple cases, no? I do have a feeling that the implementation is a bit too narrowly focused on the stuff IN SCHEMA foo case; if we were ever to add other filtering options it seems like we'd have to rip all this code out and start over. But I don't have any immediate ideas on what it should look like instead. Well, schemas do make a good grouping set for objects of different security contexts; they are certainly more reliable than name fragments (as would be supported by a regex scheme). The main defect of schemas is the well-documented issues with managing search_path. Other than that I don't have much to say. I wonder though if this approach isn't sort of a dead-end, and we should instead look at making it easier to build sql or plpgsql functions for doing bulk grants with arbitrary selection conditions. Right now we have a situation where most web developers aren't using ROLEs *at all* because they are too complex for them to bother with. I literally couldn't count the number of production applications I've run across which connect to Postgres as the superuser. We need a dead-simple approach for the entry-level DB users, and I haven't heard one which is simpler or more approachable than the GRANT ALL + SET DEFAULT approach. With that approach, setting up a 3-role, table only database to have the right security is only 6 statements. I agree that we should also provide examples of how to do this by script in the docs, and maybe even some tools on pgFoundry. But those cover the sophisticated users. For the simple users, we need a dead-simple tool. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] log shipping and nextval sequences
Hi, In warm standby system when we have a filled log segment forwarded to archiving, there is an inconsistency on standby next value sequences obtained by a call to nextval() function. e.g.: * Primary server - Create sequence seq_a; - Select nextval ( 'seq_a'); # value 1; - Log shipping; * Standby server - Failover; - Select nextval ( 'seq_a') on standby # value = currval + 31 (written ahead) AFAIK this occurs because some fetches (log_cnt) are made in advance and they are recorded in the log and shipping together. Does it necessary for some kind of overhead or something like that? Does it make sense to create a GUC to control the log_cnt amount rather than SEQ_LOG_VALS approach? version: 8.3.7 regards, -Leo -- Leonardo Cezar http://postgreslogia.wordpress.com http://www.dextra.com.br/postgres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest 2009-07: Closing Soon
On Wed, Aug 5, 2009 at 1:43 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Between now and then, I'm going to be working on identifying which patches are not marked as Waiting on Author but should be, or which are marked as Waiting on Author but shouldn't be. Er, shouldn't you first work on finishing your own patches? The EXPLAIN output patch is still Waiting on Author ... Well, I'm hoping those are not mutually exclusive. Figuring out what is waiting on author shouldn't take more than about a half hour each of the next two nights, and the EXPLAIN output patch doesn't need more than another hour or two of work to address the feedback given thus far. However, hypothetically speaking, if I *don't* manage to finish that patch up in the next two nights, I don't see why it should be treated any differently than any other patch that isn't ready to go. Unless you think that patch is so important that it's worth holding up the entire CommitFest for? But I'm assuming that isn't the case. It's important to me not to create the impression that I am giving special treatment to my own patches. I am trying to handle them in the same way that I would handle any other patches (well, except that I nag myself internally, rather than sending myself an email and copying -hackers). Of course, being me, it's hard for me to be absolutely certain that I'm actually doing that, but for the record, that's what I'm attempting to do. One problem that I've run into during this process is that I submitted a LOT of patches - I believe 11 of 71 patches in this CommitFest are mine, and I'm also trying to do high-level management of the entire CommitFest. That's made it really hard for me to do anything like the amount of reviewing I did for the last CommitFest. It doesn't seem quite fair that I'm submitting more patches and reviewing fewer of other people's, but I don't know what to do about it. Not working on the patches that I've submitted slows down the CommitFest just as much as working on them does, only for different reasons. Fortunately, we had enough reviewers anyway: I think that nearly every patch that wasn't already claimed by a committer got a review pretty quickly. Still, I'd welcome any suggestions on how to balance this better. At some point, maybe after this CommitFest is done, it might be good to have a postmortem on what people thought worked well/poorly and suggestions for improvement next time around. I'm going to go ahead and commit the dict_xsyn patch. There isn't anything obviously wrong with it, and although I'd have preferred to get Teodor's input, he's evidently too busy with other work to comment on it. Works for me. ...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] GRANT ON ALL IN schema
On Wed, Aug 5, 2009 at 2:57 PM, Josh Berkusj...@agliodbs.com wrote: Right now we have a situation where most web developers aren't using ROLEs *at all* because they are too complex for them to bother with. I literally couldn't count the number of production applications I've run across which connect to Postgres as the superuser. We need a I have one database that is set up with a reporting user (read only on everything). It requires constant maintenance. Every time an object is added or deleted (or dropped and recreated, like a view, which I do ALL THE TIME to work around the inability to add/remove columns) the permissions get shot to hell. I finally crontabbed a script that fixes it every 20 minutes. I had another database where I tried to do some real permission separation and it was just a huge pain in the ass. Grant on all isn't gonna fix these problems completely, but it's a start. The DefaultACL stuff is another important step in the right direction. Documenting how to use PL/pgsql to do this stuff is an EXCELLENT idea, but it's not a complete substitute for providing some usable SQL-level facilities. ...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] the case for machine-readable error fields
Tom Lane wrote: Peter pointed out upthread that the SQL standard already calls out some things that should be available in this way --- has anyone studied that yet? Yeah, I gave it a look. It looks useful as a guide, though obviously not directly implementable because it relies on GET DIAGNOSTICS to have somewhere to store the diagnostics information into (a host variable, etc). They do define that there is a TABLE_NAME, etc. Not much else to report at the moment. -- 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] log shipping and nextval sequences
Leonardo Cezar lhce...@gmail.com writes: In warm standby system when we have a filled log segment forwarded to archiving, there is an inconsistency on standby next value sequences obtained by a call to nextval() function. e.g.: * Primary server - Create sequence seq_a; - Select nextval ( 'seq_a'); # value 1; - Log shipping; * Standby server - Failover; - Select nextval ( 'seq_a') on standby # value = currval + 31 (written ahead) AFAIK this occurs because some fetches (log_cnt) are made in advance and they are recorded in the log and shipping together. Does it necessary for some kind of overhead or something like that? Does it make sense to create a GUC to control the log_cnt amount rather than SEQ_LOG_VALS approach? No. If your application expects the series not to have gaps, your application is broken independently of warm standby. The same sort of advance would happen if the master crashed and restarted. 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] Prefix support for synonym dictionary
On Wed, 2009-08-05 at 12:34 -0400, Robert Haas wrote: Oleg, Are you planning to update this patch this week? If not I will set it to Returned with Feedback. My only comments were related to docs and comments, and I supplied a patch as a suggested fix for the docs. Also, the patch is very small. I'd hate to hold it up over such a minor issue, and it seems like a useful feature. If Oleg is unavailable, would you mind just having a second review of the patch to see if they agree with my suggestions, and then mark ready for committer review? 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] the case for machine-readable error fields
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: Peter pointed out upthread that the SQL standard already calls out some things that should be available in this way --- has anyone studied that yet? Yeah, I gave it a look. It looks useful as a guide, though obviously not directly implementable because it relies on GET DIAGNOSTICS to have somewhere to store the diagnostics information into (a host variable, etc). They do define that there is a TABLE_NAME, etc. Not much else to report at the moment. I'm not proposing that we implement GET DIAGNOSTICS as a statement. I was just thinking that the list of values it's supposed to make available might do as a guide to what extra error fields we need to provide where. 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] the case for machine-readable error fields
2009/8/5 Tom Lane t...@sss.pgh.pa.us: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: Peter pointed out upthread that the SQL standard already calls out some things that should be available in this way --- has anyone studied that yet? Yeah, I gave it a look. It looks useful as a guide, though obviously not directly implementable because it relies on GET DIAGNOSTICS to have somewhere to store the diagnostics information into (a host variable, etc). They do define that there is a TABLE_NAME, etc. Not much else to report at the moment. I'm not proposing that we implement GET DIAGNOSTICS as a statement. I was just thinking that the list of values it's supposed to make available might do as a guide to what extra error fields we need to provide where. +1 regards Pavel Stehule 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 -- 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] GRANT ON ALL IN schema
Robert Haas robertmh...@gmail.com writes: I have one database that is set up with a reporting user (read only on everything). It requires constant maintenance. Every time an object is added or deleted (or dropped and recreated, like a view, which I do ALL THE TIME to work around the inability to add/remove columns) the permissions get shot to hell. I finally crontabbed a script that fixes it every 20 minutes. I had another database where I tried to do some real permission separation and it was just a huge pain in the ass. Grant on all isn't gonna fix these problems completely, but it's a start. The DefaultACL stuff is another important step in the right direction. Seems like default ACLs, not grant-on-all, is what you want for that. The idea of better support for plpgsql-driven granting isn't going to compete with default ACLs, but it does compete with grant-on-all. So that's why I'm thinking we ought to take a harder look at that before adding nonstandard extensions to GRANT. Josh's position that this should be standard SQL is nonsense, or at least he ought to be making that argument to the standards committee not us. It *isn't* standard, and therefore it's up to us to decide how we want to expose the facility. What's more, syntax extensions to GRANT are a pretty risky way to do it: what if the SQL committee sees the light and SQL:201x includes a GRANT extension, only it conflicts with ours? If we want something built-in, maybe providing some prefab plpgsql functions is the way to go. But we'd have to arrive at a consensus on what best practice of that form looks like. 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] GRANT ON ALL IN schema
On Wed, Aug 5, 2009 at 3:40 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I have one database that is set up with a reporting user (read only on everything). It requires constant maintenance. Every time an object is added or deleted (or dropped and recreated, like a view, which I do ALL THE TIME to work around the inability to add/remove columns) the permissions get shot to hell. I finally crontabbed a script that fixes it every 20 minutes. I had another database where I tried to do some real permission separation and it was just a huge pain in the ass. Grant on all isn't gonna fix these problems completely, but it's a start. The DefaultACL stuff is another important step in the right direction. Seems like default ACLs, not grant-on-all, is what you want for that. Well, that helps with the maintenance, but you also have to set it up initially. There were already 100+ objects in the schema at the time the reporting user was created. ...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] slow commits with heavy temp table usage in 8.4.0
Todd A. Cook tc...@blackducksoftware.com writes: I've noticed that on 8.4.0, commits can take a long time when a temp table is repeatedly filled and truncated within a loop. A very contrived example is Hmm. I tweaked the function to allow varying the number of truncates: regression=# begin; BEGIN Time: 1.037 ms regression=# select commit_test_with_truncations(1) ; commit_test_with_truncations -- (1 row) Time: 9466.060 ms regression=# commit; COMMIT Time: 1095.946 ms regression=# begin; BEGIN Time: 1.002 ms regression=# select commit_test_with_truncations(3) ; commit_test_with_truncations -- (1 row) Time: 93492.874 ms regression=# commit; COMMIT Time: 3184.248 ms The commit time doesn't seem tremendously out of line, but it looks like there's something O(N^2)-ish in the function execution. Do you see a similar pattern? With so many temp files there could well be some blame on the kernel side. (This is a Fedora 10 box.) 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] GRANT ON ALL IN schema
2009/8/5 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: I have one database that is set up with a reporting user (read only on everything). It requires constant maintenance. Every time an object is added or deleted (or dropped and recreated, like a view, which I do ALL THE TIME to work around the inability to add/remove columns) the permissions get shot to hell. I finally crontabbed a script that fixes it every 20 minutes. I had another database where I tried to do some real permission separation and it was just a huge pain in the ass. Grant on all isn't gonna fix these problems completely, but it's a start. The DefaultACL stuff is another important step in the right direction. Seems like default ACLs, not grant-on-all, is what you want for that. The idea of better support for plpgsql-driven granting isn't going to compete with default ACLs, but it does compete with grant-on-all. So that's why I'm thinking we ought to take a harder look at that before adding nonstandard extensions to GRANT. Josh's position that this should be standard SQL is nonsense, or at least he ought to be making that argument to the standards committee not us. It *isn't* standard, and therefore it's up to us to decide how we want to expose the facility. What's more, syntax extensions to GRANT are a pretty risky way to do it: what if the SQL committee sees the light and SQL:201x includes a GRANT extension, only it conflicts with ours? If we want something built-in, maybe providing some prefab plpgsql functions is the way to go. But we'd have to arrive at a consensus on what best practice of that form looks like. There are some people, that dislike stored procedures :(. Probably lot of MySQL users. For them are procedures devil still. I would to like some base maintenance library in plpgsql. But it's need plpgsql installed in core by default. Pavel 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 -- 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] slow commits with heavy temp table usage in 8.4.0
Tom Lane t...@sss.pgh.pa.us wrote: Todd A. Cook tc...@blackducksoftware.com writes: I've noticed that on 8.4.0, commits can take a long time when a temp table is repeatedly filled and truncated within a loop. The commit time doesn't seem tremendously out of line, but it looks like there's something O(N^2)-ish in the function execution. Do you see a similar pattern? With so many temp files there could well be some blame on the kernel side. (This is a Fedora 10 box.) This sounds very similar to my experience here: http://archives.postgresql.org/pgsql-hackers/2008-11/msg01472.php Depending on what sort of RAID controller caching is present, a BBU cache might be containing the problem up to some threshold. Perhaps it's not so much O(N^2) as O(N)-someconstant, with a min of zero? -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] Alpha Releases: Docs?
What I would like to avoid is a situation where we're basically ready to go with beta and Bruce says, Hold on, everybody, it's going to take another two weeks while I plow through 600 commit messages. I have a theory that that work can be spread out and much of it done in advance and not necessarily by Bruce. However, that theory has yet to be tested, and the committers (principally Tom and Bruce) have to be open to it for it to have any chance of success. I just talked to Bruce on IM, and he said that he would NOT use any release notes we produce no matter how good they are. Therefore, us trying to help by producing alpha release notes is a waste of time; I won't bother. Instead, I'll just write up a brief, informal user-friendly list of features and changes, and we can release that combined with the cvslog. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GRANT ON ALL IN schema
Josh's position that this should be standard SQL is nonsense, or at least he ought to be making that argument to the standards committee not us. Huh? When did I say that? If we want something built-in, maybe providing some prefab plpgsql functions is the way to go. But we'd have to arrive at a consensus on what best practice of that form looks like. *Built-in* functions are just as good as extra syntax, as far as I'm concerned. Functions which require installing plpgsql, reading the docs, creating a function, pasting it in, and saving it are NOT as good; they are unlikely to ever be used, except by the people who didn't really need them in the first place. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] slow commits with heavy temp table usage in 8.4.0
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: Todd A. Cook tc...@blackducksoftware.com writes: I've noticed that on 8.4.0, commits can take a long time when a temp table is repeatedly filled and truncated within a loop. The commit time doesn't seem tremendously out of line, but it looks like there's something O(N^2)-ish in the function execution. Do you see a similar pattern? With so many temp files there could well be some blame on the kernel side. (This is a Fedora 10 box.) This sounds very similar to my experience here: http://archives.postgresql.org/pgsql-hackers/2008-11/msg01472.php I did some more poking with oprofile, and got this: samples %image name symbol name 559375 39.9848 postgres index_getnext 167626 11.9821 postgres TransactionIdIsCurrentTransactionId 1074217.6786 postgres HeapTupleSatisfiesNow 65689 4.6955 postgres HeapTupleHeaderGetCmin 47220 3.3753 postgres HeapTupleHeaderGetCmax 46799 3.3452 postgres hash_search_with_hash_value 29331 2.0966 postgres heap_hot_search_buffer 23737 1.6967 postgres CatalogCacheFlushRelation 20562 1.4698 postgres LWLockAcquire 19838 1.4180 postgres heap_page_prune_opt 19044 1.3613 postgres _bt_checkkeys 17400 1.2438 postgres LWLockRelease 12993 0.9288 postgres PinBuffer So what I'm seeing is entirely explained by the buildup of dead versions of the temp table's pg_class row --- the index_getnext time is spent scanning over dead HOT-chain members. It might be possible to avoid that by special-casing temp tables in TRUNCATE to recycle the existing file instead of assigning a new one. However, there is no reason to think that 8.3 would be any better than 8.4 on that score. Also, I'm not seeing the very long CPU-bound commit phase that Todd is seeing. So I think there's something happening on his box that's different from what I'm measuring. I'm actually testing CVS HEAD, not 8.4.0, but I don't recall that we've done anything in the past month that would be likely to affect this ... 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] GRANT ON ALL IN schema
Josh Berkus j...@agliodbs.com writes: Josh's position that this should be standard SQL is nonsense, or at least he ought to be making that argument to the standards committee not us. Huh? When did I say that? Sorry, I think I got one of your messages confused with one of Robert's. Anyway, *Built-in* functions are just as good as extra syntax, as far as I'm concerned. Functions which require installing plpgsql, reading the docs, creating a function, pasting it in, and saving it are NOT as good; they are unlikely to ever be used, except by the people who didn't really need them in the first place. Agreed, whatever we want to provide here should be available in a vanilla installation. This might argue for providing a C-code implementation instead of plpgsql, since I'm not sure we are yet ready to have plpgsql force-installed. But we can certainly design and prototype in plpgsql. 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] Alpha Releases: Docs?
Josh Berkus wrote: What I would like to avoid is a situation where we're basically ready to go with beta and Bruce says, Hold on, everybody, it's going to take another two weeks while I plow through 600 commit messages. I have a theory that that work can be spread out and much of it done in advance and not necessarily by Bruce. However, that theory has yet to be tested, and the committers (principally Tom and Bruce) have to be open to it for it to have any chance of success. I just talked to Bruce on IM, and he said that he would NOT use any release notes we produce no matter how good they are. Therefore, us trying to help by producing alpha release notes is a waste of time; I won't bother. Instead, I'll just write up a brief, informal user-friendly list of features and changes, and we can release that combined with the cvslog. Sorry I didn't chime in yesterday; I am still 3.7k community emails backlogged. As far as the release notes, I think we would have to have proof that the alpha-generated release notes are as good or close to the quality of the release notes using the current process. If they are, we can use them for 8.6, or even for 8.5 if the quality is similar, but we can't know that without creating identical release notes for 8.5 and comparing them, to make sure the alpha process has not missed any items, etc. What we don't want to do is switch to a new process for creating the release notes, and only later realize we missed stuff or there was some subtle change that caused inaccuracies. Remember, we are database guys. :-) This is going to follow the same process as the patch application/commit fest changes; I am glad to give up the burden of creating the release notes (and I think Tom is too) but we have to have something as good or better before making the switch. This happened for the patch application process, and it might happen with the release notes too, but we have to do duplicate work while we are testing out the new system. (I frankly think the economies of scale (http://en.wikipedia.org/wiki/Economy_of_scale) for the release notes are going to make creating them during alpha much harder, but I am willing to be proven wrong. I am not willing to expend effort to create alpha release notes because it is too hard to fit into my workload.) As far as the alpha releases, I am still worried about the use of the word alpha. I am worried someone is going to look at 8.4alpha1 and think that represents most of the features that will be in 8.5final, and will think the Postgres project is losing momentum. I would much rather they be called Commit Feast 1 (CF1), or something like that. -- Bruce Momjian br...@momjian.ushttp://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
[HACKERS] Alpha Releases (was the Alpha Docs)
On Wed, 2009-08-05 at 17:11 -0400, Bruce Momjian wrote: Josh Berkus wrote: As far as the alpha releases, I am still worried about the use of the word alpha. I am worried someone is going to look at 8.4alpha1 and think that represents most of the features that will be in 8.5final, and will think the Postgres project is losing momentum. I would much rather they be called Commit Feast 1 (CF1), or something like that. An Alpha release should be feature complete. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Alpha Releases: Docs?
Bruce Momjian br...@momjian.us writes: As far as the alpha releases, I am still worried about the use of the word alpha. I am worried someone is going to look at 8.4alpha1 and think that represents most of the features that will be in 8.5final, and will think the Postgres project is losing momentum. I would much rather they be called Commit Feast 1 (CF1), or something like that. I think that's easily dealt with by a suitable notice at the top of the alpha release notes (or whatever substitutes for them). As was discussed at the PGCon meeting, we can't use cfN because that doesn't sort before betaN, which would confuse the heck out of various package management services. I'm not wedded to the term alpha, but we need something that is alphanumerically less than beta. 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] Alpha Releases: Docs?
As far as the alpha releases, I am still worried about the use of the word alpha. I am worried someone is going to look at 8.4alpha1 and think that represents most of the features that will be in 8.5final, and will think the Postgres project is losing momentum. I would much rather they be called Commit Feast 1 (CF1), or something like that. milestone ? regards Marcin Mańk -- 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] mixed, named notation support
--On Mittwoch, August 05, 2009 05:28:55 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: At least, we need to document that both notations behaves different in this case. +1 Here again a patch version with updated documentation. I will stop reviewing this patch now and mark this ready for committer, so we have some time left to incorporate additional feedback. -- Thanks Bernd named_and_mixed_notation_review4.patch.gz 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] Alpha Releases: Docs?
Bruce Momjian wrote: I would much rather they be called Commit Feast 1 (CF1), or something like that. ITYM Fest, although sometimes we make a meal of it ;-) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha Releases: Docs?
Tom Lane t...@sss.pgh.pa.us wrote: we need something that is alphanumerically less than beta. antebeta1? Then, each commit-fest, we up the ante -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] Alpha Releases: Docs?
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: we need something that is alphanumerically less than beta. antebeta1? Then, each commit-fest, we up the ante rotfl... Actually just ante1 would work better for that joke. 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] Alpha Releases: Docs?
On Wed, 2009-08-05 at 16:34 -0500, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: we need something that is alphanumerically less than beta. antebeta1? Then, each commit-fest, we up the ante 1stCF09 2ndCF09 3rdCF09 -Kevin -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] slow commits with heavy temp table usage in 8.4.0
Tom Lane wrote: So what I'm seeing is entirely explained by the buildup of dead versions of the temp table's pg_class row --- the index_getnext time is spent scanning over dead HOT-chain members. It might be possible to avoid that by special-casing temp tables in TRUNCATE to recycle the existing file instead of assigning a new one. However, there is no reason to think that 8.3 would be any better than 8.4 on that score. Also, I'm not seeing the very long CPU-bound commit phase that Todd is seeing. The commit looks CPU-bound when I let the residual I/O from the function execution die out before I issue the commit. I'm actually testing CVS HEAD, not 8.4.0, but I don't recall that we've done anything in the past month that would be likely to affect this ... regards, tom lane . Tom's theory may explain the different commit results I get when testing on two different databases: db truncations function commit --- --- -- test 1 29603.624 6054.889 test 1 34740.16714551.177 test 1 30608.26011144.503 test 1 32239.049 9846.676 test 3 227115.85050206.947 test 3 201859.69846083.222 test 3 231926.64246681.009 test 3 235665.97047113.137 production 1 32982.06917654.772 production 1 33297.52417396.792 production 1 35503.18518343.045 production 1 34251.75318284.725 production 3 200899.78675480.448 production 3 206793.20973316.405 production 3 260491.75972570.297 production 3 191363.16866659.129 The test DB is nearly empty with 251 entries in pg_class, whereas production has real data with 9981 entries in pg_class. -- todd -- 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] log shipping and nextval sequences
On Aug 5, 2009, at 3:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Leonardo Cezar lhce...@gmail.com writes: In warm standby system when we have a filled log segment forwarded to archiving, there is an inconsistency on standby next value sequences obtained by a call to nextval() function. e.g.: * Primary server - Create sequence seq_a; - Select nextval ( 'seq_a'); # value 1; - Log shipping; * Standby server - Failover; - Select nextval ( 'seq_a') on standby # value = currval + 31 (written ahead) AFAIK this occurs because some fetches (log_cnt) are made in advance and they are recorded in the log and shipping together. Does it necessary for some kind of overhead or something like that? Does it make sense to create a GUC to control the log_cnt amount rather than SEQ_LOG_VALS approach? No. If your application expects the series not to have gaps, your application is broken independently of warm standby. The same sort of advance would happen if the master crashed and restarted. Or if you ever roll back a transaction that has done nextval(). ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
