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

Tom Lane wrote: Kris Jurka <[EMAIL PROTECTED]> writes: 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. Hmm ... I see your point, but on what grounds could one argue that a "*" targe

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

Samantha Atkins <[EMAIL PROTECTED]> writes: > 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? Well, there's the PDP-endianness of odious memory, but AFAIK all curre

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

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. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is yo

Re: [GENERAL] Using hashtext and unique constraints together

On 11/12/2007, Mason Hale <[EMAIL PROTECTED]> wrote: > > > I'm thinking that an insert trigger that ensures (SELECT count(*) FROM > page WHERE hashtext(url) = > hashtext('http://www.postgresql.org') > AND url = ' http://www.postgresql.org' ) = 0 won't work given MVCC,

Re: [GENERAL] Slow PITR restore

"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Wow, o.k. well it is something we (the community) really should look at for > 8.4. I am surprised that it is slower than just walking through the xlogs on > recovery. I am sure there is a reason just surprised. Well in the worst case it has to do ne

Re: [GENERAL] Hijack!

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 > one that posted the question. I hope we aren't going to hit people with > hammers over thi

Re: [GENERAL] top posting

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 just to see a sentence or > two is a waste of their time.

Re: [GENERAL] Killing a session in windows

On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Howard Cole wrote: > >> I take it from the lack of response that nobody knows how to kill a > >> connection from the postgresql side on windows? > > > You can't, short of sending a signal to

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

Hello this is bug. Please send backtrace from core file. Regards Pavel Stehule On 12/12/2007, Lawrence Oluyede <[EMAIL PROTECTED]> wrote: > While developing a Python program I encountered a situation that makes > on of the PostgreSQL 8.3b4's processes crash badly with a segfault. > Let me explai

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

While developing a Python program I encountered a situation that makes on of the PostgreSQL 8.3b4's processes crash badly with a segfault. Let me explain. After enabling "debug5" as log level I was able to locate and reproduce the exact sequence of SQL queries made against my test database and cra

Re: [GENERAL] thesaurus support in postgresql

