Is there a trick to using window functions to SET columns in an UPDATE?
Here is the query I'd like to run:

UPDATE profiles
SET score_tier = percent_rank()
OVER (PARTITION BY site_id ORDER BY score ASC)
WHERE score IS NOT NULL

But that gives me an error on Postgres 9.1:

ERROR: cannot use window function in UPDATE

This alternate version works, but is messier and slower:

UPDATE profiles p
SET score_tier = x.perc
FROM (SELECT id,
percent_rank() OVER (PARTITION BY site_id ORDER BY
score AS

Dear listmembers,

I need to move

/var/lib/postgresql/8.4/main

from the / partion to another disc.

Based on the

<a href="http://wiki.postgresql.org/wiki/Simple_Configuration_Recommendation#File_System_Layouts" title="http://wiki.postgresql.org/wiki/Simple_Configuration_Recommendation#File_System_Layouts">http://wiki.postgresql.org/wiki/Simple_Configuration_Recommendation#File...</a>

I plan the following.

0.) Mount new pgroot, are there any hints for the mount command?

mount -t ext4 -o noatime,nodiratime,nodev,noexec,nosuid /dev/sde1
/pgroot

output from mount

/dev/sde1 on /pgroot type ext4
(rw,nosuid,nodev,noexec,noatime,nodiratime,barrier=1,data=ordered)

1.) create a full backup of the current DB

/usr/bin/pg_dumpall --file=/tmp/backup_before_

Lists wrote:

Hey everyone,

We recently got bit by this, and I wanted to make sure it was known to
the general community.

In new(er) Linux kernels, including late versions of the 2.6 tree, XFS
has introduced dynamic speculative preallocation. What does this do? It
was added to prevent filesystem fragmentation by preallocating a large
chunk of memory to files so extensions to those files can go on the same
allocation. The "dynamic" part just means it adjusts the size of this
preallocation based on internal heuristics.

Unfortunately, they also changed the logic in how this extra space is
tracked.

Hello,

Is anyone running PostgreSQL on a clustered file system on Linux? By
"clustered" I actually mean "shared", such that the same storage is
mounted by different servers at the same time (of course, only one
instance of PostgreSQL on only one server can be running on such a
setup, and there are a lot of other precautions that need to be satisfied).

Hi all：
I made partition tables:

postgres=# create table ptest(id integer, name varchar(20));
CREATE TABLE
postgres=# create table ctest01(CHECK(id<5000000)) inherits (ptest);
CREATE TABLE
postgres=# create table ctest02(CHECK(id>=5000000)) inherits (ptest);
CREATE TABLE
postgres=#
postgres=# create index on ctest01(id);
CREATE INDEX
postgres=# create index on ctest02(id);
CREATE INDEX
postgres=#
postgres=#

postgres=# CREATE OR REPLACE FUNCTION ptest_insert_trigger() RETURNS
TRIGGER AS $$
postgres$#
postgres$# BEGIN
postgres$#
postgres$# IF ( NEW.id <5000000 ) THEN
postgres$# INSER

Hello all,
GoldenGate added PostgreSQL as a target database for replication. I tried
setting it up, and not finding any tutorial, put together a how to here -
<a href="http://jayadevanm.wordpress.com/2012/11/07/goldengate-replication-from-oracle-to-postgresql/" title="http://jayadevanm.wordpress.com/2012/11/07/goldengate-replication-from-oracle-to-postgresql/">http://jayadevanm.wordpress.com/2012/11/07/goldengate-replication-from-o...</a>
I think near real-time replication might have quite a few cases - such as
trying out the application on PostgreSQL before really cutting over from an
Oracle database, may be running reports off PostgreSQL and so on.
Regards,
Jayadevan

Correction, that should have been Postgres 9.0.4 not 9.4.

