Re: [GENERAL] timestamp default values
> [ shrug... ] This is just a variant of the choose-a-new-function-name > game. If we are going to choose a new function name, choosing one that > collides with an existing name (obsolete or not) doesn't seem like a > win to me. You could just as well choose another name, and avoid > angering whoever out there might still be using timenow(). Agreed. It looks like finding a good name for this function would in fact be the hardest part of adding it ... the namespace for now()-like functions is quite cluttered. I'd be inclined to go with "gettime()", but I'm certainly open to suggestions. > BTW: at least with our current interpretation of these datatypes, the > only type that is sensible for a now()-like function to return is > timestamptz. Not plain timestamp; that cannot be considered to > represent a well-defined instant at all. True. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] timestamp default values
Brendan Jurd <[EMAIL PROTECTED]> writes: > If the current implementation of timenow() is truly obsolete, would it > be verboten to change its return type? We could rewrite the function > to return timestamp, for example. [ shrug... ] This is just a variant of the choose-a-new-function-name game. If we are going to choose a new function name, choosing one that collides with an existing name (obsolete or not) doesn't seem like a win to me. You could just as well choose another name, and avoid angering whoever out there might still be using timenow(). BTW: at least with our current interpretation of these datatypes, the only type that is sensible for a now()-like function to return is timestamptz. Not plain timestamp; that cannot be considered to represent a well-defined instant at all. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] timestamp default values
> I'd be interested to see how you do that, considering that abstime > can't store fractional seconds. timenow() is even more obsolete than > timeofday() --- AFAICS it's not documented at all, anywhere. The whole > abstime datatype is obsolete, actually, and will have to go away > sometime before 2038. > If the current implementation of timenow() is truly obsolete, would it be verboten to change its return type? We could rewrite the function to return timestamp, for example. -- BJ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] timestamp default values
Brendan Jurd <[EMAIL PROTECTED]> writes: > Does anybody know why this function returns text? The documentation > cites "historical reasons". Are any of those historical reasons still > relevant? Backwards compatibility --- changing the result datatype would surely break some applications. I'm a tad worried even about changing the output format, but we can probably get away with that, especially if we do it as part of a new release. (Back-patching such a change seems like a no-no.) There has been some talk of bypassing this problem by inventing a whole new function with a different name, but no one's put up a proposal that garnered enough support. (I seem to recall a tentative consensus on inventing a single function that would take an argument to say whether you wanted transaction start time, statement start time, or true current time --- but that fell apart when it was pointed out that we would have to label such a function volatile, thereby making it unindexable. We really need to use differently-named functions for these things.) > Or perhaps I could add an optional precision parameter to timenow(), > so you could call timenow(6) and achieve the same thing. I'd be interested to see how you do that, considering that abstime can't store fractional seconds. timenow() is even more obsolete than timeofday() --- AFAICS it's not documented at all, anywhere. The whole abstime datatype is obsolete, actually, and will have to go away sometime before 2038. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] timestamp default values
On 8/7/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Brendan Jurd <[EMAIL PROTECTED]> writes: > > Are there any good reasons why the output of timeofday() needs to > > include the three letter day-of-week? > > If we are going to change it, I'd be inclined to make it output the > canonical ISO format (-MM-DD HH:MM:SS.-TZ). Or perhaps the > format should be the same as whatever the current DateStyle setting > would emit. > > regards, tom lane > Going to ISO format would be a big improvement. Does anybody know why this function returns text? The documentation cites "historical reasons". Are any of those historical reasons still relevant? I would suggest that in the vast majority of cases, the desired type from timeofday() is timestamp. Wouldn't it make more sense to have it return timestamp, and then use to_char() for those cases where we want a textual representation of the time? Currently the function gets the time as a "pg_time_t", then converts it into a string with pg_strftime(). Then, in order to make the value useful we run that string through a datetime parser. I realise it's not a major performance hit, but it's just not elegant to run all these superfluous conversions. In the interests of backwards compatibility, how about I just write a new function that does the same thing as timeofday(), but returns timestamp? Or perhaps I could add an optional precision parameter to timenow(), so you could call timenow(6) and achieve the same thing. Objections? -- BJ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ACM Sigmod interview with Bruce Lindsay
Bruce Momjian writes: > Mike Mascari wrote: >> How long has a partial index implementation been in PostgreSQL? > We have had partial indexes since the Berkeley days, per-1996, but we > have improved them quite a bit. We inherited code for the feature from Berkeley, but it was broken for some considerable period of time. I find this in the CVS logs: 2001-07-16 01:06 tgl * doc/src/sgml/indices.sgml, doc/src/sgml/ref/create_index.sgml, src/backend/bootstrap/bootstrap.c, src/backend/catalog/index.c, src/backend/commands/command.c, src/backend/commands/indexcmds.c, src/backend/executor/execUtils.c, src/backend/optimizer/path/indxpath.c, src/backend/optimizer/util/pathnode.c, src/backend/parser/analyze.c, src/backend/parser/gram.y, src/backend/parser/keywords.c, src/backend/tcop/utility.c, src/backend/utils/adt/ruleutils.c, src/backend/utils/adt/selfuncs.c, src/bin/pg_dump/pg_dump.c, src/bin/pg_dump/pg_dump.h, src/include/catalog/index.h, src/include/catalog/pg_proc.h, src/include/config.h.in, src/include/commands/defrem.h, src/include/nodes/execnodes.h, src/include/utils/builtins.h, src/interfaces/ecpg/preproc/keywords.c, src/interfaces/ecpg/preproc/preproc.y, src/test/regress/expected/create_index.out, src/test/regress/expected/portals_p2.out, src/test/regress/expected/sanity_check.out, src/test/regress/expected/select.out, src/test/regress/sql/create_index.sql, src/test/regress/sql/portals_p2.sql, src/test/regress/sql/select.sql: Partial indexes work again, courtesy of Martijn van Oosterhout. Note: I didn't force an initdb, figuring that one today was enough. However, there is a new function in pg_proc.h, and pg_dump won't be able to dump partial indexes until you add that function. This is reflected in the 7.2 release notes. In the context of the interview, I agree it's pretty amusing --- I would've thought the academic DBMS community would see partial indexes as ancient news. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] timestamp default values
Brendan Jurd <[EMAIL PROTECTED]> writes: > Are there any good reasons why the output of timeofday() needs to > include the three letter day-of-week? If we are going to change it, I'd be inclined to make it output the canonical ISO format (-MM-DD HH:MM:SS.-TZ). Or perhaps the format should be the same as whatever the current DateStyle setting would emit. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] timestamp default values
Here we go ... line 498 of backend/utils/adt/datetime.c:
/* Used for SET australian_timezones to override North American ones */
static datetkn australian_datetktbl[] = {
{"acst", TZ, POS(38)}, /* Cent. Australia */
{"cst", TZ, POS(42)}, /* Australia Central Std Time */
{"east", TZ, POS(40)}, /* East Australian Std Time */
{"est", TZ, POS(40)}, /* Australia Eastern Std Time */
{"sat", TZ, POS(38)},
};
"sat" is being parsed as a timezone. Probably "South Australian Time".
I'm guessing the parser is throwing an error because it thinks I'm
trying to give it two different timezones.
Are there any good reasons why the output of timeofday() needs to
include the three letter day-of-week? Could we either remove it, or
perhaps change it to the full day-of-week (e.g. "Saturday"), which
cannot be confused with a timezone?
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] timestamp default values
On 8/7/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sun, Aug 07, 2005 at 12:05:43PM +1000, Brendan Jurd wrote: > > => select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > > ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 06 > > 12:00:43.668919 2005 EST" > > What are your timezone and australian_timezones settings? I can > duplicate the problem thusly: > > set australian_timezones to on; > set timezone to 'EST'; > select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > ERROR: invalid input syntax for type timestamp: "Sat Aug 06 12:00:43.668919 > 2005 EST" > > set australian_timezones to off; > select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > timestamp > > 2005-08-06 12:00:43.668919 > (1 row) > > -- > Michael Fuhr > => show timezone; TimeZone --- Australia/ACT (1 row) => show australian_timezones; australian_timezones -- on (1 row) -- BJ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ACM Sigmod interview with Bruce Lindsay
Mike Mascari wrote: > I find this Q & A a bit amusing: > > http://www.sigmod.org/sigmod/record/issues/0506/p71-column-winslet.pdf > > > > Q. If you magically had enough extra time to do one additional thing at > work that you're not doing now, what would it be? > > A. I think I would work on indexing a little harder. > > Q. What aspect of indexing? > > A. Oh, there are many aspects of indexing that I think need improvement. > I think we can do a better job in the searching. I think there are > exciting things to do in multi-dimensional indexing. I think there are a > lot more advanced ways that we can use database indexes for indexing on > columns that contain sequences or XML data, and for partial indexing. > With partial indexing, we would index only some of the rows, based on > some predicate. For example, we might not index the NULL values, or we > might index only those salaries greater than $100K. > > > > How long has a partial index implementation been in PostgreSQL? I seem > to recall it being brought up to speed around six years ago. The > underlying code may have been in there for much, much longer... We have had partial indexes since the Berkeley days, per-1996, but we have improved them quite a bit. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] timestamp default values
On Sun, Aug 07, 2005 at 12:05:43PM +1000, Brendan Jurd wrote: > => select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; > ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 06 > 12:00:43.668919 2005 EST" What are your timezone and australian_timezones settings? I can duplicate the problem thusly: set australian_timezones to on; set timezone to 'EST'; select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; ERROR: invalid input syntax for type timestamp: "Sat Aug 06 12:00:43.668919 2005 EST" set australian_timezones to off; select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; timestamp 2005-08-06 12:00:43.668919 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] timestamp default values
On 8/7/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sat, Aug 06, 2005 at 10:06:35AM -0400, Tom Lane wrote: > > Can anyone else duplicate the problem? > > I couldn't duplicate the problem in 8.0.2 or in any other version > from 7.2.8 through HEAD (latest CVS for all). > > Brendan, if you execute "\set VERBOSITY verbose" in psql and then > generate the errors, what's the complete error message? > > Tom (or anybody else), could the errors could be due to the "if > (VARSIZE(str) - VARHDRSZ > MAXDATELEN)" checks in text_date() and > text_timestamp()? Could an encoding affect that? That's why I > suggested increasing the verbosity: so we could see where the error > is being raised. > > -- > Michael Fuhr > Okay, I have some more information on this. The error only occurs for some very particular outputs of timeofday(). Namely, Saturdays. No I'm not kidding. When I tried to diagnose the problem today (Sun Aug 07), everything worked fine. I was able to replicate the error, however, by deliberately specifying yesterday's timeofday() string: => select timeofday()::timestamp; timeofday 2005-08-07 12:00:43.668919 (1 row) => select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp; ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 06 12:00:43.668919 2005 EST" LOCATION: DateTimeParseError, datetime.c: => select 'Sat Aug 13 12:00:43.668919 2005 EST'::timestamp; ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 13 12:00:43.668919 2005 EST" LOCATION: DateTimeParseError, datetime.c: => select 'Sat Aug 13 12:00:43.668919 2005 CST'::timestamp; ERROR: 22007: invalid input syntax for type timestamp: "Sat Aug 13 12:00:43.668919 2005 CST" LOCATION: DateTimeParseError, datetime.c: => select 'Sun Aug 07 12:00:43.668919 2005 EST'::timestamp; timestamp 2005-08-07 12:00:43.668919 (1 row) => select 'Aug 06 12:00:43.668919 2005 EST'::timestamp; timestamp 2005-08-06 12:00:43.668919 (1 row) => select 'Sat Aug 06 12:00:43.668919 2005'::timestamp; timestamp 2005-08-06 12:00:43.668919 (1 row) From the last few examples, you can see that the conversion succeeds when "Sat" is present at the start, or when the timezone is present at the end, but not when both are present, as in the timeofday() output. Perhaps the parser is treating the string "Sat" as a timezone token? -- BJ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgresql Hosting
Thanks , Would seem there is no problem eith cpanel + postgresql. thanks for the link to a2hosting I will look them up. John DeSoi wrote: On Aug 4, 2005, at 10:28 PM, Richard Sydney-Smith wrote: I have asked my internet host to include postgresql as part of their service but it seems that there are issues in getting it to work with "cpanel" which is their support service for their clients. Is their a reason why Postgresql is harder to host than mysql? Is their any docs I can point the server admin to that would help him? If not what service providers are people recommending? I'm a satisfied customer of http://www.a2hosting.com. They use cpanel, so I'm not sure what the problem is. And the only thing you use cpanel for is to create users and databases. Otherwise you use phpPgAdmin (or psql if your account has shell access). John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ACM Sigmod interview with Bruce Lindsay
I find this Q & A a bit amusing: http://www.sigmod.org/sigmod/record/issues/0506/p71-column-winslet.pdf Q. If you magically had enough extra time to do one additional thing at work that you're not doing now, what would it be? A. I think I would work on indexing a little harder. Q. What aspect of indexing? A. Oh, there are many aspects of indexing that I think need improvement. I think we can do a better job in the searching. I think there are exciting things to do in multi-dimensional indexing. I think there are a lot more advanced ways that we can use database indexes for indexing on columns that contain sequences or XML data, and for partial indexing. With partial indexing, we would index only some of the rows, based on some predicate. For example, we might not index the NULL values, or we might index only those salaries greater than $100K. How long has a partial index implementation been in PostgreSQL? I seem to recall it being brought up to speed around six years ago. The underlying code may have been in there for much, much longer... Mike Mascari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inheritance vs. LIKE - need advice
William Bug wrote:
> Hi All,
>
> Sorry to bring up the topic of PostgreSQL inheritance again, but after
> going through the archives and Google results, I still don't have a
> clear sense of whether my plan to implement a schema I'm working on is
> the preferred way to go.
>
> First, I'd like to find out if the way I'm thinking about Inheritance
> vs. the SQL DDL CREATE TABLE modifier LIKE is correct.
>
> The simplest analogy I can think of from OO techniques is PGSQL
> Inheritance corresponds to Class Inheritance, while LIKE is more like
> an inheritable Interface (Java) or Mixin (Ruby). Inheritance maintains
> strict hierarchical relationships propagating the "Class" identity down
> through to all progeny. LIKE on the other hand simply provides a means
> to re-use a set of fields in an unlimited number tables without having
> to redefine those fields for each table you use them in.
As far as the "database table = application object class" analogy goes,
I think you're correct.
> This view is incomplete and far from a perfect fit to the way PGSQL
> Inheritance & LIKE work, but I think it's a helpful way of thinking of
> these 2 related mechanisms, when trying to decide how and when to use
> them in their current form. As has been mentioned many times in posts
> here, as well as in the PGSQL docs, PGSQL Inheritance is only partial.
> Table fields are propagated as well as the group identity, but no other
> RDBMS objects created on the parent ( INDEXES, CONSTRAINTS & SEQUENCES
> primarily) are inherited. As has been endlessly stated in posts here
> and elsewhere, this is a significant short-coming for the PGSQL
> Inheritance mechanism which those of us desirous of using Inheritance
> would love to see fixed (I understand it has been on the TODO list for
> many years, as this mechanism has been in the PGSQL code base for over
> 15 years).
For a variety of reasons, it seems Inheritance is basically on
life-support in PostgreSQL, only there for backwards-compatibility.
First, some of the problems like inheriting keys and constraints are the
exact same problems with table partitioning. When PG has a
mostly-complete table partitioning system, I would imagine those
features will appear in the inheritance facilities. Second, there is
limited demand, because inheritance isn't "needed" (more on that below).
> I don't agree this makes PGSQL Inheritance unusable. There are
> situations where I think it can still be useful, and I describe one
> below. I'd welcome feedback on that opinion, however, as I'd hate to
> have my relative ignorance doom the data schema I'm about to fill with
> a few million rows of data to serious problems later.
It's not unusable, but because it's not all that well defined, and not
under active development and maintenance, you might be wary of using it.
> The following is an example of using both Inheritance and LIKE in the
> context described above.
>
> This is not the best way to model book info (for instance, books are
> only allowed to have 1 author in this schema), but it will help me to
> make my point.
>
> Books, novels and textbooks will be considered equivalent in the
> context of many queries. At the same time, there will be other queries
> where it will be important to consider novels & textbooks as distinct
> entities. The PGSQL Inheritance mechanism easily supports both of
> these situations.
>
> The curation fields listed in the 'curation_info' table are found
> ubiquitously in tables throughout many data schema. However, it is not
> likely there would be a circumstance where you would want to consider
> all tables containing these fields "curatable entities" to be queried
> as a group. That simply makes no sense. In this case, LIKE seems to
> be the best way to propagate these fields, since it doesn't couple all
> tables containing them to the parent 'curation_info' table.
>
> As I see it, there are at least 3 major problems with adopting such a
> schema - despite the obvious efficiencies it offers (most of which have
> been reported elsewhere):
> 1) none of the parent table ('book') CONSTRAINTS or INDEXES are
> propagated to the children. This means if you want the children to
> have the same CONSTRAINTS - as you probably will - you need to build
> them yourself for each child table.
That's because PostgreSQL hasn't really solved the table-partitioning
problem yet, as I mentioned above.
> 2) the primary keys generated across the book, novel & textbook
> tables are completely uncouple and will definitely collide. In other
> words, due to the fact that neither the SEQUENCE behind the
> 'book.id_pk' SERIAL field, not the PK CONSTRAINTS & INDEX that comes
> with that field will automatically propagate to the child tables. That
> is why the SQL DDL given above has an 'id_pk' SERIAL field in all 3
> tables. There may be some conditions where you want those PKs to be
> independent from one ano
Re: [GENERAL] timestamp default values
On Sat, Aug 06, 2005 at 10:06:35AM -0400, Tom Lane wrote: > Can anyone else duplicate the problem? I couldn't duplicate the problem in 8.0.2 or in any other version from 7.2.8 through HEAD (latest CVS for all). Brendan, if you execute "\set VERBOSITY verbose" in psql and then generate the errors, what's the complete error message? Tom (or anybody else), could the errors could be due to the "if (VARSIZE(str) - VARHDRSZ > MAXDATELEN)" checks in text_date() and text_timestamp()? Could an encoding affect that? That's why I suggested increasing the verbosity: so we could see where the error is being raised. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] tables in public
OK.thanks...I suppose I don't need it. - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Saturday, August 06, 2005 9:55 PM Subject: RE: [GENERAL] tables in public Could you, please, give me short description of functionalities of those modules? tsearch2 is full text indexing. postgis is geographical functionality. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tables in public
> Could you, please, give me short description of > functionalities of those modules? tsearch2 is full text indexing. postgis is geographical functionality. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tables in public
Could you, please, give me short description of functionalities of those modules? I don't know whether I need it, because I don't know what is the purpose of those tables... - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Saturday, August 06, 2005 9:28 PM Subject: RE: [GENERAL] tables in public Hello. Postgres automatically included some tables in my public domain: pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, I beleive these are from tsearch2. spatial_ref_sys. And this is postgis. I suppose it's because I switched on all additional options during initdb... Yes (this is win32 MSI installer, right?) My question regarding those tables is wheter I can delete them safely from public ? Yes, unless you plan to use any of the functionality of those modules. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tables in public
> Hello. > > Postgres automatically included some tables in my public domain: > pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, I beleive these are from tsearch2. > spatial_ref_sys. And this is postgis. > I suppose it's because I switched on all additional options > during initdb... Yes (this is win32 MSI installer, right?) > My question regarding those tables is wheter I can delete > them safely from public ? Yes, unless you plan to use any of the functionality of those modules. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Does preparing statements other than selects help performance?
David Goodenough wrote: > On Friday 05 August 2005 11:57, Martijn van Oosterhout wrote: > >>On Fri, Aug 05, 2005 at 10:08:42AM +0100, David Goodenough wrote: >> >>>I was looking at an application recently which was written in Java and >>>used Postgresql as it DB. In it extensive use had been made of >>>PreparedStatements both for SELECTs and for INSERT, UPDATE and DELETE >>>statements. Some of the routines had multiple UPDATEs doing much the >>>same thing but with slightly different parameters. In the comments it >>>was stated that it was better to prepare lots of statements in advance >>>rather than build one on the spot (and then prepare it, it needed the >>>substitution) because of the optimiser. >> >>Which version of PostgreSQL was this built for? Until recently there >>was no support for server side prepared statements so it mattered not >>one wit whether you had one or a thousand prepared queries, it was all >>done by the client anyway. > > I am not sure it was originally build for PostgreSQL, but it all client side > anyway, or that its inside Tomcat and thus from PG's point of view > client side. I presume by server side you mean triggers and functions or > am I misunderstanding you? > He's saying that until recently, PostgreSQL did not support prepared queries. That feature was introduced in version 7.3 (I guess that's not very recent, but it can take a while for client code to start using the new features). So, it's possible that the "prepared queries" you're using are actually just sending a new query each time. For instance, if you connect to a 7.2 database and start doing prepared queries, surely those are done on the client side, because the 7.2 server doesn't support prepared queries. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DNS vs /etc/hosts
On Sat, Aug 06, 2005 at 12:38:50AM -0400, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > Your are correct in that 8.0 is doing a request first. I am running > > Red Hat version 8.0. The difference in the way 7.2 and 8.0 resolve the > > host option has to be because of the change from gethostbyname to > > getaddrinfo. Is there some way I can force my machine to do an A search > > before a search? > > On a recent RH system, "man 5 resolver" suggests that putting "options > inet6" into /etc/resolv.conf is what makes this happen ... if there is > such an entry on your system, try removing it. RH 8.0 is a good ways > back though, so read the local version of that man page before doing > anything with that config file. Hmmm...I have unprivileged access to a RH 7.3 box and I see the "inet6" option in its resolver(5) manual page, but /etc/resolv.conf doesn't have that option. Yet a test program that calls getaddrinfo() with hints.ai_family = AF_UNSPEC nevertheless tries queries first (I can't run a sniffer on that box, so I tweaked the test program's _res structure to send DNS queries to a server that I can sniff). The resolver algorithm for an unqualified hostname is: 1. query for hostname.domain (for each domain in the search list). 2. query for hostname (i.e., as a top-level domain). 3. A query for hostname.domain. 4. A query for hostname. Lowell's sniffer output shows this algorithm in action. The (1) query returns zero answers, so we proceed to the (2) query. Here we see a retry due to a timeout and eventually the DNS server responds with SERVFAIL (see later comments on this). Then we proceed to (3) and finally get an answer. Thomas Pundt suggested running "lsmod | grep ipv6" and disabling the ipv6 module if it's not needed. On the RH 7.3 box I have access to, lsmod shows nothing like "ipv6", "ip6", "inet6", etc. So, /etc/resolv.conf doesn't have an "inet6" option and the kernel doesn't appear to have an IPv6 module, and yet getaddrinfo() still makes queries. Does anybody know if this behavior can be disabled on Linux if the box doesn't use IPv6? The (2) and (4) queries above (the queries for the hostname as a top-level domain) are also a nuisance. On FreeBSD those can be disabled with the "no_tld_query" option in /etc/resolv.conf, but a glance through Linux's resolver(5) manual page doesn't show any such option. Can these queries be disabled on Linux? (This is becoming a Linux configuration thread, so these questions might need to be asked elsewhere.) > I concur with Michael's previous suggestion that the best answer > is to fix the clearly-broken DNS environment you're dealing with. > It is no longer acceptable for anyone to be running nameservers > that have not heard of IPv6 --- unless it's for a network that > only contains clients that have not heard of IPv6, which yours > evidently is not. Have a word with your local network admin. Something Wrong does appear to be happening with this site's DNS. The top-level domain queries should fail fairly quickly with NXDOMAIN after the query goes to a root DNS server that responds with "sorry, ain't no such name," yet the DNS server takes several seconds to respond at all, and when it does it responds with SERVFAIL. That's why I was wondering about connectivity problems to the roots. In summary, several things would be desirable: 1. Disable queries if the box doesn't use IPv6. 2. Disable top-level domain queries in the resolver search algorithm when looking up an unqualified hostname. 3. Fix the DNS servers so that if top-level domain queries for hostnames are made, responses are made quickly instead of taking so long and failing with SERVFAIL. Lowell, you'll probably have to look elsewhere for solutions to these problems, as they're not PostgreSQL-specific. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] timestamp default values
Brendan Jurd <[EMAIL PROTECTED]> writes: > On 8/6/05, Tom Lane <[EMAIL PROTECTED]> wrote: >> Brendan Jurd <[EMAIL PROTECTED]> writes: >>> timeofday() returns text, and moreover it returns in a bizarre format >>> which cannot be converted directly into any useful temporal types, at >>> least not in 8.0.2: >> >> Hm? Works fine for me. What datestyle setting do you have exactly? > => show datestyle; > DateStyle > --- > ISO, DMY Well, it works just fine here. regression=# set datestyle = iso,dmy; SET regression=# select timeofday(); timeofday - Sat Aug 06 10:00:45.791921 2005 EDT (1 row) regression=# select timeofday()::timestamp; timeofday 2005-08-06 10:00:47.920636 (1 row) I'm testing 8.0 branch tip (or nearly so), not 8.0.2, but I don't see any related bug fixes in the CVS logs. And this is something that's always worked in the past --- else we'd have been more motivated to change timeofday()'s behavior. Is it possible you have a broken build? Any nondefault configure options? Can anyone else duplicate the problem? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Optimizing large data loads
Richard Huxton said: > You don't say what the limitations of Hibernate are. Usually you might > look to: > 1. Use COPY not INSERTs Not an option, unfortunately. > 2. If not, block INSERTS into BEGIN/COMMIT transactions of say 100-1000 We're using 50/commit...we can easily up this I suppose. > 3. Turn fsync off Done. > 4. DROP/RESTORE constraints/triggers/indexes while you load your data Hmmm...will have to think about this a bit...not a bad idea but not sure how we can make it work in our situation. > 5. Increase sort_mem/work_mem in your postgresql.conf when recreating > indexes etc. > 6. Use multiple processes to make sure the I/O is maxed out. 5. falls in line with 4. 6. is definitely doable. Thanks for the suggestions! John ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Case sensitivity
Hi all Is there an LC_COLLATE setting, or any other method, which allows all data in a database to be treated in a case-insensitive manner? I have two scenarios in mind. There are workarounds for both of them, but it would be nice if they were not necessary. 1. In a UNIQUE column, I would like a value of 'a' to be rejected if there is already a value of 'A'. Workaround - create a unique index on LOWER(col_name). 2. I would like WHERE col_name = 'x' and WHERE col_name LIKE 'x%' to find 'X' and 'X1'. Workaround - WHERE LOWER(col_name) = 'x' and WHERE col_name ILIKE 'x%'. TIA Frank Millman ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgresql Hosting
On Aug 4, 2005, at 10:28 PM, Richard Sydney-Smith wrote: I have asked my internet host to include postgresql as part of their service but it seems that there are issues in getting it to work with "cpanel" which is their support service for their clients. Is their a reason why Postgresql is harder to host than mysql? Is their any docs I can point the server admin to that would help him? If not what service providers are people recommending? I'm a satisfied customer of http://www.a2hosting.com. They use cpanel, so I'm not sure what the problem is. And the only thing you use cpanel for is to create users and databases. Otherwise you use phpPgAdmin (or psql if your account has shell access). John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] timestamp default values
On 8/6/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Brendan Jurd <[EMAIL PROTECTED]> writes: > > timeofday() returns text, and moreover it returns in a bizarre format > > which cannot be converted directly into any useful temporal types, at > > least not in 8.0.2: > > Hm? Works fine for me. What datestyle setting do you have exactly? > => show datestyle; DateStyle --- ISO, DMY ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] tables in public
Hello. Postgres automatically included some tables in my public domain: pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, spatial_ref_sys. I suppose it's because I switched on all additional options during initdb... My question regarding those tables is wheter I can delete them safely from public ? Zlatko