just a follow up to my question regarding thesaurus support. Is there really no one here who knows anything about it? If there are plans for it? If there is someone currently working on it? Or if there is somebody else I could ask? Regards Florian -- Media Ventures GmbH Jabber-ID [EMAIL PROTEC

Re: [GENERAL] Hijack!

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 > > one that posted the questio

Re: [GENERAL] thesaurus support in postgresql

Florian, I'm one of the author of thesaurus. We're interested in improving of thesaurus, but have no spare time. You're welcome to improve thesaurus dictionary. Oleg On Wed, 12 Dec 2007, Florian Aumeier wrote: just a follow up to my question regarding thesaurus support. Is there really no o

Re: [GENERAL] thesaurus support in postgresql

Hi Oleg, thanks for your reply. Unfortunately I can't do C programming :-( Regards Florian -- Media Ventures GmbH Jabber-ID [EMAIL PROTECTED] Telefon +49 (0) 2236 480 10 22 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner wi

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

Here it is: postgres$ gdb /usr/local/pgsql/bin/postgres core.1600 (gdb) bt #0 0x082c101c in pfree (pointer=0x8472f00) at mcxt.c:591 #1 0xb7e46513 in xmlCleanupCharEncodingHandlers () from /usr/lib/libxml2.so.2 #2 0xb7e4f091 in xmlCleanupParser () from /usr/lib/libxml2.so.2 #3 0x082940e4 in xpa

Re: [GENERAL] thesaurus support in postgresql

On Wed, 12 Dec 2007, Florian Aumeier wrote: Hi Oleg, thanks for your reply. Unfortunately I can't do C programming :-( C skill is not mandatory, you can help suggestion a ways how to improve thesaurus. Regards Florian Regards, Oleg __

Re: [GENERAL] Using hashtext and unique constraints together

Mason Hale wrote: > The problem I need help with is guaranteeing uniqueness of the URL on > insert, with a non-unique index on hashtext(url) and *without* creating a > unique index on page(url). > > I'm thinking that an insert trigger that ensures (SELECT count(*) FROM page > WHERE hashte

Re: [GENERAL] Hijack!

Guy Rouillier wrote: > (2) delete all the individual addressees so only the list is left, then > change that from CC to TO Why do you do that? It's unnecessary. > (3) change my from identity to the one used for the list; although the list > always posts to the identity I have set up for maili

Re: [GENERAL] Hijack!

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 care about the people's content that you are replying to, to have enough wits to not top post. I personally find non-trimmed bottom postings at lo

Re: [GENERAL] Trigger - will not perform INSERT

On Dec 11, 2007, at 21:35, smiley2211 wrote: ex: CREATE TRIGGER mytrig AFTER INSERT OR UPDATE ON foo1 FOR EACH ROW EXECUTE PROCEDURE updatefoo1('datarow'); The command itself is able to be executed without error. However, what then happens, is that whenever the application attempts to in

Re: [GENERAL] very slow query

am Wed, dem 12.12.2007, um 11:44:58 + mailte Ashish Karalkar folgendes: > 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 J

Re: [GENERAL] Slow PITR restore

On Wed, 2007-12-12 at 08:55 +, Gregory Stark wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > > Wow, o.k. well it is something we (the community) really should look at for > > 8.4. I am surprised that it is slower than just walking through the xlogs on > > recovery. I am sure there i

[GENERAL] very slow query

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.00..5030217.97 rows=2 width=106) -> Seq Scan on sms_new (cost=0.00..5027902.0

Re: [GENERAL] Slow PITR restore

Gregory Stark wrote: > bgwriter is started already when doing recovery, right? Perhaps things could > be helped by telling bgwriter to behave differently during recovery. No. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "Amanece.

[GENERAL] index organized tables use case

Hello, I'm thinking about migrating from another DBMS to postgresql. I have an almost working proof of concept, but still have some doubts about the following use case. I have a table like the following CREATE TABLE test ( code character varying(32) NOT NULL, tag integer NOT NULL, value dou

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

sorry s/date/data/g Pavel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[GENERAL] LIBPQ Exception

How do I program a DML statement that does not roll back using C. res=PQexec(conn,"BEGIN; insert into x values(100); exception when others then null; end;") fails on syntax. Any other alternative? Thanks Danny ---(end of broadcast)--- TIP 9: I

Re: [GENERAL] very slow query

am Wed, dem 12.12.2007, um 12:25:20 + mailte Ashish Karalkar folgendes: > > here is the output from planner: > > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106) > > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106) > > Filter: (mobile = 919820920858

[GENERAL] Instaltiating an ARRAY within a function

=== 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; === CREATE OR REPLACE FUNCTION callarr()

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

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 Let me know if you need anything else -- Lawrence, oluy

Re: [GENERAL] Hijack!

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 care about the people's >> content that you are replying to, to have enough wits to not top post. > > I personally find

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

Hello I cannot repeat this bug on my test date. Can you send some small set that reproduce the bug? Regards Pavel On 12/12/2007, Lawrence Oluyede <[EMAIL PROTECTED]> wrote: > Here it is: > > postgres$ gdb /usr/local/pgsql/bin/postgres core.1600 > (gdb) bt > #0 0x082c101c in pfree (pointer=0x847

Re: [GENERAL] very slow query

"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am Wed, dem 12.12.2007, um 12:44:09 + mailte Ashish Karalkar folgendes: > > Do you have an index on "mobile"? Can you show us the table definition > > and the output from EXPLAIN ANALYSE? > > > > no index on mobile > > Seq Scan

[GENERAL] executing a procedure with delay

I'd like to execute a stored procedure in postgresql one minute after a table has been modified. In case there are two changes in less than one minute, I want to reset the time that has to be waited before running the procedure. I think I need to use a trigger, but I don't know how... Can you hel

Re: [GENERAL] very slow query

Ashish Karalkar wrote: > Thanks for your answer actually that was the part of full query here is the > actuall plan I think you are confusing this for "here is a completely different plan for a completely different query that has nothing to do whatsoever to what I was asking before". On this

[GENERAL] Southern California Linux Expo

Are there any planes in the works for a booth and talks for PostgreSQL? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED]

