Re: [GENERAL] Help, How to start the server??

[YuChi]

wooo, thanks for all of your advice

it seems to be ok now!



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


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

[Peter Eisentraut]

On 5/21/13 2:41 PM, Bruce Momjian wrote:
> I have thought about this and there are potentially several options
> specified to pg_upgrade that could be passed into scripts:
> 
>   -O, --new-options=OPTIONS new cluster options to pass to the server
>   -P, --new-port=NEWPORTnew cluster port number (default 50432)
>   -u, --user=NAME   cluster superuser (default "root")
> 
> However, if we pass these items into the scripts, we then force these
> values to be used, even if the user wants to use a different value.  It
> is a balance between supplying defaults vs. requiring the user to supply
> or change the values used during the ugprade.
> 
> At this point, I have favored _not_ supplying defaults in the script. 
> Do you have an alternative argument in favor of supplying defaults?

You could put environment variable assignments at the top of the script,
so they are easy to change or remove.  But it seems to me the values
should be in there somehow.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] foreign key to multiple tables depending on another column's value

[Rodrigo Rosenfeld Rosas]

Sorry, I wasn't sure what list I should be sending this question to...

I have a multi-tenant-like application. We have a fields tree that we 
call a template, with something like this:


fields(id, parent_id, name)

And several other related tables. Since we started to support multiple 
templates we created another schema per added template, each having the 
same fields and related tables. Now I was asked to create another 
special template that doesn't contain the values for those fields 
(stored in separate tables in the regular schemas) and that would allow 
us to map some field to some specific field on each of the aggregate 
templates.


So, to exemplify, let's suppose we have regular templates "template1" 
and "template2" and a special template "special1". This is what I want 
to achieve:


I'll use the name (id, parent_id) notation.

template1 (table name is template1.fields):

- Target (1, null)
  - Name (2, 1)
  - Country (3, 1)


template2 (table name is template2.fields):

- Borrower (1, null)
  - Name (12, 1)
  - Country (13, 1)

special1 (table name is special1.fields):

- Company [maps to either Target or Borrower] (20, null)
 - Name (21, 20)
 - Country (22, 20)

So, now the idea is to map those fields using another table:

special1.mapped_fields(field_id, template_name, mapped_field_id), with 
records like:


(20, 'template1', 1)
(20, 'template2', 1)
(21, 'template1', 2)
(21, 'template2', 12)
(22, 'template1', 3)
(22, 'template2', 13)

But the problem, as you have noticed is that I can't use a foreign key 
with mapped_field_id, because the referenced table will depend on the 
value of template_name.


I know I could use a trigger, or some check constraint maybe, to ensure 
the field exists upon insert (or update), but I can't ensure the 
database will become inconsistent in case I remove a mapped field from 
the other schema.


Now I can finally explain my question: is it possible that I set some 
sort of foreign key whose referenced table and column would depend on 
the value of another column?


Thanks in advance,
Rodrigo.



--
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] [PERFORM] Very slow inner join query Unacceptable latency.

[fburgess]

We re-tested these settings a few times after our initial test and realized that the execution time I posted was shewed, because the execution plan was cached after the initial run. Subsequent executions ran in a little over a second.
There ended up being no significant saving by setting these parameters. Un-cached the query ran in about 55 seconds. 
 

 Original Message Subject: Re: [GENERAL] [PERFORM] Very slow inner join query Unacceptablelatency.From: Scott Marlowe Date: Fri, May 24, 2013 3:03 pmTo: fburg...@radiantblue.comCc: Jaime Casanova , psql performance list, Postgres GeneralOn Fri, May 24, 2013 at 3:44 PM,  wrote:> Total runtime: 1606.728 ms 1.6 seconds <- very good response time> improvement>> (7 rows)>> Questions:>> Any concerns with setting these conf variables you recommended; work_mem,> random_page_cost dbserver wide (in postgresql,conf)?>> Thanks so much!!!Yes 500MB is pretty high especially if you have a lot of connections.Try it with it back down to 16MB and see how it does. Work mem is persort so a setting as high as 500MB can exhaust memory on the machineunder heavy load.--To understand recursion, one must first understand recursion.

Re: [GENERAL] Foreign Key violated

[Thom Brown]

