Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi All,

On Tue, Nov 17, 2015 at 12:10 PM, Peter Eisentraut <pete...@gmx.net> wrote:

> On 11/17/15 2:16 AM, Jim Nasby wrote:
> > On 11/15/15 10:56 PM, dinesh kumar wrote:
> >> So, shall we make this pg_report_log TO pg_write_log OR pg_ereport OR
> >>  from you.
> >
> > Why not pg_raise to mirror plpgsql? (The function does have the same
> > semantics, right? It's not doing something like only sending to the log
> > and not the client?)
>
> I think the "raise" terminology is specific to plpgsql, as it actually
> raises an exception in that language.
>
>
Sorry for being too late on this, as I have engaged into some other
personal tasks.

Could someone let me know, what else I need to do to get this patch
completed.

Any further suggestions on function name. If all OK with pg_log or
someother, I would modify the patch,
and will submit new one.

Kindly let me know.

Thanks in advance.

-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Mon, Nov 16, 2015 at 3:58 PM, Kevin Grittner  wrote:

> On Sunday, November 15, 2015 8:51 PM, Craig Ringer 
> wrote:
>
> > I'd prefer to omit fields if explicitly assigned to NULL. You can
> > always use coalesce if you want the string 'NULL'; I agree with
> > others here that the vast majority of users will want the field
> > just omitted.
>
> +1
>
> Unfortunately those writing the SQL standard chose to have a single
> flag (NULL) to indicate either "unknown" or "not applicable".  That
> causes problems where it's not clear which way the value should be
> interpreted, but in this case it seems pretty clear that someone
> passing a NULL parameter for hint to a function like this doesn't
> mean "there is likely to be a valid value for hint, but I don't
> know what it is" -- they mean there is no available hint, so please
> don't show one.  Any other behavior seems rather silly.
>
> Thanks Kevin/Craig for your comments.



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



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

On Tue, Nov 17, 2015 at 1:02 AM, Michael Paquier 
wrote:

> On Mon, Nov 16, 2015 at 11:27 AM, Michael Paquier wrote:
> > In short, it seems that this patch is better rejected. And I am
> > planning to do so if there are no complaints.
>
> OK, done so in the CF app.
> Dinesh, please do not be discouraged by that. Everybody has patches
> rejected, and I know a bit of it :)
>

Sure Michael :-)

Thanks.

> --
> Michael
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi,

On Mon, Nov 16, 2015 at 2:50 AM, Peter Eisentraut <pete...@gmx.net> wrote:

> On 10/22/15 3:20 AM, dinesh kumar wrote:
> > +  
> > +   
> > +
> pg_report_log(logleveltext,
> message anyelement[, ishidestmt
> boolean ] [, detail  text] [,
> hint text] [, sqlstate
> text])
> > +   
> > +   void
> > +   
> > +Report message or error.
> > +   
> > +  
>
> I haven't seen this discussed before, but I don't find the name
> pg_report_log particularly good.  Why not jut pg_log?
>
>
Thanks for your comments.

Sorry for my too late response here.

I'm sure pg_log is good. But, I don't see it's more easily understandable.
What I mean is, If we see "pg_log" as function name, we can't say that,
what this function is going to do by just reading it's name. For example,
we have "pg_write_file". By reading the function name itself, we can define
this, this is the  function is for writing contents into given file.

So, shall we make this pg_report_log TO pg_write_log OR pg_ereport OR  from you.


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

On Thu, Nov 12, 2015 at 4:35 AM, Michael Paquier <michael.paqu...@gmail.com>
wrote:

> On Sat, Oct 3, 2015 at 1:43 PM, dinesh kumar wrote:
> > We can also use "PROGRAM 'cat > Output.csv' " to achieve this "NO READ
> > ACCESS", since the program is always running as a instance owner.
> > Let me know your inputs and thoughts.
>
> That's one way. And as PROGRAM presents the advantage to open the file
> before the COPY has begun processing any tuples, the umask would be
> set before writing any data to it, hence why complicating COPY with a
> new option while we already have something to apply restrictions to
> the output file? It seems that this patch is just an unnecessary
> complication, and I doubt that we would want to change the default
> behavior of 644 that has been here for ages.
>

Hi Michael,

Thanks for your inputs.

I am also against changing the default behavior. Since, I see, some
advantages having 644.

As pg_file_write, and 'PROGRAM' uses it's instance owner umask for the
output file, I believe,
we should also have the same umaks for the COPY too. I Could be wrong here,
But I don't see
"PROGRAM" as an alternative to this. Since, we have a separate TO clause,
which takes care about
dumping data into files.

Let me know, if I'm wrong here.

--
> Michael
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi All,

On Tue, Oct 20, 2015 at 1:22 PM, Pavel Stehule 
wrote:

>
> 2015-10-20 20:05 GMT+02:00 Robert Haas :
>
>> On Tue, Oct 20, 2015 at 11:29 AM, Pavel Stehule 
>> wrote:
>> > 2015-10-20 17:15 GMT+02:00 Robert Haas :
>> >> On Tue, Oct 20, 2015 at 11:09 AM, Pavel Stehule <
>> pavel.steh...@gmail.com>
>> >> wrote:
>> >> > Probably it was my request. I don't like to using NULL as value, that
>> >> > should
>> >> > be ignored. The "hint" is clean, there NULL can be ignored, but what
>> >> > about
>> >> > DETAIL or MESSAGE?
>> >>
>> >> If the field is required - as MESSAGE is - then its absence is an
>> >> error.  If the field is optional, treat a NULL if the parameter were
>> >> not supplied.
>> >
>> > I understand well, what was proposed. Personally I see small risk, but
>> I am
>> > thinking so can be useful if users can choose between two possibilities
>> > (strict, and NULL tolerant). For some adhoc work it can be useful.
>>
>> You haven't made any attempt to explain why that behavior would be
>> useful to anyone except that saying some information might be lost.
>> But what field of an error report can sensibly be populated with the
>> word NULL, and nothing else?
>>
>
> My previous idea was wrong (I didn't though well about all details). I am
> sorry. The implementation of variadic parameters in Postgres requires some
> default value - in this case the only one logical default value is NULL.
> And in this case, when the default is used, the NULL shouldn't be
> displayed. I propose following behave. The level and the message arguments
> are mandatory (has not default value), others are optional. The level is
> should not be NULL, the message can be NULL, and the NULL should be
> displayed, any others are ignored if holds NULL.  A alternative is - only
> the level will be mandatory, others will be optional, and then there are
> not any exception for message.
>
>
Thanks for valuable insight inputs.

I just want to be clear about the things from your side,
and want to take further required development from my side.

Let me summarize the issues  as below.

1. We need a patch, as per Jim's suggestion about RAISE's USING
should skip any NULL argument, rather throwing an ERROR.
So, we need a new patch if everyone accept this for the RAISE statement.

2. Using this function, if we provide any "NULL" argument to the function,
 we should either skip it or report it. I see this is what the function is
doing.

postgres=# SELECT pg_report_log('INFO', 'NULL', false, NULL, NULL);
INFO:  NULL

postgres=# SELECT pg_report_log('INFO', 'NULL', false, 'NULL', 'NULL');
INFO:  NULL
DETAIL:  NULL  *-- Are you suggesting to change this behaviour*
HINT:  NULL

Kindly let me know your suggestions. Please find the attached patch, which
is generated on top of latest branch head.

Thanks in advance.

-- 

Regards,
Dinesh
manojadinesh.blogspot.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2946122..8deb679 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17966,6 +17966,15 @@ postgres=# SELECT * FROM 
pg_xlogfile_name_offset(pg_stop_backup());
 Return information about a file.

   
+  
+   
+pg_report_log(logleveltext, 
message anyelement[, ishidestmt 
boolean ] [, detail  text] [, 
hint text] [, sqlstate 
text])
+   
+   void
+   
+Report message or error.
+   
+  
  
 

@@ -18034,6 +18043,32 @@ SELECT (pg_stat_file('filename')).modification;
 

 
+   
+pg_report_log
+   
+   
+pg_report_log is useful to write custom messages
+or raise exception. This function don't support the PANIC, FATAL
+log levels due to their unique internal DB usage, which may cause
+the database instability. Using ishidestmt which default 
values
+is true, function can write or ignore the current SQL statement
+into log destination. Also, we can have DETAIL, HINT log messages
+by provding detail, hint as function
+arguments, which are NULL by default. The parameter sqlstate
+allows to set a SQLSTATE of raised exception. Default value of this
+parameter is 'P0001' for ERROR only level.
+
+Typical usages include:
+
+postgres=# SELECT pg_report_log('NOTICE', 'Custom Message', true);
+NOTICE:  Custom Message
+ pg_report_log 
+---
+ 
+(1 row)
+
+   
+
   
 
   
diff --git a/src/backend/catalog/system_views.sql 
b/src/backend/catalog/system_views.sql
index ccc030f..7e551f2 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -940,3 +940,22 @@ RETURNS jsonb
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'jsonb_set';
+
+CREATE OR REPLACE FUNCTION pg_report_log(loglevel text, message text,
+ ishidestmt boolean DEFAULT true, 
detail text DEFAULT NULL,
+ hint text DEFAULT NULL, 

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Thu, Oct 22, 2015 at 11:15 AM, Jim Nasby <jim.na...@bluetreble.com>
wrote:

> On 10/22/15 2:20 AM, dinesh kumar wrote:
>
>>
>> 2. Using this function, if we provide any "NULL" argument to the function,
>>   we should either skip it or report it. I see this is what the function
>> is doing.
>>
>> postgres=# SELECT pg_report_log('INFO', 'NULL', false, NULL, NULL);
>> INFO:  NULL
>>
>> postgres=# SELECT pg_report_log('INFO', 'NULL', false, 'NULL', 'NULL');
>> INFO:  NULL
>> DETAIL:  NULL /-- Are you suggesting to change this behaviour/
>> HINT:  NULL
>>
>
> It should operate the same as what was decided for RAISE.
>
> I'd say it should also support the remaining RAISE options as well
> (COLUMN, CONSTRAINT, DATATYPE, TABLE, SCHEMA).
>
> I think hide_statement is a better name than ishidestmt. It would be nice
> if RAISE supported that too...
>
> I think the function should also allow specifying a condition name instead
> of a SQL state, same as RAISE does.
>
> In other words, this function and raise should operate exactly the same
> unless there's a really strong reason not to. Otherwise it's just going to
> create confusion.
>
>
Thanks Jim,

That make sense to me. Let me cover these options too, and will update here.


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



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PROPOSAL] DIAGNOSTICS = SKIPPED_ROW_COUNT

[dinesh kumar]

On Wed, Oct 14, 2015 at 4:06 PM, Robert Haas <robertmh...@gmail.com> wrote:

> On Wed, Oct 14, 2015 at 6:28 PM, dinesh kumar <dineshkuma...@gmail.com>
> wrote:
> > I see this feature as an add on to do the parallel DML operations.
> > There won't be any problem, if operations are mutually exclusive.
> > I mean, each session operates on unique set of tuples.
> >
> > In the above case, we don't even need of SKIP LOCKED wait policy.
> >
> > But, when it comes to mutually depend operations, isn't it nice to
> provide,
> > how much were locked by the other sessions. OR atlest a HINT to the other
> > session like,
> >
> > GET DIAGNOSTICS var = DID_I_MISS_ANYTHING_FROM_OTHER_SESSIONS;
> >
> > I agree that, adding counter will take a performance hit.
> > Rather going to my actual proposal on providing the counter value,
> > isn't it good to provide a boolean type HINT, if we miss atleast a single
> > tuple.
>
> Suppose there are 5 locked rows and 5 unlocked rows in the heap and you do
> this:
>
> select * from t1 for share skip locked limit 5
>
> The Boolean you propose will be false if the first 5 rows in physical
> order are locked, and otherwise it will be false.  But there's no
> difference between those two scenarios from the perspective of the
> application.  Here's another example:
>
> with foo as (select * from t1 for share skip locked) select * from foo
> where a = 2;
>
> If foo contains any locked rows at all, this will return true,
> regardless of whether a = 2.
>
> It's true that, for a lot of normal-ish queries, LockRows is applied
> late enough that your proposed Boolean would return the intended
> answer.  But there are a bunch of exceptions, like the ones shown
> above, and there might be more in the future.
>
>
Hi Robert,

As usual, a great guidance from you. Thanks :-)

