[GENERAL] How to convert MS SQL functions to pgSQL functions

der = G.accountGroupId)) INSERT INTO @table_variable (accountGroupId) ( SELECT accountGroupId FROM GroupInMainGroupP) Return END I need to convert this code to pgSQL. Please help. Thanks, Yogi Yang

Re: [GENERAL] How can i add a postgresql connection using inno setup?

Hi Andreas I installed My project using inno setup program. (Tomcat, PostgreSQL, etc) Oddly, my db was not registered in postgresql. Because the localhost server does not exist on the newly installed postgreSQL. If I add the localhost server and install the project, my db was added to localhost

[GENERAL] How can i add a postgresql connection using inno setup?

Hi all I want to know how can i add a connection using inno setup program like below picture. In my program, I should add databases to localhost like below picture. How can I add loca

[GENERAL] Question about CONSTRAINT TRIGGER

Hi folks, I'm confused about the usage of CONSTRAINT TRIGGER. More specifically, the documentation says that the name of another table referenced by the constraint can be specified in a FROM clause: "The (possibly schema-qualified) name of another table referenced by the constraint. This option i

[GENERAL] Column is a computation

the _calc part more or less is just a flag? Vang P. Yang Advanced Planning Tech, Inc. West Salem, WI 54669 (608) 786-1215 --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com

Re: [GENERAL] "Unlogged indexes"

On 3 May 2013 21:06, Yang Zhang wrote: >> Guessing the answer's no, but is there any way to construct indexes >> such that I can safely put them on (faster) volatile storage? (Just to >> be clear, I'm asking about indexes for *logged* tables.) > > Yes: > > CRE

[GENERAL] "Unlogged indexes"

Guessing the answer's no, but is there any way to construct indexes such that I can safely put them on (faster) volatile storage? (Just to be clear, I'm asking about indexes for *logged* tables.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscr

[GENERAL] Curious why planner can't handle NOT IN

I have an `account` table with 5.3M rows, with primary key `id` of type `text` (and 600+ columns if that matters). I'm trying to create a `newaccount` table with the same schema but 600k newly imported rows, then insert all the old rows for which `id` isn't already in the set of newly updated rows

Re: [GENERAL] Basic question on recovery and disk snapshotting