On 23 May 2013 15:33, Thom Brown  wrote:
> On 23 May 2013 10:15, Keith Fiske  wrote:
>> 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 suggest how I can look into
>> this deeper and find what the problem may be, I'd appreciate it. I'm here at
>> PGCon if anyone is available to help IRL as well
>
> What do you get with:
>
> SELECT conname
> FROM pg_constraint
> WHERE NOT convalidated;

Did you resolve this?

--
Thom


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

[Bruce Momjian]

On Wed, May 29, 2013 at 09:42:12AM -0400, Peter Eisentraut wrote:
> On 5/21/13 2:41 PM, Bruce Momjian wrote:
> > I have thought about this and there are potentially several options
> > specified to pg_upgrade that could be passed into scripts:
> > 
> >   -O, --new-options=OPTIONS new cluster options to pass to the server
> >   -P, --new-port=NEWPORTnew cluster port number (default 50432)
> >   -u, --user=NAME   cluster superuser (default "root")
> > 
> > However, if we pass these items into the scripts, we then force these
> > values to be used, even if the user wants to use a different value.  It
> > is a balance between supplying defaults vs. requiring the user to supply
> > or change the values used during the ugprade.
> > 
> > At this point, I have favored _not_ supplying defaults in the script. 
> > Do you have an alternative argument in favor of supplying defaults?
> 
> You could put environment variable assignments at the top of the script,
> so they are easy to change or remove.  But it seems to me the values
> should be in there somehow.

Yes, that was another option, though I am worried that I am going to
start getting script-specific, no?  Can I just do this:

PGUSER="postgres"
export PGUSER

vacuumedb -U "$PGUSER"

Is that portable?  I know I can't do:

export PGUSER="postgres"

as older shells can't process that.  What about -P?  Do I pass that too?
I now realize we can't pass -O.

This is all easy to do for 9.4 if we can just agree on it.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
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] foreign key to multiple tables depending on another column's value

[Vick Khera]

On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas  wrote:

> I know I could use a trigger, or some check constraint maybe, to ensure
> the field exists upon insert (or update), but I can't ensure the database
> will become inconsistent in case I remove a mapped field from the other
> schema.
>
> Now I can finally explain my question: is it possible that I set some sort
> of foreign key whose referenced table and column would depend on the value
> of another column?
>

The FK tests are basically triggers, but highly optimized.

That said, the way they enforce the integrity is by having a trigger on
both tables. So for your custom need here, you would want to put a trigger
on the referenced table to disallow deleting a value that is still
referenced, or do whatever appropriate action upon delete/update your
application needs.

Re: [GENERAL] foreign key to multiple tables depending on another column's value

[Rodrigo Rosenfeld Rosas]

Em 29-05-2013 12:51, Vick Khera escreveu:


On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas 
mailto:rr.ro...@gmail.com>> wrote:


I know I could use a trigger, or some check constraint maybe, to
ensure the field exists upon insert (or update), but I can't
ensure the database will become inconsistent in case I remove a
mapped field from the other schema.

Now I can finally explain my question: is it possible that I set
some sort of foreign key whose referenced table and column would
depend on the value of another column?


The FK tests are basically triggers, but highly optimized.

That said, the way they enforce the integrity is by having a trigger 
on both tables. So for your custom need here, you would want to put a 
trigger on the referenced table to disallow deleting a value that is 
still referenced, or do whatever appropriate action upon delete/update 
your application needs.




Ok, thanks. I just wanted to be sure there wasn't some hidden feature of 
PostgreSQL I wasn't aware of yet...


You know, I'm always learning something new on PG, so it worths trying 
to ask first ;)


Cheers,
Rodrigo.

[GENERAL] Introduction

[Corbett, James]

Greetings:

I've just joined this list having heard of this database only thirty minutes 
ago. Apparently my new assignment will have me working on / developing a local 
solution using this database for the backend.

My name is Jim Corbett and I hail from Ottawa Canada where I work for the 
federal government as a Java Web developer.

I've been programming for the past twenty years, the last seven in the 
wonderful world of Java.

For those twenty years as a developer I should say that I have been completely 
blind, relying upon a screen review application known as JAWS and a Braille 
display.

I'm looking forward to being part of this list.

Sincerely,

Jim

Jim Corbett

Programmer / Analyst
WCAG 2.0 AA Assessor / JAWS SME

