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

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

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

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

[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 with time zone) from t2; t - 12:3

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

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

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

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

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

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?

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

[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

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

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

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

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

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

[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

[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

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

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

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

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

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

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

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 of > segments (replay took hours and hours). I asked a f

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

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

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] General Q's

Dave Page wrote: Joshua D. Drake wrote: * Is there a project to create MySQL compatibility for Postgresql? I No. Thank god. Just think of all those potential customers you could be missing JD :-) http://pgfoundry.org/projects/mysqlcompat/ I know you put a smiley face there but... I

Re: [GENERAL] Slow PITR restore

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 grief, no.

Re: [GENERAL] General Q's

David wrote: Both Joomla / Drupal are making their databases work with Postgsql using an abstraction layer. Nothing wrong with that. Questions - re MySQL/ Postgresql/Joomla/Drupel Could you advise on the following? * Is there a project to create MySQL compatibility for Postgresql? I a

Re: [GENERAL] General Q's

Joshua D. Drake wrote: * Is there a project to create MySQL compatibility for Postgresql? I No. Thank god. Just think of all those potential customers you could be missing JD :-) http://pgfoundry.org/projects/mysqlcompat/ /D ---(end of broadcast)-

Re: [GENERAL] LIBPQ Exception

On Dec 12, 2007 7:36 AM, Abraham, Danny <[EMAIL PROTECTED]> wrote: > Any other alternative? There are no anonymous blocks in PostgreSQL: You could try creating a function: create or replace function shootmyselfinthefoot() returns boolean as $$ declare (...); begin execute 'SOME DML HERE';

Re: [GENERAL] General Q's

