Re: [GENERAL] Function error

[David G. Johnston]

On Wed, Jan 13, 2016 at 10:06 PM, Sachin Srivastava  wrote:

> Dear Albe,
>
> Thanks for your support !!!
>
> In my function the problem is that global variables defined inside the
> function. These variables are visible to functions defined inside a
> function.
>

​Holy Crap...PostgreSQL functions support closures and global variables?
Who knew?  Well, aside from that fact that relations are global, anyway...

The problem you seem to have is trying to do something in PostgreSQL
exactly like you'd do it in Oracle.  While I'm sure many things can be
ported over you seem to have stumbled upon one of those things that likely
will need to be written using PostgreSQL idioms.

​David J.​

Re: [GENERAL] Function error

[Sachin Srivastava]

Dear Albe,

Thanks for your support !!!

In my function the problem is that global variables defined inside the
function. These variables are visible to functions defined inside a
function. If we move these inner functions to outside of the main function,
they will lose the visibility of the global variables. So I think that's
why we have to correct this functions rather then creating it as
individual.

We have migrated our data from Oracle to Postgres through ORA2PG Tool after
that we are getting this error for this function.

Dear Adrian,

I have also used the PERFORM option but still it's not running.

Regards,
Sachin



On Thu, Jan 14, 2016 at 12:37 AM, Adrian Klaver 
wrote:

> On 01/13/2016 01:18 AM, Sachin Srivastava wrote:
> >
> > Dear Team,
> >
> > I am getting the below error for function, please see the bold line in
> > "Function code", please suggest what I will do to correct this code.
> >
> >   ---
> >
> > ERROR:  syntax error at or near "*"
> > LINE 35: SELECT * from logError(msg text) is
> >  ^
> > CONTEXT:  invalid type name "* from logError(msg text) is
> >
>
> >
> > *_Function Code as below:_*
> >
> >
> >
> --
> >
> >
> > -- Function: cp_property_room_count_trans(bigint, bigint, text)
> >
> > -- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
> >
> > CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
> >  subscriberid bigint,
> >  incompanyid bigint,
> >  loginname text)
> >RETURNS void AS
> > $BODY$
> > DECLARE
> >
> >
> >CRS_1 CURSOR FOR SELECT distinct company_id from
> > CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag
> > is null or process_flag=0)  order by company_id;
> >
> >--Version:
> >-- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
> >--   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
> >-- 06/08/07 (Bea) insert value for
> > CP_ROOM_TYPE_COUNT.room_budget_home_amt
> >--  If phaseID is new, validate that these as required
> > fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
> >--   Will set process_flag=2 if fail the validation.
> >--  CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated
> > per business rule 15327
> >--
> >
> >RW_1 RECORD; --CRS_1 %ROWTYPE;
> >err_msg_1 varchar(100);
> >v_errorMessage_1 varchar(4000);
> >sucessfulRecCount bigint :=0;
> >failedRecCount bigint :=0;
> >
> > *  --
> >SELECT logError(v_errorMesg text, procedureName text, subscriberID
> > bigint, companyID bigint, supplierSku text, loginName text) is
>
> I have not even tried to go through all of this function, but the above is
> a problem in and of itself.
>
> If you want to do something that returns no result then you need to use
> PERFORM:
>
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
>
> That stills the question of what ..'loginName text) is' is supposed to be
> doing?
>
> If you are trying to SELECT the result of logError into a  variable then
> you need to use INTO:
>
>
> http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
> If you are trying to do something else, then as Albe said, you need to let
> us know what that is?
>
>
> >
> > BEGIN
> >   insert into SMERROR_LOG
> >   (error_message, method_name, system_message, error_log_id,
> > subscriber_id, company_id, creation_date, creation_user, update_date,
> > update_user)
> >   values(v_errorMesg, procedureName, supplierSku,
> > nextval('smerror_log_sequence'), subscriberID, companyID,
> > LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
> >end;*
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Adrian Klaver]

On 01/13/2016 03:25 PM, Williamson, Michael wrote:

Ccing list


Is that happening on all triggers you are trying to DROP, or just
this one?


All, consistently.


If just this one, you might want to check to see if the trigger was
created with a quoted mixed case name and therefore needs to have
the
exact case quoted when used in the DROP TRIGGER. Something else to
look
at is whether there is more then one customer table and you need to
schema qualify the name.



The triggers are all being defined in unquoted lowercase.  Also, these
triggers happen call functions, but I doubt that is relevant.


So is the below the actual error you are getting:

Observed Output:
ERROR:  relation "udf_customer_update_trigger" does not exist



Michael




--
Adrian Klaver
adrian.kla...@aklaver.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] Code of Conduct: Is it time?

[Bruce Momjian]

On Tue, Jan  5, 2016 at 08:47:16AM -0800, Joshua Drake wrote:
> Hello,
> 
> I had a hard time writing this email. I think Code of Conducts are
> non-essential, a waste of respectful people's time and frankly if
> you are going to be a jerk, our community will call you out on it.
> Unfortunately a lot of people don't agree with that. I have over the
> course of the last year seen more and more potential users very
> explicitly say, "I will not contribute to a project or attend a
> conference that does not have a CoC".
> 
> Some of us may be saying, "Well we don't want those people". I can't
> argue with some facts though. Ubuntu has had a CoC[1] since the
> beginning of the project and they grew exceedingly quick. Having
> walls in the hallway of interaction isn't always a bad thing.
> 
> In reflection, the only thing a CoC does is put in writing what
> behaviour we as a project already require, so why not document it
> and use it as a tool to encourage more contribution to our project?

Just to give some context, the core team has quietly handled discipline
issues for years.  In fact, it was so quiet that no one really knew it
was happening, unless you were one of those people that core had to
discipline.  This secrecy caused people who felt they needed help with
unfair treatment to try to deal with discipline themselves, rather than
come to core.  The recognition of this behavior caused the creation of a
core responsibilities web page:

http://www.postgresql.org/developer/core/

I see a CoC as a way of codifying expected behavior in the same way the
"core responsibilities" document does.

It is also true that any document you create to try to fix bad behavior
can be abused, e.g. laws to compensate victims of carelessly unsafe
environments have yielded many unethical personal injury lawyers in the
USA.  Therefore, we need to be careful of negative CoC effects.

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

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
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] Moving a large DB (> 500GB) to another DB with different locale

[Jim Nasby]

On 1/13/16 2:39 PM, Andreas Joseph Krogh wrote:

Where can I find more info about how to use and configure pg_logical to
replicate a 9.4 DB to 9.5?


http://2ndquadrant.com/en/resources/pglogical/
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[David Rowley]

On 14 January 2016 at 12:08, Adrian Klaver 
wrote:

> On 01/13/2016 02:51 PM, David Rowley wrote:
>
>> On 14 January 2016 at 11:32, Adrian Klaver > > wrote:
>>
>> On 01/13/2016 02:24 PM, Tom Lane wrote:
>>
>> "Williamson, Michael" > > writes:
>>
>> I'm attempting to drop a trigger that may or may not exist,
>> so am using
>> the "IF EXISTS" clause. Â This works fine for tables, views,
>> functions,
>> domains, and types, but for some reason seems to be ignored
>> for
>> triggers. Â I'd expect to see more about this online if it
>> were a bug,
>> so I'm thinking I may be missing something obvious.
>>
>>
>> Example:
>> DROP TRIGGER IF EXISTS udf_customer_update_trigger ON
>> customer;
>>
>>
>> Expected Output:
>> NOTICE:Â Â trigger "udf_customer_update_trigger" does not
>> exist, skipping
>>
>>
>> Observed Output:
>> ERROR:Â Â relation "udf_customer_update_trigger" does not
>> exist
>>
>>
>> Environment:
>> CentOS 6.6
>> postgresql91-server-9.1.14-1PGDG.rhel6.x86_64
>>
>>
>> This has worked the way you're imagining since (I think) 9.4.
>> Before
>> that the "if exists" semantics only applied to the trigger itself,
>> not to the relation.
>>
>>
>> Alright now I am confused. Other then changing table to table_name I
>> am not seeing where the below changed. In both cases a NOTICE is
>> supposed to be raised.
>>
>> http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.html
>>
>> http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.html
>>
>>
>> Seems to have been changed in
>>
>> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c6cd0de1827ba58756e24e18110cf902182a
>>
>
> I will take a look at this at some point. The part that has me confused
> from the original post is this:
>
> ERROR:  relation "udf_customer_update_trigger" does not exist
>
> If the ERROR is because the table does not exist, why not?:
>
> ERROR:  relation "customer" does not exist


I assumed this part was a mistake in the post by Michael.  I tried this on
9.1.19 and I correctly get the name of the table rather than the name of
the trigger. So even in the unlikely event that this was a bug, it's
working in the latest 9.1 minor release:

# drop trigger if exists test_trigger on testtable;
ERROR:  relation "testtable" does not exist
# select version();
version



 PostgreSQL 9.1.19 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
5.2.1-22 ubuntu2) 5.2.1 20151010, 64-bit
(1 row)

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Adrian Klaver]

On 01/13/2016 02:51 PM, David Rowley wrote:

On 14 January 2016 at 11:32, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 01/13/2016 02:24 PM, Tom Lane wrote:

"Williamson, Michael" mailto:michael.william...@tamucc.edu>> writes:

I'm attempting to drop a trigger that may or may not exist,
so am using
the "IF EXISTS" clause. Â This works fine for tables, views,
functions,
domains, and types, but for some reason seems to be ignored for
triggers. Â I'd expect to see more about this online if it
were a bug,
so I'm thinking I may be missing something obvious.


Example:
DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;


Expected Output:
NOTICE:Â Â trigger "udf_customer_update_trigger" does not
exist, skipping


Observed Output:
ERROR:Â Â relation "udf_customer_update_trigger" does not exist


Environment:
CentOS 6.6
postgresql91-server-9.1.14-1PGDG.rhel6.x86_64


This has worked the way you're imagining since (I think) 9.4.
Before
that the "if exists" semantics only applied to the trigger itself,
not to the relation.


Alright now I am confused. Other then changing table to table_name I
am not seeing where the below changed. In both cases a NOTICE is
supposed to be raised.

http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.html

http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.html


Seems to have been changed in
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c6cd0de1827ba58756e24e18110cf902182a


I will take a look at this at some point. The part that has me confused 
from the original post is this:


ERROR:  relation "udf_customer_update_trigger" does not exist

If the ERROR is because the table does not exist, why not?:

ERROR:  relation "customer" does not exist



Perhaps that commit should have also made changes to the documents to
change things such as:

Do not throw an error if the trigger does not exist. A notice is issued
in this case.

To

Do not throw an error if the trigger or table does not exist. A notice
is issued in this case.

--
  David Rowley http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services



--
Adrian Klaver
adrian.kla...@aklaver.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] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[David Rowley]

On 14 January 2016 at 11:32, Adrian Klaver 
wrote:

> On 01/13/2016 02:24 PM, Tom Lane wrote:
>
>> "Williamson, Michael"  writes:
>>
>>> I'm attempting to drop a trigger that may or may not exist, so am using
>>> the "IF EXISTS" clause. Â This works fine for tables, views, functions,
>>> domains, and types, but for some reason seems to be ignored for
>>> triggers. Â I'd expect to see more about this online if it were a bug,
>>> so I'm thinking I may be missing something obvious.
>>>
>>
>> Example:
>>> DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;
>>>
>>
>> Expected Output:
>>> NOTICE:Â Â trigger "udf_customer_update_trigger" does not exist, skipping
>>>
>>
>> Observed Output:
>>> ERROR:Â Â relation "udf_customer_update_trigger" does not exist
>>>
>>
>> Environment:
>>> CentOS 6.6
>>> postgresql91-server-9.1.14-1PGDG.rhel6.x86_64
>>>
>>
>> This has worked the way you're imagining since (I think) 9.4.  Before
>> that the "if exists" semantics only applied to the trigger itself,
>> not to the relation.
>>
>
> Alright now I am confused. Other then changing table to table_name I am
> not seeing where the below changed. In both cases a NOTICE is supposed to
> be raised.
>
> http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.html
>
> http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.html


Seems to have been changed in
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c6cd0de1827ba58756e24e18110cf902182a

Perhaps that commit should have also made changes to the documents to
change things such as:

Do not throw an error if the trigger does not exist. A notice is issued in
this case.

To

Do not throw an error if the trigger or table does not exist. A notice is
issued in this case.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Adrian Klaver]