Business and Enterprise Solutions Directorate (BESD).
Business Registration, Infrastructure Support and Portals (BRISP)
Canada Revenue Agency | Agence du revenu du Canada
Room: H4-202
875 Heron Road, Ottawa ON  K1A 0L5
james.corb...@cra-arc.gc.ca
Telephone | Téléphone 613) 954-9154
Facsimile | Télécopieur (613) 941-4932
Government of Canada | Gouvernement du Canada

"Quid, Me Anxius Sum"

Alfred E. Neuman

Re: [GENERAL] Introduction

[Stephen Frost]

James,

* Corbett, James (james.corb...@cra-arc.gc.ca) wrote:
> My name is Jim Corbett and I hail from Ottawa Canada where I work for the 
> federal government as a Java Web developer.

Good to have you; unfortunately you just missed PgCon, which was held at
the University of Ottawa last week.  The list of talks which were held
is up at http://www.pgcon.org and many of those talks now have the
slides uploaded as well.  Other interesting places of information are
http://planet.postgresql.org, the PostgreSQL IRC channel (#postgresql on
irc.freenode.net), and, of course, http://www.postgresql.org where the
PostgreSQL documentation resides (which, in my view at least, is quite
excellent).

> I've been programming for the past twenty years, the last seven in the 
> wonderful world of Java.

If you intended that sarcastically (hard to tell through email), then
you might be happy to learn that PostgreSQL is written entirely in C and
we could always use more C developers hacking on the backend. :)

> I'm looking forward to being part of this list.

Happy to have you!

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] BLOB updates -> database size explodes

[Stephen Scheck]

This is just a guess (I haven't dug into the low-level page/disk access
Postgres code for Large Objects yet but if I'm right, the LO-based project
I'm working on will likely face the same issues you're seeing), but LOs
enjoy transactional behavior just like anything else (as far as I can tell
from my testing) and so are subject to MVCC effects. Since LOs are opaque
to Postgres and it can't infer anything about their structure, even
flipping a single bit in a LO causes whatever page that bit maps to be
marked invalid (as if the page corresponded exactly to one row in a normal
table), and the page copied to a new one along with your change(s).

If this hypothesis is correct, doing a vacuum should free up dead pages and
your size expectations should be more accurate. And if that's the case
putting more intelligence into the application could mitigate some of the
update growth (predicting what page temporally similar updates will go to
and grouping them into a single transaction, for instance).


On Tue, May 28, 2013 at 2:53 PM, Dimitar Misev wrote:

> I'm having some issue with BLOB updates (via ECPG). The total blobs size
> should be ~280MB, but after partially updating all of them for 150 times
> the size on disk grows up from 184MB to 18GB.
>
> In more details:
>
> There are 608 blobs of size 460800 bytes. All blobs are updated piecewise
> in 150 repetitions; so first all blobs are updated in bytes 0 - 3071, then
> 3072 - 6143, etc. In the end on the disk the database is 18GB.
>
> Computing the size of pg_largeobject gives me 267MB:
>
>SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
>  pg_size_pretty
>
>  267 MB
>
> On the other hand, this gives me 18GB, and du -sh on the disk also reports
> 18.4GB:
>
>SELECT tablename,
>pg_size_pretty(size) AS size_pretty,
>pg_size_pretty(total_size) AS total_size_pretty
>FROM (SELECT *, pg_relation_size(schemaname||'**.'||tablename) AS size,
> pg_total_relation_size(**schemaname||'.'||tablename)
>AS total_size
>   FROM pg_tables) AS TABLES
>WHERE TABLES.tablename = 'pg_largeobject'
>ORDER BY total_size DESC;
>tablename| size_pretty | total_size_pretty
>+-**+---
>  pg_largeobject | 18 GB   | 18 GB
>
>
> Doing these updates takes 85 minutes on quad-core i7 with 6GB RAM and SSD
> hard disk. This is PostgreSQL 8.4.12 on Debian 64 bit.
>
> Anyone knows what's going on here?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

[GENERAL] feedback request (Perl/Pg developers)

[Chris Travers]

Hi everyone;

I have put together a basic framework for integrating PostgreSQL stored
procedures into Perl objects.  For those who have followed my blog, this
implements the stored procedure mapping systems we use in LedgerSMB for
more general users.Interfaces have been generalized and so they should
be useful beyond our use case.  It is my hope that this is inspiration for
similar frameworks in other languages.  Before anyone asks, yes, these are
licensed under the 2-clause BSD license.