Re: [GENERAL] very slow query

am Wed, dem 12.12.2007, um 12:44:09 + mailte Ashish Karalkar folgendes: > > Do you have an index on "mobile"? Can you show us the table definition > > and the output from EXPLAIN ANALYSE? > > > > no index on mobile > > Seq Scan on sms_new ... Filter: (mobile = 919820920858

Re: [GENERAL] very slow query

Alvaro Herrera <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote: > Thanks for your answer actually that was the part of full query here is the > actuall plan I think you are confusing this for "here is a completely different plan for a completely different query that has nothing to do whatso

Re: [GENERAL] Killing a session in windows

On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Howard Cole wrote: I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? You can't, short of sending a signal to the process or r

Re: [GENERAL] Creating Aggregate functions in PLpgSQL

MG>obligatory toppost > > http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html > > is for > > 8.3 where there were some changes to the number of > > arguments an aggregate > > could take. I believe it's also more descriptive > > documentation. > > > > of course for things like avera

Re: [GENERAL] very slow query

Alvaro Herrera <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote: > Thanks for your answer actually that was the part of full query here is the > actuall plan I think you are confusing this for "here is a completely different plan for a completely different query that has nothing to do whatso

Re: [GENERAL] POSIX and libpq

[EMAIL PROTECTED] wrote: I've a multithread application running on Linux. In each thread i've a loop performing a single INSERT operation on the DB. Sometimes (not always), two of the three thread die apparently without any reason. The strange thing is that if I remove the store operation from th

Re: [GENERAL] executing a procedure with delay

Hello you can use pg_sleep function. But using it in trigger is ugly, because transaction stay in open state. Look to orafce on intersession communication. Maybe you can use it. http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29 Regards Pavel Stehule On 12/12/2007, Ottavio Campana <[EM

Re: [GENERAL] Instaltiating an ARRAY within a function

Hello problem is elsewhere. PostgreSQL doesn't support by ref variables. In your sample you have to do: CREATE OR REPLACE FUNCTION callarr() returns integer AS $Z$ DECLARE x varchar[6]; BEGIN x := arr(x); <---! RAISE NOTICE 'x[1]=%',x[1]; return 0; end; $Z$ LANGUAGE 'plpgsql' VOLATIL

Re: [GENERAL] Multiple clusters on one box for PITR

Leigh Dyer wrote: However, I'm running on the Debian Etch packages, which seem to make it quite easy to run multiple clusters, enabling me to run PITR just on the application that requires it. Would running separate clusters on the one box for these two applications be a sensible thing to do?

Re: [GENERAL] Creating Aggregate functions in PLpgSQL

--- On Wed, 12/12/07, Martin Gainty <[EMAIL PROTECTED]> wrote: > MG>what is Xcomp? I really meant x component = effective component of power as opposed to the reactive component of power. > MG>set on stun? :o) I guess a better way to describe the problem is that total power has both a real an

[GENERAL] fiori zanella

We have sent flowers to Zanella this morning, who wants to participate -> 4 euros each p. -- Dr. Patricia Rodriguez-Tomé, PhD CRS4 - Bioinformatics Loc. Pixina Manna Edificio 3 Pula 09010 (CA), Italy http://www.bioinformatica.crs4.org ---(end of broadcast)---

Re: [GENERAL] executing a procedure with delay

am Wed, dem 12.12.2007, um 14:43:55 +0100 mailte Pavel Stehule folgendes: > Hello > > you can use pg_sleep function. But using it in trigger is ugly, > because transaction stay in open state. Look to orafce on intersession > communication. Maybe you can use it. other solution (vaguely): LISTEN/N

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

D. Dante Lorenso wrote: All, 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 something that

Re: [GENERAL] index organized tables use case

On 12/12/07, Enrico Sirola <[EMAIL PROTECTED]> wrote: > Hello Isak, > > Isak Hansen ha scritto: > > > Have a look at the cluster operation; > > . > > > > AFAIK it does lock & duplicate the whole table during reordering, > > which may or ma

Re: [GENERAL] Slow PITR restore

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 has bee

Re: [GENERAL] partitioned table query question

On Monday 10 December 2007 20:01, Erik Jones wrote: > Again, though, is there some better way to go about implementing some > kind of hash based partitioning in postgres besides this that would > be more natural wrt queries? > One way is to set a static bin id for each partition, then do a select

[GENERAL] convert function

Hi all I have problem with "convert" function. Previous behaviour was SELECT convert('ján', 'UNICODE', 'SQL_ASCII'); === jan In postgresql 8.3 is quite new behaviour. SELECT convert('ján', 'UNICODE', 'SQL_ASCII'); == "j\241n

Re: [GENERAL] very slow query

"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am Wed, dem 12.12.2007, um 12:25:20 + mailte Ashish Karalkar folgendes: > > here is the output from planner: > > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106) > > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 wid

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

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 order the results, though no errors are th

Re: [GENERAL] Understanding Aliases

> SELECT-list output aliases name the *output* columns of the SELECT. > Those output columns are not available to the SELECT's computation > clauses Then it's unclear how could the first query work

Re: [GENERAL] Improving the timing of a query

Thanks for your suggestions :) Even though the ideas are good, the function is working really fast now, 1.5-2seconds which is quite enough I guess :) This is how the function is at the moment : http://yancho.pastebin.com/d236fd97f Sorry I forgot to update this thread :( On 12/7/07, Martijn van

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

