gmane.comp.db.postgresql.general

9.1: Slow to add range check on indexed column

Moshe Jacobson — 2013-05-25T19:37:04

I just created a new partition in a log table, and then built a check
constraint on the recorded timestamp column.

I have an index on this column, but the adding of the check constraint took
a very, very long time.

It seems that PostgreSQL 9.1 does not check the index when building a check
constraint on an indexed column. Is this correct? Is this addressed in
future releases?

Thanks.

pg_receivexlog 9.2 client working with 9.1 server?

Dan Birken — 2013-05-24T23:42:54

I am running a pg_receivexlog 9.2 client against a 9.1 server.  It seems to
work.  Comments in the code seem to indicate that this setup is workable.

However, pg_receivexlog is not part of the 9.1 source tree nor the rpm
package (as far as I see), so I am curious if there is some caveat that I
sure be aware of, or some other incompatibility that makes this setup not
work.

The goal is to have PITR using a combination of pg_basebackup (which is
part of 9.1) and pg_receivexlog.

Thanks,
Dan

Insert with query

Juliano Amaral Chaves — 2013-05-24T19:26:21

By doing insert into a table using a query, it seems that all records of consultation were included bypassing the AFTER INSERT triggers and as few as after all the records already included, the TRIGGER is fired for each record, I wonder if this occurrence is normal.
I created an example that demonstrates my doubt, where the 1st case works properly. The 2nd case is restricted by TRIGGER when I think, that could not be

---- EXEMPLE ----
drop table if exists test;
create table test(chave serial primary key, campo1 integer, campo2 integer, campo3 integer);

CREATE OR REPLACE FUNCTION ft_block()  RETURNS trigger AS$BODY$declare  vMax integer;begin
  select max(coalesce(campo3, campo2)) from test where campo1 = new.campo1 into vMax;
  if vMax > coalesce(new.campo3, new.campo2) then   raise 'Erro na inclusão: %, %, %, %', new.campo1, new.campo2, new.campo3, vMax;  end if;
return null;end;$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;

CREATE TRIGGER t_block  AFTER INSERT   ON test  FOR EACH ROW  EXECUTE PROCEDURE f

Help, How to start the server??

YuChi — 2013-05-24T07:35:19

i use postgresql-9.2.4 install on ubuntu11.04(natty) 
and i use the command: 

*postgres< at >ubuntu:/$ postgres -D /usr/local/pgsql/data*

or use

*postgres< at >ubuntu:/$ /usr/local/pgsql/bin/postmaster -D
/usr/local/pgsql/data*

to start the server
but it's stopping at the message:

*LOG:  database system was shut down at 2013-05-24 15:07:34 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started*

for a long time
it seems never continue again
and i don't know how to do next...
can anyone tell me is there anything wrong? or how to do next?

thank you



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Help-How-to-start-the-server-tp5756725.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

There are many processes created and died frequently during PostgreSQl idle

高健 — 2013-05-24T02:50:45

Hello all:

I  found that  during postgresql running, there are so many processes being
created and then died.
I am interested in the reason.

Here is the detail:
I installed from postgresql-9.2.1.tar.bz2.
I put some debug code in fd.c 's PathNameOpenFile function:

