Re: [HACKERS] PATCH: Batch/pipelining support for libpq

[Prabakaran, Vaishnavi]

On 22 November 2016 at 18:32, Craig Ringer wrote:
> On 22 November 2016 at 15:14, Haribabu Kommi
>  wrote:
> >
> > On Fri, Nov 18, 2016 at 7:18 PM, Craig Ringer 
> wrote:
> >>
> >> The latest is what's attached upthread and what's in the git repo
> >> also referenced upthread.
> >>
> >> I haven't been able to update in response to more recent review due
> >> to other development commitments. At this point I doubt I'll be able
> >> to get update it again in time for v10, so anyone who wants to adopt
> >> it is welcome.
> >
> >
> > Currently patch status is marked as "returned with feedback" in the
> > 11-2016 commitfest. Anyone who wants to work on it can submit the
> > updated patch by taking care of all review comments and change the
> > status of the patch at any time.
> >
> > Thanks for the patch.
>
> Thanks. Sorry I haven't had time to work on it. Priorities.
Hi,
I am interested in this patch and addressed various below comments from 
reviewers. And, I have separated out code and test module into 2 patches. So, 
If needed, test patch can be enhanced more, meanwhile code patch can be 
committed.

>Renaming and refactoring new APIs
> +PQisInBatchMode   172
>+PQqueriesInBatch  173
>+PQbeginBatchMode  174
>+PQendBatchMode175
>+PQsendEndBatch176
>+PQgetNextQuery177
>+PQbatchIsAborted  178
>This set of routines is a bit inconsistent. Why not just prefixing them with 
>PQbatch? Like that for example:
> PQbatchStatus(): consists of disabled/inactive/none, active, error. This 
> covers both PQbatchIsAborted() and PQisInBatchMode().
>PQbatchBegin()
>PQbatchEnd()
>PQbatchQueueSync() or PQbatchQueueFlush, same as PQsendEndBatch() to add and 
>process a sync message into the queue.

Renamed and modified batch status APIs as below
PQisInBatchMode & PQbatchIsAborted ==> PQbatchStatus
PQqueriesInBatch ==> PQbatchQueueCount
PQbeginBatchMode ==> PQbatchBegin
PQendBatchMode ==> PQbatchEnd
PQsendEndBatch ==> PQbatchQueueSync
PQgetNextQuery ==> PQbatchQueueProcess

>PQbatchQueueCount(): returns N>0 if there are N entries, 0 if empty,-1 on 
>failure
>PQbatchQueueProcess(): returns 1 if process can begin, 0 if not, -1 on failure 
>(OOM)
I think it is still ok to keep the current behaviour like other ones present in 
the same file. E.g:"PQsendPrepare" "PQsendQueryGuts"

>PQqueriesInBatch() (Newname(NN):PQbatchQueueCount)doesn't work as documented.
>It says:
>"Returns the number of queries still in the queue for this batch"
>but in fact it's implemented as a Boolean.
Modified the logic to count number of entries in pending queue and return the 
count

>The changes in src/test/examples/ are not necessary anymore. You moved all the 
>tests to test_libpq (for the best actually).
Removed these unnecessary changes from src/test/examples folder and corrected 
the path mentioned in comments section of testlibpqbatch.c

> +   while (queue != NULL)
>+  {
>   PGcommandQueueEntry *prev = queue;
>+   queue = queue->next;
>+   free(prev);
>+   }
>This should free prev->query.
Both prev->query and prev is freed. Also, this applies to "cmd_queue_recycle" 
too.

>Running directly make check in src/test/modules/test_libpq/ does not work
Modified "check" rule in makefile

>You could just remove the VERBOSE flag in the tests, having a test more 
>talkative is always better.
Removed ifdef VERBOSE checks.

>But with the libpq batch API, maybe this could be modernized
>with meta-commands like this:
>\startbatch
>...
>\endbatch
I think it is a separate patch candidate.

> It is possible to guess each one of those errors(occurred in 
> PQbatchQueueProcess API) with respectively
> PQgetResult == NULL, PQisInBatchMode() and PQqueriesInBatch().
> Definitely it should be mentioned in the docs that it is possible to make a 
> difference between all those states.
Updated documentation section of PQbatchQueueProcess() with these details.

> +   entry = PQmakePipelinedCommand(conn);
>+   entry->queryclass = PGQUERY_SYNC;
>+   entry->query = NULL;
>PQmakePipelinedCommand() returns NULL, and boom.
Corrected to return false if PQmakePipelinedCommand() returns NULL.