H i , I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I use bcp to export a table named admin in sqlserver to a text file: --table T_admin id name key regDate isLock realName 1 rison 9988

[GENERAL] about the performance of autovacuum and vacuumdb?

i try to compare two methods of garbage-collect.(Postgresql Ver:8.1.3) 1. enable autovacuum without using vacuumdb -f mydb in crontab. 2. using crontab to vacuumdb in every 10 min (autovacuum disable) the method 2 got more free space than method 1. the disk size of database still increased in m

Re: [GENERAL] very slow query

"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am Wed, dem 12.12.2007, um 11:44:58 + mailte Ashish Karalkar folgendes: > 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

Re: [GENERAL] Pg_catalog reference

It is not possible to assign triggers to system tables hence it is impossible to reference system table in FK constraint. Obviously, users that don't exist in the database cannot modify anything, you can use "session_user" and "current_user" functions for logging purposes, see http://www.postgresql

[GENERAL] copy a large table raises out of memory exception

We have a large table (about 9,000,000 rows and total size is about 2.8 GB) which is exported to a binary file. Postgre 8.2 is running on a Windows 2003 Small business Server which has a 2 GB RAM. When we run "copy tablename from filepath" command, memory usage increases up to 1.8 GB and postgre ra

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

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 are a few scenarios: a.) CREATE TABLE TestTable ( TestID SE

Re: [GENERAL] Trigger - will not perform INSERT

Ok, thanks...I will do some more testing and see what I get... -- View this message in context: http://www.nabble.com/Trigger---will-not-perform-INSERT-tp14282848p14296002.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)

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

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? Thanks. ---(end of broadcast)--

Re: [GENERAL] Hijack!

"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 care about the people's >>> content that you are replying to, to hav

Re: [GENERAL] index organized tables use case

Enrico Sirola wrote: The application code usually performs selection queries like select tag, value from test where code='XXX'; also, deletions are like delete from test where code='XXX'; and insertions follow the same pattern (all the data for a code is inserted using a loop in a single tran

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

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

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

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

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

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 #1: top posting (was: [GENERAL] Hijack!)

You criticize that Joshua's reply was dogmatism but was yours any better? I think people can see through these weak ad hominem arguments; no matter how much you try to cast the technique in a negative light, that doesn't really make it wrong, and in fact, there are many reasons to encourage peop

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

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

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

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

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

  1   2   >