Re: [GENERAL] Converting time interval to double precision of time unit

') Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4bb236cf10412084085775! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Why index occupy less amount of space than the table with same structure.

bytes..which causing the table to hold what index is not and the space occupied is exactly half of the table in indexes. Can you explain a bit on this. I'm pretty sure the documentation explains this better than I can. Alban Hertroys -- Screwing up is an excellent way to attach something

Re: [GENERAL] Why index occupy less amount of space than the table with same structure.

on this. And what exactly the postgres architecture treat on Index table and Ordinary table. For one thing: The table holds information regarding to which transactions each row is visible (the xid) whereas the index does not. Alban Hertroys -- Screwing up is an excellent way to attach

Re: [GENERAL] How to perform text merge

to address a person more politely? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4baf9e7810416492686854! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

Re: [GENERAL] Daylight savings time confusion

another location than they usually do, for example while at a conference in a different country. If you leave determining the timezone up to them you can't ever be wrong ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM

Re: [GENERAL] update ... set ... subquery

at or near in). thks, jzs Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ba1607e1041183665! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Fwd: [GENERAL] postgresql 8.2 startup script

We solved this off-list. Begin forwarded message: From: Aleksandar Sosic alex.so...@gmail.com Date: 7 March 2010 23:39:27 GMT+01:00 To: Alban Hertroys dal...@solfertje.student.utwente.nl Subject: Re: [GENERAL] postgresql 8.2 startup script On Sun, Mar 7, 2010 at 11:31 PM, Alban Hertroys

Re: [GENERAL] XML Index again

WHERE array_upper((xpath('/AttributeList/Attributes/Attribute[Name=x]', external_attributes)),1) 0 On Sun, Mar 7, 2010 at 1:11 PM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: ... You seem to want to test for the existence of nodes with a specific name

Re: [GENERAL] XML Index again

mind around it. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b94e083296929244758856! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] XML Index again

a try as I outlined in a previous message. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4b94ef13296926894712957! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] XML Index again

is that you need to use the expression you indexed in your where clause, or the database has no idea you mean something similar as to what you indexed. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b9389db296924445911763

Re: [GENERAL] postgresql 8.2 startup script

/rc.conf? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:1036,4b93fde9296921763426367! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] For Loop using row_data to increase performance

template_product.template_article_name = p.template_article_name; Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b91809f296922908710608! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Alternative to UPDATE (As COPY to INSERT)

Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b83c7c410447773417439! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] pg_dump new version

this? I'm not familiar with RPM packages (or any other Linux package manager), but don't they have a separate package with the client applications? My OS of choice does. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b83c85910441343920892! -- Sent

Re: [GENERAL] Sorting performance vs. MySQL

, which will obviously be quite slow. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b82d18510442035320951! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Setting a table to be ignored by autovacuum

PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit (1 row) Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b7e74ef10441772699480! -- Sent

Re: [GENERAL] Getting number of affected row after performing update

convenient for your case than counting actual rows. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b7e795110442010528220! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

to reduce traffic. Do you see this problem with larger result sets (say 10k rows)? Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b7a5d9710441627593049! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

simple test failed. From your description it turns out dblink isn't involved yet, so it can't cause the issue you were asking about. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b7a71b410441921015512! -- Sent via

Re: [GENERAL] Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

(GCC) 4.2.1 (Apple Inc. build 5646), 64-bit !DSPAM:737,4b7a925f10448503891907! ret_next_test.sql Description: Binary data Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b7a925f10448503891907! -- Sent via pgsql

[GENERAL] Handling Numeric Datums in C

around for dealing with numerics? The stuff in the documentation (http://www.postgresql.org/docs/8.4/interactive/xfunc-c.html) conveniently omits numerics, so I had to dig through doxygen to get as far as I am now... Regards, Alban Hertroys -- Screwing up is the best way to attach something

[GENERAL] Fwd: Handling Numeric Datums in C

/docs/8.4/interactive/xfunc-c.html) conveniently omits numerics, so I had to dig through doxygen to get as far as I am now... Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b74034810441727621217! -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] need clean way to copy col vals from one rec to another