But I'm still trying to see, is there a way we can implement this for all
use cases.
Will update this thread with my findings.


> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PROPOSAL] DIAGNOSTICS = SKIPPED_ROW_COUNT

[dinesh kumar]

Hi Robert,

On Wed, Oct 14, 2015 at 12:56 PM, Robert Haas <robertmh...@gmail.com> wrote:

> On Tue, Oct 13, 2015 at 10:37 PM, dinesh kumar <dineshkuma...@gmail.com>
> wrote:
> > In an existing wait policies like WAIT(default) and NO WAIT,
> > one can be sure to determine(Using ROW_COUNT daignostics counter),
> > how many required tuples he processed in a transaction.
> > But this is not case when it comes to SKIP LOCKED.
>
> Sure it is.  You didn't process the ones that you skipped.  This is no
> different than if you say WHERE a = 5.  Depending on plan choice and
> table contents, you may have "skipped" a large number of rows where a
> != 5, or you may have skipped none at all.
>

Yes, True.

But, using SKIP LOCKED we may bypass the tuples where a = 5, If those were
locked by parallel operations.


>
> > In my view, SKIP LOCKED is a nice feature, which gives only the
> available OR
> > unlocked tuples.
> > But those are not the complete required tuples for the given SQL
> statement.
> > Isn't it ?!
>
> They better be.


Agreed.


>   If you wanted the locked tuples, you shouldn't have
> asked to skip them.
>

Kindly let me know if I am going in a wrong way.

I see this feature as an add on to do the parallel DML operations.
There won't be any problem, if operations are mutually exclusive.
I mean, each session operates on unique set of tuples.

In the above case, we don't even need of SKIP LOCKED wait policy.

But, when it comes to mutually depend operations, isn't it nice to provide,
how much were locked by the other sessions. OR atlest a HINT to the other
session like,

GET DIAGNOSTICS var = DID_I_MISS_ANYTHING_FROM_OTHER_SESSIONS;

I agree that, adding counter will take a performance hit.
Rather going to my actual proposal on providing the counter value,
isn't it good to provide a boolean type HINT, if we miss atleast a single
tuple.

Let me know your thoughts.

Thanks in advance.


>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PROPOSAL] DIAGNOSTICS = SKIPPED_ROW_COUNT

[dinesh kumar]

On Tue, Oct 13, 2015 at 5:53 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
>>> > Using this attribute, we can have more control on parallel operations
>>> like,
>>>
>>> > IF SKIPPED_ROW_COUNT =0 THEN
>>> > <>
>>> > ELSE
>>> > <>
>>> > END IF;
>>>
>>> Um ... so what?  This is not a use-case.
>>>
>>>
>> In my view, "How one can be sure that, he obtained all the tuples with
>> SKIP LOCKED". If the end user has this counter value, he may proceed with a
>> different approach with partially locked tuples.
>>
>>
> ​Can you be more specific?  In most cases I can come up with (queues,
> basically) where skipped locked is running the processing performing the
> query is going to re-query the database on the next tick regardless of
> whether they thought they say only some or all of the potential rows on the
> prior pass.
>
>
Sure,

In an existing wait policies like WAIT(default) and NO WAIT,
one can be sure to determine(Using ROW_COUNT daignostics counter),
how many required tuples he processed in a transaction.
But this is not case when it comes to SKIP LOCKED. My concern is,
how we come to know that, our SKIP LOCKED missed few tuples due to lock, OR
it's processed all the tuples.
I understood that, the name itself defining SKIP the LOCKED rows, but we
are not measuring it.

I wrote the patch and it's working as below. But I haven't extended this to
other planners yet.

postgres=# DO
$$
DECLARE
rc INT;
BEGIN
PERFORM * FROM test WHERE mod(t,2)=0 FOR UPDATE SKIP LOCKED;
GET DIAGNOSTICS rc=SKIPPED_ROW_COUNT;
RAISE NOTICE 'Skipped : %', rc;
GET DIAGNOSTICS rc=ROW_COUNT;
RAISE NOTICE 'Processed : %', rc;
END;
$$
;
NOTICE:  Skipped : 2
NOTICE:  Processed : 3
DO

Once we measured the SKIP LOCKED, then we can only consider
to re-process the Skipped !=0 transactions rather doing every transaction
again.

In my view, SKIP LOCKED is a nice feature, which gives only the available
OR unlocked tuples.
But those are not the complete required tuples for the given SQL statement.
Isn't it ?!

Let me know, if I am still not clear about this.

David J.
>
>


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] Eclipse Help

[dinesh kumar]

On Tue, Oct 13, 2015 at 3:49 AM, Praveen M  wrote:

> Hi All,
>
> I was able to follow the debugging of the child process using this link,
> https://wiki.postgresql.org/wiki/Working_with_Eclipse
>
> As per the notes , I was able to set breakpoints and everything seem to be
> working (hopefully). However I am not able to see the debug messages in the
> eclipse console (for the attached process) . Please help
>
> When I check on the console in eclipse , this is the last message I see.
>
> 0x773fad48 in poll () from /lib/x86_64-linux-gnu/libc.so.6
>
> I added a 2 lines in pl_exec.c and kept breakpoints for these lines. The
> breakpoints work fine but I am not able to see the console log.
> I was able to use the log message "ereport(LOG, (errmsg("test here
> started")));" in autovaccum.c line 414 and see the message in the console.
> But this message is from the main process. I am having trouble seeing the
> console log only for the attached process.
>
> pl_exec.c :
>
> Line 310 :
>  ereport(LOG,
> (errmsg("test here started")));
>
> Line 311 :
> elog(ERROR,"test here");
>
>
Not sure what could be the problem. It may be due to your log settings in
postgresql.conf file, OR may the content not flushed yet.

BTW, did you check the log file for these entries.

PS

*I prefer netbeans rather eclipse for local development. If you have enough
resources in your machine, it's worth to give a try latest netbeans.*



> Thanks
> Praveen
>
>
>
>


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

[HACKERS] [PROPOSAL] DIAGNOSTICS = SKIPPED_ROW_COUNT

[dinesh kumar]

Hi Team,

Would like to propose a new DIAGNOSTICS attribute, which returns the no.of
rows got skipped during the FOR UPDATE SKIP LOCKED;

Using this attribute, we can have more control on parallel operations like,

IF SKIPPED_ROW_COUNT =0 THEN
<>
ELSE
<>
END IF;

Kindly let me know your inputs/suggestions on this.

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PROPOSAL] DIAGNOSTICS = SKIPPED_ROW_COUNT

[dinesh kumar]

