[GENERAL] Can we not give tyrannical pedants control of #postgresql?

ven here, but it appears #postgresql encourages a particular tier and makes aware of it's rigid hierarchy. I owe alot to #postgresql but not to these particular users, I've perhaps been idle for too long and the channel has change for the worse, well that's not my fault. I leave it

Re: [GENERAL] Lock contention in TransactionIdIsInProgress()

Hi >>>>> "JJ" == Jeff Janes writes: JJ> This is a known issue and is fixed for 9.6 in commit JJ> 8a7d0701814a4e. thanks for the quick reply. Is there a chance this will get into the 9.5 branch as well? Regards, Julian v. Bock -- Julian v. Bock

[GENERAL] Lock contention in TransactionIdIsInProgress()

circumstances. It does no full table scan but uses a few indexes. The returned rows are among the rows that get deleted and inserted repeatedly. Regards, Julian v. Bock -- Julian v. Bock Projektleitung Software-Entwicklung OpenIT GmbH Tel +49 211 239 577-0 In der Steele

Re: [GENERAL] Largest PG database known to man!

On 02/10/13 07:49, Mark Jones wrote: > Hi all, > > We are currently working with a customer who is looking at a database > of between 200-400 TB! They are after any confirmation of PG working > at this size or anywhere near it. > Anyone out there worked on anything like this size in PG please? If >

Re: [GENERAL] Partitioning V schema

large enough to require a dedicated server. This is the question I put to the OP, I would be interested to know. Regards, Julian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitioning V schema