On Wed, May 1, 2013 at 4:56 PM, Jeff Janes wrote: > That brings up another point to consider. If wal level is minimal, then > tables which you bulk load in the same transaction as you created them or > truncated them will not get any WAL records written. (That is the main > reason the WAL verbos

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

On Tue, Apr 30, 2013 at 11:14 PM, Yang Zhang wrote: > On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane wrote: >> Ian Lawrence Barwick writes: >>> I think this is the post in question: >>> http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace

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

On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane wrote: > Ian Lawrence Barwick writes: >> 2013/5/1 Yang Zhang : >>> That is unfortunate. Good thing I asked, I guess. Do you have a >>> pointer to said blog post? > >> I think this is the post in question: >&g

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

On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane wrote: > Ian Lawrence Barwick writes: >> 2013/5/1 Yang Zhang : >>> That is unfortunate. Good thing I asked, I guess. Do you have a >>> pointer to said blog post? > >> I think this is the post in question: >&g

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

On Tue, Apr 30, 2013 at 7:14 PM, Ian Lawrence Barwick wrote: > 2013/5/1 Yang Zhang : > >> On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan >> wrote: >>> On 2013.04.30 4:55 PM, Yang Zhang wrote: >>>> >>>> I would intuit that it's fine, but I

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

That is unfortunate. Good thing I asked, I guess. Do you have a pointer to said blog post? On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan wrote: > On 2013.04.30 4:55 PM, Yang Zhang wrote: >> >> I would intuit that it's fine, but I just want to make sure there are >>

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

I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view: If I were to lose my temp tablespace upon system crash, would this prevent proper crash recovery? Also, if I were to omit the temp tablespace from the base backup, would that prevent pr

Re: [GENERAL] Basic question on recovery and disk snapshotting

On Sat, Apr 27, 2013 at 11:55 AM, Jeff Janes wrote: > On Sat, Apr 27, 2013 at 10:40 AM, Yang Zhang wrote: >> My question really boils down to: if we're interested in using COW >> snapshotting (a common feature of modern filesystems and hosting >> environments), wo

Re: [GENERAL] Optimizing bulk update performance

ELECT * FROM bar UNION SELECT * FROM tmp WHERE id NOT IN (SELECT id FROM bar); The question I have remaining is whether the bulk UPDATE will be able to update many rows efficiently (smartly order them to do largely sequential scans) - if so, I imagine it would be faster than the above. > >

Re: [GENERAL] Optimizing bulk update performance

On Sat, Apr 27, 2013 at 2:54 AM, Yang Zhang wrote: > On Sat, Apr 27, 2013 at 1:55 AM, Misa Simic wrote: >> Hi, >> >> If dataset for update is large... >> >> Maybe best would be: >> >> From client machine, instead of sending update statements with

Re: [GENERAL] Optimizing bulk update performance

ECT * FROM bar UNION SELECT * FROM foo WHERE id NOT IN (SELECT id FROM bar); Wouldn't this alternative be faster? > > Kind regards, > > Misa > > > > On Saturday, April 27, 2013, Yang Zhang wrote: >> >> On Fri, Apr 26, 2013 at 9:41 PM, Tom Lane wrote: >&g

Re: [GENERAL] Optimizing bulk update performance

On Sat, Apr 27, 2013 at 12:24 AM, Yang Zhang wrote: > On Fri, Apr 26, 2013 at 9:41 PM, Tom Lane wrote: >> Yang Zhang writes: >>> It currently takes up to 24h for us to run a large set of UPDATE >>> statements on a database, which are of the form: >> >>&g

Re: [GENERAL] Optimizing bulk update performance

On Fri, Apr 26, 2013 at 9:41 PM, Tom Lane wrote: > Yang Zhang writes: >> It currently takes up to 24h for us to run a large set of UPDATE >> statements on a database, which are of the form: > >> UPDATE table SET field1 = constant1, field2 = constant2, ... W

Re: [GENERAL] Optimizing bulk update performance

; > On 27/04/13 13:35, Yang Zhang wrote: >> >> We're using Postgresql 9.1.9 on Ubuntu 12.04 on EBS volumes on >> m1.xlarge instances, which have: >> >> 15 GiB memory >> 8 EC2 Compute Units (4 virtual cores with 2 EC2 Compute Units each) >> 64-bi

[GENERAL] Basic question on recovery and disk snapshotting

We're running on EBS volumes on EC2. We're interested in leveraging EBS snapshotting for backups. However, does this mean we'd need to ensure our pg_xlog is on the same EBS volume as our data? (I believe) the usual reasoning for separating pg_xlog onto a separate volume is for performance. Howe

Re: [GENERAL] Optimizing bulk update performance

benchmarks suggest this won't get us enough of a boost as much as possibly refactoring the way we're executing these bulk updates in our application.) On Fri, Apr 26, 2013 at 5:27 PM, Gavin Flower wrote: > On 27/04/13 12:14, Yang Zhang wrote: > > It currently takes up to 24h for us

[GENERAL] Optimizing bulk update performance