On Mon, Oct 12, 2015 at 9:38 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> dinesh kumar <dineshkuma...@gmail.com> writes:
> > Would like to propose a new DIAGNOSTICS attribute, which returns the
> no.of
> > rows got skipped during the FOR UPDATE SKIP LOCKED;
>
> I'm concerned that there may not be any implementation-independent
> definition of this.  That is, the query plan might or might not reject
> rows before the locking step is reached, which would result in
> random-looking changes in the output of the proposed counter.
>
> Constraining the query plan might fix that, but only at unacceptable
> performance costs, especially since those constraints would have to apply
> to every plan ever generated (since the query planner can't know whether
> you will inquire about this counter value later).
>
>
Thanks Tom. Understood.


> > Using this attribute, we can have more control on parallel operations
> like,
>
> > IF SKIPPED_ROW_COUNT =0 THEN
> > <>
> > ELSE
> > <>
> > END IF;
>
> Um ... so what?  This is not a use-case.
>
>
In my view, "How one can be sure that, he obtained all the tuples with SKIP
LOCKED". If the end user has this counter value, he may proceed with a
different approach with partially locked tuples.

regards, tom lane
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi All,

Thanks for your inputs on this.

Here, I see a conflict between the doable{RAISE}, and convenience{SQL
function}, and will follow your inputs on this.

Also, I was under impression that, all our TODO
 items are filtered for the real use
cases. Is my impression wrong. If I wanted to work on another TODO item,
where i need to take a look.

Thanks in advance.

-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Thu, Sep 10, 2015 at 8:44 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:

> dinesh kumar wrote:
>
> > Also, I was under impression that, all our TODO
> > <https://wiki.postgresql.org/wiki/Todo> items are filtered for the real
> use
> > cases. Is my impression wrong. If I wanted to work on another TODO item,
> > where i need to take a look.
>
> Your impression is completely, absolutely, horribly wrong.
>
>
:-)


> The TODO contains some ideas that are good but postponed, other ideas
> that are bad but we didn't know at the time they were recorded, other
> ideas that we don't know either way.  Before doing anything on an item
> from the TODO list, you should first read the linked threads (if any),
> and keep track when they end with an email saying "what an awful idea".
> If this doesn't happen, _search_ for other threads not linked on the
> TODO list that also deal with the same topic; note if they end the same
> way (if you find such threads, it's useful to add a link to them in the
> TODO item).
>
Even if you can't find overly negative opinions about some item, discuss
> it here before doing any actual coding.
>
>
Sure.


> I wonder if we need a new page TONOTDO or something like that.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

HI Robert,

On Wed, Sep 9, 2015 at 8:30 PM, Robert Haas <robertmh...@gmail.com> wrote:

> On Wed, Jul 22, 2015 at 9:56 PM, dinesh kumar <dineshkuma...@gmail.com>
> wrote:
> >> The real question is why the existing functionality in plpgsql isn't
> >> sufficient.  Somebody who wants a "log from SQL" function can easily
> >> write a simple plpgsql function that does exactly what they want,
> >> with no more or fewer bells-n-whistles than they need.  If we try
> >> to create a SQL function that does all that, it's likely to be a mess
> >> to use, even with named arguments.
> >>
> >> I'm not necessarily against the basic idea, but I think inventing
> >> something that actually offers an increment in usability compared
> >> to the existing alternative is going to be harder than it sounds.
> >>
> >
> > I agree with your inputs. We can build  pl/pgsql function as alternative
> for
> > this.
> >
> > My initial proposal, and implementation was, logging messages to log file
> > irrespectively of our log settings. I was not sure we can do this with
> some
> > pl/perlu. And then, I started working on our to do item,
> > ereport, wrapper callable from SQL, and found it can be useful to have a
> > direct function call with required log level.
>
> But, why?
>
> I am admitting here that, I don’t know the real use case behind this
proposal in our TODO list. I thought, having ereport wrapper at SQL level,
gives a default debugging behavior for the end users, and this is the only
real use case I see.


> I just took a look at the latest patch and I can't see why it's any
> better than just using PL/pgsql's RAISE statement.
>
> Sure, it’s a clear fact that, we can implement this function with RAISE
statements.

Thanks in advance for your guidance.

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Sun, Sep 6, 2015 at 4:00 PM, dinesh kumar <dineshkuma...@gmail.com>
wrote:

> On Sun, Sep 6, 2015 at 3:39 PM, Pavel Stehule <pavel.steh...@gmail.com>
> wrote:
>
>> Hi
>>
>> attached patch with fixed broken error message
>>
>> Regards
>>
>> Pavel
>>
>
> Hi Pavel,
>
> Thanks much for taking care of it. Patch looks great.
>
>
> Hi Pavel,

Could you let me know, what status value i need to change in commitfest's
UI.


> --
>
> Regards,
> Dinesh
> manojadinesh.blogspot.com
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Sun, Sep 6, 2015 at 3:39 PM, Pavel Stehule 
wrote:

> Hi
>
> attached patch with fixed broken error message
>
> Regards
>
> Pavel
>

Hi Pavel,

Thanks much for taking care of it. Patch looks great.


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Sun, Sep 6, 2015 at 4:46 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-09-06 13:12 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>
>>
>> On Sun, Sep 6, 2015 at 4:00 PM, dinesh kumar <dineshkuma...@gmail.com>
>> wrote:
>>
>>> On Sun, Sep 6, 2015 at 3:39 PM, Pavel Stehule <pavel.steh...@gmail.com>
>>> wrote:
>>>
>>>> Hi
>>>>
>>>> attached patch with fixed broken error message
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>
>>> Hi Pavel,
>>>
>>> Thanks much for taking care of it. Patch looks great.
>>>
>>>
>>> Hi Pavel,
>>
>> Could you let me know, what status value i need to change in commitfest's
>> UI.
>>
>
> if you have not objections, the status can be "ready for commiter"
>
>
I do not have objections. Let's take this to committers for more inputs.

Thanks again.



> Regards
>
> Pavel
>
>
>>
>>
>>> --
>>>
>>> Regards,
>>> Dinesh
>>> manojadinesh.blogspot.com
>>>
>>
>>
>>
>> --
>>
>> Regards,
>> Dinesh
>> manojadinesh.blogspot.com
>>
>
>


-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Fri, Sep 4, 2015 at 2:03 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-09-02 21:49 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>
>> On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule <pavel.steh...@gmail.com>
>> wrote:
>>
>>>
>>>
>>> 2015-09-01 6:59 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
>>>
>>>>
>>>>
>>>> 2015-08-31 20:43 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>
>>>>> Hi,
>>>>>
>>>>> On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule <
>>>>> pavel.steh...@gmail.com> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> I am starting to work review of this patch
>>>>>>
>>>>>> 2015-07-13 9:54 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>>>
>>>>>>> Hi All,
>>>>>>>
>>>>>>> Greetings for the day.
>>>>>>>
>>>>>>> Would like to discuss on below feature here.
>>>>>>>
>>>>>>> Feature:
>>>>>>> Having an SQL function, to write messages to log destination.
>>>>>>>
>>>>>>> Justification:
>>>>>>> As of now, we don't have an SQL function to write
>>>>>>> custom/application messages to log destination. We have "RAISE" clause
>>>>>>> which is controlled by
>>>>>>> log_ parameters. If we have an SQL function which works irrespective
>>>>>>> of log settings, that would be a good for many log parsers. What i mean 
>>>>>>> is,
>>>>>>> in DBA point of view, if we route all our native OS stats to log files 
>>>>>>> in a
>>>>>>> proper format, then we can have our log reporting tools to give most
>>>>>>> effective reports. Also, Applications can log their own messages to
>>>>>>> postgres log files, which can be monitored by DBAs too.
>>>>>>>
>>>>>>> Implementation:
>>>>>>> Implemented a new function "pg_report_log" which takes one
>>>>>>> argument as text, and returns void. I took, "LOG" prefix for all the
>>>>>>> reporting messages.I wasn't sure to go with new prefix for this, since
>>>>>>> these are normal LOG messages. Let me know, if i am wrong here.
>>>>>>>
>>>>>>> Here is the attached patch.
>>>>>>>
>>>>>>
>>>>>> This patch is not complex, but the implementation doesn't cover a
>>>>>> "ereport" well.
>>>>>>
>>>>>> Although this functionality should be replaced by custom function in
>>>>>> any PL (now or near future), I am not against to have this function in
>>>>>> core. There are lot of companies with strong resistance against stored
>>>>>> procedures - and sometimes this functionality can help with SQL 
>>>>>> debugging.
>>>>>>
>>>>>> Issues:
>>>>>>
>>>>>> 1. Support only MESSAGE field in exception - I am expecting to
>>>>>> support all fields: HINT, DETAIL, ...
>>>>>>
>>>>>
>>>>> Added these functionalities too.
>>>>>
>>>>>
>>>>>> 2. Missing regress tests
>>>>>>
>>>>>
>>>>> Adding here.
>>>>>
>>>>>
>>>>>> 3. the parsing ereport level should be public function shared with
>>>>>> PLpgSQL and other PL
>>>>>>
>>>>>
>>>>> Sorry Pavel. I am not getting your point here. Would you give me an
>>>>> example.
>>>>>
>>>>
>>>> The transformation: text -> error level is common task - and PLpgSQL it
>>>> does in pl_gram.y. My idea is to add new function to error utils named
>>>> "parse_error_level" and use it from PLpgSQL and from your code.
>>>>
>>>>
>>>>>
>>>>>
>>>>>> 4. should be hidestmt mandatory parameter?
>>>>>>
>>>>>
>>>>> I changed this argument's default value as &quo

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi Pavel,

