Re: [GENERAL] Trigger function interface

[Tatsuo Ishii]

> On 1/6/16 7:03 PM, Tatsuo Ishii wrote:
>> Is it possible to get the parse tree in a C trigger function which is
>> invoked when DML (INSERT/UPDATE/DELETE against a view) is executed?
> 
> Yes, it's in fcinfo->flinfo->fn_expr.

Thanks for the info. But is this the parse tree for the top level
query which involves the trigger?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] Definitive answer: can functions use indexes?

[Alban Hertroys]

> On 07 Jan 2016, at 5:19, Jim Nasby  wrote:
> 
> On 1/6/16 5:41 PM, Tom Lane wrote:
>> Since the question makes little sense as stated, I'm going to assume
>> you mean "can a query like SELECT ... WHERE left(foo, 3) = 'bar'
>> use an index on column foo?"
>> 
>> The answer to that is no, there is no such optimization built into
>> Postgres.  (In principle there could be, but I've not heard enough
>> requests to make me think we'd ever pursue it.)
> 
> BTW, the case where this would be highly valuable is timestamps. Being able 
> to do something like date_part('month',timestamptz)='Jan' would be a big, big 
> deal for warehousing.

Not just warehousing, for BI in general.

But, as is now quite clear, for many of those cases it should be fairly trivial 
to work around this limitation by creating either a functional index or an 
operator. For the above example, say something like timestamptz % 'month' = 
'Jan'.

There are downsides to that approach though, such as readability and that this 
way of using % instead of date_part() is not according to any standard 
behaviour and could even behave differently or (more likely) not work at all on 
other PG instances.

That said, it's not uncommon in BI to require a seq. scan anyway, in which case 
the point is rather moot.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
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] RAM of Postgres Server

[David G. Johnston]

On Thu, Jan 7, 2016 at 12:32 AM, Sachin Srivastava 
wrote:

> Dear David,
>
>
>
>
>
> Q: RAM holds data that is recently accessed - how much of that will you
> have?
>
>
>
> Ans: Kindly confirm, as per your question “RAM holds data that is recently
> accessed” :  How we figured out that how much data we will have. Is it
> depends of Total WAL files (total "checkpoint_segment" I have given 32), am
> I correct or thinking wrong, please clarify to me.
>
>
>
> Right now we have 10 GB RAM for first database server and 3 GB RAM for
> another database server.
>
>
Using WAL to measure your active dataset is not going to work.  WAL
activity occurs when you WRITE data while in many cases the data in RAM is
data that was written to the WAL a long time ago.


>
>
>
>
> Q: Cores help service concurrent requests - how many of those will you
> have?  How fast will they complete?
>
>
>
> Ans: It’s means, if we have more core then we can do our work fast. Like
> from 9.3 onwards for pg_dump as example, if machines having multiple cores
> as the load can be shared among separate threads.
>
>
>
> So if possible to us then more core should be available on database server
> for better performance, please clarify the benefit of more core to me.
>
>
>
> Right now we have 1 core for first database server and 2 core for another
> database server.
>
>
>

​PostgreSQL is process-oriented and presently only uses a single process to
service a single connection.  Application software can upon up multiple
connections -  which is what pg_dump does.  More rypically you'd have
something like a web server where all of the incoming requests are funneled
through a connection pool which then opens a number of connections to the
database which it then shares among those requests.

If you want advice you are going to have to give considerably more detail
of your application and database usage patterns than you have.

David J.

Re: [GENERAL] 9.5rc1 RLS select policy on insert?

[Stephen Frost]

Ted,

Please don't top-post on these lists.

> On Wed, Jan 6, 2016 at 9:40 PM, Stephen Frost  wrote:
> > * Ted Toth (txt...@gmail.com) wrote:
> >> I see the insert policy check running but also the select policy using
> >> on insert. I don't understand why the select policy is being run.
> >> Could it possibly be related to using a sequence on the table?
> >
> > It's used when SELECT rights are required on the table, such as with an
> > INSERT .. RETURNING.

* Ted Toth (txt...@gmail.com) wrote:
> This happens on simple INSERTs no RETURNING.

You'll have to provide more information on what you're seeing.

I've just stepped through get_row_security_quals() for a simple INSERT
to a table and reviewed what ends up in securityQuals (it's NULL) and
withCheckOptions (it's the single boolean expression corresponding to
the INSERT policy defined on the table).  The SELECT policy, which is
also defined on the table, is no where to be found in the results of
that call.

I was testing with:

=*> \d t1
  Table "public.t1"
 Column |  Type   | Modifiers 
+-+---
 c1 | integer | 
Policies:
POLICY "p1" FOR INSERT
  WITH CHECK ((c1 > 5))
POLICY "p2" FOR SELECT
  USING ((c1 < 5))

=*> insert into t1 values (6);

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] 9.5rc1 RLS select policy on insert?

[Tom Lane]

Ted Toth  writes:
> This happens on simple INSERTs no RETURNING.

You're going to need to show a concrete example.

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] Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

[Andy Colson]

On 01/07/2016 06:30 PM, Steve Petrie, P.Eng. wrote:

Thanks to forum members for the four helpful replies, to my earlier message 
that initiated this thread.

The replies expressed concerns, with the feasibility of my proposal to use 
postgres tables to store short-lived context data, for dialog continuity during 
website app transient sessions, with visitor browsers over modeless HTTP 
connections.

Hope the four emails I sent in response (5 January 2016), went some way to 
satisfying the concerns expressed.

Here is a list of the issues discussed, in the dialog mentioned above:

1. "Session" defined;
2. Avoid row DELETEs;
3. Periodically TRUNCATE each table in a pool of session context tables;
4. Embed a session ID key parameter in an HTML "hidden" field (optional);
5. Use sequence generators as rapid global iterators controlling access to 
session context tables;





Thanks to forum members for taking the time to read my email.



This feels hugely overcomplicated.  I also didn't read most of the last thread, 
so forgive me if you've answered this already:  How many website requests a 
second (that actually need to touch session data) are you expecting?  How much 
space is the session data going to take?  (like, 5 Gig a day?)

If its a huge number, you should put effort into growing horizontally, not all 
of this stuff.
If its a small number, you'll spend more time fixing all the broken things than 
its worth.
Have you benchmarked this?  In my mind, complicated == slow.

Sorry if I'm raining on your parade, it looks like you have really put a lot of 
work into this.

Have you considered saving session data to disk is faster than saving to db?  A 
good reverse web proxy can stick a session to the same backend.  1 web proxy up 
front, 5 web servers behind it.  I'd bet its way faster.

-Andy



--
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] RAM of Postgres Server

[Sachin Srivastava]

Dear David G. and Gavin,

Please find the details as below:


* Q. What type of queries to be run on postgres database server? *

All types. Databases will be general OLTP.

*Q. Size of commonly accessed tables, indexes, ... ?*

Various. Anywhere from a few MB to over 100GB.



*Q. Database size for current and for future for all databases: *

Various. Anywhere from 100 – 1000GB and always growing.



*Q. Total Connection limit from the database through users and Application
on daily basis for all the database:*

Probably 4000+



*Q.  CPU Core: *

Prod databases will have 32 cores of CPU and 128 GB of RAM.


If you want any other information then inform to me.


Regards,

SS




On Thu, Jan 7, 2016 at 8:51 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Jan 7, 2016 at 12:32 AM, Sachin Srivastava <
> ssr.teleat...@gmail.com> wrote:
>
>> Dear David,
>>
>>
>>
>>
>>
>> Q: RAM holds data that is recently accessed - how much of that will you
>> have?
>>
>>
>>
>> Ans: Kindly confirm, as per your question “RAM holds data that is
>> recently accessed” :  How we figured out that how much data we will
>> have. Is it depends of Total WAL files (total "checkpoint_segment" I have
>> given 32), am I correct or thinking wrong, please clarify to me.
>>
>>
>>
>> Right now we have 10 GB RAM for first database server and 3 GB RAM for
>> another database server.
>>
>>
> Using WAL to measure your active dataset is not going to work.  WAL
> activity occurs when you WRITE data while in many cases the data in RAM is
> data that was written to the WAL a long time ago.
>
>
>>
>>
>>
>>
>> Q: Cores help service concurrent requests - how many of those will you
>> have?  How fast will they complete?
>>
>>
>>
>> Ans: It’s means, if we have more core then we can do our work fast. Like
>> from 9.3 onwards for pg_dump as example, if machines having multiple cores
>> as the load can be shared among separate threads.
>>
>>
>>
>> So if possible to us then more core should be available on database
>> server for better performance, please clarify the benefit of more core to
>> me.
>>
>>
>>
>> Right now we have 1 core for first database server and 2 core for another
>> database server.
>>
>>
>>
>
> ​PostgreSQL is process-oriented and presently only uses a single process
> to service a single connection.  Application software can upon up multiple
> connections -  which is what pg_dump does.  More rypically you'd have
> something like a web server where all of the incoming requests are funneled
> through a connection pool which then opens a number of connections to the
> database which it then shares among those requests.
>
> If you want advice you are going to have to give considerably more detail
> of your application and database usage patterns than you have.
>
> David J.
>

Re: [GENERAL] RAM of Postgres Server

[John R Pierce]

On 1/7/2016 8:21 PM, Sachin Srivastava wrote:


*Q. Total Connection limit from the database through users and 
Application on daily basis for all the database:*


**Probably 4000+



I hope you mean 4000 sessions/day, and not 4000 concurrent connections.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] RAM of Postgres Server

[Sachin Srivastava]

Dear John,

We are looking at more like 500-600 connections simultaneously in 1 day
and I want to say we get 1 to 12000 connections a day per db.

Regards
SS

On Fri, Jan 8, 2016 at 10:49 AM, John R Pierce  wrote:

> On 1/7/2016 8:21 PM, Sachin Srivastava wrote:
>
> *Q. Total Connection limit from the database through users and Application
> on daily basis for all the database:*
>
> Probably 4000+
>
>
> I hope you mean 4000 sessions/day, and not 4000 concurrent connections.
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>

Re: [GENERAL] RAM of Postgres Server

[David G. Johnston]

On Thu, Jan 7, 2016 at 9:21 PM, Sachin Srivastava 
wrote:

> Dear David G. and Gavin,
>
> Please find the details as below:
>
>
> * Q. What type of queries to be run on postgres database server? *
>
> All types. Databases will be general OLTP.
>
> *Q. Size of commonly accessed tables, indexes, ... ?*
>
> Various. Anywhere from a few MB to over 100GB.
>
>
>
> *Q. Database size for current and for future for all databases: *
>
> Various. Anywhere from 100 – 1000GB and always growing.
>
>
>
> *Q. Total Connection limit from the database through users and Application
> on daily basis for all the database:*
>
> Probably 4000+
>
>
>
> *Q.  CPU Core: *
>
> Prod databases will have 32 cores of CPU and 128 GB of RAM.
>
>
>
​So, what's your question...?  Given the broad brush generalities and
explicit references to multiple databases I have no clue what you expect
from this list - not that I was going to be of much help on the topic
anyway...

David J.

Re: [GENERAL] RAM of Postgres Server

[Ben Chobot]

On Jan 7, 2016, at 10:32 PM, Sachin Srivastava  wrote:
> 
> 
> Dear John,
> 
> We are looking at more like 500-600 connections simultaneously in 1 day and I 
> want to say we get 1 to 12000 connections a day per db.


Unless you have 300 cores to service those 500-600 simultaneous connections, 
you are really going to want to put your database behind a connection pooler 
such as pgBouncer. A connection pooler is a proxy that decreases the 
concurrency on the database, letting the database see only a few of the 
connections that clients want to make, and thereby increasing overall query 
throughput.

If it works for your application, pgBouncer has wonderful mode called 
transaction pooling, which automatically rotates clients into an open database 
slot on transaction boundaries. 

-- 
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

[Charles Clavadetscher]

Hello

If I understand you correctly you have two functions create_catexp_ss_1 and 
create_catexp_ss_2 that you then call from create_catexp_master.
If so then you probably need to change the call to them:

> -- Exposure for single supplier without category filtering
> create_catexp_ss_1;
> 
> -- Exposure for single supplier with category filtering
> create_catexp_ss_2;

Should be:

PERFORM create_catexp_ss_1();
PERFORM create_catexp_ss_2();

If necessary with according parameters and assuming that you have no values 
returned that you need.

Bye
Charles

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sachin Srivastava
> Sent: Freitag, 8. Januar 2016 08:24
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Function error
> 
> Dear Concern,
> 
> 
> 
> I am creating below function “create_catexp_ss_master()” and getting error as 
> below, I have already created
> dependent function firstly successfully (“create_catexp_ss_1” and 
> “create_catexp_ss_2”) but still getting error,
> please suggest why?
> 
> 
> 
> ERROR:  syntax error at or near "create_catexp_ss_1"
> 
> LINE 38:  create_catexp_ss_1;
> 
>   ^
> 
> ** Error **
> 
> 
> 
> ERROR: syntax error at or near "create_catexp_ss_1"
> 
> SQL state: 42601
> 
> Character: 1104
> 
> 
> 
> 
> 
> 
> 
> -- Function: create_catexp_ss_master()
> 
> 
> 
> -- DROP FUNCTION create_catexp_ss_master();
> 
> 
> 
> CREATE OR REPLACE FUNCTION create_catexp_ss_master()
> 
>   RETURNS void AS
> 
> $BODY$
> 
> DECLARE
> 
> 
> 
> -- Build snapshot tables for catalog itme exposure.
> 
> 
> 
> -- Versions:
> 
> -- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13
> 
> -- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen and 
> from the Code. 05/23/13
> 
> --
> 
> 
> 
> v_count_before bigint;
> 
> v_count_after bigint;
> 
> v_start_time timestamp;
> 
> v_err_msg varchar(1000);
> 
> v_set_name varchar(10);
> 
> 
> 
> BEGIN
> 
> v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;
> 
> SELECT LOCALTIMESTAMP INTO v_start_time ;
> 
> 
> 
> if v_set_name='A' then
> 
> SELECT count(1) INTO v_count_before FROM 
> pcat_exp_supp_buyer_ss_a;
> 
> else
> 
> SELECT count(1) INTO v_count_before FROM 
> pcat_exp_supp_buyer_ss_b;
> 
> end if;
> 
> 
> 
> -- Remove old data.
> 
> EXECUTE 'truncate table pcat_exp_supp_buyer_ss_'||v_set_name;
> 
> EXECUTE 'truncate table 
> pcat_exp_supp_cat_buyer_ss_'||v_set_name;
> 
> 
> 
> -- Exposure for single supplier without category filtering
> 
> create_catexp_ss_1;
> 
> 
> 
> -- Exposure for single supplier with category filtering
> 
> create_catexp_ss_2;
> 
> 
> 
> if v_set_name='A' then
> 
> SELECT count(1) INTO v_count_after FROM 
> pcat_exp_supp_buyer_ss_a;
> 
> else
> 
> SELECT count(1) INTO v_count_after FROM 
> pcat_exp_supp_buyer_ss_b;
> 
> end if;
> 
> 
> 
> -- Log
> 
> create_ss_log('Catalog Exposure', v_start_time, 
> 'pcat_exp_supp_buyer_ss_'||v_set_name,
> 
> v_count_before, v_count_after, null);
> 
> 
> 
> exception-- log error
> 
> when others then
> 
> v_err_msg := SQLERRM;
> 
> create_ss_log('Catalog Exposure - Error', v_start_time, 
> 'pcat_exp_supp_buyer_ss_'||v_set_name,
> 
> v_count_before, 
> v_count_after, v_err_msg);
> 
> 
> 
> END;
> 
> $BODY$
> 
>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
> 
>   COST 100;
> 
> ALTER FUNCTION create_catexp_ss_master()
> 
>   OWNER TO postgres;
> 
> 
> 
> 
> 
> 
> 
> Regards,
> SS
> 




-- 
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

[Sachin Srivastava]

Thanks Pavel !!!

On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule 
wrote:

> Hi
>
>
>
> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava :
>
>> Dear Concern,
>>
>>
>>
>> I am creating below function *“create_catexp_ss_master()” *and getting
>> error as below, I have already created dependent function firstly
>> successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still
>> getting error, please suggest why?
>>
>>
>>
>> ERROR:  syntax error at or near "create_catexp_ss_1"
>>
>> LINE 38:  create_catexp_ss_1;
>>
>>   ^
>>
>> ** Error **
>>
>>
>>
>> ERROR: syntax error at or near "create_catexp_ss_1"
>>
>> SQL state: 42601
>>
>> Character: 1104
>>
>>
>>
>>
>>
>>
>>
>> -- Function: create_catexp_ss_master()
>>
>>
>>
>> -- DROP FUNCTION create_catexp_ss_master();
>>
>>
>>
>> CREATE OR REPLACE FUNCTION create_catexp_ss_master()
>>
>>   RETURNS void AS
>>
>> $BODY$
>>
>> DECLARE
>>
>>
>>
>> -- Build snapshot tables for catalog itme exposure.
>>
>>
>>
>> -- Versions:
>>
>> -- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13
>>
>> -- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
>> and from the Code. 05/23/13
>>
>> --
>>
>>
>>
>> v_count_before bigint;
>>
>> v_count_after bigint;
>>
>> v_start_time timestamp;
>>
>> v_err_msg varchar(1000);
>>
>> v_set_name varchar(10);
>>
>>
>>
>>
>> BEGIN
>>
>> v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;
>>
>> SELECT LOCALTIMESTAMP INTO v_start_time ;
>>
>>
>>
>> if v_set_name='A' then
>>
>> SELECT count(1) INTO v_count_before FROM
>> pcat_exp_supp_buyer_ss_a;
>>
>> else
>>
>> SELECT count(1) INTO v_count_before FROM
>> pcat_exp_supp_buyer_ss_b;
>>
>> end if;
>>
>>
>>
>> -- Remove old data.
>>
>> EXECUTE 'truncate table
>> pcat_exp_supp_buyer_ss_'||v_set_name;
>>
>> EXECUTE 'truncate table
>> pcat_exp_supp_cat_buyer_ss_'||v_set_name;
>>
>
> Attention - this is potentially serious security bug
>
> EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
> v_set_name);
>
>
>>
>>
>> -- Exposure for single supplier without category
>> filtering
>>
>> create_catexp_ss_1;
>>
>
> you have to call this function via PERFORM statement
>
>PERFORM create_catexp_ss_1();
>
>
>>
>>
>> -- Exposure for single supplier with category filtering
>>
>>
>> create_catexp_ss_2;
>>
>>
>>
>> if v_set_name='A' then
>>
>> SELECT count(1) INTO v_count_after FROM
>> pcat_exp_supp_buyer_ss_a;
>>
>> else
>>
>> SELECT count(1) INTO v_count_after FROM
>> pcat_exp_supp_buyer_ss_b;
>>
>> end if;
>>
>>
>>
>> -- Log
>>
>> create_ss_log('Catalog Exposure', v_start_time,
>> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>>
>> v_count_before, v_count_after, null);
>>
>>
>>
>> exception-- log error
>>
>> when others then
>>
>> v_err_msg := SQLERRM;
>>
>> create_ss_log('Catalog Exposure - Error', v_start_time,
>> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>>
>> v_count_before,
>> v_count_after, v_err_msg);
>>
>>
>>
>> END;
>>
>> $BODY$
>>
>>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>>
>>   COST 100;
>>
>> ALTER FUNCTION create_catexp_ss_master()
>>
>>   OWNER TO postgres;
>>
>>
>>
>>
>>
>>
>> Regards,
>> SS
>>
>>
> Regards
>
> Pavel Stehule
>

Re: [GENERAL] Function error

[Sachin Srivastava]

Thanks Charles !!!

On Fri, Jan 8, 2016 at 1:15 PM, Sachin Srivastava 
wrote:

> Thanks Pavel !!!
>
> On Fri, Jan 8, 2016 at 1:05 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>>
>>
>> 2016-01-08 8:24 GMT+01:00 Sachin Srivastava :
>>
>>> Dear Concern,
>>>
>>>
>>>
>>> I am creating below function *“create_catexp_ss_master()” *and getting
>>> error as below, I have already created dependent function firstly
>>> successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but
>>> still getting error, please suggest why?
>>>
>>>
>>>
>>> ERROR:  syntax error at or near "create_catexp_ss_1"
>>>
>>> LINE 38:  create_catexp_ss_1;
>>>
>>>   ^
>>>
>>> ** Error **
>>>
>>>
>>>
>>> ERROR: syntax error at or near "create_catexp_ss_1"
>>>
>>> SQL state: 42601
>>>
>>> Character: 1104
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> -- Function: create_catexp_ss_master()
>>>
>>>
>>>
>>> -- DROP FUNCTION create_catexp_ss_master();
>>>
>>>
>>>
>>> CREATE OR REPLACE FUNCTION create_catexp_ss_master()
>>>
>>>   RETURNS void AS
>>>
>>> $BODY$
>>>
>>> DECLARE
>>>
>>>
>>>
>>> -- Build snapshot tables for catalog itme exposure.
>>>
>>>
>>>
>>> -- Versions:
>>>
>>> -- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13
>>>
>>> -- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
>>> and from the Code. 05/23/13
>>>
>>> --
>>>
>>>
>>>
>>> v_count_before bigint;
>>>
>>> v_count_after bigint;
>>>
>>> v_start_time timestamp;
>>>
>>> v_err_msg varchar(1000);
>>>
>>> v_set_name varchar(10);
>>>
>>>
>>>
>>>
>>> BEGIN
>>>
>>> v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;
>>>
>>> SELECT LOCALTIMESTAMP INTO v_start_time ;
>>>
>>>
>>>
>>> if v_set_name='A' then
>>>
>>> SELECT count(1) INTO v_count_before
>>> FROM pcat_exp_supp_buyer_ss_a;
>>>
>>> else
>>>
>>> SELECT count(1) INTO v_count_before
>>> FROM pcat_exp_supp_buyer_ss_b;
>>>
>>> end if;
>>>
>>>
>>>
>>> -- Remove old data.
>>>
>>> EXECUTE 'truncate table
>>> pcat_exp_supp_buyer_ss_'||v_set_name;
>>>
>>> EXECUTE 'truncate table
>>> pcat_exp_supp_cat_buyer_ss_'||v_set_name;
>>>
>>
>> Attention - this is potentially serious security bug
>>
>> EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
>> v_set_name);
>>
>>
>>>
>>>
>>> -- Exposure for single supplier without category
>>> filtering
>>>
>>> create_catexp_ss_1;
>>>
>>
>> you have to call this function via PERFORM statement
>>
>>PERFORM create_catexp_ss_1();
>>
>>
>>>
>>>
>>> -- Exposure for single supplier with category filtering
>>>
>>>
>>> create_catexp_ss_2;
>>>
>>>
>>>
>>> if v_set_name='A' then
>>>
>>> SELECT count(1) INTO v_count_after FROM
>>> pcat_exp_supp_buyer_ss_a;
>>>
>>> else
>>>
>>> SELECT count(1) INTO v_count_after FROM
>>> pcat_exp_supp_buyer_ss_b;
>>>
>>> end if;
>>>
>>>
>>>
>>> -- Log
>>>
>>> create_ss_log('Catalog Exposure', v_start_time,
>>> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>>>
>>> v_count_before, v_count_after, null);
>>>
>>>
>>>
>>> exception-- log error
>>>
>>> when others then
>>>
>>> v_err_msg := SQLERRM;
>>>
>>> create_ss_log('Catalog Exposure - Error', v_start_time,
>>> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>>>
>>> v_count_before,
>>> v_count_after, v_err_msg);
>>>
>>>
>>>
>>> END;
>>>
>>> $BODY$
>>>
>>>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>>>
>>>   COST 100;
>>>
>>> ALTER FUNCTION create_catexp_ss_master()
>>>
>>>   OWNER TO postgres;
>>>
>>>
>>>
>>>
>>>
>>>
>>> Regards,
>>> SS
>>>
>>>
>> Regards
>>
>> Pavel Stehule
>>
>
>

[GENERAL] Function error

[Sachin Srivastava]

Dear Concern,



I am creating below function *“create_catexp_ss_master()” *and getting
error as below, I have already created dependent function firstly
successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still
getting error, please suggest why?



ERROR:  syntax error at or near "create_catexp_ss_1"

LINE 38:  create_catexp_ss_1;

  ^

** Error **



ERROR: syntax error at or near "create_catexp_ss_1"

SQL state: 42601

Character: 1104







-- Function: create_catexp_ss_master()



-- DROP FUNCTION create_catexp_ss_master();



CREATE OR REPLACE FUNCTION create_catexp_ss_master()

  RETURNS void AS

$BODY$

DECLARE



-- Build snapshot tables for catalog itme exposure.



-- Versions:

-- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13

-- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
and from the Code. 05/23/13

--



v_count_before bigint;

v_count_after bigint;

v_start_time timestamp;

v_err_msg varchar(1000);

v_set_name varchar(10);




BEGIN

v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;

SELECT LOCALTIMESTAMP INTO v_start_time ;



if v_set_name='A' then

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_before FROM
pcat_exp_supp_buyer_ss_b;

end if;



-- Remove old data.

EXECUTE 'truncate table
pcat_exp_supp_buyer_ss_'||v_set_name;

EXECUTE 'truncate table
pcat_exp_supp_cat_buyer_ss_'||v_set_name;



-- Exposure for single supplier without category
filtering


create_catexp_ss_1;



-- Exposure for single supplier with category filtering


create_catexp_ss_2;



if v_set_name='A' then

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_a;

else

SELECT count(1) INTO v_count_after FROM
pcat_exp_supp_buyer_ss_b;

end if;



-- Log

create_ss_log('Catalog Exposure', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before, v_count_after, null);



exception-- log error

when others then

v_err_msg := SQLERRM;

create_ss_log('Catalog Exposure - Error', v_start_time,
'pcat_exp_supp_buyer_ss_'||v_set_name,

v_count_before,
v_count_after, v_err_msg);



END;

$BODY$

  LANGUAGE plpgsql VOLATILE SECURITY DEFINER

  COST 100;

ALTER FUNCTION create_catexp_ss_master()

  OWNER TO postgres;






Regards,
SS

Re: [GENERAL] Function error

[Pavel Stehule]

Hi



2016-01-08 8:24 GMT+01:00 Sachin Srivastava :

> Dear Concern,
>
>
>
> I am creating below function *“create_catexp_ss_master()” *and getting
> error as below, I have already created dependent function firstly
> successfully *(“create_catexp_ss_1” and “create_catexp_ss_2”) *but still
> getting error, please suggest why?
>
>
>
> ERROR:  syntax error at or near "create_catexp_ss_1"
>
> LINE 38:  create_catexp_ss_1;
>
>   ^
>
> ** Error **
>
>
>
> ERROR: syntax error at or near "create_catexp_ss_1"
>
> SQL state: 42601
>
> Character: 1104
>
>
>
>
>
>
>
> -- Function: create_catexp_ss_master()
>
>
>
> -- DROP FUNCTION create_catexp_ss_master();
>
>
>
> CREATE OR REPLACE FUNCTION create_catexp_ss_master()
>
>   RETURNS void AS
>
> $BODY$
>
> DECLARE
>
>
>
> -- Build snapshot tables for catalog itme exposure.
>
>
>
> -- Versions:
>
> -- 2013.1 hxu T11464 - Two Table Catalog Snap Shot Process. 02/27/13
>
> -- 2013.02 hxu T11899 - Remove the Single Supplier check box from Screen
> and from the Code. 05/23/13
>
> --
>
>
>
> v_count_before bigint;
>
> v_count_after bigint;
>
> v_start_time timestamp;
>
> v_err_msg varchar(1000);
>
> v_set_name varchar(10);
>
>
>
>
> BEGIN
>
> v_set_name:=ssj4_snapshot_pkg.get_inactive_ss_set_name;
>
> SELECT LOCALTIMESTAMP INTO v_start_time ;
>
>
>
> if v_set_name='A' then
>
> SELECT count(1) INTO v_count_before FROM
> pcat_exp_supp_buyer_ss_a;
>
> else
>
> SELECT count(1) INTO v_count_before FROM
> pcat_exp_supp_buyer_ss_b;
>
> end if;
>
>
>
> -- Remove old data.
>
> EXECUTE 'truncate table
> pcat_exp_supp_buyer_ss_'||v_set_name;
>
> EXECUTE 'truncate table
> pcat_exp_supp_cat_buyer_ss_'||v_set_name;
>

Attention - this is potentially serious security bug

EXECUTE 'TRUNCATE TABLE ' || quote_ident(pcat_exp_supp_cat_buyer_ss_ ||
v_set_name);


>
>
> -- Exposure for single supplier without category filtering
>
>
> create_catexp_ss_1;
>

you have to call this function via PERFORM statement

   PERFORM create_catexp_ss_1();


>
>
> -- Exposure for single supplier with category filtering
>
>
> create_catexp_ss_2;
>
>
>
> if v_set_name='A' then
>
> SELECT count(1) INTO v_count_after FROM
> pcat_exp_supp_buyer_ss_a;
>
> else
>
> SELECT count(1) INTO v_count_after FROM
> pcat_exp_supp_buyer_ss_b;
>
> end if;
>
>
>
> -- Log
>
> create_ss_log('Catalog Exposure', v_start_time,
> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>
> v_count_before, v_count_after, null);
>
>
>
> exception-- log error
>
> when others then
>
> v_err_msg := SQLERRM;
>
> create_ss_log('Catalog Exposure - Error', v_start_time,
> 'pcat_exp_supp_buyer_ss_'||v_set_name,
>
> v_count_before,
> v_count_after, v_err_msg);
>
>
>
> END;
>
> $BODY$
>
>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>
>   COST 100;
>
> ALTER FUNCTION create_catexp_ss_master()
>
>   OWNER TO postgres;
>
>
>
>
>
>
> Regards,
> SS
>
>
Regards

Pavel Stehule

[GENERAL] pg_upgrade on Windows

[Igal @ Lucee.org]

hi guys,

I am having issues with pg_upgrade on Windows.  I have posted a question 
on StackOverflow -- at 
http://stackoverflow.com/questions/34664236/pg-upgrade-on-windows-cannot-write-to-log-file-pg-upgrade-internal-log 
-- copied below for convenience:


I'm trying to run pg_upgrade on Windows, but I'm getting the error:

   cannot write to log file pg_upgrade_internal.log Failure, exiting

I saw a similar question for Linux at23216734 
which 
explains that the issue is with permissions, but it doesn't help with 
Windows as I do not have a user named|postgres|


Same goes for thepg_upgrade docs 
, which 
mention a|postgres|user:


   RUNAS /USER:postgres "CMD.EXE"

But again, I do not have such a user, and am trying to run this command 
as Administrator so I don't understand why I would have no permission. I 
even tried to do


|RUNAS /USER:Administrator "CMD.EXE"|

And run pg_upgrade in the new command prompt, but am getting the same error.

Also, I am not sure which directory needs permissions? Where is the 
process trying to write|pg_upgrade_internal.log|to?


any ideas?  TIA!

--

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] Postgresql 9.3 not coming up after restart in centos

[balajishanmu...@live.in]

Hi, 

For storage I am using a 2.5inch SATA 3 SSD hard disk. It is about 60 GB. I
am yet to get the log. I will post the Postgres log once I have it.

Thanks!



--
View this message in context: 
http://postgresql.nabble.com/Postgresql-9-3-not-coming-up-after-restart-in-centos-tp5880435p5880957.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Postgresql 9.3 not coming up after restart in centos

[Scott Marlowe]

On Wed, Jan 6, 2016 at 2:08 PM, balajishanmu...@live.in
 wrote:
> Most of the time I will be restarting centOS by issuing reboot command. Which
> will do the orderly shutdown of all the service and sometimes just pull the
> plug.
>
> But the issue appears to be random. Is there a way that before Postgres
> starts we can check whether data is flushed, if not flush it manually or any
> other better way to avoid this issue.

As Adrian mentioned, by the time you go for a startup of pgsql, the
damage is already done during the previous shut down.

The real issue here is that a properly operating server should be able
to have the power plug pulled, and on boot up postgres should be able
to come back up. When pgsql can't come back up, it's usually due to an
unreliable storage subsystem. So what are you using for storage?


-- 
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] Any way to selectively color query output in psql?

[David G. Johnston]

On Thu, Jan 7, 2016 at 2:32 PM, Gavin Flower 
wrote:

> On 08/01/16 10:25, David G. Johnston wrote:
>
>> Basically I want to write this:
>>
>> psql -c "SELECT E'\e[1;33m Some Text Here \e[0m';"
>>
>> And have just the text "Some Text Here" colored while everything else is
>> default white.
>>
>> The \e constructs are the ANSI color escapes which work when using echo -e
>>
>> I am using bash 4.2.25(1) on Ubuntu 14.04
>>
>> Is this possible?
>>
>> Thanks!
>>
>> David J.
>>
>> But what is the version of Postgres?  :-)
>
>

​Touché

​9.3.10

Re: [GENERAL] Any way to selectively color query output in psql?

[Gavin Flower]

On 08/01/16 10:25, David G. Johnston wrote:

Basically I want to write this:

psql -c "SELECT E'\e[1;33m Some Text Here \e[0m';"

And have just the text "Some Text Here" colored while everything else 
is default white.


The \e constructs are the ANSI color escapes which work when using echo -e

I am using bash 4.2.25(1) on Ubuntu 14.04

Is this possible?

Thanks!

David J.


But what is the version of Postgres?  :-)



--
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] Postgresql 9.3 not coming up after restart in centos

[Scott Marlowe]

On Thu, Jan 7, 2016 at 1:41 PM, balajishanmu...@live.in
 wrote:
> Hi,
>
> For storage I am using a 2.5inch SATA 3 SSD hard disk. It is about 60 GB. I
> am yet to get the log. I will post the Postgres log once I have it.
>
> Thanks!

Yeah a lot of cheaper consumer grade SSDs don't fsync safely. There
are drives that do, and they're usually bit more expensive. We use the
Intel DC S3500 series at work and they do pass the "pull the power
cables" test for us.


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

Re: [GENERAL] pg_upgrade on Windows

[John R Pierce]

On 1/7/2016 12:32 PM, Igal @ Lucee.org wrote:


I'm trying to run pg_upgrade on Windows, but I'm getting the error:

cannot write to log file pg_upgrade_internal.log Failure, exiting

I saw a similar question for Linux at23216734 
which 
explains that the issue is with permissions, but it doesn't help with 
Windows as I do not have a user named|postgres|


Same goes for thepg_upgrade docs 
, which 
mention a|postgres|user:


RUNAS /USER:postgres "CMD.EXE"

But again, I do not have such a user, and am trying to run this 
command as Administrator so I don't understand why I would have no 
permission. I even tried to do


|RUNAS /USER:Administrator "CMD.EXE"|

And run pg_upgrade in the new command prompt, but am getting the same 
error.


Also, I am not sure which directory needs permissions? Where is the 
process trying to write|pg_upgrade_internal.log|to?


any ideas?  TIA!



how was your postgresql installed ?   is the existing version of 
postgres running as a system service or what? what versions are you 
upgrading from and to ?


note, btw, just being Administrator does NOT automatically override file 
permissions... an administrator is allowed to change file ownership.


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Postgresql 9.3 not coming up after restart in centos

[John R Pierce]

On 1/7/2016 12:41 PM, balajishanmu...@live.in wrote:

For storage I am using a 2.5inch SATA 3 SSD hard disk. It is about 60 GB. I
am yet to get the log. I will post the Postgres log once I have it.


is this an enterprise grade SSD with supercap backup?  or is it a 
consumer desktop/notebook class SSD without write buffer protection?


if the latter, its lying about data being written, and if the power 
fails, poof, the last bunch of data that had just been written is likely 
gone.




--
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

[GENERAL] Any way to selectively color query output in psql?

[David G. Johnston]

Basically I want to write this:

psql -c "SELECT E'\e[1;33m Some Text Here \e[0m';"

And have just the text "Some Text Here" colored while everything else is
default white.

The \e constructs are the ANSI color escapes which work when using echo -e

I am using bash 4.2.25(1) on Ubuntu 14.04

Is this possible?

Thanks!

David J.