On 01/13/2016 02:24 PM, Tom Lane wrote:

"Williamson, Michael"  writes:

I'm attempting to drop a trigger that may or may not exist, so am using
the "IF EXISTS" clause. Â This works fine for tables, views, functions,
domains, and types, but for some reason seems to be ignored for
triggers. Â I'd expect to see more about this online if it were a bug,
so I'm thinking I may be missing something obvious.



Example:
DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;



Expected Output:
NOTICE:Â Â trigger "udf_customer_update_trigger" does not exist, skipping



Observed Output:
ERROR:Â Â relation "udf_customer_update_trigger" does not exist



Environment:
CentOS 6.6
postgresql91-server-9.1.14-1PGDG.rhel6.x86_64


This has worked the way you're imagining since (I think) 9.4.  Before
that the "if exists" semantics only applied to the trigger itself,
not to the relation.


Alright now I am confused. Other then changing table to table_name I am 
not seeing where the below changed. In both cases a NOTICE is supposed 
to be raised.


http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.html

http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.html



regards, tom lane





--
Adrian Klaver
adrian.kla...@aklaver.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] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Adrian Klaver]

On 01/13/2016 02:14 PM, Williamson, Michael wrote:

I'm attempting to drop a trigger that may or may not exist, so am using
the "IF EXISTS" clause.  This works fine for tables, views, functions,
domains, and types, but for some reason seems to be ignored for
triggers.  I'd expect to see more about this online if it were a bug,
so I'm thinking I may be missing something obvious.

Example:
DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;

Expected Output:
NOTICE:  trigger "udf_customer_update_trigger" does not exist, skipping

Observed Output:
ERROR:  relation "udf_customer_update_trigger" does not exist

Environment:
CentOS 6.6
postgresql91-server-9.1.14-1PGDG.rhel6.x86_64



Is that happening on all triggers you are trying to DROP, or just this one?

If just this one, you might want to check to see if the trigger was 
created with a quoted mixed case name and therefore needs to have the 
exact case quoted when used in the DROP TRIGGER. Something else to look 
at is whether there is more then one customer table and you need to 
schema qualify the name.




Thanks,
Michael




--
Adrian Klaver
adrian.kla...@aklaver.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] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Tom Lane]

"Williamson, Michael"  writes:
> I'm attempting to drop a trigger that may or may not exist, so am using
> the "IF EXISTS" clause.  This works fine for tables, views, functions,
> domains, and types, but for some reason seems to be ignored for
> triggers.  I'd expect to see more about this online if it were a bug,
> so I'm thinking I may be missing something obvious.

> Example:
> DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;

> Expected Output: 
> NOTICE:  trigger "udf_customer_update_trigger" does not exist, skipping

> Observed Output:
> ERROR:  relation "udf_customer_update_trigger" does not exist

> Environment:
> CentOS 6.6
> postgresql91-server-9.1.14-1PGDG.rhel6.x86_64

This has worked the way you're imagining since (I think) 9.4.  Before
that the "if exists" semantics only applied to the trigger itself,
not to the relation.

regards, tom lane


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

[GENERAL] v9.1, DROP TRIGGER IF EXISTS behaving oddly

[Williamson, Michael]

I'm attempting to drop a trigger that may or may not exist, so am using
the "IF EXISTS" clause.  This works fine for tables, views, functions,
domains, and types, but for some reason seems to be ignored for
triggers.  I'd expect to see more about this online if it were a bug,
so I'm thinking I may be missing something obvious.

Example:
DROP TRIGGER IF EXISTS udf_customer_update_trigger ON customer;

Expected Output: 
NOTICE:  trigger "udf_customer_update_trigger" does not exist, skipping

Observed Output:
ERROR:  relation "udf_customer_update_trigger" does not exist

Environment:
CentOS 6.6
postgresql91-server-9.1.14-1PGDG.rhel6.x86_64


Thanks,
Michael

-- 
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] Data Packaging/Data Unpacking

[oleg yusim]

Kevin,

It is understood. But doesn't change things from my perspective. It would
anyway be user, with sole access, nobody can elevate privileges to and only
root can su to. The name, in that case, is irrelevant, but answer postgres
gave me idea of the protection level here.

Thanks,

Oleg

On Wed, Jan 13, 2016 at 4:04 PM, Kevin Grittner  wrote:

> On Wed, Jan 13, 2016 at 3:54 PM, oleg yusim  wrote:
>
> > Answer "postgres" would suffice.
>
> But the user would not always be "postgres".  To be accurate, it is
> the user which owns the files for the "cluster" (database instance)
> and which runs the database service.  If a machine contains
> multiple clusters it is (IMO) best practice, for both security and
> operational reasons, to use a separate OS user for each cluster.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [GENERAL] Data Packaging/Data Unpacking

[Kevin Grittner]

On Wed, Jan 13, 2016 at 3:54 PM, oleg yusim  wrote:

> Answer "postgres" would suffice.

But the user would not always be "postgres".  To be accurate, it is
the user which owns the files for the "cluster" (database instance)
and which runs the database service.  If a machine contains
multiple clusters it is (IMO) best practice, for both security and
operational reasons, to use a separate OS user for each cluster.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Data Packaging/Data Unpacking

[oleg yusim]

David,

Answer "postgres" would suffice. I have fairly decent idea of what happens
below that (you are right system utility - Memory Manager is what
marshaling data back and forth to RAM, abstracting absolute addresses from
application along the way, and once in RAM, security kernel of the system
protects data, terminating any process which tries to obtain data from
another process - provided data were not shared to begin with).

Thanks a lot for your consultation guys.

Oleg

On Wed, Jan 13, 2016 at 3:49 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jan 13, 2016 at 2:37 PM, oleg yusim  wrote:
>
>> OK, Kevin, David,
>>
>> Thanks you very much for explanation. Now who is the owner of this
>> process? My understanding is, data then located physically in RAM, in the
>> memory stack assigned by OS to this process. Now the question is who owns
>> the process?
>>
>>
> Who owns the PostgreSQL backend user process?​
>
> ​Typically the ​"postgres" user.
> Who owns the "process" responsible for marshalling data back and forth
> from RAM?  There isn't really a concept of "process" when dealing with
> system resources.  The kernel is responsible for management of those
> resources.  Processes are a user-space concern.
>
> There may be some here concerned with the deep workings of the various O/S
> that PostgreSQL runs on but ultimately the answer, from PostgreSQL's
> perspective, is that we use system APIs to request and use resources and
> expect the underlying system to handle those securely.  If you want to know
> how that layer works you should ask there since it seems out-of-scope for
> this forum.
>
> David J.
>
>

Re: [GENERAL] Data Packaging/Data Unpacking

[David G. Johnston]

On Wed, Jan 13, 2016 at 2:37 PM, oleg yusim  wrote:

> OK, Kevin, David,
>
> Thanks you very much for explanation. Now who is the owner of this
> process? My understanding is, data then located physically in RAM, in the
> memory stack assigned by OS to this process. Now the question is who owns
> the process?
>
>
Who owns the PostgreSQL backend user process?​

​Typically the ​"postgres" user.
Who owns the "process" responsible for marshalling data back and forth from
RAM?  There isn't really a concept of "process" when dealing with system
resources.  The kernel is responsible for management of those resources.
Processes are a user-space concern.

There may be some here concerned with the deep workings of the various O/S
that PostgreSQL runs on but ultimately the answer, from PostgreSQL's
perspective, is that we use system APIs to request and use resources and
expect the underlying system to handle those securely.  If you want to know
how that layer works you should ask there since it seems out-of-scope for
this forum.

David J.

Re: [GENERAL] WIP: CoC V5

[Joshua D. Drake]

On 01/13/2016 01:27 PM, Tom Lane wrote:

Steve Litt  writes:

To me it's simple...
Disallow "You "
Disallow "Your code "
Encourage "It would be better if your code  because
."


That's fine as practice, but I don't think we want to get anywhere near
being that detailed in the CoC per se.  If we start trying to write that
sort of rule, the CoC will be multiple pages long and no one will read it.
(I thought Kevin's last draft was already too long.)


+1



regards, tom lane





--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
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] Data Packaging/Data Unpacking

[oleg yusim]

OK, Kevin, David,

Thanks you very much for explanation. Now who is the owner of this process?
My understanding is, data then located physically in RAM, in the memory
stack assigned by OS to this process. Now the question is who owns the
process?

Thanks,

Oleg

On Wed, Jan 13, 2016 at 3:29 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jan 13, 2016 at 2:19 PM, Kevin Grittner  wrote:
>
>> On Wed, Jan 13, 2016 at 2:57 PM, oleg yusim  wrote:
>>
>> > Say, I got network package. The package was decrypted by OpenSSL. Where
>> this
>> > data are, physically, at this moment?
>>
>> Process-local memory for the PostgreSQL backend process associated
>> with the database connection.
>>
>> > Opposite situation: we are about to send results of SELECT statement
>> over
>> > the OpenSSL tunnel. Where are the data, physically, between the moment
>> DB
>> > engine returned results for my query and moment the package was
>> encrypted by
>> > OpenSSL?
>>
>> Process-local memory for the PostgreSQL backend process associated
>> with the database connection.
>>
>
> ​In other words (?) while OpenSSL may be a separate project the method of
> its use involves loading all relevant code into the process space​
>
> ​of PostgreSQL - specifically the process that was forked when the SSL
> client connection was established.
>
> David J.​
>

Re: [GENERAL] plpython3 package absent in 9.5 repository

[Devrim GÜNDÜZ]

Hi Clodoaldo,

It is a shame that I totally skipped that. Thanks for the report.

I am working on the patch now. I will commit it as soon as it is ready.

Regards, Devrim


 On Wed, 2016-01-13 at 10:44 -0200, Clodoaldo Neto wrote:
> I don't know the policy of package inclusion in the repositories. The
> plpython3 package exists in the Fedora repository:
> 
> http://mirror.globo.com/fedora/linux/updates/22/x86_64/p/
> 
> But it does not exist in the Postgresql repository:
> 
> https://download.postgresql.org/pub/repos/yum/9.5/fedora/fedora-22-x8
> 6_64/
> 
> Or as source:
> 
> https://download.postgresql.org/pub/repos/yum/srpms/9.5/fedora/fedora
> -22-x86_64/
> 
> Where to get the plpython3 packages for 9.5?
> 
> Regards, Clodoaldo

-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR




signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Data Packaging/Data Unpacking

[David G. Johnston]

On Wed, Jan 13, 2016 at 2:19 PM, Kevin Grittner  wrote:

> On Wed, Jan 13, 2016 at 2:57 PM, oleg yusim  wrote:
>
> > Say, I got network package. The package was decrypted by OpenSSL. Where
> this
> > data are, physically, at this moment?
>
> Process-local memory for the PostgreSQL backend process associated
> with the database connection.
>
> > Opposite situation: we are about to send results of SELECT statement over
> > the OpenSSL tunnel. Where are the data, physically, between the moment DB
> > engine returned results for my query and moment the package was
> encrypted by
> > OpenSSL?
>
> Process-local memory for the PostgreSQL backend process associated
> with the database connection.
>

​In other words (?) while OpenSSL may be a separate project the method of
its use involves loading all relevant code into the process space​

​of PostgreSQL - specifically the process that was forked when the SSL
client connection was established.

David J.​

Re: [GENERAL] WIP: CoC V5

[Tom Lane]

Steve Litt  writes:
> To me it's simple...
> Disallow "You "
> Disallow "Your code "
> Encourage "It would be better if your code  because
> ."

That's fine as practice, but I don't think we want to get anywhere near
being that detailed in the CoC per se.  If we start trying to write that
sort of rule, the CoC will be multiple pages long and no one will read it.
(I thought Kevin's last draft was already too long.)

regards, tom lane


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

[John Wiencek]

Thank you all for the quick replies.

John Wiencek

On 1/13/16, 2:43 PM, "Thomas Munro"  wrote:

>On Thu, Jan 14, 2016 at 9:34 AM, Joshua D. Drake 
>wrote:
>> On 01/13/2016 12:28 PM, jwienc...@comcast.net wrote:
>>>
>>> Is it possible to set up synchronous replication to more that one node
>>> in a cluster?  Or, am I limited to one synchronous node and one
>>> asynchronous node?
>>
>>
>> Yes you can have N number of synchronous slaves. However, be careful.
>>It can
>> be a huge performance hit.
>
>Note that only one of the listed standbys is a synchronous standby at
>any given time though.  That is, when you commit, the primary server
>will wait just for that one server to report that it has fsync'ed the
>WAL.  (There is a patch being developed to change that so that you
>might be able to wait for more than one in a future release).
>
>-- 
>Thomas Munro
>http://www.enterprisedb.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] Data Packaging/Data Unpacking

[Kevin Grittner]

On Wed, Jan 13, 2016 at 2:57 PM, oleg yusim  wrote:

> Say, I got network package. The package was decrypted by OpenSSL. Where this
> data are, physically, at this moment?

Process-local memory for the PostgreSQL backend process associated
with the database connection.

> Opposite situation: we are about to send results of SELECT statement over
> the OpenSSL tunnel. Where are the data, physically, between the moment DB
> engine returned results for my query and moment the package was encrypted by
> OpenSSL?

Process-local memory for the PostgreSQL backend process associated
with the database connection.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Unable to build python extension with PGXS

[Jim Nasby]

On 1/12/16 10:04 PM, Jim Nasby wrote:

Attempting to build a python extension, I'm getting:

Undefined symbols for architecture x86_64:
   "_PyErr_Clear", referenced from:
   _PLyNdarray_FromDatum in pg_ndarray.o
   _PLyObject_To_ndarray in pg_ndarray.o
   "_PyImport_ImportModule", referenced from:
   _PLyNdarray_FromDatum in pg_ndarray.o
   _PLyObject_To_ndarray in pg_ndarray.o
   "_PyObject_CallFunction", referenced from:
   _PLyNdarray_FromDatum in pg_ndarray.o
   _PLyObject_To_ndarray in pg_ndarray.o
   "_PyObject_GetAttrString", referenced from:
   _PLyNdarray_FromDatum in pg_ndarray.o
   _PLyObject_To_ndarray in pg_ndarray.o
   "_PyString_AsString", referenced from:
   _PLyObject_To_ndarray in pg_ndarray.o
   "_PyString_FromStringAndSize", referenced from:
   _PLyNdarray_FromDatum in pg_ndarray.o
   "_PyString_Size", referenced from:
   _PLyObject_To_ndarray in pg_ndarray.o
ld: symbol(s) not found for architecture x86_64

I've included $(python_includespec) in my Makefile:

override CPPFLAGS := $(python_includespec) $(CPPFLAGS)

Is there some other magic I need? Do I need to switch to using
MODULE_big or something?


After some Screwing Around(TM), I figured out that the magic trick is to 
add the output of python-config --ldflags to LDFLAGS. I see we have a 
$(perl_embed_ldflags), should there be something similar for python?

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] WIP: CoC V5

[Steve Litt]

On Wed, 13 Jan 2016 09:15:45 -0500
"Regina Obe"  wrote:

 
> For example if Tom makes some snide remark like "Do all Bostonians
> program this way?"

Why not simply discuss the code, with no value judgment about the
coder?

"The strcpy() in the foo() function can cause intermittent problems and
open an attack route. Why not use strncpy() instead?"

Of course we all know there are some people who prefer to say something
like the following:

"Really? I mean really? People still use strcpy() in 2016? All but the
dullest know that opens an attack route."

For some reason, a significant percentage of people just LOVE to get
judgmental about the other guy's work product, rather than simply
showing a better way.

To me it's simple...

Disallow "You "

Disallow "Your code "

Encourage "It would be better if your code  because
."

Steve

Steve Litt 
January 2016 featured book: Twenty Eight Tales of Troubleshooting
http://www.troubleshooters.com/28




-- 
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] Data Packaging/Data Unpacking

[oleg yusim]

Kevin,

Thank you for your reply. I understand what you are saying, but I guess I
need a bit deeper understanding for my assessment. Let's dive a bit here:

Say, I got network package. The package was decrypted by OpenSSL. Where
this data are, physically, at this moment?

Opposite situation: we are about to send results of SELECT statement over
the OpenSSL tunnel. Where are the data, physically, between the moment DB
engine returned results for my query and moment the package was encrypted
by OpenSSL?

Thanks,

Oleg

On Wed, Jan 13, 2016 at 2:46 PM, Kevin Grittner  wrote:

> On Tue, Jan 12, 2016 at 10:00 PM, oleg yusim  wrote:
>
> > Important: let's assume data at rest is encrypted using EFS and data at
> > transit is encrypted using ciphers, provided by OpenSSL.
> >
> > So, with that in mind, please, help me to understand movement and
> location
> > of the data between the moment when it is pulled from file system and
> > encrypted as network package going through the SSL tunnel.
> >
> > And reversing it - between the moment network package arrived through the
> > SSL tunnel is decrypted and the moment its content is placed into the
> file
> > system.
>
> At all times the data is present only in files owned by the OS user
> which runs the database server or in RAM allocated to processes run
> by that user.  Files and RAM are freed without overwrite; we count
> on the OS to not gratuitously show the old values to processes
> making new allocations.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [GENERAL] Data Packaging/Data Unpacking

[Kevin Grittner]

On Tue, Jan 12, 2016 at 10:00 PM, oleg yusim  wrote:

> Important: let's assume data at rest is encrypted using EFS and data at
> transit is encrypted using ciphers, provided by OpenSSL.
>
> So, with that in mind, please, help me to understand movement and location
> of the data between the moment when it is pulled from file system and
> encrypted as network package going through the SSL tunnel.
>
> And reversing it - between the moment network package arrived through the
> SSL tunnel is decrypted and the moment its content is placed into the file
> system.

At all times the data is present only in files owned by the OS user
which runs the database server or in RAM allocated to processes run
by that user.  Files and RAM are freed without overwrite; we count
on the OS to not gratuitously show the old values to processes
making new allocations.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[Thomas Munro]

On Thu, Jan 14, 2016 at 9:34 AM, Joshua D. Drake  wrote:
> On 01/13/2016 12:28 PM, jwienc...@comcast.net wrote:
>>
>> Is it possible to set up synchronous replication to more that one node
>> in a cluster?  Or, am I limited to one synchronous node and one
>> asynchronous node?
>
>
> Yes you can have N number of synchronous slaves. However, be careful. It can
> be a huge performance hit.

Note that only one of the listed standbys is a synchronous standby at
any given time though.  That is, when you commit, the primary server
will wait just for that one server to report that it has fsync'ed the
WAL.  (There is a patch being developed to change that so that you
might be able to wait for more than one in a future release).

-- 
Thomas Munro
http://www.enterprisedb.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] Moving a large DB (> 500GB) to another DB with different locale

[Andreas Joseph Krogh]

På onsdag 13. januar 2016 kl. 19:18:20, skrev Jim Nasby <
jim.na...@bluetreble.com >:
On 1/13/16 2:03 AM, Andreas Joseph Krogh wrote:
 > Seems like pglogical migth be better?
 > http://2ndquadrant.com/en/resources/pglogical/
 > It's available for 9.4 also.

 It would certainly be faster. It's also less tested than Slony is
 though, as it's fairly new. If it was me, I'd use pg_logical.
 --
 Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
 Experts in Analytics, Data Architecture and PostgreSQL
 Data in Trouble? Get it in Treble! http://BlueTreble.com
 
Where can I find more info about how to use and configure pg_logical to 
replicate a 9.4 DB to 9.5?
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Synchronous replication

[Joshua D. Drake]

On 01/13/2016 12:28 PM, jwienc...@comcast.net wrote:

Is it possible to set up synchronous replication to more that one node
in a cluster?  Or, am I limited to one synchronous node and one
asynchronous node?


Yes you can have N number of synchronous slaves. However, be careful. It 
can be a huge performance hit.




Assuming my replicated databases are on nodes:   testqa1, testqa2.  What
would my synchronous_standby_names entry in my postgresql.conf on my
master database?


The IP address that each is binding to.

JD
--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


--
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] New Slave - timeline ERROR

[drum.lu...@gmail.com]

Hi guys..
I started a new PG_BASEBACKUP and it's working now..

The problem was the line: standby_mode = on on the recovery.conf  on the
STANDBY server.

After the basebackup, I comented this line and started the postgreSQL. BUT,
you shouldn't  do that.

On the last time I didn't  comment and it worked.

Thank you!



Lucas Possamai

kinghost.co.nz


On 10 January 2016 at 19:22, drum.lu...@gmail.com 
wrote:

> What is the --pgdata=- in your original command? Are you perhaps in the
>> wrong directory and not getting all the required files?
>
>
> I run the pg_basebackup from the Slave on /var/lib/pgsql/9.2/data.
> So I'm not in the wrong directory...
>
> I'm out of fresh ideas. The rsync command is what I would go with, given
>> that I can't think of any other commands to try.
>
>
> I chose the pg_basebackup command just to not stop any database. It's out
> of circumstances to stop even the slave one... sorry...
>
> I really don't know what else to do. Have tried everything!
>
> Lucas
>
> On 10 January 2016 at 13:31, bricklen  wrote:
>
>> Bottom-posting is the convention in the postgresql lists, and makes it
>> easier to follow a long thread.
>>
>> On Sat, Jan 9, 2016 at 3:16 PM, drum.lu...@gmail.com <
>> drum.lu...@gmail.com> wrote:
>>
>>> My servers are not in the same network. A new pg_backup would take 30
>>> hours to complete as I use --rate-limit 100MB.
>>
>>
>> If you had enough bandwidth, you could do some shell magic to parallelize
>> the rsync commands, or use something like
>> http://moo.nac.uci.edu/~hjm/parsync/ to do that. If you are limited by
>> bandwidth, then a single rsync run is probably what you're stuck with.
>>
>>
>>> I really need to put his server up! =\
>>>
>>
>> If you were running zfs you could also take a snapshot of the fs and use
>> that for your base backup, but I assume you would have mentioned that if it
>> was an option.
>>
>>
>>
>>> I don't think that running a pg_basebackup one more time will solve the
>>> problem, because I've already done that!
>>> I could run actually, but the problem is that it takes 30h! hahahahah
>>>
>>
>> What is the --pgdata=- in your original command? Are you perhaps in the
>> wrong directory and not getting all the required files?
>>
>>
>> I'm out of fresh ideas. The rsync command is what I would go with, given
>> that I can't think of any other commands to try.
>>
>>
>>
>>>
>>> *Have a look:*
>>> http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html
>>>
>>> Note that there are some limitations in an online backup from the
 standby:

>>>
>>>
>>> The backup history file is not created in the database cluster backed up.
 There is no guarantee that all WAL files required for the backup are
 archived at the end of backup. If you are planning to use the backup for an
 archive recovery and want to ensure that all required files are available
 at that moment, you need to include them into the backup by using -x
  option.

>>>
>> You had that in your original command I believe.
>>
>
>

Re: [GENERAL] pg_dump problem with dropped NOT NULL on child table

[Tom Lane]

Karsten Hilbert  writes:
> Please consider the following:

>   create table parent (
>   not_null_in_parent integer not null
>   );

>   create table child() inherits (parent);
>   alter table child
>   alter column not_null_in_parent
>   drop not null
>   ;

> Is this a bug or am I doing things I shouldn't hope work ?

You should not expect this to work; sooner or later we will make
the backend reject it.  See
http://www.postgresql.org/message-id/21633.1448383...@sss.pgh.pa.us

Alvaro or someone had a WIP patch to track NOT NULL constraints in
pg_constraint, which is the bookkeeping we'd need to deal with this
sort of thing properly.  I'm not sure what the status of it is.

In the meantime, you could get the effect you want if the parent
were marked with CHECK (not_null_in_parent IS NOT NULL) NO INHERIT.

regards, tom lane


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

[GENERAL] Synchronous replication

[jwiencek3]

Is it possible to set up synchronous replication to more that one node in a 
cluster? Or, am I limited to one synchronous node and one asynchronous node? 

Assuming my replicated databases are on nodes: testqa1, testqa2. What would my 
synchronous_standby_names entry in my postgresql.conf on my master database? 


Regards 

John Wiencek 

[GENERAL] Exclude bdr data from dump

[Roland van Laar]

Hello,

I want to exclude all bdr data from a database dump.
My command is:

$ bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data 
--data-only --exclude-table='bdr*


This results in bdr data being included.

Including only bdr results in an error:

$ bdr_dump -Fp -h localhost -U postgres mydb -f /tmp/mydb.data 
--data-only --table='bdr*'

bdr_dump: No matching tables were found

Please help.

Roland



--
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_dump problem with dropped NOT NULL on child table

[Adrian Klaver]

On 01/13/2016 11:38 AM, Karsten Hilbert wrote:

Please consider the following:

- Debian
- server is PG 9.4.5
- client (psql/pg_dump/libpq5) is 9.5

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;





--

Is this a bug or am I doing things I shouldn't hope work ?


The latter if I am following the below correctly:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

"All check constraints and not-null constraints on a parent table are 
automatically inherited by its children. Other types of constraints 
(unique, primary key, and foreign key constraints) are not inherited."




I noticed this during a recent 9.4 -> 9.5 pg_upgradecluster
attempt with actual data in "child" violating-upon-restore
the newly created NOT NULL constraint on "child" when COPYing.

Thanks,
Karsten




--
Adrian Klaver
adrian.kla...@aklaver.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] Call postgres PL/Python stored function from another PL/Python block.

[lodopidolo]

Ok, thank you very much.

2016-01-13 19:04 GMT+01:00 Adrian Klaver :

> On 01/13/2016 07:47 AM, lodopidolo wrote:
>
>> Hello. It is possible to call al PL/Python stored function natively from
>> another PL/Python function?
>>
>> Something like:
>>
>> |createorreplace functionf1()returns text as$$return"hello"$$language
>> 'plpython3u';|
>>
>> |do $$begin...t =f1()...end;$$language 'plpython3u';|
>>
>>
>> Regards.
>>
>> (this question has been made in
>>
>> http://stackoverflow.com/questions/34764665/call-postgres-pl-python-stored-function-from-another-pl-python-block
>> too).
>>
>
> In addition to the other answers there is the option of just creating the
> function in a Python module outside Postgres and doing:
>
> from some_module import f
>
> Which would seem to address this(from the SO post):
>
> "This can be done using t = plpy.execute("select f1()"), but I want, if it
> is possible, call it as a normal Python function to avoid type conversions
> (for example jsonb, etc)."
>
> The downside being you have a dependency outside the database.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

[GENERAL] pg_dump problem with dropped NOT NULL on child table

[Karsten Hilbert]

Please consider the following:

- Debian
- server is PG 9.4.5
- client (psql/pg_dump/libpq5) is 9.5

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

Resulting in (as expected):

postgres@hermes:/tmp$ psql -d test
Ausgabeformat ist „wrapped“.
psql (9.5.0, Server 9.4.5)
Geben Sie „help“ für Hilfe ein.

test=# \d parent
 Tabelle „public.parent“
   Spalte   |   Typ   | Attribute
+-+---
 not_null_in_parent | integer | not null
Anzahl Kindtabellen: 1 (Mit \d+ alle anzeigen.)

test=# \d child
  Tabelle „public.child“
   Spalte   |   Typ   | Attribute
+-+---
 not_null_in_parent | integer |
Erbt von: parent

But getting dumped as (note the re-appearing NOT NULL
constraint on child):

--
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: parent; Type: TABLE; Schema: public; Owner: postgres; 
Tablespace: 
--

CREATE TABLE parent (
not_null_in_parent integer NOT NULL
);


ALTER TABLE parent OWNER TO postgres;

--
-- Name: child; Type: TABLE; Schema: public; Owner: postgres; 
Tablespace: 
--

CREATE TABLE child (
)
INHERITS (parent);


ALTER TABLE child OWNER TO postgres;

--
-- Data for Name: child; Type: TABLE DATA; Schema: public; Owner: 
postgres
--

COPY child (not_null_in_parent) FROM stdin;
\.


--
-- Data for Name: parent; Type: TABLE DATA; Schema: public; Owner: 
postgres
--

COPY parent (not_null_in_parent) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

--

Is this a bug or am I doing things I shouldn't hope work ?

I noticed this during a recent 9.4 -> 9.5 pg_upgradecluster
attempt with actual data in "child" violating-upon-restore
the newly created NOT NULL constraint on "child" when COPYing.

Thanks,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Blocked updates and background writer performance

[Cory Tucker]

On Wed, Jan 13, 2016 at 9:48 AM Vick Khera  wrote:

> That was my intuition too. Not enough I/O available from the hardware for
> the workload requested.
>
> As recommended, log your checkpoints and try tuning them to spread the
> load.
>

Thanks guys, will turn on checkpoint logging and try to sniff this out
further.

Re: [GENERAL] Function error

[Adrian Klaver]

On 01/13/2016 01:18 AM, Sachin Srivastava wrote:
> 
> Dear Team,
> 
> I am getting the below error for function, please see the bold line in 
> "Function code", please suggest what I will do to correct this code.
> 
>   ---
> 
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
>  ^
> CONTEXT:  invalid type name "* from logError(msg text) is
> 

> 
> *_Function Code as below:_*
> 
> 
> --
> 
> 
> -- Function: cp_property_room_count_trans(bigint, bigint, text)
> 
> -- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
> 
> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
>  subscriberid bigint,
>  incompanyid bigint,
>  loginname text)
>RETURNS void AS
> $BODY$
> DECLARE
> 
> 
>CRS_1 CURSOR FOR SELECT distinct company_id from 
> CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag 
> is null or process_flag=0)  order by company_id;
> 
>--Version:
>-- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
>--   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
>-- 06/08/07 (Bea) insert value for 
> CP_ROOM_TYPE_COUNT.room_budget_home_amt
>--  If phaseID is new, validate that these as required 
> fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
>--   Will set process_flag=2 if fail the validation.
>--  CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated 
> per business rule 15327
>--
> 
>RW_1 RECORD; --CRS_1 %ROWTYPE;
>err_msg_1 varchar(100);
>v_errorMessage_1 varchar(4000);
>sucessfulRecCount bigint :=0;
>failedRecCount bigint :=0;
> 
> *  --
>SELECT logError(v_errorMesg text, procedureName text, subscriberID 
> bigint, companyID bigint, supplierSku text, loginName text) is

I have not even tried to go through all of this function, but the above is a 
problem in and of itself.

If you want to do something that returns no result then you need to use PERFORM:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

That stills the question of what ..'loginName text) is' is supposed to be doing?

If you are trying to SELECT the result of logError into a  variable then you 
need to use INTO:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

If you are trying to do something else, then as Albe said, you need to let us 
know what that is?


> 
> BEGIN
>   insert into SMERROR_LOG
>   (error_message, method_name, system_message, error_log_id, 
> subscriber_id, company_id, creation_date, creation_user, update_date, 
> update_user)
>   values(v_errorMesg, procedureName, supplierSku, 
> nextval('smerror_log_sequence'), subscriberID, companyID, 
> LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>end;*



-- 
Adrian Klaver
adrian.kla...@aklaver.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] Query Questions - PostgreSQL

[Saulo Merlo]

Anyone who can help with this please?
Thanks

Sent from my phone

> On 13/01/2016, at 3:30 PM, Saulo Merlo  wrote:
> 
> So.. I have a Query that is taking too long to complete.
> 
> OLD QUERY:
> 
>>   SELECT
> 
>>   file.inode_idAS file_id,
> 
>>   file.parent_inode_id AS file_group,
> 
>>   file.relative_path   AS file_type,
> 
>>   file.file_data   AS file_binary,
> 
>>   file.node_full_path  AS file_name,
> 
>>   file.last_modified   AS date_created
> 
>> FROM
> 
>>   gorfs.nodes AS file
> 
>>   INNER JOIN
> 
>>   gorfs.inode_segments AS iseg ON iseg.st_ino = file.parent_inode_id
> 
>>   AND file.object_type = 'S_IFREG'
> 
>>   AND iseg.nfs_migration_date IS NULL
> 
>>   AND (file.last_modified <
> 
>>(transaction_timestamp() AT TIME ZONE 
>> 'UTC' - '1 months' :: INTERVAL))
> 
>>   AND iseg.st_ino_target = file.inode_id
> 
>> LIMIT
> 
>>   100;
> 
> 
> We created a new temporary table to store migrations, which may be the best 
> option (no longer need to join new columns in query).
> I just need the same output as any of the correct above one.
> 
> NEW QUERY STRUCTURE:
> 
> table: gorfs.nfs_data:
> 
>> CREATE TABLE gorfs.nfs_data
> 
>> (
> 
>>   owner_id integer NOT NULL,
> 
>>   file_id integer NOT NULL,
> 
>>   migration_path "text",
> 
>>   migration_date timestamp with time zone,
> 
>>   CONSTRAINT nfs_data_pkey PRIMARY KEY ("file_id")
> 
>> )
> 
> 
> INDEX: 
> 
>> CREATE INDEX ix_nfs_data_owner_id
> 
>>   ON gorfs.nfs_data
> 
>>   USING btree
> 
>>   ("owner_id")
> 
>>   WHERE "migration_date" IS NULL;
> 
> 
> 
> OLD EXPLAIN ANALYZE (Using the OLD query):
> Link: http://explain.depesz.com/s/Swu
> 
> COLUMNS:
> ALTER TABLE gorfs.nfs_data ADD COLUMN owner_id integer;
> ALTER TABLE gorfs.nfs_data ALTER COLUMN owner_id SET NOT NULL;
> ALTER TABLE gorfs.nfs_data ADD COLUMN file_id integer;
> ALTER TABLE gorfs.nfs_data ALTER COLUMN file_id SET NOT NULL;
> ALTER TABLE gorfs.nfs_data ADD COLUMN migration_path "text";
> ALTER TABLE gorfs.nfs_data ADD COLUMN migration_date timestamp with time zone;
> ALTER TABLE gorfs.nfs_data
>   ADD CONSTRAINT nfs_data_pkey PRIMARY KEY("file_id");
> 
> 
> QUESTION:
> How could I create the Query?
> Any help would be great.
> 
> Thank you!

[GENERAL] Fwd: Data Packaging/Data Unpacking

[oleg yusim]

Appologies, for posting it again, but I didn't get any responses so far.
Looks like I posted it too late in the evening and it went not noticed.

Oleg

-- Forwarded message --
From: oleg yusim 
Date: Tue, Jan 12, 2016 at 10:00 PM
Subject: Data Packaging/Data Unpacking
To: PostgreSQL General 


Greetings,

I have matching couple of security requirements, speaking about preserving
data confidentiality and integrity in PostgreSQL DB during packaging for
transmission / unpacking from transmission.

Important: let's assume data at rest is encrypted using EFS and data at
transit is encrypted using ciphers, provided by OpenSSL.

So, with that in mind, please, help me to understand movement and location
of the data between the moment when it is pulled from file system and
encrypted as network package going through the SSL tunnel.

And reversing it - between the moment network package arrived through the
SSL tunnel is decrypted and the moment its content is placed into the file
system.



For those interested, here are requirements themselves, quoted:

1) The DBMS must maintain the confidentiality and integrity of information
during preparation for transmission.

Information can be either unintentionally or maliciously disclosed or
modified during preparation for transmission, including, for example,
during aggregation, at protocol transformation points, and during
packing/unpacking. These unauthorized disclosures or modifications
compromise the confidentiality or integrity of the information.

Use of this requirement will be limited to situations where the data owner
has a strict requirement for ensuring data integrity and confidentiality is
maintained at every step of the data transfer and handling process.

When transmitting data, the DBMS, associated applications, and
infrastructure must leverage transmission protection mechanisms.

2) The DBMS must maintain the confidentiality and integrity of information
during reception.

Information can be either unintentionally or maliciously disclosed or
modified during reception, including, for example, during aggregation, at
protocol transformation points, and during packing/unpacking. These
unauthorized disclosures or modifications compromise the confidentiality or
integrity of the information.

This requirement applies only to those applications that are either
distributed or can allow access to data non-locally. Use of this
requirement will be limited to situations where the data owner has a strict
requirement for ensuring data integrity and confidentiality is maintained
at every step of the data transfer and handling process.

When receiving data, the DBMS, associated applications, and infrastructure
must leverage protection mechanisms.


Thanks,

Oleg

Re: [GENERAL] plv8 binaries for PostgreSQL 9.5 on Windows

[Adrian Klaver]

On 01/13/2016 08:25 AM, Igal @ Lucee.org wrote:

On 1/12/2016 12:57 PM, Adrian Klaver wrote:

On 01/11/2016 05:50 PM, Igal @ Lucee.org wrote:

Where can I find binaries for plv8 for PostgreSQL 9.5 for Windows?


The only Windows binaries I could find where for 9.4, here:

http://www.postgresonline.com/journal/archives/341-PLV8-binaries-for-PostgreSQL-9.4-windows-both-32-bit-and-64-bit.html


Maybe you could contact the authors and see if they are going to build
9.5 versions?

Yes, that's where I found the binaries for 9.4 which worked very nicely.

The post cites Leo Hsu and Regina Obe (I actually have a couple of their
books) as the authors, and judging by the replies to comments it looks
like Regina is the one who posted it.

I believe that I've seen some posts by her in the mailing list here
recently so hopefully she will see this thread and comment on it.


Or you could email her directly.



Thanks!






--
Adrian Klaver
adrian.kla...@aklaver.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] BDR install broken on Ubuntu 14.04

[Roland van Laar]

On 13-01-16 17:48, LOUBRADOU, Rémy wrote:

Hi,

I have the same issue. It was working fine a month ago. I wrote a 
cookbook with chef to install BDR a month ago since then I did not 
change the cookbook but now I got this error.


This seem to be the heart of the issue:

Removing obsolete dictionary files:
==> ldev4:  * No PostgreSQL clusters exist; see "man pg_createcluster"
==> ldev4: /usr/share/postgresql-common/maintscripts-functions: line 
95: db_stop: command not found
==> ldev4: dpkg: error processing package postgresql-bdr-9.4 
(--configure):
==> ldev4:  subprocess installed post-installation script returned 
error exit status 127



Anyone know what's going on ? I'm going to investigate.


The debconf source file is missing.
db_stop, stands for debconf stop.

It is supposed to be fixed but it is not.
See this closed issue on Github: 
https://github.com/2ndQuadrant/bdr/issues/156


I "fixed" it in ansible by

- installing bdr accepting the fail
- removing the db_stop line
- installing bdr again.

This is the relevant part of my ansible configuration:

- name: install postgresql bdr
  apt:
name=postgresql-bdr-9.4-bdr-plugin
state=present
  ignore_errors: yes

- name: remove db_stop from postgres postinstall script
  lineinfile:
dest=/usr/share/postgresql-common/maintscripts-functions
regexp=db_stop
state=absent

- name: reinstall postgresql bdr to mitigate the db_stop problem
  apt:
name=postgresql-bdr-9.4-bdr-plugin
state=present

Regards,

Roland



Thanks for your replies,

Rémy





--
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] Changing varchar length by manipulating pg_attribute

[Jim Nasby]

On 1/13/16 5:59 AM, Christian Ramseyer wrote:

UPDATE pg_attribute SET atttypmod = 35+4   -- new desired length + 4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';


I don't know of any reason that wouldn't work. Note that you might have 
to make the same change to all the views too.



Is this safe to do in Postgres 9.4? Also, best practice seems to be to
use text nowadays, is there even a variant of this that lets me convert


FWIW, I prefer using varchar with a fairly large limit unless the field 
really does need to be unlimited. That protects against bad code or a 
malicious user filling your database with garbage.



a "column from character varying(256)" to "text" without having to
recreate all the nested views?


You could probably change pg_attribute.atttypid to 'text'::regtype. You 
should change atttypmod to -1 at the same time if you do that.


Obviously you should test all of this thoroughly before doing it in 
production.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Moving a large DB (> 500GB) to another DB with different locale

[Jim Nasby]

On 1/13/16 2:03 AM, Andreas Joseph Krogh wrote:

Seems like pglogical migth be better?
http://2ndquadrant.com/en/resources/pglogical/
It's available for 9.4 also.


It would certainly be faster. It's also less tested than Slony is 
though, as it's fairly new. If it was me, I'd use pg_logical.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] Call postgres PL/Python stored function from another PL/Python block.

[Adrian Klaver]

On 01/13/2016 07:47 AM, lodopidolo wrote:

Hello. It is possible to call al PL/Python stored function natively from
another PL/Python function?

Something like:

|createorreplace functionf1()returns text as$$return"hello"$$language
'plpython3u';|

|do $$begin...t =f1()...end;$$language 'plpython3u';|


Regards.

(this question has been made in
http://stackoverflow.com/questions/34764665/call-postgres-pl-python-stored-function-from-another-pl-python-block
too).


In addition to the other answers there is the option of just creating 
the function in a Python module outside Postgres and doing:


from some_module import f

Which would seem to address this(from the SO post):

"This can be done using t = plpy.execute("select f1()"), but I want, if 
it is possible, call it as a normal Python function to avoid type 
conversions (for example jsonb, etc)."


The downside being you have a dependency outside the database.

--
Adrian Klaver
adrian.kla...@aklaver.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] Blocked updates and background writer performance

[Vick Khera]

On Wed, Jan 13, 2016 at 11:51 AM, Jeff Janes  wrote:

> Anyway, it looks to me like you have a checkpoint problem.  The checkpoint
> overwhelms your IO system.  The overwhelmed IO system then backs up into
> the bgwriter.  What you see in the bgwriter is just a symptom, not the
> cause.  The background writer is usually not very useful in recent versions
> of PostgreSQL, anyway.  But, the same IO problem that is clogging up the
> background writer is also clogging up either your buffer_backend, or your
> WAL writes/fsyncs.  And both of those will destroy your throughput.
>

That was my intuition too. Not enough I/O available from the hardware for
the workload requested.

As recommended, log your checkpoints and try tuning them to spread the load.

Re: [GENERAL] Moving a large DB (> 500GB) to another DB with different locale

[Vick Khera]

On Wed, Jan 13, 2016 at 3:03 AM, Andreas Joseph Krogh 
wrote:

> Seems like pglogical migth be better?
> http://2ndquadrant.com/en/resources/pglogical/
>
>

I would have no idea. I never used it, nor do I run RHEL (or any linux for
that matter) which seems to be the only supported OS for that software.

I've been using slony for close to a decade now, so I'm pretty familiar
with it and very confident with it.

Re: [GENERAL] Why PG uses nested-loop join when no indexes are available?

[David Grelaud]

Thank you for your fast response!

I'm sorry, my vocabulary may be not correct and my "french approach" to
explain my problem is not efficient ;).

But the underestimation problem in complex queries is still there? ... and
generates potential "dangerous" plans (nested loop).

We thought at the beginning we were alone but it seems to be a problem of
most database systems.
What do you think about the paragraph 4.1 of this paper
http://www.vldb.org/pvldb/vol9/p204-leis.pdf ?

Regards,
---
David Grelaud,
Ideolys.



2016-01-13 16:02 GMT+01:00 Tom Lane :

> David Grelaud  writes:
> > Statistics are not propagated when Common Table Expressions (CTE) are
> used.
> > The cardinality of a CTE is equal to 1 most of the time
>
> Really?
>
> The rest of this seems to be proceeding from completely false assumptions.
>
> regards, tom lane
>

Re: [GENERAL] Call postgres PL/Python stored function from another PL/Python block.

[Jim Nasby]

On 1/13/16 9:47 AM, lodopidolo wrote:

Hello. It is possible to call al PL/Python stored function natively from
another PL/Python function?


Stackoverflow is stupid and won't let me post there, but here's what you 
want:


There is no special capability to call other plpython functions. You 
need to call them as you would any other Postgres function, ie:


do $$
begin
...
rv = plpy.execute("SELECT f1()")
t = rv[1]["f1]
...
end;
$$ language 'plpython3u';

See 
http://www.postgresql.org/docs/9.5/static/plpython-database.html#AEN65599 for 
more information.


--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.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] WIP: CoC V5

[Joshua D. Drake]

On 01/13/2016 08:05 AM, Tom Lane wrote:


My general reaction to the rest of this is that it's got the right
idea, but it could be cut to about half the length and be better
off for that.  Short and sweet is the way, IMO.


+1


Less words, more point.

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Call postgres PL/Python stored function from another PL/Python block.

[Pavel Stehule]

Hi

2016-01-13 16:47 GMT+01:00 lodopidolo :

> Hello. It is possible to call al PL/Python stored function natively from
> another PL/Python function?
>

The function created by CREATE OR REPLACE FUNCTION statement can be called
only via special interface, or via SQL executor, so you cannot to call this
function directly from Python

Pavel



>
> Something like:
>
> create or replace function f1() returns text as $$
> return "hello"$$ language 'plpython3u';
>
> do $$begin
> ...
> t = f1()
> ...end;$$ language 'plpython3u';
>
>
> Regards.
>
> (this question has been made in
> http://stackoverflow.com/questions/34764665/call-postgres-pl-python-stored-function-from-another-pl-python-block
> too).
>

Re: [GENERAL] plv8 binaries for PostgreSQL 9.5 on Windows

[Igal @ Lucee.org]

On 1/12/2016 12:57 PM, Adrian Klaver wrote:

On 01/11/2016 05:50 PM, Igal @ Lucee.org wrote:

Where can I find binaries for plv8 for PostgreSQL 9.5 for Windows?


The only Windows binaries I could find where for 9.4, here:

http://www.postgresonline.com/journal/archives/341-PLV8-binaries-for-PostgreSQL-9.4-windows-both-32-bit-and-64-bit.html 



Maybe you could contact the authors and see if they are going to build 
9.5 versions?

Yes, that's where I found the binaries for 9.4 which worked very nicely.

The post cites Leo Hsu and Regina Obe (I actually have a couple of their 
books) as the authors, and judging by the replies to comments it looks 
like Regina is the one who posted it.


I believe that I've seen some posts by her in the mailing list here 
recently so hopefully she will see this thread and comment on it.


Thanks!



--
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] WIP: CoC V5

[Tom Lane]

Kevin Grittner  writes:
> I still feel it is more productive to discuss a proposed document
> than proposed language for some "motion to adopt".

Agreed.  We're trying to write a document, not a document about a
document.

> ... It applies
> to all "collaborative space", which is defined as community
> communications channels (such as mailing lists, IRC, submitted
> patches, commit comments, etc.) and to public events (such as
> meetings and conferences) which are associated with the PostgreSQL
> community.  Private communications which result from words or
> actions in the collaborative space should also conform to the
> standards stated here.

Magnus pointed out to me that (1) appropriate behavior in the virtual
space is not necessarily the same as appropriate behavior in physical
contexts such as meetings, and (2) most conferences already have their
own CoCs, which we should not be attempting to override.  So I'm
inclined to think that this CoC should be specifically about on-line
interaction, and explicitly leave it to conference organizers to set up
CoCs that work for their situations.

My general reaction to the rest of this is that it's got the right
idea, but it could be cut to about half the length and be better
off for that.  Short and sweet is the way, IMO.

regards, tom lane


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

[GENERAL] Call postgres PL/Python stored function from another PL/Python block.

[lodopidolo]

Hello. It is possible to call al PL/Python stored function natively from
another PL/Python function?

Something like:

create or replace function f1() returns text as $$
return "hello"$$ language 'plpython3u';

do $$begin
...
t = f1()
...end;$$ language 'plpython3u';


Regards.

(this question has been made in
http://stackoverflow.com/questions/34764665/call-postgres-pl-python-stored-function-from-another-pl-python-block
too).

Re: [GENERAL] WIP: CoC V5

[Kevin Grittner]

On Tue, Jan 12, 2016 at 10:04 PM, Joshua D. Drake  
wrote:
> On 01/12/2016 07:10 PM, Tom Lane wrote:
>> Kevin Grittner  writes:

>>> * To maintain a safe, respectful, productive and collaborative
>>> environment all participants must ensure that their language and
>>> actions are free of personal attacks and disparaging remarks of any
>>> kind.
>>
>> The "disparaging remarks" part of this could easily be taken to forbid
>> technical criticism of any sort, eg "this patch is bad because X,Y, and
>> Z", even when X,Y, and Z are perfectly neutral technical points.  "Of any
>> kind" doesn't improve that either.  I'm on board with the "personal
>> attacks" part.  Maybe "disparaging personal remarks" would be better?
>
> Hrm, I see your point but the definition of disparaging is:
>
> expressing the opinion that something is of little worth; derogatory.

Below is a modified version of what I posted, attempting to improve
it based on further thoughts of my own as well as suggestions from
Tom, JD, and Bill.  I see a lot to like in the variation proposed
by Chris, but wasn't sure quite how to meld that with this.  I've
left off the enforcement part for now.

I still feel it is more productive to discuss a proposed document
than proposed language for some "motion to adopt".  (I'm not sure
where such a motion would be made and adopted.)



== PostgreSQL Community Code of Conduct (CoC) ==

This document is intended to provide community guidelines for
creating and enforcing a safe, respectful, productive, and
collaborative place for any person who is willing to contribute in
a safe, respectful, productive and collaborative way.  It applies
to all "collaborative space", which is defined as community
communications channels (such as mailing lists, IRC, submitted
patches, commit comments, etc.) and to public events (such as
meetings and conferences) which are associated with the PostgreSQL
community.  Private communications which result from words or
actions in the collaborative space should also conform to the
standards stated here.

* Participants must ensure that their language and actions are free
of personal attacks and disparaging personal remarks.  Critical
remarks regarding patches and/or technical work are necessary to
ensure a quality product; however, critical remarks directed at
individuals are not constructive and therefore not acceptable.

* When interpreting the words and actions of others, participants
should always assume good intentions.  Consider that due to
language and cultural differences, something may be intended in a
benign or helpful way, even if some participants initially see a
possible interpretation which is otherwise.

* Participants must avoid sustained disruption of the collaborative
space, or any pattern of behavior which could reasonably be
considered harassment.

There is a distinction between words and actions taken within the
community and words and actions outside community communication
channels and events, but there is a gray area when using public
forums or social media where a person identifies as a member of
this community.  Members of the community, especially those with a
high profile within the community, should be mindful of this and
avoid saying or doing anything in such venues which might create an
unwelcoming or hostile attitude toward the community.



-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Why PG uses nested-loop join when no indexes are available?

[Tom Lane]

David Grelaud  writes:
> Statistics are not propagated when Common Table Expressions (CTE) are used.
> The cardinality of a CTE is equal to 1 most of the time

Really?

The rest of this seems to be proceeding from completely false assumptions.

regards, tom lane


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

[GENERAL] Why PG uses nested-loop join when no indexes are available?

[David Grelaud]

Hi,

Statistics are not propagated when Common Table Expressions (CTE) are used.
The cardinality of a CTE is equal to 1 most of the time so every joins with
previously computed CTEs are done with the nested-loop algorithm.
This seems to be really a risky choice, even without CTEs, according to
this paper and our own experiments:

"How good are query optimizers, really?." Proceedings of the VLDB Endowment
9.3 (2015): 204-215. (Paragraph 4.1) Leis, Viktor, et al.
http://www.vldb.org/pvldb/vol9/p204-leis.pdf

There are interesting discussions on the web about CTEs and bad
performances:

-
http://www.postgresql.org/message-id/flat/col116-w25f8931477407ed7689d69a3...@phx.gbl#col116-w25f8931477407ed7689d69a3...@phx.gbl
- http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
- ...

So when the problem happens (underestimation costs -> nested-loop ->  many
rows -> bad performance guarantee), we have currently these solutions:

- refactor the query using Subquery Expressions instead of CTEs but the
query looks really ugly to read (increasing maintenance cost), and we may
loose some other execution plan optimisations provided by CTEs
- refactor the query using temporary table but it becomes impossible to use
single-query prepared statement
- disable nested loop but PostgreSQL does not use Indexes anymore when
available
- use an extension to enable Oracle-style hints (
https://fr.osdn.jp/projects/pghintplan/) but the system becomes blindness
(not data-dependent, potential futures algorithms never used, ...)
- is there another existing solution I'm not aware of?

I'm sure PostgreSQL could provide a better solution to solve this problem.


1) Would it be easy to compute and propagate statistics of CTEs, like
subqueries?

The argument usually returned by the PostgreSQL community is:
"CTEs have been created to let the developer control the execution plan, so
the statistics computation is virtually disabled"
Ok, the developer can control roughly the execution plan but in the same
time, Postgres becomes "stupid" inside each CTEs and chooses always the
"same" join algorithm (the riskiest) to join previously computed CTEs.
It is like giving to somebody the power to fly, while removing his eyes ;).

Drawbacks: even if statistics are computed and propagated across CTEs, and
if queries are really complex, the cost estimator may fail to compute
cardinality and the problem of nested-loop joins still happens.


2) Would it be possible to let the developer inject cardinality hints in
the query?

As suggested by this paper:
"Query optimizers: time to rethink the contract?."" In : Proceedings of the
2009 ACM SIGMOD International Conference on Management of data. ACM, 2009.
p. 961-968. CHAUDHURI, Surajit.
http://courses.cs.washington.edu/courses/csep544/10au/readings/p961-chaudhuri.pdf

The SQL developer could for example inject cardinality in a comment
"my_cte:10". The developer is responsible to update this cardinality
with its own metrics and tools.
Thus, the cardinality is still data-dependent (not constant Ad vitam
æternam) and the planner is able to choose the best join algorithm
according to all other parameters (system IO...).


3) Always avoid nested-loop join when no indexes are available?

Tom Lane said "There might be some cases where this would help, but there
would be many more where it would be useless or counterproductive."
Who is right between Tom Lane and the Leis Viktor's paper above?

We tried to disable nested_loop all the time in a production environment
and we observed an overall improvement in all queries where Indexes are not
useful or not available (CTEs), which confirms the paper.
In fact, one of our production environment is still running with
"nested_loop off" because benefits are a lot greater than drawbacks as long
as some tables are relatively small (Indexes not used).


4) Do runtime optimizations?

According to research papers, this will be the next challenge. But I think
it is difficult to implement it in a relatively short-term period?



What is the purpose of this message:

We would like to find a "simple" long-term solution to this
under-estimation cost problem, which generate hazarduous performance
regressions in our production environments.

We would like to hear critiques or other solutions from PostgreSQL experts.

We would like to help developing and testing the solution.


Thank you very much!

Regards,
---
David Grelaud,
Ideolys.

Re: [GENERAL] WIP: CoC V5

[Chris Travers]

On Wed, Jan 13, 2016 at 3:15 PM, Regina Obe  wrote:

> Another anecdotal thing.  Personal attacks sometimes soften the blow. Take
> that as you will.
>
>
>
> For example if Tom makes some snide remark like "Do all Bostonians program
> this way?"
>
>
>
> It would lessen the blow of the criticism of the code as I would think
> he's making fun of Bostonians coding style more than he is about my
> abilities, and I as a Bostonian just don't know any better.
>
> He can also make fun of my tabbing style and say  "What's wrong with your
> editor? Perhaps you need to use a different one or change the settings"
>
>
>
> Although maybe those don't constitute personal attacks.  I don't know.
>

That's why I am not such a fan of rules and a larger fan of mediation,
discussion etc as an effort to work out issues.

>
>
>
>
> Thanks,
>
> Regina
>
>
>
> *From:* Regina Obe [mailto:l...@pcorp.us]
> *Sent:* Wednesday, January 13, 2016 9:08 AM
> *To:* 'Chris Travers' 
> *Cc:* 'Geoff Winkless' ; 'Psql_General (E-mail)' <
> pgsql-general@postgresql.org>
> *Subject:* RE: [GENERAL] WIP: CoC V5
>
>
>
> > On 13 January 2016 at 03:10, Tom Lane  wrote:
> >> The "disparaging remarks" part of this could easily be taken to forbid
> >> technical criticism of any sort, eg "this patch is bad because X,Y,
> >> and Z", even when X,Y, and Z are perfectly neutral technical points.
> >> "Of any kind" doesn't improve that either.  I'm on board with the
> >> "personal attacks" part.  Maybe "disparaging personal remarks" would be
> better?
>
> > One thing to think about here is the idea of framing the process.  One
> reason it might be a good idea to have a "respect the commons" clause is
> that it becomes a good way to think about the interaction of review and
> technical discussion.  I.e. both sides want to improve
>
> > the software.  The focus is on the software, not on the other person.
>
> > People *can* take offense when you say their code is not good enough,
> particularly when it is true, because for better or worse we do often
> identify with what we produce.  But I would hope that if the focus is on
> improvement of the software the this becomes at least a
>
> > bit less of a problem..
>
>
>
> --
>
> > Best Wishes,
>
> > Chris Travers
>
>
>
> Very good point.  I know personally I feel more hurt at my code being
> criticized than someone criticizing some random aspect of me.  That said
>
>
>
> Perhaps something like
>
>
>
> "we judge contributions primarily based on how easily it fits into our
> existing code base and the popularity of the problem or feature it targets"
>
>
>
> I was going to talk about correctness and all that, but I think that's
> kind of inferred by the comment about fitting into our existing code base.
> If it's not correct it wouldn't fit anyway.
>
>
>
> Thanks,
>
> Regina
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] WIP: CoC V5

[Geoff Winkless]

On 13 January 2016 at 14:15, Regina Obe  wrote:
> He can also make fun of my tabbing style and say  "What's wrong with your
> editor? Perhaps you need to use a different one or change the settings"

You're right, what we've really been missing all these years is an
editor flamewar :)

Geoff


-- 
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] WIP: CoC V5

[Regina Obe]

Another anecdotal thing.  Personal attacks sometimes soften the blow. Take that 
as you will.

 

For example if Tom makes some snide remark like "Do all Bostonians program this 
way?"

 

It would lessen the blow of the criticism of the code as I would think he's 
making fun of Bostonians coding style more than he is about my abilities, and I 
as a Bostonian just don't know any better.

He can also make fun of my tabbing style and say  "What's wrong with your 
editor? Perhaps you need to use a different one or change the settings"

 

Although maybe those don't constitute personal attacks.  I don't know.

 

 

Thanks,

Regina

 

From: Regina Obe [mailto:l...@pcorp.us] 
Sent: Wednesday, January 13, 2016 9:08 AM
To: 'Chris Travers' 
Cc: 'Geoff Winkless' ; 'Psql_General (E-mail)' 

Subject: RE: [GENERAL] WIP: CoC V5

 

> On 13 January 2016 at 03:10, Tom Lane   > wrote:
>> The "disparaging remarks" part of this could easily be taken to forbid
>> technical criticism of any sort, eg "this patch is bad because X,Y,
>> and Z", even when X,Y, and Z are perfectly neutral technical points.
>> "Of any kind" doesn't improve that either.  I'm on board with the
>> "personal attacks" part.  Maybe "disparaging personal remarks" would be 
>> better?

> One thing to think about here is the idea of framing the process.  One reason 
> it might be a good idea to have a "respect the commons" clause is that it 
> becomes a good way to think about the interaction of review and technical 
> discussion.  I.e. both sides want to improve 

> the software.  The focus is on the software, not on the other person.

> People *can* take offense when you say their code is not good enough, 
> particularly when it is true, because for better or worse we do often 
> identify with what we produce.  But I would hope that if the focus is on 
> improvement of the software the this becomes at least a 

> bit less of a problem..



-- 

> Best Wishes,

> Chris Travers

 

Very good point.  I know personally I feel more hurt at my code being 
criticized than someone criticizing some random aspect of me.  That said

 

Perhaps something like 

 

"we judge contributions primarily based on how easily it fits into our existing 
code base and the popularity of the problem or feature it targets"

 

I was going to talk about correctness and all that, but I think that's kind of 
inferred by the comment about fitting into our existing code base.  If it's not 
correct it wouldn't fit anyway.

 

Thanks,

Regina

Re: [GENERAL] WIP: CoC V5

[Regina Obe]

> On 13 January 2016 at 03:10, Tom Lane   > wrote:
>> The "disparaging remarks" part of this could easily be taken to forbid
>> technical criticism of any sort, eg "this patch is bad because X,Y,
>> and Z", even when X,Y, and Z are perfectly neutral technical points.
>> "Of any kind" doesn't improve that either.  I'm on board with the
>> "personal attacks" part.  Maybe "disparaging personal remarks" would be 
>> better?

> One thing to think about here is the idea of framing the process.  One reason 
> it might be a good idea to have a "respect the commons" clause is that it 
> becomes a good way to think about the interaction of review and technical 
> discussion.  I.e. both sides want to improve 

> the software.  The focus is on the software, not on the other person.

> People *can* take offense when you say their code is not good enough, 
> particularly when it is true, because for better or worse we do often 
> identify with what we produce.  But I would hope that if the focus is on 
> improvement of the software the this becomes at least a 

> bit less of a problem..



-- 

> Best Wishes,

> Chris Travers

 

Very good point.  I know personally I feel more hurt at my code being 
criticized than someone criticizing some random aspect of me.  That said

 

Perhaps something like 

 

"we judge contributions primarily based on how easily it fits into our existing 
code base and the popularity of the problem or feature it targets"

 

I was going to talk about correctness and all that, but I think that's kind of 
inferred by the comment about fitting into our existing code base.  If it's not 
correct it wouldn't fit anyway.

 

Thanks,

Regina

Re: [GENERAL] WIP: CoC V5

[Geoff Winkless]

On 13 January 2016 at 13:55, Chris Travers  wrote:
> People *can* take offense when you say their code is not good enough,
> particularly when it is true,

Well I would hope that you wouldn't say so if it wasn't. :)

My point is that most people won't be posting code here going "this is
a bit crap, but I'll post it anyway". Chances are, especially if
they're new, they will have checked and double-checked it beforehand.
Any things that are "not good enough", as you say, are likely to be
things that they couldn't really be expected to have known, given
their limited experience of the codebase.

So all I'm saying is: try to be gentle and encouraging, rather than
blunt and critical.

> But I would hope that if the focus is on
> improvement of the software the this becomes at least a bit less of a
> problem..

Yeah, telling someone "it's for your own good" has always been shown to help. :)

G


-- 
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] WIP: CoC V5

[Chris Travers]

On Wed, Jan 13, 2016 at 2:05 PM, Regina Obe  wrote:

>
> > On 13 January 2016 at 03:10, Tom Lane  wrote:
> >> The "disparaging remarks" part of this could easily be taken to forbid
> >> technical criticism of any sort, eg "this patch is bad because X,Y,
> >> and Z", even when X,Y, and Z are perfectly neutral technical points.
> >> "Of any kind" doesn't improve that either.  I'm on board with the
> >> "personal attacks" part.  Maybe "disparaging personal remarks" would be
> better?
>
> > IME attacks (even if they are purely technical) on one's code can be as
> hurtful and equally as likely to result in alienation as personal attacks.
> I'm not sure how you would word it but just concentrating on personal
> attacks leaves open the sort of bullying that has been seen in other
> projects.
>
> > Perhaps you could add something about valuing contributions from and
> making allowances for those with less expertise.
>
> > I know that's sort-of implied by the "any person who is willing to
> contribute" phrase but I would say that being explicit about it is more
> likely to encourage non-contributors to contribute than what's been arrived
> at so far.
>
> > Geoff
>
> I agree it's hard to even talk about just technical without hurting
> someone's feelings, but I really don't think there is much we can do about
> that. Except linking to a separate document about how to get newbie help.
> I think that kind of thing would be better handled by mentoring sessions
> (like a big developer taking you under their wings) than trying to deal
> with that sensitivity in a Coc.
> In fact people would take advantage of the situation if you say things
> like  "less expertise",
> because then they'd expect preferential treatment because they don't know
> C and be constantly badgering everybody for help.
>

One thing to think about here is the idea of framing the process.  One
reason it might be a good idea to have a "respect the commons" clause is
that it becomes a good way to think about the interaction of review and
technical discussion.  I.e. both sides want to improve the software.  The
focus is on the software, not on the other person.

People *can* take offense when you say their code is not good enough,
particularly when it is true, because for better or worse we do often
identify with what we produce.  But I would hope that if the focus is on
improvement of the software the this becomes at least a bit less of a
problem..

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



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

[GENERAL] [BDR] Best practice to automatically abort a DDL operation when one node is down

[Sylvain MARECHAL]

Hello all,

I am using BDR with two nodes 1 and 2.
If I issue a DDL operation in node 1 when node 2 is down, for example:
  CREATE TABLE test (i int PRIMARY KEY); (1)

all other transactions fail with the following error:
  Database is locked against DDL operations

The problem is that the (1) DDL request will wait indefinitely, meaning 
all transactions will continue to fail until the DDL operation is 
manually aborted (for example, doing CTRL C in psql to abort the "CREATE 
TABLE").


What is the best practice to make sure the DDL operation will fail, 
possibly after a timeout, if one of the node is down? I could check the 
state of the node before issuing the DDL operation, but this solution is 
far from being perfect as the node may fail right after this.


Thanks and Regards,
--
Sylvain


--
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] WIP: CoC V5

[Geoff Winkless]

On 13 January 2016 at 13:05, Regina Obe  wrote:
>> Perhaps you could add something about valuing contributions from and making 
>> allowances for those with less expertise.
>
> I agree it's hard to even talk about just technical without hurting someone's 
> feelings,

I don't believe that it is: it just involves a little empathy.
Remember how you felt the first time you posted a patch to a system
you didn't know, or remember that people can easily misunderstand how
stuff works, especially in a codebase as complex and large as this
one, and that they're giving up the few hours a week that they can
spare from their busy lives and aren't being paid to do it. Start out
by (maybe just quietly, in your head) thanking that person for their
efforts, before you explain gently how the work they've generously
submitted can be improved. Just putting yourself in that frame of mind
makes it more likely that you won't stomp over someone's feelings
because their 30 line patch that they spent 2 months building doesn't
take into account that some mechanism they've never heard of and isn't
referenced anywhere in the code they've modified relies on the
specific way the code worked before.

> because then they'd expect preferential treatment because they don't know C 
> and be constantly badgering everybody for help.

Someone coming on to the mailing lists and asking for help with newbie
stuff so they can spend their own free time improving the product? How
dare they! :p

Geoff


-- 
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] WIP: CoC V5

[Regina Obe]

> On 13 January 2016 at 03:10, Tom Lane  wrote:
>> The "disparaging remarks" part of this could easily be taken to forbid 
>> technical criticism of any sort, eg "this patch is bad because X,Y, 
>> and Z", even when X,Y, and Z are perfectly neutral technical points.  
>> "Of any kind" doesn't improve that either.  I'm on board with the 
>> "personal attacks" part.  Maybe "disparaging personal remarks" would be 
>> better?

> IME attacks (even if they are purely technical) on one's code can be as 
> hurtful and equally as likely to result in alienation as personal attacks. 
> I'm not sure how you would word it but just concentrating on personal attacks 
> leaves open the sort of bullying that has been seen in other projects.

> Perhaps you could add something about valuing contributions from and making 
> allowances for those with less expertise. 

> I know that's sort-of implied by the "any person who is willing to 
> contribute" phrase but I would say that being explicit about it is more 
> likely to encourage non-contributors to contribute than what's been arrived 
> at so far.

> Geoff

I agree it's hard to even talk about just technical without hurting someone's 
feelings, but I really don't think there is much we can do about that. Except 
linking to a separate document about how to get newbie help.
I think that kind of thing would be better handled by mentoring sessions (like 
a big developer taking you under their wings) than trying to deal with that 
sensitivity in a Coc.
In fact people would take advantage of the situation if you say things like  
"less expertise", 
because then they'd expect preferential treatment because they don't know C and 
be constantly badgering everybody for help.

Thanks,
Regina




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

[GENERAL] plpython3 package absent in 9.5 repository

[Clodoaldo Neto]

I don't know the policy of package inclusion in the repositories. The
plpython3 package exists in the Fedora repository:

http://mirror.globo.com/fedora/linux/updates/22/x86_64/p/

But it does not exist in the Postgresql repository:

https://download.postgresql.org/pub/repos/yum/9.5/fedora/fedora-22-x86_64/

Or as source:

https://download.postgresql.org/pub/repos/yum/srpms/9.5/fedora/fedora-22-x86_64/

Where to get the plpython3 packages for 9.5?

Regards, Clodoaldo

[GENERAL] Changing varchar length by manipulating pg_attribute

[Christian Ramseyer]

Hi

I have a database in which I'd like to increase the length of a varchar
column. Unfortunately, the column is used in various views which then
are used in other views, so doing this with ALTER TABLE ALTER COLUMN
TYPE is quite a lot of work.

I have found this suggestion

to just update pg_attribute like this:

UPDATE pg_attribute SET atttypmod = 35+4   -- new desired length + 4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';


Is this safe to do in Postgres 9.4? Also, best practice seems to be to
use text nowadays, is there even a variant of this that lets me convert
a "column from character varying(256)" to "text" without having to
recreate all the nested views?


Thanks
Christian



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

[Albe Laurenz]

Sachin Srivastava wrote:
> I am getting the below error for function, please see the bold line in 
> "Function code", please suggest
> what I will do to correct this code.
> 
>  ---
> 
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
> ^
> CONTEXT:  invalid type name "* from logError(msg text) is

That error message does not look like it could come from the
function definition below, but the function definition is clearly
syntactically incorrect:

> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
> subscriberid bigint,
> incompanyid bigint,
> loginname text)
>   RETURNS void AS
> $BODY$
> DECLARE

[...]

>   SELECT logError(v_errorMesg text, procedureName text, subscriberID bigint, 
> companyID bigint,
> supplierSku text, loginName text) is
> 
> BEGIN
>  insert into SMERROR_LOG
>  (error_message, method_name, system_message, error_log_id, 
> subscriber_id, company_id,
> creation_date, creation_user, update_date, update_user)
>  values(v_errorMesg, procedureName, supplierSku, 
> nextval('smerror_log_sequence'), subscriberID,
> companyID, LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>   end;

You cannot declare a function inside the DECLARE section of
another function.  Besides, what is "SELECT logError" supposed to mean?

You will have to declare each of the helper functions with ist own
CREATE FUNCTION statement.

Yours,
Laurenz Albe

-- 
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] WIP: CoC V5

[Geoff Winkless]

On 13 January 2016 at 03:10, Tom Lane  wrote:
> The "disparaging remarks" part of this could easily be taken to forbid
> technical criticism of any sort, eg "this patch is bad because X,Y, and
> Z", even when X,Y, and Z are perfectly neutral technical points.  "Of any
> kind" doesn't improve that either.  I'm on board with the "personal
> attacks" part.  Maybe "disparaging personal remarks" would be better?

IME attacks (even if they are purely technical) on one's code can be
as hurtful and equally as likely to result in alienation as personal
attacks. I'm not sure how you would word it but just concentrating on
personal attacks leaves open the sort of bullying that has been seen
in other projects.

Perhaps you could add something about valuing contributions from and
making allowances for those with less expertise. I know that's sort-of
implied by the "any person who is willing to contribute" phrase but I
would say that being explicit about it is more likely to encourage
non-contributors to contribute than what's been arrived at so far.

Geoff


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

[GENERAL] Function error

[Sachin Srivastava]

Dear Team,

I am getting the below error for function, please see the bold line in
"Function code", please suggest what I will do to correct this code.

 ---

ERROR:  syntax error at or near "*"
LINE 35: SELECT * from logError(msg text) is
^
CONTEXT:  invalid type name "* from logError(msg text) is

BEGIN
insert into SMERROR_LOG
(error_message,  error_log_id, method_name, subscriber_id, company_id,
creation_date, creation_user, update_date, update_user)
   values(msg,  nextval('smerror_log_sequence'),
'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP,
'Admin', LOCALTIMESTAMP, 'Admin')"
** Error **

ERROR: syntax error at or near "*"
SQL state: 42601
Character: 1276
Context: invalid type name "* from logError(msg text) is

BEGIN
insert into SMERROR_LOG
(error_message,  error_log_id, method_name, subscriber_id, company_id,
creation_date, creation_user, update_date, update_user)
   values(msg,  nextval('smerror_log_sequence'),
'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP,
'Admin', LOCALTIMESTAMP, 'Admin')"

--


*Function Code as below:*


--


-- Function: cp_property_room_count_trans(bigint, bigint, text)

-- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);

CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
subscriberid bigint,
incompanyid bigint,
loginname text)
  RETURNS void AS
$BODY$
DECLARE


  CRS_1 CURSOR FOR SELECT distinct company_id from CP_ROOM_COUNT_STAGING
where subscriber_id=subscriberID and (process_flag is null or
process_flag=0)  order by company_id;

  --Version:
  -- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
  --   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
  -- 06/08/07 (Bea) insert value for CP_ROOM_TYPE_COUNT.room_budget_home_amt
  --  If phaseID is new, validate that these as required fields
:CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
  --   Will set process_flag=2 if fail the validation.
  --  CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated per
business rule 15327
  --

  RW_1 RECORD; --CRS_1 %ROWTYPE;
  err_msg_1 varchar(100);
  v_errorMessage_1 varchar(4000);
  sucessfulRecCount bigint :=0;
  failedRecCount bigint :=0;








*  --  SELECT
logError(v_errorMesg text, procedureName text, subscriberID bigint,
companyID bigint, supplierSku text, loginName text) is  BEGIN insert
into SMERROR_LOG (error_message, method_name, system_message,
error_log_id, subscriber_id, company_id, creation_date, creation_user,
update_date, update_user)  values(v_errorMesg, procedureName,
supplierSku, nextval('smerror_log_sequence'), subscriberID, companyID,
LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');end;*

*  --*

  FUNCTION Invoke_InsertPropertyRCProcess(subID in number, compID in
number, loginName in varchar2) is

  CRS CURSOR FOR
 SELECT st.*, st.rowid, ph.project_code from
CP_ROOM_COUNT_STAGING st, CP_PROJECT_PHASE ph
 where st.SUBSCRIBER_ID =subID and st.company_id=compID and
(st.process_flag is null or st.process_flag=0)
 and ph.SUBSCRIBER_ID(+)= st.SUBSCRIBER_ID AND ph.PHASE_ID(+)=
st.CORP_PHASE_ID order by st.subscriber_id, st.company_id, st.phase_id;

 RW CRS%ROWTYPE;
 err_msg varchar2(100);
 v_errorMessage varchar2(4000);
 v_errorCountExp number :=0;
 MAX_ERR_WRITTEN constant number :=10;
 recCount number := 0;

 checkCount number;
 startRowCounter number :=0;
 isValidated boolean :=true;


 begin
   logError('Begin Invoke_InsertPropertyRCProcess subID:'|| subID || '
and compID:'||compID,'Invoke_InsertPropertyRCProcess', subID, compID,null,
loginName);

   -- the row_number counter starting number
   select max(Row_number) into startRowCounter from CP_ROOM_TYPE_COUNT
   where subscriber_id=subID and company_id=compID;

   if(startRowCounter is null) then
   startRowCounter :=0;
   end if;

   open CRS;
   loop
  begin -- the begin inside the loop
 fetch CRS into RW;
 IF NOT FOUND THEN EXIT; END IF; -- apply on CRS
   recCount := recCount+1;
   startRowCounter := startRowCounter+1;

   --check to if phase_id already existed in CP_PROPERTY_PHASE
if not insert
   select  count(1)  into checkCount from CP_PROPERTY_PHASE
   where subscriber_id=subID and company_id=compID and
PHASE_ID=RW.PHASE_ID;

   isValidated := true;
--check to see if pass validation.
   if(checkCount =0 and (RW.phase_start_date is null or
RW.phase_

Re: [GENERAL] WIP: CoC V5

[Regina Obe]

This looks much better than earlier editions.  I think this one I could avoid 
violating without too much trouble.

 

When we talk about community commons, does that include physical spaces?  

I'm trying to visualize what "safe" means in a virtual space and I'm drawing a 
blank picture.

 

Thanks,

Regina

 

 

 

From: Chris Travers [mailto:chris.trav...@gmail.com] 
Sent: Wednesday, January 13, 2016 2:47 AM
To: Joshua D. Drake 
Cc: Tom Lane ; Brian Dunavant ; 
Psql_General (E-mail) 
Subject: Re: WIP: CoC V5

 

Still trying the Danish "Make Love not Codes" approach (Love being the plural 
of the Danish Lov meaning law).

1. The CoC is to provide community guidelines for creating and enforcing a 
safe, respectful, productive, and collaborative place for any person who is 
willing to contribute in a safe, respectful, productive and collaborative way.  
This CoC is only about interaction via community channels or relating to 
community commons.  Your personal lives outside the PostgreSQL community are 
your own.

2. Please be respectful of others.  Understand that the community is about 
collaboration.  We are here to build and further a software project, so let's 
work together.

 

3. Please try to assume that perceived slights are the result of genuine 
miscommunication or different perspective not personal attacks.  Assume others 
are being reasonable.

4. Please respect the common work and the need for high quality of code.  
Understand that the review process is an opportunity for discussion and 
improvement.  Work to further the community and the software, and assume that 
others are doing the same.

 

5. The CoC committee, those they designate may take action as needed to 
facilitate or, as necessary, take action to enforce the community principles 
and conduct embodied in this CoC.  For affiliated projects, the maintainers of 
the project or those they designate may fill the same role instead.

=

I am going to reiterate my view that a rules approach is not going to really 
prevent problems and those who want to harass others can often use codes of 
conduct as weapons to do so.  I would rather see something as general as 
possible.emphasizing what we want rather than what we want to avoid.

 

On Tue, Jan 12, 2016 at 10:37 PM, Joshua D. Drake mailto:j...@commandprompt.com> > wrote:

tl;dr;

* Moved #2 to #3 and #3 to #2
* Added wording for assuming positive intent to #2

PostgreSQL Global Development Group (PGDG) Code of Conduct (CoC):

1. The CoC is to provide community guidelines for creating and enforcing a 
safe, respectful, productive, and collaborative place for any person who is 
willing to contribute in a safe, respectful, productive and collaborative way.

2. A safe, respectful, productive and collaborative environment is free of 
personal attacks and disparaging remarks of any kind.

3. The CoC is not about being offended. One should always assume good 
intentions. As with any diverse community, anyone can get offended at anything.

4. Any sustained disruption of the collaborative space (mailing lists, IRC 
etc..) or other PostgreSQL events shall be construed as a violation of the CoC 
and appropriate action will be taken by the CoC committee.

5. The CoC is only about interaction with the PostgreSQL community. Your 
private and public lives outside of the PostgreSQL community are your own.



-- 
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564 
 
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


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




-- 

Best Wishes,

Chris Travers

 

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

http://www.efficito.com/learn_more

Re: [GENERAL] Code of Conduct: Is it time?

[Chris Travers]

On Wed, Jan 13, 2016 at 9:00 AM, John R Pierce  wrote:

> On 1/12/2016 11:32 PM, Chris Travers wrote:
>
>>
>> One of the nice things about the Ubuntu Code of Conduct is that it
>> focuses primarily on the positive.  It is long, perhaps overly verbose, but
>> it does focus on what the community wants rather than what the community
>> wants to avoid.
>>
>> It is easy to say "don't do these things."  But it is perhaps better to
>> say "these are the values our community lives by. Please respect them."
>>
>
>
> +1, except for the overly verbose part.


I probably should have said "overly verbose for our uses here."

I won't second guess a different project with a different scope.

>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] [BUGS] permissions.

[Shulgin, Oleksandr]

[not a bug, moving to -general]

On Tue, Jan 12, 2016 at 12:46 PM, Frik Brits  wrote:

> Hello,
> What is to be done here, below between the lines is an excerpt from my
> bash console if I want to start PostgreSQL.
>
>
> -
> frik@linux-cj2v:/etc/alternatives> postgres -D /bigdisk/data/postgres &
> [1] 10438
> frik@linux-cj2v:/etc/alternatives> 2016-01-12 13:09:29 SAST   FATAL:
> could
> not create lock file "/var/run/postgresql/.s.PGSQL.5432.lock": Permission
> denied
>
> --
>
> mySQL has the same problems. Perhaps worse.I know u do not do mySQL stuff,
> but
> still.
>
> I can go fix this by changing the permissions of /var/run and
> /var/run/postgresql/ direc tories but then one will have to change it every
> single time I want to start the databse. O by the way starting it as a
> service
> is even worse because the then it wants to work in it own data directory.
>
> Can this not be fixed, I mean linux is linux whichever flavour one my want
> to
> use?.
>
> Fortunately not all linux programs have these strange permissions hickups.
>

Typically a postgres installation configured in such a way expects that the
master process is started by the user named postgres.  You can achieve that
by using sudo(1) command, e.g: sudo -u postgres COMMAND

In your example:

$ sudo -u postgres postgres -D /bigdisk/data/postgres

Not sure why are you trying to start it in foreground.  Please refer to
documentation for pg_ctl(1) and your distribution init system.  Something
like the following should have equivalent effect (that is, starting
postgres):

$ sudo pg_ctl -D /bigdisk/data/postgres start

OR

$ sudo service postgresql start

Debian and derivative systems such as Ubuntu provide pg_ctlcluster(1)
command to manage multiple clusters and PostgreSQL versions.

Can you elaborate on the problems you encounter when PostgreSQL is started
as a service?

--
Alex

Re: [GENERAL] Moving a large DB (> 500GB) to another DB with different locale

[Andreas Joseph Krogh]

På onsdag 13. januar 2016 kl. 04:17:03, skrev Vick Khera mailto:vi...@khera.org>>:
  On Tue, Jan 12, 2016 at 4:20 PM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote: I'm moving a > 500GB DB to another server 
which is initdb'ed with a different locale (nb_NO.UTF-8 to get correct 
collation when ORDER BY).
Is there another option than pg_dump/restore for doing this?
 


 I recently used slony to move data stored in an SQL_ASCII db to one that was 
strict UTF-8. I wrote up a program that would fix any invalid byte sequences in 
the original db first. It went very well, and we had very minimal downtime.
 
It seems to me you could use slony to accomplish your goal as well of just 
copying the data into a new DB with the different collation setting. Once done, 
just stop your application for a minute or two to switch the DB it uses, and 
drop the slony replication. This process will work if you can assure that the 
dump/restore would have worked too. That is, there are no invalid data.

 
Seems like pglogical migth be better? 
http://2ndquadrant.com/en/resources/pglogical/
It's available for 9.4 also.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Moving a large DB (> 500GB) to another DB with different locale

[Andreas Joseph Krogh]

På onsdag 13. januar 2016 kl. 03:58:27, skrev John McKown <
john.archie.mck...@gmail.com >:
On Tue, Jan 12, 2016 at 4:09 PM, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote:
På tirsdag 12. januar 2016 kl. 22:56:56, skrev John R Pierce <
pie...@hogranch.com >:
On 1/12/2016 1:20 PM, Andreas Joseph Krogh wrote:
 > I'm moving a > 500GB DB to another server which is initdb'ed with a
 > different locale (nb_NO.UTF-8 to get correct collation when ORDER BY).
 > Is there another option than pg_dump/restore for doing this?

 nope, because the text data has to be converted to the new encoding,
 indexes rebuilt since the collation order is different, etc.
 
I was pretty sure there wasn't, but had to ask before imposing downtime on our 
customers.
 
Thanks.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

 
​I install, maintain, and use PostgreSQL on Fedora Linux. But only as a type 
of "power" user. I'm not a DBA​. Nor do I have good knowledge of PostgreSQL 
internals. What I gather you are considering is something like:
 
server1: pg_dump one or more data bases to a file on server1 (or on an NFS / 
CIFS shared NAS box of some sort)
server1: transfer that file to server2 if necessary (i.e. if the file dumped 
on server1 is not readable directly on server2.
server2: pg_restore the data from the file.
 
What I am wondering is this: Is there some way to make the PostgreSQL instance 
on server2 be accessable, say via FDW, to PostgreSQL on server1? Or may vice 
versa. 
ref: http://www.postgresql.org/docs/current/static/postgres-fdw.html 

if so, would it be possible to do something like:
 
on server1:
 
CREATE EXTENSION posgres_fdw;
CREATE SERVER server2
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS(host 'a.b.c.d', port '5432', db_name 'bubba')
;
CREATE USER MAPPING FOR 
    SERVER server2
    OPTIONS(user 'remote_user', password 'password')
;
CREATE FOREIGN TABLE server2_sometable (
   -- duplicate definition of "sometable" on server2
) SERVER server2 OPTIONS(schema_name 'PUBLIC', table_name 'sometable')
;
INSERT INTO server2_sometable (COL1, COL2, COL3, ...) SELECT COL1, COL2, COL3, 
... FROM sometable;
 
This is most likely a stupid thought. And I don't know if it would address the 
locale and collation issue or not. If nothing else, someone will explain 
(hopefully kindly) why this is a bad idea. Such as performance or some such 
thing.
 
An auxiliary thought, if the communications speed between server1 & server2 is 
"poor", would be to have two copies of PostgreSQL running on server1. The 
second Postgres would write to a filesystem on an SSD connected to Server1, 
either via SATA, eSATA, or maybe USB. I would hope that this would be faster 
than using a 1Gig (or ever 10 Gig) IP connection. Once the copy is complete, 
stop the second Postgres instance, unmount the filesystem, move the SSD to 
"server2" hardware platform.
 
Just some wacky thoughts. They are right at home in my head.


 
Note that I'm searching for a solution for moving a single (out of many) 
database in a cluster, not just some tables.
This database contains about 400 tables, triggers, constraints (also 
exclusion-constraints) PL/pgSQL functions etc.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Code of Conduct: Is it time?

[John R Pierce]

On 1/12/2016 11:32 PM, Chris Travers wrote:


One of the nice things about the Ubuntu Code of Conduct is that it 
focuses primarily on the positive.  It is long, perhaps overly 
verbose, but it does focus on what the community wants rather than 
what the community wants to avoid.


It is easy to say "don't do these things."  But it is perhaps better 
to say "these are the values our community lives by. Please respect them."



+1, except for the overly verbose part.

--
john r pierce, recycling bits in santa cruz



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