It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = constant2, ... WHERE id = constid (We're just overwriting fields of objects identified by ID.) The tables have handfuls of indi

Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select

Apologies for that Tom. I will paste the information in line once I'm back at my computer. I do appreciate your help. On Fri, Apr 12, 2013 at 10:24 AM, Tom Lane wrote: > Yang Zhang writes: >> I updated my SO question with some more info including explain analyze >> (no dif

Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select

It's actually just `text`. I updated my SO question with some more info including explain analyze (no difference), \d, and your last incantation. Thanks! On Fri, Apr 12, 2013 at 7:11 AM, Tom Lane wrote: > Yang Zhang writes: >> db=> explain select * from lead wh

Re: [GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select

QUERY PLAN --- Seq Scan on lead (cost=100.00..1319666.99 rows=1 width=5208) Filter: (email = 'f...@blah.com'::text) (2 rows) On Fri, Apr 12, 2013 at 1:13 AM, John R Pierce wrote: > On 4/12/2013 1:03 AM, Yang Zhang wrote: >

[GENERAL] Why is PostgreSQL 9.1 not using index for simple equality select

Any hints with this question I had posted to SO? http://stackoverflow.com/questions/15965785/why-is-postgresql-9-1-not-using-index-for-simple-equality-select Pasted here as well. Thanks. My table `lead` has an index: \d lead ... Indexes: "lead_pkey" PRIMARY KEY, btree (id)

Re: [GENERAL] Why is this query running slowly?

On Thu, Sep 15, 2011 at 1:22 AM, Tomas Vondra wrote: > On 15 Září 2011, 9:53, Yang Zhang wrote: >> I have a simple query that's been running for a while, which is fine, >> but it seems to be running very slowly, which is a problem: >> >> mydb=# explain select user_

Re: [GENERAL] Why is this query running slowly?

Should also add that while the client is under no load (CPU<1%, load ~0.1, mem ~20%), the server looks pretty busy (CPU 90-100% of 1 core, load ~1.5, mem ~70-80%), but PG is the only thing using resources. On Thu, Sep 15, 2011 at 12:58 AM, Yang Zhang wrote: > If it matters, the client is

Re: [GENERAL] Why is this query running slowly?

If it matters, the client is a JDBC app doing: con = DriverManager.getConnection("jdbc:postgresql://localhost:5434/mydb", "yang", password) con.setAutoCommit(false) val st = con.prepareStatement(""" select user_id from den where user_id not

[GENERAL] Why is this query running slowly?

nt_query | waiting | xact_start | query_start | backend_start | client_addr | client_port ---+-+-+--+-+---+

[GENERAL] Preventing OOM kills

PG tends to be picked on by the Linux OOM killer, so lately we've been forcing the OOM killer to kill other processes first with this script: while true; do for i in `pgrep postgres`; do echo -17 > /proc/$i/oom_adj done sleep 60 done Is there a Better Way? Thanks in advance. -- Sent

Re: [GENERAL] how to start a procedure after postgresql started.

2011/5/24 Craig Ringer : > On 24/05/11 12:46, jun yang wrote: > >> thanks for the info,i am just not have such deep learn of pg internal, >> i am on user level,not hacker,so the mail is in pgsql-general,not >> hacker list. > > Then you really, really, REALLY don'

Re: [GENERAL] how to start a procedure after postgresql started.

2011/5/24 Rick Genter : > > On May 23, 2011, at 9:46 PM, jun yang wrote: > >> thanks for the info,i am just not have such deep learn of pg internal, >> i am on user level,not hacker,so the mail is in pgsql-general,not >> hacker list. > > What you are asking to do i

Re: [GENERAL] how to start a procedure after postgresql started.

2011/5/23 Craig Ringer : > On 23/05/2011 10:13 AM, jun yang wrote: > >> actually, we will write the procedure in pl/python,then fork a new >> thread or a new process which is easy. > > Yikes. Be careful there - it's not as easy as you think it is. > > Spawni

Fwd: [GENERAL] how to start a procedure after postgresql started.

-- Forwarded message -- From: jun yang Date: 2011/5/23 Subject: Re: [GENERAL] how to start a procedure after postgresql started. To: Craig Ringer 2011/5/23 Craig Ringer : > On 23/05/2011 9:37 AM, jun yang wrote: > >> what we want to do is explore the ability to mov

Re: [GENERAL] how to start a procedure after postgresql started.

2011/5/22 Scott Marlowe : > On Sun, May 22, 2011 at 6:49 AM, jun yang wrote: >> 2011/5/22 Scott Marlowe : >>> On Sat, May 21, 2011 at 10:57 PM, jun yang wrote: >>>> now all the question: >>>> 1.how start a procedure or a script after postgresql start. &g

Re: [GENERAL] how to start a procedure after postgresql started.

2011/5/23 Darren Duncan : > John R Pierce wrote: >> >> On 05/21/11 10:41 PM, Darren Duncan wrote: >>> >>> Well, if you can run a stored procedure automatically when Postgres >>> starts, that looks like a necessary step to being able to implement an >>> entire application inside Postgres. >>> >>> St

Re: [GENERAL] how to start a procedure after postgresql started.

2011/5/22 Scott Marlowe : > On Sat, May 21, 2011 at 10:57 PM, jun yang wrote: >> now all the question: >> 1.how start a procedure or a script after postgresql start. > > Do you need this stored procedure or script to always run at pg > database start?  Or every time

Re: [GENERAL] how to start a procedure after postgresql started.

we don't need se-linux function now ,so it is not the good idea, and se-linux don't available on windows. 2011/5/22 Pavel Stehule : > Hello > > 2011/5/22 jun yang : >> now all the question: >> 1.how start a procedure or a script after postgresql start. >> 2.h

[GENERAL] how to start a procedure after postgresql started.

now all the question: 1.how start a procedure or a script after postgresql start. 2.how to get notify when a table created. 3.how to get notify when a database created. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresq

[GENERAL] ERROR: cannot execute nextval() in a read-only transaction

I upgrade postgresql from 8.4.2 to 9.0.4. But I have one problem. The Error message 'cannot execute nextval() in a read-only transaction' Please help me. [Version 8.4.2] DEVDB=# select version(); version -

[GENERAL] Disk space usage discrepancy

total of under 600MB (the exact query we use for determining this is below, derived from the example query in the PG documentation): $ pg-dbspace psql: FATAL: database "template0" is not currently accepting connections 1272446976 rp 971186176 sfbox 513794048 yang 30326784

Re: [GENERAL] Compression

On Thu, Apr 14, 2011 at 6:46 PM, mark wrote: > > >> -Original Message- >> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- >> ow...@postgresql.org] On Behalf Of Yang Zhang >> Sent: Thursday, April 14, 2011 6:51 PM >> To: Adrian Klaver

Re: [GENERAL] Compression

On Thu, Apr 14, 2011 at 7:42 PM, Adrian Klaver wrote: > On Thursday, April 14, 2011 5:51:21 pm Yang Zhang wrote: > >> > > >> > adrian.kla...@gmail.com > >> > >> Already know about TOAST. I could've been clearer, but that's not the > >&

Re: [GENERAL] Compression

On Thu, Apr 14, 2011 at 5:07 PM, Adrian Klaver wrote: > On Thursday, April 14, 2011 4:50:44 pm Craig Ringer wrote: > >> On 15/04/2011 7:01 AM, Yang Zhang wrote: > >> > Is there any effort to add compression into PG, a la MySQL's > >> > row_format=co

[GENERAL] Compression

Is there any effort to add compression into PG, a la MySQL's row_format=compressed or HBase's LZO block compression? -- 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] .pgpass not working?

Dah, left out the port. On Fri, Apr 8, 2011 at 10:36 PM, Yang Zhang wrote: > I'm using the postgresql 8.4.7 in Ubuntu 10.04, and I'm trying to use > .pgpass documented here: > > http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html > > I have a ~/.pgpa

[GENERAL] .pgpass not working?

d up by psql -h myhostname.com. I also tried explicitly specifying PGPASSFILE, but that didn't work. Neither that env var nor .pgpass is documented in man psql. Entering my password manually works, however. (Don't need to specify username/database since they default to my username, w

Re: [GENERAL] What happened to ALTER SCHEMA ... SET TABLESPACE?

Also, in PG8.4+, is there any way to set the default tablespace on a per-schema basis? On Thu, Apr 7, 2011 at 12:27 PM, Yang Zhang wrote: > Last I could find on this, it was slated for 8.1 inclusion: > > http://archives.postgresql.org/pgsql-patches/2004-08/msg00425.php > > But it

[GENERAL] What happened to ALTER SCHEMA ... SET TABLESPACE?

Last I could find on this, it was slated for 8.1 inclusion: http://archives.postgresql.org/pgsql-patches/2004-08/msg00425.php But it doesn't seem to be there in my PG8.4 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.po

[GENERAL] Attaching/detaching tablespaces (or, in general, parts of a DB)

Is it possible to attach or detach parts of a DB (e.g. tablespaces), such that I can flexibly move the disks containing the DB tables around to different hosts? The last discussion I could find on this topic is from 2007, and the answer was "No": http://postgresql.1045698.n5.nabble.com/Possible-t

Re: [GENERAL] Preventing accidental non-SSL connections in psql?

On Wed, Apr 6, 2011 at 6:18 PM, Adrian Klaver wrote: > On Wednesday, April 06, 2011 5:21:23 pm Yang Zhang wrote: > >> On Wed, Apr 6, 2011 at 4:57 PM, Scott Marlowe >> wrote: > >> > On Wed, Apr 6, 2011 at 5:24 PM, Yang Zhang >> > wrote: > >>

Re: [GENERAL] Preventing accidental non-SSL connections in psql?

On Wed, Apr 6, 2011 at 4:57 PM, Scott Marlowe wrote: > On Wed, Apr 6, 2011 at 5:24 PM, Yang Zhang wrote: >> On Wed, Apr 6, 2011 at 4:22 PM, Adrian Klaver >> wrote: >>> On Wednesday, April 06, 2011 4:06:40 pm Yang Zhang wrote: >>>> How do I prevent acciden

Re: [GENERAL] Preventing accidental non-SSL connections in psql?

On Wed, Apr 6, 2011 at 4:53 PM, Adrian Klaver wrote: > On Wednesday, April 06, 2011 4:24:30 pm Yang Zhang wrote: > >> On Wed, Apr 6, 2011 at 4:22 PM, Adrian Klaver >> wrote: > >> > On Wednesday, April 06, 2011 4:06:40 pm Yang Zhang wrote: > >> >> Ho

Re: [GENERAL] Preventing accidental non-SSL connections in psql?

On Wed, Apr 6, 2011 at 4:22 PM, Adrian Klaver wrote: > On Wednesday, April 06, 2011 4:06:40 pm Yang Zhang wrote: >> How do I prevent accidental non-SSL connections (at least to specific >> hosts) when connecting via psql? Is there any configuration for this? >

[GENERAL] Preventing accidental non-SSL connections in psql?

How do I prevent accidental non-SSL connections (at least to specific hosts) when connecting via psql? Is there any configuration for this? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-

Re: [GENERAL] How to set password without echoing characters?

On Wed, Apr 6, 2011 at 2:09 PM, Adrian Klaver wrote: > On Wednesday, April 06, 2011 2:00:53 pm Yang Zhang wrote: >> Anyone know how to set your password without having it visible in the >> terminal, psql history, etc. (i.e. getpass(3))? Hoping there's a >> program that

Re: [GENERAL] How to set password without echoing characters?

On Wed, Apr 6, 2011 at 2:15 PM, Yang Zhang wrote: > On Wed, Apr 6, 2011 at 2:09 PM, Adrian Klaver wrote: >> On Wednesday, April 06, 2011 2:00:53 pm Yang Zhang wrote: >>> Anyone know how to set your password without having it visible in the >>> terminal, psql hi

[GENERAL] How to set password without echoing characters?

Anyone know how to set your password without having it visible in the terminal, psql history, etc. (i.e. getpass(3))? Hoping there's a program that already does this. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.pos

[GENERAL] Disk space usage analyzer?

Is there any tool for breaking down how much disk space is used by (could be freed by removing) various tables, indexes, selected rows, etc.? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgs

Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE?

Sent: Thursday, March 24, 2011 4:56 PM > To: Yang Zhang > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Default permissions for CREATE SCHEMA/TABLE? > > * Yang Zhang (yanghates...@gmail.com) wrote: >> Any way I can have all newly created schemas/tables be owned by

[GENERAL] Default permissions for CREATE SCHEMA/TABLE?

Any way I can have all newly created schemas/tables be owned by, or have all permissions granted to, a certain group, without having to remember to GRANT ALL ON [SCHEMA|TABLE] TO that group? Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

[GENERAL] Compression hacks?

ffects of app-level large-field compression in analytical workloads (though I'd be curious about transactional workloads as well)? Thanks in advance. -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscript

[GENERAL] Synchronous replication hack for 9.0?

ow wait for the standby"? -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Checking for stale reads on hot standby

Thanks in advance. -- Yang Zhang http://yz.mit.edu/ -- 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] Bizarre problem: Python stored procedure using protocol buffers not working

cedure languages as well? -- Yang Zhang http://yz.mit.edu/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Bizarre problem: Python stored procedure using protocol buffers not working

I have a table containing a protocol buffer (pb) attribute, and a stored procedure foo that tries to parse it and return the parsed value as a human-friendly string (not actual application code, just a minimal test case to demonstrate the problem). But running foo gives me nothing back. yang=# \d

Re: [GENERAL] How to retrieve List of Tables in a Database using...

Hello, Thanks everyone for your help. I really appreciate it. Yogi Yang -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to retrive List of Tables in a Database using...

. Structure of a Table TIA Yogi Yang -- 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] Sorting performance vs. MySQL

On Tue, Feb 23, 2010 at 1:48 AM, Scott Marlowe wrote: > On Mon, Feb 22, 2010 at 10:51 PM, Yang Zhang wrote: >> nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe >> wrote: >>> >>> What do things like vmstat 10 say while the query is running on each >>>

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 9:30 PM, Alex Hunsaker wrote: > On Mon, Feb 22, 2010 at 11:10, Yang Zhang wrote: >> I have the exact same table of data in both MySQL and Postgresql. In ?> >> Postgresql: > > FWIW on a stock (unchanged postgresql.conf) 8.3.9 I get (best of 3 >

Re: [GENERAL] Sorting performance vs. MySQL

nOn Mon, Feb 22, 2010 at 3:45 PM, Scott Marlowe wrote: > On Mon, Feb 22, 2010 at 12:53 PM, Yang Zhang wrote: >> On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe >> wrote: >>> On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote: >>>> I have the exact

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 5:31 PM, Igor Neyman wrote: > When in doubt - test. > Why not remove index in MySQL (or create index in PostgreSQL) and see > what happens. > Why trying compare "apples and oranges"? Continue reading this thread -- I also tried using an index in Post

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 3:44 PM, Tom Lane wrote: > Yang Zhang writes: >> On Mon, Feb 22, 2010 at 1:13 PM, Pavel Stehule >> wrote: >>> the speed depends on setting of working_memory. Try to increase a >>> working_memory > >> It's already at >

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 2:41 PM, Frank Heikens wrote: > > Op 22 feb 2010, om 20:28 heeft Yang Zhang het volgende geschreven: >> >> >>> >>> >>>>> If your work-mem is too low there's a good chance that Postgres has to >>>

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 2:52 PM, Scott Marlowe wrote: > On Mon, Feb 22, 2010 at 11:10 AM, Yang Zhang wrote: >> I have the exact same table of data in both MySQL and Postgresql. In >> Postgresql: > > Just wondering, are these on the same exact machine? > Yes, on the s

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 2:39 PM, Scott Marlowe wrote: > On Mon, Feb 22, 2010 at 12:30 PM, Yang Zhang wrote: >> This isn't some microbenchmark. This is part of our actual analytical >> application. We're running large-scale graph partitioning algorithms. > > It&#x

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 2:27 PM, Alvaro Herrera wrote: > Yang Zhang escribió: >> I have the exact same table of data in both MySQL and Postgresql. In >> Postgresql: > > I just noticed two things: > > [snip lots of stuff] > > 1. > >> ) ENGINE=MyISAM AUTO

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 2:15 PM, Frank Heikens wrote: > > Op 22 feb 2010, om 20:07 heeft Yang Zhang het volgende geschreven: > >> On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys >> wrote: >>> >>> On 22 Feb 2010, at 19:35, Yang Zhang wrote: >>> >

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 2:03 PM, Alvaro Herrera wrote: > Yang Zhang escribió: > >> I'm running: >> >>   select * from metarelcloud_transactionlog order by transactionid; >> >> It takes MySQL 6 minutes, but Postgresql is still running after 70 >

Re: [GENERAL] Sorting performance vs. MySQL

On Mon, Feb 22, 2010 at 1:48 PM, Alban Hertroys wrote: > On 22 Feb 2010, at 19:35, Yang Zhang wrote: > >> I also wouldn't have imagined an external merge-sort as being very > > > Where's that external merge-sort coming from? Can you show an explain analyze? I just

Re: [GENERAL] Sorting performance vs. MySQL

REATE INDEX i_transactionid ON public.metarelcloud_transactionlog >> (transactionid); > > Does an index help a sort operation in PostgreSQL? I also share the same doubt. An external merge-sort needs to make complete passes over the entire dataset, with no index-directed accesses. -- Yan

Re: [GENERAL] Sorting performance vs. MySQL

I also wouldn't have imagined an external merge-sort as being very memory-intensive--wouldn't it only enough buffer space to read 2x and write 1x in big-enough chunks for mostly-sequential access? -- Yang Zhang http://www.mit.edu/~y_z/ -- Sent via pgsql-general mailing list (pgsql-gene

[GENERAL] Sorting performance vs. MySQL

KEY `nodeid` (`nodeid`) ) ENGINE=MyISAM AUTO_INCREMENT=50410166 DEFAULT CHARSET=latin1 I'm running: select * from metarelcloud_transactionlog order by transactionid; It takes MySQL 6 minutes, but Postgresql is still running after 70 minutes. Is there something like a glaring misconfiguratio

Re: [GENERAL] Performance cost of a sort-merge join

;s got approximately nothing to do with this query. Isn't that exactly what the leaf sorts are doing? By comparison, "select * from metarelcloud_transactionlog order by transactionid" takes much, much longer in PG (it's been running for 68 minutes now, and still going, whereas M

[GENERAL] Performance cost of a sort-merge join

34..8534662.95 rows=50410244 width=17) Sort Key: b.transactionid -> Seq Scan on metarelcloud_transactionlog b (cost=0.00..925543.44 rows=50410244 width=17) (14 rows) -- Yang Zhang http://www.mit.edu/~y_z/ -- 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] psql and Emacs on Windows

:44 PM, Joshua D. Drake wrote: > > On Fri, 2009-08-07 at 17:36 +0100, Sam Mason wrote: > > > On Fri, Aug 07, 2009 at 11:11:10AM -0500, Wenjian Yang wrote: > > > > We currently installed emacs 23.1 and PostgreSQL 8.4.0 for Windows on > a > > > > windows desktop. Wh

[GENERAL] psql and Emacs on Windows

greSQL. I'm also sending the question to the emacs help list, since I'm not an expert in emacs either. Thanks. Wenjian Yang

Re: [GENERAL] Can I add a super table to existing tables?

On Sat, Aug 1, 2009 at 9:42 PM, David Fetter wrote: > On Sat, Aug 01, 2009 at 08:10:02PM -0700, Jun Yang wrote: > > On Sat, Aug 1, 2009 at 7:53 PM, David Fetter wrote: > > > On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote: > > > > Hi all, > > &

Re: [GENERAL] Can I add a super table to existing tables?

On Sat, Aug 1, 2009 at 7:53 PM, David Fetter wrote: > On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote: > > Hi all, > > > > I want to add some common columns to all of my tables. > > Your case may be very special, but offhand, this sounds like a very > bad

[GENERAL] Can I add a super table to existing tables?

Hi all, I want to add some common columns to all of my tables. One way I think would be to add a super table that contains the common columns to all tables. But is there a way to add a super table to existing tables for them to inherit from? Thanks! Jun -- Sent via pgsql-general mailing list

[GENERAL] tables filled to 1 Gig each

Postgresql 8.2 backend to a Java application with JDBC For whatever reason all of postgresql tables all filled up to 1 gig each Accessing Postgresql through either the application or PGadmin fails any ideas of how to get into Postgresql or a way to clean up these tables? thanks -- Sent v

[GENERAL] give a hand to a user having trouble with MySQL?

Came across a blog from Ovid, one of the Perl gurus at BBC. http://use.perl.org/~Ovid/journal/35752?from=rss Quote "They're not kidding, either. Subquery optimization in MySQL is terribly broken, as we've discove

Re: [GENERAL] PQexec(), what should I do for the "NULL in command" problem?

> > > - out of line parameters > > How to? Is there any function for this? I mean the C interface. > > PQexecParams. > > > > - escape the nulls, like \0 > > Do you mean this function? > > #unsigned char *PQescapeBytea(const unsigned char *from, > > # size_t from_length,

Re: [GENERAL] PQexec(), what should I do for the "NULL in command" problem?

On Nov 12, 2007 11:13 PM, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > On Thu, Nov 08, 2007 at 06:39:23PM -0800, jason wrote: > > As you know the interface of PQexec(): > > PGresult *PQexec(PGconn *conn, const char *command); > > > > "command" contains some SQL statements. > > > > But there

Re: [GENERAL] how to ignore invalid byte sequence for encoding without using sql_ascii?

got it. thanks very much. On 10/2/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: > > On Thu, Sep 27, 2007 at 02:28:27AM -0700, [EMAIL PROTECTED] wrote: > > I am now importing the dump file of wikipedia into my postgresql using > > maintains/importDump.php. It fails on 'ERROR: invalid byte s

[GENERAL] Postgres 8.2.3 or 8.1.8?

Hi, We plan to upgrade our database from 7.4.11 to 8.x. It seems that both 8.1 and 8.2 branches are maintained. Which one is more stable? 8.1.18 or 8.2.3? 8.1 or 8.2 branch in general? We are trying to settle with one so we don't have to upgrade for a while. Thank you in advance for your answers,

  1   2   >