On Sat, Sep 5, 2015 at 12:36 AM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-09-05 8:35 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>
>>
>>
>> On Fri, Sep 4, 2015 at 2:03 PM, Pavel Stehule <pavel.steh...@gmail.com>
>> wrote:
>>
>>>
>>>
>>> 2015-09-02 21:49 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>
>>>> On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule <
>>>> pavel.steh...@gmail.com> wrote:
>>>>
>>>>>
>>>>>
>>>>> 2015-09-01 6:59 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
>>>>>
>>>>>>
>>>>>>
>>>>>> 2015-08-31 20:43 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule <
>>>>>>> pavel.steh...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Hi
>>>>>>>>
>>>>>>>> I am starting to work review of this patch
>>>>>>>>
>>>>>>>> 2015-07-13 9:54 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>>>>>
>>>>>>>>> Hi All,
>>>>>>>>>
>>>>>>>>> Greetings for the day.
>>>>>>>>>
>>>>>>>>> Would like to discuss on below feature here.
>>>>>>>>>
>>>>>>>>> Feature:
>>>>>>>>> Having an SQL function, to write messages to log destination.
>>>>>>>>>
>>>>>>>>> Justification:
>>>>>>>>> As of now, we don't have an SQL function to write
>>>>>>>>> custom/application messages to log destination. We have "RAISE" clause
>>>>>>>>> which is controlled by
>>>>>>>>> log_ parameters. If we have an SQL function which works
>>>>>>>>> irrespective of log settings, that would be a good for many log 
>>>>>>>>> parsers.
>>>>>>>>> What i mean is, in DBA point of view, if we route all our native OS 
>>>>>>>>> stats
>>>>>>>>> to log files in a proper format, then we can have our log reporting 
>>>>>>>>> tools
>>>>>>>>> to give most effective reports. Also, Applications can log their own
>>>>>>>>> messages to postgres log files, which can be monitored by DBAs too.
>>>>>>>>>
>>>>>>>>> Implementation:
>>>>>>>>> Implemented a new function "pg_report_log" which takes one
>>>>>>>>> argument as text, and returns void. I took, "LOG" prefix for all the
>>>>>>>>> reporting messages.I wasn't sure to go with new prefix for this, since
>>>>>>>>> these are normal LOG messages. Let me know, if i am wrong here.
>>>>>>>>>
>>>>>>>>> Here is the attached patch.
>>>>>>>>>
>>>>>>>>
>>>>>>>> This patch is not complex, but the implementation doesn't cover a
>>>>>>>> "ereport" well.
>>>>>>>>
>>>>>>>> Although this functionality should be replaced by custom function
>>>>>>>> in any PL (now or near future), I am not against to have this function 
>>>>>>>> in
>>>>>>>> core. There are lot of companies with strong resistance against stored
>>>>>>>> procedures - and sometimes this functionality can help with SQL 
>>>>>>>> debugging.
>>>>>>>>
>>>>>>>> Issues:
>>>>>>>>
>>>>>>>> 1. Support only MESSAGE field in exception - I am expecting to
>>>>>>>> support all fields: HINT, DETAIL, ...
>>>>>>>>
>>>>>>>
>>>>>>> Added these functionalities too.
>>>>>>>
>>>>>>>
>>>>>>>> 2. Missing regress tests
>>>>>>>>
>>>>>>>
>>>>>>&g

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Fri, Sep 4, 2015 at 1:08 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-09-02 21:49 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>
>> On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule <pavel.steh...@gmail.com>
>> wrote:
>>
>>>
>>>
>>> 2015-09-01 6:59 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
>>>
>>>>
>>>>
>>>> 2015-08-31 20:43 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>
>>>>> Hi,
>>>>>
>>>>> On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule <
>>>>> pavel.steh...@gmail.com> wrote:
>>>>>
>>>>>> Hi
>>>>>>
>>>>>> I am starting to work review of this patch
>>>>>>
>>>>>> 2015-07-13 9:54 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>>>
>>>>>>> Hi All,
>>>>>>>
>>>>>>> Greetings for the day.
>>>>>>>
>>>>>>> Would like to discuss on below feature here.
>>>>>>>
>>>>>>> Feature:
>>>>>>> Having an SQL function, to write messages to log destination.
>>>>>>>
>>>>>>> Justification:
>>>>>>> As of now, we don't have an SQL function to write
>>>>>>> custom/application messages to log destination. We have "RAISE" clause
>>>>>>> which is controlled by
>>>>>>> log_ parameters. If we have an SQL function which works irrespective
>>>>>>> of log settings, that would be a good for many log parsers. What i mean 
>>>>>>> is,
>>>>>>> in DBA point of view, if we route all our native OS stats to log files 
>>>>>>> in a
>>>>>>> proper format, then we can have our log reporting tools to give most
>>>>>>> effective reports. Also, Applications can log their own messages to
>>>>>>> postgres log files, which can be monitored by DBAs too.
>>>>>>>
>>>>>>> Implementation:
>>>>>>> Implemented a new function "pg_report_log" which takes one
>>>>>>> argument as text, and returns void. I took, "LOG" prefix for all the
>>>>>>> reporting messages.I wasn't sure to go with new prefix for this, since
>>>>>>> these are normal LOG messages. Let me know, if i am wrong here.
>>>>>>>
>>>>>>> Here is the attached patch.
>>>>>>>
>>>>>>
>>>>>> This patch is not complex, but the implementation doesn't cover a
>>>>>> "ereport" well.
>>>>>>
>>>>>> Although this functionality should be replaced by custom function in
>>>>>> any PL (now or near future), I am not against to have this function in
>>>>>> core. There are lot of companies with strong resistance against stored
>>>>>> procedures - and sometimes this functionality can help with SQL 
>>>>>> debugging.
>>>>>>
>>>>>> Issues:
>>>>>>
>>>>>> 1. Support only MESSAGE field in exception - I am expecting to
>>>>>> support all fields: HINT, DETAIL, ...
>>>>>>
>>>>>
>>>>> Added these functionalities too.
>>>>>
>>>>>
>>>>>> 2. Missing regress tests
>>>>>>
>>>>>
>>>>> Adding here.
>>>>>
>>>>>
>>>>>> 3. the parsing ereport level should be public function shared with
>>>>>> PLpgSQL and other PL
>>>>>>
>>>>>
>>>>> Sorry Pavel. I am not getting your point here. Would you give me an
>>>>> example.
>>>>>
>>>>
>>>> The transformation: text -> error level is common task - and PLpgSQL it
>>>> does in pl_gram.y. My idea is to add new function to error utils named
>>>> "parse_error_level" and use it from PLpgSQL and from your code.
>>>>
>>>>
>>>>>
>>>>>
>>>>>> 4. should be hidestmt mandatory parameter?
>>>>>>
>>>>>
>>>>> I changed this argument's default value as &quo

[HACKERS] [PROPOSAL] Inputs on forcing VACUUM VERBOSE to write timestamp

[dinesh kumar]

Hi All,

Greetings for the day.

Would like to know your inputs/suggestions on below proposal. Kindly let me
know, if it's already taken care.

*Proposal*

Forcing VACUUM VERBOSE to write timestamp, for each "INFO" entry. This was
raised already in this

thread, and wanted to discuss it again. I didn't see any replies on this
old thread.

*Justification*


As we are recording the timestamp too, it gives an idea about how much time
it took for each VACUUM activity.

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PROPOSAL] Inputs on forcing VACUUM VERBOSE to write timestamp

[dinesh kumar]

On Thu, Sep 3, 2015 at 9:48 PM, Andres Freund <and...@anarazel.de> wrote:

> Hi,
>
> On 2015-09-03 21:45:52 +0530, dinesh kumar wrote:
> > Forcing VACUUM VERBOSE to write timestamp, for each "INFO" entry. This
> was
> > raised already in this
> > <
> http://www.postgresql.org/message-id/20031110162349.65542.qm...@web21408.mail.yahoo.com
> >
> > thread, and wanted to discuss it again. I didn't see any replies on this
> > old thread.
>
> Unconvinced - sounds like you're just re-inventing log_line_prefix.
>
> Thanks for your inputs.

I thought, it will be helpful without modifying the log_min_messages
parameter. Otherwise, we have to run the vacuum verbose, by modifying the
log_min_messages at session level, which will be recorded in pg_log.

If we allow VERBOSE to log timestamp, we can have it in stdout, which can
be diverted to a required log file. This will be useful for the vacuumdb
binary too.

Let me know your thoughts.

Regards,
Dinesh
manojadinesh.blogspot.com


> Greetings,
>
> Andres Freund
>

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Mon, Aug 31, 2015 at 10:08 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