On Sat, Nov 10, 2012 at 12:26 PM, Steve Crawford
< ... at pinpointresearch dot com> wrote:
Tuning a PostgreSQL database is a major science, but is there a
reasonably easy way to get a stable baseline for comparison? We've
been exploring different hosting options recently, and one thing we
want to know is how well Postgres will perform.

After posting a reply I remembered another recent thread which was
more-or-less on the same topic, but with a different slant. Perhaps
you would also find that useful:

<a href="http://archives.postgresql.org/pgsql-general/2012-11/msg00166.php" title="http://archives.postgresql.org/pgsql-general/2012-11/msg00166.php">http://archives.postgresql.org/pgsql-general/2012-11/msg00166.php</a>

-Kevin

Dear Everybody!

Can somebody show me an example for phantom read in "Repeatable Read" mode
(in 9.1, new, serialized kind of isolation level)?

The help wrote that it is possible:

Repeatable read Not possible Not possible Possible
But I don't understand, why, and how.

C1, C2 are clients.
Every of them is in RR transaction.

What need to do in C1 to read C2's "phantom records"?
Can you show me the detailed steps?

Thanks for every info, help!

Best wishes: dd

Hello there,

Once in awhile, as I am trying to run various versions of the Postgres DB
engine I get a message on startup indicating that my control file is not up
to snuff. Last time it happened with Postgres 9.1 on OpenIndiana 11.

So my questions are as follows:

1) Why does that generally happen?

2) When that does happen is there a way to re-init that control file
somehow?

Thanks.

Boris.

Hi all.

A tablespace is created successfully and so is a test table in it:
# CREATE TABLESPACE ts_name LOCATION '/path/to/tablespace/location';
# SET default_tablespace=ts_name;
# CREATE TABLE ts_test_table;

From the command line:
$ cd /path/to/tablespace/location
$ ls
PG_9.1_201105231
$ cd PG_9.1_201105231/
$ ls
11955
$ cd 11955/
$ ls
24683 24687 24689

All files 2468x correspond to objects identifiable by oid2name:
$ oid2name -d postgres -f 24683
From database "postgres":
Filenode Table Name
But these give nothing:
$ oid2name -d

Hi all:

I have one question about the visibility of explain plan.

Firstly , I was inserting into data to a table. I use : [ insert into
ptest select * from test02; ]

And test02 table has 10,000,000 records.

Maybe you want an exclusion constraint:

<a href="http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION" title="http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION">http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONST...</a>

For example:

CREATE EXTENSION btree_gist ;
CREATE TABLE t1 (c1 INT NOT NULL,
                c2 int8range NOT NULL,
                EXCLUDE USING gist (c1 WITH =, c2 WITH &&));
INSERT INTO t1 VALUES (1, '[1,100]');

Now try to insert an overlapping row:

INSERT INTO t1 VALUES (1, '[51,150]');

-Kevin

Hello,

I've searched the mailing list archives and google regarding using a
directory to contain pg_hba.conf snippets. Does such a feature exist
for any version of PG?

Would this be a better question for a pg dev mailing list?

Please Cc me, I am not (yet) subscribed to the list.

Thanks!

-Matt Zagrabelny

Hi,

The following SQL join command runs the PostgreSQL server out of memory.
The server runs on a box with Red Hat Enterprise Linux Server release 6.3
(Santiago) and PostgreSQL 8.3.21.

select wm_nfsp from "5611_isarq".wm_nfsp
left join "5611_nfarq".nfe on
wm_nfsp.tpdoc = 7 where 1 = 1 and
wm_nfsp.codpre = 2866 and
wm_nfsp.compet = '10/2012';

Explain result:
Nested Loop Left Join (cost=7356.61..48466.46 rows=346312 width=32)
Join Filter: (wm_nfsp.tpdoc = 7)
-> Bitmap Heap Scan on wm_nfsp (cost=11.65..1162.37 rows=11 width=34)
Recheck Cond: (codpre = 2866)
Filter:

Hi all:

