[GENERAL] Casting composite types

Using 8.1 # create table foo (a integer, b integer); # create table baz (b integer, c integer); # insert into foo values (8,9); # insert into baz values (9,1); # select * from foo; a | b ---+--- 8 | 9 (1 row) # select * from baz; b | c ---+--- 9 | 1 (1 row) # create view foobaz as select foo.

[GENERAL] last analyze time in 8.1?

Is there any way of getting at the last time a table was analyzed (by autovacuum) in 8.1 or is that only recorded (in pg_stat_*_tables) since 8.2? TIA Julian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? h

Re: [GENERAL] Timezones -- what comes out does not go in?

From: "Tom Lane" <[EMAIL PROTECTED]> This works in CVS HEAD, thanks to some hard work by Joachim Wieland. One of these days I'll find an issue *before* you folks have patched it. :-) Thanks Julian ---(end of broadcast)--- TIP 9: In version

[GENERAL] Timezones -- what comes out does not go in?

The timezone on our boxes is set to Etc/UCT (possibly a distro default). (psql 8.1.4) => select to_char(current_timestamp, 'HH24:MI:SS TZ'); to_char -- 09:05:48 UCT (1 row) => select '09:05:48 UCT'::time; ERROR: invalid input syntax for type time: "09:05:48 UCT" UTC works, of c

[GENERAL] INSERT OR UPDATE

, d1, d2 rows are not updated to match db1. What I'd really like is to be able to do: INSERT OR UPDATE INTO a VALUES ('a2', 'b2', 'c2'); INSERT OR UPDATE INTO a VALUES ('a1', 'b1 new', 'c1'); INSERT OR UPDATE INTO d VALUES ('d3', 'a1', 'e3', 'f3'); INSERT OR UPDATE INTO d VALUES ('d1', 'a1', 'e1 new', 'f1'); INSERT OR UPDATE INTO d VALUES ('d2', 'a2', 'e2', 'f2 new'); so that the rest of the row is treated as an UPDATE if the primary key is a duplicate. Of course I can write something at the application level to examine each row and take appropriate action. But it feels like this may be a commonly encountered problem for which there may be a database-level solution. Am I missing something obvious? Thanks Julian Scarfe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] SQL Question

LastUpdate from produpdate p1 where LastUpdate <> ( select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId ) order by ProdId , LastUpdate desc ; but there may be a much more efficient way of getting the nth result in general. Julian Scarfe --

Re: [GENERAL] Index use with left join

"Julian Scarfe" <[EMAIL PROTECTED]> writes: Does the planner "realise" that the intersection, Query 6, will still return 150 rows, or does it assume independence of the filters in some way and estimate 20,000*(150/20,000)*(396/20,000)? From: "Tom Lane" <[EM

Re: [GENERAL] Index use with left join

From: "Tom Lane" <[EMAIL PROTECTED]> The problem is that it's underestimating the number of rows pulled from the n table (1 vs actual 150), which makes a simple nestloop join look like the way to go. That error comes from the fact that we don't really have any statistical estimation for geometric

[GENERAL] Index use with left join

#x27;(0.017,0.908),(-0.032,0.873)'::box) -> Sort (cost=1283.17..1308.44 rows=10105 width=25) (actual time=165.520..166.348 rows=1391 loops=1) Sort Key: (a.ident)::text -> Seq Scan on a (cost=0.00..611.05 rows=10105 width=25) (actual time=0.042..69.560 rows=10105 loops=1) Total runtime: 182.275 ms What's happening here, please? How am I misleading the planner? Is it because the index is rtree? Yes, I should consider PostGIS for spatial stuff, but I've got what I've got :-). TIA Julian Scarfe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] truncate/create slowness

ing when a pg_dumpall/restore is likely to be faster than a vacuum full? Or perhaps more straightforwardly, how would you expect the time required for a vacuum full to scale with pages used and rows in the table? Thanks Julian Scarfe ---(end of

[GENERAL] Clustering system tables

it would be vastly quicker than a VACUUM FULL. But CLUSTER responds with: ERROR: "pg_attribute" is a system catalog Is VACUUM FULL my only option to compact the table? Julian Scarfe ---(end of broadcast)--- TIP 2: you can get

Re: [GENERAL] postgresql 8.0 advantages

query than prior releases did, because we fixed some issues in the GEQO planner module. Could you give an example or two of the sorts of queries for which performance is improved under 8.0 compared with 7.4, please Tom? Thanks Julian Scarfe ---(end of broadcast)---

Re: [GENERAL] pgpool 2.5b2 released

> After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] ("Julian Scarfe") belched out: > > So all I'm looking for is a way for pgpool to shout if it detects a > > failure. That could initiate the investigation of the other > > criteria required for

Re: [GENERAL] pgpool 2.5b2 released

datastream cannot be written to the database, the database becomes worse than useless quite rapidly. We need the ability to switchover or failover to another node as master as soon as possible, to allow the datastream to be written to the other node. We'll rebuild the "failed" master

Re: [GENERAL] pgpool 2.5b2 released

he capabilty to perform periodical health checking to > PostgreSQL. Since pgpool has this capability, how about including a hook that allows a script to be run when pgpool detects a problem with the master? That would allow action to be taken to investigate further and, if required, switchover or

Re: [GENERAL] pgpool 2.5b2 released

ol. Sounds good! Does it attempt any interaction with Slony when it detects a failure of the master? It would seem a pity to have pgpool watching the pair to detect failure but having to have a separate watcher process to tell Slony to failover. Julian Scarfe --

Re: [GENERAL] Index bloat in 7.2

On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote: b) Only a dump-restore major version upgrade (which we'll do next time we can take the system out for long enough) will avoid the issue. On 6 Dec 2004, at 16:18, Alvaro Herrera wrote: "Long enough" could be a minutes o

Re: [GENERAL] Index bloat in 7.2

From: "Christopher Browne" <[EMAIL PROTECTED]> > The "empty pages not reclaimed" problem is something that did indeed > get fixed in the post-7.2 days. I _think_ it was 7.4, but it might > have been 7.3. > In short, 7.4.x is indeed a good resolution to your issue. From: "Tom Lane" <[EMAIL PROTE

[GENERAL] Index bloat in 7.2

t got fixed, or have I got potentially more serious problems? Thanks Julian Scarfe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] How can I change a cast from explicit only to implicit?

From: "Tom Lane" <[EMAIL PROTECTED]> > > So how can I force a built-in cast to become implicit? > > If you're intent on doing that, you can change its entry in pg_cast. > But I think you'd be far better off to fix your application. Implicit > casts across type categories have a habit of kicking i

[GENERAL] How can I change a cast from explicit only to implicit?

cit; ERROR: cast from type text to type float8 already existsoat8(text) as implicit; template1=# drop cast (text as float8); ERROR: cannot drop cast from text to double precision because it is required by the database system So how can I force a built-in cast to become implicit? Thanks Julian S

Re: [GENERAL] Simulating an outer join

etter position than me to judge whether that's the case in pgsql! But it might be worth a test. Julian Scarfe

Re: [GENERAL] Simulating an outer join

around for OUTER JOINs in 'SQL for Smarties'. Well in fact he uses (SELECT * FROM table2 WHERE table1.key = table2.key) as the subquery, but I presume that's an insignificant difference. Julian Scarfe

[GENERAL] Making points into paths

ons. It doesn't look too hard to write an external function that appends a point to a path, but am missing something obvious? Thanks Julian Scarfe