>
>
> 2015-09-01 6:59 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>>
>>
>> 2015-08-31 20:43 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>
>>> Hi,
>>>
>>> On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule <pavel.steh...@gmail.com>
>>> wrote:
>>>
>>>> Hi
>>>>
>>>> I am starting to work review of this patch
>>>>
>>>> 2015-07-13 9:54 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>>>>
>>>>> Hi All,
>>>>>
>>>>> Greetings for the day.
>>>>>
>>>>> Would like to discuss on below feature here.
>>>>>
>>>>> Feature:
>>>>> Having an SQL function, to write messages to log destination.
>>>>>
>>>>> Justification:
>>>>> As of now, we don't have an SQL function to write
>>>>> custom/application messages to log destination. We have "RAISE" clause
>>>>> which is controlled by
>>>>> log_ parameters. If we have an SQL function which works irrespective
>>>>> of log settings, that would be a good for many log parsers. What i mean 
>>>>> is,
>>>>> in DBA point of view, if we route all our native OS stats to log files in 
>>>>> a
>>>>> proper format, then we can have our log reporting tools to give most
>>>>> effective reports. Also, Applications can log their own messages to
>>>>> postgres log files, which can be monitored by DBAs too.
>>>>>
>>>>> Implementation:
>>>>> Implemented a new function "pg_report_log" which takes one
>>>>> argument as text, and returns void. I took, "LOG" prefix for all the
>>>>> reporting messages.I wasn't sure to go with new prefix for this, since
>>>>> these are normal LOG messages. Let me know, if i am wrong here.
>>>>>
>>>>> Here is the attached patch.
>>>>>
>>>>
>>>> This patch is not complex, but the implementation doesn't cover a
>>>> "ereport" well.
>>>>
>>>> Although this functionality should be replaced by custom function in
>>>> any PL (now or near future), I am not against to have this function in
>>>> core. There are lot of companies with strong resistance against stored
>>>> procedures - and sometimes this functionality can help with SQL debugging.
>>>>
>>>> Issues:
>>>>
>>>> 1. Support only MESSAGE field in exception - I am expecting to support
>>>> all fields: HINT, DETAIL, ...
>>>>
>>>
>>> Added these functionalities too.
>>>
>>>
>>>> 2. Missing regress tests
>>>>
>>>
>>> Adding here.
>>>
>>>
>>>> 3. the parsing ereport level should be public function shared with
>>>> PLpgSQL and other PL
>>>>
>>>
>>> Sorry Pavel. I am not getting your point here. Would you give me an
>>> example.
>>>
>>
>> The transformation: text -> error level is common task - and PLpgSQL it
>> does in pl_gram.y. My idea is to add new function to error utils named
>> "parse_error_level" and use it from PLpgSQL and from your code.
>>
>>
>>>
>>>
>>>> 4. should be hidestmt mandatory parameter?
>>>>
>>>
>>> I changed this argument's default value as "true".
>>>
>>>
>>>> 5. the function declaration is strange
>>>>
>>>> postgres=# \sf pg_report_log (text, anyelement, boolean)
>>>> CREATE OR REPLACE FUNCTION pg_catalog.pg_report_log(text, anyelement,
>>>> boolean)
>>>>  RETURNS void
>>>>  LANGUAGE sql
>>>>  STABLE STRICT COST 1
>>>> AS $function$SELECT pg_report_log($1::pg_catalog.text,
>>>> $2::pg_catalog.text, $3::boolean)$function$
>>>>
>>>> Why polymorphic? It is useless on any modern release
>>>>
>>>>
>>> I took quote_ident(anyelement) as referral code, for implementing this.
>>> Could you guide me if I am doing wrong here.
>>>
>>
>> I was wrong - this is ok - it is emulation of force casting to text
>>
>>
>>>
>>>
>>>> postgres=# \sf pg_repo

Re: [HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

On Tue, Sep 1, 2015 at 10:58 PM, Stefan Kaltenbrunner <
ste...@kaltenbrunner.cc> wrote:

> On 07/25/2015 03:38 AM, dinesh kumar wrote:
> >
> >
> > On Fri, Jul 24, 2015 at 10:22 AM, Robert Haas <robertmh...@gmail.com
> > <mailto:robertmh...@gmail.com>> wrote:
> >
> > On Thu, Jul 23, 2015 at 8:15 PM, dinesh kumar
> > <dineshkuma...@gmail.com <mailto:dineshkuma...@gmail.com>> wrote:
> > > On Thu, Jul 23, 2015 at 9:21 AM, Robert Haas
> > <robertmh...@gmail.com <mailto:robertmh...@gmail.com>> wrote:
> > >>
> > >> On Thu, Jul 23, 2015 at 12:19 PM, dinesh kumar
> > <dineshkuma...@gmail.com <mailto:dineshkuma...@gmail.com>>
> > >> wrote:
> > >> > Sorry for my  unclear description about the proposal.
> > >> >
> > >> > "WITH PERMISSIVE" is equal to our existing behavior. That is,
> chmod=644
> > >> > on
> > >> > the created files.
> > >> >
> > >> > If User don't specify "PERMISSIVE" as an option, then the
> chmod=600 on
> > >> > created files. In this way, we can restrict the other users
> from reading
> > >> > these files.
> > >>
> > >> There might be some benefit in allowing the user to choose the
> > >> permissions, but (1) I doubt we want to change the default
> behavior
> > >> and (2) providing only two options doesn't seem flexible enough.
> > >>
> > >
> > > Thanks for your inputs Robert.
> > >
> > > 1) IMO, we will keep the exiting behavior as it is.
> > >
> > > 2) As the actual proposal talks about the permissions of
> group/others. So,
> > > we can add few options as below to the WITH clause
> > >
> > > COPY
> > > ..
> > > ..
> > > WITH
> > > [
> > > NO
> > > (READ,WRITE)
> > > PERMISSION TO
> > > (GROUP,OTHERS)
> > > ]
> >
> > If we're going to do anything here, it should use COPY's
> > extensible-options syntax, I think.
> >
> >
> > Thanks Robert. Let me send a patch for this.
>
>
> how are you going to handle windows or unix ACLs here?
> Its permission model is quite different and more powerful than (non-acl
> based) unix in general, handling this in a flexible way might soon get
> very complicated and complex for limited gain...
>
>
Hi Stefan,

I had the same questions too. But, I believe, our initdb works in these
cases, after creating the data cluster. Isn't ?

Regards,
Dinesh
manojadinesh.blogspot.com

>
>
> Stefan
>

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi,

On Sat, Aug 29, 2015 at 4:22 PM, Pavel Stehule <pavel.steh...@gmail.com>
wrote:

> Hi
>
> I am starting to work review of this patch
>
> 2015-07-13 9:54 GMT+02:00 dinesh kumar <dineshkuma...@gmail.com>:
>
>> Hi All,
>>
>> Greetings for the day.
>>
>> Would like to discuss on below feature here.
>>
>> Feature:
>> Having an SQL function, to write messages to log destination.
>>
>> Justification:
>> As of now, we don't have an SQL function to write custom/application
>> messages to log destination. We have "RAISE" clause which is controlled by
>> log_ parameters. If we have an SQL function which works irrespective of
>> log settings, that would be a good for many log parsers. What i mean is, in
>> DBA point of view, if we route all our native OS stats to log files in a
>> proper format, then we can have our log reporting tools to give most
>> effective reports. Also, Applications can log their own messages to
>> postgres log files, which can be monitored by DBAs too.
>>
>> Implementation:
>> Implemented a new function "pg_report_log" which takes one argument
>> as text, and returns void. I took, "LOG" prefix for all the reporting
>> messages.I wasn't sure to go with new prefix for this, since these are
>> normal LOG messages. Let me know, if i am wrong here.
>>
>> Here is the attached patch.
>>
>
> This patch is not complex, but the implementation doesn't cover a
> "ereport" well.
>
> Although this functionality should be replaced by custom function in any
> PL (now or near future), I am not against to have this function in core.
> There are lot of companies with strong resistance against stored procedures
> - and sometimes this functionality can help with SQL debugging.
>
> Issues:
>
> 1. Support only MESSAGE field in exception - I am expecting to support all
> fields: HINT, DETAIL, ...
>

Added these functionalities too.


> 2. Missing regress tests
>

Adding here.


> 3. the parsing ereport level should be public function shared with PLpgSQL
> and other PL
>

Sorry Pavel. I am not getting your point here. Would you give me an example.


> 4. should be hidestmt mandatory parameter?
>

I changed this argument's default value as "true".


> 5. the function declaration is strange
>
> postgres=# \sf pg_report_log (text, anyelement, boolean)
> CREATE OR REPLACE FUNCTION pg_catalog.pg_report_log(text, anyelement,
> boolean)
>  RETURNS void
>  LANGUAGE sql
>  STABLE STRICT COST 1
> AS $function$SELECT pg_report_log($1::pg_catalog.text,
> $2::pg_catalog.text, $3::boolean)$function$
>
> Why polymorphic? It is useless on any modern release
>
>
I took quote_ident(anyelement) as referral code, for implementing this.
Could you guide me if I am doing wrong here.


> postgres=# \sf pg_report_log (text, text, boolean)
> CREATE OR REPLACE FUNCTION pg_catalog.pg_report_log(text, text, boolean)
>  RETURNS void
>  LANGUAGE internal
>  IMMUTABLE STRICT
> AS $function$pg_report_log$function$
>
> Why stable, why immutable? This function should be volatile.
>
> Fixed these to volatile.


> 6. using elog level enum as errcode is wrong idea - errcodes are defined
> in table http://www.postgresql.org/docs/9.4/static/errcodes-appendix.html
>

You mean, if the elevel is 'ERROR', then we need to allow errcode. Let me
know your inputs.

Adding new patch, with the above fixes.

Thanks in advance.

Regards,
Dinesh

>
> Regards
>
> Pavel
>
>
>>
>> Regards,
>> Dinesh
>> manojadinesh.blogspot.com
>>
>
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b3b78d2..1ee8945 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17925,6 +17925,15 @@ postgres=# SELECT * FROM 
pg_xlogfile_name_offset(pg_stop_backup());
 Return information about a file.

   
+  
+   
+pg_report_log(eleveltext, 
message anyelement, 
ishidestmtboolean, detail 
text, hint text, context 
text)
+   
+   void
+   
+Write message into log file as per log level.
+   
+  
  
 