I am undertaking a development which typically relies on the type of functionality that can be found at Joomla (www.joomla.com ) and Drupel (www.drupel.com Drupal. Both Joomla / Drupal are making their databases work with Postgsql using an abstraction layer. C

Re: [GENERAL] convert function

On 12/12/2007, Jan Sunavec <[EMAIL PROTECTED]> wrote: > Thanks a lot > > Lots like nice a easy solution.. I am not sure if this is fast solution.. > Many convertions you know.. :-( > Thanks a lot anyway. > If you do this often, use functional index. Pavel >John > > On Wed, 12 Dec 2007 17:13:

[GENERAL] General Q's

Hello I have some questions regarding pgsql and how it may apply to other open source software. Could you advise which forum is best suited to answer these questions. Background I am undertaking a development which typically relies on the type of functionality that can be found at Joomla

Re: [GENERAL] Slow PITR restore

Simon Riggs wrote: On Wed, 2007-12-12 at 07:36 -0800, Joshua D. Drake wrote: We can't really expect people to use PITR if they new it would take hours to recover even on the size of machine I was working on. That's not true statement in all cases and can often be improved with some monitoring

Re: [GENERAL] Would it be OK if I put db file on a ext2 filesystem?

On 12/12/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Regular database files need metadata journalling (data=writeback mount > option for ext3). This is quite faster than full-blown journalling > which is what you get with default ext3 mount options. WAL files > (pg_xlog) do not need any kind

Re: [GENERAL] Would it be OK if I put db file on a ext2 filesystem?

On Dec 12, 2007 9:46 AM, Collin Kidder <[EMAIL PROTECTED]> wrote: > Magicloud Wang wrote: > > Dear, > > I think database has its own operation journal, and different journal > > filesystem does give different performance. So if I put database file on a > > non-journal filesystem, would it be

Re: [GENERAL] Would it be OK if I put db file on a ext2 filesystem?

Magicloud Wang wrote: > Dear, > I think database has its own operation journal, and different journal > filesystem does give different performance. So if I put database file on a > non-journal filesystem, would it be safe? Does this like using a raw device? Regular database files need meta

Re: [GENERAL] convert function

Hello It's look like SQL_ASCII support diacritic chars now. First you have to encode from bytea to text postgres=# SELECT encode(convert('ján', 'UNICODE', 'SQL_ASCII'),'escape'); encode ján (1 row) you wont postgres=# SELECT to_ascii(encode(convert_to('ján', 'latin2'),'escape'),'latin

Re: [GENERAL] Slow PITR restore

On Wed, 2007-12-12 at 07:36 -0800, Joshua D. Drake wrote: > We can't really expect people to use PITR if they new > it would take hours to recover even on the size of machine I was working on. That's not true statement in all cases and can often be improved with some monitoring and tuning. Just

Re: [GENERAL] slony1 replication question

SHARMILA JOTHIRAJAH wrote: Hi, I need some help with slony... Which comes from the Slony lists: http://lists.slony.info/mailman/listinfo Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore

Re: [GENERAL] Instaltiating an ARRAY within a function

Danny Abraham wrote: > === > > CREATE OR REPLACE FUNCTION arr( inout x varchar[] ) > AS > $Z$ > DECLARE > i integer; > BEGIN > select ARRAY['Danny','Eissam','Moshe'] into x; > end; > $Z$ LANGUAGE 'plpgsql' VOLATILE; > > ===

Re: [GENERAL] Hijack!

me too. On Wednesday 12. December 2007, Gregory Stark wrote: >"Alvaro Herrera" <[EMAIL PROTECTED]> writes: >> Thomas Kellerer wrote: >>> Joshua D. Drake, 11.12.2007 17:43: O.k. this might be a bit snooty but frankly it is almost 2008. If you are still a top poster, you obviously don't ca

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

pilzner wrote: > Does stuff like this cause any aches and pains to developers out there, or > do I just need to get in a new mindset??? Also, is there a way to be sure > the primary key is *ONLY* ever given a value by serial, and not subject to > updates??? It doesn't. Just do not update the ID

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

bookman bookman escribió: > Then error occured: > error:invalid input syntax for type timestamp:"" > context:copy T_Admin ,line 2,column regDate:"" > It seemed that the column REGDATE cannot accept a NULL.I tested it use: >insert into T_Admin(name,key,regDate,isLock,realName) >valu

Re: [GENERAL] index organized tables use case

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 primary key. Otherwise, I'd be doubtful you'd see that much benefit.

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

Actually what you are doing below is trying to stuff '' in a timestamp field. Keep in mind '' and NULL are not the same. '' is invalid for timestamp where as NULL is fine. Your example should be insert into T_Admin(name,key,regDate,isLock,realName) values('aaa','aaa',NULL,'1','aaa'); I t

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

Hello use simply NULL postgres=# create table bb(a timestamp, b integer); CREATE TABLE postgres=# insert into bb values(null, null); INSERT 0 1 postgres=# insert into bb values(null, 10); INSERT 0 1 postgres=# insert into bb values(current_timestamp, 10); INSERT 0 1 postgres=# copy bb to stdout;

Re: [GENERAL] Would it be OK if I put db file on a ext2 filesystem?

Magicloud Wang wrote: Dear, I think database has its own operation journal, and different journal filesystem does give different performance. So if I put database file on a non-journal filesystem, would it be safe? Does this like using a raw device? You lose a little bit of data integrit

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

On Tuesday 11 December 2007 9:42 pm, pilzner wrote: > Hi - I'm new to PostGres, but have used MSSQL for about a year. I'm going > through the documentation, but after reading about serials have a lot of > worries about keeping referential integrity in place and other things. > Specifically, here ar

[GENERAL] slony1 replication question

Hi, I need some help with slony... I read the slony manual and tried to replicate the database as given in this... http://www.slony.info/documentation/firstdb.html These are the steps that I did. 1. set the shell variables like clustername,masterdbname etc thro export command 2. created a user

Re: [GENERAL] Southern California Linux Expo

Richard Broersma Jr wrote: Are there any planes in the works for a booth and talks for PostgreSQL? This is already being discussed on the advocacy list :) Joshua D. Drake Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if postin

Re: [GENERAL] Slow PITR restore

Simon Riggs wrote: On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: ... Now I understand that restoring log files can be slow but this is a big machine. Yeah, restoring is known to be less than speedy, because essentially zero optimization work h

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

On Tue, Dec 11, 2007 at 10:29:04AM +0800, bookman bookman wrote: > So it means that the column with type timestamp cannot accept a NULL > .Is there any way I can tansfer this table into postgre?How can i deal > with NULL in this case? NULL values are encoded as an unquoted \N by default in PG.

[GENERAL] VTD-XML Tutorial by Code Examples Posted Options

The C version: http://downloads.sourceforge.net/vtd-xml/c_tutorial_by_code_examples The C# version: http://downloads.sourceforge.net/vtd-xml/CSharp_tutorial_by_code_exam... The Java version: http://downloads.sourceforge.net/vtd-xml/Java_tutorial_by_code_exampl... Also some latest articles

Re: [GENERAL] Determining current block size?

In article <[EMAIL PROTECTED]>, John Wells <[EMAIL PROTECTED]> wrote: % I see that BLOCK_SIZE can be set at compile time, but is there a way % to determine what block size is in use in a running system? I've been % searching but have been unsuccessful so far. show block_size; If you try to start

Re: [GENERAL] Hijack!

Gregory Williamson wrote: Peter Childs caused electrons to shape a message: > > On 11/12/2007, Obe, Regina <[EMAIL PROTECTED]> wrote: > > > > Well said Greg. I have the same problem too of having a crippled mail > > reader :) Really I find mid posting hard to follow especially if I'm the

  1   2   >