Re: [HACKERS] Closing some 8.4 open items
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote: > 2009/4/11 Andrew Gierth : > >> "Tom" == Tom Lane writes: > > > > >>> Perhaps more to the point: the previous round of discussion about > > >>> this already rejected the idea of treating window functions as a > > >>> category fundamentally separate from plain functions --- that is, > > >>> we are not following the "aggregate" model of having separate > > >>> commands for aggregate functions. > > > > >> I hadn't seen any such a consensus. > > > > Tom> We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, > > Tom> ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be > > Tom> presenting a different world view than exists at the SQL level. > > > > I'm not sure why that would matter. The fact that it is CREATE > > FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean > > that window functions aren't a distinctly different animal to normal > > functions. The usage and syntax is different enough that putting them > > all together under \df seems forced. > > Yeah, but all the window functions are stored in pg_proc. So are aggregate functions, and they have their own separate way of being addressed in psql :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
2009/4/11 Tom Lane : > Bruce Momjian writes: >> Yea, I thought we were going to do this: > >> Please find enclosed one way to handle it, this being prepending >> WINDOW to the result types in \df. > >> but I don't see this behavior in CVS. > > IIRC, my original proposal involved adding something to the argument > list --- it seems more natural to regard window-ness as having something > to do with the arguments than the result. But that was shot down on the > grounds of not fitting in well unless we wanted to add more decoration, > like parens around the regular argument list. And someone has claimed the argument column won't fit the syntax of DROP FUNCTION, which is not sure to be harmful or not. > Another idea was to add a new column to the \df output to mark > window-ness. Which, as I recall, *nobody* liked. But maybe if we > only did it for \df+ it would be more tolerable? The only negative opinion of this is added column is useful for only window function so far. And nobody can find the future possible extension by this column. So I'm +1 for "do nothing now", and let's wait for users reactions. The changes for this in the future seems not so painful. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
2009/4/11 Andrew Gierth : >> "Tom" == Tom Lane writes: > > >>> Perhaps more to the point: the previous round of discussion about > >>> this already rejected the idea of treating window functions as a > >>> category fundamentally separate from plain functions --- that is, > >>> we are not following the "aggregate" model of having separate > >>> commands for aggregate functions. > > >> I hadn't seen any such a consensus. > > Tom> We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, > Tom> ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be > Tom> presenting a different world view than exists at the SQL level. > > I'm not sure why that would matter. The fact that it is CREATE > FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean > that window functions aren't a distinctly different animal to normal > functions. The usage and syntax is different enough that putting them > all together under \df seems forced. Yeah, but all the window functions are stored in pg_proc. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allow COMMENT ON to accept an expression rather than just a string
On Fri, Apr 10, 2009 at 11:47 PM, Abhijit Menon-Sen wrote: > Hi. > > There's a TODO item about making COMMENT ON accept an expression. really? what's the use case for that? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
> "Tom" == Tom Lane writes: >>> Perhaps more to the point: the previous round of discussion about >>> this already rejected the idea of treating window functions as a >>> category fundamentally separate from plain functions --- that is, >>> we are not following the "aggregate" model of having separate >>> commands for aggregate functions. >> I hadn't seen any such a consensus. Tom> We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, Tom> ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be Tom> presenting a different world view than exists at the SQL level. I'm not sure why that would matter. The fact that it is CREATE FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean that window functions aren't a distinctly different animal to normal functions. The usage and syntax is different enough that putting them all together under \df seems forced. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Allow COMMENT ON to accept an expression rather than just a string
Hi. There's a TODO item about making COMMENT ON accept an expression. The grammar change is simple (SConst|NULL_P->a_expr), but as far as I can see, there are no similar utility commands that take expressions, and I'm not very familiar with the planner and executor, so I could use some advice about how to evaluate the expression. Also: what expressions should the code accept? I want to be able to use a parameter. Does that require extra work? What about subqueries, would they be useful in this context? (Or would it be better to just have functions that can set object and column descriptions instead?) Thanks. -- ams -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Andrew Dunstan writes:
> Tom Lane wrote:
>> ... Seems like it's nearly a one-liner fix, too.
> Well, what I have in mind is a bit bigger, but not large. See attached
> patch.
Hmm, you do need two instances of the loop, don't you? Might be
better to refactor along the lines of
if (has_lock_conflicts(te, running_te) ||
has_lock_conflicts(running_te, te))
// has a conflict
...
// true if te1 requires exclusive lock on any dependency of te2
static bool
has_lock_conflicts(te1, te2)
{
for (j = 0; j < te1->nLockDeps; j++)
{
for (k = 0; k < te2->nDeps; k++)
{
if (te1->lockDeps[j] == te2->dependencies[k])
return true;
}
}
return false;
}
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows installation service
> -Original Message- > From: Dave Page [mailto:dp...@pgadmin.org] > Sent: Friday, April 10, 2009 1:58 PM > To: Dann Corbit > Cc: pgsql-hackers@postgresql.org; Bill Luton; Larry McGhaw; Mike McKee; > Brian Fifer > Subject: Re: [HACKERS] Windows installation service > > On Fri, Apr 10, 2009 at 8:29 PM, Dann Corbit wrote: > > > I don't know the reason why, but that is not what happens here. > > > > We see the problem on 64-bit machines with Windows 2008 Server. > > We see the problem on 32-bit machine with Windows 2003 Server. > > We see the problem on 32-bit Windows XP machines. > > It is universal (all of these machines demonstrate the problem). > > > > I did get this email from Mike McKee this morning: > > > > "I noticed a pattern. > > > > The first time it works, and can shutdown. > > > > The second time is where it kind of hangs " > > So what is unusual about your machines that makes this happen for you, > but apparently noone else we've heard of? Are you running on a domain? Yes. This is a corporation, so we don't run peer to peer. > Any nonstandard security policy or other configuration? Nothing unusual. > I know Connx have experience developing on Windows, so can one of you > attach a debugger and see why the WaitForMultipleObjects() call in > pg_ctl doesn't work on your systems? Here is what we tried: We decided to start the service from the command line as an executable rather than a service. First, we found permissions problems: C:\CONNX32\CONNXSTORE\bin>C:\CONNX32\CONNXSTORE\bin\pg_ctl.exe start -w -N "pgsql-8.3" -D "C:\CONNX32\CONNXSTORE\data\" waiting for server to start...2009-04-10 14:55:22 PDT LOG: loaded library "$libdir/plugins/plugin_debugger.dll" 2009-04-10 14:55:22 PDT PANIC: could not open control file "global/pg_control": Permission denied This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. could not start server We allowed user postgres to have full control for the global folder. C:\CONNX32\CONNXSTORE\bin>C:\CONNX32\CONNXSTORE\bin\pg_ctl.exe start -w -N "pgsql-8.3" -D "C:\CONNX32\CONNXSTORE\data\" waiting for server to start...2009-04-10 15:09:50 PDT LOG: loaded library "$libdir/plugins/plugin_debugger.dll" 2009-04-10 15:09:50 PDT LOG: could not create file "postmaster.opts": Permission denied ... done server started We had a similar problem with the data folder, so we changed permissions for user postgres to have full control for the data folder. C:\CONNX32\CONNXSTORE\bin>C:\CONNX32\CONNXSTORE\bin\pg_ctl.exe start -w -N "pgsql-8.3" -D "C:\CONNX32\CONNXSTORE\data\" waiting for server to start...2009-04-10 15:19:49 PDT LOG: loaded library "$libdir/plugins/plugin_debugger.dll" could not start server After those changes, we no longer had permissions problems but the server did not start from the command line. I uninstalled Postgresql I removed the postgres user I removed the postgres directory structure I installed PostgreSQL postgresql-8.3.7-1. Apparently this version does not have the same bad symptoms (I have not checked all the other machines yet -- that will take some time). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Adnrew, Well, what I have in mind is a bit bigger, but not large. See attached patch. I'll test it this weekend. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Tom Lane wrote:
Andrew Dunstan writes:
What you're missing is that we need to compare the lockdeps of each item
(i.e. both the candidate item and the running item) with all the deps
(not just the lockdeps) of the other item. If neither item has any
lockdeps there will be no conflict. This will allow concurrent index
creation, since neither item will have any lockdeps. But it will prevent
us selecting a create index that conflicts with a running FK creation or
vice versa.
Oh, I see, you're using the deps as a proxy for the shared locks the
operation will acquire. Yeah, that might work. Seems like it's nearly
a one-liner fix, too.
Well, what I have in mind is a bit bigger, but not large. See attached
patch.
cheers
andrew
*** pg_backup_archiver.c 2009-04-10 00:09:57.0 -0400
--- pg_backup_archiver-fix.c 2009-04-10 19:22:07.0 -0400
***
*** 3423,3433
if (slots[i].args == NULL)
continue;
running_te = slots[i].args->te;
for (j = 0; j < te->nLockDeps && !conflicts; j++)
{
! for (k = 0; k < running_te->nLockDeps; k++)
{
! if (te->lockDeps[j] == running_te->lockDeps[k])
{
conflicts = true;
break;
--- 3423,3450
if (slots[i].args == NULL)
continue;
running_te = slots[i].args->te;
+ /* does the candidate item require an exclusive lock that
+ * would block on or conflict with the running item?
+ */
for (j = 0; j < te->nLockDeps && !conflicts; j++)
{
! for (k = 0; k < running_te->nDeps; k++)
{
! if (te->lockDeps[j] == running_te->dependencies[k])
! {
! conflicts = true;
! break;
! }
! }
! }
! /* or does the running item hold an exclusive lock that
! * would block or conflict with the candidate item?
! */
! for (j = 0; j < running_te->nLockDeps && !conflicts; j++)
! {
! for (k = 0; k < te->nDeps; k++)
! {
! if (running_te->lockDeps[j] == te->dependencies[k])
{
conflicts = true;
break;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Andrew Dunstan writes: > What you're missing is that we need to compare the lockdeps of each item > (i.e. both the candidate item and the running item) with all the deps > (not just the lockdeps) of the other item. If neither item has any > lockdeps there will be no conflict. This will allow concurrent index > creation, since neither item will have any lockdeps. But it will prevent > us selecting a create index that conflicts with a running FK creation or > vice versa. Oh, I see, you're using the deps as a proxy for the shared locks the operation will acquire. Yeah, that might work. Seems like it's nearly a one-liner fix, too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Tom Lane wrote: Andrew Dunstan writes: Tom Lane wrote: Doesn't that eliminate any chance of running two CREATE INDEXes concurrently on the same table? No, since neither of them will have any locking dependencies, which are only for items that take an exclusive lock on the table(s), such as FK constraints. In that case a CREATE INDEX would also fail to be seen as conflicting with an ALTER ADD FOREIGN KEY, which I thought was the nub of Josh's complaint. No it won't. What you're missing is that we need to compare the lockdeps of each item (i.e. both the candidate item and the running item) with all the deps (not just the lockdeps) of the other item. If neither item has any lockdeps there will be no conflict. This will allow concurrent index creation, since neither item will have any lockdeps. But it will prevent us selecting a create index that conflicts with a running FK creation or vice versa. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Andrew Dunstan writes: > Tom Lane wrote: >> Doesn't that eliminate any chance of running two CREATE INDEXes >> concurrently on the same table? > No, since neither of them will have any locking dependencies, which are > only for items that take an exclusive lock on the table(s), such as FK > constraints. In that case a CREATE INDEX would also fail to be seen as conflicting with an ALTER ADD FOREIGN KEY, which I thought was the nub of Josh's complaint. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Unicode string literals versus the world
So I started to look at what might be involved in teaching plpgsql about standard_conforming_strings, and was soon dismayed by the sheer epic nature of its failure to act like the core lexer. It was shaky enough before, but the recent introduction of Unicode strings and identifiers into the core has left plpgsql hopelessly behind. I can see two basic approaches to making things work: copy-and-paste practically all of parser/scan.l into plpgsql's lexer (certainly all of it that involves exclusive states); or throw out plpgsql's lexer altogether in favor of somehow using the core lexer directly. Neither one looks very attractive. It gets worse though: I have seldom seen such a badly designed piece of syntax as the Unicode string syntax --- see http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE You scan the string, and then after that they tell you what the escape character is!? Not to mention the obvious ambiguity with & as an operator. If we let this go into 8.4, our previous rounds with security holes caused by careless string parsing will look like a day at the beach. No frontend that isn't fully cognizant of the Unicode string syntax is going to parse such things correctly --- it's going to be trivial for a bad guy to confuse a quoting mechanism as to what's an escape and what isn't. I think we need to give very serious consideration to ripping out that "feature". regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
"David E. Wheeler" writes: > On Apr 10, 2009, at 12:15 PM, Tom Lane wrote: >> I gave my reasoning before: widening this API has possibly nontrivial >> future maintenance costs, and the actual use-case for the data is >> unconvincing. > It seems to me that the immediate patch to simply copy zone.tab has no > effect on the API. That's a debate worth having, but I don't think > it's relevant to this particular patch, is it? Well, as far as I can see the immediate patch is pretty useless without an API change in front of it. Andrew was threatening to write a pgfoundry module that read from the file directly, but considering that such a thing would require superuser privileges it doesn't seem like a particularly attractive answer. The immediate patch has other bogosities too: why not iso3166.tab too, if we are going to start exposing this data? Without an agreed-on API change we don't really know what we need or why. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Tom Lane wrote: Andrew Dunstan writes: Yeah. I think the correct logic is roughly this: When considering if a candidate item has a locking conflict with a running item, then if *either* of them has a locking dependency that coincides with *any* dependency of the other item, then the candidate is rejected. The principle is that we don't give any item a chance to block on a lock. Doesn't that eliminate any chance of running two CREATE INDEXes concurrently on the same table? No, since neither of them will have any locking dependencies, which are only for items that take an exclusive lock on the table(s), such as FK constraints. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Andrew Dunstan writes: > Yeah. I think the correct logic is roughly this: When considering if a > candidate item has a locking conflict with a running item, then if > *either* of them has a locking dependency that coincides with *any* > dependency of the other item, then the candidate is rejected. The > principle is that we don't give any item a chance to block on a lock. Doesn't that eliminate any chance of running two CREATE INDEXes concurrently on the same table? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabledinplpgsql
"Kevin Grittner" writes: > Well, that's a change I'm arguing for. That would require both the > plpgsql parser change Tom is talking about, and a change to CREATE > FUNCTION such that there is an implied SET standard_compliant_strings > FROM CURRENT -- which is something I've suggested a couple times; > there's been no explicit response to that. If you want one: it seems like a really bad idea. Aside from the sheer ugliness of special-casing one particular GUC, it would break existing pg_dump files, since pg_dump has no idea that its setting of standard_conforming_strings might influence the behavior of functions it defines. I don't actually see that standard_conforming_strings is worse than search_path or half a dozen other settings that will influence the semantics of SQL queries. If anything it's less bad than those since it's less likely to break things silently. The whole topic just illustrates that "invent a GUC" is not a pain-free solution to handling definitional conflicts. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabled inplpgsql
Andrew Gierth writes: > "Tom" == Tom Lane writes: > Tom> Back to the point at hand: do we want to look at making plpgsql > Tom> respect the GUC? > Surely what matters is the value of the GUC at the time that you did > the CREATE FUNCTION, not the value at the time you happen to be > calling it? No, it isn't, and that's not the immediate problem anyway --- the immediate problem is that plpgsql doesn't respect *any* value of the GUC. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows installation service
On Fri, Apr 10, 2009 at 8:29 PM, Dann Corbit wrote: > I don't know the reason why, but that is not what happens here. > > We see the problem on 64-bit machines with Windows 2008 Server. > We see the problem on 32-bit machine with Windows 2003 Server. > We see the problem on 32-bit Windows XP machines. > It is universal (all of these machines demonstrate the problem). > > I did get this email from Mike McKee this morning: > > "I noticed a pattern. > > The first time it works, and can shutdown. > > The second time is where it kind of hangs " So what is unusual about your machines that makes this happen for you, but apparently noone else we've heard of? Are you running on a domain? Any nonstandard security policy or other configuration? I know Connx have experience developing on Windows, so can one of you attach a debugger and see why the WaitForMultipleObjects() call in pg_ctl doesn't work on your systems? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Josh Berkus wrote: Tom, Andrew, Well, we certainly want to be able to run CREATE INDEXes in parallel, so this would appear to require hard-wiring some conception of shared versus exclusive lock into pg_restore. I think it might be a bit late to consider that for 8.4. I'm pretty sure I had the logic for this correct stuff originally, so I'm going to go back and check that. FWIW, I've tested 3 moderately complex databases with this, and the locking issue happens on every one. As a result, getting more than 3 cores of scalability on any fairly complex DB isn't possible without fixing this. Yeah. I think the correct logic is roughly this: When considering if a candidate item has a locking conflict with a running item, then if *either* of them has a locking dependency that coincides with *any* dependency of the other item, then the candidate is rejected. The principle is that we don't give any item a chance to block on a lock. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unable to install tsearch2 on PostgreSQL 8.3.7 successfully
l0rins writes: > In version 8.1, tsearch2.sql contained SQL statements to create 4 pg_ts_* > tables: pg_ts_cfg, pg_ts_dict, ... and populate them with data. These > statements are missing in tsearch2.sql 8.3.7 version. So, I'm getting: > ERROR: relation "pg_ts_*" does not exist when trying to run tsearch > statements on version 8.3.2. You need to read the documentation --- 8.3's text search facility is quite a lot different from previous releases, and contrib/tsearch2 is now just a compatibility layer that does not fully hide the differences. http://www.postgresql.org/docs/8.3/static/textsearch-migration.html regards, tom lane Thanks for your reply! Actually, I read this Postgresql documentation before (http://www.postgresql.org/docs/8.3/static/textsearch-migration.html), but it's still very unclear to me how to install tsearch2 module on Postgres v8.3.7 from scratch. In fact, I'm relying on tsearch2 docs for installation instructions here: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html but it apparently doesn't work with Postgres v8.3.7 I would be grateful if you could provide more information on that. Thanks, Adel -- View this message in context: http://www.nabble.com/unable-to-install-tsearch2-on-PostgreSQL-8.3.7-successfully-tp22989298p22994030.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabledinplpgsql
Andrew Gierth wrote: > Surely what matters is the value of the GUC at the time that you did > the CREATE FUNCTION, not the value at the time you happen to be > calling it? Well, that's a change I'm arguing for. That would require both the plpgsql parser change Tom is talking about, and a change to CREATE FUNCTION such that there is an implied SET standard_compliant_strings FROM CURRENT -- which is something I've suggested a couple times; there's been no explicit response to that. See back here in the thread for some behavior which surprised me: http://archives.postgresql.org/pgsql-hackers/2009-04/msg00519.php -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabled inplpgsql
> "Tom" == Tom Lane writes: Tom> Back to the point at hand: do we want to look at making plpgsql Tom> respect the GUC? Surely what matters is the value of the GUC at the time that you did the CREATE FUNCTION, not the value at the time you happen to be calling it? -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
Tom, Actually, what this thread is leading me towards is the idea that almost nobody really has standard_conforming_strings turned on in production (except maybe with apps ported from Oracle or someplace else). If they did, we'd be seeing more complaints about plpgsql not working properly. So maybe we *could* change plpgsql to honor the GUC without anyone noticing too much. Actually, a lot of people are using $escapes$ for all nested quotes in plpgsql. So they wouldn't notice the problem. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
On Apr 10, 2009, at 12:15 PM, Tom Lane wrote: I gave my reasoning before: widening this API has possibly nontrivial future maintenance costs, and the actual use-case for the data is unconvincing. It seems to me that the immediate patch to simply copy zone.tab has no effect on the API. That's a debate worth having, but I don't think it's relevant to this particular patch, is it? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabled inplpgsql
Tom Lane wrote: > do we want to look at making plpgsql respect the GUC? +1 > I'm inclined to deal with the special case (RAISE and anything else > similar) by changing the code so that we *do* feed the string > literal through the main parser, not for any functional effect but > just to have it throw the right warnings/errors. +1 -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Tom, Andrew, Well, we certainly want to be able to run CREATE INDEXes in parallel, so this would appear to require hard-wiring some conception of shared versus exclusive lock into pg_restore. I think it might be a bit late to consider that for 8.4. I'm pretty sure I had the logic for this correct stuff originally, so I'm going to go back and check that. FWIW, I've tested 3 moderately complex databases with this, and the locking issue happens on every one. As a result, getting more than 3 cores of scalability on any fairly complex DB isn't possible without fixing this. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabled inplpgsql
Bruce Momjian writes: > Kevin Grittner wrote: >> My personal bias is to go to the standard behavior as the default at >> some point. For legacy reasons, I don't know that you would ever want >> to remove the setting; especially since I don't think it adds much >> code if you're going to support the E'...' literals. The ugliest >> thing about this GUC is that it adds some complications to the flex >> code, but it doesn't seem that bad to me. > Agreed, we would probably never remove standard_conforming_strings. Yeah, I don't see that happening either. I agree with Kevin that it would be nice to flip the default at some point, but I'm afraid it's a long way off yet. Back to the point at hand: do we want to look at making plpgsql respect the GUC? I think it's a bit trickier than it looks, because we don't want duplicate warnings from both plpgsql and the main parser for strings that get fed through. I'm inclined to deal with the special case (RAISE and anything else similar) by changing the code so that we *do* feed the string literal through the main parser, not for any functional effect but just to have it throw the right warnings/errors. Otherwise the plpgsql lexer has to somehow know when to warn and when not, which'd be a mess. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabled inplpgsql
Kevin Grittner wrote: > Bruce Momjian wrote: > > It would be nice to know if we are ever going to set > > standard_conforming_strings to on. > > My personal bias is to go to the standard behavior as the default at > some point. For legacy reasons, I don't know that you would ever want > to remove the setting; especially since I don't think it adds much > code if you're going to support the E'...' literals. The ugliest > thing about this GUC is that it adds some complications to the flex > code, but it doesn't seem that bad to me. Agreed, we would probably never remove standard_conforming_strings. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escapingnotdisabled inplpgsql
Bruce Momjian wrote: > It would be nice to know if we are ever going to set > standard_conforming_strings to on. My personal bias is to go to the standard behavior as the default at some point. For legacy reasons, I don't know that you would ever want to remove the setting; especially since I don't think it adds much code if you're going to support the E'...' literals. The ugliest thing about this GUC is that it adds some complications to the flex code, but it doesn't seem that bad to me. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
>Surely we'd have seen more complaints, then. > regards, tom lane This gets a definite +1 here as we are using "SET TIMEZONE" at the beginning of each transaction so that each user sees/records dates automatically in whatever timezone they have associated with them. Works beautifully with very little help from the application side. The only downside is finding a way to give the user an appropriate list of timezones to choose from. -- "An eye for eye only ends up making the whole world blind." -- Mohandas Gandhi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
Tom Lane wrote: > "Kevin Grittner" writes: > > Let me ask this -- If we were to change the plpgsql parser to pay > > attention to the GUC, it couldn't break anything for any environment > > which always has the GUC 'off', could it? > > Right, because the behavior wouldn't actually change. > > I'm starting to lean in the same direction --- the current plpgsql > behavior with the GUC 'on' is sufficiently broken that it seems unlikely > anyone is doing much with plpgsql and that setting. > > It still remains that actually flipping the default would probably > provoke lots of breakage, but plpgsql's current behavior doesn't > help that. It would be nice to know if we are ever going to set standard_conforming_strings to on. If not, we can remove the TODO item. The bigger question is if we aren't going to turn it on was there any value to setting escape_string_warning to on in 8.2? We required a lot of users to prefix their strings with 'E'. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows installation service
> -Original Message- > From: Dann Corbit > Sent: Friday, April 10, 2009 12:30 PM > To: 'Dave Page' > Cc: pgsql-hackers@postgresql.org; Bill Luton; Larry McGhaw; Mike McKee; > Brian Fifer > Subject: RE: [HACKERS] Windows installation service > > > -Original Message- > > From: Dave Page [mailto:dp...@pgadmin.org] > > Sent: Friday, April 10, 2009 8:16 AM > > To: Dann Corbit > > Cc: pgsql-hackers@postgresql.org; Bill Luton; Larry McGhaw; Mike > McKee; > > Brian Fifer > > Subject: Re: [HACKERS] Windows installation service > > > > On Mon, Apr 6, 2009 at 9:32 PM, Dann Corbit > wrote: > > > The Windows installation service uses pg_ctl to perform the network > > > start-up operation. > > > This program starts up the postmaster and exits. > > > The net effect of performing the operation in this manner is that > the > > > Windows service manager sees the service as "not running" a few > > minutes > > > after the startup is complete. It also prevents proper pause and > > > restart of the service. > > > > Per our offlist conversation, this is not how it works. > > > > > As a suggestion: > > > Instead of installing pg_ctl as the service, start up postgres as > the > > > service. This is how we did our Windows port. If the idea is > > appealing > > > to the PostgreSQL group, we can send our service code modifications > > for > > > review as a possible alternative to the current method. > > > > > > Another approach that could be equally helpful (along the same > lines) > > is > > > to leave pg_ctl.exe in memory and allow it to control the program. > > > > Which is what does happen. > > I don't know the reason why, but that is not what happens here. > > We see the problem on 64-bit machines with Windows 2008 Server. > We see the problem on 32-bit machine with Windows 2003 Server. > We see the problem on 32-bit Windows XP machines. > It is universal (all of these machines demonstrate the problem). > > I did get this email from Mike McKee this morning: > > "I noticed a pattern. > > The first time it works, and can shutdown. > > The second time is where it kind of hangs " I should mention that PostgreSQL is still operational. The Postgres servers are in memory and I am able to perform queries, despite the apparent status of the service. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows installation service
> -Original Message- > From: Dave Page [mailto:dp...@pgadmin.org] > Sent: Friday, April 10, 2009 8:16 AM > To: Dann Corbit > Cc: pgsql-hackers@postgresql.org; Bill Luton; Larry McGhaw; Mike McKee; > Brian Fifer > Subject: Re: [HACKERS] Windows installation service > > On Mon, Apr 6, 2009 at 9:32 PM, Dann Corbit wrote: > > The Windows installation service uses pg_ctl to perform the network > > start-up operation. > > This program starts up the postmaster and exits. > > The net effect of performing the operation in this manner is that the > > Windows service manager sees the service as "not running" a few > minutes > > after the startup is complete. It also prevents proper pause and > > restart of the service. > > Per our offlist conversation, this is not how it works. > > > As a suggestion: > > Instead of installing pg_ctl as the service, start up postgres as the > > service. This is how we did our Windows port. If the idea is > appealing > > to the PostgreSQL group, we can send our service code modifications > for > > review as a possible alternative to the current method. > > > > Another approach that could be equally helpful (along the same lines) > is > > to leave pg_ctl.exe in memory and allow it to control the program. > > Which is what does happen. I don't know the reason why, but that is not what happens here. We see the problem on 64-bit machines with Windows 2008 Server. We see the problem on 32-bit machine with Windows 2003 Server. We see the problem on 32-bit Windows XP machines. It is universal (all of these machines demonstrate the problem). I did get this email from Mike McKee this morning: "I noticed a pattern. The first time it works, and can shutdown. The second time is where it kind of hangs " -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
"Kevin Grittner" writes: > Let me ask this -- If we were to change the plpgsql parser to pay > attention to the GUC, it couldn't break anything for any environment > which always has the GUC 'off', could it? Right, because the behavior wouldn't actually change. I'm starting to lean in the same direction --- the current plpgsql behavior with the GUC 'on' is sufficiently broken that it seems unlikely anyone is doing much with plpgsql and that setting. It still remains that actually flipping the default would probably provoke lots of breakage, but plpgsql's current behavior doesn't help that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
Bruce Momjian writes: > Brendan Jurd wrote: >> I agree that there are probably a great many app authors out there who >> don't realise how very boned they might be if the default GUC gets >> changed and they haven't prepared their SQL to cope. > I assume those authors are getting warnings, which is something we don't > for PL/pgSQL now. To the extent that the strings are getting passed through to the main SQL engine, they do get warnings now, and pretty noisy ones: regression=# create function foo2() returns text as $$ begin return 'foo\'s bar'; end$$ language plpgsql; WARNING: nonstandard use of \' in a string literal LINE 1: SELECT 'foo\'s bar' ^ HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...'). QUERY: SELECT 'foo\'s bar' CONTEXT: SQL statement in PL/PgSQL function "foo2" near line 2 CREATE FUNCTION regression=# select foo2(); WARNING: nonstandard use of \' in a string literal LINE 1: SELECT 'foo\'s bar' ^ HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...'). QUERY: SELECT 'foo\'s bar' CONTEXT: PL/pgSQL function "foo2" line 2 at RETURN foo2 --- foo's bar (1 row) It's the corner cases where plpgsql doesn't pass strings through that are worrisome. It's possible that RAISE is the only such case --- anyone want to check? Actually, what this thread is leading me towards is the idea that almost nobody really has standard_conforming_strings turned on in production (except maybe with apps ported from Oracle or someplace else). If they did, we'd be seeing more complaints about plpgsql not working properly. So maybe we *could* change plpgsql to honor the GUC without anyone noticing too much. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
On Fri, Apr 10, 2009 at 3:01 PM, Tom Lane wrote: > Josh Berkus writes: >> Tom, >>> Like what? I do not actually believe that anyone needs an >>> interactive geographical timezone selector based on >>> pg_timezone_names. > >> Actually, considering that PostgreSQL is the leading open source GIS >> database, I expect that a *lot* of people want this. > > Surely we'd have seen more complaints, then. This idea is at least +5 just on this thread; more significant changes have been made with less support. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
Tom Lane wrote: > "Kevin Grittner" writes: >> The aspect of 8.3 behavior that concerns me most is that neither >> the author of a function, nor anyone using it, can control or >> predict which way a string literal with a backslash will be >> interpreted, unless the author explicitly specifies the SET >> standard_conforming_strings clause in the function declaration. > > Yeah. This is one reason why I'm still afraid to flip the default > value of standard_conforming_strings --- there seems too much risk > of widespread breakage. > > I don't have a good solution for it, but I agree it's a problem. Now that I see that string literals are currently interpreted inconsistently, I don't think there's any way to get to a sane behavior without risking some breakage somewhere. If, as I've seen some people assert, most people aren't setting the standard_conforming_strings = on, it would seem to be reasonable to put the risk with that 'on' setting. Let me ask this -- If we were to change the plpgsql parser to pay attention to the GUC, it couldn't break anything for any environment which always has the GUC 'off', could it? If not, I am having a hard time seeing a smoother transition than to change the plpgsql parser to use the GUC, and to have the CREATE FUNCTION statement make a special case of defaulting this GUC to FROM CURRENT. Making an exception of this offends a little, but not as badly as unpredictable runtime behavior. An advantage of this approach is that it would be just another place to check your string literals when and if you go to switch over to standard literals. Whether to ever change the default behavior over to the standard is more of a "marketing" decision than a technical one, I think. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
David Fetter writes: > You're setting a pretty high bar here for a pretty small change which > will cause a pretty large increase in convenience. What is the actual > problem here? I gave my reasoning before: widening this API has possibly nontrivial future maintenance costs, and the actual use-case for the data is unconvincing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
Brendan Jurd wrote: > On Sat, Apr 11, 2009 at 4:40 AM, Kevin Grittner > wrote: > > The aspect of 8.3 behavior that concerns me most is that neither the > > author of a function, nor anyone using it, can control or predict > > which way a string literal with a backslash will be interpreted, > > unless the author explicitly specifies the SET > > standard_conforming_strings clause in the function declaration. ?I'm > > betting that most people writing and using plpgsql functions don't > > know that. ?Any thoughts about what can or should be done about that? > > Isn't this exactly the same problem that application authors have been > facing with SQL in their code? > > Namely, if there's a backslash anywhere in a string literal you > *cannot* leave it as a bare single-quoted string literal. You need to > decide whether you want the backslash treated as an escape character > (and therefore use E quoting), or as a backslash (and therefore use $$ > quoting). > > Until you've done that for every single string literal with a > backslash, your application isn't ready for > standard_conforming_strings to be switched on. > > I agree that there are probably a great many app authors out there who > don't realise how very boned they might be if the default GUC gets > changed and they haven't prepared their SQL to cope. I assume those authors are getting warnings, which is something we don't for PL/pgSQL now. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
On Fri, Apr 10, 2009 at 03:01:05PM -0400, Tom Lane wrote: > Josh Berkus writes: > > Tom, > >> Like what? I do not actually believe that anyone needs an > >> interactive geographical timezone selector based on > >> pg_timezone_names. > > > Actually, considering that PostgreSQL is the leading open source > > GIS database, I expect that a *lot* of people want this. > > Surely we'd have seen more complaints, then. You're setting a pretty high bar here for a pretty small change which will cause a pretty large increase in convenience. What is the actual problem here? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
On Sat, Apr 11, 2009 at 4:40 AM, Kevin Grittner wrote: > The aspect of 8.3 behavior that concerns me most is that neither the > author of a function, nor anyone using it, can control or predict > which way a string literal with a backslash will be interpreted, > unless the author explicitly specifies the SET > standard_conforming_strings clause in the function declaration. I'm > betting that most people writing and using plpgsql functions don't > know that. Any thoughts about what can or should be done about that? Isn't this exactly the same problem that application authors have been facing with SQL in their code? Namely, if there's a backslash anywhere in a string literal you *cannot* leave it as a bare single-quoted string literal. You need to decide whether you want the backslash treated as an escape character (and therefore use E quoting), or as a backslash (and therefore use $$ quoting). Until you've done that for every single string literal with a backslash, your application isn't ready for standard_conforming_strings to be switched on. I agree that there are probably a great many app authors out there who don't realise how very boned they might be if the default GUC gets changed and they haven't prepared their SQL to cope. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
Josh Berkus writes: > Tom, >> Like what? I do not actually believe that anyone needs an >> interactive geographical timezone selector based on >> pg_timezone_names. > Actually, considering that PostgreSQL is the leading open source GIS > database, I expect that a *lot* of people want this. Surely we'd have seen more complaints, then. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
"Kevin Grittner" writes: > The aspect of 8.3 behavior that concerns me most is that neither the > author of a function, nor anyone using it, can control or predict > which way a string literal with a backslash will be interpreted, > unless the author explicitly specifies the SET > standard_conforming_strings clause in the function declaration. Yeah. This is one reason why I'm still afraid to flip the default value of standard_conforming_strings --- there seems too much risk of widespread breakage. I don't have a good solution for it, but I agree it's a problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
Tom Lane wrote: >> Can you show one case where having plgpsql parse the function body >> based on the standard_conforming_strings GUC would break *anything* >> that now works? > > regression=# create function foo() returns int as $$ > regression$# begin > regression$# raise notice 'foo\'s bar'; > regression$# return 1; > regression$# end$$ language plpgsql; > CREATE FUNCTION > regression=# select foo(); > NOTICE: foo's bar > foo > - >1 > (1 row) > > In this case the string literal isn't actually ever passed to the > main SQL engine, so the SQL quoting rules aren't relevant. (I don't > remember offhand if anything besides RAISE works that way.) > > It may be that this isn't a very important case, but to claim that > it doesn't exist is simply wrong. OK, I didn't try that. Point taken. It is a bigger mess than I thought then. The aspect of 8.3 behavior that concerns me most is that neither the author of a function, nor anyone using it, can control or predict which way a string literal with a backslash will be interpreted, unless the author explicitly specifies the SET standard_conforming_strings clause in the function declaration. I'm betting that most people writing and using plpgsql functions don't know that. Any thoughts about what can or should be done about that? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4027: backslash escaping notdisabled inplpgsql
"Kevin Grittner" writes: > Tom Lane wrote: >> I think you are confusing parsing of the string literal that >> is the argument of CREATE FUNCTION with the parsing that the plpgsql >> interpreter does on the function body once it gets it. > Oh, I'm not confused about that at all. I'm arguing that it's a bad > idea. I agree with the OP that this is a bug. Did you look at my > other examples of behavior? I ignored all the ones that used non-dollar-quote syntax for the overall function body, since they are just confusing the issue. > Can you show one case where having plgpsql parse the function body > based on the standard_conforming_strings GUC would break *anything* > that now works? regression=# create function foo() returns int as $$ regression$# begin regression$# raise notice 'foo\'s bar'; regression$# return 1; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# select foo(); NOTICE: foo's bar foo - 1 (1 row) In this case the string literal isn't actually ever passed to the main SQL engine, so the SQL quoting rules aren't relevant. (I don't remember offhand if anything besides RAISE works that way.) It may be that this isn't a very important case, but to claim that it doesn't exist is simply wrong. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: to_char, support for EEEE format
Hi, I know very well, so all texts in my patches should be translated to English. My language skills are really minimal. So, please, if you can, propose these error messages (with hints)- result will be much better. Thank you Pavel 2009/4/10 Brendan Jurd : > On Sat, Apr 11, 2009 at 2:16 AM, Pavel Stehule > wrote: >> >> I was surprised so PostgreSQL doesn't support this basic output format. >> > > Hi Pavel, > > I had a look through your patch and would like to suggest improvements > to the new error messages you've introduced. > > 1. "invalid using of format " > > This message occurs several times in the patch. For one thing, the > grammar is wrong; it should be "use", not "using". > > Additionally, this message on its own is not very helpful. If I was > trying to use to_char and got "invalid use of format" my first thought > would be "Invalid how?" The message should at minimum have a DETAIL, > and possibly a HINT as well to make it effective. > > 2. "cannot use and others" > > The wording on this message is a bit awkward. I think what you meant > to say is that cannot be used with certain other formatting > codes, but this should be made explicit in the message. > > Cheers, > BJ > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: to_char, support for EEEE format
On Sat, Apr 11, 2009 at 2:16 AM, Pavel Stehule wrote: > > I was surprised so PostgreSQL doesn't support this basic output format. > Hi Pavel, I had a look through your patch and would like to suggest improvements to the new error messages you've introduced. 1. "invalid using of format " This message occurs several times in the patch. For one thing, the grammar is wrong; it should be "use", not "using". Additionally, this message on its own is not very helpful. If I was trying to use to_char and got "invalid use of format" my first thought would be "Invalid how?" The message should at minimum have a DETAIL, and possibly a HINT as well to make it effective. 2. "cannot use and others" The wording on this message is a bit awkward. I think what you meant to say is that cannot be used with certain other formatting codes, but this should be made explicit in the message. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Tom Lane escreveu: Bruce Momjian writes: Yea, I thought we were going to do this: Please find enclosed one way to handle it, this being prepending WINDOW to the result types in \df. but I don't see this behavior in CVS. IIRC, my original proposal involved adding something to the argument list --- it seems more natural to regard window-ness as having something to do with the arguments than the result. But that was shot down on the grounds of not fitting in well unless we wanted to add more decoration, like parens around the regular argument list. Another idea was to add a new column to the \df output to mark window-ness. Which, as I recall, *nobody* liked. But maybe if we only did it for \df+ it would be more tolerable? Adding another column to \df+ is not a good idea; there are already too much columns. Window functions are special functions (they even have an different syntax and separate section in docs) and are not less special than aggregate functions. So +1 to add \dw for them. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New trigger option of pg_standby
Fujii-san,
I like the new patch using the content of the file to determine the
mode. Much easier to use at failover time.
On Fri, 2009-04-10 at 12:47 +0900, Fujii Masao wrote:
> > One problem with this patch is that in smart mode, the trigger file is not
> > deleted. That's different from current pg_standby behavior, and makes
> > accidental failovers after one failover more likely.
>
> Yes, it's because pg_standby cannot be sure when the trigger file
> can be removed in smart mode. If the trigger file is deleted as soon
> as it's found, just like in fast mode, pg_standby may keep waiting
> for WAL file again.
My understanding of smart mode is fairly simple:
if (triggered)
{
if (smartMode && nextWALfile+1 exists)
exit(0);
else
{
delete trigger file
exit(1);
}
}
If you perform a file lookahead (the +1) as shown above then you avoid
the problem Heikki observes.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: to_char, support for EEEE format
Hello
I was surprised so PostgreSQL doesn't support this basic output format.
Regards
Pavel Stehule
*** ./src/backend/utils/adt/formatting.c.orig 2009-04-10 11:31:17.0 +0200
--- ./src/backend/utils/adt/formatting.c 2009-04-10 18:05:14.0 +0200
***
*** 335,340
--- 335,341
#define NUM_F_MULTI (1 << 11)
#define NUM_F_PLUS_POST (1 << 12)
#define NUM_F_MINUS_POST (1 << 13)
+ #define NUM_F_ (1 << 14)
#define NUM_LSIGN_PRE (-1)
#define NUM_LSIGN_POST 1
***
*** 355,360
--- 356,362
#define IS_PLUS(_f) ((_f)->flag & NUM_F_PLUS)
#define IS_ROMAN(_f) ((_f)->flag & NUM_F_ROMAN)
#define IS_MULTI(_f) ((_f)->flag & NUM_F_MULTI)
+ #define IS_(_f) ((_f)->flag & NUM_F_)
/* --
* Format picture cache
***
*** 821,827
{"B", 1, NUM_B}, /* B */
{"C", 1, NUM_C}, /* C */
{"D", 1, NUM_D}, /* D */
! {"E", 1, NUM_E}, /* E */
{"FM", 2, NUM_FM}, /* F */
{"G", 1, NUM_G}, /* G */
{"L", 1, NUM_L}, /* L */
--- 823,829
{"B", 1, NUM_B}, /* B */
{"C", 1, NUM_C}, /* C */
{"D", 1, NUM_D}, /* D */
! {"", 4, NUM_E}, /* E */
{"FM", 2, NUM_FM}, /* F */
{"G", 1, NUM_G}, /* G */
{"L", 1, NUM_L}, /* L */
***
*** 1043,1048
--- 1045,1058
if (n->type != NODE_TYPE_ACTION)
return;
+
+ if (IS_(num) && n->key->id != NUM_E)
+ {
+ NUM_cache_remove(last_NUMCacheEntry);
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot use \"\" and others")));
+ }
switch (n->key->id)
{
***
*** 1217,1226
break;
case NUM_E:
! NUM_cache_remove(last_NUMCacheEntry);
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("\"E\" is not supported")));
}
return;
--- 1227,1249
break;
case NUM_E:
! if (IS_(num))
! {
! NUM_cache_remove(last_NUMCacheEntry);
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cannot use \"\" twice")));
! }
! if (IS_BLANK(num) || IS_FILLMODE(num) || IS_LSIGN(num) || IS_BRACKET(num)
! || IS_MINUS(num) || IS_PLUS(num) || IS_ROMAN(num) || IS_MULTI(num))
! {
! NUM_cache_remove(last_NUMCacheEntry);
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cannot use \"\" and others")));
! }
! num->flag |= NUM_F_;
! break;
}
return;
***
*** 4138,4143
--- 4161,4178
--Np->Num->zero_start;
/*
+ * Short code for format
+ */
+ if (IS_(Np->Num))
+ {
+ if (!Np->is_to_char)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("\"\" not supported")));
+ return strcpy(inout, number);
+ }
+
+ /*
* Roman correction
*/
if (IS_ROMAN(Np->Num))
***
*** 4232,4238
#ifdef DEBUG_TO_FROM_CHAR
elog(DEBUG_elog_output,
! "\n\tSIGN: '%c'\n\tNUM: '%s'\n\tPRE: %d\n\tPOST: %d\n\tNUM_COUNT: %d\n\tNUM_PRE: %d\n\tSIGN_WROTE: %s\n\tZERO: %s\n\tZERO_START: %d\n\tZERO_END: %d\n\tLAST_RELEVANT: %s\n\tBRACKET: %s\n\tPLUS: %s\n\tMINUS: %s\n\tFILLMODE: %s\n\tROMAN: %s",
Np->sign,
Np->number,
Np->Num->pre,
--- 4267,4273
#ifdef DEBUG_TO_FROM_CHAR
elog(DEBUG_elog_output,
! "\n\tSIGN: '%c'\n\tNUM: '%s'\n\tPRE: %d\n\tPOST: %d\n\tNUM_COUNT: %d\n\tNUM_PRE: %d\n\tSIGN_WROTE: %s\n\tZERO: %s\n\tZERO_START: %d\n\tZERO_END: %d\n\tLAST_RELEVANT: %s\n\tBRACKET: %s\n\tPLUS: %s\n\tMINUS: %s\n\tFILLMODE: %s\n\tROMAN: %s\n\t: %s",
Np->sign,
Np->number,
Np->Num->pre,
***
*** 4248,4254
IS_PLUS(Np->Num) ? "Yes" : "No",
IS_MINUS(Np->Num) ? "Yes" : "No",
IS_FILLMODE(Np->Num) ? "Yes" : "No",
! IS_ROMAN(Np->Num) ? "Yes" : "No"
);
#endif
--- 4283,4290
IS_PLUS(Np->Num) ? "Yes" : "No",
IS_MINUS(Np->Num) ? "Yes" : "No",
IS_FILLMODE(Np->Num) ? "Yes" : "No",
! IS_ROMAN(Np->Num) ? "Yes" : "No",
! IS_(Np->Num) ? "Yes" : "No"
);
#endif
***
*** 4618,4623
--- 4654,4680
int_to_roman(DatumGetInt32(DirectFunctionCall1(numeric_int4,
NumericGetDatum(x;
}
+ else if (IS_(&Num))
+ {
+ float8 val;
+
+ if (Num.pre != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid using of format ")));
+
+ val = DatumGetFloat8(DirectFunctionCall1(numeric_float8,
+ NumericGetDatum(value)));
+
+ numstr = orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);
+ len = snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%.*e", Num.post, val);
+ if (len > Num.pre + Num.post + 5)
+ {
+ numstr = (char *) palloc(Num.pre + Num.post + 6);
+ fill_str(numstr, '#', Num.pre + Num.post + 5);
+ *(numstr + Num.pre) = '.';
+ }
+ }
else
{
Numeric val = value;
***
*** 4699,4704
--- 4756,4773
*/
if (IS_ROMAN(&Num))
Re: [HACKERS] Closing some 8.4 open items
David Fetter writes: > On Fri, Apr 10, 2009 at 11:30:30AM -0400, Tom Lane wrote: >> Perhaps more to the point: the previous round of discussion about >> this already rejected the idea of treating window functions as a >> category fundamentally separate from plain functions --- that is, we >> are not following the "aggregate" model of having separate commands >> for aggregate functions. > I hadn't seen any such a consensus. We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be presenting a different world view than exists at the SQL level. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows installation service
Dann Corbit wrote: The Windows installation service uses pg_ctl to perform the network start-up operation. This program starts up the postmaster and exits. The net effect of performing the operation in this manner is that the Windows service manager sees the service as "not running" a few minutes after the startup is complete. I don't know what platform you're running on, but this isn't true of either of the machines I am looking at right now (one Vista, one WS2k3), which (correctly) show the service as started. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Tom Lane wrote: Andrew Dunstan writes: We still have a little work to do on dependencies in parallel pg_restore. The current test compares the candidate's locking dependencies with those of the running jobs, and allows the candidate is there isn't a match. That's not a broad enough test. The candidate will block if there's a currently running CREATE INDEX command on the table, for example, even though that doesn't require an exclusive lock. That's not catastrophic, in that the restore doesn't fail, but it's fairly bad because it reduces the achievable parallelism. Josh Berkus observed this during testing on a very large restore. Well, we certainly want to be able to run CREATE INDEXes in parallel, so this would appear to require hard-wiring some conception of shared versus exclusive lock into pg_restore. I think it might be a bit late to consider that for 8.4. I'm pretty sure I had the logic for this correct stuff originally, so I'm going to go back and check that. With luck it won't take long. It shouldn't hold up beta - it's just a bug we need to fix, and with any luck I'll actually have it fixed in the next few days. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Fri, Apr 10, 2009 at 11:30:30AM -0400, Tom Lane wrote: > David Fetter writes: > > Revised patch attached. \dw does not need an 'S' decorator, > > Yes it does. We have only painfully gotten to the point of having > consistent behavior across all the \d commands. We are not going to > break that consistency before it's even shipped. I'd be happy to revert that part. > Perhaps more to the point: the previous round of discussion about > this already rejected the idea of treating window functions as a > category fundamentally separate from plain functions --- that is, we > are not following the "aggregate" model of having separate commands > for aggregate functions. I hadn't seen any such a consensus. If anything, the consensus seemed to be going toward the \da and not away from it, hence the revised patch. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Bruce Momjian writes: > Yea, I thought we were going to do this: > Please find enclosed one way to handle it, this being prepending > WINDOW to the result types in \df. > but I don't see this behavior in CVS. IIRC, my original proposal involved adding something to the argument list --- it seems more natural to regard window-ness as having something to do with the arguments than the result. But that was shot down on the grounds of not fitting in well unless we wanted to add more decoration, like parens around the regular argument list. Another idea was to add a new column to the \df output to mark window-ness. Which, as I recall, *nobody* liked. But maybe if we only did it for \df+ it would be more tolerable? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
Tom Lane wrote: > David Fetter writes: > > Revised patch attached. \dw does not need an 'S' decorator, > > Yes it does. We have only painfully gotten to the point of having > consistent behavior across all the \d commands. We are not going > to break that consistency before it's even shipped. > > Perhaps more to the point: the previous round of discussion about this > already rejected the idea of treating window functions as a category > fundamentally separate from plain functions --- that is, we are not > following the "aggregate" model of having separate commands for > aggregate functions. So it's not apparent to me that a separate \dw > command is a good solution to start with. Yea, I thought we were going to do this: > > Please find enclosed one way to handle it, this being prepending > > WINDOW to the result types in \df. but I don't see this behavior in CVS. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unable to install tsearch2 on PostgreSQL 8.3.7 successfully
l0rins writes: > In version 8.1, tsearch2.sql contained SQL statements to create 4 pg_ts_* > tables: pg_ts_cfg, pg_ts_dict, ... and populate them with data. These > statements are missing in tsearch2.sql 8.3.7 version. So, I'm getting: > ERROR: relation "pg_ts_*" does not exist when trying to run tsearch > statements on version 8.3.2. You need to read the documentation --- 8.3's text search facility is quite a lot different from previous releases, and contrib/tsearch2 is now just a compatibility layer that does not fully hide the differences. http://www.postgresql.org/docs/8.3/static/textsearch-migration.html regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
David Fetter writes: > Revised patch attached. \dw does not need an 'S' decorator, Yes it does. We have only painfully gotten to the point of having consistent behavior across all the \d commands. We are not going to break that consistency before it's even shipped. Perhaps more to the point: the previous round of discussion about this already rejected the idea of treating window functions as a category fundamentally separate from plain functions --- that is, we are not following the "aggregate" model of having separate commands for aggregate functions. So it's not apparent to me that a separate \dw command is a good solution to start with. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Closing some 8.4 open items
On Tue, Apr 07, 2009 at 07:28:25PM -0700, David Fetter wrote:
> On Mon, Apr 06, 2009 at 10:51:22PM -0700, David Fetter wrote:
> > On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote:
> > > On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
> > > > David Fetter writes:
> > > > > On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
> > > > >> The \df thing? That's something it'd be okay to revisit during
> > > > >> beta, IMHO.
> > > >
> > > > > OK, I'll work on this tomorrow :)
> > > >
> > > > I think what we were lacking was consensus on what it should do, not
> > > > code ...
> > >
> > > I was thinking I'd knock out a proposal or two.
> >
> > Please find enclosed one way to handle it, this being prepending
> > WINDOW to the result types in \df.
> >
> > Another way, patch coming tomorrow, would be to add a \dw and remove
> > the functions where pg_proc.iswindowing is true from \df.
>
> Here's another way, adding \dw.
Revised patch attached. \dw does not need an 'S' decorator, and would
be confusing with one now as there are only a few windowing functions,
and all of those system.
Also included are SGML docs. Mea culpa.
There is one translatable string added. Sorry about that.
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 10d42ca..2e6484f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1257,6 +1257,18 @@ testdb=>
+\dw [ pattern ]
+
+
+Lists all windowing functions. If pattern is specified, only
+those windowing functions whose names match the pattern are listed.
+
+
+
+
+
+
\edit (or \e)
filename
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b39466d..b737daf 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -396,6 +396,9 @@ exec_command(const char *cmd,
case 'u':
success = describeRoles(pattern, show_verbose);
break;
+ case 'w':
+ success = describeWindowingFunctions(pattern);
+ break;
case 'F': /* text search
subsystem */
switch (cmd[2])
{
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 731baf8..5699e29 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -280,6 +280,9 @@ describeFunctions(const char *pattern, bool verbose, bool
showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND
n.nspname <> 'information_schema'\n");
+if (pset.sversion >= 80400)
+ appendPQExpBuffer(&buf, " AND NOT p.proiswindow\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname",
NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
@@ -3059,6 +3062,56 @@ listUserMappings(const char *pattern, bool verbose)
return true;
}
+bool
+describeWindowingFunctions(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 80400)
+ {
+ fprintf(stderr, _("The server (version %d.%d) does not support
windowing functions.\n"),
+ pset.sversion / 1, (pset.sversion / 100) %
100);
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname as \"%s\",\n"
+ " p.proname as \"%s\",\n"
+ "
pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
+ "
pg_catalog.pg_get_function_arguments(p.oid) as \"%s\""
+ "\nFROM pg_catalog.pg_proc p"
+ "\n LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
+ "WHERE p.proiswindow\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Result data type"),
+ gettext_noop("Argument data types"));
+
+ processSQLNamePattern(pset.db, &buf, pattern, true,
Re: [HACKERS] FW: Multiple character encodings within a single database/table?
Dann Corbit wrote: > > -Original Message- > > From: Dann Corbit > > Sent: Monday, March 23, 2009 3:50 PM > > To: pgsql-gene...@postgresql.org > > Subject: Multiple character encodings within a single database/table? > > > > If I have the C locale, can I have multiple character encodings > within: > > 1. A single database? > > 2. A single table? > > > > More specifically, I would like to be able to have Unicode columns and > > ASCII text columns within the same table. Is this possible? If so, > > how do I achieve it? > > > > It was not clear to me from: > > http://www.postgresql.org/docs/current/static/multibyte.html > > > > It seems to me from this statement: > > "It can be overridden when you create a database, so you can have > > multiple databases each with a different character set." > > That it may be database wide, but I am not sure that it is not > possible > > to have both ordinary char and Unicode in the same table. > > > > Possible or not? > > I know that this message is better placed on the general group, but I > got no reply in 24 hours. > So perhaps I can get a rise in hackers... ASCII is a non-encoding, in my mind, so it can be safely used with any other encoding in the same database or table. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows installation service
On Mon, Apr 6, 2009 at 9:32 PM, Dann Corbit wrote: > The Windows installation service uses pg_ctl to perform the network > start-up operation. > This program starts up the postmaster and exits. > The net effect of performing the operation in this manner is that the > Windows service manager sees the service as "not running" a few minutes > after the startup is complete. It also prevents proper pause and > restart of the service. Per our offlist conversation, this is not how it works. > As a suggestion: > Instead of installing pg_ctl as the service, start up postgres as the > service. This is how we did our Windows port. If the idea is appealing > to the PostgreSQL group, we can send our service code modifications for > review as a possible alternative to the current method. > > Another approach that could be equally helpful (along the same lines) is > to leave pg_ctl.exe in memory and allow it to control the program. Which is what does happen. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Documentation Update: WAL & Checkpoints
Michael Renner wrote: > Bruce Momjian wrote: > > Michael Renner wrote: > >> Hi, > >> > >> this is a small update to the first paragraph of the WAL configuration > >> chapter, going into more detail WRT redo vs. checkpoint records, since > >> the underlying behavior is currently only deducible from the source. I'm > >> not perfectly sure if I got everything right, so feel free to change as > >> necessary. > > [..] > > > I read over you patch and I was afraid it was trying to put too much > > information into a single paragraph, so I added a second paragraph that > > just talks about checkpoint smoothing. I did not address the issue of > > when the REDO WAL entry is written --- that is probably too much detail > > for our documentation. > > Too bad, understanding how this works is necessary to properly implement > more complex log shipping setups. Maybe /backend/access/transam/README > instead? Or specific "under the hood" paragraphs for selected areas of > the DBMS? Let's back up and let me ask why it is important for a user to know when the REDO record is written vs. when the checkpoint completes, and how that affects more complex log shipping setups. This detail is certainly appropriate for /backend/access/transam/README so if you could send in a patch, that would be great. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One click installer and pgInstaller collide
On Fri, Mar 27, 2009 at 12:47 AM, Dann Corbit wrote: > 64 bit Windows platform. > PG Installer version, installed first, was 8.3.5 > EnterpriseDB version was 8.3.7 > There was an error message (file in use) during installation of the > second tool set. > > It is also possible that it was caused by the small step from 8.3.5 to > 8.3.7 and the problem would have occurred with an upgrade using the same > version (I did not test to find out). Both installers upgraded that file for 8.3.6, hence the difference seen between 8.3.5 and 8.3.7. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] unable to install tsearch2 on PostgreSQL 8.3.7 successfully
Hello, I'm trying to install tsearch2 on PostgreSQL 8.3.7 but I'm struggling to get it run. I installed it successfully on PostgreSQL 8.1 but can't get it working on 8.3.7 using the same steps. The steps I'm using are: 1. yum install postgresql-contrib 2. psql mydb < tsearch2.sql In version 8.1, tsearch2.sql contained SQL statements to create 4 pg_ts_* tables: pg_ts_cfg, pg_ts_dict, ... and populate them with data. These statements are missing in tsearch2.sql 8.3.7 version. So, I'm getting: ERROR: relation "pg_ts_*" does not exist when trying to run tsearch statements on version 8.3.2. I'm attaching PostgreSQL 8.3.7 tsearch2.sql for reference. Am I missing something? Thanks in advance. l0rins http://www.nabble.com/file/p22989298/tsearch2.sql tsearch2.sql -- View this message in context: http://www.nabble.com/unable-to-install-tsearch2-on-PostgreSQL-8.3.7-successfully-tp22989298p22989298.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
[HACKERS] Windows installation service
The Windows installation service uses pg_ctl to perform the network start-up operation. This program starts up the postmaster and exits. The net effect of performing the operation in this manner is that the Windows service manager sees the service as "not running" a few minutes after the startup is complete. It also prevents proper pause and restart of the service. As a suggestion: Instead of installing pg_ctl as the service, start up postgres as the service. This is how we did our Windows port. If the idea is appealing to the PostgreSQL group, we can send our service code modifications for review as a possible alternative to the current method. Another approach that could be equally helpful (along the same lines) is to leave pg_ctl.exe in memory and allow it to control the program. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FW: Multiple character encodings within a single database/table?
> -Original Message- > From: Dann Corbit > Sent: Monday, March 23, 2009 3:50 PM > To: pgsql-gene...@postgresql.org > Subject: Multiple character encodings within a single database/table? > > If I have the C locale, can I have multiple character encodings within: > 1. A single database? > 2. A single table? > > More specifically, I would like to be able to have Unicode columns and > ASCII text columns within the same table. Is this possible? If so, > how do I achieve it? > > It was not clear to me from: > http://www.postgresql.org/docs/current/static/multibyte.html > > It seems to me from this statement: > "It can be overridden when you create a database, so you can have > multiple databases each with a different character set." > That it may be database wide, but I am not sure that it is not possible > to have both ordinary char and Unicode in the same table. > > Possible or not? I know that this message is better placed on the general group, but I got no reply in 24 hours. So perhaps I can get a rise in hackers... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] One click installer and pgInstaller collide
If you install with the PostgreSQL group's installer for Windows: http://www.postgresql.org/ftp/binary And then install a second instance of PostgreSQL using the EnterpriseDB one click installer: http://www.enterprisedb.com/products/pgdownload.do#windows An error may occur. It will occur, probably, either with one system or the other. The PG Admin III produce uses a DLL called libiconv-2.dll One of these DLL files has an entry point called libiconv_set_relocation_prefix and the other one does not. When you reference the DLL file, it will complain about an entry point that could not be located. It is probably a fairly esoteric issue, since most people (if they install multiple instances of a product) will install only a single version of it. But we support everything under the sun, so I purposely try to install as many versions as possible for testing. Anyway, I thought you might like to know about this odd collision. 64 bit Windows platform. PG Installer version, installed first, was 8.3.5 EnterpriseDB version was 8.3.7 There was an error message (file in use) during installation of the second tool set. It is also possible that it was caused by the small step from 8.3.5 to 8.3.7 and the problem would have occurred with an upgrade using the same version (I did not test to find out). I do not know if this message will get to the PG group. None of my messages has shown up this week for some reason. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Documentation Update: WAL & Checkpoints
Bruce Momjian wrote: Michael Renner wrote: Hi, this is a small update to the first paragraph of the WAL configuration chapter, going into more detail WRT redo vs. checkpoint records, since the underlying behavior is currently only deducible from the source. I'm not perfectly sure if I got everything right, so feel free to change as necessary. [..] I read over you patch and I was afraid it was trying to put too much information into a single paragraph, so I added a second paragraph that just talks about checkpoint smoothing. I did not address the issue of when the REDO WAL entry is written --- that is probably too much detail for our documentation. Too bad, understanding how this works is necessary to properly implement more complex log shipping setups. Maybe /backend/access/transam/README instead? Or specific "under the hood" paragraphs for selected areas of the DBMS? best regards, Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore dependencies
Andrew Dunstan writes: > We still have a little work to do on dependencies in parallel > pg_restore. The current test compares the candidate's locking > dependencies with those of the running jobs, and allows the candidate is > there isn't a match. That's not a broad enough test. The candidate will > block if there's a currently running CREATE INDEX command on the table, > for example, even though that doesn't require an exclusive lock. That's > not catastrophic, in that the restore doesn't fail, but it's fairly bad > because it reduces the achievable parallelism. Josh Berkus observed this > during testing on a very large restore. Well, we certainly want to be able to run CREATE INDEXes in parallel, so this would appear to require hard-wiring some conception of shared versus exclusive lock into pg_restore. I think it might be a bit late to consider that for 8.4. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New trigger option of pg_standby
On Fri, Apr 10, 2009 at 5:47 AM, Fujii Masao wrote: > One idea to solve this problem is to tell pg_standby as a > command-line argument about whether the trigger file can be > removed. That parameter value can be set to 'true' when the last > applied record is re-fetched. Though pg_standby is called to > restore timeline history files also after that point, the trigger file > is already unnecessary (pg_standby doesn't wait for history file). > > Specifically, if restore_command contains new % option (%e?), > it's replaced by the boolean value which indicates whether the > trigger file can be deleted. This value is set to 'true' when the > startup process re-fetches the last valid record, 'false' otherwise. > In smart mode, pg_standby determines whether to delete the > trigger file according to that value. > > Comments? Hmmm, it seems overly complicated but I don't know the code of pg_standby. > Or, do you have any better idea? Wouldn't it be possible to have a global switch (let's name it startCluster, default to false) which is set to true when the trigger file is found for the first time? You would then be able to remove the trigger file and let the cluster start by checking this variable. One more time, I don't know the code of pg_standby so it may be a stupid idea. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