@@ -17993,6 +18002,24 @@ SELECT (pg_stat_file('filename')).modification;
 

 
+   
+pg_report_log
+   
+   
+pg_report_log is useful to write custom messages
+into current log destination and returns void.
+This function don't support the PANIC, FATAL log levels due to their 
unique internal DB usage, which may cause the database instability. Using 
ishidestmt, function can write or ignore the current SQL 
statement into the log file. Also, we can have DETAIL, HINT, CONTEXT log 
messages by provding detail, hint and 
context as 

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Sun, Aug 30, 2015 at 4:52 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:

 Hi

 I am starting to work review of this patch

 Hi Pavel,

Thanks for your review.


 2015-07-13 9:54 GMT+02:00 dinesh kumar dineshkuma...@gmail.com:

 Hi All,

 Greetings for the day.

 Would like to discuss on below feature here.

 Feature:
 Having an SQL function, to write messages to log destination.

 Justification:
 As of now, we don't have an SQL function to write custom/application
 messages to log destination. We have RAISE clause which is controlled by
 log_ parameters. If we have an SQL function which works irrespective of
 log settings, that would be a good for many log parsers. What i mean is, in
 DBA point of view, if we route all our native OS stats to log files in a
 proper format, then we can have our log reporting tools to give most
 effective reports. Also, Applications can log their own messages to
 postgres log files, which can be monitored by DBAs too.

 Implementation:
 Implemented a new function pg_report_log which takes one argument
 as text, and returns void. I took, LOG prefix for all the reporting
 messages.I wasn't sure to go with new prefix for this, since these are
 normal LOG messages. Let me know, if i am wrong here.

 Here is the attached patch.


 This patch is not complex, but the implementation doesn't cover a
 ereport well.

 Although this functionality should be replaced by custom function in any
 PL (now or near future), I am not against to have this function in core.
 There are lot of companies with strong resistance against stored procedures
 - and sometimes this functionality can help with SQL debugging.

 Issues:

 1. Support only MESSAGE field in exception - I am expecting to support all
 fields: HINT, DETAIL, ...
 2. Missing regress tests
 3. the parsing ereport level should be public function shared with PLpgSQL
 and other PL
 4. should be hidestmt mandatory parameter?
 5. the function declaration is strange

 postgres=# \sf pg_report_log (text, anyelement, boolean)
 CREATE OR REPLACE FUNCTION pg_catalog.pg_report_log(text, anyelement,
 boolean)
  RETURNS void
  LANGUAGE sql
  STABLE STRICT COST 1
 AS $function$SELECT pg_report_log($1::pg_catalog.text,
 $2::pg_catalog.text, $3::boolean)$function$

 Why polymorphic? It is useless on any modern release

 postgres=# \sf pg_report_log (text, text, boolean)
 CREATE OR REPLACE FUNCTION pg_catalog.pg_report_log(text, text, boolean)
  RETURNS void
  LANGUAGE internal
  IMMUTABLE STRICT
 AS $function$pg_report_log$function$

 Why stable, why immutable? This function should be volatile.

 6. using elog level enum as errcode is wrong idea - errcodes are defined
 in table http://www.postgresql.org/docs/9.4/static/errcodes-appendix.html


Let me go through each concern and will update you on this.

Regards,
Dinesh
manojadinesh.blogspot.com


 Regards

 Pavel



 Regards,
 Dinesh
 manojadinesh.blogspot.com

Re: [HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

On Fri, Jul 24, 2015 at 10:22 AM, Robert Haas robertmh...@gmail.com wrote:

 On Thu, Jul 23, 2015 at 8:15 PM, dinesh kumar dineshkuma...@gmail.com
 wrote:
  On Thu, Jul 23, 2015 at 9:21 AM, Robert Haas robertmh...@gmail.com
 wrote:
 
  On Thu, Jul 23, 2015 at 12:19 PM, dinesh kumar dineshkuma...@gmail.com
 
  wrote:
   Sorry for my  unclear description about the proposal.
  
   WITH PERMISSIVE is equal to our existing behavior. That is,
 chmod=644
   on
   the created files.
  
   If User don't specify PERMISSIVE as an option, then the chmod=600 on
   created files. In this way, we can restrict the other users from
 reading
   these files.
 
  There might be some benefit in allowing the user to choose the
  permissions, but (1) I doubt we want to change the default behavior
  and (2) providing only two options doesn't seem flexible enough.
 
 
  Thanks for your inputs Robert.
 
  1) IMO, we will keep the exiting behavior as it is.
 
  2) As the actual proposal talks about the permissions of group/others.
 So,
  we can add few options as below to the WITH clause
 
  COPY
  ..
  ..
  WITH
  [
  NO
  (READ,WRITE)
  PERMISSION TO
  (GROUP,OTHERS)
  ]

 If we're going to do anything here, it should use COPY's
 extensible-options syntax, I think.


Thanks Robert. Let me send a patch for this.

Regards,
Dinesh
manojadinesh.blogspot.com


 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

Re: [HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

On Thu, Jul 23, 2015 at 9:21 AM, Robert Haas robertmh...@gmail.com wrote:

 On Thu, Jul 23, 2015 at 12:19 PM, dinesh kumar dineshkuma...@gmail.com
 wrote:
  Sorry for my  unclear description about the proposal.
 
  WITH PERMISSIVE is equal to our existing behavior. That is, chmod=644
 on
  the created files.
 
  If User don't specify PERMISSIVE as an option, then the chmod=600 on
  created files. In this way, we can restrict the other users from reading
  these files.

 There might be some benefit in allowing the user to choose the
 permissions, but (1) I doubt we want to change the default behavior
 and (2) providing only two options doesn't seem flexible enough.


Thanks for your inputs Robert.

1) IMO, we will keep the exiting behavior as it is.

2) As the actual proposal talks about the permissions of group/others. So,
we can add few options as below to the WITH clause

COPY
..
..
WITH
[
NO
(READ,WRITE)
PERMISSION TO
(GROUP,OTHERS)
]

Best Regards,
Dinesh
manojadinesh.blogspot.com


 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

Re: [HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

Hi Robert/Andres,

On Thu, Jul 23, 2015 at 8:34 AM, Robert Haas robertmh...@gmail.com wrote:

 On Thu, Jul 23, 2015 at 11:32 AM, Andres Freund and...@anarazel.de
 wrote:
  On 2015-07-23 11:26:27 -0400, Robert Haas wrote:
  On Wed, Jul 22, 2015 at 11:29 PM, dinesh kumar dineshkuma...@gmail.com
 wrote:
   Would like to propose PERMISSIVE mode for the COPY created out files.
   I mean, at this moment, if do COPY as postgres instance owner, i
 can able
   to read the file as non instance user as well, and would like to
 restrict
   this to
   instance owner WITH PERMISSIVE option.
  
   Let me know your thoughts on this.
 
  I don't understand the proposal.
 
  I understand it as PERMISSIVE := chmod 777 the target file of a COPY TO.

 That doesn't sound like an especially good idea.  What if I want mode 770?


Sorry for my  unclear description about the proposal.

WITH PERMISSIVE is equal to our existing behavior. That is, chmod=644 on
the created files.

If User don't specify PERMISSIVE as an option, then the chmod=600 on
created files. In this way, we can restrict the other users from reading
these files.

Let me know if i am still bad at explaining things here.

Thanks in advance.

Regards,
Dinesh
manojadinesh.blogspot.com

--
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi All,

On Mon, Jul 13, 2015 at 2:12 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Jim Nasby jim.na...@bluetreble.com writes:
  On 7/13/15 3:39 PM, dinesh kumar wrote:
  Ah. It's' my bad interpretation. Let me work on it, and will send a new
  patch as a wrapper sql function for ereport.

  You might want to present a plan for that; it's not as trivial as it
  sounds due to how ereport works. In particular, I'd want to see (at
  minimum) the same functionality that plpgsql's RAISE command now
  provides (errdetail, errhint, etc).


Jim,

For now, I  worked on (ERROR Level, ERROR Message, HIDE ERROR Stmt). In our
to do item description, I found this wrapper needs to return Anyelement.
But, I believe, return VOID is enough for this function. Let me know if I
erred here.

In design phase,

1. I took a CustomDataType with the elevel code, elevel text

2. Populated this CDT with all existing pre-processors, except {FATAL,
PANIC}. Since, we don't expose these to client.

3. By matching the user elevel text, processing the report log function.

Find the attached patch with implementation.



 The real question is why the existing functionality in plpgsql isn't
 sufficient.  Somebody who wants a log from SQL function can easily
 write a simple plpgsql function that does exactly what they want,
 with no more or fewer bells-n-whistles than they need.  If we try
 to create a SQL function that does all that, it's likely to be a mess
 to use, even with named arguments.

 I'm not necessarily against the basic idea, but I think inventing
 something that actually offers an increment in usability compared
 to the existing alternative is going to be harder than it sounds.


Tom,

I agree with your inputs. We can build  pl/pgsql function as alternative
for this.

My initial proposal, and implementation was, logging messages to log file
irrespectively of our log settings. I was not sure we can do this with some
pl/perlu. And then, I started working on our to do item,
ereport, wrapper callable from SQL, and found it can be useful to have a
direct function call with required log level.

Thanks.

Regards,
Dinesh
manojadinesh.blogspot.com

regards, tom lane

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 76f77cb..43dbaec 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17850,6 +17850,15 @@ postgres=# SELECT * FROM 
pg_xlogfile_name_offset(pg_stop_backup());
 Return information about a file.
/entry
   /row
+  row
+   entry
+literalfunctionpg_report_log(parameterelevel/typetext/, 
parametermessage/ typeanyelement/type, 
parameterishidestmt/typeboolean/)/function/literal
+   /entry
+   entrytypevoid/type/entry
+   entry
+Write message into log file as per log level.
+   /entry
+  /row
  /tbody
 /tgroup
/table
@@ -17918,6 +17927,24 @@ SELECT (pg_stat_file('filename')).modification;
 /programlisting
/para
 
+   indexterm
+primarypg_report_log/primary
+   /indexterm
+   para
+functionpg_report_log/ is useful to write custom messages
+into current log destination and returns typevoid/type.
+This function don't support the PANIC, FATAL log levels due to their 
unique internal DB usage, which may cause the database instability. Using 
parameterishidestmt/, function can write or ignore the current SQL 
statement into the log file.
+Typical usages include:
+programlisting
+postgres=# SELECT pg_report_log('NOTICE', 'Custom Message', true);
+NOTICE:  Custom Message
+ pg_report_log 
+---
+ 
+(1 row)
+/programlisting
+   /para
+
   /sect2
 
   sect2 id=functions-advisory-locks
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..1c7c263 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -76,6 +76,91 @@ current_query(PG_FUNCTION_ARGS)
 }
 
 /*
+ * pg_report_log()
+ *
+ * Printing custom log messages in log file.
+ */
+
+typedef struct
+{
+   int ecode;
+   char *level;
+} errorlevels;
+
+Datum
+pg_report_log(PG_FUNCTION_ARGS)
+{
+
+   /*
+* Do not add FATAL, PANIC log levels to the below list.
+*/
+   errorlevels elevels[]={
+   {DEBUG5, DEBUG5}, {DEBUG4, DEBUG4}, {DEBUG3, 
DEBUG3},
+   {DEBUG2, DEBUG2}, {DEBUG1, DEBUG1}, {LOG, LOG},
+   {COMMERROR, COMMERROR}, {INFO, INFO}, {NOTICE, 
NOTICE},
+   {WARNING, WARNING}, {ERROR, ERROR}
+   /*
+* Adding PGERROR to elevels if WIN32
+*/
+   #ifdef WIN32
+   ,{PGERROR, PGERROR}
+   #endif
+   };
+
+   int itr = 0;
+   bool ishidestmt = false;
+   int noelevel = (int) sizeof(elevels)/sizeof(*elevels);
+   char *level;
+
+   level = text_to_cstring(PG_GETARG_TEXT_P(0));
+   ishidestmt = PG_GETARG_BOOL(2

[HACKERS] Proposing COPY .. WITH PERMISSIVE

[dinesh kumar]

Hi All,

Would like to propose PERMISSIVE mode for the COPY created out files.
I mean, at this moment, if do COPY as postgres instance owner, i can able
to read the file as non instance user as well, and would like to restrict
this to
instance owner WITH PERMISSIVE option.

Let me know your thoughts on this.

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Wed, Jul 22, 2015 at 8:56 PM, Michael Paquier michael.paqu...@gmail.com
wrote:

 On Thu, Jul 23, 2015 at 10:56 AM, dinesh kumar dineshkuma...@gmail.com
 wrote:
  Hi All,
 
  On Mon, Jul 13, 2015 at 2:12 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 
  Jim Nasby jim.na...@bluetreble.com writes:
   On 7/13/15 3:39 PM, dinesh kumar wrote:
   Ah. It's' my bad interpretation. Let me work on it, and will send a
 new
   patch as a wrapper sql function for ereport.
 
   You might want to present a plan for that; it's not as trivial as it
   sounds due to how ereport works. In particular, I'd want to see (at
   minimum) the same functionality that plpgsql's RAISE command now
   provides (errdetail, errhint, etc).
 
 
  Jim,
 
  For now, I  worked on (ERROR Level, ERROR Message, HIDE ERROR Stmt). In
 our
  to do item description, I found this wrapper needs to return
 Anyelement.
  But, I believe, return VOID is enough for this function. Let me know
 if I
  erred here.
 
  In design phase,
 
  1. I took a CustomDataType with the elevel code, elevel text
 
  2. Populated this CDT with all existing pre-processors, except {FATAL,
  PANIC}. Since, we don't expose these to client.
 
  3. By matching the user elevel text, processing the report log function.
 
  Find the attached patch with implementation.


Thanks Michael.

Uploaded my patch there.


Regards,
Dinesh
manojadinesh.blogspot.com


 Btw, if you want to get more attention for your patch as well as
 reviews, you should consider registering to the next commit fest of
 September:
 https://commitfest.postgresql.org/6/
 Regards,
 --
 Michael

Re: [HACKERS] [PROPOSAL] VACUUM Progress Checker.

[dinesh kumar]

Hi

On Wed, Jul 15, 2015 at 9:27 PM, Fujii Masao masao.fu...@gmail.com wrote:

 On Thu, Jul 16, 2015 at 5:18 AM, Rahila Syed rahilasye...@gmail.com
 wrote:
  Hello,
 
  Please find attached updated patch with an interface to calculate command
  progress in pgstat.c.

 Thanks for updating the patch!

 I got the following compiler warning.

 guc.c:2316: warning: initialization makes pointer from integer without a
 cast

 make check-world caused lots of failures in my environment.

 Yeah, i got couple of warnings with plain make.


 The following query caused a segmentation fault.


It was the typo I believe. I see the problem is with GUC definition in
guc.c. There should be NULL between gettext_noop and GUC_UNIT_MS.

Regards,
Dinesh
manojadinesh.blogspot.com

SELECT name FROM  (SELECT pg_catalog.lower(name) AS name FROM
 pg_catalog.pg_settings   UNION ALL SELECT 'session authorization'
 UNION ALL SELECT 'all') ss  WHERE substring(name,1,3)='tra';

 Regards,

 --
 Fujii Masao


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

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Mon, Jul 13, 2015 at 1:11 AM, Michael Paquier michael.paqu...@gmail.com
wrote:

 On Mon, Jul 13, 2015 at 4:54 PM, dinesh kumar dineshkuma...@gmail.com
 wrote:
  Would like to discuss on below feature here.
 
  Feature:
  Having an SQL function, to write messages to log destination.
 
  Justification:
  As of now, we don't have an SQL function to write custom/application
  messages to log destination. We have RAISE clause which is controlled
 by
  log_ parameters. If we have an SQL function which works irrespective of
 log
  settings, that would be a good for many log parsers. What i mean is, in
 DBA
  point of view, if we route all our native OS stats to log files in a
 proper
  format, then we can have our log reporting tools to give most effective
  reports. Also, Applications can log their own messages to postgres log
  files, which can be monitored by DBAs too.

 What's the actual use case for this feature other than it would be
 good to have it?


That's a good question Michael.

When i was working as a DBA for a different RDBMS, developers used to write
some serious APP errors, Followed by instructions into some sort of log and
trace files.
Since, DBAs didn't have the permission to check app logs, which was owned
by Ops team.

In my old case, application was having serious OOM issues, which was
crashing frequently after the deployment. It wasn't the consistent behavior
from the app side, hence they used to sent  a copy all APP metrics to trace
files, and we were monitoring the DB what was happening during the spike on
app servers.

I didn't mean that, we need to have this feature, since we have it on other
RDBMS. I don't see a reason, why don't we have this in our PG too.

I see the similar item in our development list
http://www.postgresql.org/message-id/53a8e96e.9060...@2ndquadrant.com.

Let me know if i miss anything here.

Best Regards,
Dinesh
manojadinesh.blogspot.com

A log message is here to give information about the
 state of something that happens in backend, but in the case of this
 function the event that happens is the content of the function itself.
 It also adds a new log level for something that has a unique usage,
 which looks like an overkill to me. Btw, you could do something more
 advanced with simply an extension if you really want to play with this
 area... But I am dubious about what kind of applications would use
 that.
 --
 Michael

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Mon, Jul 13, 2015 at 1:56 PM, Jim Nasby jim.na...@bluetreble.com wrote:

 On 7/13/15 3:39 PM, dinesh kumar wrote:

 I don't really see the point of what you're describing here. Just do
 something like RAISE WARNING which should normally be high enough to
 make it into the logs. Or use a pl language that will let you write
 your own logfile on the server (ie: plperlu).

 True. Using plperlu, shall we bypass our log_* settings. If it's true, i
 wasn't sure about it.


 plperlu can do anything the server can do. Including fun things like
 appending to any file the server can write to or executing things like `rm
 -rf pg_xlog`.


Thanks Much Jim.




  I didn't mean that, we need to have this feature, since we have
 it on
 other RDBMS. I don't see a reason, why don't we have this in our
 PG too.

 I see the similar item in our development list
 
 http://www.postgresql.org/message-id/53a8e96e.9060...@2ndquadrant.com.


 That's not at all what that item is talking about. It's talking
 about exposing ereport as a SQL function, without altering the rest
 of our logging behavior.


 Ah. It's' my bad interpretation. Let me work on it, and will send a new
 patch as a wrapper sql function for ereport.


 You might want to present a plan for that; it's not as trivial as it
 sounds due to how ereport works. In particular, I'd want to see (at
 minimum) the same functionality that plpgsql's RAISE command now provides
 (errdetail, errhint, etc).


Sure. Let me prepare a prototype for it, and will share with you before
implementing.