'sue', joe.company, joe.job FROM foo AS joe WHERE joe.name = 'joe'; Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b72e2dd10446151245148! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Literals in foreign key definitions

On 4 Feb 2010, at 20:34, Tim Landscheidt wrote: Alban Hertroys dal...@solfertje.student.utwente.nl wrote: [...] Now the intent here is to restrict foreign keys referencing the base class to unitclass records that describe a baseclass and to restrict foreign keys referencing a derived

Re: [GENERAL] Query to find list of dates between two dates

() to do that as well. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b6c070b10441449311484! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] Literals in foreign key definitions

think it would be nice if the above syntax could be made to work. Or is this already in 8.4 or 8.5 or is this a can of worms? Does the SQL spec disallow it? Cheers, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM

Re: [GENERAL] Is it necessary to have index for child table in following case?

it, serials generate unique numbers (unless they wrap around when they run out of numbers, but you control whether they're allowed to and you can design them large enough that it won't happen). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest

Re: [GENERAL] Is this the warning message I should pay attention on it, during table partition

higher. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b6824cc10441094912516! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Problem after installing triggering function

here obviously doesn't have any purpose other then to show what's going on, so it's hard to advise how to work around this problem. You could probably solve your situation by creating a trigger on each child table, it depends on what needs to be done. Alban Hertroys -- If you can't see the forest

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

http://www.postgresql.org/docs/8.4/interactive/indexes-multicolumn.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b62d2e810751877882363! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

. Advertiser_id is probably a foreign key to another table, so it's not unique by itself and they added the day column to the primary key to make it unique - it's some kind of summary table with a resolution of one day per advertiser, so those together are unique. Alban Hertroys -- If you can't see

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html: All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited. Alban Hertroys -- If you can't see the forest

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

|| ',' || quote_literal(NEW.v) || ')'; RETURN NULL; END;$BODY$ LANGUAGE plpgsql; Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b608af610606065868549! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Primary Key Increment Doesn't Seem Correct Under Table Partition

in the child table. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b5ec59910605107914066! -- 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] Recursion in triggers?

/trigger_test.sql:26: NOTICE: OLD.test = 1, NEW.test = 3 UPDATE 1 ROLLBACK Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b5c183b10607129821012! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Optimization on JOIN

measurement_type ON (fk_measurement_type_id = measurement_type_id) WHERE fk_lot_id = 7 Notice that we now use a different column in the WHERE clause, namely measurement.fk_lot_id instead of lot.lot_id. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see

Re: [GENERAL] DDL question

outperform it. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b59f10c10604404016430! -- 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] Index on immutable function call

). Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b5579a310607798915529! -- 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] Index on immutable function call

think you misread his post. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b5579d610602488920658! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Index on immutable function call

out of three does, that still means a sequential scan is probably going to be faster than an index scan. I'm quite sure you would get an index scan if you'd reduce the number of rows that match your query significantly, for example by querying for data1 * 100 this_is... Alban Hertroys

Re: [GENERAL] Index on immutable function call

then a combined index on (data1, this_is_a_long_transformation(data2)) will probably also work and give you the flexibility you need. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b55875b10601514515279! -- Sent via pgsql-general mailing list

Re: [GENERAL] Index on immutable function call

of the statistics the planner tracks are quite useless here. Real data tends to be a lot less random so estimates are usually much better there. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b55902010601090241314! -- Sent via pgsql-general mailing

Re: [GENERAL] postgres external table

/pythonu, pl/perlu, etc) returning TABLE (...), which means this is in fact already possible I think? It's just that nobody's (publicly) thought of doing this so far. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b559f7d10601504612258! -- Sent

Re: [GENERAL] Get Unix timestamp from SQL timestamp through libpq

make sure you specify the format when making the connection. Another option is to add an extra epoch column to your result-row and return that row instead of the original row. You'd have to change the return type to include the extra column of course (see RETURNS TABLE in the docs). Alban

Re: [GENERAL] Rows missing from table despite FK constraint

of corruption, but not likely in your case. And of course there could be a bug in PG; are you up to date on the minor versions? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b47130010732637119309! -- Sent via

Re: [GENERAL] Rows missing from table despite FK constraint

... Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b47b35810731946694119! -- 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] interesting check constraint behavior

) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b45bc2610731598743439! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] PostgreSQL Write Performance

. Don't forget that at the start the queue table will be empty ;) I recall some of this lists' members wrote up a webpage about how to implement queue-tables reliably. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b431caa10731320433375! -- Sent

Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key

) VALUES (_customer_id, _price); END; $body$; --- On Mon, 1/4/10, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: From: Alban Hertroys dal...@solfertje.student.utwente.nl Subject: Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key To: Yan Cheng Cheok ycch...@yahoo.com Cc

Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key

could achieve this by calling only one statement that I can think of is to wrap this in a stored procedure. Plain SQL doesn't provide any means to do what you want. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b41d7ac9957514533904! -- Sent

Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key

On 4 Jan 2010, at 13:15, A. Kretschmer wrote: In response to Alban Hertroys : On 4 Jan 2010, at 9:53, Yan Cheng Cheok wrote: For example, John place 1.34 priced order. (1) Get Customer_ID from Customer table, where name is John (2) If there are no Customer_ID returned (There is no John

Re: [GENERAL] WEIRD! postmaster: segfault with sub select??!

Postgres is to blame here. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b40e6e69954031810539! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] How to get a list of tables that have a particular column value?

][0-9]*)\.([0-9]{2})[0-9]*', E'\\1.\\2' ) development= select regexp_replace('4.8000', E'([1-9][0-9]*)\.([0-9]{2})[0-9]*' , E'\\1.\\2'); regexp_replace 4.80 (1 row) Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees

Re: [GENERAL] Optimizing data layout for reporting in postgres

a nightly cron-job instead. I suppose I mean to say to use triggers to pre-calculate data for simple cases but to prefer cron jobs for the complicated ones. Debugging complicated triggers can be time-consuming. Regards, Alban Hertroys -- Screwing up is the best way to attach something to the ceiling

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b333e33228059156120885! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [GENERAL] Charset Win1250 on Windows and Ubuntu

TO 'WIN1250' once you've set up your connection. You can even do that between queries if your client encoding requirements change between queries. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b2f51b9228057414011521

Re: [GENERAL] Something like Oracle Forms, but Open Source to use with PostgreSQL?

like this. It uses the Python language, but if that's no problem for you then I heartily recommend it. I understand it's quite popular with Java developers too, apparently they use Django for quick prototyping and translate the result to Java. Alban Hertroys -- Screwing up is the best way

Re: [GENERAL] How to remove non-UTF values from a table?

utf-8! Thanks for any pointers. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b264f49228051716694515! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [GENERAL] Error in crosstab using date_trunc

option is the non-standard \' escaping. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b1b914911734630115167! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription

Re: [GENERAL] How to get RTREE performance from GIST index?

it in a complicate query, it is desirable to run an ANALYZE command on it? I haven't been doing that, because I didn't know. Only if you created an index on it. If you didn't it doesn't matter as in that case you always get a sequential scan, unless I'm mistaken. Alban Hertroys -- Screwing up is the best

Re: [GENERAL] limiting resources to users

other info about the backend, but I don't think there's anything in there that you'd need to know at that point (you got the pid to re-nice already, after all). Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b14df4211731232810455! -- Sent via

Re: [GENERAL] WARNING: worker took too long to start; cancelled on VACCUM ANALYZE

not that, there is a good chance that your AV software installed all kinds of hooks that can subtly change the behaviour of system functions. If that's the case disabling it is not enough, you should try to uninstall it and hope it removes all the hooks. Alban Hertroys -- If you can't see the forest

Re: [GENERAL] date_trunct() and start of week

.) ^^ It seems safe to assume date_trunc() uses the same ISO standard when truncating dates. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b0fb5a211731686815181! -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] Multicolumn index including tsvector.

too. And as Tom already said casting can be a problem with bigints, not only if certain operators aren't defined for comparison between int and bigint, but you'll also see a performance hit if table data you compare to needs to be upcasted to a bigint; joins come to mind. Alban Hertroys

Re: [GENERAL] How to get RTREE performance from GIST index?

