[HACKERS] [PATCH] explain tup_fetched/returned in monitoring-stats
I'm making some changes to a program that, among other things, reports tup_fetched/tup_returned as if it were a cache hit rate, analogous to blks_hit/blks_fetched. The documentation didn't help me to understand if that was appropriate, so I looked at the source and asked on IRC. It seems I'm not the first person to be confused by these descriptions, so here's a tiny patch to clarify the meaning of fetched and returned. -- Abhijit diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 39ccfbb..ed42ce8 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -793,12 +793,12 @@ postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re row entrystructfieldtup_returned//entry entrytypebigint//entry - entryNumber of rows returned by queries in this database/entry + entryNumber of rows returned by sequential scans in this database/entry /row row entrystructfieldtup_fetched//entry entrytypebigint//entry - entryNumber of rows fetched by queries in this database/entry + entryNumber of rows fetched by index scans in this database/entry /row row entrystructfieldtup_inserted//entry -- 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] Switching timeline over streaming replication
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Amit Kapila Sent: Wednesday, October 10, 2012 7:57 PM To: 'Heikki Linnakangas' Cc: 'PostgreSQL-development' Subject: Re: [HACKERS] Switching timeline over streaming replication On Tuesday, October 09, 2012 10:32 PM Heikki Linnakangas wrote: On 06.10.2012 15:58, Amit Kapila wrote: One more test seems to be failed. Apart from this, other tests are passed. It seems there is one more defect, please check the same Defect: The test is finished from myside. one more issue: Steps to reproduce the defect: 1. Do initdb 2. Set port=2303, wal_level=hot_standby, hot_standby=off, max_walsenders=3 in the postgresql.conf file 3. Enable the replication connection in pg_hba.conf 4. Start the server. Executing the following commands is leading failure. ./pg_basebackup -P -D ../../data_sub -X fetch -p 2303 pg_basebackup: COPY stream ended before last file was finished rm -fr ../../data_sub ./pg_basebackup -P -D ../../data_sub -X fetch -p 2303 pg_basebackup: COPY stream ended before last file was finished The following logs are observed in the server console. ERROR: requested WAL segment 0002 has already been removed ERROR: requested WAL segment 0003 has already been removed With Regards, Amit Kapila. -- 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] Deprecating RULES
On 11 October 2012 23:59, Josh Berkus j...@agliodbs.com wrote: With the DDL trigger, we're able to do that faster. The idea is you can still delete it if you need compatibility, so we get the message across without an extra release and without an annoying GUC (etc). You're seeing these things as bugs. I see them as features. And we don't need a GUC if you can't turn the warning off. I'm also not real keen on the idea that someone could dump a 9.2 database and be unable to load it into 9.3 because of the DDL trigger, especially if they might not encounter it until halfway through a restore. That seems rather user-hostile to me. I don't think you're listening, none of those things are problems and so not user hostile. I've proposed a trigger that is there by default but which is *removable*. So you can turn it off, and yet there is no GUC. Also, how would you picture that working with pg_upgrade? If RULEs are in use, we automatically delete the trigger. RULEs are a major feature we've had for over a decade. We've discussed deprecating them on -hackers, but believe it or don't, most of our users don't read -hackers. We need to warn people, loudly and repeatedly, for at *least* a year and a half before removing RULEs. That is exactly what I proposed. -- 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] Deprecating RULES
On 12 October 2012 01:52, Andrew Dunstan and...@dunslane.net wrote: I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I've never caused nor argued in favour of hardcoded changes that catch users. This would be a documented change and one that is alterable, should the user wish. So your comments don't apply. -- 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] Deprecating RULES
On 12 October 2012 00:45, Peter Geoghegan pe...@2ndquadrant.com wrote: On 11 October 2012 20:28, Simon Riggs si...@2ndquadrant.com wrote: Not many RULE-lovers out there, once you've tried to use them. Allowing RULEs complicates various things and can make security more difficult. What exactly do they make more difficult? Specifically with regard to security, they allow transparent modification of DML in ways that are not visible to people performing audits on SQL standard conforming databases. The principle of removing unused features applies here. For 9.3, I suggest we create a DDL trigger by default which prevents RULEs and throws an ERROR that explains they are now deprecated. Anybody that really cares can delete this and use them. Sometime in future, we hard code it, barring complaints. Well, rules have been around since the Berkeley days [1]. I don't think that anyone, including Tom, is willing to argue that user-defined rules are not a tar-pit (except perhaps ON SELECT DO INSTEAD SELECT rules - which are exactly equivalent to views anyway). Personally, I'd like to see them removed too. However, in order to be able to get behind your proposal, I'd like to see a reasonably developed cost/benefit analysis. People do use user-defined rules. For example, the xTuple open source ERP package uses ON INSERT DO INSTEAD rules [2]. [1] http://db.cs.berkeley.edu/papers/ERL-M89-82.pdf [2] http://www.xtuple.org/ApiDevelopment AFAICS all RULEs can be re-expressed as Triggers or Views. Perhaps the right way to do this is to supply a package that allows appropriate Triggers to be generated from Rule definitions, allowing us to cope with the few uses out there in the wild. That is more work and frankly, I don't object to people who use rules, I just object to new people being told they are useful when they aren't. As regards cost/benefit analysis, this is a low importance feature, but then that is why I proposed a low effort fix that is flexible to the needs of users affected. -- 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] Measure Theoretic Data Types in Postgresql
On 11.10.2012 07:37, Aaron Sheldon wrote:
This would allow for a succinct syntax to do calculations such as
finding the daily unique patient count given the intervals of their
attendance in particular programs; a computation I encounter
routinely as a statistician for a health services provider.
Hmm. It's easy to get the count of unique patients on a particular date
with something like:
select count(distinct patient) from attendance where interval
'2012-10-12'::date
I guess what you're after is to get that count for a range of days, in
one query, so that the result looks something like this:
date| patients
---+
2012-10-05 | 20
2012-10-06 | 24
2012-10-07 | 30
2012-10-08 | 29
The way I think of that problem is that you need to join the dates
you're interested in with the attendance table.
select date, count (distinct patientid)
from attendance
inner join (
select '2012-10-04'::date + a AS date from generate_series(1,20) a
) dates on interval @ date
group by date;
date| count
+---
2012-10-05 |11
2012-10-06 |27
2012-10-07 |47
2012-10-08 |63
2012-10-09 |83
2012-10-10 |95
2012-10-11 |80
2012-10-12 |60
2012-10-13 |35
2012-10-14 |13
(10 rows)
I created the test table for that with:
create table attendance (patientid int4 , interval daterange)
insert into attendance select id, daterange('2012-10-05'::date +
(random()*5)::int4, '2012-10-10'::date + (random()*5)::int4) from
generate_series(1,100) id;
So, I think the current range types already cover that use case pretty
well. I can't imagine how the proposed measure theoretic concepts would
make that simpler. Can you give some more complicated problem, perhaps,
that the proposed measure theoretic concepts would make simpler than the
current tools?
- Heikki
--
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] Deprecating RULES
On 10/12/2012 05:47 AM, David Johnston wrote: -Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Andrew Dunstan Sent: Thursday, October 11, 2012 8:52 PM To: Daniel Farina Cc: Joshua D. Drake; Josh Berkus; Simon Riggs; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Deprecating RULES On 10/11/2012 08:20 PM, Daniel Farina wrote: On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake j...@commandprompt.com wrote: On 10/11/2012 03:59 PM, Josh Berkus wrote: I'm also not real keen on the idea that someone could dump a 9.2 database and be unable to load it into 9.3 because of the DDL trigger, especially if they might not encounter it until halfway through a restore. That seems rather user-hostile to me. Also, how would you picture that working with pg_upgrade? RULEs are a major feature we've had for over a decade. That nobody in the right mind would use in production for YEARS. That said there is a very real problem here. For a very, very long time the recommended way (wrong way in fact) to do partitioning was based on rules. Now, those in the know immediately said, WTF but I bet you that a lot of people that we don't know about are using rules for partitioning. We definitely need a warning period that this is going away. That said, I don't know that we need a whole release cycle. If we start announcing now (or before the new year) that in 9.3 we will not have rules, that gives people 9-10 months to deal with the issue and that is assuming that we are dealing with early adopters, which we aren't because early adopters are not going to be using rules. My experience suggests that only ample annoyance for at least one full release cycle will provide a low-impact switch. This annoyance must not be able to be turned off. Spot on. All our experience is that just announcing things, especially in places other than release notes and similar, is ineffective as a way of communicating with our user base. I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I still come across uses of rules in the wild, and not just for partitioning either. Personally I think if we start now the earliest we should even consider removing the support is 9.4. cheers andrew Deprecation means that existing code will no longer work without refactoring. If CREATE RULE was a security hazard or unstable that may justify such an action but simply because using it properly (or at least safely) is difficult doesn't mean that those who have managed should be punished for their expertise. Late night rambling here but the risk mitigation that we seem to be caring about is new users searching for and using algorithms that they find on the web without understanding the intricacies of how those algorithms work. Do we really want to build something into the database to deal with this (by disallowing it outright) or do we do our best to provide authoritative and useful documentation so that when users go looking for the CREATE RULE command in our documentation they are provided with reasoning and alternatives to its use? RULEs may be difficult but maybe there are some rare use-cases where they would be appropriate. I have missed SELECT rules on real tables a few times, but I do not remember that there was much outcry when they were disabled. On the other hand, I do not miss them much, as my experience with rules in general is that they are brittle - even if you can get them to work for your current use, there is no guarantee that they keep working after changes to queries or table structure. So restricting the SELECT rules to what can be defined in CREATE VIEW seems a good compromise. You can (in theory) train yourself in proper use of foot-guns, but you can never be absolutely sure that you have reached the appropriate level for using them in real-life situations where a broken foot is not desirable. No one here is all-knowing and just maybe someone in the future will have an idea and decide to further improve them or at the least recognize a situation where the current implementation is useful. So, what actual harms are there to using CREATE RULE and are there less invasive means, via a more nuanced restriction implementation of CREATE RULE or simply via documentation, to mitigate those harms? Maybe there would not be enough benefits to CREATE RULE at this point in time to consider implementing in from scratch but given that it already exists it should be worth some effort to keep it functioning even if only for forward-compatibility reasons. And regardless, the whole what do you use instead of CREATE RULE documentation needs to be created no matter the eventual decision to fully remove the feature from the system. David J. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:
Re: [HACKERS] Deprecating RULES
On 10/12/2012 08:47 AM, Simon Riggs wrote: On 12 October 2012 01:52, Andrew Dunstan and...@dunslane.net wrote: I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I've never caused nor argued in favour of hardcoded changes that catch users. This would be a documented change and one that is alterable, should the user wish. So your comments don't apply. Nah! You already surprised Andrew by proposing to remove rules ;) Hannu Krosing -- 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] Deprecating RULES
On Thu, Oct 11, 2012 at 11:55 PM, Simon Riggs si...@2ndquadrant.com wrote: As regards cost/benefit analysis, this is a low importance feature, but then that is why I proposed a low effort fix that is flexible to the needs of users affected. Is there any feature that is more loathed and more narrowly used than rules? What about hash indexes? It is suspicious if we cannot identify even one feature that is more pain than gain to target for removal. In any case, I think it's important to keep an open mind to planning deprecations, and I say that as someone who is directly and hugely negatively impacted by deprecated features -- however, it is important to do them slowly. I think the choice of rules was a pretty credible one to bring up for consideration. The most sensible conservative deprecation plan I can think of sense is to only remove the feature when no release under project support claims to support -- without deprecation warning -- that feature. So, all in all, a four year deprecation window. I think this makes sense for features that are not in urgent need of deprecation but chip away at time spent serving defects or making them work with more desirable features. Because of this long pipeline in ideal cases, there is some benefit to starting in advance before everyone gets fed up and wants it removed Real Soon. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] velog + vereport?
Hi,
When writing code which should be able to run inside and outside a normal
backend environment its sometimes useful to be able add a wrapper arround
elog/ereport for when executing inside the backend.
Currently that requires relatively ugly macro surgery and/or recompiling the
file. I suggest adding velog/vereport or elog_va/vereport_va to make such
wrappers easier (still not easy though).
The aim would be able to do something like:
#define my_elog \
elog_wrapper_start(__FILE__, __LINE__, PG_FUNCNAME_MACRO), \
elog_wrapper_finish
And then
void
elog_wrapper_finish(int elevel, const char *fmt, ..)
{
va_list args;
va_start(args);
velog(elevel, fmt, args);
va_end(args);
}
when inside the backend
And something like:
void elog_wrapper_finish(int elevel, const char *fmt, ..)
{
va_list args;
fprintf(stderr, ..., my_elog_file, my_elog_line, my_elog_func);
va_start(args);
vfprintf(stderr, fprintf, args);
va_end(args);
}
Comments? Better idea (please!)?
This would be easier if we had vararg macros, but I don't see me winning that
argument :P
Greetings,
Andres
--
Andres Freund 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] Deprecating RULES
On Friday, October 12, 2012 01:45:56 AM Peter Geoghegan wrote: On 11 October 2012 20:28, Simon Riggs si...@2ndquadrant.com wrote: Not many RULE-lovers out there, once you've tried to use them. Allowing RULEs complicates various things and can make security more difficult. What exactly do they make more difficult? Are you particularly concerned with the overhead that rules impose when developing certain types of features? If so, since there's going to be a legacy compatibility mode for a long time, I don't know that deprecating rules will buy you much in the next 3 - 4 releases. For 9.3, I suggest we create a DDL trigger by default which prevents RULEs and throws an ERROR that explains they are now deprecated. Anybody that really cares can delete this and use them. Sometime in future, we hard code it, barring complaints. Well, rules have been around since the Berkeley days [1]. I don't think that anyone, including Tom, is willing to argue that user-defined rules are not a tar-pit (except perhaps ON SELECT DO INSTEAD SELECT rules - which are exactly equivalent to views anyway). Personally, I'd like to see them removed too. However, in order to be able to get behind your proposal, I'd like to see a reasonably developed cost/benefit analysis. People do use user-defined rules. For example, the xTuple open source ERP package uses ON INSERT DO INSTEAD rules [2]. [2] http://www.xtuple.org/ApiDevelopment And *drumroll*, they are broken. Every file in https://postbooks.svn.sourceforge.net/svnroot/postbooks/xtupleserver/trunk/dbscripts/api/views/ seems to have multiple evaluation dangers in the rules. Andres -- Andres Freund 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] velog + vereport?
Andres Freund and...@2ndquadrant.com writes: When writing code which should be able to run inside and outside a normal backend environment its sometimes useful to be able add a wrapper arround elog/ereport for when executing inside the backend. Currently that requires relatively ugly macro surgery and/or recompiling the file. I suggest adding velog/vereport or elog_va/vereport_va to make such wrappers easier (still not easy though). Um ... and that accomplishes what? You wouldn't have velog/vereport outside the backend either. If you were going to clone those in some form in the external environment, you might as well clone the existing elog infrastructure functions. 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] velog + vereport?
On Friday, October 12, 2012 02:48:42 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: When writing code which should be able to run inside and outside a normal backend environment its sometimes useful to be able add a wrapper arround elog/ereport for when executing inside the backend. Currently that requires relatively ugly macro surgery and/or recompiling the file. I suggest adding velog/vereport or elog_va/vereport_va to make such wrappers easier (still not easy though). Um ... and that accomplishes what? You wouldn't have velog/vereport outside the backend either. If you were going to clone those in some form in the external environment, you might as well clone the existing elog infrastructure functions. The advantage is that if you something velog-ish you can have a function which accepts vararg arguments and forwards them. E.g. xlogreader-error(ERROR, ..., argument, argument); Which is a callback using vfprintf(stderr) if running standalone or a callback calling vereport(). (obviously you need some more magick to also forward file/line/function, but its not too hard) Greetings, Andres -- Andres Freund 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] velog + vereport?
Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 02:48:42 PM Tom Lane wrote: Um ... and that accomplishes what? You wouldn't have velog/vereport outside the backend either. If you were going to clone those in some form in the external environment, you might as well clone the existing elog infrastructure functions. The advantage is that if you something velog-ish you can have a function which accepts vararg arguments and forwards them. E.g. xlogreader-error(ERROR, ..., argument, argument); Meh. I can't get excited about that, but in any case, that looks like it would only justify a varargs version of errmsg(), not the entire ereport infrastructure. 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] Deprecating RULES
On Thu, Oct 11, 2012 at 05:20:14PM -0700, Daniel Farina wrote: On Thu, Oct 11, 2012 at 5:07 PM, Joshua D. Drake j...@commandprompt.com wrote: On 10/11/2012 03:59 PM, Josh Berkus wrote: I'm also not real keen on the idea that someone could dump a 9.2 database and be unable to load it into 9.3 because of the DDL trigger, especially if they might not encounter it until halfway through a restore. That seems rather user-hostile to me. Also, how would you picture that working with pg_upgrade? RULEs are a major feature we've had for over a decade. That nobody in the right mind would use in production for YEARS. That said there is a very real problem here. For a very, very long time the recommended way (wrong way in fact) to do partitioning was based on rules. Now, those in the know immediately said, WTF but I bet you that a lot of people that we don't know about are using rules for partitioning. We definitely need a warning period that this is going away. That said, I don't know that we need a whole release cycle. If we start announcing now (or before the new year) that in 9.3 we will not have rules, that gives people 9-10 months to deal with the issue and that is assuming that we are dealing with early adopters, which we aren't because early adopters are not going to be using rules. My experience suggests that only ample annoyance for at least one full release cycle will provide a low-impact switch. This annoyance must not be able to be turned off. As a reminder, standard_conforming_strings and escape_string_warning were added in PG 8.1 (2005-11-08), and we defaulted standard_conforming_strings to 'on' in PG 9.1 (2011-09-12). The reason it took six years to change the default to treat backslashes literally is because the change could produce incorrect results. In the RULE case, it will just cause an error when the rule is created, and hopefully someone will see that. However, there will be no error when someone issues a query and the rule is _missing_ because it threw an error on rule creation. -- 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] line type
Peter Eisentraut pete...@gmx.net writes: What's the deal with the line type? It's installed in the catalogs and listed in the documentation, varyingly as not implemented or not fully implemented, but all the support functions throw an error. Is there any known list of things that would need to be done to make it fully implemented? Or should we just get rid of it? Tom Lockhart seems to have lobotomized line_in and line_out in commit 402b47cffafcbbc66af8ee6b6340b4db09f00a7b. A look in the archives suggests it was because of my complaint the day before: http://archives.postgresql.org/pgsql-hackers/1998-08/msg00346.php I'm not sure there's anything wrong with line_in, but line_out is still obviously broken. However, there are a couple dozen other functions taking/returning line, and in general those don't look significantly more bogus than any other part of geo_ops.c. I do notice this comment above line_in: **Lines are not intended to be used as ADTs per se, **but their ops are useful tools for other ADT ops. Thus, **there are few relops. but that argument seems specious from here. Even if it's mostly a support type, being able to get at it would be useful for testing/debugging the other operations, I think. I'd vote for fixing the I/O functions and adding some docs and regression tests ... but on the other hand, I'm not volunteering to do the work. 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] velog + vereport?
On Friday, October 12, 2012 04:59:39 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 02:48:42 PM Tom Lane wrote: Um ... and that accomplishes what? You wouldn't have velog/vereport outside the backend either. If you were going to clone those in some form in the external environment, you might as well clone the existing elog infrastructure functions. The advantage is that if you something velog-ish you can have a function which accepts vararg arguments and forwards them. E.g. xlogreader-error(ERROR, ..., argument, argument); Meh. I can't get excited about that, but in any case, that looks like it would only justify a varargs version of errmsg(), not the entire ereport infrastructure. Yes, that sounds good enough. Are you vetoing that idea (in that case I won't pursue it) or just aren't excited about it? Andres -- Andres Freund 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] velog + vereport?
Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 04:59:39 PM Tom Lane wrote: Meh. I can't get excited about that, but in any case, that looks like it would only justify a varargs version of errmsg(), not the entire ereport infrastructure. Yes, that sounds good enough. Are you vetoing that idea (in that case I won't pursue it) or just aren't excited about it? Well, I'm not excited about adding more elog.c infrastructure in advance of having a use-case in the core code --- how would we know if it got broken? That's not meant as an absolute veto, but I'm not terribly comfortable about adding code speculatively. 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] Truncate if exists
On 10/11/2012 09:22 PM, Simon Riggs wrote:
That is a lot more typing and it's not exactly intuitive. One obvious
thing that would help is a function pg_table_exists(text) that would
return true or false. But even with that there's a lot of syntactic
sugar in there that is less than ideal: begin/end, dollar-quoting, do.
Whatever becomes of this particular patch, I think we'd make a lot of
people really happy if we could find a way to dispense with some of
that stuff in simple cases.
Yeh, definitely.
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.
And you write
SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
and we can even get rid of all that other DDL crud that's been added
and we can have pg_if_table_not_exists() also.
If we can do something like :
SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE
TABLE foo, bar, foobar')) ;
or
SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo',
'bar') ;
I say yes !
--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media
--
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] velog + vereport?
On Friday, October 12, 2012 06:02:44 PM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Friday, October 12, 2012 04:59:39 PM Tom Lane wrote: Meh. I can't get excited about that, but in any case, that looks like it would only justify a varargs version of errmsg(), not the entire ereport infrastructure. Yes, that sounds good enough. Are you vetoing that idea (in that case I won't pursue it) or just aren't excited about it? Well, I'm not excited about adding more elog.c infrastructure in advance of having a use-case in the core code --- how would we know if it got broken? That's not meant as an absolute veto, but I'm not terribly comfortable about adding code speculatively. Oh, thats fine. I will submit it together with the code thats using it (next xlogreader draft). Andres -- Andres Freund 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] Truncate if exists
On 10/12/2012 12:03 PM, Sébastien Lardière wrote:
If we can do something like :
SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE
TABLE foo, bar, foobar')) ;
or
SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo',
'bar') ;
I say yes !
This strikes me as just highly un-SQL-like.
Someone could fairly easily write it for themselves in Plpgsql or C, but
it doesn't seem to me like something we should be doing.
I tend to agree with Noah's comment upthread:
But the syntax is a bandage for raw psql input remaining a hostile
environment for implementing the full range of schema changes. Switch to
submitting your SQL from a richer programming environment, and these additions
to core syntax cease to add much.
I think this goes a fortiori for Heath Robinson-like devices such as this.
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] Truncate if exists
Hello
2012/10/12 Sébastien Lardière slardi...@hi-media.com:
On 10/11/2012 09:22 PM, Simon Riggs wrote:
That is a lot more typing and it's not exactly intuitive. One obvious
thing that would help is a function pg_table_exists(text) that would
return true or false. But even with that there's a lot of syntactic
sugar in there that is less than ideal: begin/end, dollar-quoting, do.
Whatever becomes of this particular patch, I think we'd make a lot of
people really happy if we could find a way to dispense with some of
that stuff in simple cases.
Yeh, definitely.
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.
And you write
SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
and we can even get rid of all that other DDL crud that's been added
and we can have pg_if_table_not_exists() also.
If we can do something like :
SELECT pg_if_table_exists('bar' , pg_if_table_exists('foo', 'TRUNCATE
TABLE foo, bar, foobar')) ;
or
SELECT pg_if_tables_exists('TRUNCATE TABLE foo, bar, foobar', 'foo',
'bar') ;
I say yes !
I don't like it in core - it can be used for SQL injection - it is dynamic SQL.
Regards
Pavel
--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media
--
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] Truncate if exists
Andrew Dunstan and...@dunslane.net writes: This strikes me as just highly un-SQL-like. +1 I tend to agree with Noah's comment upthread: But the syntax is a bandage for raw psql input remaining a hostile environment for implementing the full range of schema changes. Switch to submitting your SQL from a richer programming environment, and these additions to core syntax cease to add much. I think the comment is generally true, but fails in the face of the simplicity of this particular grammar addition. Also, I have the same problem as Sébastien to understand Tom's usage example. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Deparsing DDL command strings
Dimitri Fontaine dimi...@2ndquadrant.fr writes:
I'll show some examples of very involved command (CREATE and ALTER TABLE
are the most complex we have I think) and some very simple commands
(DROP TABLE is one of the simplest), so that we can make up our minds on
that angle.
So please find attached a demo patch to show up what it takes to deparse
complex command strings, and here's inline some example of why that's a
good idea to actually deparse them rather than hand out whatever the
user typed in:
\dy
List of event triggers
-[ RECORD 1 ]--
Name | regress_event_trigger_trace
Event | ddl_command_trace
Owner | dim
Enabled | enabled
Procedure | test_event_trigger
Tags |
foo=# drop table foo;
NOTICE: test_event_trigger: ddl_command_start DROP TABLE
NOTICE: test_event_trigger: DROP, TABLE
NOTICE: test_event_trigger: DROP TABLE public.foo RESTRICT;
DROP TABLE
foo=# create table foo(id serial primary key,
f2 text default 'plop' check (f2 != ''));
NOTICE: test_event_trigger: ddl_command_end CREATE TABLE
NOTICE: test_event_trigger: CREATE, TABLE
NOTICE: test_event_trigger: CREATE TABLE public.foo (id integer PRIMARY
KEY DEFAULT nextval('foo_id_seq'::regclass) NOT NULL, f2 text DEFAULT 'plop'
CHECK ((f2 ''::text)), CHECK ((f2 ''::text)));
CREATE TABLE
The user of that command string still has to know what to look for and
maybe should include a proper SQL parser, but at least it doesn't need
to do much guesswork about how the serial attached sequence will get
named by the system and such oddities.
The attached patch also includes support for the complete ALTER TABLE
command and some more (CREATE SEQUENCE, CREATE EXTENSION).
Doing the same thing at ddl_command_end would allow us have all the
information we need and leave nothing to magic guesses: full schema
qualification of all objects involved, main object(s) OIDs available,
all the jazz.
That's what is happening now in the attached patch, also with a new
event called 'ddl_command_trace' which will either map to _start or _end
depending on the operation (we want _start when doing DROP TABLE, we
want the operation to be complete before tracing it when talking about a
CREATE or an ALTER table).
And here's the scope we're talking about, including new command types,
new information passed down to user triggers, and the rewrite support
itself, isolated away:
git diff --stat postgres/master..
src/backend/catalog/heap.c |5 +-
src/backend/commands/event_trigger.c| 241 -
src/backend/tcop/utility.c | 187 ++--
src/backend/utils/adt/Makefile |2 +-
src/backend/utils/adt/ddl_rewrite.c | 1415
+++
src/backend/utils/adt/ruleutils.c |9 +-
src/backend/utils/cache/evtcache.c |4 +
src/include/catalog/heap.h |4 +
src/include/commands/event_trigger.h| 43 +-
src/include/utils/builtins.h| 14 +
src/include/utils/evtcache.h|4 +-
src/pl/plpgsql/src/pl_comp.c| 40 +
src/pl/plpgsql/src/pl_exec.c| 53 +-
src/pl/plpgsql/src/plpgsql.h|5 +
src/test/regress/expected/event_trigger.out | 40 +-
src/test/regress/sql/event_trigger.sql | 36 +-
16 files changed, 1938 insertions(+), 164 deletions(-)
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
ddl_rewrite.1.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] Deprecating RULES
I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very understanding group of users. Lemme try to make it clear to you exactly how user-hostile you're being: 1. User downloads 9.2 today. 2. User builds a new application. 3. User finds the doc page on RULEs, decides they're a nifty concept. 4. New application includes some RULEs. 5. 9.3 comes out. 6. User schedules a downtime for upgrading. 7. In the middle of the upgrade, at 2am, they get a cryptic warning, and dump/restore fails. 8. User has to rollback the upgrade. 9. User googles a bunch, eventually finds information on the trigger. 10. User realizes that a bunch of their code, written not 6 months before, needs to be refactored now. 11. User switches to MongoDB in disgust. I realize you weren't around when we removed row OIDs, but I was *still* getting flack from that in 2008. And we lost entire OSS projects to other databases because of removing row OIDs. And those were marked deprecated for 3 years before we removed them. That is exactly what I proposed. No, it's not. You proposed inserting a SURPRISE! break-your-application trigger in 9.3 ... 10 months from now. With zero warning to our general user base. -- Josh Berkus PostgreSQL Experts Inc. http://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] [PATCH] explain tup_fetched/returned in monitoring-stats
Abhijit Menon-Sen a...@2ndquadrant.com writes: I'm making some changes to a program that, among other things, reports tup_fetched/tup_returned as if it were a cache hit rate, analogous to blks_hit/blks_fetched. The documentation didn't help me to understand if that was appropriate, so I looked at the source and asked on IRC. It seems I'm not the first person to be confused by these descriptions, so here's a tiny patch to clarify the meaning of fetched and returned. It may be tiny, but I don't believe it's correct. t_tuples_returned for instance is incremented by both pgstat_count_heap_getnext() (ie, successful returns from heap_getnext()) and pgstat_count_index_tuples() (which counts heap TIDs returned from either index_getnext_tid or index_getbitmap). t_tuples_fetched is incremented by pgstat_count_heap_fetch(), which is called in heap_fetch and index_fetch_heap. Right at the moment it's not obvious to me that these calls are usefully placed. My intuitive understanding of fetched vs returned is that the former should count physical tuple visits while the latter should count tuples that satisfied some qual or other. It doesn't seem like the implementation actually squares with that. But in any case, indexscan vs heapscan is a completely wrong description of the difference. 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] Measure Theoretic Data Types in Postgresql
On 10/12/12 12:48 AM, Heikki Linnakangas wrote: So, I think the current range types already cover that use case pretty well. I can't imagine how the proposed measure theoretic concepts would make that simpler. Can you give some more complicated problem, perhaps, that the proposed measure theoretic concepts would make simpler than the current tools? Well, the nice thing about EXTENSIONs is that, if he builds it, people can just try it out and see if it's useful. I suspect that the use cases are rarified enough that this would always be an EXTENSION and not core. One thing I could use, for example, would be a time-keyed array, in the form: metrics_series ( '2012-10-17 45:22:10',10, '1 second',15.0,15.1,16.2,NULL,15.8, 14.9, 15.1,14.2, 13.9, NULL ) WHich would allow me to do: SELECT metric WHERE ts = '2012-10-17 45:22:14' Without storing a timestamp with each element. This is not a set of functionality I would expect to be generally useful and belong in Core. But for a certain set of analytics applications, it would be indispensible. I expect that theoretic data types are the same way. -- Josh Berkus PostgreSQL Experts Inc. http://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] Measure Theoretic Data Types in Postgresql
So the key algorithmic inefficient is the inner join on the generated
series. Worst case scenario this compares every range to every date in the
series, which for m ranges and n dates yields O(m*n) operations. The
analysts in my shop currently write queries like this for billions of
records against thousands of dates and then go and take 8 hour coffee
breaks.
However, by realizing that the bounds on the ranges have a linear ordering
one can speed this up to 0(m) using windowing functions on common table
expressions.
So what I am proposing is formalizing this optimization into a class of
data types, that will hide the implementation details.
On Fri, Oct 12, 2012 at 1:48 AM, Heikki Linnakangas hlinnakan...@vmware.com
wrote:
On 11.10.2012 07:37, Aaron Sheldon wrote:
This would allow for a succinct syntax to do calculations such as
finding the daily unique patient count given the intervals of their
attendance in particular programs; a computation I encounter
routinely as a statistician for a health services provider.
Hmm. It's easy to get the count of unique patients on a particular date
with something like:
select count(distinct patient) from attendance where interval
'2012-10-12'::date
I guess what you're after is to get that count for a range of days, in one
query, so that the result looks something like this:
date| patients
---+
2012-10-05 | 20
2012-10-06 | 24
2012-10-07 | 30
2012-10-08 | 29
The way I think of that problem is that you need to join the dates you're
interested in with the attendance table.
select date, count (distinct patientid)
from attendance
inner join (
select '2012-10-04'::date + a AS date from generate_series(1,20) a
) dates on interval @ date
group by date;
date| count
+---
2012-10-05 |11
2012-10-06 |27
2012-10-07 |47
2012-10-08 |63
2012-10-09 |83
2012-10-10 |95
2012-10-11 |80
2012-10-12 |60
2012-10-13 |35
2012-10-14 |13
(10 rows)
I created the test table for that with:
create table attendance (patientid int4 , interval daterange)
insert into attendance select id, daterange('2012-10-05'::date +
(random()*5)::int4, '2012-10-10'::date + (random()*5)::int4) from
generate_series(1,100) id;
So, I think the current range types already cover that use case pretty
well. I can't imagine how the proposed measure theoretic concepts would
make that simpler. Can you give some more complicated problem, perhaps,
that the proposed measure theoretic concepts would make simpler than the
current tools?
- Heikki
--
Aaron Sheldon
#67 Westedge Townhouses
5019 46 Ave, SW
Calgary AB, T3E 6R1
(h) 1.403.453.6316
(c) 1.403.710.9357
aaron.shel...@gmail.com
Re: [HACKERS] proposal - assign result of query to psql variable
Hello here is updated version of gset patch. * merge Shigeru's doc patch * rename psql regression test from psql to psql_cmd Regards Pavel Stehule 2012/9/27 Pavel Stehule pavel.steh...@gmail.com: Hello 2012/9/21 Shigeru HANADA shigeru.han...@gmail.com: Hi Pavel, (2012/09/21 2:01), Pavel Stehule wrote: - Is it intentional that \gset can set special variables such as AUTOCOMMIT and HOST? I don't see any downside for this behavior, because \set also can do that, but it is not documented nor tested at all. I use a same SetVariable function, so a behave should be same It seems reasonable. Document - Adding some description of \gset command, especially about limitation of variable list, seems necessary. - In addition to the meta-command section, Advanced features section mentions how to set psql's variables, so we would need some mention there too. - The term target list might not be familiar to users, since it appears in only sections mentioning PG internal relatively. I think that the feature described in the section Retrieving Query Results in ECPG document is similar to this feature. http://www.postgresql.org/docs/devel/static/ecpg-variables.html I invite any proposals about enhancing documentation. Personally I am a PostgreSQL developer, so I don't known any different term other than target list - but any user friendly description is welcome. How about to say stores the query's result output into variable? Please see attached file for my proposal. I also mentioned about 1-row limit and omit of variable. should be Coding == The code follows our coding conventions. Here are comments for coding. - Some typo found in comments, please see attached patch. - There is a code path which doesn't print error message even if libpq reported error (PGRES_BAD_RESPONSE, PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR) in StoreQueryResult. Is this intentional? FYI, ecpg prints bad response message for those errors. yes - it is question. I use same pattern like PrintQueryResult, but bad response message should be used. I am sending updated patch It seems ok. BTW, as far as I see, no psql backslash command including \setenv (it was added in 9.2) has regression test in core (I mean src/test/regress). Is there any convention about this issue? If psql backslash commands (or any psql feature else) don't need regression test, we can remove psql.(sql|out). # Of course we need to test new feature by hand. It is question for Tom or David - only server side functionalities has regress tests. But result of some backslash command is verified in other regress tests. I would to see some regression tests for this functionality. Anyway, IMO the name psql impresses larger area than the patch implements. How about to rename psql to psql_cmd or backslash_cmd than psql as regression test name? I have no idea - psql_cmd is good name Regards Pavel -- Shigeru HANADA gset_06.diff 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
[HACKERS] patch: assign result of query to psql variable
-- Forwarded message -- From: Pavel Stehule pavel.steh...@gmail.com Date: 2012/10/12 Subject: Re: [HACKERS] proposal - assign result of query to psql variable To: Shigeru HANADA shigeru.han...@gmail.com Kopie: David Fetter da...@fetter.org, Tom Lane t...@sss.pgh.pa.us, PostgreSQL Hackers pgsql-hackers@postgresql.org Hello here is updated version of gset patch. * merge Shigeru's doc patch * rename psql regression test from psql to psql_cmd Regards Pavel Stehule 2012/9/27 Pavel Stehule pavel.steh...@gmail.com: Hello 2012/9/21 Shigeru HANADA shigeru.han...@gmail.com: Hi Pavel, (2012/09/21 2:01), Pavel Stehule wrote: - Is it intentional that \gset can set special variables such as AUTOCOMMIT and HOST? I don't see any downside for this behavior, because \set also can do that, but it is not documented nor tested at all. I use a same SetVariable function, so a behave should be same It seems reasonable. Document - Adding some description of \gset command, especially about limitation of variable list, seems necessary. - In addition to the meta-command section, Advanced features section mentions how to set psql's variables, so we would need some mention there too. - The term target list might not be familiar to users, since it appears in only sections mentioning PG internal relatively. I think that the feature described in the section Retrieving Query Results in ECPG document is similar to this feature. http://www.postgresql.org/docs/devel/static/ecpg-variables.html I invite any proposals about enhancing documentation. Personally I am a PostgreSQL developer, so I don't known any different term other than target list - but any user friendly description is welcome. How about to say stores the query's result output into variable? Please see attached file for my proposal. I also mentioned about 1-row limit and omit of variable. should be Coding == The code follows our coding conventions. Here are comments for coding. - Some typo found in comments, please see attached patch. - There is a code path which doesn't print error message even if libpq reported error (PGRES_BAD_RESPONSE, PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR) in StoreQueryResult. Is this intentional? FYI, ecpg prints bad response message for those errors. yes - it is question. I use same pattern like PrintQueryResult, but bad response message should be used. I am sending updated patch It seems ok. BTW, as far as I see, no psql backslash command including \setenv (it was added in 9.2) has regression test in core (I mean src/test/regress). Is there any convention about this issue? If psql backslash commands (or any psql feature else) don't need regression test, we can remove psql.(sql|out). # Of course we need to test new feature by hand. It is question for Tom or David - only server side functionalities has regress tests. But result of some backslash command is verified in other regress tests. I would to see some regression tests for this functionality. Anyway, IMO the name psql impresses larger area than the patch implements. How about to rename psql to psql_cmd or backslash_cmd than psql as regression test name? I have no idea - psql_cmd is good name Regards Pavel -- Shigeru HANADA gset_06.diff 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
[HACKERS] Adding comments for system table/column names
There was a thread in January of 2012 where we discussed the idea of pulling system table/column name descriptions from the SGML docs and creating SQL comments for them: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00837.php Magnus didn't seem to like the idea: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00848.php Well, I'd expect some of those columns to get (at least over time) significantly more detailed information than they have now. Certainly more than you'd put in comments in the catalogs. And having some sort of combination there seems to overcomplicate things... I think the idea of having the short descriptions in SQL and longer ones in SGML is not maintainable. One idea would be to clip the SQL description to be no longer than a specified number of characters, with proper word break detection. Here is how psql displays column and table comments: test= create table test(x int); CREATE TABLE test= comment on column test.x IS 'wow'; COMMENT test= \d+ test Table public.test Column | Type | Modifiers | Storage | Stats target | Description +-+---+-+--+- x | integer | | plain | | wow Has OIDs: no test= comment on table test is 'yikes'; COMMENT test= \d+ List of relations Schema | Name | Type | Owner | Size | Description +--+---+--+-+- public | test | table | postgres | 0 bytes | yikes (1 row) Should I continue working on this patch? -- 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] getopt() and strdup()
Applied. --- On Wed, Oct 10, 2012 at 07:54:15PM -0400, Bruce Momjian wrote: On Mon, Oct 8, 2012 at 09:03:37PM -0400, Bruce Momjian wrote: On Mon, Oct 8, 2012 at 04:33:29PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: A while ago I noticed that in some places we strdup/pg_strdup() optarg strings from getopt(), and in some places we don't. If we needed the strdup(), the missing cases should generate errors. If we don't need them, the strdup() is unnecessary, and research confirms they are unnecessary. Should we remove the extra strdup/pg_strdup() calls, for consistency. What research? Given the number of different ways argv[] is handled on different platforms (cf ps_status.c), I am very unwilling to trust that it's safe to hang onto an argv string for long without strdup'ing it. I think we might have had old platforms that required it, but none are still supported today. And what's your grounds for stating that? All the alternatives in ps_status.c are still live code AFAICS. My feeling is it's more likely to be a good idea to be adding strdup's than removing them. Well, what we have now is either wrong or over-kill --- I don't know for sure which. OK, I have developed the attached patch to add strdup/pg_strdup() calls to all saving of getopt optarg arguments. Also, do we want to centralize the definition of pg_strdup() in /port, or leave each module to define it on its own? I see pg_strdup() defined in these modules: /pgtop/contrib/oid2name /pgtop/contrib/pgbench /pgtop/contrib/pg_upgrade /pgtop/src/bin/initdb /pgtop/src/bin/pg_basebackup /pgtop/src/bin/pg_ctl /pgtop/src/bin/pg_dump /pgtop/src/bin/psql /pgtop/src/bin/scripts -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_archivecleanup/pg_archivecleanup.c b/contrib/pg_archivecleanup/pg_archivecleanup.c new file mode 100644 index 8f77998..e97a11c *** a/contrib/pg_archivecleanup/pg_archivecleanup.c --- b/contrib/pg_archivecleanup/pg_archivecleanup.c *** main(int argc, char **argv) *** 299,305 dryrun = true; break; case 'x': ! additional_ext = optarg;/* Extension to remove from * xlogfile names */ break; default: --- 299,305 dryrun = true; break; case 'x': ! additional_ext = strdup(optarg); /* Extension to remove from * xlogfile names */ break; default: diff --git a/contrib/pg_standby/pg_standby.c b/contrib/pg_standby/pg_standby.c new file mode 100644 index 84941ed..659bd50 *** a/contrib/pg_standby/pg_standby.c --- b/contrib/pg_standby/pg_standby.c *** main(int argc, char **argv) *** 643,649 } break; case 't': /* Trigger file */ ! triggerPath = optarg; break; case 'w': /* Max wait time */ maxwaittime = atoi(optarg); --- 643,649 } break; case 't': /* Trigger file */ ! triggerPath = strdup(optarg); break; case 'w': /* Max wait time */ maxwaittime = atoi(optarg); diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c new file mode 100644 index c399d59..5d48aee *** a/contrib/pgbench/pgbench.c --- b/contrib/pgbench/pgbench.c *** main(int argc, char **argv) *** 1995,2001 is_init_mode++; break; case 'h': ! pghost = optarg; break; case 'n': is_no_vacuum++; --- 1995,2001 is_init_mode++; break; case 'h': !
Re: [HACKERS] Measure Theoretic Data Types in Postgresql
However, by realizing that the bounds on the ranges have a linear ordering one can speed this up to 0(m) using windowing functions on common table expressions. So what I am proposing is formalizing this optimization into a class of data types, that will hide the implementation details. Could this not also be handled by extending merge join to work with an overlap operator? -- 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 mailing list
Hello I have a problem with sending patch to mailing list. It is working now? Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dumping recursive views broken in master
CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT sum(n) FROM t; dumps as CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT (t_1.n + 1) FROM t WHERE (t_1.n 100)) SELECT sum(t.n) AS sum FROM t; which doesn't load back, because of this missing FROM item t_1. Evidently, this is related to commit 11e131854f8231a21613f834c40fe9d046926387 Author: Tom Lane t...@sss.pgh.pa.us Date: Fri Sep 21 19:03:10 2012 -0400 Improve ruleutils.c's heuristics for dealing with rangetable aliases. -- 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] explain tup_fetched/returned in monitoring-stats
At 2012-10-12 13:05:44 -0400, t...@sss.pgh.pa.us wrote: t_tuples_returned for instance is incremented by both pgstat_count_heap_getnext() (ie, successful returns from heap_getnext()) and pgstat_count_index_tuples() (which counts heap TIDs returned from either index_getnext_tid or index_getbitmap). But pgstat_count_index_tuples() is called only on the index relation, right? And pgstat_count_heap_fetch() is called by index_fetch_heap on the index relation too. Earlier, I thought that pgstat_recv_tabstat() adds t_tuples_fetched and t_tuples_returned only for tables to the database stats (as the comments and variable names suggest), but it makes more sense for it to include index relations too (and the code in pgstat_initstats does imply that's what is happening). But in any case, indexscan vs heapscan is a completely wrong description of the difference. Yes. I'm sorry. Is there any concise description that applies? I think it's worth fixing, seeing that multiple competent people have got the wrong idea about what it means. -- Abhijit -- 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] Support for REINDEX CONCURRENTLY
* Jim Nasby (j...@nasby.net) wrote: Yeah, I was just trying to remember what other situations this has come up in. My recollection is that there's been a couple other cases where that would be useful. Yes, I've run into similar issues in the past also. It'd be really neat to somehow make the SnapshotNow (and I'm guessing the whole SysCache system) behave more like MVCC. My recollection is also that such a change would be rather large... but it might be smaller than all the other work-arounds that are needed because we don't have that... Perhaps.. Seems like it'd be a lot of work tho, to do it 'right', and I suspect there's a lot of skeletons out there that we'd run into.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Deprecating RULES
On 10/12/2012 06:59 PM, Josh Berkus wrote: I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very understanding group of users. Lemme try to make it clear to you exactly how user-hostile you're being: 1. User downloads 9.2 today. 2. User builds a new application. 3. User finds the doc page on RULEs, decides they're a nifty concept. 4. New application includes some RULEs. 5. 9.3 comes out. 6. User schedules a downtime for upgrading. 7. In the middle of the upgrade, at 2am, they get a cryptic warning, and dump/restore fails. 8. User has to rollback the upgrade. 9. User googles a bunch, eventually finds information on the trigger. 10. User realizes that a bunch of their code, written not 6 months before, needs to be refactored now. 11. User switches to MongoDB in disgust. Perhaps more likely p11. is User starts drinking and gets a divorce. His dog dies as a result. More seriously, if it was something that is easier to do in MongoDB, the user _should_ switch. And MongoDB does not have RULEs. I can't think of anything using RULES that would make PostgreSQL behave like MongoDB. It can be done using json/htree/xml + pl/jsv8 (or any other pl), but not RULES. I realize you weren't around when we removed row OIDs, but I was *still* getting flack from that in 2008. And we lost entire OSS projects to other databases because of removing row OIDs. I'm sure we also lost entire projects to other databases _because_ _of_ having row OIDs. And those were marked deprecated for 3 years before we removed them. That is exactly what I proposed. No, it's not. You proposed inserting a SURPRISE! break-your-application trigger in 9.3 ... 10 months from now. With zero warning to our general user base. Daniel hinted at a better approach - use a trigger which rewrites all rules to send a nagging notice at every use of the rule in addition to what they do originally. -- 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] dumping recursive views broken in master
Peter Eisentraut pete...@gmx.net writes: CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT sum(n) FROM t; dumps as CREATE VIEW sums_1_100 AS WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT (t_1.n + 1) FROM t WHERE (t_1.n 100)) SELECT sum(t.n) AS sum FROM t; which doesn't load back, because of this missing FROM item t_1. Evidently, this is related to commit 11e131854f8231a21613f834c40fe9d046926387 Author: Tom Lane t...@sss.pgh.pa.us Date: Fri Sep 21 19:03:10 2012 -0400 Improve ruleutils.c's heuristics for dealing with rangetable aliases. Oops. I'll take a look. 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] Deprecating RULES
On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs si...@2ndquadrant.com wrote: AFAICS all RULEs can be re-expressed as Triggers or Views. This is a bizarre discussion. Firstly this isn't even close to true. The whole source of people's discontentment is that triggers are *not* equivalent to rules. If they were then they wouldn't be so upset. Secondly the only reason views work is because they're implemented using rules. If you want to do anything similar but different from views you would need to use rules as well. I'm still waiting on updateable views for example. It sounds like what people are really looking for is to move the section of the manual describing rules to an internals section of the manual and add a note saying do not try to use rules to implement triggers. they are not triggers that explains how they're different and what they're useful for. In general user manuals, especially ones written like Unix man pages, tend to describe what things do without explaining why that might be useful. That's leaves users faced with a decision between trying similar-sounding features like rules and triggers and they might pick the wrong one. The Postgres manual is better than most in this respect but this is one area where it might pay to be extra clear. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fix for log_line_prefix and session display
Currently, our session id, displayed by log_line_prefix and CSV output, is made up of the session start time epoch seconds and the process id. The problem is that the printf mask is currently %lx.%x, causing a process id less than 4096 to not display a full four hex digits after the decimal point. I think this is confusing because the number .423 appears higher than .1423, though it is not. Here is what our current output looks like with log_line_prefix=%c: : 50785b3e.7ff9: ERROR: syntax error at or near test at character 1 50785b3e.7ff9: STATEMENT: test 50785b3e.144: ERROR: syntax error at or near test at character 1 50785b3e.144: STATEMENT: test With my fix, here is the updated output: 507864d3.7ff2: ERROR: syntax error at or near test at character 1 507864d3.7ff2: STATEMENT: test 507864d3.013d: ERROR: syntax error at or near test at character 1 507864d3.013d: STATEMENT: test Patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c new file mode 100644 index a40b343..68b7ab3 *** a/src/backend/utils/error/elog.c --- b/src/backend/utils/error/elog.c *** log_line_prefix(StringInfo buf, ErrorDat *** 1970,1976 } break; case 'c': ! appendStringInfo(buf, %lx.%x, (long) (MyStartTime), MyProcPid); break; case 'p': appendStringInfo(buf, %d, MyProcPid); --- 1970,1976 } break; case 'c': ! appendStringInfo(buf, %lx.%04x, (long) (MyStartTime), MyProcPid); break; case 'p': appendStringInfo(buf, %d, MyProcPid); *** write_csvlog(ErrorData *edata) *** 2149,2155 appendStringInfoChar(buf, ','); /* session id */ ! appendStringInfo(buf, %lx.%x, (long) MyStartTime, MyProcPid); appendStringInfoChar(buf, ','); /* Line number */ --- 2149,2155 appendStringInfoChar(buf, ','); /* session id */ ! appendStringInfo(buf, %lx.%04x, (long) MyStartTime, MyProcPid); appendStringInfoChar(buf, ','); /* Line number */ -- 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] Deprecating RULES
Josh Berkus wrote: I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very understanding group of users. Lemme try to make it clear to you exactly how user-hostile you're being: 1. User downloads 9.2 today. 2. User builds a new application. 3. User finds the doc page on RULEs, decides they're a nifty concept. 4. New application includes some RULEs. snip I have a proposal. Assuming we decide to do away with RULEs, change the *documentation* for RULEs right away in all supported maintenance branches (including 9.2), saying that RULEs will be deprecated, but don't change any code / add any warnings until 9.3. Then, no later than the next bug/security fix minor release, 9.2.2/etc, the documentation for RULEs all says that, yes we have RULEs, but you shouldn't use them on any new projects as they are going away, and you should migrate any existing uses, and uses will warn starting in 9.3.0. This documentation change can also be highlighted in a bullet point in the 9.2.2/etc release announcements. If necessary, also make reference in the docs to some tool or procedure to help find any uses of RULEs and help with the migration. Since this isn't a code change, it should be very conservative and be safe to include in maintenance branches, and it will alert users right where they're most likely to look, the RULEs documentation, without any undue delay. How does that sound? -- Darren Duncan -- 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] Truncate if exists
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote:
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.
And you write
SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
and we can even get rid of all that other DDL crud that's been added
and we can have pg_if_table_not_exists() also.
You could make this more composable by having pg_table_exists() and
pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
you want the if-not-exists case then just stick a NOT in there. And
if you want a more complicated condition, you can easily write that as
well.
--
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] September 2012 commitfest
On Thu, Oct 11, 2012 at 3:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On 11 October 2012 20:30, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 11, 2012 at 2:42 PM, Andrew Dunstan and...@dunslane.net wrote: I have a quietish few days starting on Saturday, will be looking at this then. Is it only the Windows aspect that needs reviewing? Are we more or less happy with the rest? I think the Windows issues were the biggest thing, but I suspect there may be a few other warts as well. It's a lot of code, and it's modifying pg_dump, which is an absolute guarantee that it's built on a foundation made out of pure horse manure. That may be so, but enough people dependent upon it that now I'm wondering whether we should be looking to create a new utility altogether, or at least have pg_dump_parallel and pg_dump to avoid any screw ups with people's backups/restores. Well, I think pg_dump may well need a full rewrite to be anything like sane. But I'm not too keen about forking it as part of adding parallel dump. I think we can sanely hack this patch into what's there now. It's liable to be a bit hard to verify, but in the long run having two copies of the code is going to be a huge maintenance headache, so we should avoid that. -- 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] Truncate if exists
* Robert Haas (robertmh...@gmail.com) wrote: INSERT IF EXISTS (and, hey, why not INSERT OR CREATE for good measure?). I'm not sure what the right thing to do is... but we should probably come up with some consensus position we can all live with, and then go make this uniform[1]. 'INSERT OR CREATE' was specifically mentioned as something which would be very useful for certain development-type activities at PGOpen. I'm on the fence about it myself, but it is kind of a neat idea. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] September 2012 commitfest
On 10/12/2012 03:07 PM, Robert Haas wrote: On Thu, Oct 11, 2012 at 3:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On 11 October 2012 20:30, Robert Haas robertmh...@gmail.com wrote: On Thu, Oct 11, 2012 at 2:42 PM, Andrew Dunstan and...@dunslane.net wrote: I have a quietish few days starting on Saturday, will be looking at this then. Is it only the Windows aspect that needs reviewing? Are we more or less happy with the rest? I think the Windows issues were the biggest thing, but I suspect there may be a few other warts as well. It's a lot of code, and it's modifying pg_dump, which is an absolute guarantee that it's built on a foundation made out of pure horse manure. That may be so, but enough people dependent upon it that now I'm wondering whether we should be looking to create a new utility altogether, or at least have pg_dump_parallel and pg_dump to avoid any screw ups with people's backups/restores. Well, I think pg_dump may well need a full rewrite to be anything like sane. But I'm not too keen about forking it as part of adding parallel dump. I think we can sanely hack this patch into what's there now. It's liable to be a bit hard to verify, but in the long run having two copies of the code is going to be a huge maintenance headache, so we should avoid that. That's my feeling too. 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] Truncate if exists
* Tom Lane (t...@sss.pgh.pa.us) wrote: Second, to my mind the point of a multi-table TRUNCATE is to ensure that all the referenced tables get reset to empty *together*. With something like this, you'd have no such guarantee. Consider a timeline like this: Don't we have the exact same issue with DROP TABLE and multi-table support for it today..? Session 1 Session 2 DROP IF EXISTS a, b, c; ... finds c doesn't exist ... ... working on a and b ... CREATE TABLE c ( ... ); ... commits ... But now we have a table 'c' where we didn't expect to because we DROP'd it? If you COMMIT then you can't expect things to not have changed under you after your transaction is over, you're going to have to be ready to deal with the consequences either way.. Heck, even if your scenario, don't you have to be concerned in Session 1 that someone insert'd data into 'c' after your commit but before you open your next transaction? The TRUNCATE in a multi-table case, imv, is typically to address FK relationships. Provided we don't allow a situation where data could be stored which violates a FK due to a TRUNCATE IF EXISTS happening in some other session concurrently (which I don't think could happen, but it'd be something to verify, I suppose), the precedent of proceeding with multi-table IF EXISTS commands even in the face of a given table not existing should hold. If we don't feel that is appropriate for TRUNCATE, then I would question if we should have it for DROP TABLE- but if we don't have that semantic, what are we going to have? All tables have to either exist or not exist? Disallow IF EXISTS when a multi-table command is given? Neither strikes me as better. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Deprecating RULES
On 10/12/12 11:57 AM, Darren Duncan wrote: Assuming we decide to do away with RULEs, change the *documentation* for RULEs right away in all supported maintenance branches (including 9.2), saying that RULEs will be deprecated, but don't change any code / add any warnings until 9.3. I'd say back to the 8.3 docs, actually, but yes, that's the right idea. -- Josh Berkus PostgreSQL Experts Inc. http://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] No, pg_size_pretty(numeric) was not such a hot idea
On Wed, Oct 10, 2012 at 2:49 PM, Josh Berkus j...@agliodbs.com wrote: So, here's a complaint: 9.2 is breaking our code for checking table sizes: postgres=# select pg_size_pretty(100); ERROR: function pg_size_pretty(integer) is not unique at character 8 You know, if we implemented what Tom proposed here: http://archives.postgresql.org/pgsql-hackers/2012-08/msg01055.php ...then we probably get away with removing pg_size_pretty(bigint) and then this would Just Work. pg_size_pretty(numeric) is doubtless a little slower than pg_size_pretty(bigint), but I think in practice nobody's going to care. -- 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] Deprecating RULES
On Thu, Oct 11, 2012 at 8:52 PM, Andrew Dunstan and...@dunslane.net wrote: I'm with Tom and Josh and Daniel on this, and to be honest I'm somewhat surprised at the willingness of some people to spring surprises on users. I still come across uses of rules in the wild, and not just for partitioning either. Personally I think if we start now the earliest we should even consider removing the support is 9.4. Yeah. Actually, I think even that is far too soon. Frankly, the fact that several people here seem to think rules are still something they see regularly in the field makes me wonder if we should be entertaining this proposition at all ... but if we are, what I think we should do first is add a warning to the documentation that says don't use rules. And then after, say, two releases, we could have the CREATE RULE command throw a warning. And then after, say, two more releases, we could have it fail with an error saying, dude, not supported any more. That means we would start to warn no earlier than 9.5 and actually shut it off no earlier than 9.7. The case of standard_conforming_strings has already been discussed as a parallel. I think the case of = should also be mentioned. The SQL standards committee has standardized this as a way of calling functions with named arguments, but we have long allowed it as an operator - and in fact hstore has long shipped an operator with that name. We began warning about the use of that operator name in 9.0, and we removed the version that ships with hstore in 9.2. I can't imagine we'll actually implement the SQL standard behavior any sooner than 9.4. The thing we've got to keep in mind here is that many people upgrade more than one release at a time. We regularly have customers who will upgrade from, say, 8.2 or 8.3 all the way up to 9.1 or 9.2. Now, we can't completely cater to people who are on that kind of very long time scale or we'll never get anywhere, but cutting out a feature that isn't even deprecated today in less than a year is going to the opposite end of the spectrum. We know that rules are a bad fit for almost everything, *but we can't assume that our users all know that when it isn't even documented*. -- 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] Truncate if exists
Robert Haas escribió:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote:
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.
And you write
SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
and we can even get rid of all that other DDL crud that's been added
and we can have pg_if_table_not_exists() also.
You could make this more composable by having pg_table_exists() and
pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
you want the if-not-exists case then just stick a NOT in there. And
if you want a more complicated condition, you can easily write that as
well.
Uh, we had an execute() function of sorts in the extensions patch; that
seems to have been ripped out. Do we want it back?
--
Á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] Truncate if exists
* Josh Berkus (j...@agliodbs.com) wrote: On 10/9/12 1:35 PM, Peter Eisentraut wrote: On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? That's a very good point. I tend to think of all utility commands as DDL, which of course they're not. I don't actually see why that's, inherently, a bad idea. Nor do I see why IF EXISTS should only apply to DDL and not to all commands. Obviously, if you write 'IF EXISTS', you've got a plan to deal with the fact that it doesn't exist. In a lossy system that's using partitions, I could actually see a pretty good use-case for wanting INSERT IF EXISTS (rather than having to constantly poll, waiting for the partition table that's supposted to be created by some other process to show up). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] enhanced error fields
Hello
2012/10/11 Peter Geoghegan pe...@2ndquadrant.com:
On 10 October 2012 14:56, Pavel Stehule pavel.steh...@gmail.com wrote:
(eelog3.diff)
This looks better.
You need a better comment here:
+ * relerror.c
+ * relation error loging functions
+ *
I'm still not satisfied with the lack of firm guarantees about what
errcodes one can assume these fields will be available for. I suggest
that this be explicitly documented within errcodes.h. For example,
right now if some client code wants to discriminate against a certain
check constraint in its error-handling code (typically to provide a
user-level message), it might do something like this:
try
{
...
}
catch(CheckViolation e)
{
// This works:
if (e.constraint_name == postive_balance)
MessageBox(Your account must have a positive balance.);
// This is based on a check constraint in a domain, and is
therefore broken right now:
else if (e.constraint_name == valid_barcode)
MessageBox(You inputted an invalid barcode - check digit was
wrong);
}
This is broken right now, simply because the application cannot rely
on the constraint name being available, since for no particular reason
some of the ERRCODE_CHECK_VIOLATION ereport sites (like in execQual.c)
don't provide an errconstraint(). What is needed is a coding standard
that says ERRCODE_CHECK_VIOLATION ereport call sites need to have an
errconstraint(). Without this, the patch is quite pointless.
I understand to your request, but I don't thing so this request is
100% valid. Check violation is good example. Constraint names are
optional in PostgreSQL - so we cannot require constraint_name. One
from first prototypes I used generated name for NULL constraints and
it was rejected - because It can be confusing, because a user doesn't
find these names in catalogue. I agree with it now - it better show
nothing, than show some phantom. More - a design of these feature from
SQL/PSM and ANSI/SQL is not too strict. There is no exception, when
you asking any unfilled value - you get a empty string instead. And
more - there are no info in standard, what fields are optional and
what fields are mandatory.
And although we don't checking consistence of exception fields, I
think so this patch is very usable. I have a three text fields now:
message, detail, hint - and I can do same error, that you are
described. This patch doesn't change it. But it creates a few new
basic variables (for all possible exceptions), that can be used for
simplification of error processing. It is not silver bullet. And it is
not C++. Creating some new tool for checking consistency of exceptions
is not good way - and you are newer ensure consistency of custom
exceptions.
My mind is not 100% closed to the idea that we provide these extra
fields on a best-effort basis per errcode, but it's pretty close to
there. Why should we allow this unreasonable inconsistency? The usage
pattern that I describe above is a real one, and I thought that the
whole point was to support it.
I have previously outlined places where this type of inconsistency
exists, in an earlier revision. [1]
If you want to phase in the introduction of requiring that all
relevant callsites use this infrastructure, I guess I'm okay with
that. However, I'm going to have to insist that for each of these new
fields, for any errcode you identify as requiring the field, either
all callsites get all relevant fields, or no call sites get all
relevant fields, and that each errcode be documented as such. So you
should probably just bite the bullet and figure out a reasonable and
comprehensive set of rules on adding these fields based on errcode.
Loosey goosey isn't going to cut it.
I'm having a difficult time imagining why we'd only have the
constraint/tablename for these errcodes (with one exception, noted
below):
/* Class 23 - Integrity Constraint Violation */
#define ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION
MAKE_SQLSTATE('2','3','0','0','0')
#define ERRCODE_RESTRICT_VIOLATION MAKE_SQLSTATE('2','3','0','0','1')
#define ERRCODE_NOT_NULL_VIOLATION MAKE_SQLSTATE('2','3','5','0','2')
#define ERRCODE_FOREIGN_KEY_VIOLATION MAKE_SQLSTATE('2','3','5','0','3')
#define ERRCODE_UNIQUE_VIOLATION MAKE_SQLSTATE('2','3','5','0','5')
#define ERRCODE_CHECK_VIOLATION MAKE_SQLSTATE('2','3','5','1','4')
#define ERRCODE_EXCLUSION_VIOLATION MAKE_SQLSTATE('2','3','P','0','1')
ERRCODE_UNIQUE_VIOLATION and ERRCODE_EXCLUSION_VIOLATION should be
related to index relation, not parent relation. Then we don't need set
COLUMN_NAME, that can be expression or more columns.
You previously defending some omissions [2] on the basis that they
involved domains, so some fields were unavailable. This doesn't appear
to be quite valid, though. For example, consider this untouched
callsite within execQual, that relates to a domain:
if (!conIsNull
Re: [HACKERS] Deprecating RULES
On 10/12/2012 08:48 PM, Greg Stark wrote: On Fri, Oct 12, 2012 at 7:55 AM, Simon Riggs si...@2ndquadrant.com wrote: AFAICS all RULEs can be re-expressed as Triggers or Views. This is a bizarre discussion. Firstly this isn't even close to true. The whole source of people's discontentment is that triggers are *not* equivalent to rules. If they were then they wouldn't be so upset. Secondly the only reason views work is because they're implemented using rules. Nobody is discussing deprecating VIEWs. And SELECT rules that are the basis of VIEWs are deprecated from being an independent user-visible feature for quite some time already If you want to do anything similar but different from views you would need to use rules as well. I'm still waiting on updateable views for example. You CAN do these using triggers, that is the main reason we have INSTEAD triggers. It sounds like what people are really looking for is to move the section of the manual describing rules to an internals section of the manual and add a note saying do not try to use rules to implement triggers. they are not triggers that explains how they're different and what they're useful for. Moving them to internals _and_ adding a note to not use them directly for any user code seems like a good plan. And replacing the original RULES page with suggestion to look under internals. In general user manuals, especially ones written like Unix man pages, tend to describe what things do without explaining why that might be useful. That's leaves users faced with a decision between trying similar-sounding features like rules and triggers and they might pick the wrong one. The Postgres manual is better than most in this respect but this is one area where it might pay to be extra clear. --- Hannu Krosing -- 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] Successor of MD5 authentication, let's use SCRAM
* Marko Kreen (mark...@gmail.com) wrote: As it works only on connect time, it can actually be secure, unlike user switching with SET ROLE. I'm guessing your issue with SET ROLE is that a RESET ROLE can be issued later..? If so, I'd suggest that we look at fixing that, but realize it could break poolers. For that matter, I'm not sure how the proposal to allow connections to be authenticated as one user but authorized as another (which we actually already support in some cases, eg: peer) *wouldn't* break poolers, unless you're suggesting they either use a separate connection for every user, or reconnect every time, both of which strike me as defeating a great deal of the point of having a pooler in the first place... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Deprecating RULES
On 12 October 2012 17:59, Josh Berkus j...@agliodbs.com wrote: I don't think you're listening, none of those things are problems and so not user hostile. Having an upgrade fail for mysterious reasons with a cryptic error message the user doesn't understand isn't user-hostile? Wow, you must have a very understanding group of users. I think its sad we can't even attempt a technical conversation without you making snide ad hominem attacks that aren't even close to being true on a personal level, nor accurate in a technical sense. Your argument applies to any and all deprecations, not just the one proposed. And it still applies to the deprecation schedule you outlined, since upgrades aren't always across consecutive releases. I'm the wrong person to lecture about upgrade incompatibility problems since I've spoken out against them for years. This style of debate makes it impossible to get new ideas across and holds us all back. -- 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
[HACKERS] [PATCH] assign result of query to psql variable
Hello here is updated version of gset patch. * merge Shigeru's doc patch * rename psql regression test from psql to psql_cmd Regards Pavel Stehule 2012/9/27 Pavel Stehule pavel.steh...@gmail.com: Hello 2012/9/21 Shigeru HANADA shigeru.han...@gmail.com: Hi Pavel, (2012/09/21 2:01), Pavel Stehule wrote: - Is it intentional that \gset can set special variables such as AUTOCOMMIT and HOST? I don't see any downside for this behavior, because \set also can do that, but it is not documented nor tested at all. I use a same SetVariable function, so a behave should be same It seems reasonable. Document - Adding some description of \gset command, especially about limitation of variable list, seems necessary. - In addition to the meta-command section, Advanced features section mentions how to set psql's variables, so we would need some mention there too. - The term target list might not be familiar to users, since it appears in only sections mentioning PG internal relatively. I think that the feature described in the section Retrieving Query Results in ECPG document is similar to this feature. http://www.postgresql.org/docs/devel/static/ecpg-variables.html I invite any proposals about enhancing documentation. Personally I am a PostgreSQL developer, so I don't known any different term other than target list - but any user friendly description is welcome. How about to say stores the query's result output into variable? Please see attached file for my proposal. I also mentioned about 1-row limit and omit of variable. should be Coding == The code follows our coding conventions. Here are comments for coding. - Some typo found in comments, please see attached patch. - There is a code path which doesn't print error message even if libpq reported error (PGRES_BAD_RESPONSE, PGRES_NONFATAL_ERROR, PGRES_FATAL_ERROR) in StoreQueryResult. Is this intentional? FYI, ecpg prints bad response message for those errors. yes - it is question. I use same pattern like PrintQueryResult, but bad response message should be used. I am sending updated patch It seems ok. BTW, as far as I see, no psql backslash command including \setenv (it was added in 9.2) has regression test in core (I mean src/test/regress). Is there any convention about this issue? If psql backslash commands (or any psql feature else) don't need regression test, we can remove psql.(sql|out). # Of course we need to test new feature by hand. It is question for Tom or David - only server side functionalities has regress tests. But result of some backslash command is verified in other regress tests. I would to see some regression tests for this functionality. Anyway, IMO the name psql impresses larger area than the patch implements. How about to rename psql to psql_cmd or backslash_cmd than psql as regression test name? I have no idea - psql_cmd is good name Regards Pavel -- Shigeru HANADA gset_06.diff 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] Truncate if exists
Alvaro Herrera alvhe...@2ndquadrant.com writes: Uh, we had an execute() function of sorts in the extensions patch; that seems to have been ripped out. Do we want it back? It was pretty different from what's being proposed here, as it was the server-side version of psql \i feature, that is, executing commands read directly from a SQL file on the server file's system. I'd much prefer that we spend time making such an hypothetical feature that irrelevant in all cases. There's still some work here, because the feature only is hypothetical to end users, that's exactly what we rely on today in the backend internal code… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Make pg_basebackup configure and start standby [Review]
On Wed, Oct 10, 2012 at 8:02 PM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Oct 11, 2012 at 3:36 AM, Boszormenyi Zoltan z...@cybertec.at wrote: 2012-10-10 18:23 keltezéssel, Fujii Masao írta: When tar output format is specified together with -R option, recovery.conf is not included in base.tar. I think it should. Why? This patch only promises to write the recovery.conf into the directory specified with -D. Because it's more user-friendly. If recovery.conf is not included in base.tar, when base.tar is extracted to disk to use the backup, a user always needs to copy recovery.conf to the extracted directory. OTOH if it's included in base.tar, such copy operation is not required and we can simplify the procedures to use the backup a bit. +1. -- 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] Improving the performance of psql tab completion
* Bruce Momjian (br...@momjian.us) wrote: On Fri, Oct 12, 2012 at 03:57:15PM -0400, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: There was also some discussion of fixing the name-check to be indexable, which the substring hack isn't. That would take a bit of work though. Right. I still want to do it, but it still needs a few more to-its, as it were. TODO item? Yes, but it should link to the previous thread which included info about what the right approach would be.. eg: http://archives.postgresql.org/pgsql-hackers/2012-08/msg00654.php Or the top of that thread. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Truncate if exists
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote:
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.
And you write
SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
and we can even get rid of all that other DDL crud that's been added
and we can have pg_if_table_not_exists() also.
You could make this more composable by having pg_table_exists() and
pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
you want the if-not-exists case then just stick a NOT in there. And
if you want a more complicated condition, you can easily write that as
well.
While that certainly has the merit of being compact, it mixes kinds of
evaluation (e.g. - parts of it are parsed at different times) and
requires quoting that isn't true for the other sorts of IF EXISTS
queries.
To be sure, you can do anything you like inside a DO $$ $$ language
plpgsql; block, but it's not nice to have to do a lot of work
involving embedding code between languages. Makes it harder to
manipulate, analyze, and verify.
Let me observe that Perl has, as one of its conditional concepts, the
notion of a statement modifier
http://perldoc.perl.org/perlsyn.html#Statement-Modifiers, which
corresponds pretty much to the IF EXISTS/IF NOT EXISTS modifiers that
have gotten added to Postgres over the last few versions. (I *think*
statement modifiers are attributable to SNOBOL, not 100% sure. I'm
pretty sure it predates Perl.)
I suggest the though of embracing statement modifiers in DDL, with
some options possible:
a) { DDL STATEMENT } IF CONDITION;
b) { DDL STATEMENT } UNLESS CONDITION;
where CONDITION has several possible forms:
i) {IF|UNLESS} ( SQL expression returning T/F )
ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
{TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
That feels like a cleaner extension than what we have had, with the IF
EXISTS/IF NOT EXISTS clauses that have been added to various
CREATE/DROP/ALTER commands.
--
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle this?
--
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] foreign key locks
Alvaro Herrera wrote: Andres Freund wrote: * heap_lock_tuple with mode == LockTupleKeyShare nowait looks like it would wait anyway in heap_lock_updated_tuple_rec Oops. I took a stab at fixing this. However, it is not easy. First you need a way to reproduce the problem, which involves setting breakpoints in GDB. (Since a REPEATABLE READ transaction will fail to follow an update chain due to tuple concurrently updated, you need to use a READ COMMITTED transaction; but obviously the timing to insert the bunch of updates in the middle is really short. Hence I inserted a breakpoint at the end of GetSnapshotData, had a SELECT FOR KEY SHARE NOWAIT get stuck in it, and then launched a couple of updates in another session). I was able to reproduce the undesirable wait. I quickly patched heap_lock_updated_tuple to pass down the nowait flag, but this is actually not reached, because the update chain is first followed by EvalPlanQual in ExecLockRows, and not by heap_lock_updated_tuple directly. And EPQ does not have the nowait behavior. So it still blocks. Maybe what we need to do is prevent ExecLockRows from following the update chain altogether -- after all, if heap_lock_tuple is going to do it by itself maybe it's wholly redundant. Not really sure what's the best way to approach this. At this stage I'm inclined to ignore the problem, unless some EPQ expert shows up and tells me that (1) it's okay to patch EPQ in that way, or (2) we should hack ExecLockRows (and remove EPQ?). I pushed (to github) patches for a couple of other issues you raised. Some others still need a bit more of my attention. -- Á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] Truncate if exists
On 10/12/12 2:05 PM, Christopher Browne wrote: That feels like a cleaner extension than what we have had, with the IF EXISTS/IF NOT EXISTS clauses that have been added to various CREATE/DROP/ALTER commands. +1 Josh like! -- Josh Berkus PostgreSQL Experts Inc. http://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] Truncate if exists
On 10/12/2012 11:05 PM, Christopher Browne wrote:
On Fri, Oct 12, 2012 at 3:04 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Oct 11, 2012 at 3:22 PM, Simon Riggs si...@2ndquadrant.com wrote:
So we just need a function called pg_if_table_exists(table, SQL) which
wraps a test in a subtransaction.
And you write
SELECT pg_if_table_exists('foo', 'TRUNCATE TABLE foo');
and we can even get rid of all that other DDL crud that's been added
and we can have pg_if_table_not_exists() also.
You could make this more composable by having pg_table_exists() and
pg_execute_sql_from_string(). Then you can write: SELECT CASE WHEN
pg_table_exists(...) THEN pg_execute_sql_from_string(...) END. And if
you want the if-not-exists case then just stick a NOT in there. And
if you want a more complicated condition, you can easily write that as
well.
While that certainly has the merit of being compact, it mixes kinds of
evaluation (e.g. - parts of it are parsed at different times) and
requires quoting that isn't true for the other sorts of IF EXISTS
queries.
To be sure, you can do anything you like inside a DO $$ $$ language
plpgsql; block, but it's not nice to have to do a lot of work
involving embedding code between languages. Makes it harder to
manipulate, analyze, and verify.
Let me observe that Perl has, as one of its conditional concepts, the
notion of a statement modifier
http://perldoc.perl.org/perlsyn.html#Statement-Modifiers, which
corresponds pretty much to the IF EXISTS/IF NOT EXISTS modifiers that
have gotten added to Postgres over the last few versions. (I *think*
statement modifiers are attributable to SNOBOL, not 100% sure. I'm
pretty sure it predates Perl.)
I suggest the though of embracing statement modifiers in DDL, with
some options possible:
a) { DDL STATEMENT } IF CONDITION;
b) { DDL STATEMENT } UNLESS CONDITION;
We could even go as far as
{ DDL STATEMENT } IF CONDITION ELSE {ANOTHER DDL STATEMENT };
For example
CREATE TABLE mytable(...)
IF NOT EXISTS TABLE mytable
ELSE TRUNCATE mytable;
where CONDITION has several possible forms:
i) {IF|UNLESS} ( SQL expression returning T/F )
ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
{TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
That feels like a cleaner extension than what we have had, with the IF
EXISTS/IF NOT EXISTS clauses that have been added to various
CREATE/DROP/ALTER commands.
--
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] Truncate if exists
Christopher Browne cbbro...@gmail.com writes:
I suggest the though of embracing statement modifiers in DDL, with
some options possible:
a) { DDL STATEMENT } IF CONDITION;
b) { DDL STATEMENT } UNLESS CONDITION;
Just saying. I hate that. Makes it harder to read, that last bit at the
end of the command changes it all. It's cool for a linguist, I guess,
but we're not typing sentences at the psql prompt…
where CONDITION has several possible forms:
i) {IF|UNLESS} ( SQL expression returning T/F )
ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
{TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
us that way, but I couldn't resist comparing. Soon enough you want a
full programming language there.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] Truncate if exists
where CONDITION has several possible forms:
i) {IF|UNLESS} ( SQL expression returning T/F )
ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
{TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
us that way, but I couldn't resist comparing. Soon enough you want a
full programming language there.
Well, embedding such a thing into plpgsql wouldn't be a bad thing. It's
a lot less hard on the DevOps person to request that they write a DO
statement if the DO statement is one line:
DO $$
BEGIN
TRUNCATE TABLE foo IF EXISTS foo;
END;$$;
Come to think of it, I've *often* wished for the perl-ish do x if y
syntax for plpgsql, and not just for DDL.
--
Josh Berkus
PostgreSQL Experts Inc.
http://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] Truncate if exists
On Fri, Oct 12, 2012 at 5:52 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
Christopher Browne cbbro...@gmail.com writes:
I suggest the though of embracing statement modifiers in DDL, with
some options possible:
a) { DDL STATEMENT } IF CONDITION;
b) { DDL STATEMENT } UNLESS CONDITION;
Just saying. I hate that. Makes it harder to read, that last bit at the
end of the command changes it all. It's cool for a linguist, I guess,
but we're not typing sentences at the psql prompt…
I could see it being
WHEN CONDITION { STATEMENT } OTHERWISE { STATEMENT };
It's all a strawman proposal, where I'm perfectly happy if there's
something people like better. I like to think this is cleaner than
the present proliferation of {IF EXISTS|IF NOT EXISTS}, but if others
don't concur, there's little point to taking it further.
where CONDITION has several possible forms:
i) {IF|UNLESS} ( SQL expression returning T/F )
ii) {IF|UNLESS} {EXISTS|NOT EXISTS}
{TABLE|SCHEMA|COLUMN|FUNCTION|...} object_name
Now we're talking about T-SQL? Sorry, I suppose you didn't mean to lead
us that way, but I couldn't resist comparing. Soon enough you want a
full programming language there.
Heh. Next, I'll be proposing LETREC*, or of adopting the EVERY
operator from Icon, and coroutines from BCPL :-). Keen on LOOP? :-)
The fact that we now have WITH RECURSIVE does extend what's reasonable
to hope for :-).
--
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle this?
--
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] Truncate if exists
On Tue, Oct 9, 2012 at 9:04 PM, Robert Haas robertmh...@gmail.com wrote: I've been a big proponent of adding IF EXISTS support to CREATE TABLE and ALTER TABLE but I'm having a hard time getting excited about this one. I can't imagine that many people would use it The reason CREATE IF NOT EXISTS and DROP IF EXISTS are so useful is because they're shortcuts for ensuring some specific state is always true. Regardless of whether the table existed before, now it does or doesn't as desired. (The concern about create was in fact specifically that it wouldn't guarantee that the same table definition would exist afterwards) The same is not true of TRUNCATE IF EXISTS. In that case after the command has run either the table exists and is empty or it doesn't exist and still needs to be created. I take it the intended use is something like TRUNCATE IF EXISTS foo; CREATE IF NOT EXISTS foo... So perhaps what we really need is a CREATE OR TRUNCATE foo(...), but just plain TRUNCATE IF EXISTS doesn't seem to make sense. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On 10/12/12 3:49 PM, Greg Stark wrote: TRUNCATE IF EXISTS foo; CREATE IF NOT EXISTS foo... Thing is, this can be written: CREATE IF NOT EXISTS foo ... TRUNCATE foo; For the exact same result. So, based on all of the objections and discussion on this feature, I personally no longer support it. -- Josh Berkus PostgreSQL Experts Inc. http://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] Successor of MD5 authentication, let's use SCRAM
On 10/12/12 12:44 PM, Stephen Frost wrote: Don't get me wrong- I really dislike that we don't have something better today for people who insist on password based auth, but perhaps we should be pushing harder for people to use SSL instead? Problem is, the fact that setting up SSL correctly is hard is outside of our control. Unless we can give people a run these three commands on each server and you're now SSL authenticating script, we can continue to expect the majority of users not to use SSL. And I don't think that level of simplicity is even theoretically possible. -- Josh Berkus PostgreSQL Experts Inc. http://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] Successor of MD5 authentication, let's use SCRAM
* Josh Berkus (j...@agliodbs.com) wrote: Problem is, the fact that setting up SSL correctly is hard is outside of our control. Agreed, though the packagers do make it easier.. Unless we can give people a run these three commands on each server and you're now SSL authenticating script, we can continue to expect the majority of users not to use SSL. And I don't think that level of simplicity is even theoretically possible. The Debian-based packages do quite a bit to ease this pain. Do the other distributions do anything to set up SSL certificates, etc on install? Perhaps they could be convinced to? Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Potential autovacuum optimization: new tables
Folks, One chronic problem users encounter is this one: 1. User creates new table 2. User inserts 45 records into new table. 3. Time passes. 4. User creates a query which joins against new table. 5. Planner uses estimate of 1000 rows for the new table. 6. User gets very bad query plan. Now, I look at this, and ask myself: why didn't autoanalyze kick in at step 3? After all, this was a table which had 0 rows, we inserted 45 rows, making the table infinitely larger. It should have got on the autoanalyze list, no? Well, no. It seems that any table with less than autovacuum_analyze_threshold rows will NEVER be autoanalyzed. Ever. postgres=# create table thirty_rows ( val int ); CREATE TABLE ^ postgres=# insert into thirty_rows select i from generate_series(1,30) as gs(i); INSERT 0 30 postgres=# create table onetwenty_rows ( val int ); CREATE TABLE postgres=# insert into onetwenty_rows select i from generate_series(1,120) as gs(i); INSERT 0 120 postgres=# create table twocent_rows ( val int ); CREATE TABLE postgres=# insert into twocent_rows select i from generate_series(1,200) as gs(i); ... wait 5 min ... postgres=# select relname, last_autoanalyze from pg_stat_user_tables where relname like '%_rows'; relname | last_autoanalyze +--- thirty_rows| twocent_rows | 2012-10-12 16:46:45.025647-07 onetwenty_rows | 2012-10-12 16:46:45.014084-07 postgres=# select * from pg_stats where tablename = 'thirty_rows'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram (0 rows) This seems easy to fix. If a table has no stats and has any write stats at all, it should automatically go on the autoanalyze list. Or if it's easier, one where last_autoanalyze is null. Objections/complications/alternatives? -- Josh Berkus PostgreSQL Experts Inc. http://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] Successor of MD5 authentication, let's use SCRAM
On 10/12/12 4:25 PM, Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: Unless we can give people a run these three commands on each server and you're now SSL authenticating script, we can continue to expect the majority of users not to use SSL. And I don't think that level of simplicity is even theoretically possible. The Debian-based packages do quite a bit to ease this pain. Do the other distributions do anything to set up SSL certificates, etc on install? Perhaps they could be convinced to? don't forget, there's OS's other than Linux to consider too... the various BSD's, Solaris, AIX, OSX, and MS Windows are all platforms PostgreSQL runs on. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Potential autovacuum optimization: new tables
All, 1. User creates new table 2. User inserts 45 records into new table. 3. Time passes. 4. User creates a query which joins against new table. 5. Planner uses estimate of 1000 rows for the new table. 6. User gets very bad query plan. Because Snowman asked me for an example: Before ANALYZE on the new table: http://explain.depesz.com/s/36D After ANALYZE on the new table: http://explain.depesz.com/s/851t That's a 25X difference in execution time. This is not the first time I've seen this issue. -- Josh Berkus PostgreSQL Experts Inc. http://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] Potential autovacuum optimization: new tables
Josh Berkus j...@agliodbs.com writes: Now, I look at this, and ask myself: why didn't autoanalyze kick in at step 3? After all, this was a table which had 0 rows, we inserted 45 rows, making the table infinitely larger. It should have got on the autoanalyze list, no? Well, no. It seems that any table with less than autovacuum_analyze_threshold rows will NEVER be autoanalyzed. Ever. Yeah ... This seems easy to fix. If a table has no stats and has any write stats at all, it should automatically go on the autoanalyze list. Or if it's easier, one where last_autoanalyze is null. No, it's not that easy. The question you have to ask is when has that initial write burst stopped?. As an example, if autovacuum happened to see that table in the instant after CREATE, it might autovacuum it while it's still empty, and then this rule fails to trigger any further effort. Personally I've always thought that autovacuum's rules should be based on a percentage of rows changed, not an absolute threshold (or maybe in addition to an absolute threshold). This way, if you create a table and insert 10 rows, that would make it subject to analyze on-sight, even if autovac had managed to pass by while it was still empty, because the percentage-changed is infinite. Then, if you insert the other 35 rows you meant to insert, it's *again* subject to autoanalyze on the next pass, because the percentage-changed is still 350%. I remember having got voted down on the percentage approach back when we first put AV into core, but I remain convinced that decision was a bad one. 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] Potential autovacuum optimization: new tables
* Josh Berkus (j...@agliodbs.com) wrote: Because Snowman asked me for an example: Thanks. :) That's a 25X difference in execution time. This is not the first time I've seen this issue. If we can figure out an 'easy' solution to this, I'd definitely vote for it being back-patched. Having a table simply never get analyze'd strikes me as a very bad thing. Thanks again, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Potential autovacuum optimization: new tables
No, it's not that easy. The question you have to ask is when has that initial write burst stopped?. As an example, if autovacuum happened to see that table in the instant after CREATE, it might autovacuum it while it's still empty, and then this rule fails to trigger any further effort. Well, frankly, it would be useful to know it's empty too. If you really wanna see that 1000-row default estimate bite you on the tuchas, try a JOIN against an empty new table. Personally I've always thought that autovacuum's rules should be based on a percentage of rows changed, not an absolute threshold (or maybe in addition to an absolute threshold). This way, if you create a table and insert 10 rows, that would make it subject to analyze on-sight, even if autovac had managed to pass by while it was still empty, because the percentage-changed is infinite. Then, if you insert the other 35 rows you meant to insert, it's *again* subject to autoanalyze on the next pass, because the percentage-changed is still 350% I remember having got voted down on the percentage approach back when we first put AV into core, but I remain convinced that decision was a bad one. Yeah, I was one of the ones voting against you. The reason not to have percentage-only is for small tables. Imagine that you have a table with 18 rows, and analyze_threshold is 0 and analyze_scale_factor is 0.1. Add two rows -- ANALYZE UPDATE two rows -- ANALYZE UPDATE three more rows -- ANALYZE DELETE three rows -- ANALYZE Without a threshold, any table under 100 rows which gets UPDATEs would be continually in the autoanalyze queue, which would not be beneficial; the churn in pg_statistic alone would be detrimental. I guess the real issue is that we tried to solve the issue of ANALYZEing tables of radically different sizes with a simple heuristic of threshold+scale_factor, and that's proving too simple for actual production sites. The other end where autoanalyze often falls down is the high end (tables with a million rows). Can anyone think of a new heuristic which doesn't involve adding 2-6 new GUCS knobs? -- Josh Berkus PostgreSQL Experts Inc. http://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] Potential autovacuum optimization: new tables
Josh Berkus j...@agliodbs.com writes: I remember having got voted down on the percentage approach back when we first put AV into core, but I remain convinced that decision was a bad one. Yeah, I was one of the ones voting against you. The reason not to have percentage-only is for small tables. Imagine that you have a table with 18 rows, and analyze_threshold is 0 and analyze_scale_factor is 0.1. [ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update percentages quite far north of 100%. It's possible and maybe likely that we need a sliding scale. Also, I don't necessarily accept the conclusion you seem to be drawing, that it's okay to have complete turnover of a small table and not redo its stats. If you don't like the current behavior when there's no stats, why would you like the behavior when there are some stats but they no longer have the remotest relationship to reality? Can anyone think of a new heuristic which doesn't involve adding 2-6 new GUCS knobs? The increased number of knobs may be a problem, but I don't think we can avoid having more. Your own complaint is that the current design is too simplistic. Replacing it with a different but equally simplistic design probably won't help much. 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] Potential autovacuum optimization: new tables
[ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update percentages quite far north of 100%. It's possible and maybe likely that we need a sliding scale. Yes, or a logarithmic one. Also, I don't necessarily accept the conclusion you seem to be drawing, that it's okay to have complete turnover of a small table and not redo its stats. I'm not drawing that conclusion. I'm explaining the logic of autovacuum_analyze_threshold. That logic actually works pretty well for tables between 200 rows and 200,000 rows. It's outside of those boundaries where it starts to break down. The increased number of knobs may be a problem, but I don't think we can avoid having more. Your own complaint is that the current design is too simplistic. Replacing it with a different but equally simplistic design probably won't help much. Well, we could do something which involves no GUCS at all, which would be my favorite approach. For example, Frost and I were discussing this on IRC. Imagine if autovac threshold were set according to a simple log function, resulting in very small tables getting analyzed after 100% changes, and very large tables getting analyzed after 0.1% changes, and everyone else between? -- Josh Berkus PostgreSQL Experts Inc. http://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] Potential autovacuum optimization: new tables
* Tom Lane (t...@sss.pgh.pa.us) wrote: [ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update percentages quite far north of 100%. It's possible and maybe likely that we need a sliding scale. I was just discussing such a sliding scale approach w/ Josh on IRC, my thinking was that we could use a logarithmic approach based on table size. Also, I don't necessarily accept the conclusion you seem to be drawing, that it's okay to have complete turnover of a small table and not redo its stats. If you don't like the current behavior when there's no stats, why would you like the behavior when there are some stats but they no longer have the remotest relationship to reality? Josh's concern is about autovacuum causing lots of stats churn, which is understandable, we don't want it constantly rescanning a table, but perhaps we could use some kind of threshold for preventing autovac from rescanning a table it just scanned? Note that I did *not* say 'GUC', but I don't know what the 'right' answer is for how frequently is good-but-not-too-frequent. I'd also like to try and avoid adding GUCs. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Potential autovacuum optimization: new tables
On Oct 12, 2012, at 22:13, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: [ shrug... ] You're attacking a straw man, or more precisely putting words into my mouth about what the percentage-based thresholds might be. Notice the examples I gave involved update percentages quite far north of 100%. It's possible and maybe likely that we need a sliding scale. I was just discussing such a sliding scale approach w/ Josh on IRC, my thinking was that we could use a logarithmic approach based on table size. Also, I don't necessarily accept the conclusion you seem to be drawing, that it's okay to have complete turnover of a small table and not redo its stats. If you don't like the current behavior when there's no stats, why would you like the behavior when there are some stats but they no longer have the remotest relationship to reality? Josh's concern is about autovacuum causing lots of stats churn, which is understandable, we don't want it constantly rescanning a table, but perhaps we could use some kind of threshold for preventing autovac from rescanning a table it just scanned? Note that I did *not* say 'GUC', but I don't know what the 'right' answer is for how frequently is good-but-not-too-frequent. I'd also like to try and avoid adding GUCs. Instead of global could you attach an interface function to the table and have the auto-analyzer call that function to basically ask the table whether it needs to be analyzed? Still need to deal with defaults and provide a decent supply of built-in algorithms but at least the system can be made tunable. The default algorithm could maybe just handoff to a table size specific handler. The create table and alter table commands could be used to change the assigned algorithm if desired and new ones could be supplied via extensions. The 1000 row default seems unusual at first glance and contributes to the problem described. It is likely that the first I sent following the create table is going to be a bulk load if the table is going to have many rows. In the case where rows are inserted individually it is likely that the expected row count will be closer to 1 than 1000. One useful algorithm to provide the user is analyze on insert and, though maybe less so, analyze on update. So that any insert/update causes the table to be re-analyzed. Not a good default but, combined with delayed analyze logic to establish a minimum frequency, is a possible option for some use cases. Temporary table creation should have special attention given if changes are going to be made here. Another idea is to have system after [command] trigger(s) than can be used to call analyze without waiting for the auto-vacuum process. Provide some way for CREATE/ALTER TABLE and maybe auto-vacuum to enable and disable the trigger. David J. -- 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] Potential autovacuum optimization: new tables
* David Johnston (pol...@yahoo.com) wrote: Instead of global could you attach an interface function to the table and have the auto-analyzer call that function to basically ask the table whether it needs to be analyzed? Still need to deal with defaults and provide a decent supply of built-in algorithms but at least the system can be made tunable. The default algorithm could maybe just handoff to a table size specific handler. The create table and alter table commands could be used to change the assigned algorithm if desired and new ones could be supplied via extensions. For my part, while that's certainly an interesting idea, it's far more complicated than even providing GUCs and the idea is to make PG just do it right, not to offer the user more ways to get it wrong... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Adding comments for system table/column names
2012/10/12 Bruce Momjian br...@momjian.us There was a thread in January of 2012 where we discussed the idea of pulling system table/column name descriptions from the SGML docs and creating SQL comments for them: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00837.php Magnus didn't seem to like the idea: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00848.php Well, I'd expect some of those columns to get (at least over time) significantly more detailed information than they have now. Certainly more than you'd put in comments in the catalogs. And having some sort of combination there seems to overcomplicate things... I think the idea of having the short descriptions in SQL and longer ones in SGML is not maintainable. One idea would be to clip the SQL description to be no longer than a specified number of characters, with proper word break detection. Here is how psql displays column and table comments: test= create table test(x int); CREATE TABLE test= comment on column test.x IS 'wow'; COMMENT test= \d+ test Table public.test Column | Type | Modifiers | Storage | Stats target | Description +-+---+-+--+- x | integer | | plain | | wow Has OIDs: no test= comment on table test is 'yikes'; COMMENT test= \d+ List of relations Schema | Name | Type | Owner | Size | Description +--+---+--+-+- public | test | table | postgres | 0 bytes | yikes (1 row) Should I continue working on this patch? Hi all, If it can help, Euler wrote about that some time ago in your blog [1]. Regards, [1] http://eulerto.blogspot.com.br/2010/11/comment-on-catalog-tables.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Successor of MD5 authentication, let's use SCRAM
Stephen Frost wrote: * Josh Berkus (j...@agliodbs.com) wrote: Problem is, the fact that setting up SSL correctly is hard is outside of our control. Agreed, though the packagers do make it easier.. Unless we can give people a run these three commands on each server and you're now SSL authenticating script, we can continue to expect the majority of users not to use SSL. And I don't think that level of simplicity is even theoretically possible. The Debian-based packages do quite a bit to ease this pain. Do the other distributions do anything to set up SSL certificates, etc on install? Perhaps they could be convinced to? This has bit me. At my work we started a project on Debian, using the http://packages.debian.org/squeeze-backports/ version of Postgres 9.1, and it included the SSL out of the box, just install that regular Postgres or Pg client package and SSL was ready to go. And now we're migrating to Red Hat for the production launch, using the http://www.postgresql.org/download/linux/redhat/ packages for Postgres 9.1, and these do *not* include the SSL. This change has been a pain, as we then disabled SSL when we otherwise would have used it. (Though all database access would be over a private server-server network, so the situation isn't as bad as going over the public internet.) How much trouble would it be to make the http://www.postgresql.org/download/linux/redhat/ packages include SSL? -- Darren Duncan -- 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] Successor of MD5 authentication, let's use SCRAM
On 10/12/12 9:00 PM, Darren Duncan wrote: And now we're migrating to Red Hat for the production launch, using the http://www.postgresql.org/download/linux/redhat/ packages for Postgres 9.1, and these do *not* include the SSL. hmm? I'm using the 9.1 for CentOS 6(RHEL 6) and libpq.so certainly has libssl3.so, etc as references. ditto the postmaster/postgres main program has libssl3.so too. maybe your certificate chains don't come pre-built, I dunno, I haven't dealt with that end of things. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Successor of MD5 authentication, let's use SCRAM
John R Pierce wrote: On 10/12/12 9:00 PM, Darren Duncan wrote: And now we're migrating to Red Hat for the production launch, using the http://www.postgresql.org/download/linux/redhat/ packages for Postgres 9.1, and these do *not* include the SSL. hmm? I'm using the 9.1 for CentOS 6(RHEL 6) and libpq.so certainly has libssl3.so, etc as references. ditto the postmaster/postgres main program has libssl3.so too. maybe your certificate chains don't come pre-built, I dunno, I haven't dealt with that end of things. Okay, I'll have to look into that. All I know is out of the box SSL just worked on Debian and it didn't on Red Hat; trying to enable SSL on out of the box Postgres on Red Hat gave a fatal error on server start, at the very least needing the installation of SSL keys/certs, which I didn't have to do on Debian. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