> +   boolin_batch;   /* connection is in batch (pipelined) mode */
>+   boolbatch_aborted;  /* current batch is aborted, discarding until 
>next Sync */
>Having only one flag would be fine. batch_aborted is set of used only
>when in_batch is used, so both have a strong link
Yes, agree that tracking the batch status via one flag is more clean
So, Added new enum typedef enum
{
PQBATCH_MODE_OFF,
PQBATCH_MODE_ON,
PQBATCH_MODE_ABORTED
} PQBatchStatus;
and " PQBatchStatus batch_status"  member of pg_conn is used to track the 
status of batch mode.

>/* OK, it's launched! */
>-   conn->asyncStatus = PGASYNC_BUSY;
>+   if (conn->in_batch)
>+   PQappendPipelinedCommand(conn, pipeCmd);
>+   else
>+   

Re: [HACKERS] New option in pg_basebackup to exclude pg_log files during base backup

[Prabakaran, Vaishnavi]

On Thursday, Apr 10,2014 at 1:15Am, Álvaro Herrera wrote:
Magnus Hagander wrote:
On Wed, Apr 9, 2014 at 4:55 PM, Alvaro Herrera 
alvhe...@2ndquadrant.comwrote:

  So it'd be an array, and by default you'd have something like:
   basebackup_skip_path = $log_directory ?
 
  Maybe use it to skip backup labels by default as well.
  basebackup_skip_path = $log_directory, $backup_label_files
 
 
 I hadn't considered any details, but yes, someting along that line. 
 And then you could also include arbitrary filenames or directories 
 should you want. E.g. if you use the data directory to store your 
 torrents or something.

Man, that's a great idea.  Database servers have lots of diskspace in that 
partition, so it should work really well.  Thanks!

Yes, It sounds like a good idea. I will look into this and start working in 
sometime. 

Thanks  Regards,
Vaishnavi
Fujitsu Australia



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

[HACKERS] New option in pg_basebackup to exclude pg_log files during base backup

[Prabakaran, Vaishnavi]

Hi all,

Following the discussion in message id - 
cahgqgwffmor4ecugwhzpaapyqbsekdg66vmj1rvej6z-ega...@mail.gmail.commailto:cahgqgwffmor4ecugwhzpaapyqbsekdg66vmj1rvej6z-ega...@mail.gmail.com
  , I have developed the patch which gives option to user to exclude pg_log 
directory contents in pg_basebackup.

[Current situation]
During pg_basebackup, all files in pg_log directory will be copied to new 
backup directory.

[Design]
- Added new non-mandatory option -S/--skip-log-dir to pg_basebackup .
- If skip-log-dir is specified in pg_basebackup command, then in basebackup, 
exclude copying log files from standard pg_log directory and any other 
directory specified in Log_directory guc variable. (Still empty folder 
pg_log/$Log_directory will be created)
- In case, pg_log/$Log_directory is symbolic link, then an empty folder will be 
created

[Advantage]
It gives an option to user to avoid copying of large log files if they doesn't 
wish to and hence can save memory space.

Attached the patch.


Thanks  Regards,
Vaishnavi
Fujitsu Australia



pgbasebackup_excludes_pglog_v1.patch
Description: pgbasebackup_excludes_pglog_v1.patch

-- 
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] Providing catalog view to pg_hba.conf file - Patch submission

[Prabakaran, Vaishnavi]

On Friday, Mar 14, 2014 at 9:33 PM, Maganus Hagander 
mag...@hagander.netmailto:mag...@hagander.net  wrote:

Hi,
 In connection to my previous proposal about providing catalog view to 
 pg_hba.conf file contents , I have developed the attached patch .
  [Current situation]
Currently, to view the pg_hba.conf file contents, DB admin has to access the 
file from database server to read the settings.  In case of huge and multiple 
hba files, finding the appropriate hba rules which are loaded will be 
difficult and take some time.

 [What this Patch does]
Functionality of the attached patch is that it will provide a new view 
pg_hba_settings to admin users. Public access to the view is restricted. 
This view will display basic information about HBA setting details of 
postgresql cluster.  Information to be shown , is taken from parsed hba 
lines and not directly read from pg_hba.conf files. Documentation files are 
also updated to include details of this new view under Chapter 47.System 
Catalogs. Also , a new note is added in chapter 19.1 The pg_hba.conf File
  [Advantage]