fprintf(stderr,"+++While Calling PathNameOpenFile pid=%d.....",getpid();
 fprintf(stderr,"VfdCache Address is: %p \n\n",VfdCache);

When it run, I open two termianal using psql(pid=22109 and pid=22114), then
I can got information like this:
[postgres< at >lex pgsql]$ ./bin/pg_ctl -D ./data start
server starting
[postgres< at >lex pgsql]$ LOG:  database system was shut down at 2013-05-24
09:28:59 CST
LOG:  autovacuum launcher started
+++While Calling PathNameOpenFile pid=22106.....VfdCache Address is:
0xbfb3320

LOG:  database system is ready to accept connections
+++While Calling PathNameOpenFile pid=22109.....VfdCache Address is:
0xbfb2de0

+++While Calling PathNameOpenFile pid=22109.....VfdCache Address is:
0xbfb2de0

+++While Calling PathNameOpenFile pi

Find all the the SELECT statements that occured

Nik Tek — 2013-05-23T21:37:45

Hi,

I have a question on how to find all the SELECT statements that have
occurred in the database. I don't want any DML(Insert/Update/Delete)
statements to be captured. This is for knowing how many selects statements
occur within the database in an 1 hour interval or on a average.

Would this simple or possible in postgres?

Thank you
Nik

Success stories of PostgreSQL implementations in different companies

Oscar Calderon — 2013-05-23T21:36:32

Hi, this question isn't technical, but is very important for me to know.
Currently, here in El Salvador our company brings PostgreSQL support, but
Oracle and SQL Server are more popular here.

Even with that, some clients are being encouraged to change to PostgreSQL
to lower their companies costs in technologies, but very often they ask if
there are success stories of PostgreSQL implementations in companies in our
region or around the world, success stories (if is possible) with some
information like number of concurrent users, some hardware specs or storage
size.

I think that in my country is more common to hear success stories like that
about other databases like Oracle because is more expanded here, but i
would like if there's a place or if you can share with me some real
experiences or success stories that you ever heard of successful
implementations of PostgreSQL in companies to talk with people when they
ask that kind of things.

Regards.

***************************
Oscar Calderon
Analista de Sistema

New role, no database

Melvin Call — 2013-05-23T21:01:56

Hello,

I have a question for which I am unable to find an answer in the
documentation, if y'all don't mind, I will ask here and hope for an
answer.

After installing PostgreSQL and logging in as the admin user of
postgres, I have created a new user with the CREATEDB privilege. How
does that user log in for the first time to create a DB? Am I required
to create a default user DB when I create the user, or is there a way
to log in with no default DB?

TIA,
Melvin

Temp tables

Pascal Tufenkji — 2013-05-23T07:24:17

Hi,

 

To enhance the performance of the queries, I use temp tables in my website
reports.

It seems that a temp table has not been dropped automatically in a certain
session, and now the report is giving an error since the temp table already
exists ?!

 

When I type the following, the database gives me an error:

dragon=# CREATE TEMP TABLE _parcours (id int);

ERROR:  type "_parcours" already exists

 

1.       How can I identify the session in which the temp table is still
locked, so I can drop it manually

2.       Why does those cases happen and the temp tables dont drop
automatically

 

Id appreciate a quick reply 

Thanks in advance

 

 

Pascal TUFENKJI
Service de Technologie de l'Information
Université Saint-Joseph - Rectorat
Tel: +961 1 421 132
Email:  <mailto:ptufenkji< at >usj.edu.lb> ptufenkji< at >usj.edu.lb

Foreign Key violated

Keith Fiske — 2013-05-23T14:15:09

Client reported an issue where it appears a foreign key has been violated

prod=#\d rma_items
[snip]
rma_items_rma_id_status_fk" FOREIGN KEY (rma_id, rma_status) REFERENCES
rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE

prod=# select i.rma_id, i.rma_status, r.id, r.status from rmas r join
rma_items i on i.rma_id = r.id and i.rma_status != r.status;
   rma_id   | rma_status |     id     | status
------------+------------+------------+--------
 1008122437 | r          | 1008122437 | c
(1 row)


Attempting to reinsert this data again causes a violation error, so it
doesn't appear to be broken

prod=# begin;
BEGIN
prod=# insert into rma_items (rma_id, order_item_id, return_reason_id,
rma_status) values (1008122437, 1007674099, 9797623, 'r');
ERROR:  insert or update on table "rma_items" violates foreign key
constraint "rma_items_rma_id_status_fk"
DETAIL:  Key (rma_id, rma_status)=(1008122437, r) is not present in table
"rmas".
prod=# rollback;
ROLLBACK

This is running 9.2.4 on CentOS. If anyone can sugge

What is a DO block for?

Rob Richardson — 2013-05-23T12:58:30

Greetings!

Another post on this list suggested using a DO block if the user's Postgres version is 9.0 or later.  The documentation for the DO block says what it is, but not what it is for.  The only benefit I could see for it is allowing the use of locally defined variables.  I'm sure there's more to it than that.  What justifies the existence of the DO block?

The message that mentioned the DO block is quoted below as an example.

Thanks very much!

RobR

-----Original Message-----
From: pgsql-general-owner< at >postgresql.org [mailto:pgsql-general-owner< at >postgresql.org] On Behalf Of Sergey Konoplev
Sent: Thursday, May 23, 2013 2:14 AM
To: Sajeev Mayandi
Cc: pgsql-general< at >postgresql.org
Subject: Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi <Sajeev_Mayandi< at >symantec.com> wrote:

