[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

[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?

[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

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 versions

[GENERAL] INSERT OR UPDATE

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

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 ---(end of broadcast

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 [EMAIL PROTECTED] It assumes independence

[GENERAL] Index use with left join

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] 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

Re: [GENERAL] truncate/create slowness

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 broadcast

[GENERAL] Clustering system tables

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 off all lists at once with the unregister

Re: [GENERAL] postgresql 8.0 advantages

, 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)--- TIP 3

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 failover. _There_ lies the one change

Re: [GENERAL] pgpool 2.5b2 released

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 later, if necessary. But if the failover doesn't happen promptly, we might as well rebuild the whole cluster. Julian Scarfe

Re: [GENERAL] pgpool 2.5b2 released

. 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 failover and promote the slave to master. Julian Scarfe

Re: [GENERAL] pgpool 2.5b2 released

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 ---(end of broadcast)--- TIP 9

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 PROTECTED]

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 or seconds issue

[GENERAL] Index bloat in 7.2

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

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

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 Scarfe ---(end

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 in when

Re: [GENERAL] Simulating an outer join

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

[GENERAL] Making points into paths

look too hard to write an external function that appends a point to a path, but am missing something obvious? Thanks Julian Scarfe