Advantage of having this pg_hba_settings view is that the admin can check, 
what hba rules are loaded in runtime via database connection itself.  And, 
thereby it will be easy and useful for admin to check all the users with 
their privileges in a single view to manage them.
 This looks like a useful feature, so make sure you register it on 
 https://commitfest.postgresql.org/action/commitfest_view?id=22.
Sure, I will add it to commitfest.
I haven't looked at the actual code yet, btu I did notice one thing at a very 
quick lookover at the docs - it seems to be completely ignoring the key/value 
parameters given on a row, and stops reporting after the auth method? That 
seems bad. And also, probably host/mask should be using the inet style 
datatypes and not text?

Added new column configuration_option to pg_hba_settings view to display the 
key/value parameter set. Attached the updated patch.


Thanks  Regards,
Vaishnavi
Fujitsu Australia


Catalog_view_to_HBA_settings_patch_V2.patch
Description: Catalog_view_to_HBA_settings_patch_V2.patch

-- 
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] Providing catalog view to pg_hba.conf file - Patch submission

[Prabakaran, Vaishnavi]

From: Magnus Hagander [mailto:mag...@hagander.net]
Sent: Friday, 14 March 2014 9:33 PM
To: Prabakaran, Vaishnavi
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch 
submission

On Fri, Mar 14, 2014 at 6:30 AM, Prabakaran, Vaishnavi 
vaishna...@fast.au.fujitsu.commailto:vaishna...@fast.au.fujitsu.com wrote:
Hi,

In connection to my previous proposal about providing catalog view to 
pg_hba.conf file contents , I have developed the attached patch .

[Current situation]
Currently, to view the pg_hba.conf file contents, DB admin has to access the 
file from database server to read the settings.  In case of huge and multiple 
hba files, finding the appropriate hba rules which are loaded will be difficult 
and take some time.

[What this Patch does]
Functionality of the attached patch is that it will provide a new view 
pg_hba_settings to admin users. Public access to the view is restricted. This 
view will display basic information about HBA setting details of postgresql 
cluster.  Information to be shown , is taken from parsed hba lines and not 
directly read from pg_hba.conf files. Documentation files are also updated to 
include details of this new view under Chapter 47.System Catalogs. Also , a 
new note is added in chapter 19.1 The pg_hba.conf File

[Advantage]
Advantage of having this pg_hba_settings view is that the admin can check, 
what hba rules are loaded in runtime via database connection itself.  And, 
thereby it will be easy and useful for admin to check all the users with their 
privileges in a single view to manage them.


This looks like a useful feature, so make sure you register it on 
https://commitfest.postgresql.org/action/commitfest_view?id=22.

I haven't looked at the actual code yet, btu I did notice one thing at a very 
quick lookover at the docs - it seems to be completely ignoring the key/value 
parameters given on a row, and stops reporting after the auth method? That 
seems bad. And also, probably host/mask should be using the inet style 
datatypes and not text?

Agree, am now working on including a new column configuration_option to 
display the key/value parameter set.  I will send the updated patch once after 
adding new column.
Host/mask values are stored as sockaddr_storage structure in parsed_hba_lines, 
so I have used text datatype to display the hostname.

Thanks  Regards,
Vaishnavi
Fujitsu Australia
--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

[HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

[Prabakaran, Vaishnavi]

Hi,

 

In connection to my previous proposal about providing catalog view to
pg_hba.conf file contents , I have developed the attached patch . 

 

[Current situation]

Currently, to view the pg_hba.conf file contents, DB admin has to access
the file from database server to read the settings.  In case of huge and
multiple hba files, finding the appropriate hba rules which are loaded
will be difficult and take some time. 

 

[What this Patch does] 

Functionality of the attached patch is that it will provide a new view
pg_hba_settings to admin users. Public access to the view is
restricted. This view will display basic information about HBA setting
details of postgresql cluster.  Information to be shown , is taken from
parsed hba lines and not directly read from pg_hba.conf files.
Documentation files are also updated to include details of this new view
under Chapter 47.System Catalogs. Also , a new note is added in
chapter 19.1 The pg_hba.conf File

 

[Advantage]

Advantage of having this pg_hba_settings view is that the admin can
check, what hba rules are loaded in runtime via database connection
itself.  And, thereby it will be easy and useful for admin to check all
the users with their privileges in a single view to manage them. 

 

 

 

Thanks  Regards,

Vaishnavi

Fujitsu Australia

 



Catalog_view_to_HBA_settings_patch.patch
Description: Catalog_view_to_HBA_settings_patch.patch

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

[HACKERS] Proposal/design feedback needed: Providing catalog view to pg_hba.conf file

[Prabakaran, Vaishnavi]

Hi All,

 

I would like to propose an implementation of creating new catalog view
for pg_hba.conf file contents. Aim of this proposal is to present a new
view pg_settings_hba to database administrator, for viewing
pg_hba.conf file contents. 

 

Currently, to view the pg_hba.conf file contents, DB admin has to access
the file from database server to read the settings.  In case of huge and
multiple hba files, finding the appropriate hba rules which are loaded
will be difficult and take some time. 

 

Advantage of having this pg_settings_hba view is that the admin can
check what hba rules are loaded in runtime via database connection
itself.  And, thereby it will be easy and useful for admin to check all
the users with their privileges in a single view to manage them. 

 

Since exposing this view to everyone poses a security problem, access of
this view will be limited to super user. 

As a first step, am proposing only the SELECT option for this new view.
Later, based on your feedbacks, I would like to add UPDATE/DELETE
options also to this view. 

 

Here is the brief design of the proposal:

1.   Create a new view pg_settings_hba in system_views.sql.

Structure of new view:

 

ColumnType

--   --

connection_type text

databases   text[]

roles text[]

socket_Address   text

socket_Mask text

compare_Method  text

hostName  text

authMethod text

linenumber   integer

 

2.   Grant select permission of this view to super user.

3.   Adding new function in guc.c (and in hba.c to load data from
parsed hba lines)  to create tuple descriptor . CREATE VIEW command in
system_views.sql will make use of this new function, in guc.c, to build
view.

 

Input for this view is taken from parsed hba lines and not from files
directly. 

 

Any comments or feedback on this proposal?

 

 

 

 

Thanks  Regards,

Vaishnavi

 

[HACKERS] Proposal - Support for 'OR REPLACE' in 'CREATE TRIGGER' command

[Prabakaran, Vaishnavi]

Hi,

 

This is a proposal to implement support for 'OR REPLACE' in 'CREATE
TRIGGER' command as an optional clause. 

 

The aim of this proposal is to present a way to support replace trigger
functionality. 

Adding the optional clause 'OR REPLACE' in CREATE TRIGGER syntax gives
users the option of redefining the trigger in single command. 

And such support will be helpful for customers when they are migrating
from other RDBMs, which already has this support in-built.

 

As PostgreSQL supports 'OR REPLACE' in various commands such as CREATE
VIEW, CREATE FUNCTION, CREATE LANGUAGE, CREATE RULE,  implementing this
support to 'CREATE TRIGGER'  command also will be useful for different
migration/schema upgrade scripts.

 

[Brief Design]

Brief design of 'OR REPLACE' support in 'CREATE TRIGGER' implementation
is presented below:

- Updating parser rules to support 'OR REPLACE' as an optional clause in
the syntax of 'CREATE TRIGGER' command.

- Support to update existing trigger's system table(pg_trigger) record
with new definition.

- Support to update existing trigger's dependencies(which the trigger
depends on)based on new trigger definition. 

- Following the existing system behaviour of other commands that
supports 'OR REPLACE' functionality, any dependent object should not be
dropped while replacing the trigger. 

Only identified dependent object is the constraint object, created for
constraint trigger. When the constraint trigger is replaced by regular
trigger, to avoid having constraint object still tagged to regular
trigger, a new condition will be enforced to restrict replacing
constraint trigger with regular trigger and vice-versa.  Any attempt to
replace between regular and constraint triggers displays new error
messages. 

 

 

I can start working on a patch based on the above.

 

Thanks  Regards,

Vaishnavi

 

Re: [HACKERS] Differences in WHERE clause of SELECT

[Prabakaran, Vaishnavi]

Hi,

Thanks for your responses.

The specific use case which I am interested in is 

 Numeric LIKE Pattern_string .

I'm willing to attempt a patch to support the specific use case above by adding 
implicit casts, without modifying the entire casting rules.

Is this something that is likely to be included in the code ? 

Thanks  Regards,
Vaishnavi

-Original Message-
From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Kevin Grittner
Sent: Wednesday, 17 July 2013 6:23 AM
To: Robert Haas; Merlin Moncure
Cc: Tom Lane; Josh Berkus; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Differences in WHERE clause of SELECT

Robert Haas robertmh...@gmail.com wrote:

 We can certainly continue to play whack-a-mole and dream up a new 
 solution every time a really intolerable variant of this problem comes 
 up.  But that doesn't seem good to me.  It means that every case 
 behaves a little different from every other case, and the whole thing 
 is kinda arcane and hard to understand, even for hackers.