You can use DO block if your postgres version is >=9.0.

DO $$
DECLARE _tablename text
BEGIN
    FOR
        SELECT INTO _tablename tablename
        FROM pg_tables WHERE schemaname =

update ARRAY of COMPOSITE TYPE of text

Wojciech Skaba — 2013-05-23T09:40:37

I did:

CREATE TYPE telephone AS (
area text,
number text,
ext text
);

Then:

CREATE TABLE directory (
id integer,
tel telephone,
faxes telephone[]
);

After some data has been entered, I tried:

UPDATE directory SET tel = ROW('11', '2222222', '333') WHERE id = 1;
UPDATE directory SET faxes[1] = ROW('11', '2222222', '333') WHERE id = 1;

both worked fine, but:

UPDATE directory SET faxes = ARRAY[ROW('11', '2222222', '333'), ROW('44', '555', '666')] WHERE id = 1;

has failed: (You will need to rewrite or cast the expression) with arrow pointing to ARRAY.

Does anybody know how to overcome it?

Please note, I'm not seeking an alternative, as I can still do the following:

UPDATE directory SET faxes = '{(11\,2222222\,333),(44\,555\,666)}' WHERE id=1;

I would like, however, do the same with ARRAY/ROW.

W

Rule for all the tables in a schema

Sajeev Mayandi — 2013-05-23T05:34:46

Hi,

I am in the process of creating a rule  that provides upsert functionality for all the tables which has primary key.    The issue here is we have some 50 to 60 odd tables and have to write a functions that iterates through all these tables , create rules for each of this table, so that the rule body provides  upsert functionality for every one of them.

Is there a way, I can say create a rule for all the tables in an schema? This will avoid writing complicated functions.

Thanks,

Sajeev

Contents of data/base/ and no corresponding entry in pg_database

Kjetil Jørgensen — 2013-05-21T20:21:51

Hi,