required to be generated on demand dynamically and automatically (which probably isn't the case here). SCHEMAs have other uses, provide a level of security (GRANT) and useful in design when partitioning off blocks of related datasets completely. Regards, Julian On 19/09/13 17:02, Dave Potts

Re: [GENERAL] Best practice on inherited tables

sing INHERIT when I see it mentioned. Regards, Julian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Best practice on inherited tables

differentiate for when it is actually useful. It can bring up debate involving OOP practices transfered over into general database design. For which I quash with: database design != application design How you store data should be irrelevant to application interfaces (API's). People do it and s

Re: [GENERAL] How to convert numbers into words in postgresql

On 15/05/13 08:27, Merlin Moncure wrote: > On Tue, May 14, 2013 at 5:24 PM, Adrian Klaver > wrote: >> On 05/14/2013 03:17 PM, John R Pierce wrote: >>> >>> On 5/14/2013 2:57 PM, Alvaro Herrera wrote: Jashaswee escribió: > >> i want to convert numbers into words in postgresql.is t

Re: [GENERAL] Design advice requested

On 09/05/13 17:42, Johann Spies wrote: > Hallo Julian, > > Thanks for your reply. > > Firstly, don't worry too much about speed in the design phase, > there may > be differences of opinion here, but mine is that even with database > design the f

Re: [GENERAL] Does it make sense to break a large query into separate functions?

ld take time, so I decided to ask for > more educated input than mine. > > Best regards > Seref > Hi Seref, The code generated sql queries isn't giving you much to work with (or a choice). However I suspect its doing its best dealing with this data structure (relationship

Re: [GENERAL] Design advice requested

suggest without actually looking at a sample of the data (problem) you are dealing with. Its a matter of breaking it down into logical steps and having some fun. Regards, Julian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?

On 01/05/13 12:36, Yang Zhang wrote: > On Tue, Apr 30, 2013 at 7:21 PM, Julian Glass > wrote: >> On 01/05/13 09:55, Yang Zhang wrote: >>> I would intuit that it's fine, but I just want to make sure there are >>> no gotchas from a recovery point of view

Re: [GENERAL] how to create materialized view in postgresql 8.3

On 10/04/13 23:33, Vincent Veyron wrote: > Le lundi 08 avril 2013 à 09:36 -0700, Mike Christensen a écrit : >> This is the number one requested feature on Uservoice: >> >> >> http://postgresql.uservoice.com/forums/21853-general/suggestions/247548-materialized-views >> >> > > I find this rather sur

Re: [GENERAL] Using varchar primary keys.

On 03/04/13 06:37, Merlin Moncure wrote: > On Tue, Apr 2, 2013 at 2:01 PM, Samantha Atkins wrote: >> Natural keys are in user data space. Thus they are not guaranteed invariant >> and therefore cannot serve as persistent identity. Can't find Samantha's original post. I agree but done right they

Re: [GENERAL] Using varchar primary keys.

On 02/04/13 06:35, jesusthefrog wrote: > On the topic of 'natural' versus 'synthetic' primary keys, I am > generally in the camp that an extra ID field won't cost you too much, > and while one may not need it for a simple table (i.e. id, name) one > might add any number of columns later, and you'll

Re: [GENERAL] Using varchar primary keys.

On 01/04/13 12:19, Modulok wrote: > On 3/31/13, Tim Uckun wrote: >> Consider the following scenario. >> >> I have a typical tagging structure. There is a table called tags, there is >> a table called taggings. The taggings table acts as a many to many join >> table between the taggers and the tags

Re: [GENERAL] Money casting too liberal?

On 31/03/13 21:57, Gavan Schneider wrote: > On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: > Basically if MONEY is to be a useful tool it should really handle money > matters in a way that makes accountants happy. If it can't do that then > nobody is going to bother using it for serious work since

Re: [GENERAL] Money casting too liberal?

On 29/03/13 23:32, Gavan Schneider wrote: > Some people wrote: > >> ... Hmm... This should optionally apply to time. >> ... for anything that really matters, I'll work with UTC. >> > Is there a Godwin's law > equivalent for when our conversations end up

Re: [GENERAL] table logging

. So this all depends on the requirements of your app. It can be fair to say that all kinds of logging leads to bloat that you'll have to deal with later. (partitioning, purging etc). Julian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to don't update sequence on rollback of a transaction

s what its intended to do. Regards, Julian P.S I have heard of people using a sequence in an AFTER trigger to generate consecutive numbering to some success. But anything could happen. On 08/03/12 17:56, Frank Lanitz wrote: Am 02.08.2012 17:15, schrieb Andrew Hastie: Hi Frank, I believe

Re: [GENERAL] Debian upgrade with PostgreSQL

with the bytea format properly (sticking with 8.3 until our new client app is ready). refer to this. http://www.postgresql.org/docs/9.0/static/release-9-0.html#AEN100218 Regards, Julian. Read through On 07/30/12 20:57, Patrick Ernst wrote: Hello, We are running PostgreSQL 8.3 on a Debian

Re: [GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger

r when the row is inserted and the index is updated. You can try the insert and catch the unique violation in a loop (see http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html) although that won't work with a BEFORE trigger. Regards, Julian -- Julian v. Bock

[GENERAL] Restoring default privileges on a table

This message has been digitally signed by the sender. _GENERAL__Restoring_default_privileges_on_a_table.eml Description: Binary data - Hi-Tech Gears Ltd, Gurgaon, India -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you

[GENERAL] Restoring default privileges on a table

ly *remove* privileges as well. The important thing is: there should be a way to restore default privileges on a database object. What do you think? -Julian signature.asc Description: This is a digitally signed message part.

[GENERAL] PostgreSQL performance

Hi Are there any performance tuning tools for PostgreSQL, besides explain. Any system management views to find out missing indexes or indexes that are not being used. Thanks Julian Valoo SQL Database Administrator Corporate and Transactional Banking IT BankCity e-mail

Re: [GENERAL] Efficient Way to Merge Two Large Tables

able1.row_id = table2.row_id and table1.row_id >= 0e6 and table1.row_id < 1e6 and table2.row_id >= 0e6 and table2.row_id < 1e6; for a moving row_id window. This has helped me in the past with a similar scenario (where both tables were partitioned by the PK, but it would pr

Re: [GENERAL] "--variable foo=bar" vs. "\set foo quux" in ~/.psqlrc

Tom Lane wrote: > Julian Mehnle writes: > > Can anyone confirm that --variable command-line options are evaluated > > before .psqlrc is read and executed? If so, does anyone know the > > rationale for that? It seems counterintuitive to me, as it makes > > overriding

[GENERAL] "--variable foo=bar" vs. "\set foo quux" in ~/.psqlrc

es anyone know the rationale for that? It seems counterintuitive to me, as it makes overriding variables from the command-line impossible. If there is consensus that evaluating --variable options *after* .psqlrc was read and executed is an acceptable change, or that a --variable-late option should be

Re: [GENERAL] regexp_matches() quantified-capturing-parentheses oddity

(?:foo)*(foo) if you want the last one I would use the ((foo+)) form, but of course it doesn't return all of the subexpression matches as separate elements, which was the point of my exercise. For what it's worth, I'm now using a "FOR ... IN SELECT regexp_matches(...) LOOP" construct in a custom plpgsql function. -Julian signature.asc Description: This is a digitally signed message part.

[GENERAL] regexp_matches() quantified-capturing-parentheses oddity

zip', '([...@.]|[...@.]+){1,3}', 'g'); {foo} {.} {p} What's going on here?? Regards, -Julian Mehnle signature.asc Description: This is a digitally signed message part.

Re: [GENERAL] support for embedded db and a clustered index?

hoping pgsql has a small memory footprint) and I also do NOT mean to ask if pgsql supports embedded sql. Any help would be greatly appreciated. Thanks, Julian

[GENERAL] Casting composite types

provided foo is not empty. # select foo(foobaz) from foobaz; foo --- (8,9) (1 row) Am I missing an obvious trick or syntax here for such an 'autocast'? Or have I just been corrupted by Perl to take types too lightly? Thanks Julian -- Sent via pgsql-general mailing list (pg

[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

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)

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

ot;09:05:48 UCT" UTC works, of course: => select '09:05:48 UTC'::time; time -- 09:05:48 (1 row) Is this behaviour expected? Desirable? Thanks Julian ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[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] PROBLEM: Function does not exist

Hello Michael, yes, you have right. Solution is cast it to smallint. Thank you for your advice, with best regards, Julian Legeny Thursday, June 23, 2005, 2:48:17 AM, you wrote: MF> On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote: >> >> CREATE OR R

[GENERAL] PROBLEM: Function does not exist

ot;, integer, integer, integer, integer, integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. Can you advice me what can be a problem? -- Thank you for your answer, best regards, Julian mailto:[EMAIL P

Re: [GENERAL] ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)

Hello, that's what I was looking for. Thanks to all for advices, with best regards, Julian Legeny Tuesday, May 10, 2005, 12:14:38 PM, you wrote: RS> SELECT * FROM MY_TABLE ORDER BY lower(NAME), NAME RS> The second NAME is to ensure that AAA comes before aaa, otherwis

[GENERAL] ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)

BBB CCC aaa bbb ccc But I would like to sort all data as following: NAME --- AAA aaa BBB bbb CCC ccc How can I write sql command (or set up ORDER BY options) for selecting that? Thank you in advance for answer, best regards, Julian

Re: [GENERAL] 'prepare' is not quite schema-safe

Vlad [EMAIL PROTECTED] wrote: > ok, since there is no gurantee that server-side prepared query is > still active, pergaps postgresql interface library provide way to > check if a prepared before query still alive prior runing exec, so > that dbd::pg driver can make sure it's still there, right befo

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

of rows using an approximate filter (e.g. bounding box for the geometrical case) with an index and then use a second, exact but computationally expensive filter to keep only those rows that I really want. Any general suggestions for workarounds? Julian ---(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] 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 --

[GENERAL] Select after insert to the unique column

hat SELECT COUNT(*), there is error occured again: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block How can I solve this? Thank you in advance, with best regards, Julian Legeny All th

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

estore major version upgrade (which we'll do next time we can take the system out for long enough) will avoid the issue. Julian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

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

abase would be preferable in the short term. I think the current default of these casts being explicit only is sensible. Thanks Julian ---(end of broadcast)--- TIP 8: explain analyze is your friend

[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

[GENERAL] VACUUM ANALYZE question - PostgreSQL performance tests

(from a DB client) query VACUUM ANALYZE one more time (during retrieving of pages), the performance is much better. Is there also neccessary to call VACUUM ANALYZE also for getting of better performance for select query? Thank you for your answer, with best regards, Julian Legeny

Re: [GENERAL] Best practices for migrating a development database

Hi, We manage a number of high-volume databases that require 24/7 uptime (pretty much) and deal with this problem a lot. The solution we employ is that once a database is in production the only way to alter the database is using a change script that deals with any data migration issues as well

Re: [GENERAL] tablespaces a priority for 7.5?

speaking as someone currently migrating enterprise stuff to postgres point-in-time is definitely the biggest issue. this is the main thing we are having to look reproducing using some form of replication to an alternative server. -Original Message- From: Rick Gigger [mailto:[EMAIL

Re: [GENERAL] Modelling Oracle Sequences

Hi Pavel, Thanks for that. I hadn't realised there was support for proper sequences. Usual asking before fully researching. Thanks, Julian. -Original Message- From: Pavel Stehule [mailto:[EMAIL PROTECTED] Sent: 21 November 2003 09:42 To: Julian North Cc: '[EMAIL PROTECTED

[GENERAL] Modelling Oracle Sequences

to open an UPDATE cursor, which places a lock on the row in the sequence table. I then update, release and return. Has anyone done something similar or have any pointers on how to do this safely, securely without an update cursor? Any infor appreciated. Cheers, Julian

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