What confused me is that: When I select data using order by clause, I got
the following execution plan:

postgres=# set session
enable_indexscan=true;

SET

postgres=# explain SELECT * FROM pg_proc ORDER BY
oid;

QUERY
PLAN

Index Scan using pg_proc_oid_index on pg_proc (cost=0.00..321.60
rows=2490 width=552)

(1
row)

postgres=#

My Question is :

If I want to find record using the where clause which hold the id column,
the index scan

Regarding the caveats here
<a href="http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS" title="http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHERIT-CAVEATS">http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html#DDL-INHE...</a>

I am attempting to logically structure my location data. Say for example I
have cities/states/countries. I have objects that reference a location, but
at any level. An object may reference a city or it may reference a state,
depending on how granular we know its location to be. If the city is known
for an object, the state and country can be inferred, so the object need
not point to all levels in fact that would be redundant and require
checking consistency.

Hi all:

I have one question about the cache clearing.

If I use the following soon after database startup(or first time I use it):

postgres=# explain analyze select id,deptno from gaotab where id=200;

QUERY
PLAN

Index Only Scan using idx_id_dept on gaotab (cost=0.00..8.27 rows=1
width=8) (

actual time=30.912..30.915 rows=1 loops=1)

Index Cond: (id = 200)

Heap Fetches: 1

Total runtime: 47.39

I'd like to create a function :
returning true if the length of the text arg is greater than 0 ;
false otherwise ;

and also returning false when arg is NULL, then i wrote :

CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select
character_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL
INPUT;

where i get the error :

ERROR: syntax error at or near "FALSE"
LINE 1: ...r_length($1) > 0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...

with "RETURNS NULL on NULL INPUT", that's OK.

Hi All,
For the below test case shouldn't the update statement throw error
because the sub query projects a column which is not existing in the table.
For ex in inner select nos1 column exists in t1 and not t2.
Is this a bug or an OP error?

template1=# SELECT version();
version

<a href="mailto:hari. ... at gmail dot com">hari. ... at gmail dot com</a>

Can you give an example of where adding an interval based on *hours*
to TIMESTAMP WITH TIME ZONE would give a different value based on
client's time zone and DST rules?

Darren Duncan wrote:

Note that the message level is LOG, not WARNING or ERROR or anything
more severe. It found the end of the valid WAL stream in WAL files it
was provided, and it's telling you how it decided it was at the end.

One thing I don't think you've really described is how you took your
base backup.

I have streaming replication configured over SSL, and
there seems to be a problem with SSL renegotiation.

This is from the primary's log:

2012-11-06 00:13:10.990
CET,"replication","",5204,"10.153.109.3:49889",509843df.1454,10,"streami
ng 1E3/76D64000",2012-11-05 23:55:27 CET,4/0,0,LOG,08P01,"SSL
renegotiation failure",,,,,,,,,"walreceiver"

2012-11-06 00:13:10.998
CET,"replication","",5204,"10.153.109.3:49889",509843df.1454,11,"streami
ng 1E3/76D64000",2012-11-05 23:55:27 CET,4/0,0,LOG,08P01,"SSL error:
unexpected record",,,,,,,,,"walreceiver"

2012-11-06 00:13:10.998
CET,"replication","",52

Hi,

Does anyone know whether PostgreSQL has complete functional test cases? And
where can I find them?

Currently, I only find some test cases in "PG_SOURCE/src/tests", but it
seems that they are pretty simple and do not cover a lot of features of
PostgreSQL.

Thanks a lot!
Tianyin

I had a 9.0.8 hot standby setup, one master, two slaves, working great.
Then, I tried to re-initialize by making a base backup, the way I've done
it many times before, but for some reason I can't get the standby to accept
connections. I copied the postgresql.conf and recorvery.conf out of the
way, cleaned the data directory and extracted the backup files, then
replaced the conf files.

Peter Geoghegan wrote: