Re: [GENERAL] Slow PITR restore

On Dec 12, 2007, at 11:29 AM, Joshua D. Drake wrote: 8 seconds for a single archive recovery is very slow in consideration of this machine. Even single threaded that seems slow. I've seen this on my PITR restores (thankfully, they were for fetching some old data, not because we expoded).

Re: [GENERAL] Slow PITR restore

Tom Lane wrote: Simon Riggs <[EMAIL PROTECTED]> writes: On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote: Yeah, restoring is known to be less than speedy, because essentially zero optimization work has been done on it. If there was a patch to improve this, would it be applied to 8.3? Good

Re: [GENERAL] WHERE (columnX IN (x,y,z)) ORDER BY columnY conflict

"Jay" <[EMAIL PROTECTED]> writes: > I've been trying to get this query to work to no avail. Both parts > work in isolation. I am able to replace the IN clause with an ILIKE > statement and the ORDER BY works fine. But in combination with the IN > statement, the ORDER BY statement does not actually

Re: [GENERAL] Slow PITR restore

"Jeff Trout" <[EMAIL PROTECTED]> writes: > On Dec 12, 2007, at 11:29 AM, Joshua D. Drake wrote: > >> 8 seconds for a single archive recovery is very slow in consideration of >> this machine. Even single threaded that seems slow. > > I've seen this on my PITR restores (thankfully, they were for f

Re: [GENERAL] Slow PITR restore

On Wed, 2007-12-12 at 12:13 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote: > >> Yeah, restoring is known to be less than speedy, because essentially > >> zero optimization work has been done on it. > > > If there was a patch

Re: [GENERAL] Slow PITR restore

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Dec 2007 13:13:35 -0500 Jeff Trout <[EMAIL PROTECTED]> wrote: > > On Dec 12, 2007, at 1:02 PM, Gregory Stark wrote: > > > > I'm not sure what you guys' expectations are, but if you're > > restoring 5 > > minutes worth of database traffic

Re: [GENERAL] Function to convert from TEXT to BYTEA?

Richard Huxton wrote: D. Dante Lorenso wrote: I want to use the ENCRYPT and DECRYPT functions from contrib, but they require inputs of BYTEA. My data is in VARCHAR and TEXT fields and when I try to use the contrib functions, they complain about wrong datatypes. Is there a string function or

Re: [GENERAL] Slow PITR restore

On Wed, 2007-12-12 at 10:08 -0800, Joshua D. Drake wrote: > > Depending on your transaction mix and what percentage of it is > > read-only select queries you might reasonably expect the restore to > > take as long as it took to generate t > > We archive selects? No, but that is exactly his point

Re: [GENERAL] Slow PITR restore

On Dec 12, 2007, at 1:02 PM, Gregory Stark wrote: I'm not sure what you guys' expectations are, but if you're restoring 5 minutes worth of database traffic in 8 seconds I wouldn't be complaining. Depending on your transaction mix and what percentage of it is read- only select queries you

Re: [GENERAL] what is the date format in binary query results

Less than useful. I did read the thread last night. What am I missing? On Dec 12, 2007, at 12:06 AM, Tom Lane wrote: Samantha Atkins <[EMAIL PROTECTED]> writes: How can it be a simple 8 byte int or float and specify a timezone? It doesn't. Read the thread again. r

Re: [GENERAL] very slow query

On Dec 12, 2007 5:44 AM, Ashish Karalkar <[EMAIL PROTECTED]> wrote: > Hi, > I am having PostgreSQL 8.2.4 on Suse 10.3 > > Server is not using the index insted it chooses to take seq scan path. table > is having @ 120 million rows > > here is the output from planner: > Nested Loop IN Join (cost=0.0

Re: [GENERAL] Function to convert from TEXT to BYTEA?

D. Dante Lorenso wrote: I know about creating a CAST from VARCHAR to BYTEA, but the problem with a CAST is that it doesn't port to other database servers when I do a dump and restore. Doesn't it? Hmm... seems to dump for me in 8.2 My CAST was defined as follows: CREATE CAST (VARCHAR AS

Re: [GENERAL] Slow PITR restore

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Dec 2007 18:02:39 + Gregory Stark <[EMAIL PROTECTED]> wrote: > I'm not sure what you guys' expectations are, but if you're restoring > 5 minutes worth of database traffic in 8 seconds I wouldn't be > complaining. I would be. This is a

[GENERAL] data type change on a view

Hello @all, i have a question (rot really for myself, a member of ther german forum asks): i have two tables, contains a varchar(N)-column. Now i create a VIEW based on this tables. The resulting view contains now a varchar without length. How can i prevent this? How can i force that the column i

Re: [GENERAL] Function to convert from TEXT to BYTEA?

I think decode('your string', 'escape') might be what you need. It returns bytea when 'your string' is type text or varchar. TJ http://www.gnova.com Is there a function that will do what I want to convert the datatype without having to create a CAST that PostgreSQL doesn't have natively? How e

Re: [GENERAL] what is the date format in binary query results

OK, I read it again. I don't see anything about how the timezone is specified for this type of column. On Dec 12, 2007, at 12:06 AM, Tom Lane wrote: Samantha Atkins <[EMAIL PROTECTED]> writes: How can it be a simple 8 byte int or float and specify a timezone? It doesn't. Read the thread

Re: [GENERAL] about the performance of autovacuum and vacuumdb?

On Dec 10, 2007 3:48 AM, Charles.Hou <[EMAIL PROTECTED]> wrote: > i try to compare two methods of garbage-collect.(Postgresql Ver:8.1.3) You need to update RIGHT NOW to 8.1.10. seriously, there are crash / data eating bugs in 8.1.3. > 1. enable autovacuum without using vacuumdb -f mydb in cronta

Re: [GENERAL] top posting

Gregory Stark wrote: > We're not goldfish, we can remember the topic of discussion for at least a few > hours. So can Goldfish. Apparently they have a 3-month+ memory. http://nootropics.com/intelligence/smartfish.html http://en.wikipedia.org/wiki/MythBusters_(season_1)#Goldfish_Memory With a me

Re: [GENERAL] Slow PITR restore

On Wed, 2007-12-12 at 12:56 -0500, Tom Lane wrote: > Jeff Trout <[EMAIL PROTECTED]> writes: > > I've seen this on my PITR restores (thankfully, they were for > > fetching some old data, not because we expoded). On a 2.4ghz opteron > > it took 5-50 seconds per wal segment, and there were a LOT

Re: [GENERAL] General Q's

On Wed, 12 Dec 2007 09:01:59 -0800 "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > Those are the two I know best. We have several extremely high > profile customers that use Drupal & PostgreSQL with great success. May I ask you why they choose Postgres inspite of MySQL? Drupal core works *nearly*

Re: [GENERAL] data type change on a view

On Dec 12, 2007 12:11 PM, A. Kretschmer <[EMAIL PROTECTED]> wrote: > Hello @all, > > i have a question (rot really for myself, a member of ther german forum > asks): > > i have two tables, contains a varchar(N)-column. Now i create a VIEW > based on this tables. The resulting view contains now a va

Re: [GENERAL] General Q's

On Dec 12, 2007 12:33 PM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Wed, 12 Dec 2007 09:01:59 -0800 > "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > > > Those are the two I know best. We have several extremely high > > profile customers that use Drupal & PostgreSQL with great success. >

Re: [GENERAL] data type change on a view

Scott Marlowe <[EMAIL PROTECTED]> schrieb: > On Dec 12, 2007 12:11 PM, A. Kretschmer > <[EMAIL PROTECTED]> wrote: > > Hello @all, > > > > i have a question (rot really for myself, a member of ther german forum > > asks): > > > > i have two tables, contains a varchar(N)-column. Now i create a VIEW

[GENERAL] jdbc lob and postgresql

Hello all, I am contemplating to use postgresql for a product that requires to manipulate many blobs. >From the documentation I am not clear if one has to use postgres api to insert/update/select blobs or one can use standard jdbc api? Can I just execute a statement insert into (..) that has a blo

[GENERAL] why chose pgsql for "light" CMS was: General Q's

On Wed, 12 Dec 2007 12:43:27 -0600 "Scott Marlowe" <[EMAIL PROTECTED]> wrote: > The two most common reasons a company chooses postgresql over MySQL > for these kinds of things are: > They already have a real db server running pgsql in house. If Any good reason for situation that don't have alre

[GENERAL] Altering field passed as parameter to plpgsql trigger

I am trying to make a function that can be reused as a trigger on various tables but am somehow not finding the correct syntax for using a parameter to the function to identify the column to be altered by the trigger. Stripped to basics: create or replace function foo_trigger() returns trigger

Re: [GENERAL] Slow PITR restore

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > On Wed, 12 Dec 2007 18:02:39 + > Gregory Stark <[EMAIL PROTECTED]> wrote: > >> I'm not sure what you guys' expectations are, but if you're restoring >> 5 minutes worth of database traffic in 8 seconds I wouldn't be >> complaining. > > I would be.

Re: [GENERAL] what is the date format in binary query results

Samantha Atkins wrote: > OK, I read it again. I don't see anything about how the timezone is > specified for this type of column. It is not -- that's the point. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "La tristeza es un muro entre dos jardines" (Khalil

Re: [GENERAL] Altering field passed as parameter to plpgsql trigger

Steve Crawford wrote: What is the correct syntax for the line: new.field_to_alter = some_computed_value; Can't be done in plpgsql - it's too static a language. On first call, that assignments basically gets compiled into a planned query and from then on you're stuck. You can use pl/tcl or

Re: [GENERAL] Slow PITR restore

Gregory Stark wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: On Wed, 12 Dec 2007 18:02:39 + Gregory Stark <[EMAIL PROTECTED]> wrote: I'm not sure what you guys' expectations are, but if you're restoring 5 minutes worth of database traffic in 8 seconds I wouldn't be complaining. I w

Re: [GENERAL] Altering field passed as parameter to plpgsql trigger

Richard Huxton wrote: Steve Crawford wrote: What is the correct syntax for the line: new.field_to_alter = some_computed_value; Can't be done in plpgsql - it's too static a language. On first call, that assignments basically gets compiled into a planned query and from then on you're stuck.

Re: [GENERAL] Better alternative for Primary Key then serial??

pilzner wrote: > > > > Alvaro Herrera-3 wrote: > > > > Just do not update the ID -- what use do you have for that > > anyway? If you want to prevent it, you can put a trigger to the column, > > but IMHO it would be a waste of your time and machine resources. > > > > I have absolutely no use

Re: [GENERAL] what is the date format in binary query results

Samantha Atkins wrote: > OK, I read it again. I don't see anything about how the timezone is > specified for this type of column. What differs between timestamp and timestamptz is the behavior on input and output, but in both cases what is effectively stored is only an UTC timestamp, n

Re: [GENERAL] Better alternative for Primary Key then serial??

Alvaro Herrera-3 wrote: > > Just do not update the ID -- what use do you have for that > anyway? If you want to prevent it, you can put a trigger to the column, > but IMHO it would be a waste of your time and machine resources. > I have absolutely no use to update the ID. I'm not sure why an

[GENERAL] Extract last 4 characters from string?

All, Getting the first 4 characters from the begining of a string is easy enough: SELECT SUBSTR('ABCDEFGHIJKLMNOP', 1, 4); Returns 'ABCD'. But getting the last 4 characters appears to be a little more work and is ugly: SELECT SUBSTR('ABCDEFGHIJKLMNOP', LENGTH('ABCDEFGHIJKLMNOP') - 3, 4)

[GENERAL] simple way to find the constraints

We have a php script that executes \dt, \ds, \di, and \dv against our latest database to capture the tables, sequences, etc. The script compares the results of the commands to a file that defines the current schema to generate a script that updates existing schemas to the latest. I need to ad

Re: [GENERAL] top posting

Peter Childs wrote: On 12/12/2007, *Stephen Cook* <[EMAIL PROTECTED] > wrote: I am subscribed to some other technical mailing lists on which the standard is top posting. Those people claim that filing through interleaved quotes or scrolling to the bottom

Re: [GENERAL] Extract last 4 characters from string?

On Dec 12, 2007 4:11 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote: > Is there an easy (preferred) method that I'm missing? select substring('ABCDEFGHIJKLMNOP' from '$'); ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

Re: [GENERAL] Extract last 4 characters from string?

On Wed, 12 Dec 2007 15:11:50 -0600 "D. Dante Lorenso" <[EMAIL PROTECTED]> wrote: >SELECT SUBSTR('ABCDEFGHIJKLMNOP', -4); there is an example in the manual... still "regexpyous" http://www.postgresql.org/docs/8.1/static/functions-string.html substring('Thomas' from '...$') -> mas -- Ivan S

Re: [GENERAL] data type change on a view

Andreas Kretschmer <[EMAIL PROTECTED]> writes: >> cast it to varchar(8): > As i said in a private mail to Scott (sorry): the suggested way don't > work, at least with 8.1. Maybe this works better in more recent > versions. Yes, it works a lot better in 8.2: http://archives.postgresql.org/pgsql-co

Re: [GENERAL] Extract last 4 characters from string?

Rodrigo De León wrote: On Dec 12, 2007 4:11 PM, D. Dante Lorenso <[EMAIL PROTECTED]> wrote: Is there an easy (preferred) method that I'm missing? select substring('ABCDEFGHIJKLMNOP' from '$'); Thanks! Now, since I'd like to see a number in there, I'll do this: SELECT SUBSTRING('ABCDEF

[GENERAL] Insert/Update/Delete views with INSTEAD OF

Hi Does anyone have any recommendations on how to migrate Oracle's 'INSTEAD OF' triggers to PostgreSQL's 'INSTEAD OF' rules? What I would like to do are: - When there's an insert to the view, the rule would insert the data into many tables. - When there's an update, the rule would insert and upd

Re: [GENERAL] jdbc lob and postgresql

Anu Padki wrote on 12.12.2007 20:09: Hello all, I am contemplating to use postgresql for a product that requires to manipulate many blobs. From the documentation I am not clear if one has to use postgres api to insert/update/select blobs or one can use standard jdbc api? Can I just execute a sta

Re: [GENERAL] Hijack!

Em Tuesday 11 December 2007 15:47:27 Joshua D. Drake escreveu: > On Tue, 11 Dec 2007 17:37:27 + > > Gregory Stark <[EMAIL PROTECTED]> wrote: > > "Gregory Williamson" <[EMAIL PROTECTED]> writes: > > > Confidentiality Notice: This e-mail message, including any > > > attachments, is for the sole u

Re: [GENERAL] Hijack!

Em Wednesday 12 December 2007 10:39:32 Alvaro Herrera escreveu: > > It's not just you. Much as I am annoyed by top-posting, I am much more > so by people who top-post at the bottom. Hey, did I say something > stupid? No -- think about it. These guys do exactly the same thing as > top-posters, e

Re: [GENERAL] Slow PITR restore

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Jeff Trout <[EMAIL PROTECTED]> wrote: >> in this case it was 24hrs of data - about 1500 wal segments. During >> this time the machine was nearly complete idle and there wasn't very >> much IO going on (few megs/sec). > Exactly. Which is the poin

Re: [GENERAL] what is the date format in binary query results

On Dec 12, 2007 2:14 AM, Samantha Atkins <[EMAIL PROTECTED]> wrote: > This brings up a second question. How should I do byte order > conversion for 8 byte ints? I can't use hton ntoh routines as they > max out at 32 bits. Is there a better way? Also, are floating point > numbers guaranteed unif

Re: [GENERAL] what is the date format in binary query results

Merlin Moncure wrote: On Dec 12, 2007 2:14 AM, Samantha Atkins <[EMAIL PROTECTED]> wrote: This brings up a second question. How should I do byte order conversion for 8 byte ints? I can't use hton ntoh routines as they max out at 32 bits. Is there a better way? Also, are floating point number

Re: [GENERAL] index organized tables use case

On Wednesday 12 December 2007 10:53, Thomas Kellerer wrote: > Richard Huxton, 12.12.2007 16:12: > > Hmm - I'm not sure it does benefit that much. I mean, if you're going to > > be accessing XXXA, then XXXB, XXXC etc. in order then it clearly helps > > to have the table with the same order as your p

[GENERAL] Need LIMIT and ORDER BY for UPDATE

All, I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE commands. Is this possible? UPDATE invoice i SET reserve_ts = NOW() + '1 hour'::timestamp FROM account a WHERE a.acct_id = i.acct_id AND i.reserve_ts < NOW() AND a.status = 'A' AND i.is_paid IS FALSE ORDER B

Re: [GENERAL] Need LIMIT and ORDER BY for UPDATE

"D. Dante Lorenso" <[EMAIL PROTECTED]> wrote: > > All, > > I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE > commands. Is this possible? > >UPDATE invoice i >SET reserve_ts = NOW() + '1 hour'::timestamp >FROM account a >WHERE a.acct_id = i.acct_id >AND i.res

Re: [GENERAL] Need LIMIT and ORDER BY for UPDATE

D. Dante Lorenso wrote: I'd really like to have ORDER BY and LIMIT for UPDATE and DELETE commands. Is this possible? UPDATE invoice i SET reserve_ts = NOW() + '1 hour'::timestamp FROM account a WHERE a.acct_id = i.acct_id AND i.reserve_ts < NOW() AND a.status = 'A' AND i.is_pai

[GENERAL] timestamp with time zone

Hi, Can someone enlighten me? I know that I can insert date/time data along with time zone info into the timestamp with time zone data type. My question is, can I extract the *original* time zone info afterward? I seems impossible. May be I should use date + time with time zone to preserve the t

[GENERAL] timestamp with time zone

Hi, Can someone enlighten me? I know that I can insert date/time data along with time zone info into the timestamp with time zone data type. My question is, can I extract the *original* time zone info afterward? I seems impossible. May be I should use date + time with time zone to preserve the t

Re: [GENERAL] timestamp with time zone

Tatsuo Ishii wrote: Hi, test=# select t at time zone 'jst' from t2; timezone - 17:34:56+09 (1 row) test=# select t::time from t2; t -- 12:34:56 (1 row) Hello, timezone_test=# select cast(t as time with time zone) from t2; t - 12:3

[GENERAL] For the SQL gurus out there

Ok, something I've been toying around with for a while. Here's the scenario: Imagine a blogging app. I have a table for blogs with a blog_id (primary key) and a table blog_comments also with a comment_id as primary key and a foreign key holding the blog_id the post belongs to. The comments table

Re: [GENERAL] timestamp with time zone

> Tatsuo Ishii wrote: > > Hi, > > > test=# select t at time zone 'jst' from t2; > > timezone > > - > > 17:34:56+09 > > (1 row) > > > > test=# select t::time from t2; > > t > > -- > > 12:34:56 > > (1 row) > > Hello, > > timezone_test=# select cast(t as time wi

Re: [GENERAL] Slow PITR restore

Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Jeff Trout <[EMAIL PROTECTED]> wrote: in this case it was 24hrs of data - about 1500 wal segments. During this time the machine was nearly complete idle and there wasn't very much IO going on (few megs/sec). Exactly. Which is

Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

"Lawrence Oluyede" <[EMAIL PROTECTED]> writes: > On Dec 12, 2007 1:29 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote: >> I cannot repeat this bug on my test date. Can you send some small set >> that reproduce the bug? > I dumped the entire table here: http://www.oluyede.org/temp/instrument.txt I did

Re: [GENERAL] Slow PITR restore

"Tom Lane" <[EMAIL PROTECTED]> writes: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> Exactly. Which is the point I am making. Five minutes of transactions >> is nothing (speaking generally).. In short, if we are in recovery, and >> we are not saturated the I/O and at least a single CPU, the

Re: [GENERAL] Slow PITR restore

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> You sure about that? I tested CVS HEAD just now, by setting the >> checkpoint_ parameters really high, > ... And: >> 2007-12-13 00:55:20 EST LOG: restored log file >> "000107E1006B" from archive Hmm --- I was t

Re: [GENERAL] timestamp with time zone

> > Tatsuo Ishii wrote: > > > Hi, > > > > > test=# select t at time zone 'jst' from t2; > > > timezone > > > - > > > 17:34:56+09 > > > (1 row) > > > > > > test=# select t::time from t2; > > > t > > > -- > > > 12:34:56 > > > (1 row) > > > > Hello, > > > > time

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

On Wed, 12 Dec 2007, Ken Johanson wrote: Kris, do you have pointers to a spec that says the named-columns should specify the index, or that it should instead be the order in the table? My interpretation from the JDBC spec was that the latter is true, I may be wrong... No, I was actually mi

Re: [GENERAL] Slow PITR restore

Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Tom Lane wrote: You sure about that? I tested CVS HEAD just now, by setting the checkpoint_ parameters really high, ... And: 2007-12-13 00:55:20 EST LOG: restored log file "000107E1006B" from archive Hmm --

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

Kris Jurka wrote: I think the expectation is that: CREATE TABLE t(a int, b int); INSERT INTO t(b,a) VALUES (1,2) RETURNING *; will return 1,2 instead of 2,1 as it does now. In this case the op is not expecting that the (potentially reorganized) table order is driving the results, but the or

Re: [GENERAL] what is the date format in binary query results

On Wed, 2007-12-12 at 10:16 -0800, Samantha Atkins wrote: > OK, I read it again. I don't see anything about how the timezone is > specified for this type of column. I went to the manual instead, see below for a useful section. Since the "internally stored value is always in UTC", it doesn't ne

Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

If true, my next idea would be to derive the column name using a subquery in the returning clause. But it sounds like this may have potential security contraints (will any INSERT query always have read access to the PG tables?). And no guarantee of the order matching in the long term. There

Re: [GENERAL] jdbc lob and postgresql

On Thu, 13 Dec 2007, Thomas Kellerer wrote: I am contemplating to use postgresql for a product that requires to manipulate many blobs. From the documentation I am not clear if one has to use postgres api to insert/update/select blobs or one can use standard jdbc api? Can I just execute a state

<    1   2