Before I look at submitting this as News, I would like to ask for feedback.

The docs for the current CPAN modules are available at:

http://search.cpan.org/~einhverfr/PGObject-1.1/lib/PGObject.pm
http://search.cpan.org/~einhverfr/PGObject-Simple-1.1/lib/PGObject/Simple.pm
http://search.cpan.org/~einhverfr/PGObject-Simple-Role-0.10/lib/PGObject/Simple/Role.pm

Best wishes,
Chris Travers

[GENERAL] ROLE based DEFAULT PRIVILEGES

[James Sewell]

Hello,

I have a database in which I want to grant permission so that when ANY
table is created by a member of role_a SELECT access is granted on it to
ALL members of role_b.

I had thought that:

  ALTER DEFAULT PRIVILEGES FOR ROLE role_a GRANT SELECT ON TABLES TO role_b;

or

  ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO role_b;

Would achieve this, but it seems that the first creates default permission
records ONLY for tables created by role_a (not members of role_a) and the
second creates default permission records for tables created by the
Postgres user (or whoever you are connected to psql as).

Reading the documentation again, this is actually what is stated.

Is there a way to achieve this? I want role_a to maintain this level of
access as new tables are created (all creators will be in role_b).

James Sewell
Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com

-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
<>

Re: [GENERAL] ROLE based DEFAULT PRIVILEGES

[Stephen Frost]

* James Sewell (james.sew...@lisasoft.com) wrote:
> Is there a way to achieve this? I want role_a to maintain this level of
> access as new tables are created (all creators will be in role_b).

Sadly, no.  It's exactly what I was originally hoping for with the
DEFAULT PRIVILEGES capability, but it's a non-trivial problem (what do
you do when there are conflicting sets of default privileges for a given
login role because they belong to multiple other roles...?).

What you'll need to do is grant the defauly privileges explicitly for
the account which is logging in / creating the tables.  You can specify
the user using:

ALTER DEFAULT PRIVILEGES FOR myuser IN SCHEMA myschema GRANT SELECT ON
TABLES TO role_a;

Or similar, but you'll need to do that for every role in role_b and
update the default privs as you add/remove users from role_b.

Thanks,

Stephen


signature.asc
Description: Digital signature

[GENERAL] TRIGGER EVENT

[Sajeev Mayandi]

Hi,

I am process of porting from sybase to postgresql. Is there syntax equivalent 
of the TRIGGER EVENT Statement (Please see the sybase help for trigger event 
below) in Postgresql. NOTE the TRIGGER EVENT in sybase could be used to trigger 
an user defined event. I am not seeing any similar syntax or any equivalent 
syntax which behaves similar to it.

Appreciate your help

Thanks,

Sajeev



TRIGGER EVENT statement

Use this statement to trigger a named event. The event may be defined for event 
triggers or be a scheduled event.

Syntax

TRIGGER EVENT event-name [ ( parm = value, ... ) ]


Parameters

 *   parm = value   When a triggering condition causes an event handler to 
execute, the database server can provide context information to the event 
handler using the event_parameter function. The TRIGGER EVENT statement allows 
you to explicitly supply these parameters, to simulate a context for the event 
handler.

Remarks

Actions are tied to particular trigger conditions or schedules by a CREATE 
EVENT statement. You can use the TRIGGER EVENT statement to force the event 
handler to execute, even when the scheduled time or trigger condition has not 
occurred. TRIGGER EVENT does not execute disabled event handlers.

Each value is a string. The maximum length of each value is limited by the 
maximum page size specified by the -gp server option. If the length of value 
exceeds the page size, the string is truncated at the point at which the page 
is full.

Permissions

Must have DBA authority.

Side effects

None.

Re: [GENERAL] TRIGGER EVENT

[Alvaro Herrera]

Sajeev Mayandi wrote:
> Hi,
> 
> I am process of porting from sybase to postgresql. Is there syntax equivalent 
> of the TRIGGER EVENT Statement (Please see the sybase help for trigger event 
> below) in Postgresql. NOTE the TRIGGER EVENT in sybase could be used to 
> trigger an user defined event. I am not seeing any similar syntax or any 
> equivalent syntax which behaves similar to it.

This seems similar to LISTEN / NOTIFY.


-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general