If you're building up a list of things that generate errors in PostgreSQL but 
not other DBMS products, make sure you have this:

test=# create table t(d date);
CREATE TABLE
test=# insert into t values (NULL);
INSERT 0 1
test=# insert into t values (COALESCE(NULL, NULL));
ERROR:  column d is of type date but expression is of type text LINE 1: 
insert into t values (COALESCE(NULL, NULL));
  ^
HINT:  You will need to rewrite or cast the expression.

From a user perspective, it's hard to explain why COALESCE(NULL,
NULL) fails in a location that a bare NULL works.  From the perspective of 
those working on the code, and looking at the problem from the inside out, it 
seems sane; but that's the only perspective from which it does.

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


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




-- 
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] Differences in WHERE clause of SELECT

[Prabakaran, Vaishnavi]

Hi Berkus,

Thanks for your time and response.

I do understand that there is no LIKE operator support for integers and it 
would be great if you could help me understand the reason why is it not 
supported.

My intention is to know whether this is not supported because of any technical 
limitation or is it against any Postgresql/SQL standards. 

My use cases are like below ones :
Integer LIKE pattern [ESCAPE escape-character] 
1. List all the customers who are having negative balance:
SELECT * from Customer where balance LIKE ‘-%’

2. List all the customers whose id starts with 1:
SELECT * from Customer where cust_id LIKE ‘1%’

Thanks  Regards,
Vaishnavi


-Original Message-
From: pgsql-hackers-ow...@postgresql.org 
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Josh Berkus
Sent: Wednesday, 10 July 2013 9:13 AM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Differences in WHERE clause of SELECT

Prabakaran,


 I am a newbie to PostgreSQL and was wondering about the following 
 behaviour.

pgsql-hackers is not the appropriate list for this kind of question.  In the 
future, please post to pgsql-novice, pgsql-sql, or pgsql-general with this kind 
of question.  Thanks.

 Can you please help me understand why 'LIKE' does not use implicit 
 cast ?

Like uses the operator class text_pattern_ops which doesn't include an 
implict cast.  For one thing, the implicit cast is from text -- integer, not 
the other way around, and there is no LIKE operator for integers.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


-- 
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] Differences in WHERE clause of SELECT

[Prabakaran, Vaishnavi]

On Wed, Jul 10, 2013 at 8:42 PM, Prabakaran, Vaishnavi 
vaishna...@fast.au.fujitsu.com wrote:
 Hi Berkus,

 Thanks for your time and response.

 I do understand that there is no LIKE operator support for integers and it 
 would be great if you could help me understand the reason why is it not 
 supported.

 My intention is to know whether this is not supported because of any 
 technical limitation or is it against any Postgresql/SQL standards.


 the latter

I see. Understood. Looking at the SQL standard it does say that the operands 
needs to be character or octet. But I was hoping that this can be overridden by 
implicit conversion rules which are implementation specific.  

 My use cases are like below ones :
 Integer LIKE pattern [ESCAPE escape-character] 1. List all the 
 customers who are having negative balance:
 SELECT * from Customer where balance LIKE '-%'


this is not cleaner implemented this way?
SELECT * FROM customer WHERE balance  0;

 2. List all the customers whose id starts with 1:
 SELECT * from Customer where cust_id LIKE '1%'


 there is any real use for that query? i understand if you ask for all 
 customers whose names begins with 'A' but that the code begins with '1'?

A legacy application we are migrating does have a weird requirement like this 
because it was running on 'another' RDBMS which does have support for implicit 
casting in LIKE predicate.

Rgds,
Vaishnavi


--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157




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

[HACKERS] Differences in WHERE clause of SELECT

[Prabakaran, Vaishnavi]

Hi,

 

I am a newbie to PostgreSQL and was wondering about the following
behaviour.

 

 

SELECT true WHERE 1 = '1'; -- Returns true 

 

SELECT true WHERE 1 BETWEEN '0' and '2';-- Returns true

 

SELECT true WHERE 1 IS DISTINCT FROM '2';-- Returns true

 

SELECT true WHERE 1 LIKE '1';   -- Returns 'operator does not exist'
Error

 

SELECT true WHERE '1' LIKE 1;   -- Returns 'operator does not exist'
Error

 

 

 

The first three queries work because of the implicit cast whereas the
'LIKE' is not using implicit cast. 

 

Can you please help me understand why 'LIKE' does not use implicit cast
? 

 

 

 

Best Regards,

Vaishnavi