Best Regards,
Dinesh
manojadinesh.blogspot.com


 --
 Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
 Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: [HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

On Mon, Jul 13, 2015 at 1:29 PM, Jim Nasby jim.na...@bluetreble.com wrote:

 On 7/13/15 12:39 PM, dinesh kumar wrote:

  As of now, we don't have an SQL function to write
 custom/application
  messages to log destination. We have RAISE clause which is
 controlled by
  log_ parameters. If we have an SQL function which works
 irrespective of log
  settings, that would be a good for many log parsers. What i mean
 is, in DBA
  point of view, if we route all our native OS stats to log files in
 a proper
  format, then we can have our log reporting tools to give most
 effective
  reports. Also, Applications can log their own messages to postgres
 log
  files, which can be monitored by DBAs too.

 What's the actual use case for this feature other than it would be
 good to have it?


 That's a good question Michael.

 When i was working as a DBA for a different RDBMS, developers used to
 write some serious APP errors, Followed by instructions into some sort
 of log and trace files.
 Since, DBAs didn't have the permission to check app logs, which was
 owned by Ops team.

 In my old case, application was having serious OOM issues, which was
 crashing frequently after the deployment. It wasn't the consistent
 behavior from the app side, hence they used to sent  a copy all APP
 metrics to trace files, and we were monitoring the DB what was happening
 during the spike on app servers.


 Spewing a bunch of stuff into the postgres log doesn't seem like an
 improvement here.


Agreed Jim.


 I don't really see the point of what you're describing here. Just do
 something like RAISE WARNING which should normally be high enough to make
 it into the logs. Or use a pl language that will let you write your own
 logfile on the server (ie: plperlu).

 True. Using plperlu, shall we bypass our log_* settings. If it's true, i
wasn't sure about it.


  I didn't mean that, we need to have this feature, since we have it on
 other RDBMS. I don't see a reason, why don't we have this in our PG too.

 I see the similar item in our development list
 http://www.postgresql.org/message-id/53a8e96e.9060...@2ndquadrant.com.


 That's not at all what that item is talking about. It's talking about
 exposing ereport as a SQL function, without altering the rest of our
 logging behavior.


Ah. It's' my bad interpretation. Let me work on it, and will send a new
patch as a wrapper sql function for ereport.


Thanks again.

Regards,
Dinesh
manojadinesh.blogspot.com

-- 
 Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
 Data in Trouble? Get it in Treble! http://BlueTreble.com

[HACKERS] [PATCH] SQL function to report log message

[dinesh kumar]

Hi All,

Greetings for the day.

Would like to discuss on below feature here.

Feature:
Having an SQL function, to write messages to log destination.

Justification:
As of now, we don't have an SQL function to write custom/application
messages to log destination. We have RAISE clause which is controlled by
log_ parameters. If we have an SQL function which works irrespective of log
settings, that would be a good for many log parsers. What i mean is, in DBA
point of view, if we route all our native OS stats to log files in a proper
format, then we can have our log reporting tools to give most effective
reports. Also, Applications can log their own messages to postgres log
files, which can be monitored by DBAs too.

Implementation:
Implemented a new function pg_report_log which takes one argument as
text, and returns void. I took, LOG prefix for all the reporting
messages.I wasn't sure to go with new prefix for this, since these are
normal LOG messages. Let me know, if i am wrong here.

Here is the attached patch.

Regards,
Dinesh
manojadinesh.blogspot.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 76f77cb..b2fc4cd 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17850,6 +17850,15 @@ postgres=# SELECT * FROM 
pg_xlogfile_name_offset(pg_stop_backup());
 Return information about a file.
/entry
   /row
+  row
+   entry
+literalfunctionpg_report_log(parametermessage/ 
typetext/type])/function/literal
+   /entry
+   entrytypevoid/type/entry
+   entry
+Write message into log file.
+   /entry
+  /row
  /tbody
 /tgroup
/table
@@ -17918,6 +17927,18 @@ SELECT (pg_stat_file('filename')).modification;
 /programlisting
/para
 
+   indexterm
+primarypg_report_log/primary
+   /indexterm
+   para
+functionpg_report_log/ is useful to write custom messages
+into current log destination and returns typevoid/type.
+Typical usages include:
+programlisting
+SELECT pg_report_log('Message');
+/programlisting
+   /para
+
   /sect2
 
   sect2 id=functions-advisory-locks
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c0495d9..6c54f3a 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -76,6 +76,23 @@ current_query(PG_FUNCTION_ARGS)
 }
 
 /*
+ * pg_report_log()
+ *
+ * Printing custom log messages in log file.
+ */
+
+Datum
+pg_report_log(PG_FUNCTION_ARGS)
+{
+
+   ereport(MESSAGE,
+   (errmsg(%s, text_to_cstring(PG_GETARG_TEXT_P(0))),
+   errhidestmt(true)));
+
+   PG_RETURN_VOID();
+}
+
+/*
  * Send a signal to another backend.
  *
  * The signal is delivered if the user is either a superuser or the same
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index 088c714..2e8f547 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -302,7 +302,7 @@ errstart(int elevel, const char *filename, int lineno,
elevel == INFO);
}
 
-   /* Skip processing effort if non-error message will not be output */
+   /* Skip processing effort if non-error,custom message will not be 
output */
if (elevel  ERROR  !output_to_server  !output_to_client)
return false;
 
@@ -2062,6 +2062,7 @@ write_eventlog(int level, const char *line, int len)
case DEBUG3:
case DEBUG2:
case DEBUG1:
+   case MESSAGE:
case LOG:
case COMMERROR:
case INFO:
@@ -2917,6 +2918,7 @@ send_message_to_server_log(ErrorData *edata)
case DEBUG1:
syslog_level = LOG_DEBUG;
break;
+   case MESSAGE:
case LOG:
case COMMERROR:
case INFO:
@@ -3547,6 +3549,7 @@ error_severity(int elevel)
case DEBUG5:
prefix = _(DEBUG);
break;
+   case MESSAGE:
case LOG:
case COMMERROR:
prefix = _(LOG);
@@ -3666,6 +3669,9 @@ is_log_level_output(int elevel, int log_min_level)
/* Neither is LOG */
else if (elevel = log_min_level)
return true;
+   /* If elevel is MESSAGE, then ignore log settings */
+   else if (elevel == MESSAGE)
+   return true;
 
return false;
 }
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6fd1278..62c619a 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5344,6 +5344,11 @@ DESCR(tsm_bernoulli_reset(internal));
 DATA(insert OID = 3346 (  tsm_bernoulli_cost   PGNSP PGUID 12 1 0 0 0 
f f f f t f v 7 0 2278 2281 2281 2281 2281 2281 2281 2281 

Re: [HACKERS] [PROPOSAL] VACUUM Progress Checker.

[dinesh kumar]

On Tue, Jun 30, 2015 at 1:07 PM, Rahila Syed rahilasye...@gmail.com wrote:

 Hello Hackers,

 Following is a proposal for feature to calculate VACUUM progress.

 Use Case : Measuring progress of long running VACUUMs to help DBAs make
 informed decision
 whether to continue running VACUUM or abort it.

 +1

I am excited to know how the progress works in when any of the statement
got blocked during locks. Rather displaying the stats in the LOG, shall we
have this in a pg_stat_vacuum_activity[ New catalog for all auto-vacuum
stats].

Best Regards,
Dinesh
manojadinesh.blogspot.com

Design:

 A shared preload library to store progress information from different
 backends running VACUUM, calculate remaining time for each and display
 progress in the
 in the form a view.


 VACUUM  needs to be instrumented with a hook to collect progress
 information (pages vacuumed/scanned) periodically.

 The patch attached implements a new hook to store vacuumed_pages and
 scanned_pages count at the end of each page scanned by VACUUM.

 This information is stored in a shared memory structure.

 In addition to measuring progress this function using hook also calculates
 remaining time for VACUUM.



 The frequency of collecting progress information can be reduced by
 appending delays in between hook function calls.

 Also, a GUC parameter

 log_vacuum_min_duration can be used.

 This will cause VACUUM progress to be calculated only if VACUUM runs more
 than specified milliseconds.

 A value of zero calculates VACUUM progress for each page processed. -1
 disables logging.


 Progress calculation :


 percent_complete = scanned_pages * 100 / total_pages_to_be_scanned;

 remaining_time = elapsed_time * (total_pages_to_be_scanned -
 scanned_pages) / scanned_pages;


 Shared memory struct:

 typedef struct PgStat_VacuumStats

 {

   Oid databaseoid;

   Oid tableoid;

   Int32   vacuumed_pages;

   Int32   total_pages;

   Int32   scanned_pages;

   doubleelapsed_time;

   doubleremaining_time;

  } PgStat_VacuumStats[max_connections];



 Reporting :

  A view named 'pg_maintenance_progress' can be created using the values in
 the struct above.

 pg_stat_maintenance can be called from any other backend and will display
 progress of

 each running VACUUM.


 Other uses of hook in VACUUM:


 Cost of VACUUM in terms of pages hit , missed and dirtied same as
 autovacuum can be collected using this hook.

 Autovacuum does it at the end of VACUUM for each table. It can be done
 while VACUUM on a table is in progress.
 This can be helpful to track manual VACUUMs also not just autovacuum.

 Read/Write(I/O) rates can be computed on the lines of autovacuum.
 Read rate patterns can be used to help tuning future vacuum on the
 table(like shared buffers tuning)
 Other resource usages can also be collected using progress checker hook.


 Attached patch is POC patch of progress calculation for a single backend.

 Also attached is a brief snapshot of the output log.




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