of your database if RTree would have still been in the database. It's simply not likely that the difference in index is causing your trouble. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4b09295211736876095208! -- Sent

Re: [GENERAL] How to get RTREE performance from GIST index?

c.srcid; If you expect indexes to work efficiently on temporary tables you should analyse them after filling them to update the planner's statistics on their contents. If you don't you get the default query plan that's often not efficient. Alban Hertroys -- If you can't see the forest

Re: [GENERAL] How to get RTREE performance from GIST index?

On 22 Nov 2009, at 13:19, Clive Page wrote: On 22/11/2009 12:09, Alban Hertroys wrote: If you expect indexes to work efficiently on temporary tables you should analyse them after filling them to update the planner's statistics on their contents. If you don't you get the default query plan

Re: [GENERAL] How to get RTREE performance from GIST index?

value). Insert/Update performance will decrease (there's a function call and an extra calculation after all), but Select performance will probably improve and there's sufficient time for autovacuum to pick up any changes in the data. Alban Hertroys -- If you can't see the forest for the trees

Re: [GENERAL] Fast Search on Encrypted Feild

combined with data from the DB (as it is visible to the transaction) always yields the same result. 2. The same input data always yields the same result. 3. There is no correlation between the input data and the result. Alban Hertroys -- Screwing up is the best way to attach something

Re: [GENERAL] [Fwd: Returning multiple rows in 8.4]

functions are queried as select * from my_func(); If you return a set of records then you'll have to specify it's type the way you mention above. thanks You're welcome. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4af9464211073046225299

Re: [GENERAL] Incremental Backups in postgres

in before the dump has finished writing. As others mentioned, you can also go with a PITR solution, which is probably prettier but is a bit harder to set up. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4af948a011071608518950! -- Sent via

Re: [GENERAL] virtualidx exclusive lock

---+--+--+--+---+ +---+-+---+--+ +---+--+- virtualxid| | | | | 63/10150 | | | | | 63/10150 | 31932 | ExclusiveLock| t Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM

Re: [GENERAL] DataRow Null values Frontend/Backend Protocol TCP/IP

On 2 Nov 2009, at 10:21, Raimon Fernandez wrote: byte 1: 255 HFF byte 2: 255 HFF byte 3: 255 HFF byte 4: 255 HFF - 1020 decimal or Thou shalt not sum the byte-values of a 32-bit number! H 4294967295 but never -1 That is -1. Alban Hertroys -- Screwing up

Re: [GENERAL] DataRow Null values Frontend/Backend Protocol TCP/IP

On 2 Nov 2009, at 11:15, Alban Hertroys wrote: That is -1. Pressed Send too soon, that's only true for signed 32-bit integers of course. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4aeeb49f11071380273732! -- Sent via pgsql-general

Re: [GENERAL] Absolute value of intervals

as there is a timestamp to base your calculations on, but AFAIK you can't see that from within the abs() function implementation. Unless you store that information in the context somehow. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM

Re: [GENERAL] Slow running query with views...how to increase efficiency? with index?

) - Seq Scan on TB (cost=0.00..4.72 rows=1 width=104) (actual time=0.056..0.056 rows=0 loops=1) Filter: ((term2)::text = 'c'::text) Total runtime: 5147.410 ms Alban Hertroys -- Screwing up is the best way

Re: [GENERAL] Slow running query with views...how to increase efficiency? with index?

by in the first subquery of that view can safely be removed I think. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ae823b911071766412181! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Slow running query with views...how to increase efficiency? with index?

On 28 Oct 2009, at 13:42, fox7 wrote: What do you mean for analyze results? http://www.postgresql.org/docs/8.4/interactive/sql-explain.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ae83f5911071064615400

Re: [GENERAL] PostgreSQL driver for Joomla review

have a query SELECT $keyName AS 'id' FROM $table' - That line contains two syntax errors: 'id' (You probably meant id) and $table' (spurious trailing quote). Regards, Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4addf36611682031315238

Re: [GENERAL] How ad an increasing index to a query result?

) Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4adaee3411688629581426! -- 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] Wrong estimation of rows for hash join