(We're running postgres 9.2.X)

from reading
http://www.postgresql.org/docs/9.2/static/storage-file-layout.html the
directories under $PG_DATADIR/data/base should correspond to an actual
database. I've however found a few directories in $PG_DATADIR/data/base
where select datname from pg_database where oid = <oid> returns 0 rows.

The machine does have "high churn" on databases and have suffered a few
crashes, is it possible for things to get left behind in
$PG_DATADIR/data/base after recovery ? or am I possibly missing things
still in use, that's reflected elsewhere ?

Additionally, the directory and it's contents haven't been touched in quite
a while as well, and some of them do correspond to being touched at the
machine having had a hard crash.

The reason I'm asking is that space used as reported by pg_database_size is
off by a LOT compared to df on the filesystem (4-5TB as oposed to df/du
agreeing on 9-ish TB).

Cheers,

Very simple select, using index for ordering, but not for selecting. How to make it faster?

Antonio Goméz Soto — 2013-05-22T19:38:24

Hi,

I have the following simple query on a simple table:

system=# select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000;


system=# \d history
                                   Table "public.history"
  Column  |           Type           |                      Modifiers
----------+--------------------------+------------------------------------------------------
 id       | integer                  | not null default nextval('history_id_seq'::regclass)
 created  | timestamp with time zone |
 creator  | integer                  | not null default 1
 contact  | integer                  | not null default 1
 type     | character varying        | not null default ''::character varying
 lookup   | text                     |
 lookupid | integer                  | not null default 1
 value    | text                     |
Indexes:
    "history_pkey" PRIMARY KEY, btree (id)
    "history_created_index" btree (created)
    "history_creator_index" btree (creator)
    "histor

data file corruption

PG User — 2013-05-22T19:03:20

Hi All,

We are facing one strange problem about data file corruptions.

We have many postgres databases. At some point, one simple query on one
database started crashing back-end.

 The query is

select count(*), col1 from tab1 group by col1;

After using pg_filedump (http://pgfoundry.org/projects/pgfiledump/) on data
files for tab1 (relnodeid in pg_class), we found that the number of
attributes per tuple is different for few tuples in data file than the rest.

pg_filedump utility prints the contents of each tuple along with block
header, data header.

In our case, the same data file has the following two data headers.

valid header

============

<Data> ------

Item 1 – Length: 114 Offset: 32648 (0x7f88) Flags: NORMAL

XMIN: 8849668 XMAX: 0 CID|XVAC: 0

Block Id: 0 linp Index: 1 Attributes: 10 Size: 24

infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID)



Invalid header

==========

<Data> ------

Item 1 – Length: 234 Offset: 32528 (0x7f10) Flags: NORMAL

XMIN: 2959623 XMAX: 0 CID|XVAC: 0

Blo

Ambiguous order by?

Cody Cutrer — 2013-05-22T16:41:12

I'm not sure if this is a bug, or something I'm not understanding. When I
have a column referenced in the select both fully qualified and not fully
qualified, an explicit inner join, and that column in the order by
(unqualified, and not in a function call), it complains that is ambiguous:

create table test1 (id integer, sortable_name varchar);
create table test2 (id integer, test1_id integer);
select test1.sortable_name, sortable_name from test1 inner join test2
on test1.id=test1_id order by sortable_name;


ERROR:  ORDER BY "sortable_name" is ambiguous
LINE 1: ...t1 inner join test2 on test1.id=test1_id order by sortable_n...

All of these work:

select test1.sortable_name, sortable_name from test1 order by
sortable_name /* no join */
select test1.sortable_name, sortable_name from test1, test2 where
test1.id=test1_id order by sortable_name; /* implicit join */
select test1.sortable_name from test1 inner join test2 on
test1.id=test1_id order by sortable_name /* only one sortable_name in
select */
select tes

VACUUM FULL freezes

RDNikeAir — 2013-05-22T14:49:06

I have a database that is on a RAID5 machine that is almost out of memory
(277GB of 330GB used).  I have deleted some data and run the VACUUM FULL
command, but after a few hours gave me the error message "Server closed the
connection unexpectedly. This probably means the server terminated
abnormally before or while processing the request. The connection to the
server was lost. Attempting reset: Failed."  I know that the server did not
fail, so i assume that this timed out.  The server is running version 7.4 (i
know it's an old version).

The database has several tables, but 99% of the data is located in a single
table.  My fear is that there's not enough system memory left to perform the
full vacuum since it's all contained in a single table.

Any ideas?




--
View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-FULL-freezes-tp5756477.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Interrupt WAL recovery

Fabio Rueda Carrascosa — 2013-05-22T10:08:59

hello , I'm restoring a 7 days ago full backup  + wal files. I have to
recover more than 6k wal files and I have no time, I would accept to work
with a ~ 3 days ago snapshot, can I stop the recovery process and start the
server?, if yes, how?

My recovery.conf file only has this line :

restore_command = 'envdir /etc/wal-e.d/env /etc/wal-e.d/wal-e wal-fetch
"%f" "%p"'

Thanks in advance.

Slow query and using wrong index, how to fix? Probably naive question..

Antonio Goméz Soto — 2013-05-22T08:50:13

Hi,

I am using postgresql 8.1 (CentOS5). I have the following table:

system # \d history
                                   Table "public.history"
  Column  |           Type           |                      Modifiers
----------+--------------------------+------------------------------------------------------
 id       | integer                  | not null default nextval('history_id_seq'::regclass)
 created  | timestamp with time zone |
 creator  | integer                  | not null default 1
 contact  | integer                  | not null default 1
 type     | character varying        | not null default ''::character varying
 lookup   | text                     |
 lookupid | integer                  | not null default 1
 value    | text                     |
Indexes:
    "history_pkey" PRIMARY KEY, btree (id)
    "history_created_index" btree (created)
    "history_creator_index" btree (creator)
    "history_lookup_lookupid_creator_index" btree (lookup, lookupid, creator)
    "history_lookup_lookupid_ind

Authorization,Authentication issues trying basic commands.

luis redondo — 2013-05-22T02:15:18

It's my first time using PostgreSQL and I need to do basic things as establishing a new user,create a new database,access psql command line client etc.

I tried : su - postgres to access the server but I don't have a valid password,I don't know if the server is requesting the postgres system account password,or the database one.

I installed PGSQL that come with Oracle Linux 6.4 and don't know what is the PGSQL default configuration.

Basically,I start the server with: /etc/init.d/postgresql start ,and I am stuck about what to do next,and with permissions,authentications etc.

Note: I changed in pg_hba.conf authorizations (ident to trust) but still no access to a workable PGSQL 
account.