Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Some kind of hint that there are execptions is IMO very important. I'm not sure what the best form for it to take is. I am not sure I believe the assertion that any function or type with special transactional behavior will include a documentation mention. It absolutely should, but I guess that doesn't mean it's guaranteed to. It doesn't seem like a terribly future-proof assertion at any rate. With respect to the mention of autocommit, I think it would be good to add something there, but maybe it should cross-reference our existing documentation mentions of autocommit. Also, it's a bit ambiguous the way it's worded whether you get the automatic BEGIN/COMMIT with autocommit=on or with autocommit=off; somehow we should try to clarify what we mean a little more there. Yeah. I should've kept that separate, as it was something I noticed in passing, rather than central to the changes. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is prefix pg_ reservated ?
Hello all
I found strange behave of postgresql. I would to use name
pg_stat_get_some for custom function. Probably it is not smart from
me, because I found so functions that has prefix pg are not searched
via search_path.
postgres=# select public.pg_stat_get_creation_time('xxx'::regclass);
pg_stat_get_creation_time
---
2012-08-07 12:43:25.592137+02
(1 row)
postgres=# select pg_stat_get_creation_time('xxx'::regclass);
ERROR: internal function pg_stat_get_creation_time is not in
internal lookup table
It is expected and desired behave?
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
Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Tue, Aug 7, 2012 at 3:59 AM, Craig Ringer ring...@ringerc.id.au wrote: On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Some kind of hint that there are execptions is IMO very important. I'm not sure what the best form for it to take is. I'm not sure, either. Maybe we should avoid blanket statements and just say something like: Note: Some operations on sequences are non-transactional and will not be rolled back on transaction abort. See xref. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] is prefix pg_ reservated ?
On Tue, Aug 7, 2012 at 7:16 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello all
I found strange behave of postgresql. I would to use name
pg_stat_get_some for custom function. Probably it is not smart from
me, because I found so functions that has prefix pg are not searched
via search_path.
postgres=# select public.pg_stat_get_creation_time('xxx'::regclass);
pg_stat_get_creation_time
---
2012-08-07 12:43:25.592137+02
(1 row)
postgres=# select pg_stat_get_creation_time('xxx'::regclass);
ERROR: internal function pg_stat_get_creation_time is not in
internal lookup table
It is expected and desired behave?
That error message has nothing to do with a pg_ prefix, as you would
find out yourself if you grepped for it. It means you created the
functional using language internal, when you probably should have
used c.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] WIP Patch: Use sortedness of CSV foreign tables for query planning
On Tue, Aug 7, 2012 at 2:02 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: I think that optimizations like this are going to be essential for things like pgsql_fdw (or other_rdms_fdw). Despite the thorny semantic issues, we're just not going to be able to get around it. There will even be people who want SELECT * FROM ft ORDER BY 1 to order by the remote side's notion of ordering rather than ours, despite the fact that the remote side has some insane-by-PG-standards definition of ordering. People are going to find ways to do that kind of thing whether we condone it or not, so we might as well start thinking now about how we're going to live with it. But that doesn't answer the question of whether or not we ought to support it for file_fdw in particular, which seems like a more arguable point. For file_fdw, I feel inclined to simply implement file_fdw (1) to verify the key column is sorted in the specified way at the execution phase ie, at the (first) scan of a data file, only when pathkeys are set, and (2) to abort the transaction if it detects the data file is not sorted. That seems like an even worse idea. People who want to access data repeatedly should load it into tables. Mind you, if you want to publish a version of file_fdw on PGXN that does this, that's fine with me. But I don't think it belongs in core, at least not without a lot more evidence that there is a real demand for this than we have so far. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Beta 3
On Mon, Aug 6, 2012 at 11:22 PM, Craig Ringer ring...@ringerc.id.au wrote: Heya all It seems like it's a bit trickier to find beta downloads than might be ideal. The beta info page simply reads: PostgreSQL 9.2 beta 3 was released on August 6, 2012. with no information about how to obtain a build or the sources, not even a link to the downloads page, despite being linked to with the text: More information on how to test and report issues: http://www.postgresql.org/developer/beta; in the announcement email. It should at least link to: http://www.postgresql.org/download/snapshots/ and the -bugs form or guide to reporting problems page. The beta announcement email links to the main downloads page from which the snapshots/beta downloads page can be reached, but the main beta page is a dead-end. Not ideal for encouraging feedback and testing. Yeah, this is pretty bad. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Beta 3
On Tue, Aug 7, 2012 at 2:44 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Aug 6, 2012 at 11:22 PM, Craig Ringer ring...@ringerc.id.au wrote: Heya all It seems like it's a bit trickier to find beta downloads than might be ideal. The beta info page simply reads: PostgreSQL 9.2 beta 3 was released on August 6, 2012. with no information about how to obtain a build or the sources, not even a link to the downloads page, despite being linked to with the text: More information on how to test and report issues: http://www.postgresql.org/developer/beta; in the announcement email. It should at least link to: http://www.postgresql.org/download/snapshots/ and the -bugs form or guide to reporting problems page. The beta announcement email links to the main downloads page from which the snapshots/beta downloads page can be reached, but the main beta page is a dead-end. Not ideal for encouraging feedback and testing. Yeah, this is pretty bad. Agreed. Anybody up for writing the text though? There was some text earlier (see http://git.postgresql.org/gitweb/?p=pgweb.git;a=commitdiff;h=bd02e36141bb99e9ee4e0b80fd69464e0e6d91b7#patch18 or the corresponding one in the old pgweb repo). That can probably be adapted to be put in the new one - seems it was genereally just removed because there was no beta available, and the intentino wasn't that the page should remain useless once a beta was out.. -- 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] -Wformat-zero-length
On Fri, Aug 3, 2012 at 10:02 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Aug 3, 2012 at 04:01:18PM -0400, Robert Haas wrote: On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian br...@momjian.us wrote: I don't disagree with pg_upgrade being operationally complex, but I don't see how this relates to contrib vs. non-contrib at all. Are we supposed to only have simple programs in src/bin? That seems a strange policy. Well, perhaps we need to re-open the discussion then. I feel like putting it in src/bin would carry an implication of robustness that I'm not sanguine about. Granted, putting it in contrib has already pushed the envelope in that direction further than is perhaps warranted. But ISTM that if we ever want to put this in src/bin someone needs to devote some serious engineering time to filing down the rough edges. I don't know how to file down any of the existing rough edges. That would be the serious engineering time Robert is referring to, no? If you knew how to do it already it wouldn't require serious engineering time, just SMOP. -- 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
[HACKERS] Pg_ctl promote -- wait for slave to be promoted fully ?
Should we consider if we can make pg_ctl -w work for promote as well? The main problem is, I guess, that it can't log in - so wed' need something like PQping() that actually checked if it was master or slave? //Magnus -- Forwarded message -- From: Manoj Govindassamy ma...@nimblestorage.com Date: Fri, Jul 20, 2012 at 9:28 PM Subject: [GENERAL] Pg_ctl promote -- wait for slave to be promoted fully ? To: pgsql-gene...@postgresql.org Hi team, I am using PG 9.1.2 and I am promoting a slave to master with the following command. pg_ctl promote -D /pat/to/data Command does return back faster with code 0 ( = success). I assumed the slave is now the master and issued write operations. But the statement failed as the DB complained that it can do only read operation. Postgres.log showed ready to accept connection a second later after I issued the statement. So, my understanding is pg_ctl promote is triggering a slave promote and is asynchornous. -- Anyway I can query the state of DB to know its status slave or master ?? So, that i can issue write statements only after I know that the DB is the new master now -- Or anyway to make pg_ctl promote to wait till the slave is completely promoted ? Any help is appreciated. -- thanks, Manoj -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] [PATCH] Docs: Make notes on sequences and rollback more obvious
Robert Haas robertmh...@gmail.com wrote: Craig Ringer ring...@ringerc.id.au wrote: On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Some kind of hint that there are execptions is IMO very important. I'm not sure what the best form for it to take is. I'm not sure, either. Maybe we should avoid blanket statements and just say something like: Note: Some operations on sequences are non-transactional and will not be rolled back on transaction abort. See xref. I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I think maybe a mention in the Introduction section of that chapter with a ref would be appropriate. -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] -Wformat-zero-length
On Tue, Aug 7, 2012 at 03:06:23PM +0200, Magnus Hagander wrote: On Fri, Aug 3, 2012 at 10:02 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Aug 3, 2012 at 04:01:18PM -0400, Robert Haas wrote: On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian br...@momjian.us wrote: I don't disagree with pg_upgrade being operationally complex, but I don't see how this relates to contrib vs. non-contrib at all. Are we supposed to only have simple programs in src/bin? That seems a strange policy. Well, perhaps we need to re-open the discussion then. I feel like putting it in src/bin would carry an implication of robustness that I'm not sanguine about. Granted, putting it in contrib has already pushed the envelope in that direction further than is perhaps warranted. But ISTM that if we ever want to put this in src/bin someone needs to devote some serious engineering time to filing down the rough edges. I don't know how to file down any of the existing rough edges. That would be the serious engineering time Robert is referring to, no? If you knew how to do it already it wouldn't require serious engineering time, just SMOP. Oh, I read serious _engineering_ time to say that it is just a matter of coding, while I don't even have a design idea of how to improve this, meaning it is a lot farther away than just coding it. I equiated engineering with coding, which I guess was wrong. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: pg_pretty_query
Hello
last year we are spoke about reusing pretty print view code for some queries.
Here is patch:
this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well
postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
pg_pretty_query
--
SELECT x.a, z.a+
FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
WHERE foo.a = z.a))
(1 row)
Regards
Pavel
pg_pretty_query.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: pg_pretty_query
On 7 August 2012 15:14, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
last year we are spoke about reusing pretty print view code for some queries.
Here is patch:
this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well
postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
pg_pretty_query
--
SELECT x.a, z.a+
FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
WHERE foo.a = z.a))
(1 row)
This looks odd:
postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING NULLS FIRST',
true, false);
pg_pretty_query
--
SELECT 1, +
( SELECT max(a.x) + GREATEST(2, 3) +
FROM generate_series(4, 10, 2) a(x)) +
FROM generate_series(1, 100) generate_series(generate_series)+
GROUP BY 1::integer +
ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3) +
FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
(1 row)
USING is removed completely (or if I used DESC, NULLS FIRST is then
removed instead), 2 in the order by is expanded to its full query,
and generate_series when used in FROM is repeated with its own name as
a parameter. I'm also not sure about the spacing before each line.
SELECT, FROM and GROUP BY all appear out of alignment from one
another.
Plus it would be nice if we could support something like the following style:
SELECT
field_one,
field_two + field_three
FROM
my_table
INNER JOIN
another_table
ON
my_table.field_one = another_table.another_field
AND
another_table.valid = true
WHERE
field_one 3
AND
field_two 10;
But that's just a nice-to-have.
--
Thom
--
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] -Wformat-zero-length
Excerpts from Bruce Momjian's message of vie ago 03 16:02:28 -0400 2012: On Fri, Aug 3, 2012 at 04:01:18PM -0400, Robert Haas wrote: On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian br...@momjian.us wrote: I don't disagree with pg_upgrade being operationally complex, but I don't see how this relates to contrib vs. non-contrib at all. Are we supposed to only have simple programs in src/bin? That seems a strange policy. Well, perhaps we need to re-open the discussion then. I feel like putting it in src/bin would carry an implication of robustness that I'm not sanguine about. Granted, putting it in contrib has already pushed the envelope in that direction further than is perhaps warranted. But ISTM that if we ever want to put this in src/bin someone needs to devote some serious engineering time to filing down the rough edges. I don't know how to file down any of the existing rough edges. So do you have a list of rough edges? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] WIP: pg_pretty_query
2012/8/7 Thom Brown t...@linux.com:
On 7 August 2012 15:14, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
last year we are spoke about reusing pretty print view code for some queries.
Here is patch:
this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well
postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
pg_pretty_query
--
SELECT x.a, z.a+
FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
WHERE foo.a = z.a))
(1 row)
This looks odd:
postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING NULLS FIRST',
true, false);
pg_pretty_query
--
SELECT 1, +
( SELECT max(a.x) + GREATEST(2, 3) +
FROM generate_series(4, 10, 2) a(x)) +
FROM generate_series(1, 100) generate_series(generate_series)+
GROUP BY 1::integer +
ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3) +
FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
(1 row)
USING is removed completely (or if I used DESC, NULLS FIRST is then
removed instead), 2 in the order by is expanded to its full query,
and generate_series when used in FROM is repeated with its own name as
a parameter. I'm also not sure about the spacing before each line.
SELECT, FROM and GROUP BY all appear out of alignment from one
another.
it is issue - probably we can start deserialization just from parser
stage, not from rewriter stage - but then code will be significantly
longer and we cannot reuse current code for pretty print view.
Plus it would be nice if we could support something like the following style:
SELECT
field_one,
field_two + field_three
FROM
my_table
INNER JOIN
another_table
ON
my_table.field_one = another_table.another_field
AND
another_table.valid = true
WHERE
field_one 3
AND
field_two 10;
it is second issue - probably there are more lovely styles - CELKO,
your and other. I am not sure if we can support more styles in core
(contrib should be better maybe).
Regards
Pavel
But that's just a nice-to-have.
--
Thom
--
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] -Wformat-zero-length
On Tue, Aug 7, 2012 at 10:38:52AM -0400, Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of vie ago 03 16:02:28 -0400 2012: On Fri, Aug 3, 2012 at 04:01:18PM -0400, Robert Haas wrote: On Fri, Aug 3, 2012 at 3:22 PM, Bruce Momjian br...@momjian.us wrote: I don't disagree with pg_upgrade being operationally complex, but I don't see how this relates to contrib vs. non-contrib at all. Are we supposed to only have simple programs in src/bin? That seems a strange policy. Well, perhaps we need to re-open the discussion then. I feel like putting it in src/bin would carry an implication of robustness that I'm not sanguine about. Granted, putting it in contrib has already pushed the envelope in that direction further than is perhaps warranted. But ISTM that if we ever want to put this in src/bin someone needs to devote some serious engineering time to filing down the rough edges. I don't know how to file down any of the existing rough edges. So do you have a list of rough edges? Yes, the list of rough edges is the 14-steps you have to perform to run pg_upgrade, as documented in the pg_upgrade manual page: http://www.postgresql.org/docs/9.2/static/pgupgrade.html The unknown is how to reduce the number of steps in a way the community would find acceptable. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Docs: Make notes on sequences and rollback more obvious
On Tuesday, August 07, 2012 09:45:35 AM Kevin Grittner wrote: [...snipped...] I also think it's a problem that one can get through the entire Concurrency Control chapter (mvcc.sgml) without a clue that sequences aren't transactional. I think maybe a mention in the Introduction section of that chapter with a ref would be appropriate. +1 -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] WIP: pg_pretty_query
On Tue, Aug 7, 2012 at 04:14:34PM +0200, Pavel Stehule wrote:
Hello
last year we are spoke about reusing pretty print view code for some queries.
Here is patch:
this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well
postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
pg_pretty_query
--
SELECT x.a, z.a+
FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
WHERE foo.a = z.a))
(1 row)
I can see this as very useful for people reporting badly-formatted
queries to our email lists. Great!
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] WIP: pg_pretty_query
On 08/07/2012 10:14 AM, Pavel Stehule wrote:
Hello
last year we are spoke about reusing pretty print view code for some queries.
Here is patch:
this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well
postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
pg_pretty_query
--
SELECT x.a, z.a+
FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
WHERE foo.a = z.a))
(1 row)
Good stuff. That's one less item on my TODO list :-)
I think we should have a version that lets you specify the wrap column,
like pg_get_viewdef does. Possibly for this case we should even default
it to 0 (wrap after each item) instead of 79 which it is for views.
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] Beta 3
On Tue, Aug 7, 2012 at 8:51 AM, Magnus Hagander mag...@hagander.net wrote: Agreed. Anybody up for writing the text though? There was some text earlier (see http://git.postgresql.org/gitweb/?p=pgweb.git;a=commitdiff;h=bd02e36141bb99e9ee4e0b80fd69464e0e6d91b7#patch18 or the corresponding one in the old pgweb repo). That can probably be adapted to be put in the new one - seems it was genereally just removed because there was no beta available, and the intentino wasn't that the page should remain useless once a beta was out.. How about we start by adding link(s) to the download page(s)? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] WIP: pg_pretty_query
On Tue, Aug 07, 2012 at 04:54:12PM +0200, Pavel Stehule wrote:
2012/8/7 Thom Brown t...@linux.com:
On 7 August 2012 15:14, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
last year we are spoke about reusing pretty print view code for some
queries.
Here is patch:
this patch is really short - it is nice. But - it works only with
known database objects (probably we would it) and it doesn't format
subqueries well
postgres=# select pg_pretty_query('select x.*, z.* from foo, foo x, x
z where x.a = 10 and x.a = 30 and EXISTS(SELECT * FROM foo WHERE a =
z.a)', true, false);
pg_pretty_query
--
SELECT x.a, z.a+
FROM foo, foo x, x z +
WHERE x.a = 10 AND x.a = 30 AND (EXISTS ( SELECT foo.a+
FROM foo +
WHERE foo.a = z.a))
(1 row)
This looks odd:
postgres=# SELECT pg_pretty_query('SELECT 1, (SELECT max(a.x) +
greatest(2,3) FROM generate_series(4,10,2) a(x)) FROM
generate_series(1,100) GROUP BY 1 ORDER BY 1, 2 USING NULLS FIRST',
true, false);
pg_pretty_query
--
SELECT 1, +
( SELECT max(a.x) + GREATEST(2, 3) +
FROM generate_series(4, 10, 2) a(x)) +
FROM generate_series(1, 100) generate_series(generate_series)+
GROUP BY 1::integer +
ORDER BY 1::integer, ( SELECT max(a.x) + GREATEST(2, 3) +
FROM generate_series(4, 10, 2) a(x)) NULLS FIRST
(1 row)
USING is removed completely (or if I used DESC, NULLS FIRST is then
removed instead), 2 in the order by is expanded to its full query,
and generate_series when used in FROM is repeated with its own name as
a parameter. I'm also not sure about the spacing before each line.
SELECT, FROM and GROUP BY all appear out of alignment from one
another.
it is issue - probably we can start deserialization just from parser
stage, not from rewriter stage - but then code will be significantly
longer and we cannot reuse current code for pretty print view.
Plus it would be nice if we could support something like the following
style:
SELECT
field_one,
field_two + field_three
FROM
my_table
INNER JOIN
another_table
ON
my_table.field_one = another_table.another_field
AND
another_table.valid = true
WHERE
field_one 3
AND
field_two 10;
it is second issue - probably there are more lovely styles - CELKO,
your and other. I am not sure if we can support more styles in core
(contrib should be better maybe).
Would it be better to have output plugins and not privilege one?
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pg_ctl promote -- wait for slave to be promoted fully ?
On Tue, Aug 7, 2012 at 10:22 PM, Magnus Hagander mag...@hagander.net wrote: Should we consider if we can make pg_ctl -w work for promote as well? +1 The main problem is, I guess, that it can't log in - so wed' need something like PQping() that actually checked if it was master or slave? Yes. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade bug in Windows on PG 9.2
I just got a bug report from EnterpriseDB saying pg_upgrade generates a
file share violation on PG 9.2. I was initially confused because I know
we fixed this in a May commit. Well, it turns out that this commit
re-added the same failure:
commit 4741e9afb93f0d769655b2d18c2b73b86f281010
Author: Alvaro Herrera alvhe...@alvh.no-ip.org
Date: Thu Jun 28 23:27:00 2012 -0400
Make the pg_upgrade log files contain actual commands
Now the log file not only contains the output from commands
executed by
system(), but also what command it was in the first place. This
arrangement makes debugging a lot simpler.
The commit was certainly a good idea, but exec_prog's log_file
specification was designed as for use in an error string, meaning that
the creation on an error string at one call sight would cause pg_upgrade
to erroneously create a log file named file1 or file2 on Windows. The
bigger problem is that the log file was opened at the start of the
function (with no error return check), and kept open until the end,
causing the file to be opened while the command string was run --- this
was causing the share violation.
I have applied the attached patch to head and 9.2 to fix all these
issues.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 71d8f75..aa896b5
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** issue_warnings(char *sequence_script_fil
*** 183,189
if (sequence_script_file_name)
{
prep_status(Adjusting sequences);
! exec_prog(true, true, UTILITY_LOG_FILE,
SYSTEMQUOTE \%s/psql\ --echo-queries
--set ON_ERROR_STOP=on
--no-psqlrc --port %d --username \%s\
--- 183,189
if (sequence_script_file_name)
{
prep_status(Adjusting sequences);
! exec_prog(true, true, UTILITY_LOG_FILE, NULL,
SYSTEMQUOTE \%s/psql\ --echo-queries
--set ON_ERROR_STOP=on
--no-psqlrc --port %d --username \%s\
diff --git a/contrib/pg_upgrade/dump.c b/contrib/pg_upgrade/dump.c
new file mode 100644
index 571792b..07a3b54
*** a/contrib/pg_upgrade/dump.c
--- b/contrib/pg_upgrade/dump.c
*** generate_old_dump(void)
*** 23,29
* --binary-upgrade records the width of dropped columns in pg_class, and
* restores the frozenid's for databases and relations.
*/
! exec_prog(true, true, UTILITY_LOG_FILE,
SYSTEMQUOTE \%s/pg_dumpall\ --port %d --username \%s\
--schema-only --binary-upgrade %s \%s\ 2 \%s\
SYSTEMQUOTE, new_cluster.bindir, old_cluster.port, os_info.user,
--- 23,29
* --binary-upgrade records the width of dropped columns in pg_class, and
* restores the frozenid's for databases and relations.
*/
! exec_prog(true, true, UTILITY_LOG_FILE, NULL,
SYSTEMQUOTE \%s/pg_dumpall\ --port %d --username \%s\
--schema-only --binary-upgrade %s \%s\ 2 \%s\
SYSTEMQUOTE, new_cluster.bindir, old_cluster.port, os_info.user,
diff --git a/contrib/pg_upgrade/exec.c b/contrib/pg_upgrade/exec.c
new file mode 100644
index 0d6fb8d..6f993df
*** a/contrib/pg_upgrade/exec.c
--- b/contrib/pg_upgrade/exec.c
*** static int win32_check_directory_write_p
*** 33,50
* line to be executed is saved to the specified log file.
*
* If throw_error is TRUE, this function will throw a PG_FATAL error
! * instead of returning should an error occur.
*/
int
! exec_prog(bool throw_error, bool is_priv,
! const char *log_file, const char *fmt,...)
{
va_list args;
int result;
int retval;
char cmd[MAXPGPATH];
mode_t old_umask = 0;
! FILE *log = fopen(log_file, a+);
if (is_priv)
old_umask = umask(S_IRWXG | S_IRWXO);
--- 33,51
* line to be executed is saved to the specified log file.
*
* If throw_error is TRUE, this function will throw a PG_FATAL error
! * instead of returning should an error occur. The command it appended
! * to log_file; opt_log_file is used in error messages.
*/
int
! exec_prog(bool throw_error, bool is_priv, const char *log_file,
! const char *opt_log_file, const char *fmt,...)
{
va_list args;
int result;
int retval;
char cmd[MAXPGPATH];
mode_t old_umask = 0;
! FILE *log;
if (is_priv)
old_umask = umask(S_IRWXG | S_IRWXO);
*** exec_prog(bool throw_error, bool is_priv
*** 53,61
vsnprintf(cmd, MAXPGPATH, fmt, args);
va_end(args);
pg_log(PG_VERBOSE, %s\n, cmd);
fprintf(log, command: %s\n, cmd);
! fflush(log);
result = system(cmd);
--- 54,68
vsnprintf(cmd, MAXPGPATH, fmt, args);
va_end(args);
+ if ((log = fopen_priv(log_file, a+)) == NULL)
+
Re: [HACKERS] [PATCH] Docs: Make notes on sequences and rollback more obvious
On Tue, Aug 07, 2012 at 03:59:42PM +0800, Craig Ringer wrote: On 08/07/2012 02:27 AM, Robert Haas wrote: I did not commit the advanced.sgml changes. That's arguably the most important point to raise this. The most recent question came from someone who actually bothered to RTFM and believed based on the advanced-transactions page that rollback rolls *everything* back. Perhaps we should see about correcting that misapprehension. When PostgreSQL does any irreversible process http://en.wikipedia.org/wiki/Irreversible_process such as incrementing a sequence, writing a file, sending an email, etc., it can't be rolled back. Might it be useful to find those irreversible operations we document and mark same? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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: BUG #6742: pg_dump doesn't convert encoding of DB object names to OS encoding
On Wed, Jul 25, 2012 at 7:54 AM, Alexander Law exclus...@gmail.com wrote: Hello, I would like to fix this bug, but it looks like it would be not one-line patch. Looking at the pg_dump code I see that the object names come through the following chain: 1. pg_dump executes 'SELECT c.tableoid, c.oid, c.relname, ... ' and gets the object_name with the encoding chosen for db connection/dump. 2. it invokes write_msg function or alike: write_msg(NULL, finding the columns and types of table \%s\\n, tbinfo-dobj.name); 3. vwrite_msg localizes text message, but not the argument(s): vfprintf(stderr, _(fmt), ap); Here gettext (_) internally translates fmt to OS encoding (if it's different from UTF-8 - encoding of a localized strings). And I can see only a few solutions of the problem: 1. To convert the object name at the back-end, i.e. to modify all the similar SELECT's as: 'SELECT c.tableoid, c.oid, c.relname, convert_to(c.relname, 'OS_ENCODING') AS locrelname, ...' and then do write_msg(NULL, finding the columns and types of table \%s\\n, tbinfo-dobj.local_name); The downside of this approach is that it requires rewriting all the SELECT's for all the object. And it doesn't help us to write out any other text from backend, such as localized backend error. 2. To setup another connection to backend with the OS encoding, and to get all the object names through it. It looks insane too. And we have the same problem with the localized backend errors coming on main connection. 3. To make convert_to_os_encoding(text, encoding) function for a frontend utilities. Unfortunately frontend can't use internal PostgreSQL conversion functions, and modifying them to use through libpq looks unfeasible. So the only way to implement such function is to use another encoding conversion framework (library). And my question is - is it possible to include libiconv (add this dependency) to the frontend utilities code? 4. To force users to use OS encoding as the Database encoding. Or to not use non-ASCII characters in an db object names and to disable nls on Windows completely. It doesn't look like a solution at all. I think if you're going to try to do something about this, #1 is probably the best option. It does sound like a lot of work, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] WIP patch for LATERAL subqueries
Here's an updated version of my LATERAL patch. * Accepts LATERAL func_name(args). * Handles LATERAL in JOIN nests now. I rewrote the way transformFromClause manages visibility of previously-parsed FROM items. Rather than my previous idea of adding more namespace lists to a ParseState, I changed p_relnamespace and p_varnamespace to be lists of ParseNamespaceItem structs, which carry an RTE pointer plus visibility flags. This seemed to work out fairly well, and allowed me to implement the RIGHT/FULL JOIN case exactly as per SQL spec, that is the left side of a RIGHT JOIN LATERAL is visible to the right side but throws error if used. (I'm a bit tempted now to collapse p_relnamespace and p_varnamespace into just one list, by adding two more booleans to ParseNamespaceItem to show whether the item is visible for qualified or unqualified references. But that would affect code that the current patch doesn't need to touch, so it seems better to postpone it to a separate refactoring patch.) This results in some changes in the error messages output for improper-column-reference errors, as shown in the regression test deltas. It also fixes the pre-existing spec-conformance issue about lateral versus parent references, as per Andrew Gierth in http://archives.postgresql.org/message-id/87ocpjscpa@news-spur.riddles.org.uk * Throws error if LATERAL is used to create an aggregate that belongs to its aggregation query's FROM clause. I did this in a slightly grotty way: transformAggregateCall looks at the p_lateral_active field of the appropriate pstate level, relying on the fact that the only way the case can happen is via LATERAL. As I mentioned earlier, I think it'd be better to add a ParseState field showing exactly which query part we're parsing at any instant. However, again that seems better done as part of a separate refactoring patch. I have not done anything yet about the planner shortcomings. What I'd like to do next, barring objections, is to band-aid the places where the planner could crash on a LATERAL query (probably just make it throw FEATURE_NOT_SUPPORTED errors), write some documentation, and then commit what I've got. After that I can go back to improve the planner and work on the parser refactoring issues as separate patches. Comments, better ideas? regards, tom lane binLPTyx5cE7r.bin Description: lateral-2.patch.gz -- 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] avoid unnecessary failure to open restored WAL files
On 2 August 2012 17:18, Fujii Masao masao.fu...@gmail.com wrote: Hi, In HEAD and 9.2, the following scenario happens in archive recovery. 1. The archived WAL file is restored onto the temporary file name RECOVERYXLOG. 2. The restored WAL file is renamed to the correct file name like 00010002. 3. The startup process tries to open the temporary file even though it's already been renamed and doesn't exist. This always fails. 4. The startup process retries to open the correct file as a WAL file in pg_xlog directory instead of the archived file. This succeeds. The above failure of file open is unnecessary, so I think we can avoid that. Attached patch changes the startup process so that it opens the correct restored WAL file after restoring the archived WAL file. Looks to me that the strncpy is backwards and will still fail. Please double check. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] WIP: pg_pretty_query
Bruce Momjian br...@momjian.us writes: On Tue, Aug 7, 2012 at 04:14:34PM +0200, Pavel Stehule wrote: last year we are spoke about reusing pretty print view code for some queries. Here is patch: I can see this as very useful for people reporting badly-formatted queries to our email lists. Great! Allow me to express a contrary opinion: I think this is a bad idea. * First off, packaging it as a SQL function that takes and returns text seems rather awkward to use. A lot of places where you might wish for a SQL pretty-printer aren't going to have a database connection handy (think emacs SQL mode). * The functionality provided is not merely a pretty-printer but sort of a query validator as well: the function will fail if the query refers to any tables, columns, functions, etc you don't have in your database. For some applications that's fine, but others not so much --- in particular I suspect it's nigh useless for the use-case you mention of quickly turning an emailed query into something legible. And there's no way to separate out the reformatting functionality from that. * As per some of the complaints already registered in this thread, ruleutils.c is not designed with the goal of being a pretty-printer. Its primary charter is to support pg_dump by regurgitating rules/views in an unambiguous form, which does not necessarily look very similar to what was entered. An example of a transformation that probably nobody would want in a typical pretty-printing context is expansion of SELECT * lists. But again, there is really no way to turn that off. Another aspect that seems pretty undesirable for pretty-printing is loss of any comments embedded in the query text. I'm very much not in favor of trying to make ruleutils serve two masters, but that's the game we will be playing if we accept this patch. In short, the only redeeming value of this patch is that it's short. The functionality it provides is not something that anyone would come up with in a green-field design for a pretty-printer, and if we take it we are going to be faced with a whole lot of redesign requests that will be painful to implement and will carry heavy risks of breaking pg_dump and/or EXPLAIN. 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] bug of pg_trgm?
Hi,
When I used pg_trgm, I encountered the problem that the search result of
SeqScan was the different from that of BitmapScan even if the search
keyword was the same. Is this a bug? Here is the test case:
---
CREATE EXTENSION pg_trgm;
CREATE TABLE tbl (col text);
CREATE INDEX idx ON tbl USING gin (col gin_trgm_ops);
INSERT INTO tbl VALUES ('abc'), ('ab c');
SET enable_seqscan TO off;
SET enable_bitmapscan TO on;
SELECT * FROM tbl WHERE col LIKE E'%\\c%';
col
--
ab c
(1 row)
SET enable_seqscan TO on;
SET enable_bitmapscan TO off;
SELECT * FROM tbl WHERE col LIKE E'%\\c%';
col
--
abc
ab c
(2 rows)
---
The cause is ISTM that pg_trgm wrongly ignores the heading wildcard
character (i.e., %) when an escape (i.e., \\) follows the wildcard character.
Attached patch fixes this.
The patch fixes another problem: pg_trgm wrongly ignores the backslash \\
following the escape, i.e., . This problem might be harmless when
KEEPONLYALNUM is enabled because any characters other than
alphabets and digits are ignored. But, when KEEPONLYALNUM is disabled,
should be interpreted as a backslash character itself, but
pg_trgm does not.
Regards,
--
Fujii Masao
trgm_bugfix_v1.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: pg_pretty_query
On 08/07/2012 02:14 PM, Tom Lane wrote: * As per some of the complaints already registered in this thread, ruleutils.c is not designed with the goal of being a pretty-printer. Its primary charter is to support pg_dump by regurgitating rules/views in an unambiguous form, which does not necessarily look very similar to what was entered. An example of a transformation that probably nobody would want in a typical pretty-printing context is expansion of SELECT * lists. But again, there is really no way to turn that off. Another aspect that seems pretty undesirable for pretty-printing is loss of any comments embedded in the query text. I'm very much not in favor of trying to make ruleutils serve two masters, but that's the game we will be playing if we accept this patch. I think this horse has probably bolted. If you wanted to segregate off this functionality we shouldn't have used things like pg_get_viewdef in psql, ISTM. In short, the only redeeming value of this patch is that it's short. The functionality it provides is not something that anyone would come up with in a green-field design for a pretty-printer, and if we take it we are going to be faced with a whole lot of redesign requests that will be painful to implement and will carry heavy risks of breaking pg_dump and/or EXPLAIN. One of the challenges is to have a pretty printer that is kept in sync with the dialect that's supported. Anything that doesn't use the backend's parser seems to me to be guaranteed to get out of sync very quickly. 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] WIP: pg_pretty_query
Andrew Dunstan and...@dunslane.net writes: On 08/07/2012 02:14 PM, Tom Lane wrote: In short, the only redeeming value of this patch is that it's short. One of the challenges is to have a pretty printer that is kept in sync with the dialect that's supported. Anything that doesn't use the backend's parser seems to me to be guaranteed to get out of sync very quickly. Sure. I think if we wanted an actually engineered solution, rather than a half-baked one, ecpg provides a good source of inspiration. One could imagine a standalone program that reads a query on stdin and emits a pretty-printed version to stdout, using a parser that is automatically generated from the backend's grammar with much the same technology used in recent ecpg releases. I think that would address most of the complaints I raised: it would be relatively painless to make use of from contexts that don't have a live database connection, it wouldn't impose any constraints related to having suitable database content available, it wouldn't apply any of the multitude of implementation-dependent transformations that the backend's parser does, and it could be built (I think) to do something more with comments than just throw them away. 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] Inserting heap tuples in bulk in COPY
On Fri, Aug 12, 2011 at 2:59 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 13.08.2011 00:17, Simon Riggs wrote: Also, we discussed that you would work on buffering the index inserts, which is where the main problem lies. The main heap is only a small part of the overhead if we have multiple indexes already built on a table - which is the use case that causes the most problem. Sure, if you have indexes on the table already, then the overhead of updating them is more significant. I am actually working on that too, I will make a separate post about that. Hi Heikki, Is the bulk index insert still an active area for you? If not, is there some kind of summary of design or analysis work already done, which would be a useful for someone else interested in picking it up? Thanks, Jeff -- 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] WIP: pg_pretty_query
On 7 August 2012 20:01, Andrew Dunstan and...@dunslane.net wrote: On 08/07/2012 02:14 PM, Tom Lane wrote: * As per some of the complaints already registered in this thread, ruleutils.c is not designed with the goal of being a pretty-printer. Its primary charter is to support pg_dump by regurgitating rules/views in an unambiguous form, which does not necessarily look very similar to what was entered. An example of a transformation that probably nobody would want in a typical pretty-printing context is expansion of SELECT * lists. But again, there is really no way to turn that off. Another aspect that seems pretty undesirable for pretty-printing is loss of any comments embedded in the query text. I'm very much not in favor of trying to make ruleutils serve two masters, but that's the game we will be playing if we accept this patch. +1. A pretty-printer that makes the query to be cleaned-up actually undergo parse-analysis seems misconceived to me. This is a tool that begs to be written in something like Python, as a satellite project, with much greater flexibility in the format of the SQL that it outputs. One of the challenges is to have a pretty printer that is kept in sync with the dialect that's supported. Anything that doesn't use the backend's parser seems to me to be guaranteed to get out of sync very quickly. I'm not convinced of that. Consider the example of cscope, a popular tool for browsing C code. Its parser was written to be fuzzy, so it's actually perfectly usable for C++ and Java, even though that isn't actually supported, IIRC. Now, I'll grant you that that isn't a perfectly analogous situation, but it is similar in some ways. If we add a new clause to select and that bit is generically pretty-printed, is that really so bad? I have a hard time believing that a well written fuzzy pretty-printer for Postgres wouldn't deliver the vast majority of the benefits of an in-core approach, at a small fraction of the effort. You'd also be able to pretty-print plpgsql function definitions (a particularly compelling case for this kind of tool), which any approach based on the backends grammar will never be able to do (except, perhaps, if you were to do something even more complicated). -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pg_ctl promote -- wait for slave to be promoted fully ?
On Tue, Aug 7, 2012 at 1:09 PM, Fujii Masao masao.fu...@gmail.com wrote: On Tue, Aug 7, 2012 at 10:22 PM, Magnus Hagander mag...@hagander.net wrote: Should we consider if we can make pg_ctl -w work for promote as well? +1 The main problem is, I guess, that it can't log in - so wed' need something like PQping() that actually checked if it was master or slave? Yes. Is there a security concern with exposing this information to people who haven't authenticated? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Inserting heap tuples in bulk in COPY
On 7 August 2012 20:58, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Aug 12, 2011 at 2:59 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 13.08.2011 00:17, Simon Riggs wrote: Also, we discussed that you would work on buffering the index inserts, which is where the main problem lies. The main heap is only a small part of the overhead if we have multiple indexes already built on a table - which is the use case that causes the most problem. Sure, if you have indexes on the table already, then the overhead of updating them is more significant. I am actually working on that too, I will make a separate post about that. Hi Heikki, Is the bulk index insert still an active area for you? If not, is there some kind of summary of design or analysis work already done, which would be a useful for someone else interested in picking it up? The main cost comes from repeated re-seeking down the index tree to find the insertion point, but repeated lock and pin operations on index buffers are also high. That is optimisable if the index inserts are grouped, as they will be with monotonic indexes, (e.g. SERIAL), or with partial monotonic (i.e. with Parent/Child relationship, on Child table many inserts will be of the form (x,1), (x,2), (x, 3) etc, e.g. Order/OrderLine). All we need do is buffer the inserts in the inserts, before inserting them into the main index. As long as we flush the buffer before end of transaction, we're good. Incidentally, we can also optimise repeated inserts within a normal transaction using this method, by implementing deferred unique constraints. At present we say that unique constraints aren't deferrable, but there's no reason they can't be, if we allow buffering in the index. (Implementing a deferred constraint that won't fail if we do UPDATE foo SET pk = pk+1 requires a buffer the size of foo, which is probably a bad plan, plus you'd need to sort the inputs, so that particular nut is another issue altogether, AFAICS). Suggested implementation is to buffer index tuples at the generic index API, then implement a bulk insert index API call that can then be implemented for each AM separately. Suggested buffer size is work_mem. Note we must extract index tuple from heap tuples - refetching heap blocks to get rows is too costly. I think we need to implement buffering both to end of statement or end of transaction, not just one or the other. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] WIP: pg_pretty_query
Peter Geoghegan pe...@2ndquadrant.com writes: On 7 August 2012 20:01, Andrew Dunstan and...@dunslane.net wrote: One of the challenges is to have a pretty printer that is kept in sync with the dialect that's supported. Anything that doesn't use the backend's parser seems to me to be guaranteed to get out of sync very quickly. I'm not convinced of that. Consider the example of cscope, a popular tool for browsing C code. Its parser was written to be fuzzy, so it's actually perfectly usable for C++ and Java, even though that isn't actually supported, IIRC. Now, I'll grant you that that isn't a perfectly analogous situation, but it is similar in some ways. Yeah. A related question here is whether you want a pretty printer that is entirely unforgiving of (what it thinks are) syntax errors in the input. It might be a lot more useful if it didn't spit up on that, but just did the best it could. 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] WIP patch for LATERAL subqueries
I wrote: What I'd like to do next, barring objections, is to band-aid the places where the planner could crash on a LATERAL query (probably just make it throw FEATURE_NOT_SUPPORTED errors), write some documentation, and then commit what I've got. After that I can go back to improve the planner and work on the parser refactoring issues as separate patches. ... and done (though the pgsql-committers message seems to have got hung up for moderation). I put some simplistic examples into section 7.2.1.5 and the SELECT reference page ... if anybody has ideas for more-compelling small examples, please speak up. 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] WIP fix proposal for bug #6123
Did we ever decide on this? Is it a TODO? --- On Fri, Jul 22, 2011 at 04:01:20PM -0500, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: On Wed, Jul 20, 2011 at 2:58 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: So basically, the goal of this patch is to ensure that a BEFORE DELETE trigger doesn't silently fail to delete a row because that row was updated during the BEFORE DELETE trigger firing (in our case by secondary trigger firing). I've run across this problem before while writing application code and have found it surprising, unfortunate, and difficult to work around. It's not so bad when it only bites you once, but as things get more complicated and you have more and more triggers floating around, it gets pretty darn horrible. One of the things I've done to mitigate the impact of this is to write as many triggers as possible as AFTER triggers Yeah, this is not an issue in AFTER triggers, so moving any updating to those is a solution. In most cases that's where you want triggered modifications to take place anyway. The cascading delete situation is the most obvious exception, although there certainly could be others. but that's sometimes difficult to accomplish. Yeah, sometimes. Your scenario is a BEFORE DELETE trigger that does an UPDATE on the same row, but I think this problem also occurs if you have a BEFORE UPDATE trigger that does an UPDATE on the same row. I believe the second update gets silently ignored. My testing shows that the primary update gets ignored, while all the triggered effects of that update are persisted. Yuck. :-( It certainly seems possible to turn that around, but that hardly seems better. In asking application programmers here what they would *expect* to happen, they all seem to think that it is surprising that the BEFORE trigger functions *return a record*, rather than a boolean to say whether to proceed with the operation. They feel it would be less confusing if a value set into NEW was effective if the operation does take effect, and the boolean controls whether or not that happens. They rather expect that if an update happens from the same transaction while a before trigger is running, that the NEW record will reflect the change. I recognize how hard it would be to create that expected behavior, and how unlikely it is that the community would accept such a change at this point. But current behavior is to silently do something really dumb, so I think some change should be considered -- even if that change is to throw an error where we now allow nonsense. INSERT is not a problem -- if a BEFORE INSERT trigger inserts a row with a conflicting primary key (or other unique index key), the operation will be rolled back. That's fine. I think DELETE can be cleanly fixed with a patch similar to what I posted earlier in the thread. I found one more value that looks like it should be set, and it could use some comments, but I believe that we can get DELETE behavior which is every bit as sensible as INSERT behavior with a very small change. The worms do come crawling out of the can on BEFORE UPDATE triggers, though. When faced with an UPDATE which hasn't yet been applied, and other UPDATEs triggering from within the BEFORE UPDATE trigger which touch the same row, it doesn't seem like you can honor both the original UPDATE which was requested *and* the triggered UPDATEs. Of course, if you discard the original UPDATE, you can't very well do anything with the record returned from the BEFORE UPDATE trigger for that update. Since it seems equally evil to allow the update while ignoring some of the work caused by its trigger functions as to show the work of the triggered updates while suppressing the original update, I think the right thing is to throw an error if the old row for a BEFORE UPDATE is updated by the same transaction and the trigger function ultimately returns a non-NULL value. Thoughts? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Possible bug in PostgreSQL 9.2 stable: TwoPhaseGetDummyBackendId()
I have looked at the Postgres 9.2 stable and Postgres 9.2 beta 3 git archives and this bug still appears to be present. TwoPhaseGetDummyProc returns a PGPROC*. In 9.0, it was safe for TwoPhaseGetDummyBackendId() to cast this to a GlobalTransaction because the GlobalTransactionData structure's first element was always a PGPROC structure. However, in 9.2 this is no longer true. Despite the mismatch that now exists between the structures, TwoPhaseGetDummyBackendId() still attempts to cast a PGPROC* to a GlobalTransaction to extract the member 'dummyBackendId'. Rewriting this function to use the GlobalTransaction structure that is found in TwoPhaseState-prepXacts[] appears to fix the problem. In practice this appears to result in TwoPhaseGetDummyBackendId() returning invalid values (such as 0) instead of the expected range above MaxBackends. I'm not sure why this hasn't caused problems with multixact.c's use of this function. Is this a known bug? Would it be helpful to submit a patch? Regards, Robert Ross -- 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] Inserting heap tuples in bulk in COPY
On Tue, Aug 7, 2012 at 1:52 PM, Simon Riggs si...@2ndquadrant.com wrote: On 7 August 2012 20:58, Jeff Janes jeff.ja...@gmail.com wrote: Hi Heikki, Is the bulk index insert still an active area for you? If not, is there some kind of summary of design or analysis work already done, which would be a useful for someone else interested in picking it up? The main cost comes from repeated re-seeking down the index tree to find the insertion point, but repeated lock and pin operations on index buffers are also high. That is optimisable if the index inserts are grouped, as they will be with monotonic indexes, (e.g. SERIAL), or with partial monotonic (i.e. with Parent/Child relationship, on Child table many inserts will be of the form (x,1), (x,2), (x, 3) etc, e.g. Order/OrderLine). All we need do is buffer the inserts in the inserts, before inserting them into the main index. As long as we flush the buffer before end of transaction, we're good. Incidentally, we can also optimise repeated inserts within a normal transaction using this method, by implementing deferred unique constraints. At present we say that unique constraints aren't deferrable, but there's no reason they can't be, if we allow buffering in the index. (Implementing a deferred constraint that won't fail if we do UPDATE foo SET pk = pk+1 requires a buffer the size of foo, which is probably a bad plan, plus you'd need to sort the inputs, so that particular nut is another issue altogether, AFAICS). Suggested implementation is to buffer index tuples at the generic index API, then implement a bulk insert index API call that can then be implemented for each AM separately. Suggested buffer size is work_mem. Note we must extract index tuple from heap tuples - refetching heap blocks to get rows is too costly. OK, thanks for the summary. It looks like your plans are to improve the case where the index maintenance is already CPU limited. I was more interested in cases where the regions of the index(es) undergoing active insertion do not fit into usable RAM, so the limit is the random IO needed to fetch the leaf pages in order to update them or to write them out once dirtied. Here too buffering is probably the answer, but the size of the buffer needed to turn that IO from effectively random to effectively sequential is probably much larger than the size of the buffer you are contemplating. I think we need to implement buffering both to end of statement or end of transaction, not just one or the other. With the planner deciding which would be better, or explicit user action? Thanks, Jeff -- 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] WIP: pg_pretty_query
2012/8/7 Tom Lane t...@sss.pgh.pa.us: Andrew Dunstan and...@dunslane.net writes: On 08/07/2012 02:14 PM, Tom Lane wrote: In short, the only redeeming value of this patch is that it's short. One of the challenges is to have a pretty printer that is kept in sync with the dialect that's supported. Anything that doesn't use the backend's parser seems to me to be guaranteed to get out of sync very quickly. Sure. I think if we wanted an actually engineered solution, rather than a half-baked one, ecpg provides a good source of inspiration. One could imagine a standalone program that reads a query on stdin and emits a pretty-printed version to stdout, using a parser that is automatically generated from the backend's grammar with much the same technology used in recent ecpg releases. I think that would address most of the complaints I raised: it would be relatively painless to make use of from contexts that don't have a live database connection, it wouldn't impose any constraints related to having suitable database content available, it wouldn't apply any of the multitude of implementation-dependent transformations that the backend's parser does, and it could be built (I think) to do something more with comments than just throw them away. +1 it is better, and it is allow more space for possible styling 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
Re: [HACKERS] WIP: pg_pretty_query
2012/8/7 Peter Geoghegan pe...@2ndquadrant.com: On 7 August 2012 20:01, Andrew Dunstan and...@dunslane.net wrote: On 08/07/2012 02:14 PM, Tom Lane wrote: * As per some of the complaints already registered in this thread, ruleutils.c is not designed with the goal of being a pretty-printer. Its primary charter is to support pg_dump by regurgitating rules/views in an unambiguous form, which does not necessarily look very similar to what was entered. An example of a transformation that probably nobody would want in a typical pretty-printing context is expansion of SELECT * lists. But again, there is really no way to turn that off. Another aspect that seems pretty undesirable for pretty-printing is loss of any comments embedded in the query text. I'm very much not in favor of trying to make ruleutils serve two masters, but that's the game we will be playing if we accept this patch. +1. A pretty-printer that makes the query to be cleaned-up actually undergo parse-analysis seems misconceived to me. This is a tool that begs to be written in something like Python, as a satellite project, with much greater flexibility in the format of the SQL that it outputs. One of the challenges is to have a pretty printer that is kept in sync with the dialect that's supported. Anything that doesn't use the backend's parser seems to me to be guaranteed to get out of sync very quickly. I'm not convinced of that. Consider the example of cscope, a popular tool for browsing C code. Its parser was written to be fuzzy, so it's actually perfectly usable for C++ and Java, even though that isn't actually supported, IIRC. Now, I'll grant you that that isn't a perfectly analogous situation, but it is similar in some ways. If we add a new clause to select and that bit is generically pretty-printed, is that really so bad? I have a hard time believing that a well written fuzzy pretty-printer for Postgres wouldn't deliver the vast majority of the benefits of an in-core approach, at a small fraction of the effort. You'd also be able to pretty-print plpgsql function definitions (a particularly compelling case for this kind of tool), which any approach based on the backends grammar will never be able to do (except, perhaps, if you were to do something even more complicated). I disagree - simply pretty printer based on technique that we know from ecpg can be very cheep and it cannot be fuzzy because it integrate PostgreSQL parser. Pavel -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
