Re: [HACKERS] lag(bigint,int,int), etc?
On 27 Jun 2017, at 17:06, Merlin Moncure <mmonc...@gmail.com> wrote: > >> On Tue, Jun 27, 2017 at 10:01 AM, Colin 't Hart <colinth...@gmail.com> wrote: >> Hi, >> >> The following rather contrived example illustrates that lag(), lead() >> (and probably other functions) can't automatically cast an integer to >> a bigint: >> >> select lag(sum,1,0) over () from (select sum(generate_series) over >> (order by generate_series) from generate_series(1,10)) x; >> ERROR: function lag(bigint, integer, integer) does not exist >> LINE 1: select lag(sum,1,0) over () from (select sum(generate_series... >> ^ >> HINT: No function matches the given name and argument types. You >> might need to add explicit type casts. >> >> >> I guess this is because the lag() and lead() functions take any type, >> and hence the default must be of the same type. >> This had me stumped for a few while until I realised that the types >> were different. >> >> Would there be any way to implement an automatic conversion? >> >> On the off-chance that this is actually a bug, this is on 9.6.3, but >> it also occurs on 9.3.17 > > Why not cast the arguments? The first and the third argument have to > be the same, and the second argument is always int. > > merlin I know that I can cast. I'm wondering if it would be possible/desirable to implement automatic casting. Automatic casting works already for functions defined to take bigint and you pass in an integer. But not for these functions that take any type. /Colin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] lag(bigint,int,int), etc?
Hi, The following rather contrived example illustrates that lag(), lead() (and probably other functions) can't automatically cast an integer to a bigint: select lag(sum,1,0) over () from (select sum(generate_series) over (order by generate_series) from generate_series(1,10)) x; ERROR: function lag(bigint, integer, integer) does not exist LINE 1: select lag(sum,1,0) over () from (select sum(generate_series... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. I guess this is because the lag() and lead() functions take any type, and hence the default must be of the same type. This had me stumped for a few while until I realised that the types were different. Would there be any way to implement an automatic conversion? On the off-chance that this is actually a bug, this is on 9.6.3, but it also occurs on 9.3.17 Thanks, Colin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Did the "Full-text search in PostgreSQL in milliseconds" patches land?
Hi, I've been reading wiki.postgresql.org/images/2/25/Full-text_search_in_PostgreSQL_in_milliseconds-extended-version.pdf with interest and am wondering if these patches ever made it in to the "official" version of Postgresql? I've tried doing some of the queries as described in the slides using 9.5b1 but I get the "No operator matches the given name and argument type(s)." error. Thanks, Colin
Re: [HACKERS] TABLE not synonymous with SELECT * FROM?
David et al,
How about something like this?
Cheers,
Colin
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index e603b76..a68014b 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -33,13 +33,14 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
[ WITH [ RECURSIVE ] replaceable class=parameterwith_query/replaceable
[, ...] ]
-SELECT [ ALL | DISTINCT [ ON ( replaceable
class=parameterexpression/replaceable [, ...] ) ] ]
+{ SELECT [ ALL | DISTINCT [ ON ( replaceable
class=parameterexpression/replaceable [, ...] ) ] ]
* | replaceable class=parameterexpression/replaceable [ [ AS ]
replaceable class=parameteroutput_name/replaceable ] [, ...]
[ FROM replaceable class=parameterfrom_item/replaceable [, ...] ]
[ WHERE replaceable class=parametercondition/replaceable ]
[ GROUP BY replaceable class=parameterexpression/replaceable [, ...]
]
[ HAVING replaceable class=parametercondition/replaceable [, ...] ]
[ WINDOW replaceable class=parameterwindow_name/replaceable AS (
replaceable class=parameterwindow_definition/replaceable ) [, ...] ]
+| TABLE [ ONLY ] replaceable class=parametertable_name/replaceable [ * ]
}
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] replaceable
class=parameterselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC |
DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS {
FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=parametercount/replaceable | ALL } ]
@@ -60,8 +61,6 @@ SELECT [ ALL | DISTINCT [ ON ( replaceable
class=parameterexpression/replac
phraseand replaceable class=parameterwith_query/replaceable
is:/phrase
replaceable class=parameterwith_query_name/replaceable [ (
replaceable class=parametercolumn_name/replaceable [, ...] ) ] AS (
replaceable class=parameterselect/replaceable | replaceable
class=parametervalues/replaceable | replaceable
class=parameterinsert/replaceable | replaceable
class=parameterupdate/replaceable | replaceable
class=parameterdelete/replaceable )
-
-TABLE [ ONLY ] replaceable class=parametertable_name/replaceable [ * ]
/synopsis
/refsynopsisdiv
@@ -198,6 +197,27 @@ TABLE [ ONLY ] replaceable
class=parametertable_name/replaceable [ * ]
literalUPDATE/literal privilege as well (for at least one column
of each table so selected).
/para
+
+ refsect2 id=SQL-TABLE
+ titleliteralTABLE/literal Command/title
+
+ para
+The command
+programlisting
+TABLE replaceable class=parametername/replaceable
+/programlisting
+is equivalent to
+programlisting
+SELECT * FROM replaceable class=parametername/replaceable
+/programlisting
+It can be used as a top-level command or as a space-saving syntax
+variant in parts of complex queries. Only the literalWITH/,
literalORDER BY/, literalLIMIT/,
+and Locking clauses and set operations can be used with commandTABLE/;
the
+literalWHERE/ clause and any form of aggregation cannot be used.
+
+Note that on this page and other places in the documentation, where
commandSELECT/ is mentioned, commandTABLE/ is also assumed, subject to
the restrictions mentioned here.
+ /para
+ /refsect2
/refsect1
refsect1
@@ -211,7 +231,7 @@ TABLE [ ONLY ] replaceable
class=parametertable_name/replaceable [ * ]
subqueries that can be referenced by name in the primary query.
The subqueries effectively act as temporary tables or views
for the duration of the primary query.
-Each subquery can be a commandSELECT/command,
commandVALUES/command,
+Each subquery can be a commandSELECT/command, commandTABLE/,
commandVALUES/command,
commandINSERT/command, commandUPDATE/command or
commandDELETE/command statement.
When writing a data-modifying statement (commandINSERT/command,
@@ -1437,23 +1457,6 @@ SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss
ORDER BY column1;
/para
/caution
/refsect2
-
- refsect2 id=SQL-TABLE
- titleliteralTABLE/literal Command/title
-
- para
-The command
-programlisting
-TABLE replaceable class=parametername/replaceable
-/programlisting
-is completely equivalent to
-programlisting
-SELECT * FROM replaceable class=parametername/replaceable
-/programlisting
-It can be used as a top-level command or as a space-saving syntax
-variant in parts of complex queries.
- /para
- /refsect2
/refsect1
refsect1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Sort contents entries in reference documentation
Hi, While looking at the documentation on SELECT I noticed that the entries in reference.sgml aren't sorted correctly -- psql \h does have them in the correct order. Attached a trivial patch to fix this. In addition, reference.sgml doesn't have entries for TABLE or WITH which should link to ref/select.sgml but I couldn't figure out how to achieve this. psql \h does have this so the reference page probably should too. Cheers, Colin diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 14e217a..d967f66 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -40,8 +40,8 @@ alterDatabase; alterDefaultPrivileges; alterDomain; - alterExtension; alterEventTrigger; + alterExtension; alterForeignDataWrapper; alterForeignTable; alterFunction; @@ -84,8 +84,8 @@ createConversion; createDatabase; createDomain; - createExtension; createEventTrigger; + createExtension; createForeignDataWrapper; createForeignTable; createFunction; @@ -124,8 +124,8 @@ dropConversion; dropDatabase; dropDomain; - dropExtension; dropEventTrigger; + dropExtension; dropForeignDataWrapper; dropForeignTable; dropFunction; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Links in README.git are broken
Hi, While trying to find instructions to build documentation, I noticed that the links in README.git are broken, both redirect to http://www.postgresql.org/docs/devel/static/ In addition, why isn't INSTALL stored in git? Cheers, Colin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TABLE not synonymous with SELECT * FROM?
Hi, According to http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-TABLE The command TABLE name is completely equivalent to SELECT * FROM name It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. However, this isn't true: colin@corundum:~$ psql psql (9.4devel, server 9.3.1) Type help for help. eyedb=# table x; a --- 1 (1 row) eyedb=# table x limit 10; a --- 1 (1 row) eyedb=# table x where a = 1; ERROR: syntax error at or near where LINE 1: table x where a = 1; ^ eyedb=# I would've thought it was implemented as a shortcut for SELECT * FROM at the parse level (ie encounter TABLE and insert SELECT * FROM into the parse tree and continue), but it seems there is more to it. Is the documentation wrong? Or is something broken? Cheers, Colin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Execute query with EXCEPT, INTERSECT as anti-join, join?
Hi, I can't get Postgresql to execute a query with EXCEPT (or INTERSECT) as an anti-join (or join). Is this even possible? If not currently possible, is this something we would like to have? Cheers, Colin -- 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] Execute query with EXCEPT, INTERSECT as anti-join, join?
On 11 November 2013 14:34, Tom Lane t...@sss.pgh.pa.us wrote: Colin 't Hart colinth...@gmail.com writes: I can't get Postgresql to execute a query with EXCEPT (or INTERSECT) as an anti-join (or join). Is this even possible? No, and it probably won't ever be, since the semantics aren't the same. EXCEPT/INTERSECT imply duplicate elimination. Can't we just use DISTINCT for that? Given a query query_1 EXCEPT query_2 isn't it always possible to rewrite this as select distinct * from (query_1) q1 where not exists (select 1 from (query_2) q2 where q1.col1 = q2.col1 and q1.col2 = c2.col2 and ... and q1.colN = q2.colN) ? Regards, Colin -- 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] Execute query with EXCEPT, INTERSECT as anti-join, join?
On 11 November 2013 15:16, Tom Lane t...@sss.pgh.pa.us wrote: Colin 't Hart co...@sharpheart.org writes: On 11 November 2013 14:34, Tom Lane t...@sss.pgh.pa.us wrote: No, and it probably won't ever be, since the semantics aren't the same. EXCEPT/INTERSECT imply duplicate elimination. Can't we just use DISTINCT for that? If you have to do a DISTINCT it's not clear to me that you're going to get much win. (The bigger picture here is that pretty much zero optimization effort has been spent on EXCEPT/INTERSECT, because they're just not used that much compared to other places where we could put that effort.) I'm asking because I just encountered several cases where the anti-join was *much* faster. In each case query_1's result was relatively small compared to query_2's result or the related rows from query_2 were a much smaller set than the whole result of query_2. In these cases, when the executor new how the two halves of the query were related -- and that's the crux here: by writing EXCEPT the executor couldn't determine how the two halves of the query were related -- the anti-join was about 1000 times faster. I think it's similar to the NOT IN which most DBMSes solved about 20 years ago but before that everyone used to rewrite by hand as NOT EXISTS: sometimes we want to write query as EXCEPT because it's clearer but execute it as an anti-join with DISTINCT. Would these be difficult to build in? While I know a lot about how DBMS engines work I've not hacked at PG internals. I'd be more than willing to look at it, but could use some pointers as to where to start. In particular, does PG rewrite queries in any way? Is it possible to simply rewrite the query and then pass to the optimizer to see if it would result in a better plan? if I can improve EXCEPT, the same could also be applied to INTERSECT as a DISTINCT join. Thanks regards, Colin -- 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] TABLE not synonymous with SELECT * FROM?
On 11 November 2013 15:03, Tom Lane t...@sss.pgh.pa.us wrote: Colin 't Hart co...@sharpheart.org writes: I would've thought it was implemented as a shortcut for SELECT * FROM at the parse level (ie encounter TABLE and insert SELECT * FROM into the parse tree and continue), but it seems there is more to it. If you look at the PG grammar you'll see that TABLE relation_expr appears as one variant of simple_select, which means that you can attach WITH, ORDER BY, FOR UPDATE, or LIMIT to it. The other things you mention are only possible in a clause that actually starts with SELECT. AFAICS, this comports with the SQL standard's syntax specification (look at the difference between query specification and query expression). The comment for simple_select saith * Note that sort clauses cannot be included at this level --- SQL requires * SELECT foo UNION SELECT bar ORDER BY baz * to be parsed as * (SELECT foo UNION SELECT bar) ORDER BY baz * not * SELECT foo UNION (SELECT bar ORDER BY baz) * Likewise for WITH, FOR UPDATE and LIMIT. Therefore, those clauses are * described as part of the select_no_parens production, not simple_select. * This does not limit functionality, because you can reintroduce these * clauses inside parentheses. Makes sense. I had been wondering about that order by stuff too. Methinks we should fix the documentation, something like: The command TABLE name is equivalent to SELECT * FROM name It can be used as a top-level command or as a space-saving syntax variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT, and Locking clauses and set operations can be used with TABLE; the WHERE and ORDER BY clauses and any form of aggregation cannot be used. Cheers, Colin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql built from git still reports 9.3devel
Hi, git status says that I'm on branch master. psql reports 9.3devel, yet I expected it to report 9.4devel Is this expected behaviour? I've looked at the developer's FAQ and Wiki but couldn't find anything about this. Cheers, Colin
Re: [HACKERS] psql built from git still reports 9.3devel
make distclean ./configure make sudo make install helped. Cheers, Colin On 21 October 2013 14:25, Michael Paquier michael.paqu...@gmail.com wrote: On Mon, Oct 21, 2013 at 9:09 PM, Colin 't Hart colinth...@gmail.com wrote: git status says that I'm on branch master. psql reports 9.3devel, yet I expected it to report 9.4devel Is this expected behaviour? I've looked at the developer's FAQ and Wiki but couldn't find anything about this. You should check your installation, here is what I am getting in my dev box: $ psql --version psql (PostgreSQL) 9.4devel Regards, -- Michael
[HACKERS] [PATCH] Add \ns command to psql
Hi, Here's a new version of a small patch to psql I'm using locally. It adds a command \ns to psql which is a shortcut to set the SEARCH_PATH variable. I'd like to make a case for including this patch as it makes use of schemas/namespaces much easier. There was resistance to including this before just because some developers don't use schemas very much. But we use a lot of them. And I'm sure we're not alone. Previously I used just \n but there was some resistance to this because the single letter commands are becoming scarce. I've also added tab completion making this command much more useful. I don't think tab completition would be possible if this command was defined as a variable (which was another suggestion offered at the time). Cheers, Colin command.c.diff Description: Binary data tab-complete.c.diff Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] psql \n shortcut for set search_path =
On 10 July 2012 18:00, Tom Lane t...@sss.pgh.pa.us wrote: Josh Kupershmidt schmi...@gmail.com writes: On Tue, Jul 10, 2012 at 2:09 AM, Colin 't Hart co...@sharpheart.org wrote: Attached please find a trivial patch for psql which adds a \n meta command as a shortcut for typing set search_path =. I think the use-case is a bit narrow: saving a few characters typing on a command not everyone uses very often (I don't), at the expense of adding yet another command to remember. Another point here is that we are running low on single-letter backslash command names in psql. I'm not sure that SET SEARCH_PATH is so useful as to justify using up one of the ones that are left. ISTM there was some discussion awhile back about user-definable typing shortcuts in psql. I don't recall any details, but being able to set up SET SEARCH_PATH as a user-definable shortcut if it's useful to you would eliminate the question about whether it's useful to everyone. And these could be setup to be available on psql startup by adding them to .psqlrc While I like my \n idea (heck, I thought of it :-) ), this would be a very good generic solution. I did a quick search but couldn't find the relevant discussion: do you remember roughly when it was? If I find it I could have a go at trying to implement it, but it might exceed my ability in C... Cheers, Colin
Re: [HACKERS] [PATCH] psql \n shortcut for set search_path =
On 10 July 2012 18:24, David Fetter da...@fetter.org wrote: On Tue, Jul 10, 2012 at 12:00:06PM -0400, Tom Lane wrote: Josh Kupershmidt schmi...@gmail.com writes: On Tue, Jul 10, 2012 at 2:09 AM, Colin 't Hart co...@sharpheart.org wrote: Attached please find a trivial patch for psql which adds a \n meta command as a shortcut for typing set search_path =. I think the use-case is a bit narrow: saving a few characters typing on a command not everyone uses very often (I don't), at the expense of adding yet another command to remember. Another point here is that we are running low on single-letter backslash command names in psql. I'm not sure that SET SEARCH_PATH is so useful as to justify using up one of the ones that are left. ISTM there was some discussion awhile back about user-definable typing shortcuts in psql. In some sense, we already have them: \set FOO 'SELECT * FROM pg_stat_activity;' ... :FOO Was there more to it? Can I pass a parameter to :FOO ? Cheers, Colin
[HACKERS] [PATCH] psql \n shortcut for set search_path =
Hi, Attached please find a trivial patch for psql which adds a \n meta command as a shortcut for typing set search_path =. This allows you to navigate a database very quickly in psql as follows: \dn \n my_schema \d \d my_table etc. Not yet done: updating documentation (psql internal help, psql man page, main documentation). If this is something that is desired (I hope so as this is something I now use a lot), I will update the documentation and resubmit. Cheers, Colin psql_slash_n.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] psql \n shortcut for set search_path =
Hi, Attached please find a trivial patch for psql which adds a \n meta command as a shortcut for typing set search_path =. This allows you to use psql as follows: \dn \n my_schema \d \d my_table etc. Not yet done: updating documentation (psql internal help, psql man page, main documentation). If this is something that is desired (I hope so as this is something I now use a lot), I will update the documentation and resubmit. Cheers, Colin psql_slash_n.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] documentation udpates to pgupgrade.html
Bruce, To query for Postgresql services on Windows use: sc query type= service | find postgresql On my machine this yields: SERVICE_NAME: postgresql-9.0 DISPLAY_NAME: postgresql-9.0 - PostgreSQL Server 9.0 NB the space after type= is very important, don't ask me why... I prefer to use 'sc start servicename' and 'sc stop servicename', then you can use one tool for everything. The following shows these commands (and the query command) in action. C:\Documents and Settings\Administratorsc stop postgresql-9.0 SERVICE_NAME: postgresql-9.0 TYPE : 10 WIN32_OWN_PROCESS STATE : 3 STOP_PENDING (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN) WIN32_EXIT_CODE: 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x2 WAIT_HINT : 0x2710 C:\Documents and Settings\Administratorsc start postgresql-9.0 SERVICE_NAME: postgresql-9.0 TYPE : 10 WIN32_OWN_PROCESS STATE : 2 START_PENDING (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)) WIN32_EXIT_CODE: 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x7d0 PID: 3732 FLAGS : C:\Documents and Settings\Administratorsc query postgresql-9.0 SERVICE_NAME: postgresql-9.0 TYPE : 10 WIN32_OWN_PROCESS STATE : 4 RUNNING (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN) WIN32_EXIT_CODE: 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 Hope this isn't too much info and answers all your questions :-) Regards, Colin -- 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 udpates to pgupgrade.html
Oops. Apparently type= service is the default, so we can remove that bit. Then we should add state= all. The default = active, a third option = inactive. So: sc query state= all should list all services, in all states. And then we pipe to find which is the Windows equivalent of grep, but it needs to have its parameter in double quotes. So: sc query state= all | find postgresql Regards, Colin On 29 September 2010 19:26, Massa, Harald Armin c...@ghum.de wrote: Colin, To query for Postgresql services on Windows use: sc query type= service | find postgresql sad news is that (at least on my computer) it only finds running services. Harald -- GHUM GmbH Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 Amtsgericht Stuttgart, HRB 734971 - persuadere. et programmare -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What happened to the is_type family of functions proposal?
Hi,
Back in 2002 these were proposed, what happened to them?
http://archives.postgresql.org/pgsql-sql/2002-09/msg00406.php
Also I note:
co...@ruby:~/workspace/eyedb$ psql
psql (8.4.4)
Type help for help.
colin= select to_date('731332', 'YYMMDD');
to_date
1974-02-01
(1 row)
colin=
The fact that this wraps would seem to me to make the implementation of
is_date() difficult.
I'm trying to query character strings for valid dates but can't see how to
do this quickly... but for that discussion I will move to pgsql-general :-)
Cheers,
Colin
Re: [HACKERS] What happened to the is_type family of functions proposal?
On 20 September 2010 16:54, Andrew Dunstan and...@dunslane.net wrote: On 09/20/2010 10:29 AM, Colin 't Hart wrote: Hi, Back in 2002 these were proposed, what happened to them? http://archives.postgresql.org/pgsql-sql/2002-09/msg00406.php 2002 is a long time ago. snip I think to_date is the wrong gadget to use here. You should probably be using the date input routine and trapping any data exception. e.g.: test_date := date_in(textout(some_text)); In plpgsql you'd put that inside a begin/exception/end block that traps SQLSTATE '22000' which is the class covering data exceptions. So it's not possible using pure SQL unless one writes a function? Are the is_type family of functions still desired? Also, where are the to_type conversions done? Thanks, Colin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO note
Hi, I note that the implementation of tab completion for SET TRANSACTION in PSQL could benefit from the implementation of autonomous transactions (also TODO). Regards, Colin
Re: [HACKERS] Porting to Native WindowsNT/2000
Ian Lance Taylor ( others) wrote: This is true. However, a process-pool architecture would benefit Postgres on other platforms besides Windows. Postgresql has been ported to the HP3000 MPE/iX operating system, for example, which is POSIX-compliant, but has an awfully slow fork(). On the other hand, POSIX-compliant systems generally are moving toward a faster and faster fork, as they should given the nature of POSIX programs. A process pool architecture for a system like Postgres would require very careful attention to memory usage, in order to be able to return swap space to the system or at least avoid using it. Otherwise, I believe the different processes would fragment memory over time, decreasing system performance. Process pools work best for systems with fixed memory usage. What about a pre-forked model? What about using the Apache Portable Runtime? The Apache Postgres licenses are compatible, are they not? Cheers, Colin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: List response time...
Marc wrote: Actually, the 'multi-day' delay is generally related to posts from ppl that aren't subscribed to the lists that I have to approve manually ... Is there a quick(er) way to 'subscribe, set nomail' on all the mailing lists that are mirrored to news.postgresql.org? I prefer to read/post through the news server and I've had to subscribe manually to most lists. Cheers, Colin ---(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
[HACKERS] Re: bugs - lets call an exterminator!
Vince asks: Everybody keeps saying bugzilla. What EXACTLY will bugzilla do for us that would make me want to learn it and install it? BTW, the current wheel was invented a year ago 'cuze nothing really fit what we needed. The reasons I would choose Bugzilla: 1. It's *not* written by us so (in theory) we don't have to waste time developing yet another bug tracking solution. 2. It sends email to people involved with a bug whenever the detail associated with that bug is modified. This includes the reporter, who often will feedback that it now works, at which time the fixer or the reporter can mark the bug as fixed. 3. It complains when a NEW bug hasn't been looked at for /n/ days -- this means that any not-a-bug's will be closed, while any that are really bugs will be accepted. 4. Good query facilities, if a little complex to use. 5. I think Bugzilla's concepts of products, components and versions fit the way we work. I envisage that 'Postgres', 'Interfaces', 'Languages' might be products that we would have. Within 'Postgres' we would have the various subsystems that make up the core. Within 'Interfaces' we would have 'JDBC', 'ODBC' etc. Within 'Languages' we would have 'PL/pgSQL' etc. Arguments accepted. There are other tools the Mozilla project uses that we could also use: Tinderbox -- continuous automated builds, including subsequent regression tests (useful for seeing who broke CVS). Bonsai -- CVS integration for Bugzilla Cheers, Colin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: Link to bug webpage
Matthew T. O'Connor volunteered: I don't know what a kibo is, but I would be willing to put in some time helping maintaing a bug reporting system. One of the helpful things with bugzilla setup with some other big projects is that the bug gets assigned to a developer and the bug submitter gets emailed updates any time there is a status change. I have some experience in setting up Bugzilla, although we currently run it on MySQL, but we are looking to move it off MySQL and probably onto Postgres anyway. I'd also volunteer to help admin a Bugzilla setup. Do we have a third person? Cheers, Colin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Re: Link to bug webpage / Bugzilla?
Jan Wieck said: Has anyone thought of using Bugzilla? (It is MySQL based, of course) but it might answer the bug database issues. (If you guys want a bug database) Bug tracking software that doesn't use transactions and referential integrity in a multiuser environment? Sounds like a bug by design to me, which are known not to be traceable by software. So the system might trace it's own bugs while never catching the biggies ... I agree, of course. That's why we'd use a Postgres port of Bugzilla: http://groups.google.com/groups?hl=ensafe=offth=9efb66b03a69b9fd,1 and available at ftp://people.redhat.com/dkl/pgzilla-latest.tar.gz Cheers, Colin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Link to bug webpage
We could install the Postgres version of Bugzilla. Yes, there's a version that runs on Postgres rather than MySQL. That way we don't have to maintain the bug system. Ok the functionality as well as the menu item are gone. You do realize it's going to give the impression that we're trying to hide something, don't you? Vince. Cheers, Colin ---(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
[HACKERS] Re: Link to bug webpage
Philip Warner wrote: I don't think this is a good solution. We really do need a list of bugs. We probably need to list status and the releases they apply to. Bugzilla can do this -- it has the concept of a Milestone and a Version. I don't think anybody but the most naieve (or biased) users expect software to be bug free, and the number of bugs grows with the complexity of the components. The fact we have a lot of bugs is to be expected. The fact that we don't mark them as fixed is just sloppy. Bugzilla makes it fairly painless to mark a bug as fixed. Please reinstate the page, and allow some facility to edit them. I will try to work through them *slowly* to verify they are reproducible/not reproducible in 7.1.3 and in the current CVS, then mark them as fixed in the appropriate release. Hopefully other people will do the same with bugs they know about. Does this seem reasonable? If we install Bugzilla (running on Postgres, not MySQL, obviously) we save ourselves the hassle of maintaining the bug system, and we can showcase that Postgres *can* be to back a web-based system :-) Cheers, Colin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Re: Re: Storing XML in PostgreSQL
Should we add this to /contrib? I think so, at least until we get something better. Cheers, Colin ---(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