of table b, but where do the remaining 2.5 secs come from? As I read it the seq-scans take up the first 2.5s and the actual Hash Join the other 2.5s. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ad86e2511683848115674

Re: [GENERAL] SFPUG: Video from Statistics and Postgres -- How the Planner Sees Your Data Now on Vimeo

to slides with some frequency though, are they available somewhere? Thanks for putting this up. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ad7106611681113316355! -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] How to reduce WAL file Size

. Another common strategy is to use PITR (point in time recovery) for your backups. P.S. Please try to avoid top-posting. Alban Hertroys-3 wrote: What's this? Did my name change suddenly? Or is yahoo-mail a bad mail client? On 8 Oct 2009, at 9:35, Mitesh51 wrote: By setting

Re: [GENERAL] How to reduce WAL file Size

you want to reduce the WAL file size? What problem are you trying to solve? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ace7bd211681560221775! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] is it possible to do an update with a nested select that references the outer update ?

, but if it floats your boat... Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4acb82e311687224899625! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] attempted to lock invisible tuple - PG 8.4.1

(and a serializable isolation level, but you have that). Do you have any triggers that use cursors on the table that the update fails for? One of the statement logs is at http://paste.ubuntu.com/285983/ - I can't see anything unusual going on but it might help diagnose the problem. Alban Hertroys

Re: [GENERAL] do I need a rollback() after commit that fails?

too. But I don't know what Perl DBI does internally when issuing $dbh- commit(), maybe it's taking such things into account already. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4ac356da11681178911724! -- Sent via pgsql-general mailing list

Re: [GENERAL] How should the first step of PostgreSQL implementation should be? (revised)

, in this case ivia. You probably want to connect to the database named postgres that's created by default (at the initdb step). Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4abf320b11688043321471! -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] stored procedure: RETURNS record

deserialize('some string') AS ss (a int, b int); You may need to call it like this though: MYDB=# select * from (SELECT (deserialize(kvp)).a, (deserialize (kvp)).b FROM kvp) ss (a int, b int); In that case your function better not be volatile or it will be evaluated twice. Alban Hertroys

Re: [GENERAL] concatenation issue ( 8.4 )

(name_first, '') || ' ' || coalesce (name_middle, '') Or better yet (you won't get double spaces if any value is NULL): select coalesce(name_first, '') || coalesce (' ' || name_middle, '') Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM

[GENERAL] Row estimates on empty table

-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4ab280e511031155049759! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

[GENERAL] How to match sets?

a set into an array. [2] The SRF's actually return a type unit_token(token text, exponent int) which makes using array_accum and comparisons easier. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM

Re: [GENERAL] Loop

be appreciated. Regards, Chris. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4aab70c813781789017984! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Loop

On 12 Sep 2009, at 11:58, Alban Hertroys wrote: On 12 Sep 2009, at 24:17, db.subscripti...@shepherdhill.biz wrote: Hi, I have a loop of the form: FOR rec IN SELECT code FROM staff WHERE shiftgroup = NEW.groupe ORDER BY code LOOP WHILE sdate = NEW.todate LOOP SELECT

Re: [GENERAL] query speed question

is actually very similar to the original query's plan, it has the same problems. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4aa939f812071577543529! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] More Snow Leopard problems...

after the installation too. It stopped once I rebooted once more, although that shouldn't have been necessary. Maybe that mds process running out of hand depleted some resources? Not really on topic for Postgres, but I figured it might be useful to some. Alban Hertroys -- If you can't see

Re: [GENERAL] More Snow Leopard problems...

the PG8.3 on my FreeBSD server. So if there are hidden problems I probably haven't encountered them yet ;) Alban Hertroys -- Screwing up is the correct approach to attaching something to the ceiling. !DSPAM:737,4aa5781711861822231817! -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] query speed question

-degree values by 10 (or by 2) and cast them to int. Integer comparisons are typically faster than numerics. It's hard to tell whether that does indeed take up a significant amount of time without the above ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees

Re: [GENERAL] where clause question

what you want or at least know what toolkit to avoid? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4aa1375011861997820494! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

<    3   4   5   6   7   8   9   10   11   12   >