Re: [SQL] CTAGS for PL/pgSQL ?
On Thu, Aug 29, 2013 at 08:18:03AM -0500, Charles Sheridan wrote: Does anyone know if there are any CTAGS extensions or variants that support PL/pgSQL ? I use exuberant-ctags which does not support it, and a web search does not return anything promising. As far as I know, the quick answer is NO. However I made a few simple tests with etags and it seems to work with plpgsql. However I would expect it to fail for some advanced features. Luca Thanks Luca, unfortunately (?) I'm a pretty heavy Vim user, so etags is not in my scope. Uh, I think Vim can use etags, no? Isn't etags Exuberant Ctags? The Exuberant Ctags's FAQ mentions Vim: http://ctags.sourceforge.net/faq.html#11 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 04:16:41PM +0200, Thomas Kellerer wrote: Theodore Petrosky, 03.07.2013 15:41: sorry, but you misunderstand. this is the correct behavior of SQL. It is part of the specification to do this. Not quite. The SQL standard requires folding to uppercase. Agreed. The original poster specifically wanted MYTABLE and mytable to be the same, not mytable and mytable. Postgres is certainly non-standard in this area. I think the ability visiually distinguish lower-case letters better than upper-case letters has led to our behavior. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 09:02:20PM +0530, Dev Kumkar wrote: On Wed, Jul 3, 2013 at 8:54 PM, Bruce Momjian br...@momjian.us wrote: Agreed. The original poster specifically wanted MYTABLE and mytable to be the same, not mytable and mytable. Postgres is certainly non-standard in this area. I think the ability visiually distinguish lower-case letters better than upper-case letters has led to our behavior. Not really, actually am looking for column aliases here and not the table. Here is the example again when the aliases are unquoted: - SELECT my_column as MY_COLUMN FROM my_table The above SELECT will fold the alias name as my_column and not MY_COLUMN. Yes, both the identifier names and alias names are folded to lower case. I never thought of them as different, but you are right, they are, and we are non-standard in both areas. Sorry. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 12:20:06PM -0400, Tom Lane wrote: Dev Kumkar devdas.kum...@gmail.com writes: Any plans to fix this in next release or having a patch to fix this? No. This has been discussed (many times) before. There isn't any feasible way to change this behavior without breaking an incredible amount of code, much of which isn't even under our control. The marginal increase in standards compliance is not worth the pain --- especially when the aspect of the standard in question isn't even one that most of us like. (All-upper-case is hard to read.) If this is a deal-breaker for you, then I'm sorry, but you need to find another database. Postgres settled on this behavior fifteen years ago, and we're not changing it now. Agreed. I guess we could add it to the Features We Do Not Want section of the TODO list, but it rarely comes up. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 03:47:37PM -0400, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Dev Kumkar escribi�: But what I am asking here is if an alias name is provided be it upper case, lower case, or a mix then shouldn't it be preserved as as it is given. All this talk is when alias names are unquoted, when quoted then its standard behavior as seen in other databases. Aliases are treated just like any other identifier. The downcasing happens in the lexer (src/backend/parser/scan.l), which is totally unaware of the context in which this is happening; so there's no way to tweak the downcasing behavior for only aliases and not other identifiers. Quite aside from implementation difficulty, restricting the change to just column aliases doesn't make it more palatable. You'd entirely lose the argument that the change increases spec compliance, because the spec is perfectly clear that a column alias is an identifier just like any other. And you'd still be paying a large part of the application breakage costs, because the identifiers coming back in query descriptors are one of the main ways applications would notice such a change. And let's not forget that column aliases can be used as indentifiers in queries: test= SELECT 1 AS x test- ORDER BY x; x --- 1 (1 row) test= SELECT 1 AS X ORDER BY x; ERROR: column x does not exist LINE 2: ORDER BY x; Changing this would mean that the same identifier would have different case-folding rules depending on where it appeared in the query. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 02:00:24AM +0530, Dev Kumkar wrote: On Thu, Jul 4, 2013 at 1:36 AM, Bruce Momjian br...@momjian.us wrote: And let's not forget that column aliases can be used as indentifiers in queries: test= SELECT 1 AS x test- ORDER BY x; x --- 1 (1 row) test= SELECT 1 AS X ORDER BY x; ERROR: column x does not exist LINE 2: ORDER BY x; Changing this would mean that the same identifier would have different case-folding rules depending on where it appeared in the query. Sorry but I am not sure about your point here. Currently if the alias is quoted then same needs to be used in queries as identifies: SELECT 1 AS X ORDER BY X; You would need to double-quote 'x' in the ORDER BY, but not in the target list (because case would be preserved there) --- that is confusing. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] to_char() accepting invalid dates?
Thomas Kellerer wrote:
Bruce Momjian, 19.07.2011 00:02:
postgres= select to_date('20110231', 'mmdd');
to_date
2011-03-03
(1 row)
is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
That would be *very* odd indeed.
jasen=# select '20110303'::date;
Thanks for the tip, this was more a question regarding _why_ to_char()
behaves this way.
Well, to_char() is based on Oracle's to_char(). How does Oracle handle
such a date?
Oracle throws an error for the above example:
SQL select to_date('20110231', 'MMDD') from dual;
select to_date('20110231', 'MMDD') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL
OK, it's a bug then. Let me see if I can find a fix for it.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] to_char() accepting invalid dates?
Thomas Kellerer wrote:
Jasen Betts wrote on 18.07.2011 11:23:
postgres= select to_date('20110231', 'mmdd');
to_date
2011-03-03
(1 row)
is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
That would be *very* odd indeed.
jasen=# select '20110303'::date;
Thanks for the tip, this was more a question regarding _why_ to_char()
behaves this way.
Well, to_char() is based on Oracle's to_char(). How does Oracle handle
such a date?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using min|max in where
Ben Carbery wrote: Hi, I have some sql like so: SELECT min(date) INTO d FROM interest_rate WHERE m_code = NEW.code; UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate WHERE m_code = NEW.code AND date = d; Actually this is pgsql but I don't think that matters. I am wondering if I can make this more compact by somehow including the 'min' function in the WHERE clause, but WITHOUT simply moving the select in there. So not this.. UPDATE interest_rate SET date = NEW.start_date, rate = NEW.initial_rate WHERE m_code = NEW.code AND date = (SELECT min(date) FROM interest_rate WHERE m_code = NEW.code); This is just an example but I seem to find this pattern a lot in my functions. I think you want the HAVING clause of SELECT. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] oracle to postgres migration question
Joshua Gooding wrote: Hello, I'm looking for the postgres equivalent of oracles: set numwidth command. Is there an equivalent? If we knew what it did, we might be able to help you. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] oracle to postgres migration question
Scott Marlowe wrote: Note that psql automagically right justifies numerics and dynamically sizes all columns so you don't have to do as much of this stuff. Oracle always made me feel like I was operating the machine behind the curtain in the Wizard of Oz, lots of handles and switches and knobs I had to mess with to get useful output. Yeah, I have heard that description many times in other forms. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Capacity planning.
David Harel wrote: Hi, Any way to get transaction count from the postgres daemon or any log? See the system view pg_stat_database. Also where can I find docs that can help me make a capacity plan for max 100,000 clients making around 200 transactions a day each. No idea. You should be using connection pooling for that setup. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
Alvaro Herrera wrote: Pavel Stehule escribi?: 2009/5/18 Alvaro Herrera alvhe...@commandprompt.com: Pavel Stehule escribi?: others tricks http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks There's a lot of good stuff in there ... would you care about copying/moving it to wiki.postgresql.org/wiki/Snippets ? This contents is free - and if you would do it, please do it. My english isn't good, so it's work for someone with good english. I am maintainer and founder of this site, and I am granting rights for free content copy. I don't have time for that right now, but I have added a link to your page at the top of Snippets. Thanks. FYI, I have a copy of the old plpgsql cookbook at: http://www.brasileiro.net:8080/postgres/cookbook/ PostgreSQL CookBook Pages Roberto Mello http://techdocs.postgresql.org/guides/SetReturningFunctions PostgreSQL 7.3 Set Returning Functions Stephan Szabo stored at: http://momjian.us/expire/cookbook.tgz if someone wants to transfer them to the wiki. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] proposal for a CookBook in postgresql.org
Alvaro Herrera wrote: Bruce Momjian escribi?: FYI, I have a copy of the old plpgsql cookbook at: http://www.brasileiro.net:8080/postgres/cookbook/ PostgreSQL CookBook Pages Roberto Mello http://techdocs.postgresql.org/guides/SetReturningFunctions PostgreSQL 7.3 Set Returning Functions Stephan Szabo Stephan's article is already on the wiki: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions It needs an update. I don't know about Roberto Mello's site. Did we get a copyright transfer or a license saying we could use the contents? Nope, but I assumed it was BSD-licensed. I see this Josh Berkus copyright: --Copyright Josh Berkus, j...@agliodbs.com --permission granted to use anywhere provided that this --copyright statement remains in the code. --No warranty is given or implied. --Use at your own risk -- strictly beta code. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [DOCS] [SQL] proposal for a CookBook in postgresql.org
Alvaro Herrera wrote: Bruce Momjian escribi?: Alvaro Herrera wrote: I don't know about Roberto Mello's site. Did we get a copyright transfer or a license saying we could use the contents? Nope, but I assumed it was BSD-licensed. I see this Josh Berkus copyright: --Copyright Josh Berkus, j...@agliodbs.com You're saying that Josh Berkus owns the copyright of the entire site? That seems unlikely. On one function. I think the way we should go about this is somebody talks to Roberto and gets his approval on us using his content on the Wiki (the way we did with Pavel upthread). Otherwise I think it's a nonstarter. OK. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Grass Root Protectionism
Scott Marlowe wrote: On Sat, Feb 7, 2009 at 11:40 PM, Boycott Tech Forums boycotttechfor...@yahoo.com wrote: I am a Sr. Software Engineer in USA who (like many others) have been unfairly treated with offshore software engineers who have the audacity to take our jobs, yet ask (mostly) American engineers to help them with their technical challenges via Technical Forums (like this one). One solution is a bit of Grass Root Protectionism by boycotting technical forums. Perhaps if it takes an offshore engineer 2 hours to solve a problem, then the employer would see the real cost implication. I encourage American engineers who spend hundreds of thousands of dollars to develop their skills not to give it away so freely. Without foreign engineers working on pgsql it wouldn't be nearly as far along as it is today. It is larger than that. Without non-US developers, Postgres would be less than half what it is today, meaning that US Postgres users need to thank the non-US developers for their work over the past 12 years of community Postgres development. It is US people who are benefitting more from the relationship, not non-US people. This is true of many open source projects. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
FYI, I tested your query in 8.3.X CVS and it worked so this fix will in the next 8.3 minor release. --- Corey Horton wrote: Is there any known workaround to get this the elements of the histogram_bounds anyarray in 8.3.5. If not, when might I expect a fix? Just trying to plan our testing/release schedule of rolling out to 8.3 around this problem. Thanks, Corey Tom Lane wrote: I wrote: While we could probably revert just enough of the changes to enforce_generic_type_consistency to allow this case again, I wonder just how safe that'd really be. It would amount to expecting that functions that take anyarray but don't take or return anyelement to not only work on any array type, but to be always prepared for the input element type to change on-the-fly (since that's exactly what would happen when scanning pg_statistic). Quite a lot of the built-in anyarray functions are prepared to do that, but I'm not sure they all are. I went and looked, and found that none of the thirty or so built-in functions that accept ANYARRAY are coded to make unsafe assumptions about the input array type remaining the same across calls. So at least as of CVS HEAD, it seems safe to relax this back to the way it was pre-8.3. I'm still worried about the possibility of extension functions or future core functions failing to follow this coding rule; but as long as people are lazy and copy-and-paste from the existing models, it should be okay. regards, tom lane -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] inconsistent automatic casting between psql and function
Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] writes: That's because a quoted literal isn't necessarily a timestamp. Without context it could be anything, and in the context of comparing to a date the planner probably tries to make it a date. I think the real point here is this: regression=# select '2008-12-09 02:00:00'::date; date 2008-12-09 (1 row) ie, when it does decide that a literal should be a date, it will happily throw away any additional time-of-day fields that might be in there. Had it raised an error, Stefano might have figured out his mistake sooner. ISTM we deliberately chose this behavior awhile back, but I wonder whether it does more harm than good. Well, it seems fine to me because it works just like the cast of a float to an integer: test= select 1.23432::integer; int4 -- 1 (1 row) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Public synonyms
Azzeddine Daddah wrote: Hi guys, I'm new to Postgresql and I've a small question: Does Postgresql support public synonyms? No, sorry, but it is a TODO item: Add support for public SYNONYMs -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
Jean-David Beyer wrote:
TJ O'Donnell wrote:
I use a c function, nbits_set that will do what you need.
I've posted the code in this email.
TJ O'Donnell
http://www.gnova.com
#include postgres.h
#include utils/varbit.h
Datum nbits_set(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(nbits_set);
Datum
nbits_set(PG_FUNCTION_ARGS)
{
/* how many bits are set in a bitstring? */
VarBit *a = PG_GETARG_VARBIT_P(0);
int n=0;
int i;
unsigned char *ap = VARBITS(a);
unsigned char aval;
for (i=0; i VARBITBYTES(a); ++i) {
aval = *ap; ++ap;
if (aval == 0) continue;
if (aval 1) ++n;
if (aval 2) ++n;
if (aval 4) ++n;
if (aval 8) ++n;
if (aval 16) ++n;
if (aval 32) ++n;
if (aval 64) ++n;
if (aval 128) ++n;
}
PG_RETURN_INT32(n);
}
Hi all,
Am looking for a fast and efficient way to count the number of bits set
(to 1) in a VARBIT field. I am currently using
LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS TEXT),'0','','g')).
Allan.
When I had to do that, in days with smaller amounts of RAM, but very long
bit-vectors, I used a faster function sort-of like this:
static char table[256] = {
0,1,1,2,1,2,2,3,1,.
};
Then like above, but instead of the loop,
n+= table[aval];
You get the idea.
Uh, I was kind of confused by this, even when I saw a full
implementation:
http://graphics.stanford.edu/~seander/bithacks.html#CountBitsSetTable
Actually, this looks even better:
http://graphics.stanford.edu/~seander/bithacks.html#CountBitsSetKernighan
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
Sebastian Rychter wrote: Thanks. Hiroshi sent me a possible solution (which might be useful for others going through similar situations), which is to toggle off the Disable Genetic Optimizer from the ODBC driver configuration. Uh, why is ODBC modifyingin the 'geqo' variable? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Bouncing replies [was: SQL standards in Mysql]
Dean Gibson (DB Administrator) wrote: On 2008-02-22 21:34, Scott Marlowe wrote: Bouncing messages from a public list is kinda rude. No more so, than sending two copies of your reply to me, because you don't go up to your mailer's To: line and manually delete the extra address (as I do on EVERY reply I send to this list). In fact, if you do that, you won't get the bounce. I do it out of consideration for others: since _I_ would prefer to not receive two copies of replies, I assume that _others_ prefer the same. I don't whine about it; I just do it. So, in return for that consideration, I get your reply. How did getting the bounce hurt you or your computer, or make any extra work for you, more than I would have to do when I get a duplicate message I am surprised no one suggested majordomo's 'eliminatecc' option: http://mail.postgresql.org/mj/mj_wwwusr?user=passw=list=GLOBALfunc=helpextra=set Avoid courtesy copies The eliminatecc setting controls courtesy copy elimination. If this setting is enabled, and your address appears in the To: or Cc: headers of a posted message, Majordomo will not send an additional copy to you. This helps to cut down on many of those annoying duplicates that are often received, but it deprives you of the additional processing that Majordomo does on a message (subject prefixes, additional headers, etc.). eliminatecc - turns CC elimination on noeliminatecc - turns it off This does what the requestor wants, namely not send email from the list if they are already receiving the email as a reply. (It is spam removal or elimination of duplicates that is the problem?) Perhaps I've been working with computers too long. I've been PAID as a full-time software developer for the past 40 years (45 if you count part-time employment in college), and I'm AMAZED at the amount of intolerance I see on the Internet with respect to eMails. Some people whine because the reply is at the top of the message rather than at the bottom. There are perfectly good reasons for replying at the top OR at the bottom, depending upon the circumstances. Other people whine because the sender does not wrap his/her eMail at 76 columns, or because the eMail is ALL CAPS, or some other imagined slight. Being an ADAPTABLE human being, rather than UPSET people who aren't similarly inclined, I simply ADAPT to the environment in most cases. I think a lot of the finickiness comes from the fact that emails often go to thousands of people, which does require more work from the email author. It is the cost of being able to communicate with that many people at once. Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. Let me add that just trashing all email from you is an option many might choose. You seem more concerned with making things easy for yourself and not adequately considering the thousands of people who are you communicating with, and replying to your emails trying to help you. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) Help/Unsubscribe/Update your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-sql
Re: [SQL] trigger for TRUNCATE?
Added to TODO: * Add ability to trigger on TRUNCATE http://archives.postgresql.org/pgsql-sql/2008-01/msg00050.php --- Simon Riggs wrote: On Fri, 2008-01-11 at 08:24 +, Richard Huxton wrote: I've always considered TRUNCATE to be DDL rather than DML. I mentally group it with DROP TABLE rather than DELETE DDL/DML probably isn't the right split, since its then arguable as to which group of commands it belongs in. I see we have 3 types of commands: 1. Commands that alter the rows in the table e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group 2. Commands that change the shape of a table e.g. ALTER TABLE add/drop column, change type, constraints etc 3. Commands that change the environment of a table e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM, CLUSTER etc Type (1) commands need to be replicated always, sliding down the scale to the type (3) which might well be site dependent. Applications seldom issue type 3 commands anyway, so its easy for a DBA to arrange for them to be executed in multiple places and there isn't any timing requirement usually to making that work. In some cases some of these factors might be managed by replication controllers, so the DBA doesn't need to touch at least some of these aspects. Applications do issue some type 2 commands, but usually they are for TEMP tables. Type 2 commands do change replication, but might not need to be exactly replicated on both sites. Again, some utilities exist to ensure that DDL changes are correctly replicated, so there is slightly less need for triggers on this. In many cases the application is locked down completely anyway and almost no DDL is ever executed. If it is executed it needs to be done in coordination with a change of application version. Applications issue lots of type 1 commands and we can't always easily change the SQL they execute. It's very common for an application to have a single userid, so its not a problem for it to be the owner of the table as well and hence TRUNCATE is usable. It is often written without any thought for replication, which is usually an afterthought. (If we allowed RULEs to translate TRUNCATE into DELETEs it would at least plug the gap, but thats not a great planand I'm not suggesting it.) So the main gap in all of this is the lack of a TRUNCATE trigger, probably also the lack of a specific TRUNCATE privilege as well. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
Tom Lane wrote: Daniel Caune [EMAIL PROTECTED] writes: I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR UPDATE in PostgreSQL 8.1. The number of rows returned is actually (n - 1). I'm trying to find whether this is an identified issue with PostgreSQL 8.1 that might have been fixed in a later version such as 8.2; I don't have any problem in moving to a later version if needed. There's no known issue specifically of that form (and a quick test of 8.1 doesn't reproduce any such behavior). However, it is known and documented that LIMIT and FOR UPDATE behave rather oddly together: the LIMIT is applied first, which means that if FOR UPDATE rejects any rows as being no longer up-to-date, you get fewer than the expected number of rows out. You did not mention any concurrent activity in your example, but I'm betting there was some ... Current documentation explains why in the SELECT manual page: It is possible for a commandSELECT/ command using both literalLIMIT/literal and literalFOR UPDATE/SHARE/literal clauses to return fewer rows than specified by literalLIMIT/literal. This is because literalLIMIT/ is applied first. The command selects the specified number of rows, but might then block trying to obtain lock on one or more of them. Once the literalSELECT/ unblocks, the row might have been deleted or updated so that it does not meet the query literalWHERE/ condition anymore, in which case it will not be returned. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Block size with pg_dump?
Jean-David Beyer wrote: The main question is, If I present pg_restore with a 65536-byte blocksize and it is expecting, e.g., 1024-bytes, will the rest of each block get skipped? I.e., do I have to use dd on the way back too? And if so, what should the blocksize be? Postgres (by default) uses 8K blocks. That is true of the internal storage, but not of pg_dump's output because it is using libpq to pull rows and output them in a stream, meaning there is no blocking in pg_dumps output itself. Is that true for both input and output (i.e., pg_restore and pg_dump)? I.e., can I use dd to write 65536-byte blocks to tape, and then do nothing on running pg_restore? I.e., that pg_restore will accept any block size I choose to offer it? Yes. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Block size with pg_dump?
Jean-David Beyer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 When I make a backup of a database, I put the output file directly on magnetic tape; i.e., my command looks like this: pg_dump --file=/dev/st0 This way I do not have to worry if the total backup exceeds the size of a file system, and it saves me the trouble of copying it to the tape as a separate step. My current tapes will hold 20 GBytes raw or 40GBytes if I enable hardware compression (assuming 2:1 compression happens). Now it says in the documentation that if I use format c it will compress the data in software, so I doubt the hardware compression will do much. I do not know what blocksize pg_dump uses, or if it insists on a particular blocksize on input. Now my tape drive will work with any blocksize, but prefers 65536-byte blocks. I do not see any options for this in pg_dump, but I could pipe the output of pg_dump through dd I suppose to make any blocksize I want. On the way back, likewise I could pipe the tape through dd before giving it to pg_restore. Does pg_dump care what blocksize it gets? If so, what is it? I assume you could pipe pg_dump into dd and specify the block size in dd. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Block size with pg_dump?
Erik Jones wrote: On the way back, likewise I could pipe the tape through dd before giving it to pg_restore. Does pg_dump care what blocksize it gets? If so, what is it? I assume you could pipe pg_dump into dd and specify the block size in dd. Of course on the way out I can do that. The main question is, If I present pg_restore with a 65536-byte blocksize and it is expecting, e.g., 1024-bytes, will the rest of each block get skipped? I.e., do I have to use dd on the way back too? And if so, what should the blocksize be? Postgres (by default) uses 8K blocks. That is true of the internal storage, but not of pg_dump's output because it is using libpq to pull rows and output them in a stream, meaning there is no blocking in pg_dumps output itself. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Added to TODO:
o Have timestamp subtraction not call justify_hours()?
http://archives.postgresql.org/pgsql-sql/2006-10/msg00059.php
---
Jim C. Nasby wrote:
Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
return 25:00:00, not 1 day 1:00.
I agree with Tom that this should be changed; I'm just arguing that we
might well need a backwards-compatibility solution for a while. At the
very least we'd need to make this change very clear to users.
On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
One problem with removing justify_hours() is that this is going to
return '24:00:00', rather than '1 day:
test= select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
00:00:00'::timestamptz;
?column?
--
24:00:00
(1 row)
---
Jim Nasby wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/
seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to
the
subtraction result for themselves. Not sure what the fallout would
be,
though.
I suspect there's applications out there that are relying on that
being nicely formated for display purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Do we want to do anything about this for 8.3?
---
Jim C. Nasby wrote:
Yes, but if it was '2004-01-02 01:00:00'-'2004-01-01 00:00:00' it should
return 25:00:00, not 1 day 1:00.
I agree with Tom that this should be changed; I'm just arguing that we
might well need a backwards-compatibility solution for a while. At the
very least we'd need to make this change very clear to users.
On Tue, Feb 20, 2007 at 08:07:11PM -0500, Bruce Momjian wrote:
One problem with removing justify_hours() is that this is going to
return '24:00:00', rather than '1 day:
test= select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
00:00:00'::timestamptz;
?column?
--
24:00:00
(1 row)
---
Jim Nasby wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/
seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to
the
subtraction result for themselves. Not sure what the fallout would
be,
though.
I suspect there's applications out there that are relying on that
being nicely formated for display purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] can someone explain confusing array indexing nomenclature
Yes, it is confusing. I have an update to the array documentation that
should clarify it --- attached.
---
Achilleas Mantzios wrote:
? 16 ??? 2007 20:35, ?/? chrisj ??:
I am quite sure the [2] is not discarded, easy enough to test but I don't
have access to PG at the moment.
Well it should, since
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
text
-
{{meeting}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
text
---
{{meeting,lunch}}
(1 row)
dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
text
---
{{meeting,lunch}}
(1 row)
dynacom=#
Achilleas Mantzios wrote:
?? 15 ??? 2007 18:55, ?/? chrisj ??:
Thanks Achilleas,
I see what you are saying, but if we consider just the index [2] for a
moment,
it means something different depending upon the context (in one case it
means 2 and in the other case it means 1:2) and the context is
determined by the format of indexes on other dimensions.
I believe I understandbut incredibly confusing.
Now that i think about it again, i speculate that the [2] is discarded.
- chris
Achilleas Mantzios wrote:
?? 14 ?? 2007 21:31, ??/??
chrisj
:
given the following table:
protocal2= select * from sal_emp ;
name | pay_by_quarter | schedule
---+---+
--- Bill | {1,1,1,1} |
{{meeting,lunch},{training,presentation}}
Carol | {2,25000,25000,25000} |
{{breakfast,consulting},{meeting,lunch}}
(2 rows)
why do the following two queries yield different results??
protocal2= SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
schedule
--
lunch
(1 row)
protocal2= SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
schedule
---
{{meeting,lunch}}
(1 row)
The [n:m] notation denotes a slice of the array (not element).
So schedule[1][2] is the Array element on 2nd col of 1st row,
while schedule[1:1][2] could mean
the second row of the subarray schedule[1:1][1:2].
So these two are foundamentally different things.
In my 7.4 even if you gave
SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
you would still get {{meeting,lunch}} as a result.
(Right or wrong is another story).
Anyway the first time you query for a text,
the second time you query for a text[], so you should expect
different results.
--
Achilleas Mantzios
---(end of
broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/array.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/array.sgml,v
retrieving revision 1.56
diff -c -c -r1.56 array.sgml
*** doc/src/sgml/array.sgml 1 Feb 2007 00:28:16 - 1.56
--- doc/src/sgml/array.sgml 20 Feb 2007 03:43:28 -
***
*** 243,260
(1 row)
/programlisting
! We could also have written:
programlisting
- SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
- /programlisting
-
- with the same result. An array subscripting operation is always taken to
- represent an array slice if any of the subscripts are written in the form
- literalreplaceablelower/replaceable:replaceableupper/replaceable/literal.
- A lower bound of 1 is assumed for any subscript where only one value
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
One problem with removing justify_hours() is that this is going to
return '24:00:00', rather than '1 day:
test= select '2004-01-02 00:00:00'::timestamptz - '2004-01-01
00:00:00'::timestamptz;
?column?
--
24:00:00
(1 row)
---
Jim Nasby wrote:
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/
seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to
the
subtraction result for themselves. Not sure what the fallout would
be,
though.
I suspect there's applications out there that are relying on that
being nicely formated for display purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Bruce Momjian [EMAIL PROTECTED] http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Odd PL/PgSQL Error -- relation X does not exist when using index expression
Michael Fuhr wrote: On Thu, Feb 08, 2007 at 10:32:25AM -0500, Tom Lane wrote: My advice is not to try to execute multiple commands in the same EXECUTE string --- if we were going to do anything to fix this, I think it would be along the lines of enforcing that advice. Trying to make the world safe for it doesn't sound productive. The SPI_execute() documentation does mention that multiple commands are allowed: http://www.postgresql.org/docs/8.2/interactive/spi-spi-execute.html You may pass multiple commands in one string. SPI_execute returns the result for the command executed last. The count limit applies to each command separately, but it is not applied to hidden commands generated by rules. When read_only is false, SPI_execute increments the command counter and computes a new snapshot before executing each command in the string. Should that documentation be modified? Done, and attached. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/spi.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/spi.sgml,v retrieving revision 1.52 diff -c -c -r1.52 spi.sgml *** doc/src/sgml/spi.sgml 1 Feb 2007 19:10:24 - 1.52 --- doc/src/sgml/spi.sgml 18 Feb 2007 01:45:45 - *** *** 321,327 /para para !You can pass multiple commands in one string. functionSPI_execute/function returns the result for the command executed last. The parametercount/parameter limit applies to each command separately, but it is not applied to --- 321,328 /para para !You can pass multiple commands in one string, but later commands cannot !depend on the creation of objects earlier in the string. functionSPI_execute/function returns the result for the command executed last. The parametercount/parameter limit applies to each command separately, but it is not applied to ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] domains and serial
BillR wrote:
Is it not possible to set up a domain using this construct? I will have
a lot of tables using integer and big integer types as primary key
identifier fields. It would make things simpler if I can use the serial
construct in a domain instead of using an int or bigint in the domain,
and then change each field in each table requiring a serial or bigserial
construct.
I just tried to create a domain using a bigserial type.
CREATE DOMAIN Identifier_DM AS bigserial;
I received the error message:
ERROR: type bigserial does not exist
SQL state: 42704
I tried to run this in both pgadminIII and in data architect 3.5.
Yea, that isn't going to work because if you did that, all columns
created with that domain name would have the same default sequence.
See:
test= CREATE TABLE test(x bigserial);
NOTICE: CREATE TABLE will create implicit sequence test_x_seq for
serial column test.x
CREATE TABLE
test= \d test
Table public.test
Column | Type |Modifiers
++--
x | bigint | not null default nextval('test_x_seq'::regclass)
I don't see how we would ever get that working for domains. The best we
could do would be to have each new domain reference create a new
sequence and default string, but then you just have the bigserial
behavior in a domain, which doesn't seem worth it.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Case Preservation disregarding case
Tom Lane wrote: Ken Johanson [EMAIL PROTECTED] writes: Martijn van Oosterhout wrote: I think it's unlikely to happen anytime soon. The primary reason being that then you can no longer use indexes to search the catalog. Which I take a different opinion on this: -*If* the option to turn on case-insenetive behavior were selectable at the DB or session level, the existing apps could continue to use the case sensitve mode and be completely unaffected. Ken, you clearly fail to understand the point being made above. This is not something that is selectable at the session level. It won't work from either a functional or performance perspective. The real bottom line, though, is that this community has little respect for proposals that involve moving away from the SQL spec rather than closer to it; and that's what you're asking us to do. The spec is not at all vague about the case-sensitivity of identifiers. Now certainly we are not doing exactly what the spec says, but what you ask is even less like the spec's requirements. I think there are two major issues here: o if you quote identifiers that have any upper-case characters, do it both at table creation and use o display of non-quoted identifiers is lower-case I think we are OK making people either always quote, or always not quote. What we don't currently have a good answer for is people wanting the identifiers displayed using the original case. You can use quotes all the time of you want such display, but it is a pain. I think this is the crux of the complaints. Saving the case of the original creation and displaying that does work, but then it isn't clear if the identifier needs quotes (is the upper-case real or just for display). This gets us into even more confusion. Can someone can think of an answer to all this? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Case Preservation disregarding case sensitivity?
beau hargis wrote: Having installed DB2 Enterprise today and taking it for a spin, it does indeed behave in a similar manner. However, after reading through both specifications, it seems that DB2 follows more of the spec than PostgreSQL. The specifications state that for purpose of comparing identifiers, both shall be converted to upper-case. DB2 displays all identifiers in upper-case whereas PostgreSQL displays all identifiers in lower-case. This alone would be a deviation from the specification. True. We lowercase because historically we have, and because all-upper-case is hard to read. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Porting application with rules and triggers from PG 7.4.x
Tom Lane wrote: Andreas Haumer [EMAIL PROTECTED] writes: How can I get the functionality of an deferred AFTER trigger again with PostgreSQL 8? Use CREATE CONSTRAINT TRIGGER. The manual is fairly negative about this but I don't actually foresee it going away any time soon. Do we need to update the manual? -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] how to make infinite intervals?
Tilman Baumann wrote: I see there is a isfinite(interval) function. So there must be something like a infinite interval. An infinite interval would be yuite handy for me at the moment. But i have no clue how to make one. interval 'infinity' does not do the trick. :) On the TODO list: o Allow infinite dates and intervals just like infinite timestamps -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Interval subtracting
Stephan Szabo wrote: justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are = 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 = days 30, not merely days 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? I think it should be the later. It is best to have a single sign, and I think it is possible in all cases: '2 mons -1 days' could be adjusted to '1 mons 29 days'. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Scott Marlowe wrote: On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: Stephan Szabo wrote: justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are = 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 = days 30, not merely days 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? I think it should be the later. It is best to have a single sign, and I think it is possible in all cases: '2 mons -1 days' could be adjusted to '1 mons 29 days'. There's a part of me that thinks the WHOLE THING should be positive or negative: -(2 months 1 day) But it isn't '-2 months, -1 day'. I think what you are saying is what I am saying, that we should make the signs consistent. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing wrote: ?hel kenal p?eval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian: Stephan Szabo wrote: justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are = 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 = days 30, not merely days 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? I think it should be the later. It is best to have a single sign, and I think it is possible in all cases: '2 mons -1 days' could be adjusted to '1 mons 29 days'. But unfortunately '2 mons -1 days' '1 mons 29 days' If I want something to happen 1 day less than two months from dome date, then the only way to say that consistently *is* '2 mons -1 days'. Right, but you asked to justify the days by calling the function. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] CREATE TABLE AS and tablespaces
Markus Schaber wrote: Hello, Recently, I wanted to create a table from a complex query in a specific tablespace, but CREATE TABLE name AS SELECT ... does not accept a tablespace argument. I worked around it by CREATE TABLE name AS SELECT ... LIMIT 0, then moving the generated table to the other tablespace using ALTER TABLE, and then using INSERT INTO ... SELECT to generate the data into the table. But nevertheless, I'd like to ask here whether there are specific reasons for omitting the tablespace argument from the CREATE TABLE AS statement. If not, I'd like to request this minor feature :-) Maybe it is even possible to further unify CREATE TABLE and CREATE TABLE AS. This feature will be in 8.2: revision 2.530 date: 2006/02/19 00:04:27; author: neilc; state: Exp; lines: +16 -20 Add TABLESPACE and ON COMMIT clauses to CREATE TABLE AS. ON COMMIT is required by the SQL standard, and TABLESPACE is useful functionality. Patch from Kris Jurka, minor editorialization by Neil Conway. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Does PostgreSQL support job?
Daniel Caune wrote: Hi, I try to find in the documentation whether PostgreSQL supports job, but I miserably failed. Does PostgreSQL support job? If not, what is the mechanism mostly adopted by PostgreSQL administrators for running jobs against PostgreSQL? I was thinking about using cron/plsql/sql-scripts on Linux. The unix cron systems is what most people use. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Querying date_time for date only ?
Aarni Ruuhim?ki wrote:
Hello List,
I have a time stamp without time zone field, -MM-DD hh:mm:ss, in
my table. I want to also find something just for a particular day
regardless of the time.
(Pg)SQL way to do this ?
Yes. You can use date_trunc():
test= select date_trunc('day', '2004-01-04 04:02:03'::timestamp);
date_trunc
-
2004-01-04 00:00:00
(1 row)
so it would be date_trunc('day', col) = '2004-01-05'. The problem with
this is that you cannot use an index unless you create an expression
index on the date_trunc() function call. Another option is to do
something like:
WHERE col = '2004-01-04' AND col '2004-01-05'
If the date isn't a constant, you have to use date_trunc() on those, and
add one day to the second comparison:
WHERE col = date_trunc('day', col2) AND
col date_trunc('day', col2) + '1 day';
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does VACUUM reorder tables on clustered indices
Chris Browne wrote: [EMAIL PROTECTED] (Jim C. Nasby) writes: On Wed, Dec 21, 2005 at 12:34:12AM +0100, [EMAIL PROTECTED] wrote: Hi, Utilize bCLUSTER;/b (after vacuum) to reorder the data. Why would you vacuum when cluster is just going to wipe out the dead tuples anyway? There is one reason to VACUUM before running CLUSTER... That is that VACUUM will be *guaranteed* to draw all the pages into memory. Subsequently, you can be certain that the pages are in cache, and that the CLUSTER should need to do minimal I/O to read data into memory. If I'm considering clustering the Slony-I sl_log_1 table, forcing it into memory *is* something I'll consider doing in order to minimize the time that would-be writers are blocked from writing... Why don't you just do SELECT * FROM tab WHERE col != 'lkjasdflkjadsf'. That should pull things into memory without the VACUUM overhead. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Date Interval
Magdalena Komorowska wrote:
Hi,
I hale a problem with counting interwal and I can't find what to do with
this.
I have two fields in the table:
Column | Type | Modifiers
-+-+---
date_in | date|
interwal_months | numeric |
-+-+---
Query
SELECT date_in + INTERVAL '3 MONTH' FROM any_table
works fine of course.
However, how to do something like that?
SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table
How about this?
test= CREATE TABLE test(x TEXT);
CREATE TABLE
test= INSERT INTO test VALUES ('3');
INSERT 0 1
test= SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
test;
?column?
---
2006-03-06 11:53:05.574279-05
(1 row)
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] 'AS' in 'DELETE/UPDATE'
TODO has: o Allow an alias to be provided for the target table in UPDATE/DELETE This is not SQL-spec but many DBMSs allow it. so we want to add this capability some day. --- [EMAIL PROTECTED] wrote: Hi. Maybe I miss something but I can't use 'AS' with 'DELETE' (7.4.3) Example: db=# SELECT * FROM temp1 ; host_id | user_id | raw | uniq -+-+-+-- 2 | 1 | 125 | 85 2 | 2 | 100 | 50 (2 rows) And there is temp2 just like temp1. db=# DELETE FROM temp1 AS t1 WHERE EXISTS (SELECT 1 FROM temp2 AS t2 WHERE t2.host_id = t1.host_id AND t2.user_id = t1.user_id); ERROR: syntax error at or near AS at character 19 but db=# DELETE FROM temp1 WHERE EXISTS (SELECT 1 FROM temp2 AS t2 WHERE t2.host_id = temp1.host_id AND t2.user_id = temp1.user_id); DELETE 1 db=# SELECT * FROM temp1 ; host_id | user_id | raw | uniq -+-+-+-- 2 | 1 | 125 | 85 (1 row) It make me supply full name of table... Another example with UPDATE db=# UPDATE referer_total AS ref SET ref.raw = ref.raw + u.raw, ref.uniq = ref.uniq + u.uniq FROM temp1 AS u WHERE u.user_id = ref.user_id AND ref.referer = u.referer; ERROR: syntax error at or near AS at character 22 db=# UPDATE referer_total SET db-#referer_total.raw = referer_total.raw + u.raw, db-#referer_total.uniq = referer_total.uniq + u.uniq db-# FROM temp1 AS u WHERE u.user_id = referer_total.user_id db-# AND referer_total.referer = u.referer; ERROR: syntax error at or near . at character 46 So it make me rename temp1's raw to something else (r), uniq too; and 'AS' not possible too. And finally, working version: UPDATE referer_total SET raw = raw + r, uniq = uniq + u FROM temp1 AS u WHERE u.user_id = referer_total.user_id AND referer_total.referer = u.referer; It looks strange, are there any limitations or something else that make it not possible to use 'AS' in 'DELETE' and 'UPDATE'? Of course with described workarounds I can eliminate that problems, but I want to know is it so in 8.x? Or why, if it right behaviour? -- engineer ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] ?Equiv to oracle (ENABLE|DISABLE) (CONSTRAINT|TRIGGER) statements?
Please see the 8.1 beta release notes for new capabilities in that release. --- Bath, David wrote: Folks, Summary: Does postgresql have equivalents to the following Oracle statements? DISABLE CONSTRAINT ... ENABLE CONSTRAINT ... DISABLE TRIGGER ... ENABLE TRIGGER ... Background: One of the advantages of Oracle over some competitors such as MS-SQL and Sybase is the ability to toggle a constraint or trigger on and off, without blatting it, and without the hassle of finding any code and any accessory information (like comments, permissions...). BTW, I personally put C-style comments at the front of the clause so I can get the why's/how's into the syscatalogs - but I wear jackboots where documentation is concerned :-) and get at these for autodoccing and/or generation of meaningful messages to users when raising exception messages from the server. This capability is especially useful when there is some disgusting data-munging by a DBA, not just for import/export. I've tried grovelling through the sql from a pg_dump invoked with --disable-triggers, but it has no enable/disable triggers or constraints, merely creating primary/foreign constraints AFTER issuing the COPY. Yep, I'd expect this ONLY to work when issued by someone with DBA privs (and maybe the target object owner, although I imagine reasons that /might/ be a bad idea for paranoid info management governance). Thanks in advance -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] empty view, replace view, column type change?
Tom Lane wrote: If you SELECT 'abc'::varchar(6)::varchar, the end type is varchar(6). Yeah, this is exactly what's happening --- if you look at gram.y, LOCALTIMESTAMP is expanded to 'now'::text::timestamp(6). I am unconvinced that the (6) is a very good idea though. The code says /* SQL99 mandates a default precision of 6 for timestamp. * Also, that is about as precise as we will get since * we are using a microsecond time interface. * - thomas 2001-12-07 */ d-typmod = 6; but it doesn't seem to me to follow from what the spec says that we need to explicitly cast the result of now() to six places. As long as it's coming from gettimeofday it can't have more than 6 places anyway, and so we might as well save the extra coercion step. (The parser *will* tack on a separate coercion function call when presented with this parse tree.) In short, I'm inclined to remove the above-quoted lines, and similarly for CURRENT_TIME, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? Agreed. That 6 bothered me too when I was cleaning up the timestamp code a while back. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Joe Conway wrote:
Tom Lane wrote:
I think he's got a good point, actually. We document the ARRAY-with-
parens-around-a-SELECT syntax as
The resulting one-dimensional array will have an element for
each row in the subquery result, with an element type matching
that of the subquery's output column.
To me, that implies that a subquery result of no rows generates a
one-dimensional array of no elements, not a null array.
OK, looks like I'm outnumbered.
But as far as I know, we have never had a way to produce a
one-dimensional empty array. Empty arrays thus far have been dimensionless.
Assuming we really want an empty 1D array, I created the attached patch.
This works fine, but now leaves a few oddities to be dealt with, e.g.:
regression=# select array_dims(array(select 1 where false));
array_dims
[1:0]
(1 row)
Any thoughts on how this should be handled for an empty 1D array?
The point Markus is complaining about seems like it should
be easily fixable.
Well, easily is a relative term. My Postgres hacking neurons have
gotten kind of rusty lately -- but then maybe that was your underlying
point ;-)
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test= select array_dims('{}'::integer[]);
array_dims
(1 row)
Why is [1:0] wrong to return?
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}
Joe Conway wrote:
Bruce Momjian wrote:
Joe Conway wrote:
Any thoughts on how this should be handled for an empty 1D array?
No one responed to this email, so I will try. Is this the one
dimmentional array you were talking about?
test= select array_dims('{}'::integer[]);
array_dims
(1 row)
In this case, what you get is actually a dimensionless array. Literally,
you get this:
if (nitems == 0)
{
/* Return empty array */
retval = (ArrayType *) palloc0(sizeof(ArrayType));
retval-size = sizeof(ArrayType);
retval-elemtype = element_type;
PG_RETURN_ARRAYTYPE_P(retval);
}
I.e. the array structure is allocated, the size is set (which is
required since arrays are varlena), and the element type is initialized.
There is no initialization of ndim, ARR_DIMS(), or ARR_LBOUND().
In this case, since there are no dimensions, array_dims() probably does
the right thing by returning NULL.
Why is [1:0] wrong to return?
I'm not sure it is wrong -- it just seems a bit strange. The difference
is that in order to return an empty *one-dimensional* array, ndim,
ARR_DIMS(), and ARR_LBOUND() are all appropriately set (by the patched
code). Basically, ndim == 1, ARR_DIMS() is a single element int array (C
array that is) indicating 0 elements for dimension 1, and ARR_LBOUND()
is a single element int array indicating a lower bound of 1. This leads
to the array_dims() return value of [1:0]. The value 1 is unquestionably
correct for the lower bound index, but what should be reported for the
upper bound? We can't return [1:1], because that would indicate that we
have one element.
OK, so '[1:0]' seems correct. How would to specify such an array
manually in a string?
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Query history file
Mauro Bertoli wrote: Hi, I've installed a Postgres 8.0. There's a history file with all executed queries? If you are using psql, \s will show you your old queries. From the server side, if you enable 'log_statement' all queries will go into the server logs. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] PostgreSQL ignores my indexes
Andrew Sullivan wrote: This is probably better on -performance, and is certainly a FAQ. But. . . On Wed, Feb 23, 2005 at 03:01:52PM +0100, Thomas Braad Toft wrote: Table device contains 5285 rows, tmeevent contains 834912 rows. ^^ - Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138) (actual time=0.04..2193.97 rows=834912 loops=1) ^^ - Seq Scan on device (cost=0.00..564.85 rows=5285 width=29) (actual time=0.04..25.07 rows=5285 loops=1) Why isn't the planner using my indexes? I tried making them as both rtree Because there's no advantage to using an index when you are fetching 100% of both tables. This is the most efficient plan. Of course, it's an open question whether you want to get 100% of both tables. But that's what you're doing, and using the index would be more expoensive than this. Right. The FAQ addresses this issue. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Unresolved external: tgetent
Would you please test 8.0RC release on the ftp site. It might already be fixed. --- Christoph Haller wrote: I doubt this is the right list, but it's the only one I'm subscribed to, so please, if one of the core members is reading this, forward it to the right one. I have successfully compiled and installed 7.4.5 yesterday. After doing 'make' I saw the line Ready to install. template1=# select version(); version PostgreSQL 7.4.5 on hppa2.0w-hp-hpux11.00, compiled by GCC gcc (GCC) 3.3.1 (1 row) But when I started psql it immediately core dumped with libreadline.sl.5 Unresolved external: tgetent I did a search on the mailing list archives and got 63 hits on 'tgetent', dating from 1997 to 2004, all having the same problem, the missing '-ltermcap' in LIBS in src/Makefile.global. After distclean and doing configure LIBS=-ltermcap ... and make again it finally worked. In http://archives.postgresql.org/pgsql-bugs/2001-09/msg00023.php Peter Eisentraut writes LIBS = -lz -lresolv -lcompat -lm -lutil -ltermcap -lreadline That's a good fix. The current CVS tip also works around this problem. But the 7.4.5 src/Makefile.global still has this: LIBS = -lz -lreadline -lPW -lgen -lBSD -ldld -lnsl -lm So it appears this fix must have been lost anywhere. Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] TEXT::CIDR/INET::CIDR output confusion
Added to TODO list: * Prevent INET cast to CIDR from droping netmask, SELECT '1.1.1.1'::inet::cidr It is probably related to the TODO item above it: * Prevent INET cast to CIDR if the unmasked bits are not zero, or zero the bits I think the original code thought CIDR and INET where identical types that could be cast with no changes but we are finding that was incorrect. --- Alexander M. Pravking wrote: It looks a bit strange that CIDR output depends on datatype it has been casted from: fduch=# SELECT '1.1.1.1'::cidr; cidr 1.1.1.1/32 (1 row) fduch=# SELECT '1.1.1.1'::inet::cidr; cidr - 1.1.1.1 (1 row) However these two seem to be 'equal' in terms of backend: fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::inet; ?column? -- t (1 row) fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::cidr; ?column? -- t (1 row) I'm just curious how can it even be... fduch=# SELECT version(); version PostgreSQL 7.4.5 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] session_id
Riccardo G. Facchini wrote: No, I can't provide it because your'e right. currval() is NOT affected by other sessions. Thanks anyway for the suggestion, using pg_backend_pid() solved the uniqueness I needed. I'll keep the nextval/currval for another opportunity. FYI, we needed a unique-through-time session id for the log_line_prefix so we used the seconds-since-1970-dot-pid. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Implicit Transactions
Chip Gobs wrote: We are porting from Informix to PostgreSQL 7.4.5 and have noticed the following behavior. If we try to OPEN a CURSOR for an invalid SELECT statement in ECPG, we get an error, as expected. However, if we then attempt to OPEN another CURSOR for a valid statement, we get an error that says we are in a failed transaction. At that point, no statement will succeed. The only way we have found to get out of this state is to ROLLBACK explicitly. We are not using explicit transactions. My understanding is that PG should be rolling back failed statements when we are not in an explicit transaction. Do we have an incorrect setting, a misunderstanding of how it is supposed to work, or a bug? Could anyone enlighten me? ecpg defaults to open transaction by default. There is a command to change that in ecpg or during compile I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] aggregate function stddev
Is this a TODO? --- Josh Berkus wrote: Kemin, Just noticed that the postgres stddev is the stddev_sample formula. There are two different ways to calculate this value. Their difference is very small with large samle size. It would be nice to distinguish the two different versions. Note sent to PGSQL-DOCS. I also noticed that oracle has stddev_sample and stddev_population. This is just a wish list. Were you aware that in PostgreSQL you can write your own aggregates? It's relatively easy to do. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] /contrib and win32
Theo Galanakis wrote: Im running/playing with PG 8.0 locally and want to install the contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a GMAKE.exe for Windows?? Someone enlighten me! Uh, good question. I think you need the msys/mingw environment to add contrib stuff, and this definately is a problem for many users. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] sleep function
John DeSoi wrote: Is there a sleep function of some kind? I wanted to simulate a query taking a long time to execute for testing purposes. I can't think of one, no. I think you will have to use one of the server-side languages and call a sleep in there. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Why no exprs in format string to RAISE
Richard Huxton wrote: Markus Bertheau wrote: Hi, is there a non-implementation reason as to why there are no expressions allowed in the arguments to the format string to RAISE, or is that just not implemented yet? Just not done yet AFAIK. It would require some changes to the parser for plpgsql, but nothing too extreme. Might make a good project for someone learning more about PG's code. TODO has: o Allow PL/PgSQL's RAISE function to take expressions -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] array_lower /array_prepend doubt
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: We may need another extension to the array literal syntax in order to deal with this. I'll report back after I've had some time to study it. There already is support in array_in for specification of the array dimensions (though it may be suffering bit rot for lack of use/testing). I think the main thing needed is some thought about when array_out should print dimensions; we don't want it doing so all the time, for both clutter and backwards compatibility reasons. Maybe whenever any lower bound is not 1 would do; or maybe we want to invent a GUC switch to control its behavior. Is this a TODO? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Server Side C programming Environment Set up
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I'd be happy to write more documentation, howtos, or scripts and tools that enable users to set up a proper build system, but I don't think it's our business to try to write our own build system framework. Any build framework is going to have limitations, obviously, and it would be bad to design things to prevent subprojects from having their own. But I think that is no argument for not having a build framework at all. If we try to go that route, we'll be killing a lot of useful code that isn't quite valuable enough (in isolation) for people to expend the work to create their own build system for. Even more to the point, we've already *got* a build framework, which by demonstration works for many of the bits of code that we are talking about spinning off. What we have to do is adjust it so it still works for them after they're spun off. It seems silly to abandon the not-trivial work you and other people have already put into the contrib build system; and also silly to expect gborg projects to individually adapt it to their needs. Bingo! We have often had the attitude If we can't do it perfectly, don't do it. While that applies is some cases, it doesn't apply everywhere, and we need to be wise in determining when we are helping people by giving them a 99% solution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] lifetime of temp schema versus compiled image of plpgsql proc
There is an FAQ item on this --- use EXECUTE. --- Dennis wrote: Hi, this is pg 7.4.1 I am opening a connection to postgres starting a transaction executing a plpgsql function that creates temp tables executing a plpgsql function that populates the temp tables querying the temp table closing the transaction then on the same connection, I open a transaction, execute a plpgsql function that populates the temp tables and the function bombs with this error message: ERROR: schema pg_temp_8 does not exist I am not specifying on commit when creating the temp tables. Are temp tables created in a transaction discarded when the transaction ends? I'm not explicitly referencing pg_temp_8 in my stored function. Can someone explain what is going on? Have I given enough information? dennis pg-user at calico dash consulting dot com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Querying From two databases
Pallav Kalva wrote: Hi, I am new to postgres and I need to do a query which joins two tables from different databases. Can you please advice me on how to achieve this in postgres. Sure, see /contrib/dblink in the source distribution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Crypt() encryption
Christina Zhang wrote: Hello, We are planning to use Crypt() to encrypt the application level users' passwords. Our question is that which encryption method the Crypt() is using? (DES, or some other encryption algorithms?) Because we concern about the strenth of the Crypt(). Use MD5. That is the best method for this. There is an md5() function already in the database, and there is /contrib/pgcrypto. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Standatd
Martin Marques wrote: El Mi? 07 Abr 2004 11:26, escribi?: Ricardo Vaz Mannrich wrote: Thank you. I think there is an error in the Develpoer's FAQ... http://developer.postgresql.org/ http://developer.postgresql.org/readtext.php?src/FAQ/FAQ_DEV.html+Develop ers-FAQ A lot of ugly characters. Until we fix the web site, please see doc/src/FAQ/FAQ_DEV.html in the source tree. http://db.konkuk.ac.kr/present/SQL3.pdf doesn't exist anymore. Thanks. Removed from FAQ_DEV. Also, for some reason I can't get to sqlstandards.org. Is there any other place where I can get the SQL200X docs? Not sure. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL Standatd
Joe Conway wrote: Bruce Momjian wrote: Martin Marques wrote: Also, for some reason I can't get to sqlstandards.org. Is there any other place where I can get the SQL200X docs? Not sure. See: http://www.wiscorp.com/sql/sql_2003_standard.zip Thanks, I have added this to the developer's FAQ, and removed the sqlstandads.org url. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Can statement_timeout emulated NOWAIT?
Yes, I think these are all correct, except I am not positive FOR UPDATE NOWAIT will be in 7.5 unless someone codes it. --- Luis P Caamano wrote: I've researched the recent discussions about statement_timeout and NOWAIT and I'd like to confirm my understanding of the situation before acting on it. Please let me know if the following statements are true: - LOCK TABLE ... NOWAIT has been checked in and will be available in 7.5 - SELECT ... FOR UPDATE NOWAIT is in the TODO list (among other NOWAIT commands) but it will also be available in 7.5 - Using SET LOCAL statement_timeout=xxx can be used before SELECT ... FOR UPDATE to emulate NOWAIT when the select is simple and involves a relatively small number of rows. That is, the following: BEGIN; SET LOCAL statement_timeout=3000; SELECT ... FOR UPDATE; -- (1 row) COMMIT; will be equivalent to: BEGIN; SELECT ... FOR UPDATE NOWAIT; COMMIT; - The NOWAIT feature will be able to handle select-for-updates that involve many rows better than statement_timeout because it will track timeouts on a row by row basis, which eliminates false positives. That is, if the statement_timeout is too short for the select, it might fail before finishing the select instead of failing because of locked row. - The error raised by statement_timeout and NOWAIT will be the same. Basically, I'd like to write low level code that implements NOWAIT in the application using statement_timeout. Later on, 7.5, I'd be able to reimplement the low level functions using NOWAIT without having to change the higher level code. Your thoughts and comments are appreciated. Thanks Luis P Caamano Atlanta, GA, USA ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] User defined types -- Social Security number...
Josh Berkus wrote: Greg, Anyone have a good pre-built user-defined type definition for creating / maintaining / manipulating a SSN ... where valid chars are in the range 000-00- through 999-99-. Actually, the range is more narrowly defined than that. I'm not sure of the exact rules, but you will never see a leading 0 or a -00- in an SSN. Actually I know someone who's SSN starts as 003. Agreed on the DOMAIN suggestion. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Enterprice support in PostgreSQL
George A.J wrote: Hi all, We are providing database solutions in postgreSQL... Now using PostgreSQL 7.3. It is performing well. But Now we have some enterprice level requirements. One of Our requirement is to provide a distributed solution in PostgreSQL. The questions are... 1. Is it posible to provide a distributed solution in PostgreSQL. 2. Does PostgreSQL Support distributed transactions. 3. If not does it included in the next release. Or when will be the distributed version of postgreSQL available. 4. Is there a replication solution availbale for postgreSQL. 5. Does postgreSQL support 2 phase commit and Distributed transaction standards. 6. Is there a transaction manager(or co-ordinater) available for postgreSQL. 7. Can we use MTS (Microsot Transaction Server) wtih postgreSQL. 8. Does postgreSQL support Load balancing and all other enterprice features. Can we expect these features in the next version.. We are working on all these fronts. We have replication solutions on gborg.postgresql.org, and some are working on 2-phase commit, perhaps for 7.5, dues in maybe 6 months. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] array_lower /array_prepend doubt
Joe Conway wrote: Bruce Momjian wrote: Is this a TODO? Probably -- something like: Modify array literal representation to handle array index lower bound of other than one Added to TODO. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What's wrong with COPY rights in 7.3.4?
COPY is done with the rights of the server backend, not your client. You can also use \copy in psql and that is done in the client. --- Olivier Hubaut wrote: Hi, I tried to use the COPY function in v7.3.4 but I have a problem. According to the documentation, anyboy having the good rights on the table he want to copy could do it without any problem. When I tried it, I found that using this command is impossible if you aren't an administrator for the instance of PostgreSQL. Is it a bug or a *feature*? The commands I tried are the followings: COPY table FROM '/file.sql'; and COPY table TO '/file.sql'; Thanks for responding. -- Ci-git une signature avortee. ** RIP ** ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Is it possible to set a NOT NULL constraint deferrable?
Olivier Hubaut wrote: I can put all the other constaints deferrable, but the *NOT NULL* one seems to be undeferrable. Is ther a way to by-pass this or is do you know if this is planned in the future versions? Only foreign key constraints are deferrable. Many want UNIQUE to be deferrable, but you are the first to ask for NOT NULL. Not sure when this will be done. Sorry. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] WITHOUT OIDS by default
ow wrote: Hi, Is there a way to specify that all tables should be created WITHOUT OIDS by default? No, and strangely it wasn't on the TODO list. I just added it: * Add GUC setting to make created tables default to WITHOUT OIDS -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] pg 7.4.rc1, Range query performance
ow wrote: Hi, I tried CLUSTER and it did improve performance, somewhat. The query against clustered table performs about five (5) times better than the same table but non-clustered. However, even after that table was clustered, the difference in performance between single record query and range query is significant: table Test (see below) has 10M records single record - 31 ms and remains mostly constant as table grows range query returning 30 records - about 10 secs and grows together with the table Also, CLUSTER is locking the table (in our case this also means locking the database), so it may be impossible to use it in production on large tables (impossible in our case). It feels like I really have a problem here. Any ideas? Thanks P.S. For the future I would consider implementing CREATE [CLUSTERED] INDEX Strange 30 records takes 30x the time than one record. Can you run ANALYZE and send us an EXPLAIN of the query to make sure it hasn't changed? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] pg 7.4.rc1, Range query performance
ow wrote: --- Bruce Momjian [EMAIL PROTECTED] wrote: Strange 30 records takes 30x the time than one record. Can you run ANALYZE and send us an EXPLAIN of the query to make sure it hasn't changed? explain analyze select * from Test where a = '2002-06-18' and a = '2002-07-18' and b = 5 and c = 255 QUERY PLAN Index Scan using ak_abc on test (cost=0.00..121.23 rows=34 width=53) (actual time=18.060..10726.387 rows=31 loops=1) Index Cond: (((a)::timestamp without time zone = '2002-06-18 00:00:00'::timestamp without time zone) AND ((a)::timestamp without time zone = '2002-07-18 00:00:00'::timestamp without time zone) AND ((b)::integer = 5) AND ((c) (..) Total runtime: 10726.663 ms OK, I see now. You must have a lot of rows from '2002-06-18' to '2002-07-18', but only 33 with the b,c conditions --- not much we can do to speed this up because the condition on 'a' isn't restrictive enough --- not sure if b or c is either. It is all those lookups to find the rows that match a, then b/c that is taking the time. In fact, it now make sense that it takes 30x time because all the time is spent traversing the index looking for match #1, then match #2, etc. We would do this quickly if there were lots of rows matching a specific 'a' value, e.g. explain analyze select * from Test where a = '2002-06-18' --- and b = 5 and c = 255 The index/cluster is grouping the rows, but the grouping is by timestamp value, not by range = '2002-06-18' and = '2002-07-18'. Even though you have index a,b,c, it really is only using the index on 'a' because the index on b,c only happens when you have multiple duplicate 'a' values, but in this case you have an entire months worth. The only quick way would be to create a functional index on 'a', and cluster on that: create index ii on x (date_part(month, a), b,c) or something like that. You can't actually index on a function and then ordinary columns so you would need a pretty fancy function in plpgsql that converted the a,b,c value into a nice text string and then index on that. Then if you used that function call in your WHERE clause, the index would be used and it would be very fast because all your 'a' values would be the same, and it could then jump to b and c quickly. Sorry there isn't a simple solution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] pg 7.4.rc1, Range query performance
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Sorry there isn't a simple solution. But there is: make an index with the column order (b,c,a). Oh, yea, right. If he puts the columns he is doing a straight equals comparison first, the 'a' comparison will work fine. Great. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 7.3 how remove password valid until
alban wrote: How remove a password validity ? CREATE USSER x WITH VALID UNTIL 'uu' ALTER USSER x WITH VALID UNTIL 'uu' but how remove password valid until ? I think you have to specify the data as 'infinity'. test= CREATE USER x WITH VALID UNTIL '2005-01-01'; CREATE USER test= ALTER USER x WITH VALID UNTIL 'infinity'; ALTER USER -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 7.3 : how add user when last user have lost 'createuser' option
alban wrote: i have execute ALTER USER postgres WITH NOCREATEUSER; it's work very well but now, i cant add, drop, alter user, alter group... ALTER USER postgres WITH CREATEUSER; -- ERROR : ALTER USER : permission denied there is a solution ? Uh, you removed createuser permission from the postgres super-user. I think that's why you are getting the failure. I think you have to manually update pg_shadow to fix this, if it will allow that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg 7.4.rc1, Range query performance
Try CLUSTER --- that usually helps with index scans on ranges. --- ow wrote: Postgresql 7.4.rc1 on i686 pc linux compiled by gcc 2.06. All configuration settings are default. Hi, Trying to find a way to improve range query performance. The table Test has about 30 million records. -- DLong, Dtimestamp, Dint, etc are domains of the respective types. create table Test ( id DLong not null, a Dtimestamp null, b Dintnot null, c Dintnot null, d Dstring null, constraint PK_id primary key (id), constraint AK_abc unique (a, b, c) ); The following query retrieves a single record, it runs against AK index and is extremely fast (30-150 ms) for the table of this size: -- returns result in 30-150 ms select * from Test where a = '2002-09-01' and b = 5 and c = 255 OTOH, the following range query that returns 30 records performs much slower, about 33000 ms. The query is using AK index, as it should, but why does it take so much longer to scan the index for the range of just 30 records? I see that PG is hitting the disk very intensively for this query. Can the query be rewritten, etc to improve performance? Thanks select * from Test where a = '2002-09-01' and a = '2002-09-30' and b = 5 and c = 255 QUERY PLAN Index Scan using ak_abc on test (cost=0.00..106.27 rows=30 width=53) (actual time=33.536..33200.998 rows=30 loops=1) Index Cond: (((a)::timestamp without time zone = '2002-09-01 00:00:00'::timestamp without time zone) AND ((a)::timestamp without time zone = '2002-09-30 00:00:00'::timestamp without time zone) AND ((b)::integer = 5) AND ((c) (..) Total runtime: 33201.219 ms __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Expressional Indexes
Josh Berkus wrote: Tom, Mainly that expressional is a made-up word. So? We're in the tech biz, Tom. New-coined words are expected. And that way nobody will expect it to mean something else, since we made it up. -- H. Dumpty, Q.E.D. I have been considering using calculated index or computed index but dunno if that really conveys anything. Well, Expression Indexes is the most accurate. Or Expression-Based Indexes. Computed index sound too much like there is computation done during the index lookup, which there isn't, and it sounds like it would behave differently from a normal index, which it doesn't. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pg_class.relpages
Bertrand Petit wrote: Does the figures stored in pg_class.relpages include the pages consumed by the toast tables linked to a normal table? No. See the chapter on monitoring disk space for more information. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean
Where are we on this --- we all decided on #4. Does this just require an announcment in the release notes. (I need to complete the release notes soon.) --- Tom Lane wrote: Following up this gripe http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php I've realized that plpgsql just assumes that the test expression of an IF, WHILE, or EXIT statement is a boolean expression. It doesn't take any measures to ensure this is the case or convert the value if it's not the case. This seems pretty bogus to me. However ... with the code as it stands, for pass-by-reference datatypes any nonnull value will appear TRUE, while for pass-by-value datatypes any nonzero value will appear TRUE. I fear that people may actually be depending on these behaviors, particularly the latter one which is pretty reasonable if you're accustomed to C. So while I'd like to throw an error if the argument isn't boolean, I'm afraid of breaking people's function definitions. Here are some possible responses, roughly in order of difficulty to implement: 1. Leave well enough alone (and perhaps document the behavior). 2. Throw an error if the expression doesn't return boolean. 3. Try to convert nonbooleans to boolean using plpgsql's usual method for cross-type coercion, ie run the type's output proc to get a string and feed it to bool's input proc. (This seems unlikely to avoid throwing an error in very many cases, but it'd be the most consistent with other parts of plpgsql.) 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) Any opinions about what to do? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Datafiles for Databases
Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Kumar) wrote: I was looking for a structure like oracle or ms Sql?server in Postgres. What I have expected is individual datafiles for individual databases. But i cant fine such items in the /usr/local/pgsql/data directory. Will the postgres create individual data file for databases? No. Just as with Oracle, PostgreSQL creates multiple files for each database. How to get the datafile path of a database? select oid, datname from pg_database; The path for database 'datname' will be: $PGDATA/base/oid/ See the Monitoring Disk Space section in our manuals. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] plpgsql doesn't coerce boolean expressions to boolean
Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] writes: On Mon, 08 Sep 2003 11:40:32 -0400, Tom Lane [EMAIL PROTECTED] wrote: 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans will be accepted in exactly the same cases where they'd be accepted in a boolean-requiring SQL construct (such as CASE). (By default, none are, so this isn't really different from #2. But people could create casts to boolean to override this behavior in a controlled fashion.) I vote for 4. I'm willing to do that. OK, what release should we do this? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] undefine currval()
Chris Gamache wrote: I'm using sequences and currval() to retrieve the last inserted row in a table. If currval() is undefined, as it is when a connection is made, then I know no rows were inserted in that table and can take a different action. This is problematic when using a connection pooling library, as the value of currval() for any given sequence could possibly be set from a previous connection. One (theoretical) workaround would be to issue some sort of command to the back-end database to wipe all values of currval() when a new connection is made. I've done some digging in the system tables and source code, and can't find an obvious solution. Perhaps one you you gurus can suggest a SQL statement to do such a thing. Alternately, if there is a better way to retrieve the last inserted row for any given table, I'd be very grateful for the tip. It would need to be independent of the connection history, and undefined if there has not been a row inserted to the table during a definable interval of time (drop anchor when the connection begins, raise anchor when the connection ends), and be independant of the other connections inserting rows to the same table. I don't know how you could have an application that doesn't know if it has issued a nextval() in the current connection. Unless you can explain that, we have no intention of playing tricks with currval() for connection pooling. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [BUGS] session variable
Peter Eisentraut wrote:
sad writes:
The first problem using temp table is:
CREATE TABLE locals (name text, value text);
CREATE OR REPLACE FUNCTION test_locals_access() RETURNS text AS '
DECLARE i text;
BEGIN
SELECT value INTO i FROM locals WHERE name=''n1''
RETURN i;
END;
' LANGUAGE 'plpgsql';
SELECT test_locals_access() ;
column
-
null
CREATE TEMP TABLE locals (name text, value text);
INSERT INTO locals VALUES ('n1','xxx');
SELECT test_locals_access() ;
column
-
null === the Function seing global table
This is a problem in plpgsql, not in temporary tables.
Yes, see the FAQ. You have to use EXECUTE for temp table access in
functions.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] postgres 7.1.3: why does the query plan ignore indexes?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Should we consider adding some warning when someone creates an index on an int2 column? I don't think so. Better to expend our energy on solving the fundamental problem. I am thinking _until_ we fix the problem. Is it actually hard to add? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] postgres 7.1.3: why does the query plan ignore indexes?
Should we consider adding some warning when someone creates an index on an int2 column? --- Tom Lane wrote: Marco Vezzoli [EMAIL PROTECTED] writes: ewsweb_test= \d measures_product Index measures_product Attribute | Type +-- product_id | smallint ewsweb_test= explain select zero_yield, gross from measures where product_id=29 and date between '2003-03-12' and '2003-08-14'; ^ 29 is taken as an integer (int4). To get an indexscan on an int2 column, you need to explicitly cast it to int2: product_id = 29::smallint or you can put it in quotes and let the parser figure out the right type: product_id = '29' Yes, we'd like to make this better, but there are surprisingly many pitfalls in tinkering with the assignment of datatypes to constants... regards, tom lane PS: you could also consider whether it's really saving you any space to store product_id as a smallint instead of int. Because of alignment considerations, it very possibly isn't. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Added. --- Josh Berkus wrote: Bruce, OK, so what should the TODO item be? Go with the simple and intuitive: EXECUTE query_var INTO record_var; -- -Josh Berkus Aglio Database Solutions San Francisco -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Does Oracle have a syntax for this?
---
Robert Treat wrote:
On Wednesday 23 July 2003 19:06, Bruce Momjian wrote:
Robert Treat wrote:
On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
FOR myrec IN EXECUTE myinfo LOOP
biglist := myrec.info;
END LOOP;
One other thing, I hate when I have to do things like the above, can we
get a TODO like:
allow 'EXECUTE var INTO record' in plpgsql
So the TODO would be?
Allow PL/pgSQL EXECUTE to return a single record outside a loop
that's what I wrote, but not what I meant :-) I do like the sound of it
though, but really what I meant to say was:
EXECUTE var1 INTO var2
but this assumes a number of things, namely that executing var1 will return
only one field, and one row. I guess that would be:
Allow PL/pgSQL EXECUTE to return a single variable outside a loop
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Robert Treat wrote: On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote: FOR myrec IN EXECUTE myinfo LOOP biglist := myrec.info; END LOOP; One other thing, I hate when I have to do things like the above, can we get a TODO like: allow 'EXECUTE var INTO record' in plpgsql So the TODO would be? Allow PL/pgSQL EXECUTE to return a single record outside a loop -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] min() and NaN
Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. When doing ORDER BY, we have to put the NULL value somewhere, so we put it at the end, but with aggregates, we aren't required to put the NULL somewhere, so we ignore it. Should that be the same for NaN? I just don't see how we can arbitrarly say it is greater/less than other values. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? It'll only take ten minutes to make it a DONE, once we figure out what the behavior ought to be. So far I think both Stephan and I argued that MIN/MAX ought to treat NaN as larger than all ordinary values, for consistency with the comparison operators. That was not the behavior Michael wanted, but I don't see that we have much choice given the wording of the SQL spec. Does anyone want to argue against that definition? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] min() and NaN
Stephan Szabo wrote: On Tue, 22 Jul 2003, Bruce Momjian wrote: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. When doing ORDER BY, we have to put the NULL value somewhere, so we put it at the end, but with aggregates, we aren't required to put the NULL somewhere, so we ignore it. Should that be the same for NaN? I just don't see how we can arbitrarly say it is greater/less than other values. But we already do. When doing a less than/greater than comparison, 'NaN' is considered greater than normal values which is different from NULL which returns unknown for both. But maybe that logic is the same as ORDER BY, where we have to give it some location in sorting order, while with aggregates we don't. I am not strong on this, but just point it out. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] min() and NaN
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Well, my 2 cents is that though we consider NULL when ordering via ORDER BY, we ignore it in MAX because it really isn't a value, and NaN seems to be similar to NULL. Good idea, but I don't think we can get away with it. The spec says that MAX/MIN have to be consistent with the comparison operators (and therefore with ORDER BY): iii) If MAX or MIN is specified, then the result is respec- tively the maximum or minimum value in TXA. These results are determined using the comparison rules specified in Subclause 8.2, comparison predicate. NULL can be special, because it acts specially in comparisons anyway. But NaN is just a value of the datatype. I'd be willing to go against the spec if I thought that having ignore-NaNs behavior was sufficiently important, but I don't think it's important enough to disregard the spec... Yep. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] avoid select expens_expr(col) like unneccessary calculations
Stephan Szabo wrote: On 8 Jul 2003, Markus Bertheau wrote: when you have select expensive_expression(column), * from table offset 20 limit 40 can you somehow save the cost for the first 20 calculations of expensive_expression? Right now the only way I can think of that might work is to push the offset/limit into a subselect on table and then do the expensive_expression at the top level. Well, you can do: SELECT * FROM (SELECT * FROM pg_class) AS pg_class so you could do: SELECT func(relname), * FROM (SELECT * FROM pg_class OFFSET 20 LIMIT 40) AS pg_class -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] OR vs UNION
Gavin reported UNION faster than OR in some case when doing fts queries two years ago at O'Reilly. --- [EMAIL PROTECTED] wrote: Actually, I have used a UNION to replace OR's, the case (simpliefied to) something like this: Sample 1: WHERE (f1 = 'v1' OR f1 = '') AND (f2 = 'v2' OR f2 = '') Changed to Sample 2: WHERE (f1 = 'v1') AND (f2 = 'v2') UNION WHERE (f1 = 'v1') AND (f2 = '') UNION WHERE (f1 = '') AND (f2 = '') Note that Sample 1 is actually a simplified version, the queries are not exactly equivalent. The point is that sample 2 ran MUCH faster because: a) The table was *very* large b) The OR clauses of sample 1 prevented the use of an INDEX, Reason: It is faster to scan an index 3 times then scan this very large table once. I do not know if there is a proof to say that one can *always* replace OR's with a union, but sometimes certainly, and in this case it made things much better... Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus Sent: Thursday, July 17, 2003 3:00 PM To: Scott Cain; [EMAIL PROTECTED] Subject: Re: [SQL] OR vs UNION Scott, I have a query that uses a series of ORs and I have heard that sometimes this type of query can be rewritten to use UNION instead and be more efficient. I'd be interested to know where you heard that; as far as I know, it could only apply to conditional left outer joins. select distinct f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id from feature f, featureloc fl where (f.type_id = 219 OR f.type_id = 368 OR f.type_id = 514 OR f.type_id = 475 OR f.type_id = 426 OR f.type_id = 456 OR f.type_id = 461 OR f.type_id = 553 OR f.type_id = 89) and fl.srcfeature_id = 1 and f.feature_id = fl.feature_id and fl.fmin = 2491413 and fl.fmax = 2485521 Certainly a query of the above form would not benefit from being a union. For readability, you could use an IN() statement rather than a bunch of ORs ... this would not help performance, but would make your query easier to type/read. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] min() and NaN
Is this a TODO? --- Tom Lane wrote: Jean-Luc Lachance [EMAIL PROTECTED] writes: If a compare with NaN is always false, how about rewriting it as: result = ((arg1 arg2) ? arg2 : arg1). That just changes the failure mode. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] inet versus text for ip addresses
Dan Langille wrote:
The PostgreSQL inet datatype stores an holds an IP host address, and
optionally the identity of the subnet it is in, all in one field.
This requires 12 bytes.
Using my random data of approximately 8000 IP addresses collected
during previous polls, I've found the average length of an IP address
is 13.1 bytes.An integer requires 4 bytes.
First question: Why not store an option to store just an IP address?
That should require less than the 12 bytes for inet.
We store inet and cidr in similar structures, and they are of variable
length (4 byte overhead):
/*
* This is the internal storage format for IP addresses
* (both INET and CIDR datatypes):
*/
typedef struct
{
unsigned char family;
unsigned char bits;
unsigned char type;
union
{
unsigned int ipv4_addr; /* network byte order */
/* add IPV6 address type here */
} addr;
} inet_struct;
/*
* Both INET and CIDR addresses are represented within Postgres as varlena
* objects, ie, there is a varlena header (basically a length word) in front
* of the struct type depicted above.
*
* Although these types are variable-length, the maximum length
* is pretty short, so we make no provision for TOASTing them.
*/
typedef struct varlena inet;
In 7.4, we support IPv6, so they will be even larger.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Timeout while waiting for a LOCK ...
Yep, we get asked to do that quite often. Use statement_timeout before the LOCK command. If the timeout happens, the LOCK, and hence transaction will abort. --- The Hermit Hacker wrote: Simple (I think) question ... is there a way of having an application attempt to acquire a LOCK on a table *without* it blocking? Right now, if you try to LOCK a table that another process has LOCKed, it will hang indefinitely waiting for the other LOCK to drop ... is there a way of setting a 'timer' so that if it doesn't acquire a LOCK in n secs, it just fails and reports it back to the application? Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
