Re: [GENERAL] [PERFORM] Parrallel query execution for UNION ALL Queries
On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote: > EnterpriseDB, a commercially enhanced version of PostgreSQL can do > query parallelization, but it comes at a cost, and that cost is making > sure you have enough spindles / I/O bandwidth that you won't be > actually slowing your system down. I think you're thinking ExtendDB. :) -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpsJ6UTJZf9L.pgp Description: PGP signature
Re: [GENERAL] Feature request: Per database search_path
On Wed, Jul 18, 2007 at 11:02:51PM +0100, Richard Huxton wrote: > Francisco Reyes wrote: > >As far as I know, currently one can set the search path globally, or on > >a per role bases. > > > >I was wondering if it could be possible to have a per database search_path. > >I believe this would be not only convenient, but will add flexibility. > > > ALTER DATABASE leia SET search_path = public,lookups; > > Seems to work for me on 8.2 - you'll need to disconnect and reconnect to > see it take place though. BTW, in case it's not obvious, that works with pretty much anything that session-setable. Same with ALTER ROLE ... SET. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpgFIvnPhGZA.pgp Description: PGP signature
Re: [GENERAL] postgresql 8.0 advantages
On Sat, Feb 26, 2005 at 01:27:55AM -0800, Jeff Davis wrote: > On Fri, 2005-02-25 at 09:20 -0800, Si Chen wrote: > > Thanks! > > > > Is there any documentation on how to upgrade to 8.0? > > > > Is it possible to upgrade from 7.4 to 8.0 while keeping a production > > database running "in place"? Or should I install 8.0 in another > > directory/machine and then restore the database into it? Are there any > > incompatibilities/modifications to databases from the earlier veresion > > required? > > > > You might want to check out Slony-I <http://www.slony.info> for the > purpose of upgrading. Slony is actually a replication engine, but it can > be used to upgrade with little or no downtime. Otherwise, just > dump/upgrade/restore like normal. Has anyone tried moving a database from one location to another on the HD? I'd like to use slony to minimize downtime, but I'd also like my data to end up in the same place it is right now when I'm done. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fast major-version upgrade (was: [GENERAL] postgresql 8.0 advantages)
On Sun, Feb 27, 2005 at 09:27:41PM +0100, Tino Wildenhain wrote: > Hi, > > Am Samstag, den 26.02.2005, 15:35 -0600 schrieb Jim C. Nasby: > > On Sat, Feb 26, 2005 at 01:27:55AM -0800, Jeff Davis wrote: > > > On Fri, 2005-02-25 at 09:20 -0800, Si Chen wrote: > > > > Thanks! > > > > > > > > Is there any documentation on how to upgrade to 8.0? > > > > > > > > Is it possible to upgrade from 7.4 to 8.0 while keeping a production > > > > database running "in place"? Or should I install 8.0 in another > > > > directory/machine and then restore the database into it? Are there any > > > > incompatibilities/modifications to databases from the earlier veresion > > > > required? > > > > > > > > > > You might want to check out Slony-I <http://www.slony.info> for the > > > purpose of upgrading. Slony is actually a replication engine, but it can > > > be used to upgrade with little or no downtime. Otherwise, just > > > dump/upgrade/restore like normal. > > > > Has anyone tried moving a database from one location to another on the > > HD? I'd like to use slony to minimize downtime, but I'd also like my > > data to end up in the same place it is right now when I'm done. > > I used a straight copy of the filesystem with running database > (over the net in my case) and immediately after that, > stop the db and rsync for the last changes. This took only > 10 minutes (compared to 1.5h for the full filesystem copy) > and I could start up the db in new location. > > this could work for you too. I hadn't thought about using rsync; that's a great idea! Is there somewhere this could be documented? In an FAQ maybe? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Fast major-version upgrade (was: [GENERAL] postgresql 8.0 advantages)
On Mon, Feb 28, 2005 at 09:27:46PM +0100, Martijn van Oosterhout wrote: > On Mon, Feb 28, 2005 at 01:36:59PM -0600, Jim C. Nasby wrote: > > > I used a straight copy of the filesystem with running database > > > (over the net in my case) and immediately after that, > > > stop the db and rsync for the last changes. This took only > > > 10 minutes (compared to 1.5h for the full filesystem copy) > > > and I could start up the db in new location. > > > > > > this could work for you too. > > > > I hadn't thought about using rsync; that's a great idea! > > > > Is there somewhere this could be documented? In an FAQ maybe? > > It works only in the special case where the PostgreSQL version number > is the same and you're running on the same platform. How often are you > transferring databases like that. Even transferring from i386 to amd64 > wouldn't work like this AFAIUI. Absolutely true, although in the case of database version PostgreSQL will check that itself. But in the context this was originally brought up in (using Sloney to upgrade a machine from 7.4.x to 8.x), it would work great, and rsync would make a huge difference in downtime. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Casting from a domain
I'm trying to create a seconds domain that is an interval 'mashed to
seconds' as I once saw Tom describe it.
decibel=# create domain rrs.seconds as double precision;
CREATE DOMAIN
decibel=# create cast (double precision as rrs.seconds) WITHOUT FUNCTION AS
IMPLICIT;
CREATE CAST
decibel=# create function rrs.interval_to_seconds(interval) returns seconds as
'SELECT extract( EPOCH FROM $1 );' LANGUAGE SQL;
CREATE FUNCTION
decibel=# create cast (interval as rrs.seconds) WITH FUNCTION
rrs.interval_to_seconds(interval) AS IMPLICIT;
CREATE CAST
decibel=# select cast('1 month'::interval AS seconds);
ERROR: cannot cast type interval to seconds
\dC shows that the cast is there, and rrs.interval_to_seconds works as
expected, and according to \df does return seconds.
Version is 7.4.5.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Casting from a domain
On Fri, Mar 04, 2005 at 03:40:17PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > decibel=# create cast (interval as rrs.seconds) WITH FUNCTION
> > rrs.interval_to_seconds(interval) AS IMPLICIT;
> > CREATE CAST
> > decibel=# select cast('1 month'::interval AS seconds);
> > ERROR: cannot cast type interval to seconds
>
> Given the current coercion rules, we should probably disallow attempts
> to define casts that involve domains. Casts are on base types. The
> down-cast from a domain to its base type is hardwired, and the up-cast
> from base type to domain is too (with invocation of any constraints
> that may apply). Adding random user-defined casts to this would
> probably just create confusion and ambiguity. In particular, this
> was already meaningless:
>
> decibel=# create domain rrs.seconds as double precision;
> CREATE DOMAIN
> decibel=# create cast (double precision as rrs.seconds) WITHOUT FUNCTION AS
> IMPLICIT;
> CREATE CAST
>
> since the presence of the cast might be thought to justify coercing
> floats to "seconds" without invoking domain constraints.
Yeah, I figured it was probably an issue with it being a domain. I was
looking for a way to do this without the extra work involved in defining
a full-blown type. I guess the good news is I should be able to re-use
all the double-precision functions and what-not for 'seconds'.
BTW, is there a reason 'double' isn't an alias for 'double precision'?
I'm pretty sure every other database I've used (other than oracle of
course) supports 'double'. " precision" is just too much extra typing
after all... :P
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PostgreSQL still for Linux only?
Personally, I find the anti-windows bias that has been shown in this thread by some developers to be disappointing. Maybe it sucks to program in, and maybe it's not as stable as unix (though I don't put much water in that argument anymore), but the fact is there's still a LOT of places that are windows shops and a LOT of people who use windows more heavily than *nix. More important, the egotism of "If you want to use PostgreSQL you better run it on what we tell you to run it on" is certain to turn people off of PostgreSQL. It will certainly turn off windows developers who might have been interested in working to improve PostgreSQL now that it runs on windows. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, Mar 09, 2005 at 11:39:53AM -0600, Doug Hall wrote: > On Wed, 9 Mar 2005 11:02:10 -0600, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > >... but the fact is there's still a LOT of places > > that are windows shops and a LOT of people who use windows more heavily > > than *nix. More important, the egotism of "If you want to use PostgreSQL > > you better run it on what we tell you to run it on" is certain to turn > > people off of PostgreSQL. > > Perhaps someone on the list who knows and uses the different operating > systems could set up a lab, to compare PostgreSQL between them. > Perhaps the latest Windows Server, a popular distribution of Linux, > and Mac OS X? > > Has this already been done, with regard to performance? There is a perftest project on either pgfoundry or gborg that has been doing performance testing. I think it's all being done on linux right now, but it would certainly be interesting to compare linux, freebsd, and windows. Unfortunately, there's no way to do an apples-to-apples (pun intended) comparison with OS X, since not all of the OS's will run on the same hardware. Linux will run on Power, though, as will OpenBSD. I think FreeBSD's support is still pretty bare, but I'm not certain. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, Mar 09, 2005 at 09:47:17AM -0800, Ben wrote: > Ho ho, flame on! :) > > My completely annecodal experience with devs which prefer windows over > posix is that the former prods things until they seem to work and accepts > unexplained behavior far more readily than the latter. Do I *really* want > that kind of mentality in my database devs? Of course not, and I don't think there's any risk of this happening. Are you aware that every patch submitted for inclusion goes through a code review? It's very insightful to see the discussion and mentality on the -hackers list; data integrity is always the absolute number 1 priority. Anyone who wants to code for PostgreSQL who doesn't share that priority won't last long at all. > Anyway, I think you have the focus wrong. It's not: "run our software on > what we tell you to" it's more: "we believe this platform is better > than others, so we'll write our free software for that. But if you want to > port it over to the platform of your choice, have fun doing that." With the attitude of "Windows can not be made to reliably run a database", how many developers do you think will be attracted? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL still for Linux only?
On Wed, Mar 09, 2005 at 05:51:43PM -0800, Chris Travers wrote: > Jim C. Nasby wrote: > Ok--- I will admit to a anti-Windows bias. But at least my bias is > informed. In addition to my former employment at Microsoft, I have > studies both types of OS's in detail. Here are some specific comments I > would make: > > 1) I do not expect PostgreSQL to *ever* perform as well on Windows as > it does on Linux. This is primarily due to the fundamentally different > emphasis in kernel architecture between UNIX-style and VMS-style > operating systems. Windows server applications which are process-based > are always likely to underperform. Windows applications ported to Linux > are similarly likely to underperform. This is akin to saying that an application written to use MySQL will never perform well on PostgreSQL. It depends on *how* the code is written. If your SQL is tuned to one database, it will likely have performance issues on other databases. Likewise, a process-based server will perform poorly on Windows, while a threaded server will not. This is an implimentation choice. There's no reason why PostgreSQL on windows *has* to be process based (though of course there would be serious technical issues with changing it). Of course, by simply hand waving and saying "it can never be better", it never will be better. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL still for Linux only?
On Thu, Mar 10, 2005 at 11:22:59AM -0600, Scott Marlowe wrote: > > 2. This response is alarming: > > Tom Lane wrote in digest V1.5092: > > >We are supporting Windows as a Postgres platform for the benefit of > > developers who want to > > >do testing on their laptops (and for reasons best known to themselves > > feel a need to run >Windows on their laptops). > > This is the second problem. Windows simply has problems that cause data > relibility problems that may or may not be surmountable in the future. Do you have any references to these problems? I've seen several people mention things like this in passing, but I have yet to see any specifics. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2005 ==
On Fri, Apr 01, 2005 at 09:00:15PM +0100, Peter Childs wrote: > David Fetter wrote: > > >== PostgreSQL Weekly News - April 01 2005 == > > > >As of today, the license of PostgreSQL, including all its libraries, > >is changing from the unfashionable BSD license to the more popular > >GPL. > > > > > >Fine I can't see the difference, but have you not got to clear this > with every developer past and present to get it through. Nope. You can do anything you want with BSD licensed software, so long as you maintain the copyright info. GPL restricts what you can do. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [HACKERS] plPHP in core?
On Sat, Apr 02, 2005 at 07:29:02AM -0800, Joshua D. Drake wrote: > This argument doesn't hold too much weight. Namely because there are only > 3-5 really popular languages out there. They are marketing languages. > The are languages you include because your database doesn't "sound" > complete with out them. Regardless if you can download them separately. > People are lazy. They don't want to download them separately. > > I see those as: > > plPgsql (for Oracle people) > plPerl > plPHP What databases support perl or php stored procs/functions? Or python for that matter? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] psql and mysql
On Sat, Apr 02, 2005 at 06:23:56PM -0500, [EMAIL PROTECTED] wrote: > Clients aside, and as Christopher wrote, there are lots of differences > between the two systems. PostgreSQL supports higher-end Enterprise-level > RDBMS features. MySQL tends to be quicker. For example, and as just one MySQL is generally only quicker if you don't care about your data (MyISAM tables) and if you aren't hitting it with multiple clients. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [HACKERS] plPHP in core?
On Sun, Apr 03, 2005 at 08:41:15PM -0700, Joshua D. Drake wrote: > Jim C. Nasby wrote: > > >On Sat, Apr 02, 2005 at 07:29:02AM -0800, Joshua D. Drake wrote: > > > > > >>This argument doesn't hold too much weight. Namely because there are only > >>3-5 really popular languages out there. They are marketing languages. > >>The are languages you include because your database doesn't "sound" > >>complete with out them. Regardless if you can download them separately. > >>People are lazy. They don't want to download them separately. > >> > >>I see those as: > >> > >>plPgsql (for Oracle people) > >>plPerl > >>plPHP > >> > >> > > > >What databases support perl or php stored procs/functions? Or python for > >that matter? > > > > > None on the server side (except PostgreSQL) which makes the > argument all that more powerful :) So what you're saying is that no database "sounds complete" because no database includes PHP as a procedural language. Sorry, but I don't buy it. >From a database comparison/marketing standpoint, the only languages that matter are C/C++, plpgsql and pljava, because these are the only languages that other databases support. Honestly, I think if we're going to spend time worrying about languages as features then we should be doing more to advertise the fact that perl/PHP/python/ruby/etc programmers can program in the database in their native language. This is something that makes PostgreSQL unique and should provide additional incentive for people to use PostgreSQL. I don't think it matters much at all if those 'bonus languages' are included in core or not, at least not to end-users. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/PERL: raise notice, exception ?
On Tue, Apr 05, 2005 at 07:37:03AM -0700, David Fetter wrote: > On Tue, Apr 05, 2005 at 04:28:10PM +0200, Philippe Lang wrote: > > Hi, > > > > Is there in PL/PERL, under PG 8.01, an equivalent for the "raise > > notice, exception" commands of PL/PGSQL? > > Use the elog() function. :) See also http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] psql performance
On Thu, Apr 14, 2005 at 11:05:37PM -0400, Joseph Shraibman wrote: > Incidentally when I did that I only got back one row. What's up with that? That's PostgreSQL acting according to ANSI SQL. If you want multiple rows, you need UNION ALL. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] UNION messing up sorting WAS: psql performance
On Fri, Apr 15, 2005 at 12:23:14AM -0400, Joseph Shraibman wrote: > > > Alvaro Herrera wrote: > > >>Incidentally when I did that I only got back one row. What's up with > >>that? > > > > > >Try with "union all" instead of plain union. > > > Talk about serendipity. The problem I've been struggling with for the > last few hours has been why my query wasn't producing sorted output even > though I put in an ORDER BY and the EXPLAIN shows that it is ordering. > The DISTINCT implied by the UNION must have been messing up the sorting. > > The docs say > (http://www.postgresql.org/docs/7.4/interactive/sql-select.html#SQL-UNION): > > select_statement is any SELECT statement without an ORDER BY, LIMIT, or > FOR UPDATE clause. (ORDER BY and LIMIT can be attached to a > subexpression if it is enclosed in parentheses. > > > > ... but I *did* put my SELECTs in parentheses. This is either a bug in > pg or a serious ommision from the docs. It's not a bug, though it could possibly made clearer in the docs. If you want your final output in a specific order, you have to put you ORDER BY in the very outermost level of the query. PostgreSQL is pretty liberal about where you can put ORDER BY, but when it comes to output ordering only the order of the final query step matters. Why allow ordering elsewhere? Consider this more-performant replacement for SELECT max(blah): SELECT max FROM (SELECT blah FROM table ORDER BY blah DESC LIMIT 1) a ; Maybe not a great example since you'll only get one row back, but the point is that the ORDER BY in the subquery doesn't mean a thing when it comes to output order. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Finding cardinality of an index
http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html On Thu, Apr 21, 2005 at 08:11:09AM -0700, Bill Chandler wrote: > All, > > Is there a way to determine cardinality (size) of an > index? In general how to you query the 'attributes' > (for lack of a better word) of an index. > > thanks, > > Bill > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] CURRENT_TIMESTAMP and actual time
Take a look at the tod() function at the very end of http://svn.rrs.decibel.org/viewcvs.cgi/trunk/rrs_functions.sql?rev=61&view=markup On Wed, Apr 20, 2005 at 04:17:05PM -0500, Christopher J. Bottaro wrote: > Hi, > I understand that CURRENT_TIMESTAMP marks the beginning of the current > transaction. I want it to be the _actual_ time. How do I do this? > timeofday() returns a string, how do I convert that into a TIMESTAMP? > > Is it possible to create a column with DEFAULT value evaluated to the actual > current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the > current transaction). > > What I do now to get it to work is do a COMMIT right before the insert, that > way CURRENT_TIMESTAMP is (pretty close to) the actual time. ...but that is > so crappy and doesn't work if I actually need to use transactional features > (i.e. rollback). > > Thanks for the help, > -- C > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Optimising Union Query.
On Sat, Apr 23, 2005 at 10:39:14PM +, Patrick TJ McPhee wrote: > In article <[EMAIL PROTECTED]>, > Rob Kirkbride <[EMAIL PROTECTED]> wrote: > > % I've done a explain analyze and as I expected the database has to check > % every row in each of the three tables below but I'm wondering if I can > > This is because you're returning a row for every row in the three > tables. > > % select l.name,l.id from pa i,locations l where i.location=l.id union > % select l.name,l.id from andu i,locations l where i.location=l.id union > % select l.name,l.id from idu i,locations l where i.location=l.id; > > You might get some improvement from > > select name,id from locations > where id in (select distinct location from pa union >select distinct location from andu union >select distinct location from idu); Note that SELECT DISTINCT is redundant with a plain UNION. By definition, UNION does a DISTINCT. In fact, this is going to hurt you; you'll end up doing 4 distinct operations (one for each SELECT DISTINCT and one for the overall UNION). Unless some of those tables have a lot of duplicated location values, you should either use UNION ALLs or drop the DISTINCTs. Note that going with DISTINCTs is different than what your original query does. You should also consider this: SELECT name, id FROM locations l WHERE EXISTS (SELECT * FROM pa p WHERE p.location=l.id) OR EXISTS (SELECT * FROM andu a WHERE a.location=l.id) OR EXISTS (SELECT * FROM idu i WHERE i.location=l.id) This query would definately be helped by having indexes on (pa|andu|idu).location. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Very interesting article about the future of databases
For those who didn't see it on slashdot: http://www.acmqueue.org/modules.php?name=Content&pa=showpage&pid=293 What's interesting is that PostgreSQL is already working on some of these things. Of note, there's a patch to allow sequential scans to 'piggyback' on top of other sequential scans. See the quote "For petabyte-scale databases, the only solution may be to run continuous data scans, with queries piggybacked on top of the scans." on page 4. There's also been discussion about how to more intelligently cost UDF's, something also mentioned on page 4. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgres vs Firebird?
On Wed, May 04, 2005 at 12:08:47PM -0700, Joshua D. Drake wrote: > Firebird is a nice database but I don't think it can scale as well as > PostgreSQL. IIRC they just added support for SMP. Also, although their > community is very active I do not believe it is as large nor does it > have the commercial backing like PostgreSQL. Support it in what way? Do they allow for parallel query execution? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Slony v. DBMirror
On Fri, May 06, 2005 at 12:09:14PM -0400, Chris Browne wrote: > What I need, for that, is a way of grabbing all the index definitions > for the table. One way to do that would be to run "pg_dump -s -t a", > though I'd rather have a method that uses the connection I already > have to the database. > > This may involve some more-or-less involved queries on pg_index, > unless the pg_indexes view is available on all versions of PG of > interest... You'll want to take a look at newsysviews on pgFoundry. Even if you decide not to depend on it (though hopefully it'll get built into 8.1) the index views should get you pretty close to what you need for generating index definition statements. If there's anything missing let me know. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Shorthand for foreign key indices
On Mon, May 09, 2005 at 02:05:14AM +1000, Brendan Jurd wrote: > CREATE TABLE foo ( > foo int NOT NULL REFERENCES bar INDEX > ); > > ... would be marvellous I agree that it would be handy. Another possibility is throwing a NOTICE or even WARNING if you create a foreign key that isn't covered by an index. > My apologies if this has been answered before, but a search of the > -general mailing list was not fruitful. FYI, often times new ideas are only discussed on -hackers, so you should search there as well. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Shorthand for foreign key indices
On Mon, May 09, 2005 at 09:04:49AM -0400, John D. Burger wrote: > >I know that it was decided a fair few releases ago to stop creating an > >implicit index for each foreign key, > > By the way, I presume foreign key indices are used to check for > referential integrity on insert. Can the query planner also use then > somehow? It can use them the same way it can use any other index. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Function to return number of words in a string?
You can use a combination of regex (http://www.postgresql.org/docs/8.0/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP) and strpos inside a plpgsql function to do this, but using plpython or plperl might be faster. A C function would possibly be faster still. On Mon, May 09, 2005 at 11:21:28AM -0500, Peter Fein wrote: > Hiya- > > I'm looking for a function to return the number of words in a string, > split on whitespace. I'm coming from python, so I may just write it in > that but I wanted to check first. In python, one would write: > > s="some string or other" > len(s.split()) > > Thanks! > > -- > Peter Fein [EMAIL PROTECTED] 773-575-0694 > > Basically, if you're not a utopianist, you're a schmuck. -J. Feldman > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
On Tue, May 10, 2005 at 01:34:57AM +1000, Neil Conway wrote: > Christopher Petrilli wrote: > >This being the case, is there ever ANY reason for someone to use it? > > Well, someone might fix it up at some point in the future. I don't think > there's anything fundamentally wrong with hash indexes, it is just that > the current implementation is a bit lacking. > > >If not, then shouldn't we consider deprecating it and eventually > >removing it. > > I would personally consider the code to be deprecated already. > > I don't think there is much to be gained b removing it: the code is > pretty isolated from the rest of the tree, and (IMHO) not a significant > maintenance burden. That may be true, but it's also a somewhat 'developer-centric' view. ;) Having indexes that people shouldn't be using does add confusion for users, and presents the opportunity for foot-shooting. I don't know what purpose they once served, but if there's no advantage to them they should be officially depricated and eventually removed. Even if there is some kind of advantage (would they possibly speed up hash joins?), if there's no plans to fix them they should still be removed. If someone ever really wanted to do something with, the code would still be in CVS. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
On Tue, May 10, 2005 at 02:38:41AM +1000, Neil Conway wrote: > Jim C. Nasby wrote: > >Having indexes that people shouldn't be using does add confusion for > >users, and presents the opportunity for foot-shooting. > > Emitting a warning/notice on hash-index creation is something I've > suggested in the past -- that would be fine with me. Probably not a bad idea. > >Even if there is some kind of advantage (would they possibly speed up > >hash joins?) > > No, hash joins and hash indexes are unrelated. I know they are now, but does that have to be the case? Like I said, I don't know the history, so I don't know why we even have them to begin with. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
On Tue, May 10, 2005 at 12:10:57AM -0400, Tom Lane wrote: > be responsive to your search.) (This also brings up the thought that > it might be interesting to support hash buckets smaller than a page ... > but I don't know how to make that work in an adaptive fashion.) IIRC, other databases that support hash indexes also allow you to define the bucket size, so it might be a good start to allow for that. DBA's usually have a pretty good idea of what a table will look like in production, so if there's clear documentation on the effect of bucket size a good DBA should be able to make a good decision. What's the challange to making it adaptive, comming up with an algorithm that gives you the optimal bucket size (which I would think there's research on...) or allowing the index to accommodate different bucket sizes existing in the index at once? (Presumably you don't want to re-write the entire index every time it looks like a different bucket size would help.) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
On Tue, May 10, 2005 at 10:14:11AM +1000, Neil Conway wrote: > Jim C. Nasby wrote: > >> No, hash joins and hash indexes are unrelated. > >I know they are now, but does that have to be the case? > > I mean, the algorithms are fundamentally unrelated. They share a bit of > code such as the hash functions themselves, but they are really solving > two different problems (disk based indexing with (hopefully) good > concurrency and WAL logging vs. in-memory joins via hashing with spill > to disk if needed). Well, in a hash-join right now you normally end up feeding at least one side of the join with a seqscan. Wouldn't it speed things up considerably if you could look up hashes in the hash index instead? That way you can eliminate going to the heap for any hashes that match. Of course, if limited tuple visibility info was added to hash indexes (similar to what I think is currently happening to B-tree's), many of the heap scans could be eliminated as well. A similar method could also be used for hash aggregates, assuming they use the same hash. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Trigger that spawns forked process
On Mon, May 09, 2005 at 09:07:40PM -0400, Christopher Murtagh wrote: > On Mon, 2005-05-09 at 17:01 -0400, Douglas McNaught wrote: > > Why not have a client connection LISTENing and doing the > > synchronization, and have the trigger use NOTIFY? > > > > Or, you could have the trigger write to a table, and have another > > client periodically scanning the table for new sync events. > > > > Either one of those would be simpler and more robust than fork()ing > > inside the backend. > > How is writing a daemon simpler than using something that could be done > within Postgres? Forking is something that should be natural to Unix > systems, I shouldn't need to write another application to do this. I > don't see how a daemon would necessarily be more robust either. Well, LISTEN and NOTIFY are built into PostgreSQL (http://www.postgresql.org/docs/8.0/interactive/sql-notify.html). If the processes that you're trying to notify of the changes are connected to the database then this might be the easiest way to do what you're looking for. Setting up some form of replication, such as Slony, also comes to mind. But it's impossible to really make a recommendation without having a better idea of what you're doing. BTW, my understanding is that it's pretty easy to write a daemon in perl, and there are examples of how to do this floating around. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
On Tue, May 10, 2005 at 11:49:50AM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > What's the challange to making it adaptive, comming up with an algorithm > > that gives you the optimal bucket size (which I would think there's > > research on...) or allowing the index to accommodate different bucket > > sizes existing in the index at once? (Presumably you don't want to > > re-write the entire index every time it looks like a different bucket > > size would help.) > > Exactly. That's (a) expensive and (b) really hard to fit into the WAL > paradigm --- I think we could only handle it as a REINDEX. So if it > were adaptive at all I think we'd have to support multiple bucket sizes > existing simultaneously in the index, and I do not see a good way to do > that. I'm not really familiar enough with hash indexes to know if this would work, but if the maximum bucket size was known you could use that to determine a maximum range of buckets to look at. In some cases, that range would include only one bucket, otherwise it would be a set of buckets. If you found a set of buckets, I think you could then just go to the specific one you need. If we assume that the maximum bucket size is one page it becomes more realistic to take an existing large bucket and split it into several smaller ones. This could be done on an update to the index page, or a background process could handle it. In any case, should this go on the TODO list? > Allowing a bucket size to be specified at CREATE INDEX doesn't seem out > of line though. We'd have to think up a scheme for index-AM-specific > index parameters ... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] table synonyms
I don't remember off the top of my head exactly how synonyms worked, but I'm pretty sure PostgreSQL doesn't directly support them. You might be able to emulate them with rules, though. On Mon, May 16, 2005 at 08:35:34AM -0300, Jayme Jeffman Filho wrote: > Hi, > > I would like to know if PostgreSQL has a similar database object to the > Oracle synonym. > > Thanks a lot. > > Jayme Jeffman Filho > GSEE-PUCRS > +55 (51) 9112 3422 > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] numeric precision when raising one numeric to
That's because numerics default to 16 or something similar. If you want more precision just explicitly cast it: decibel=# select power(0.1::numeric(20,20),17); 0.1000 On Fri, May 20, 2005 at 09:30:16AM -0700, Stephan Szabo wrote: > > On Fri, 20 May 2005, Tom Lane wrote: > > > Has anyone bothered to actually look into the code? > > > > regression=# select power(2::numeric,1000); > > > > power > > - > > > > 10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376. > > (1 row) > > > > AFAICT the only thing missing is a pg_operator entry linked to the > > function. > > It appears fairly limited however given that you rapidly run into the > numeric maximum length for exp. > > It also doesn't seem to work terribly well: > > sszabo=# select power(0.1::numeric, 15); >power > > 0.0010 > (1 row) > > sszabo=# select power(0.1::numeric, 16); >power > > 0.0001 > (1 row) > > sszabo=# select power(0.1::numeric, 17); >power > > 0. > (1 row) > > sszabo=# select power(0.1::numeric, 17)*100; > ?column? > > 0.00000000 > (1 row) > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] numeric precision when raising one numeric to another.
Why are we allowing implicit casts from numeric to floating point? Doesn't that violate the principle of not doing any implicit casts that would potentially drop precision? It seems that about half the arguments here are related to getting unexpected or inaccurate results, presumably from the implicit cast; if it was removed at least then people would know their results might be imprecise, where as now there's no indication of that at all. On Wed, May 18, 2005 at 05:42:28PM -0400, Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > It appears from checking the output of exponentiation of one numeric to > > another, the output is actually in floating point. Is this normal and / > > or expected? > > Yes, seeing that the only ^ operator we have is float8. > > regression=# \do ^ > List of operators >Schema | Name | Left arg type | Right arg type | Result type| > Description > +--+--+--+--+-- > pg_catalog | ^| double precision | double precision | double precision | > exponentiation (x^y) > (1 row) > > regards, tom lane > > ---(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 > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] materialized view
The BizGres project is also looking at adding materialized views. On Thu, May 19, 2005 at 11:52:01PM -0700, Ben wrote: > A quick google search for "materialized views on postgres" brings up > this very helpful page that I've used before: > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html > > On May 19, 2005, at 11:40 PM, Himanshu Baweja wrote: > > >does postgres support materialized view.. if yes can somebody post > >any links which might be useful in learning how to use them... > >thx > >Himanshu > >__ > >Do You Yahoo!? > >Tired of spam? Yahoo! Mail has the best spam protection around > >http://mail.yahoo.com > > > ---(end of broadcast)----------- > TIP 7: don't forget to increase your free space map settings > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] numeric precision when raising one numeric to another.
On Fri, May 20, 2005 at 01:03:08PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Why are we allowing implicit casts from numeric to floating point? > > Because the SQL spec requires it. > > 2) If the data type of either operand of a dyadic arithmetic op- > erator is approximate numeric, then the data type of the re- > sult is approximate numeric. > > It doesn't say to throw an error for mixed-type arithmetic. > > Now it also says > > 1) If the data type of both operands of a dyadic arithmetic opera- > tor is exact numeric, then the data type of the result is exact > numeric, ... But isn't NUMERIC exact numeric and not approximate? > which you could take as requiring us to provide numeric equivalents of > every floating-point operator, but I don't find that argument very > convincing for operations that are inherently not going to give exact > results. The spec demands exact results from addition, subtraction, > and multiplication, but as soon as you get to division they punt; let > alone transcendental functions. ISTM what's more important than be exact is respecting precision. If I'm remembering this correctly from high school, multiplying two numbers each having 10 significant digits means you then have 20 significant digits, so we should at least respect that. Which means numeric(500)^numeric(500) should give an exact numeric(1000), which I don't think is a given when casting to a double. I'm not sure how this changes if you're using a fractional exponent. But it seems like a pretty serious issue if you're doing financial calculations and those are sometimes done in floating point under the covers. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] table synonyms
I suspect you're mixing users and schemas, but it's been too long since I've used Oracle, so I'm not sure. Can you provide a more concrete example? FWIW, I suspect this is a non-issue with postgresql, since the only hierarchy of objects is schemas, and you can handle that with search_path. On Fri, May 20, 2005 at 03:01:34PM -0300, [EMAIL PROTECTED] wrote: > I have searched for the word synonym through the whole > PostrgeSQL 7.42 pdf documentation and all the ocurrences > are from functions redefinitions, and another PostreSQL > user has answered me that it does not support tables > synonyms, so I am with a big problem : How can I write > queries to suport different tables owners ? > > Let me explain : I run the same application at different > database servers, from different enterprises, and their > DBA's can choose the name of the owner of the tables I > should query to. How to manage this ? Can I use a parameter > to define the table owner ? A macro ? Is there any > solution? > > Thanks a lot. > > Jayme. > > - Original Message - > From: "Jim C. Nasby" > To: Jayme Jeffman Filho > Sent: 20-May-2005 13:31:49 -0300 > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] table synonyms > I don't remember off the top of my head exactly how synonyms worked, but > I'm pretty sure PostgreSQL doesn't directly support them. You might be > able to emulate them with rules, though. > > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] table synonyms
On Sun, May 22, 2005 at 03:28:22PM -0300, [EMAIL PROTECTED] wrote: > Of course! Maybe I am mixing users and schemas, because in Oracle they > are the same, the schema has the name of the user which is the owner of > the database tables and objects. > > So the problem can be described as follows : > > 1. Let be Ent01 an enterprise, and Ent02 a different one. > 2. At Ent01 the database schema has the name "SCH01" and at Ent02 the > database schema has the name "FOO" . > 3. The same application must run in booth enterprises, and all the > database queries and table names are the same, just the schemas has > different names. > 4. The application can run using a database user other then the tables > owner, so the queries must be written using the coplete format > (schema.table.column) . > > Using Oracle I can set up synonyms for the tables and by pass the format > above, or I can use a macro substitution (ODAC components) to use the > correct schema name, setting it at runtime. In PostgreSQL, you can get roughly the same behavior using search_path. http://lnk.nu/postgresql.org/2r2.html > By the way what "FWIW" stands for ? For What It's Worth. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] database auto-commit
IIRC there is no autocommit in postgresql itself, so the autocommit is probably from whatever connection library/method you're using. Note that PSQL does have an autocommit option, but I don't see how that would affect this case. On Tue, May 31, 2005 at 03:12:26PM +0200, FERREIRA, William (COFRAMI) wrote: > > hi > > i writing a j2ee application working with postgresql and i meet a problem > with autocommit > from my j2ee application i call a perl function and i get an error : > in french : Les Large Objects ne devraient pas ?tre utilis?s en mode > auto-commit. > in english : Large Objects should not be used in mode auto-committed. > > and i didn't find how to disable autocommit. > > Thanks in advance > > William > > This mail has originated outside your organization, either from an external > partner or the Global Internet. Keep this in mind if you answer this message. > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] mirroring oracle database in pgsql
On Mon, Jun 06, 2005 at 12:52:13PM -0700, Edward Peschko wrote: > In other words, I'm looking to make a postgresql -> Oracle mirroring > tool, and syncing the databases on a nightly basis, and I was > wondering if anybody had experience with this sort of thing. You should take a look at contrib/dblink, which AFAIK allows connections from PostgreSQL to Oracle. It should make it easy to sync data between the two. > As I see it, if we pull this off we could save quite a bit in > licensing costs - we'd still have oracle around, but it > would only be a datastore for talking to other oracle databases, > and run by batch, not accessed by end users. Unless you get Oracle backups from customers or something you should probably be able to completely leave Oracle. > However: > > a) I'm not sure how well stored procs, views, triggers and >indexes transfer over from oracle to postgresql. PostgreSQL goes to great lengths to comply with ANSI SQL, probably moreso than any other database. Generally, most SQL written for Oracle that isn't using features not yet supported by PostgreSQL (such as WITH or the OLAP extensions) should play just fine. PL/PGSQL is also fairly similar to PLSQL. I think there's also some Oracle -> PostgreSQL migration tools out there. > b) I'm not sure how scalable postgresql is, and how well >it handles multiprocessor support (we'd be using a >six-processor box. It's not as scaleable as Oracle, but then again pretty much nothing else is either. It really depends on what you're doing. PostgreSQL uses a process for each connection, so an OLTP environment well make use of multiple CPUs just fine, but there's currently no support for parallel query processing so if you're doing a lot of large queries it might be an issue. > ps - if you subscribe to the mysql list, no you're not seeing double. > I posted a very similar message on the mysql lists a couple > of days ago.. Something you might want to consider is MySQL's disregard for data integrity. Try stuffing 'xx' into a varchar(1) some time and see what happens. That's just one example; http://sql-info.de/mysql/gotchas.html has a pretty complete list. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Version Control?
On Sat, Jun 11, 2005 at 02:47:39PM +0100, Russ Brown wrote: > I'd be extremely happy if somebody finds such a system that is already > written! I've never seen such a system, and one is sorely needed. If someone were to write one that worked well against multiple databases and didn't cost a fortune they could probably make a good amount of $$ with it. BTW, I believe some of the really high-end database tools will do what you're looking for, but those typically start at a couple grand or more. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Version Control?
On Sat, Jun 11, 2005 at 11:31:02AM -0500, Peter Fein wrote: > Peter Fein wrote: > > As an uninformed, off-the-wall idea, could one compare snapshots of the > > system tables to generate these diffs? I know next-to-nothing about > > these, but it seems like they'd contain the info you'd need. > > Here's another nutty idea: Could one create a (carefully designed) audit > table on the system tables themselves? You'd need to exclude irrelevant > stuff (stats or whatever) & I'd have no idea about performance impact. > > Dumping & transforming the audit would basically give you a script that > runs through all the actions done to a schema. I'm not 100% how to use > this for an update script though. I'm not sure a straight diff of > actions will work - it seems like the results may be order dependent in > some cases and you might need to infer undos. Anyway, it's interesting... If triggers were supported on system tables, then yes, you could easily create an audit record. And yes, such a record should make it easy to generate a patch file. Personally, I've always worked in such a way that development databases tend to be re-created frequently, from a set of definition files. When it's time to create patch code, I'll diff the different versions of the files and generate a patch file based on that. Of course it'd be really handy if I could load up two databases (one with the old schema, one with the new) and have some tool compare the two and generate a diff and a patch file. Of course it might not catch everything (such as droping a column and then re-adding it with the same definition), but it would work fine in 99.99% of the cases. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Scripting issues
On Tue, Jun 21, 2005 at 10:59:58AM +0200, [EMAIL PROTECTED] wrote: > Hi Richard > > Thanks for the suggestion. I'm sure I'll go that way. > One other question: Since in PostgreSQL you can have "overloaded" > functions, how do you query the system tables for the existence of a > particular version of the function? I can query > information_schema.routines for the function name, but not for the > particular parameters. If you're specifically worried about functions, why not just use CREATE OR REPLACE? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] [PATCHES] Removing Kerberos 4
On Wed, Jun 22, 2005 at 04:39:15PM -0400, Tom Lane wrote: > "Magnus Hagander" <[EMAIL PROTECTED]> writes: > > Yeah. But it has been declared dead by the Kerberos folks > > (http://www.faqs.org/faqs/kerberos-faq/general/section-7.html. And this > > document is from 2000, an dit was declared already then)... > > Right. The real question here is who's going to be using a 2005 > database release with a pre-2000 security system? There's a fair > amount of code there and no evidence that time spent on testing > and maintaining it is going to benefit anyone anymore. > > If someone wakes up and says "hey, I'm still ACTUALLY using that code", > I'm willing to forbear ... but otherwise I think its time is long gone. While I agree, if it's easy to just disable kerb without actually ripping the code out right now that might be a tad 'safer', as there might be some users who are using it but don't read the mailling lists. Has Kerb4 been marked as depricated in the docs at all? If not it might be best to just do that and then yank it later. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [HACKERS] [PATCHES] Removing Kerberos 4
On Thu, Jun 23, 2005 at 07:34:30PM +0200, Magnus Hagander wrote: > > Has Kerb4 been marked as depricated in the docs at all? If > > not it might be best to just do that and then yank it later. > > Yes, since 7.4. > > http://www.postgresql.org/docs/8.0/static/auth-methods.html#KERBEROS-AUT > H > http://www.postgresql.org/docs/7.4/static/auth-methods.html#KERBEROS-AUT > H > > "Kerberos 4 is considered insecure and no longer recommended for general > use." Just as a nitpick, in the future it would probably be better to explicitely say if something is considered depricated and will be removed in the future. Having said that, that statement means it's removal shouldn't come as a shock to anyone. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL Certification
On Fri, Jun 24, 2005 at 08:42:00AM -0700, Joshua D. Drake wrote: > Tatsuo Ishii wrote: > >>Am Sonntag, 12. Juni 2005 05:24 schrieb Tatsuo Ishii: > >> > >>>Thank you for interested in PostgreSQL CE. There is a sample > >>>examination problems page: > >>> > >>>http://osb.sra.co.jp/postgresql-ce/sys/quiz.php?titleid=S74_en > >> > >>Regarding question 3, > >> > >> Select one incorrect statement regarding the installation of PostgreSQL. > >> > >>1 1. PostgreSQL is unable to use languages other than English unless the > >>"--enable-multibyte" option is specified at "./configure" time. > >>2 2. At least 60MB of free disk space is required for compilation. > >>3 3. GNU make is required. > >>4 4. "root" cannot execute "make check" properly. > >>5 5. "--no-locale" should be specified if English is the only language > >>to be used in PostgreSQL. > >> > >>Apparently, the correct answer is 1, but statement 5 is equally false. > > > > > >Really? I vaguley recall that someone who came from US complained > >about the sort order of en locale. I thought English speakers prefer C > >locale. > > And if you want to be really picky number 4 is wrong as well: > > root# su - postgres -c "make check" > > I know that is really picky but as someone who manages the number of > PostgreSQL systems that I do, I might have picked that one. Number 2 is > also confusing because am I compiling ALL of PostgreSQL? I just did a > check and it took 63 megs just to unpack it, let alone compile it. This is why I put very little faith in certifications; all that they prove is that you know how to pass some test. Tell me this, does knowing any of the info in this question really make you a better PostgreSQL admin or user, other than #1? I would consider #2-4 to be trivia, and as others have said #5 is misleading and misinformative. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Populating huge tables each day
On Mon, Jun 27, 2005 at 12:43:57PM -0700, Dann Corbit wrote: > I see a lot of problems with this idea. > > You mention that the database is supposed to be available 24x7. > While you are loading, the database table receiving data will not be > available. Therefore, you will have to have one server online (with Why do you think that's the case? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Populating huge tables each day
On Mon, Jun 27, 2005 at 01:05:42PM -0700, Dann Corbit wrote: > > > -Original Message- > > From: Jim C. Nasby [mailto:[EMAIL PROTECTED] > > Sent: Monday, June 27, 2005 12:58 PM > > To: Dann Corbit > > Cc: Ben-Nes Yonatan; pgsql-general@postgresql.org > > Subject: Re: [GENERAL] Populating huge tables each day > > > > On Mon, Jun 27, 2005 at 12:43:57PM -0700, Dann Corbit wrote: > > > I see a lot of problems with this idea. > > > > > > You mention that the database is supposed to be available 24x7. > > > While you are loading, the database table receiving data will not be > > > available. Therefore, you will have to have one server online (with > > > > Why do you think that's the case? > > He's doing a bulk load. I assume he will have to truncate the table and > load it with the copy command. Don't ass-u-me; he said he'd be deleting from the main table, not truncating. > Is there an alternative I do not know of that is equally fast? Nope, truncate is undoubtedly faster. But it also means you would have downtime as you mentioned. If it were me, I'd probably make the trade-off of using a delete inside a transaction. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Populating huge tables each day
On Tue, Jun 28, 2005 at 10:36:58AM -0700, Dann Corbit wrote: > > Nope, truncate is undoubtedly faster. But it also means you would have > > downtime as you mentioned. If it were me, I'd probably make the > > trade-off of using a delete inside a transaction. > > For every record in a bulk loaded table? Sure. If the data's only being loaded once a day, it probably doesn't matter if that delete takes 10 minutes. > If it were that important that both servers be available all the time, I > would bulk load into a second table with the same shape and then rename > when completed. Interesting idea, though the problem is that AFAIK everything will block on the rename. If everything didn't block though, this might be a better way to do it, although it potentially complicates the code greatly (think about needing to add indexes, rebuild RI, etc.) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] To Postgres or not
On Wed, Jul 13, 2005 at 04:24:53PM -0400, Vivek Khera wrote: > > On Jul 12, 2005, at 1:16 PM, Ted Slate wrote: > > >If I stick with a true RDBMS then Codebase is out. So that leaves > >Postgres and MySQL. > > The first sentence rules out MySQL, so the second sentence should > read "So that leaves Postgres". Your problem is solved ;-) > > (If you are accustomed to Oracle, you are probably expecting an ACID > database, which rules out MySQL too). But there's no need to take our word for it... just google for 'mysql gotchas'. :) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Converting MySQL tinyint to PostgreSQL
On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote: > Ron Mayer wrote: > > Martijn van Oosterhout wrote: > > > > > > Well, you get another issue, alignment. If you squeeze your string > > > down, the next field, if it is an int or string, will get padded to a > > > multiple of 4 negating most of the gains. Like in C structures, there > > > is padding to optimise access. > > > > Anecdotally I hear at least as many people say that their database > > is more I/O bound than CPU bound; and it seems that adding bytes > > for alignment is a way of reducing CPU for more disk I/O. > > > > I guess unaligned access so expensive that it makes up for the extra i/o? > > This is a good point. We have always stored data on disk that exactly > matches its layout in memory. We could change that, but no one has > shown it would be a win. Out of curiosity, what would be involved in hacking the backend enough to be able to test this theory out? I'm guessing you'd want to convert between on-disk and in-memory formats as you read pages in, so either on-disk pages would become variable size (and smaller than memory pages) or in-memory pages would become variable size (and larger than on-disk pages). Or maybe as an alternative, would it be possible to determine how much space in a given relation was being wasted due to padding? That could be used to figure out how much IO could be saved on different transactions. While there would be a slight CPU penalty every time you read or write a page, I find it hard to believe it could come close to equaling IO cost. On a side note, I think it might be useful to have a seperate TODO catagory for ideas that need to be tested to see if they're worth implementing for real. This is a case where it's probably substantially easier to estimate (or maybe even measure) how much there is to gain from this than to do the actual work and then see if it helps. It's also likely that a less experienced hacker could test the theory out. Some likely items for this list: Reduce WAL traffic so only modified values are written rather than entire rows? Find a way to reduce rotational delay when repeatedly writing last WAL page Precompile SQL functions to avoid overheadDo async I/O for faster random read-ahead of data Not on todo: Estimate gains from not using the in-memory format of data for on-disk storage Estimate gains from reducing the amount of space used by visibility information in each tuple BTW, what ever happened to the idea of having a list of projects for beginners? (Or maybe it'd be better to assign a numeric difficulty to each TODO item?) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Converting MySQL tinyint to PostgreSQL
On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote: > On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote: > > On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote: > > > This is a good point. We have always stored data on disk that exactly > > > matches its layout in memory. We could change that, but no one has > > > shown it would be a win. > > > > Out of curiosity, what would be involved in hacking the backend enough > > to be able to test this theory out? I'm guessing you'd want to convert > > between on-disk and in-memory formats as you read pages in, so either > > on-disk pages would become variable size (and smaller than memory pages) > > or in-memory pages would become variable size (and larger than on-disk > > pages). > > It's a pain because on some architectures you can't do unaligned > accesses. I imagine you'd have to have the on-disk pages in memory and > copy them to a temporary space when you actually want to use the data, > converting on the fly. My thought was to convert as pages were read and written. That should minimize the code impact. > IMHO a much much better approach would be the two phase: > - Decouple order of columns on disk from logical column order > Then people can rearrange columns, people do ask that occasionally. > - Change CREATE TABLE to rearrange columns on disk (not the logical > order) to minimize padding. > > This gives you real benefits without having to overhaul the code... True, that would be of some benefit, but not as much as being able to compact the disk storage. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Converting MySQL tinyint to PostgreSQL
On Sat, Jul 16, 2005 at 03:18:24PM -0700, Ron Mayer wrote: > Jim C. Nasby wrote: > >On Thu, Jul 14, 2005 at 11:29:23PM +0200, Martijn van Oosterhout wrote: > >>On Thu, Jul 14, 2005 at 11:30:36AM -0500, Jim C. Nasby wrote: > >>>On Wed, Jul 13, 2005 at 07:52:04PM -0400, Bruce Momjian wrote: > >>> > >>>>This is a good point. We have always stored data on disk that exactly > >>>>matches its layout in memory. We could change that, but no one has > >>>>shown it would be a win. > >>> > >>[...] > > > >My thought was to convert as pages were read and written. That should > >minimize the code impact. > > If that were practical, even more radical I/O saving tricks might be > possible beyond removing alignment bytes - like some compression algorithm. True, though there's a few issues with zlib compression. First, you have to be able to pull specific pages out of the files on disk. Right now that's trivial; you just read bytes xxx - yyy. With compression things are more difficult, because you no longer have a fixed page size. Another issue is that with a variable disk page size, you have to deal with what happens when you try to put a page back on disk but the page is now larger than it's original size. These issues are why I suggested a fixed disk page size and a variable in-memory page size; it simplifies things a bit. It does however create some problems of it's own. When you go to transform/compress a page to put it on disk if the in-memory page is now too large you'll need to move some tuples to another page. Something else to consider is that a simple compression scheme such as eliminating alignment padding makes it easy to determine how large a tuple will be on disk versus in memory. This means you can do things like determine at the time of tuple creation if that tuple will fit in an existing page or not. I don't know if the same can be said for other methods. Another factor is that more complex compression methods will be much more CPU intensive. FWIW, the way oracle handles compression is as a one-time operation. When you tell it to compress a table it will re-write the entire table, compressing it as it goes. But any pages that get changed after that will end up uncompressed. Of course in a data warehouse environment that's perfectly acceptable. Ultimately I don't see anything being done along these lines unless someone can come up with some data indicating performance gains, which will probably mean hacking some amount of this in and benchmarking it. Although for the case of simple elimination of alignment padding you could probably come up with some pretty good estimates just by looking at a table's layout and it's statistics. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] foreign key constraints and inheritence
On Sun, Jul 17, 2005 at 10:33:58PM +0200, Sander Steffann wrote: > I wish I knew enough about the internals of PostgreSQL to write one :-) > Sander Well, there are other TODO items that are much simpler, which would be a great way to learn more about the internals. :) There's been talk of creating a list of good TODO items for new coders to tackle, but I don't think that ever happened. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Converting MySQL tinyint to PostgreSQL
On Tue, Jul 19, 2005 at 02:02:28AM +0200, Dawid Kuroczko wrote: > On 7/18/05, Lincoln Yeoh wrote: > > However, maybe padding for alignment is a waste on the disk - disks being > > so much slower than CPUs (not sure about that once the data is in memory ). > > Maybe there should be an option to reorder columns so that less space is > > wasted. > > Out of curiosity, do I understand right that if I create table > > CREATE TABLE sample1 ( > a boolean, > b int, > c boolean > ); > > ...it will take more storage than: > > CREATE TABLE sample2 ( > b int, > a boolean, > c boolean > ); > > ...I don't think such ordering should matter, but I would like to know > how it really is. :) Actually, I believe that's the case with just about every database, though of course each one has different alignment constraints. The point is that I don't know of any database that will silently re-order fields under the covers to optimize storage. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Converting MySQL tinyint to PostgreSQL
On Tue, Jul 19, 2005 at 11:48:16AM +0200, Martijn van Oosterhout wrote: > int, bool, bool 6 bytes, no padding > bool, int, bool 9 bytes, including 3 bytes padding > bool, bool, int 8 bytes, including 2 bytes padding We store bool's in a byte and don't compact? That would be another very handy change... the first 8 bools in a table all use one byte, the next 8 use another, etc. > Assuming an int is 4 bytes. If it's 8-bytes it's even more obvious. > > I think it would be a really good for postgresql to reorder the fields > in the background (on disk, not in select * statements). I believe the last time this was discussed the consensus was that we should first have a way for users to do this manually, since the backend would need most of that framework anyway. I don't know that much (if any) work has been done on this. One of the advantages of divorcing the in-memory page layout from the on-disk page layout is that by doing so you get even more benefit from re-ordering fields, and it's not dependant on figuring out how to present fields in a different order than how they're stored in the page. The downside is that it doesn't get you some of the in-memory benefits that field ordering will get you, but I suspect in most cases the on-disk gains will swamp those out anyway. Does anyone have any idea what it would take to hack in divorcing disk page layout from memory layout just for testing? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Wishlist?
On Wed, Jul 20, 2005 at 02:44:19PM +1000, Ezequiel Tolnay wrote: > Hi everyone! I'd like to post a few features I'd like to see in coming > releases. Does anyone know about a wishlist newsgroup or web page, or > whoat is the proper way to propose such requests? http://www.postgresql.org/developer/roadmap Note especially the part about developers scratching their own itch. You can request all you want, but unless a number of developers agree it's a good idea it probably won't make it to the TODO. And stuff can get pulled from the TODO without actually being done. You'll have better luck getting something added if you're willing to commit to developing it (or pay someone else to). Having said all that, if you want to just request stuff (that's not already on the TODO), you can just post it here and it might get on the TODO if there's enough interest. If you want to discuss specifics, -hackers is probably a better place. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Slow Inserts on 1 table?
What indexes are defined on both tables? Are there any triggers or
rules?
On Wed, Jul 20, 2005 at 09:50:54AM -0500, Dan Armbrust wrote:
> I have one particular insert query that is running orders of magnitude
> slower than other insert queries, and I cannot understand why.
> For example, Inserts into "conceptProperty" (detailed below) are at
> least 5 times faster than inserts into "conceptPropertyMultiAttributes".
>
> When I am running the inserts, postmaster shows as pegging one CPU on
> the Fedora Core 3 server it is running on at nearly 100%.
>
> Any advice is appreciated. Here is a lot of info that may shed light on
> the issue to someone with more experience than me:
>
> Example Insert Query with data:
> INSERT INTO conceptPropertyMultiAttributes (codingSchemeName,
> conceptCode, propertyId, attributeName, attributeValue) VALUES ('NCI
> MetaThesaurus', 'C005', 'T-2', 'Source', 'MSH2005_2004_10_12')
>
> EXPLAIN ANALYZE output:
> QUERY PLAN
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.008
> rows=1 loops=1)
> Total runtime: 4.032 ms
>
> Table Structure:
> CREATE TABLE conceptpropertymultiattributes (
>codingschemename character varying(70) NOT NULL,
>conceptcode character varying(100) NOT NULL,
>propertyid character varying(50) NOT NULL,
>attributename character varying(50) NOT NULL,
>attributevalue character varying(250) NOT NULL
> );
>
> Primary Key:
> ALTER TABLE ONLY conceptpropertymultiattributes
>ADD CONSTRAINT conceptpropertymultiattributes_pkey PRIMARY KEY
> (codingschemename, conceptcode, propertyid, attributename, attributevalue);
>
> Foreign Key:
> ALTER TABLE ONLY conceptpropertymultiattributes
>ADD CONSTRAINT f FOREIGN KEY (codingschemename, conceptcode,
> propertyid) REFERENCES conceptproperty(codingschemename, conceptcode,
> propertyid);
>
>
> Structure of Table Referenced by Foreign Key:
> CREATE TABLE conceptproperty (
>codingschemename character varying(70) NOT NULL,
>conceptcode character varying(100) NOT NULL,
>propertyid character varying(50) NOT NULL,
>property character varying(250) NOT NULL,
>"language" character varying(32),
>presentationformat character varying(50),
>datatype character varying(50),
>ispreferred boolean,
>degreeoffidelity character varying(50),
>matchifnocontext boolean,
>representationalform character varying(50),
>propertyvalue text NOT NULL
> );
>
> Primary Key:
> ALTER TABLE ONLY conceptproperty
>ADD CONSTRAINT conceptproperty_pkey PRIMARY KEY (codingschemename,
> conceptcode, propertyid);
>
> Thanks,
>
> Dan
>
> ---(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
>
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Administration of raster data
On Wed, Jul 20, 2005 at 09:47:53AM +0200, Axel Orth wrote: > Hey List, > due to the PostGIS extension PostgreSQL is THE free DBS for geospatial data. > I know of its capabilities to handle vector data and like to know if > PostgreSQL can also be used to administer geospatial raster data. > > I would be thankful for any information and experiences in this field of > application. PostGIS has it's own email list as well as other support options; you should try those out. http://postgis.refractions.net/support/ -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Wishlist?
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote: > Thanks a lot, I've read the TODO and have now an idea of what is the > current trend or plans for future releases. > > Regarding my own needs, I'd be happy to "scratch my own itch", but > perhaps I should find some consensus before I engage myself in the task > of developing. And I would also need a little guidance in regards to how > to submit changes and all that. http://www.postgresql.org/developer/coding should help you get started. Also make sure to subscribe to -hackers and send an email there when you find something on the TODO list (or not on the list) that you'd like to work on. > * Scripting language: A very powerful feature in SQLServer is its > support of variables and control structures embedded in the SQL > language, thus allowing running complex scripts without the need to pack > them in a function and then running the function. This is especially > limiting in PG since it doesn't support the creation of temporary > functions. In MSSSQL, variables are prefixed with AT (@) symbols, must > be declared before they're used, live only within the prepared statement > and can be set using SET, SELECT and UPDATE. Also, the conditional > statements are only IF and WHILE, they support subqueries and function > calls in their expressions, and group statements using BEGIN and END. I > find prefixing variables thus is a lot less confusing and error prone > than the parameter alias and variables used in PL/pgSQL. For the > implementation on PG, I believe that perhaps it would be nice to use > curly brackets instead of BEGIN and END. Well, psql will do some of what you want, but yes, it would be useful if there was a means to run plpgsql code from the command line. There's been some talk about this, but nothing definative afaik. > * Allow FETCH command to be used with CREATE TABLE tab AS qry (in place > of qry) I'm not really clear on what you're looking for here.. > * Allow a function's result type to be defined in the function itself, > similarly to the way OUT parameters are constructed. If a type for it > must be created for this purpose, set it as a system object and handle > its recreation automatically upon CREATE OR REPLACE FUNCTION to avoid > the current catch-22 dependency issues. Or perhaps let function > returning a SETOF RECORD to have an implicit (undefined) result type, > thus avoiding having to specify the type dynamically in the caller > statement SELECT func(...) AS (...) Is there enough here for a TODO? > I hope a few of you will be interested in my proposals, so they get into > the TODO list. Once there, I'd be happy to contribute with some > development myself (either for these or other TODO items). I'm not a > proficient hacker with lots of time available, but I believe I'm a > fairly good programmer and I'm a Gentoo user so I have everything needed > to compile and build. But I believe I'll require a few months of > studying the code alone before I dare making any additions. So any hints > of how to become a PG developer to submit changes, and where to start > (or what documentation to read first) to have a fast head-start will be > highly appreciated. >From what I've seen, your best bet is to pick something (prefferably on the TODO list) that looks fairly simple, send an email to -hackers claiming it (and asking for any clarification, etc), and dive in. If you're interested in internals or new feature development you should absolutely subscribe to -hackers. Have I emphasised -hackers enough yet? :) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] Postgresql with max_connections=4096
On Wed, Jul 27, 2005 at 04:46:56PM +0200, [EMAIL PROTECTED] wrote: > I'm now testing with pg_pool installed on each apache frontend with 260 > pg_pool preforked clients in each machine. > > The database seems to work better. At least when it goes to swap it > doesn't stop working... Wait, are you saying your database server is swapping? You'll never get any kind of performance if that's the case. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type
On Thu, Jul 28, 2005 at 12:53:07AM -0400, Tom Lane wrote: > Gregory Youngblood <[EMAIL PROTECTED]> writes: > > ... the problem is unsigned bigint in mysql to postgresql. > > There's not another larger integer size that can be used that would > > allow the 18446744073709551615 (is that the max value?) max value > > available in mysql. Or am I missing something? > > You'd have to translate that to NUMERIC, which would work but would > take a bit of a performance hit ... Is there any serious impediment to adding unsigned types to PostgreSQL? They should be as readily supported as signed, right? I don't think these would even have to be in core, if that's a concern. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type
On Wed, Jul 27, 2005 at 09:16:04PM -0700, Chris Travers wrote: > Christopher Kings-Lynne wrote: > > >>So far, the checklist I can see includes: > >>* Maintaining conversion scripts > > > > > >What I think we need is a C program that dumps directly from MySQL > >into PostgreSQL sql. > > > >ie. Take the mysqldump source code and just modify its output. > > > >Will inherit the MySQL license though :( > > > > It then occurred to me that a better answer would be something like a > DBI perl script that has modules for storing important queries for > various SQL database servers, and outputting the format in PostgreSQL > syntax. That way one can have a general conversion toolkit that can be > easily expanded and could also be used in porting Oracle, MS SQL, > SQL-Lite, and other databases to PostgreSQL. In this way, you could > also avoid the MySQL license even by MySQL AB's weird interpretation of > derivative works. > > This way one could also have fairly smart logic in the conversion as > well. Gee, your max unsigned bigint is over our bigint limit, lets use > numeric instead or hmm... you have an ENUM type here. Lets use a > VARCHAR() with a constraint for now. This logic could be easily tweeked > by a decent programmer. I think that such a program should be somewhat > trivial to write for at least tables and data definitions. Triggers, > functions, etc. would be more difficult. But I think it is reasonable > to expect that functions might have to be manually ported in most > circumstances. The only downside I can think of to such an approach is that it will be much slower than directly reading a dump file of some kind. But, on the up-side, it's also possible that it could allow for minimal downtime migraitons to take place in the future (of course adding that support would take a lot of work, but theoretically it's possible). -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] [pgsql-advocacy] MySQL to PostgreSQL, was ENUM type
On Fri, Jul 29, 2005 at 03:57:48PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Is there any serious impediment to adding unsigned types to PostgreSQL? > > Quick, what's the datatype of 12345? And why? > > My guess is that transparent support for such a thing would require some > tricky and fragile compromises in the numeric hierarchy's promotion > rules. The handling of smallint is already pretty unsatisfactory > because "12345" is considered int not smallint; adding three or four > unsigned types would make that problem many times worse. ISTM that so long as you don't overflow or lose precision it shouldn't really matter what datatype is used to represent a number, until you use it someplace that specifies a datatype to use (such as a function or a table). I realize that function overloading gums this up a bit, but I don't think it's unreasonable to require the user to explicitly cast if they want an explicit function out of the overloaded set to be used (though throwing a warning if they don't cast might be a good idea). > > I don't think these would even have to be in core, if that's a > > concern. > > Sure, if you don't need it to work reasonably nicely --- although the > promotion rules are now embedded in pg_cast and hence modifiable, the > initial determination of a datatype for a numeric literal is still > hard-wired in the grammar. So what would those rules currently do when they find a value that exceedes a signed int? Or a signed bigint? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] [pgsql-advocacy] New MySQL to PostgreSQL Migration Guide
So should we ask to take over the sql2pg project on pgfoundry and move the existing scripts over there, as well as the migration guide? On Sun, Jul 31, 2005 at 10:51:06PM -0700, Chris Travers wrote: > Hi; > > I have just posted a MySQL to PostgreSQL migration guide at > http://www.metatrontech.com/wpapers and it is free for pretty much any > use (I do have a somewhat toned-down advertising clause in the copyright > license). > > It is a first draft and formatting and other stuff will be likely > changed. I am also considering creating a set of wrapper functions > which will provide compatibility with many of the non-standard functions > in MySQL. > > Any feedback, etc. is appreciated. > > Best Wishes, > Chris Travers > Metatron Technology Consulting > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL vs. MySQL
On Mon, Aug 01, 2005 at 06:44:50PM +0200, [EMAIL PROTECTED] wrote: > Hi all, > I am sorry for a stupid easy question, but I'am PostgreSQL novice. > Our development team has encountered problem with trying to install and > maintain cluster (pgcluster) on our production database. So they have > decided to switch the entire solution to MySql database. > a) have you got any good/bad experience with administering and running > cluster on PostrgeSQL DB? > b) would u switch PostgreSQL to MySql (7.4 --> 4.1) and why? For pgcluster help, your best bet is to hit either their mailling list or their forums. http://pgfoundry.org/projects/pgcluster/ has more info. As for MySQL, http://sql-info.de/mysql/gotchas.html has about 100 different reasons why you don't want to use MySQL. Some favorites: Feb. 31st is a valid date Data will be silently truncated if it overflows 1/0 = NULL count(*) is an approximation easy to configure in such a way that it's not ACID ... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to write jobs in postgresql
On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: > chiranjeevi.i wrote: > > Hi Team Members, > > > > Is it possible to write jobs in postgresql & if possible how > > should I write .please help me. > > See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's in > the planning stages. Actually, it's currently in the going nowhere stage since no one's expressed any interest in it. Anyone who's interested is encouraged to join the mailing list and post what they'd like to see from the project. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 5 new entries for FAQ
On Wed, Aug 10, 2005 at 11:22:58PM +0200, Martijn van Oosterhout wrote: > On Wed, Aug 10, 2005 at 03:33:16PM -0400, Bruce Momjian wrote: > > For the batch job and single-CPU issues, they did not fit into existing > > FAQ entries, and I am not sure they are asked enough to be added as > > FAQs. I am interested to hear other's opinions on this. > > Maybe, just looking through my email and my memories they've been a few > times. How often a question needs to be asked before putting it in the > FAQ is easier than answering it each time, well, that's a matter of > debate. > > The other extreme is the Exim FAQ which is huge, but it has answered > every question I've ever asked. Not sure if that's a goal though. My experience is that long FAQ's are fine, so long as they're easy to search through. This means you've got to support users who may not know the magic word to search on. A good example is finding the limits for how many rows in a table; searching on limit gets you nothing. The alternative is to make it very easy for users to skim through the TOC to find what they need. Right now that's not very easy to do because 2 of the catagories are over 9 items long (humans deal with info best in chunks of 5-9 items; most people do best with 7 items or less). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to implement table caching
There is also http://people.freebsd.org/~seanc/pgmemcache/
On Mon, Aug 15, 2005 at 04:54:31PM -0500, Thomas F. O'Connell wrote:
> Andrus,
>
> You might consider something like materialized views:
>
> http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
>
> Whether table caching is a good idea depends completely on the
> demands of your application.
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
>
> Strategic Open Source: Open Your i?
>
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-469-5150
> 615-469-5151 (fax)
>
> On Aug 14, 2005, at 1:12 PM, Andrus Moor wrote:
>
> >To increase performance, I'm thinking about storing copies of less
> >frequently changed tables in a client computer.
> >At startup client application compares last change times and
> >downloads newer
> >tables from server.
> >
> >CREATE TABLE lastchange (
> > tablename CHAR(8) PRIMARY KEY,
> > lastchange timestamp without time zone );
> >
> >INSERT INTO lastupdated (tablename) values ('mytable1');
> >
> >INSERT INTO lastupdated (tablename) values ('mytablen');
> >
> >CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
> >AS $$BEGIN
> >UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME;
> >RETURN NULL;
> >END$$ LANGUAGE plpgsql STRICT;
> >
> >CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON
> >mytable1
> > EXECUTE PROCEDURE setlastchange();
> >
> >CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON
> >mytablen
> > EXECUTE PROCEDURE setlastchange();
> >
> >Is table caching good idea?
> >Is this best way to implement table caching ?
> >
> >Andrus.
>
> ---(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
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Softwarehttp://pervasive.com512-569-9461
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Field order
On Wed, Aug 17, 2005 at 03:24:58PM -0500, Bruno Wolff III wrote: > On Wed, Aug 17, 2005 at 12:09:12 -0600, > Michael Schmidt <[EMAIL PROTECTED]> wrote: > > I've searched the archives and found this question was asked in 2001 but > > never answered. Does the order of fields in a table make a difference? In > > Paradox (from whence I come), there was some belief that reliability was > > increased if memo (text) fields were placed at the end of the table. If > > field order does make a difference, does the EMS PostgreSQL manager > > "reorder" function physically rearrange the fields? > > > > Thanks for your time in considering this basic question > > Some space can be saved by putting the columns with the largest alignments > first. > The columns are not moved around to do the above, but are kept in the > declared order. Ugh, and here I'd been doing it the other way around. I assume variable-length stuff should always go last, right? This make sense? Variable goes last, always Larger alignment before smaller NOT NULL before nullable -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [BUGS] BUG #1830: Non-super-user must be able to copy
On Fri, Aug 19, 2005 at 08:03:39AM -0700, Stephan Szabo wrote: > On Fri, 19 Aug 2005, Bernard wrote: > > > But we can take this one step further so that we don't even need to > > trust ourselves: > > > > The logical next step is that for a non-postgresql-superuser user, > > COPY FROM files have to be world-readable and COPY TO files and > > directories have to be world-writable. The server checks the file > > attributes and grants copy permission depending on them. Obviously any > > Postrgres system files must not be world-readable and world-writable. > > > > Problem solved. One doesn't need to be a genius to figure this out. > > No, it's not solved. It prevents that problem for the configuration > files, but still gives access to other world readable files on the system > for example /etc/passwd on many systems (yes it's not terribly interesting > in general, but still is often not acceptable to retrieve). > > You'd probably want to add the ability to setup which directories that are > allowed to be read or written to as configuration separately from unix > file permissions. FWIW, this is exactly what Oracle does. A DBA has to configure what directories you can bulk copy to/from. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ORDER BY time consuming
On Sun, Aug 21, 2005 at 12:04:01PM +0200, Ben-Nes Yonatan wrote: > Hi All, > > I got a table with about 4.5 millions rows in it which is connected to > another table with about 60 millions rows which are used as keywords for > searching. > > I succeded to create fast queries on the first table that finds a row at > the first table which is connected to up to 4 diffrent keywords at the > second table and LIMIT the result to 12 (I want to allow the surfers of > the site to press back and next to see more products so ill make it with > OFFSET). > > I want to be able to order my result by a specific column but when I > insert ORDER BY into the query (and any other query that I tried) it > becomes extremly slow, what can I do to solve this problem? Your question is too generic to answer specifically, but I suspect that if you use your un-ordered query as a subquery in the FROM clause and then order that it will work well. IE: SELECT * FROM (SELECT ...) a ORDER BY f1, f2, f3 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] extract (dow/week from date)
On Sun, Aug 21, 2005 at 08:00:45AM -0300, Clodoaldo Pinto wrote: > Of course it would be even better if we could pass parameters to the > functions changing its behavior such as sunday/monday as the first day > or 0-1 as the first day. FWIW, it seems most things that support changing first day of the week to be something other than do it through a global setting. ISTM that would be much less error prone than having to make sure you used it in all your function calls (not to mention a lot less typing...) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query results caching?
On Mon, Aug 22, 2005 at 10:13:49PM +0200, Ben-Nes Yonatan wrote: > I think that I was misunderstood, Ill make an example: > Lets say that im making the following query for the first time on the > "motorcycles" table which got an index on the "manufacturer" field: > > EXPLAIN ANALYZE SELECT manufacturer FROM motorcycles WHERE > manufacturer='suzuki'; > ... Total runtime: 3139.587 ms > > Now im doing the same query again and i get a much faster result (cause > of the "caching"): Total runtime: 332.53 ms > > After both of those queries I drop the index and query the table again > with the exact same query as before and now I receive: Total runtime: > 216834.871 ms > > And for my last check I run the exact same query again (without creating > the INDEX back again) and I get quite similar result to my third query: > Total runtime: 209218.01 ms > > > My problem is that (maybe I just dont understand something basic > here...) the last 2 (also the second query but I dont care about that) > queries were using the "cache" that was created after the first query > (which had an INDEX) so none of them actually showed me what will happen > if a client will do such a search (without an INDEX) for the first time. > > I want to delete that "caching" after I do the first 2 queries so my > next queries will show me "real life results". Emptying the cache will not show real-life results. You are always going to have some stuff cached, even if you get a query for something new. In this case (since you'll obviously want those indexes there), after some amount of time you will have most (if not all) of the non-leaf index pages cached, since they take a fairly small amount of memory and are frequently accessed. This makes index traversal *much* faster than your initial case shows, even if you query on something different each time. Testing with a completely empty cache just isn't that realistic. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Query results caching?
On Tue, Aug 23, 2005 at 12:27:39AM +0200, Ben-Nes Yonatan wrote: > Jim C. Nasby wrote: > >Emptying the cache will not show real-life results. You are always going > >to have some stuff cached, even if you get a query for something new. In > >this case (since you'll obviously want those indexes there), after some > >amount of time you will have most (if not all) of the non-leaf index > >pages cached, since they take a fairly small amount of memory and are > >frequently accessed. This makes index traversal *much* faster than your > >initial case shows, even if you query on something different each time. > >Testing with a completely empty cache just isn't that realistic. > > As far as I understand it at my situation where all of the data is > deleted and inserted each day from the start (INDEX will get lost with > it..) & the endless variety of possible keywords search's & the immense > size of the tables, the following reason wont last.. or am I wrong here? You're wrong - to an extent. Remember that while you're loading all that data it's also being cached. Now, some of it will probably end up falling out of the cache as all the data is read in, but you certainly won't be starting from the clean slate that you're looking for. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ctid access is slow
On Wed, Aug 24, 2005 at 09:26:10AM +0930, Jeff Eckermann wrote: > The ctid value can be useful in a multi user application, to check whether a > record has been changed by another user, before committing changes. > Whenever a record is updated the ctid value will be changed, so by storing > the ctid value when first fetching the record, that can be compared with the > current ctid value before doing the update. I believe that's not necessarily true. If you select a tuple and it's ctid and it's updated more than once with a vacuum in-between I believe it could end up back in the same position, which would mean the same ctid. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(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] ctid access is slow
On Tue, Aug 23, 2005 at 06:42:33PM -0700, Jeff Eckermann wrote: > > I believe that's not necessarily true. If you select > > a tuple and it's > > ctid and it's updated more than once with a vacuum > > in-between I believe > > it could end up back in the same position, which > > would mean the same > > ctid. > > True. But the probability of that happening would > generally be low enough not to bother the designers of > most applications. Designers that don't care about their data, maybe. Here's the use case that was implied: Application selects a bunch of data to present to the user to be edited User edits data even though it's not locked in the database Application gets data and checks to see if it's changed. If it not, *BLAM*, new changes are put into the database Now, if that check for changed data fails with a false negative, you just nuked data. A better solution is to use a combination of a timestamp and a sequence. Why both? Because it's possible for the clock to be set back (though this is something best avoided), and a sequence will eventually roll over. It's impossible to have a collision in this scenario unless you roll the clock way back AND reset the sequence (assuming you're using an integer sequence). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql replication
Or, for something far easier, try http://pgfoundry.org/projects/pgcluster/ which provides syncronous multi-master clustering. On Wed, Aug 24, 2005 at 12:53:34PM -0700, Jeff Davis wrote: > Chris Browne wrote: > > > > Slony-I is a master/slave asynchronous replication system; if you > > already considered it unsuitable, then I see little likelihood of > > other systems with the same sorts of properties being suitable. > > > > What could conceivably be of use to you would be a *multimaster* > > asynchronous replication system. Unfortunately, multimaster > > *anything* is a really tough nut to crack. > > > > In general that's a difficult problem, but in practice there may be a > solution. > > For instance, perhaps the following configuration would be helpful: > > Make a database for each physical server, called db1 ... dbN. Let your > logical tables in each database be table1 ... tableM. Now, for each > logical tableX (where 1 <= X <= M), make N physical tables, tableX_1 ... > tableX_N. Now, make a view called tableX that is the UNION of tableX_1 > ... tableX_N (tableX is not a real table, it's just a logical table). > > Now, use Slony-I. For each dbY (where 1 <= Y <= N), make dbY a master > for tableX_Y (for all X where 1 <= X <= M) and a slave for tableX_Z (for > all X,Z where 1 <= X <= M, Z != Y). > > Now, use a rule that replaces all INSERTs to tableX (where 1 <= X <= M) > on dbY (where 1 <= Y <= N) with INSERTs to tableX_Y. > > That was my attempt at being unambiguous. In general what I mean is that > each database is master of one piece of a table, and slave to all the > other pieces of that table, and then you have a view which is the union > of those pieces. That view is the logical table. Then have a RULE which > makes INSERTs go to the physical table for which that database is master. > > The advantages: if one machine goes down, the rest keep going, and > merely miss the updates from that one site to that table. If one machine > makes an insert to the table, it quickly propogates to the other > machines and transparently becomes a part of the logical tables on those > machines. > > The disadvantages: UPDATEs are difficult, and might end up with a > complicated set of rules/procedures/triggers. You may have to program > the application defensively in case the database is unable to update a > remote database for various reasons (if the record to be updated is a > part of a table for which another database is master). Also, since the > solution is asynchronous, the databases may provide different results to > the same query. > > In general, this solution does not account for all kinds of data > constraints. The conflict resolution is very simplified because it's > basically just the union of data. If that union could cause a constraint > violation itself, this solution might not be right for you. For > instance, let's say you're tracking video rentals, and store policy says > that you only rent one video per person. However, maybe they go to store > 1 and rent a video, and run to store 2 and rent a video before store 1 > sends the INSERT record over to store 2. Now, when they finally do > attempt to UNION the data for the view, you have an inconsistant state. > > Many applications can get by just fine by UNIONing the data like that, > and if not, perhaps work around it. > > I hope this is helpful. Let me know if there's some reason my plan won't > work. > > Regards, > Jeff Davis > > > > > ---(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 > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views
On Thu, Aug 25, 2005 at 03:03:30PM -0700, CSN wrote: > For lack of a better term, but I was curious if there > is/was any thought about making PG's views > automatically "see" changes in underlying tables, as > opposed to currently having to drop/create all > corresponding views if a table's structure (add/delete > fields, etc.) is changed. There's not currently a TODO for this, no. http://www.postgresql.org/docs/faqs.TODO.html I'm not sure how hard it would be to do, since currently CREATE VIEW v AS SELECT * FROM t actually expands the * out at creation time. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views
On Fri, Aug 26, 2005 at 04:54:06PM -0400, Greg Stark wrote: > > Ian Harding <[EMAIL PROTECTED]> writes: > > > Brand X doesn't do it in their backend either. > > If your Brand X is the same as my Brand X then it's worth noting that they > didn't previously do anything sane in their backend. It used to invalidate all > your views and you had to recompile them before they would work again. > > I wonder whether it would be saleable to have an option to work around this > "feature". I'm thinking one of two directions: > > 1) An alternate type of view that just stores the text of the view and is > interpreted at time of use like: > > CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab) > > or 2) A command to recompile a view which would go back to the original source > and reinterpret it like: > > ALTER VIEW foo RECOMPILE > > Or I guess you could have the latter and then add the former as a view that > automatically recompiles any time a object it depends on is altered. I agree that CREATE DYNAMIC would be a good thing to have. It would certainly save me time on some of my projects. Can we TODO this? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views
On Sat, Aug 27, 2005 at 12:50:44AM -0400, Bruce Momjian wrote: > Jim C. Nasby wrote: > > > I wonder whether it would be saleable to have an option to work around > > > this > > > "feature". I'm thinking one of two directions: > > > > > > 1) An alternate type of view that just stores the text of the view and is > > > interpreted at time of use like: > > > > > > CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab) > > > > > > or 2) A command to recompile a view which would go back to the original > > > source > > > and reinterpret it like: > > > > > > ALTER VIEW foo RECOMPILE > > > > > > Or I guess you could have the latter and then add the former as a view > > > that > > > automatically recompiles any time a object it depends on is altered. > > > > I agree that CREATE DYNAMIC would be a good thing to have. It would > > certainly save me time on some of my projects. > > > > Can we TODO this? > > How is this different from materialized views, which is already on the > TODO list? The idea behind the DYNAMIC VIEW is that if you made a DDL change in the table it could be reflected in the view. So for example, if you defined a view as SELECT * FROM table; and then added a field to the table that field would also show up in the view. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] update functions locking tables
On Tue, Aug 30, 2005 at 08:13:15AM -0300, Clodoaldo Pinto wrote: > 2005/8/29, Michael Fuhr <[EMAIL PROTECTED]>: > > > > In general, writers shouldn't block readers. Have you examined > > pg_locks? Do you know exactly what the blocked queries are, or can > > you find out from pg_stat_activity (stats_command_string must be > > enabled)? Are you doing any explicit locking (LOCK statement)? > > > > This is one of the blocked queries: > select count (*) from times_producao where pontos_0 - pontos_7 > 0; FWIW, that where clause might be more efficient as WHERE pontos_0 > pontos_7. Some databases would be able to use indexes to answer that (not sure if PostgreSQL could), plus it removes an operator. It also seems to be cleaner code to me. :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"
On Tue, Aug 30, 2005 at 11:20:49PM -0400, Greg Stark wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > > Plus, how is the server supposed to KNOW that you have access to the > > file? psql may know who you are, but the server only knows who you are > > in the "postgresql" sense, not the OS sense. > > My original suggestion was that clients connected via unix domain sockets > should be allowed to read any file owned by the same uid as the connecting > client. (Which can be verified using getpeereid/SO_PEERCRED/SCM_CREDS.) > > Alternatively and actually even better and more secure would be passing the fd > directly from the client to the server over the socket. That avoids any > question of the server bypassing any security restrictions. The client is > responsible for opening the file under its privileges and handing the > resulting fd to the server over the socket. > > None of this helps for remote clients of course but remote clients can just > ftp the file to the server anyways and some manual intervention is necessarily > needed by the DBA to create a security policy for them. What do people think about the Oracle method where bulk data operations can only occur in a specified directory? Making that restriction might address some of the security concerns. I don't think we should change COPY in such a way that you *have* to use a specified directory, but if it was an option that helped with the security concerns... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] optimum settings for dedicated box
On Tue, Aug 30, 2005 at 09:43:19PM -0700, Ian Harding wrote: > Mine in similar, and the only thing I have changed from defaults is > work_mem. It made certain complex queries go from taking forever to > taking seconds. I have a database connection pool limited to 10 > connections, so I set it to 10MB. That means (to me, anyway) that > work_mem will never gobble more then 100MB. Seems OK since I have > 1GB. That's not totally true. A single query can use work_mem for multiple steps, so if work_mem is 10MB a single query could end up using 20MB, 30MB, or even more. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(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] newbie - postgresql or mysql
MySQL has a nasty habit of ignoring standards; in every other database I've used, if you want to quote an identifier (such as a field name), you use ", not `. The fields are also incompatable. int() is non-standard, for starters. There are MySQL to PostgreSQL conversion tools out there that should help. On Wed, Aug 31, 2005 at 02:50:16PM -0400, Frank wrote: > Thanks for the feedback, sorry I was not more specific. > We are a non-profit hospital and have been using MySQL for about 4 years. > > I wanted to convert some apps over to use postgresql and cannot find > a good tool to import and auto create the tables. > MySQL syntax is not compatible with postgresql. > I get: > ERROR: syntax error at or near "`" at character 14 > from the MySQL output below. > > CREATE TABLE `category` ( > `category_id` int(11) NOT NULL auto_increment, > `category` char(50) default NULL, > `LastUser` int(11) NOT NULL default '0', > `LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on > update CURRENT_TIMESTAMP, > PRIMARY KEY (`category_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > > insert into category values > (4, 'Hardware - Monitor', 2, '2004-10-12 10:50:01'), > (5, 'Hardware - Printer', 2, '2004-10-12 10:50:02'), > (6, 'Hardware - Terminal', 2, '2004-10-12 10:50:02'), > (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); > > Regards, > > Frank > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] newbie - postgresql or mysql
On Wed, Aug 31, 2005 at 03:17:29PM -0400, Frank wrote: > Thanks for clearing up some confusion. > > >>Look in the contrib/mysql directory in the source file (or install the > contrib packages for your system, assuming they come with that contrib > package<< > > I do not have that, where can I download it? It's part of the PostgreSQL source code, which you can download from the website. > This fails to insert records Yes, the values (), (), () syntax isn't supported yet (I think it's slated for 8.2). In the meantime you'll need to convert either to multiple insert statements (which you'll want to wrap in a BEGIN; COMMIT;) or better yet a copy statement. But really what you want to do is use the migration tools that are out there... > >> insert into category values > >> (4, 'Hardware - Monitor', 2, '2004-10-12 10:50:01'), > >> (5, 'Hardware - Printer', 2, '2004-10-12 10:50:02'), > >> (6, 'Hardware - Terminal', 2, '2004-10-12 10:50:02'), > >> (7, 'Hardware - PC Laptop', 2, '2004-10-12 10:50:02'), > >> (9, 'Hardware - Misc.', 1, '2004-10-12 10:51:00'), > >> (10, 'Hardware - PC Desktop', 2, '2004-10-12 10:50:03'), > >> (11, 'Software - PC', 2, '2004-10-12 10:50:03'), > >> (13, 'Software - Network', 2, '2004-10-12 10:50:04'), > >> (14, 'Software - Midrange, AS/400', 2, '2004-10-12 10:50:04'), > >> (15, 'Software - Server', 2, '2004-10-12 10:50:04'), > >> (16, 'Hardware - Wyse Terminal', 2, '2004-10-12 10:50:05'); > > Regards, > > Frank > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Shared disk storage
On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > On Mon, Sep 05, 2005 at 12:20:24 +0300, > Peter Nixon <[EMAIL PROTECTED]> wrote: > > Hi List > > > > Does anyone have any comments, HOWTOs and experience running multiple > > Postgres servers with a shared disk (SAN) in a Hot standby configuration? > > > > Can someone please point me in the direction of any docs on this subject? > > Be sure to have some failsafe to prevent two servers from running at the > same time on the same data. If that ever happens your database will be hosed. I thought PostgreSQL already had such a safeguard? Or is it only against starting two backends against the same PGDATA on the same machine? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Debug plpgSQL stored procedures
On Mon, Sep 05, 2005 at 05:17:41PM -0400, Daniel Morgan wrote: > Richard Huxton wrote: > > >Daniel Morgan wrote: > > > >>Does PostgreSQL provide a way to step-debug into plpgSQL stored > >>procedures? > > > It is a shame. PostgreSQL is really rocking these days. Especially > with 8.0 on Windows. I was really impressed how far it has come since > the 6.x days running on Cygwin. 3rd hit on google: http://www.sqlmanager.net/products/postgresql/manager/documentation/hs20160.html -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Shared disk storage
On Tue, Sep 06, 2005 at 02:58:52PM -0500, Bruno Wolff III wrote: > On Tue, Sep 06, 2005 at 13:47:42 -0500, > "Jim C. Nasby" <[EMAIL PROTECTED]> wrote: > > On Tue, Sep 06, 2005 at 10:13:33AM -0500, Bruno Wolff III wrote: > > > On Mon, Sep 05, 2005 at 12:20:24 +0300, > > > Peter Nixon <[EMAIL PROTECTED]> wrote: > > > > Hi List > > > > > > > > Does anyone have any comments, HOWTOs and experience running multiple > > > > Postgres servers with a shared disk (SAN) in a Hot standby > > > > configuration? > > > > > > > > Can someone please point me in the direction of any docs on this > > > > subject? > > > > > > Be sure to have some failsafe to prevent two servers from running at the > > > same time on the same data. If that ever happens your database will be > > > hosed. > > > > I thought PostgreSQL already had such a safeguard? Or is it only against > > starting two backends against the same PGDATA on the same machine? > > Yes, but it is more likely to have problems when there are two machines > involved. One is that the file may not be on the cross mounted file > system (on FC4 it is in /var/run) and even if it is on the cross mounted > file system, there is a good chance the lock file will appear to be stale > because the process id is for the other machine. I am not sure if there are > other gotchas, but you definitely want to be careful, since a mistake is > going to defeat the purpose of having the hot spares. Maybe it would be better to keep this in PGDATA (or even a duplicate copy). Holding a write lock on the file should also help ensure that you can tell if it's stale or not. I realize this probably still isn't perfect, but it's probably better than forcing users to find an external means of locking out the other backend. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to write jobs in postgresql
On Tue, Sep 06, 2005 at 04:29:31PM -0500, Guy Rouillier wrote: > Roman Neuhauser wrote: > > # [EMAIL PROTECTED] / 2005-08-15 20:25:20 -0500: > >> On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: > >>> chiranjeevi.i wrote: > >>>> Hi Team Members, > >>>> > >>>> Is it possible to write jobs in postgresql & if possible how > >>>> should I write .please help me. > >>> > >>> See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's > >>> in the planning stages. > >> > >> Actually, it's currently in the going nowhere stage since no one's > >> expressed any interest in it. Anyone who's interested is encouraged > >> to join the mailing list and post what they'd like to see from the > >> project. > > > > What's the advantage over system-native (cron etc) means? > > Search the archives, you'll find numerous discussions on this topic, > including the one that prompted Jim to create the project. As of now, > the project is pre-concept stage, making it impossible to identify its > advantages. One possible advantage would be recording job schedules in > the database where they can be easily managed, but that's small. A > bigger advantage can be seen in the approach that Oracle takes, where > authentication happens when the job is created. So you don't need to > provide credentials at run time, which in the case of cron jobs would > mean putting passwords into shell scripts. As Guy points out, this is all in a very formative stage right now (although someone is supposed to be sending me some code), but here's some other advantages: This would be platform-independant, which is important now that we support windows natively. The interface would be in SQL (probably a set of functions), making it much easier to control programatically. Scheduling modes that are either difficult or impossible to do with cron become available, such as sub-minute scheduling (ie: every 30 seconds), running something at server start-up/shut-down, running something based on a notify, etc. I encourage anyone who's interested in this to join the mailing list at http://lists.pgfoundry.org/mailman/listinfo/pgjob-devel -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Shared disk storage
On Wed, Sep 07, 2005 at 12:19:19AM -0400, Tom Lane wrote: > Having said that, I'm not sure I believe in filesystem locks as doing > much to improve security in the case of multiple hosts attached to a SAN > filesystem. Does the locking work at all across hosts, and if it does, > does the lock get released reasonably promptly if the owning host > crashes? This seems like a there's-no-free-lunch situation. The way I see it, it will work fine for some setups, and not work for others. That means it won't help everyone, but it will help some. ISTM like it would be pretty easy to do, so why not help those who could make use of it? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] RAID0 and pg_xlog
On Wed, Sep 07, 2005 at 12:47:43PM -0700, Qingqing Zhuo wrote: > Xlog will be the only believable data if your system crashed. So it is a > dangerous practice to put xlog stuff in RAID0. No more or less so than putting your main database on RAID0. If any drive fails, you lose everything. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] RAID0 and pg_xlog
On Wed, Sep 07, 2005 at 01:02:18PM -0500, Scott Marlowe wrote: > On Wed, 2005-09-07 at 12:40, Jim C. Nasby wrote: > > On Wed, Sep 07, 2005 at 12:47:43PM -0700, Qingqing Zhuo wrote: > > > Xlog will be the only believable data if your system crashed. So it is a > > > dangerous practice to put xlog stuff in RAID0. > > > > No more or less so than putting your main database on RAID0. If any > > drive fails, you lose everything. > > Sounds like a good place to have replication. If you used syncronous replication, maybe. Otherwise failure of any drive means you just lost data. And remember that the more drives you have in your array the more likely you'll have a failure in a given time period. Basically, if you can afford to setup replication on 2 machines with RAID0 you can afford to setup RAID10 on one machine, which will usually be a better bet. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to write jobs in postgresql
On Thu, Sep 08, 2005 at 10:27:59AM -0700, Chris Travers wrote: > Karsten Hilbert wrote: > > >>3. An integrated way of logging what ran when (rather than either > >>stuffing logging code into each cron job or rummaging thru > >>cron logs) > >> > >> > >Cron can log to syslog. > > > >Karsten > > > > > And your cron scripts could log to your database log tables if that is > what you were getting at... True and true, but both of those require more work to setup. I'm not even sure if you can log only specific cronjobs to syslog. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] RAID0 and pg_xlog
On Fri, Sep 09, 2005 at 09:43:56AM -0500, Scott Marlowe wrote: > On Wed, 2005-09-07 at 16:15, Jim C. Nasby wrote: > > On Wed, Sep 07, 2005 at 01:02:18PM -0500, Scott Marlowe wrote: > > > On Wed, 2005-09-07 at 12:40, Jim C. Nasby wrote: > > > > On Wed, Sep 07, 2005 at 12:47:43PM -0700, Qingqing Zhuo wrote: > > > > > Xlog will be the only believable data if your system crashed. So it > > > > > is a dangerous practice to put xlog stuff in RAID0. > > > > > > > > No more or less so than putting your main database on RAID0. If any > > > > drive fails, you lose everything. > > > > > > Sounds like a good place to have replication. > > > > If you used syncronous replication, maybe. Otherwise failure of any > > drive means you just lost data. And remember that the more drives you > > have in your array the more likely you'll have a failure in a given > > time period. > > > > Basically, if you can afford to setup replication on 2 machines with > > RAID0 you can afford to setup RAID10 on one machine, which will usually > > be a better bet. > > Yeah, I was thinking pgpool here. pgpool is a connection pool; it has (almost) nothing to do with replication. It certainly doesn't work to provide any kind of data security on a RAID0 setup. I'm not arguing against anything people have suggested, only pointing out that if you're using RAID0 your data is not safe against a drive failure, except possible using pgcluster (some would argue that statement-based replication isn't as reliable as log-based). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cost based SELECT/UPDATE
On Thu, Sep 08, 2005 at 06:45:33AM +0400, Leonid Safronie wrote: > Hi, ppl > > Is there any way to do SELECTs with different priorities? > > Once a month I need to do some complex reports on table with over 7 > billion rows, which implies several nested SELECTS and grouping (query > runs over 20 minutes on P4/2.4GHz). Concurrently, there are over 50 > processes updating tables in the same database, including table being > SELECTed to do monthly report. The issue is that response time for > these 50 processes is very important unlike for report generation, and > time spent by these processes while report running is unacceptable for > my production environment (response time grows from 1-3 seconds up to > 1-2 minutes). > > Is there any way to give different priorities to different > queries/transactions, as it's done for VACUUMing (vacuum_cost_* > options in config file)? You can try running the select from a process that's niced via the OS; some OS's will take nice into account when scheduling IO. But there is currently no mechanism to provide this capability from within PostgreSQL. There should probably be a TODO for this, since it's something that's asked about fairly often. * Provide a means for individual queries to be run at a lower priority While nice allows this for CPU-bound queries, it generally doesn't work for I/O bound queries. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Support for Limit in Update, Insert...
On Thu, Sep 08, 2005 at 10:49:25PM -0400, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > This has been discussed before, and rejected. Please see the archives. > > For SELECT, both LIMIT and OFFSET are only well-defined in the presence > of an ORDER BY clause. (One could argue that we should reject them when > no ORDER BY, but given that the database isn't getting changed as a side > effect, that's probably too anal-retentive. When the database *is* > going to be changed, however, I for one like well-defined results.) > > If this proposal included adding an ORDER BY to UPDATE/DELETE, then it > would at least be logically consistent. I have not seen the use-case > for it though. In any case you can usually get the equivalent result > with something like > > UPDATE foo SET ... > WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...); BTW, this is a case where using ctid would make sense, though you can't: decibel=# update rrs set parent=parent+1 where ctid in (select ctid from rrs order by rrs_id limit 1); ERROR: could not identify an ordering operator for type tid HINT: Use an explicit ordering operator or modify the query. ERROR: could not identify an ordering operator for type tid HINT: Use an explicit ordering operator or modify the query. decibel=# -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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
