from:"Abbas Butt"

Re: [HACKERS] Latches API on windows

2017-10-09 Thread Abbas Butt

Thanks for the suggestion.


On Mon, Oct 9, 2017 at 6:56 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Craig Ringer <cr...@2ndquadrant.com> writes:
> > On 9 October 2017 at 21:26, Abbas Butt <abbas.b...@enterprisedb.com>
> wrote:
> >> In my case this is not true, I am calling InitSharedLatch in _PG_init
> >> which gets called at CREATE EXTENSION time.
> >> My question : Is there a way to get the latches API work on windows
> >> the way it is working on Linux?
>
> > I suspect you'd need to do it by having your extension load via
> > shared_preload_libraries, registering its latch in shmem_startup_hook
>
> Yeah.  That would also let you request your shared memory area honestly,
> instead of relying on there being some slop in the initial allocation.
>
> regards, tom lane
>



-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

[HACKERS] Latches API on windows

2017-10-09 Thread Abbas Butt

Hi,
I am working on a contrib module that uses RegisterDynamicBackgroundWorker
API
to create a couple of worker processes. For synchronization between the
background worker processes I am using InitSharedLatch, SetLatch, WaitLatch
APIs.
One of the processes is supposed to wait for the latch, the other is
supposed to set it.
The system works perfectly fine as long as its run on Linux, however when
tried
on Windows, it fails giving the error:
ResetEvent failed: error code 6
Error code 6 means invalid handle. Debugging reveals that the handle
contains
a valid value, however it seems that the handle is not accessible (was not
created)
in the process that is calling ResetEvent.

Debugging the issue lead me to the following comment on top of
InitSharedLatch:

 * InitSharedLatch needs to be called in postmaster before forking child
 * processes, usually right after allocating the shared memory block
 * containing the latch with ShmemInitStruct. (The Unix implementation
 * doesn't actually require that, but the Windows one does.)

In my case this is not true, I am calling InitSharedLatch in _PG_init
which gets called at CREATE EXTENSION time.
My question : Is there a way to get the latches API work on windows
the way it is working on Linux?

Best Regards

-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co m


*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
 and more

Re: [HACKERS] How to run PG TAP tests on windows?

2017-08-02 Thread Abbas Butt

On Tue, Aug 1, 2017 at 7:35 PM, Michael Paquier <michael.paqu...@gmail.com>
wrote:

> On Tue, Aug 1, 2017 at 10:24 AM, Abbas Butt <abbas.b...@enterprisedb.com>
> wrote:
> > Can anyone point out to a tutorial or a list of steps required to run PG
> TAP
> > tests on windows?
>
> Only MSVC has a special handling:
> https://www.postgresql.org/docs/devel/static/install-windows-full.html#
> idm46046082578368
> Using vcregress.bat, you are looking mainly for the subcommands
> bincheck and recoverycheck.
>

Thanks Michael.



> --
> Michael
>



-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

[HACKERS] How to run PG TAP tests on windows?

2017-08-01 Thread Abbas Butt

Hi,

Can anyone point out to a tutorial or a list of steps required to run PG
TAP tests on windows?

Regards

-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co m


*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
 and more

Re: [HACKERS] PG_TRY & PG_CATCH in FDW development

2017-04-25 Thread Abbas Butt

Thanks for the reply.

On Tue, Apr 25, 2017 at 7:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Abbas Butt <abbas.b...@enterprisedb.com> writes:
> > What is happening for me is that PG_RE_THROW takes me to PG_TRY in the
> same
> > function and then PG_TRY jumps to PG_CATCH where PG_RE_THROW again jumps
> to
> > PG_TRY in the same function resulting in an infinite loop. The query
> > therefore never returns. It is supposed to throw the error and quit.
>
> Apparently PG_exception_stack isn't getting restored properly, but it's
> sure hard to see why.  I'm suspicious that you have something silly like
> mismatched braces in the vicinity of the TRY/CATCH structure.
>

I rechecked, braces are matching.


>
> FWIW, doing things like disconnecting remote sessions might be better
> handled in transaction-cleanup logic, anyway.


I see that postgres_fdw is using a similar login in pgfdw_xact_callback.
Let me try and use the same technique.



>   What covers you for that
> if the query aborts while control is not within your PG_TRY block?
>

All the code that requires a connection to the foreign server is with in
the PG_TRY block, it is therefore not required any where else to close
connection before reporting any error.


>
> regards, tom lane
>



-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

[HACKERS] PG_TRY & PG_CATCH in FDW development

2017-04-25 Thread Abbas Butt

Hi Hackers,

I want to share a technical problem that I am facing while writing code for
an FDW.
The problem is as follows:
In the FDW call back functions it is recommended to use PG_TRY PG_CATCH
blocks along with PG_RE_THROW to disconnect from the foreign server.
I am using the same technique in IterateForeignScan function, and it is
supposed to work like this:

 1  PG_TRY();
 2  {
 3  ... code that might throw ereport(ERROR) ...
 4  }
 5  PG_CATCH();
 6  {
 7disconnect_from_foreign_server();
 8PG_RE_THROW();
 9  }
10  PG_END_TRY();

PG_RE_THROW is supposed to throw the same error again and then take us out
of the function.

What is happening for me is that PG_RE_THROW takes me to PG_TRY in the same
function and then PG_TRY jumps to PG_CATCH where PG_RE_THROW again jumps to
PG_TRY in the same function resulting in an infinite loop. The query
therefore never returns. It is supposed to throw the error and quit.
My question is what could possibly cause this infinite loop?

Thanks in advance.

-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co m


*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
 and more

Re: [HACKERS] An issue in remote query optimization

2017-01-31 Thread Abbas Butt

Sorry for the confusion.
ANALYZE works for the foreign table 'foreign_numbers'.
test=# analyze foreign_numbers;
ANALYZE
test=#



On Tue, Jan 31, 2017 at 5:04 AM, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:

> On Tue, Jan 31, 2017 at 5:23 PM, Abbas Butt <abbas.b...@enterprisedb.com>
> wrote:
> >
> >
> > On Tue, Jan 31, 2017 at 3:15 AM, Etsuro Fujita <
> fujita.ets...@lab.ntt.co.jp>
> > wrote:
> >>
> >> On 2017/01/31 19:53, Abbas Butt wrote:
> >>>
> >>> On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita
> >>> <fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.jp>>
> wrote:
> >>> On 2017/01/31 18:24, Abbas Butt wrote:
> >>
> >>
> >>> Postgres_fdw optimizes remote queries by pushing down the where
> >>> clause.
> >>> This feature does not work consistently when the query is
> >>> executed from
> >>> within a pl/pgsql function. The optimization works when the
> >>> function
> >>> executes the query for the first 5 times, and fails afterwards.
> >>
> >>
> >>> I understand that this is because PostgreSQL starts using
> >>> generic plan
> >>> with pulled up where clause after the 5th invocation hoping
> that
> >>> it
> >>> would be faster since we have skiped planning the query on each
> >>> invocation, but in this case this decision is causing the query
> >>> to slow
> >>> down.
> >>
> >>
> >>> How should we fix this problem?
> >>
> >>
> >>> ANALYZE for the foreign table doesn't work?
> >>
> >>
> >>> No.
> >>>
> >>> analyze ts.tickets;
> >>> WARNING:  skipping "tickets" --- cannot analyze this foreign table
> >>> ANALYZE
> >>
> >>
> >> How the foreign table ts.tickets is defined?
> >
> >
> > test=# \d ts.tickets
> >  Foreign table "ts.tickets"
> >  Column |  Type   | Modifiers | FDW Options
> > +-+---+-
> >  id | integer | not null  |
> > Server: mysql_server
> > FDW Options: (dbname 'msql_test_db', table_name 'tickets')
> >
> > Its a foreign table, referring to table 'tickets' defined on MySQL.
> >
> Isn't your original complaint about postgres_fdw? You can not tickets,
> which is a mongo_fdw foreign table, is probably because mongo_fdw has
> not implemented analyze FDW routine.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>



-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

Re: [HACKERS] An issue in remote query optimization

2017-01-31 Thread Abbas Butt

On Tue, Jan 31, 2017 at 3:15 AM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp>
wrote:

> On 2017/01/31 19:53, Abbas Butt wrote:
>
>> On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita
>> <fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.jp>> wrote:
>> On 2017/01/31 18:24, Abbas Butt wrote:
>>
>
> Postgres_fdw optimizes remote queries by pushing down the where
>> clause.
>> This feature does not work consistently when the query is
>> executed from
>> within a pl/pgsql function. The optimization works when the
>> function
>> executes the query for the first 5 times, and fails afterwards.
>>
>
> I understand that this is because PostgreSQL starts using
>> generic plan
>> with pulled up where clause after the 5th invocation hoping that
>> it
>> would be faster since we have skiped planning the query on each
>> invocation, but in this case this decision is causing the query
>> to slow
>> down.
>>
>
> How should we fix this problem?
>>
>
> ANALYZE for the foreign table doesn't work?
>>
>
> No.
>>
>> analyze ts.tickets;
>> WARNING:  skipping "tickets" --- cannot analyze this foreign table
>> ANALYZE
>>
>
> How the foreign table ts.tickets is defined?
>

test=# \d ts.tickets
 Foreign table "ts.tickets"
 Column |  Type   | Modifiers | FDW Options
+-+---+-
 id | integer | not null  |
Server: mysql_server
FDW Options: (dbname 'msql_test_db', table_name 'tickets')

Its a foreign table, referring to table 'tickets' defined on MySQL.


> Best regards,
> Etsuro Fujita
>
>
>


-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

Re: [HACKERS] An issue in remote query optimization

2017-01-31 Thread Abbas Butt

On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita <fujita.ets...@lab.ntt.co.jp>
wrote:

> On 2017/01/31 18:24, Abbas Butt wrote:
>
>> Postgres_fdw optimizes remote queries by pushing down the where clause.
>> This feature does not work consistently when the query is executed from
>> within a pl/pgsql function. The optimization works when the function
>> executes the query for the first 5 times, and fails afterwards.
>>
>
> I understand that this is because PostgreSQL starts using generic plan
>> with pulled up where clause after the 5th invocation hoping that it
>> would be faster since we have skiped planning the query on each
>> invocation, but in this case this decision is causing the query to slow
>> down.
>>
>> How should we fix this problem?
>>
>
> ANALYZE for the foreign table doesn't work?
>

No.

analyze ts.tickets;
WARNING:  skipping "tickets" --- cannot analyze this foreign table
ANALYZE



>
> Best regards,
> Etsuro Fujita
>
>
>


-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

[HACKERS] An issue in remote query optimization

2017-01-31 Thread Abbas Butt

Hi,
Postgres_fdw optimizes remote queries by pushing down the where clause.
This feature does not work consistently when the query is executed from
within a pl/pgsql function. The optimization works when the function
executes the query for the first 5 times, and fails afterwards.

Example:
Step 1:
Create the table on the foreign server and insert some rows in it
create table numbers(a int, b varchar(255));
insert into numbers values(1, 'One');
insert into numbers values(2, 'Two');
insert into numbers values(3, 'Three');
insert into numbers values(4, 'Four');
insert into numbers values(5, 'Five');
insert into numbers values(6, 'Six');
insert into numbers values(7, 'Seven');
insert into numbers values(8, 'Eight');
insert into numbers values(9, 'Nine');

Step 2:
Create the following objects on the local server
CREATE SERVER pg_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'127.0.0.1', port '5552', dbname 'test');
CREATE USER MAPPING FOR abbasbutt SERVER pg_server OPTIONS (user
'abbasbutt', password 'abc123');
CREATE FOREIGN TABLE foreign_numbers(a int, b varchar(255)) SERVER
pg_server OPTIONS (table_name 'numbers');

create or replace function test_pg_fdw() returns void as $$
DECLARE
 n varchar;
BEGIN
 FOR x IN 1..9 LOOP
 select b into n from foreign_numbers where a=x;
 raise notice 'Found number %', n;
 end loop;
 return;
END
$$ LANGUAGE plpgsql;

Step 3:
Run the test:
select test_pg_fdw();

Step 4:
Check the output of auto_explain in the local server log

2017-01-31 00:39:25 PST LOG:  duration: 8.388 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..111.91 rows=1
width=516)
  Output: b
  Remote SQL: SELECT b FROM public.numbers WHERE ((a = 1))
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.315 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..111.91 rows=1
width=516)
  Output: b
  Remote SQL: SELECT b FROM public.numbers WHERE ((a = 2))
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.250 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..111.91 rows=1
width=516)
  Output: b
  Remote SQL: SELECT b FROM public.numbers WHERE ((a = 3))
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.257 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..111.91 rows=1
width=516)
  Output: b
  Remote SQL: SELECT b FROM public.numbers WHERE ((a = 4))
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.271 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..111.91 rows=1
width=516)
  Output: b
  Remote SQL: SELECT b FROM public.numbers WHERE ((a = 5))
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.251 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..114.91 rows=1
width=516)
  Output: b
  Filter: (foreign_numbers.a = $3)
  Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.246 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..114.91 rows=1
width=516)
  Output: b
  Filter: (foreign_numbers.a = $3)
  Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST CONTEXT:  SQL statement "select bfrom
foreign_numbers where a=x"
PL/pgSQL function test_pg_fdw() line 6 at SQL statement
2017-01-31 00:39:25 PST LOG:  duration: 0.226 ms  plan:
Query Text: select bfrom foreign_numbers where a=x
Foreign Scan on public.foreign_numbers  (cost=100.00..114.91 rows=1
width=516)
  Output: b
  Filter: (foreign_numbers.a = $3)
  Remote SQL: SELECT a, b FROM public.numbers
2017-01-31 00:39:25 PST

Re: [HACKERS] Using a latch between a background worker process and a thread

2016-11-03 Thread Abbas Butt

Thanks every body for the detailed advise.
Let me try replacing latches by condition variables.
I will report the results here.

On Wed, Nov 2, 2016 at 11:54 AM, Craig Ringer <cr...@2ndquadrant.com> wrote:

> On 2 November 2016 at 02:10, Robert Haas <robertmh...@gmail.com> wrote:
> > On Tue, Nov 1, 2016 at 12:35 PM, Abbas Butt <abbas.b...@enterprisedb.com>
> wrote:
> >> Hi,
> >> Consider this situation:
> >> 1. I have a background worker process.
> >> 2. The process creates a latch, initializes it using InitLatch & resets
> it.
> >> 3. It then creates a thread and passes the latch created in step 2 to
> it.
> >> To pass it, the process uses the last argument of pthread_create.
> >> 4. The thread blocks by calling WaitLatch.
> >> 5. The process after some time sets the latch using SetLatch.
> >>
> >> The thread does not notice that the latch has been set and keeps
> waiting.
> >>
> >> My question is:
> >> Are latches supposed to work between a process and a thread created by
> that
> >> process?
> >
> > Nothing in the entire backend is guaranteed to work if you spawn
> > multiple threads within the same process.
> >
> > Including this.
>
> Yep.
>
> You could have the main thread wait on the latch, then signal the
> other threads via appropriate pthread primitives. But you must ensure
> your other threads do nothing that calls into backend code. Including
> things like atexit handlers. They need to coordinate with the main
> thread to do everything PostgreSQL related, and you'd need to make
> sure the main thread handles all signals. That's the default for Linux
> - the main thread gets first chance at all signals and other threads'
> sigmasks are only checked if the main thread has masked the signal,
> but that means your other threads should be sure to mask all signals
> used by PostgreSQL. Good luck doing that portably.
>
> There are exceptions where you can call some backend functions and
> macros from other threads. But you'd have to analyse each on a case by
> case basis, and there's no guarantee they'll _stay_ safe.
>
> I'd just avoid using threads in the backend if at all possible.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>



-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co <http://www.enterprisedb.com/>m
<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
<http://www.enterprisedb.com/resources-community> and more
<http://www.enterprisedb.com/resources-community>

[HACKERS] Using a latch between a background worker process and a thread

2016-11-01 Thread Abbas Butt

Hi,
Consider this situation:
1. I have a background worker process.
2. The process creates a latch, initializes it using InitLatch & resets it.
3. It then creates a thread and passes the latch created in step 2 to it.
To pass it, the process uses the last argument of pthread_create.
4. The thread blocks by calling WaitLatch.
5. The process after some time sets the latch using SetLatch.

The thread does not notice that the latch has been set and keeps waiting.

My question is:
Are latches supposed to work between a process and a thread created by that
process?

Thanks.

-- 
-- 
*Abbas*
Architect

Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co m


*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
 and more

Re: [HACKERS] 9.5 open items

2015-05-17 Thread Abbas Butt

GC=Garbage Collector

On Mon, May 18, 2015 at 9:24 AM, Michael Paquier michael.paqu...@gmail.com
wrote:

 On Mon, May 18, 2015 at 12:35 PM, Josh Berkus j...@agliodbs.com wrote:
  Did it ever occur to you, Bruce, that you've turned into the GC daemon
  for the project?

 GC = global coordinator?
 --
 Michael


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




-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co http://www.enterprisedb.com/m
http://www.enterprisedb.com/

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars, whitepapers
http://www.enterprisedb.com/resources-community and more
http://www.enterprisedb.com/resources-community

Re: [HACKERS] varattno remapping

2013-12-23 Thread Abbas Butt

On Tue, Dec 24, 2013 at 11:47 AM, Craig Ringer cr...@2ndquadrant.comwrote:

 On 12/24/2013 02:35 PM, Craig Ringer wrote:

  So the short version: Given the RTE for a simple view with only one base
  rel and an attribute number for a col in that view, and assuming that
  the view col is a simple reference to a col in the underlying base rel,
  is there any sane way to get the attribute number for the corresponding
  col on the base rel?

 So, of course, I find it as soon as I post.

 map_variable_attnos(...), also in src/backend/rewrite/rewriteManip.c .

 Just generate the mapping table and go.


Could you please explain a little bit more how would you solve the posed
problem using map_variable_attnos?

I was recently working on a similar problem and used the following algo to
solve it.

I had to find to which column of the base table does a column in the select
statement of the view query belong.
To relate a target list entry in the select query of the view to an actual
column in base table here is what I did

First determine whether the var's varno refer to an RTE which is a view?
If yes then get the view query (RangeTblEntry::subquery) and see which
element in the view query's target list does this var's varattno point to.
Get the varno of that target list entry. Look for that RTE in the view's
query and see if that RTE is a real table then use that var making sure its
varno now points to the actual table.

Thanks in advance.




 Sorry for the noise folks.

 --
  Craig Ringer   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training  Services


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




-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co
http://www.enterprisedb.com/mhttp://www.enterprisedb.com/

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars,
whitepapershttp://www.enterprisedb.com/resources-communityand
morehttp://www.enterprisedb.com/resources-community

[HACKERS] A problem with dump/restore of views containing whole row references

2012-04-27 Thread Abbas Butt

Hi,

This is the version I used to run the following commands

select version();

version

 PostgreSQL 9.2devel on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 64-bit
(1 row)

Run these commands

  CREATE TABLE price (id INT PRIMARY KEY, active BOOLEAN NOT NULL, price
NUMERIC);
  insert into price values (1,false,42), (10,false,100), (11,true,17.99);
  create view v2 as select price.*::price from price;
  select * from v2;
  price
  --
   (1,f,42)
   (10,f,100)
   (11,t,17.99)
  (3 rows)

  \d+ v2;
View public.v2
   Column | Type  | Modifiers | Storage  | Description
  +---+---+--+-
   price  | price |   | extended |
  View definition:
   SELECT price AS price
 FROM price;

Note the output from the view, also note the Type in view defination.

Now take dump of this database.

./pg_dump --file=/home/user_name/d.sql --format=p --inserts -p  test

The dump file is attached with the mail. (d.sql)

Now lets restore this dump.

./createdb test2 -p 
./psql -p  -f /home/user_name/d.sql test2
./psql test2 -p 
psql (9.2devel)
Type help for help.

test2=# select * from v2;
 price
---
42
   100
 17.99
(3 rows)

test2=# \d+ v2
   View public.v2
 Column |  Type   | Modifiers | Storage | Description
+-+---+-+-
 price  | numeric |   | main|
View definition:
 SELECT price.price
   FROM price;

In the database test2 the view was not restored correctly.
The output of the view as well as the Type in its defination is wrong.

The cause of the problem is as follows

The notation relation.* represents a whole-row reference.
While parsing a whole-row reference is transformed into a Var with varno
set to the correct range table entry,
and varattno == 0 to signal that it references the whole tuple. (For
reference see comments of function makeWholeRowVar)
While deparsing we need to take care of this case.
The attached patch provides deparsing of a whole-row reference.
A whole row reference will be deparsed either into alias.*::relation or
relation.*::relation depending on alias

--
Abbas
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3beed37..272d1a5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -4007,7 +4007,47 @@ get_variable(Var *var, int levelsup, bool showstar, deparse_context *context)
 	}
 
 	if (attnum == InvalidAttrNumber)
+	{
 		attname = NULL;
+		/*
+		 * The notation relation.* represents a whole-row reference.
+		 * While parsing a whole-row reference is transformed into
+		 * a Var with varno set to the correct range table entry,
+		 * and varattno == 0 to signal that it references the whole tuple.
+		 * For reference see comments of function makeWholeRowVar
+		 * While deparsing we need to take care of this case
+		 * This block of code is deparsing a whole-row reference.
+		 * A whole row reference will be deparsed either into alias.*::relation
+		 * or relation.*::relation depending on alias
+		 * Explicit typecasting to relation is needed because
+		 * input of anonymous composite types is not implemented
+		 */
+
+		if (rte-relid != InvalidOid  refname  var-varattno == 0 
+			var-varno = 1  var-varno = list_length(dpns-rtable))
+		{
+			char *rel_name;
+			List *schemalist;
+
+			schemalist = NULL;
+			if (schemaname)
+list_make1(schemaname);
+
+			/* This relation name is required for explicit type casting later */
+			rel_name = generate_relation_name(rte-relid, schemalist);
+
+			/* Add name space qualification if required */
+			if (schemaname)
+appendStringInfo(buf, %s., quote_identifier(schemaname));
+			appendStringInfoString(buf, quote_identifier(refname));
+			appendStringInfoString(buf, .*::);
+			appendStringInfoString(buf, quote_identifier(rel_name));
+
+			pfree(rel_name);
+
+			return attname;
+		}
+	}
 	else
 		attname = get_rte_attribute_name(rte, attnum);
 
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 0e7177e..cb39881 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -387,9 +387,9 @@ SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table
 (1 row)
 
 SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
-  pg_get_triggerdef   
---
- CREATE

Re: [HACKERS] A problem with dump/restore of views containing whole row references

2012-04-27 Thread Abbas Butt

On Fri, Apr 27, 2012 at 6:25 PM, Andrew Dunstan and...@dunslane.net wrote:



 On 04/27/2012 08:25 AM, Abbas Butt wrote:


 The notation relation.* represents a whole-row reference.
 While parsing a whole-row reference is transformed into a Var with varno
 set to the correct range table entry,
 and varattno == 0 to signal that it references the whole tuple. (For
 reference see comments of function makeWholeRowVar)
 While deparsing we need to take care of this case.
 The attached patch provides deparsing of a whole-row reference.
 A whole row reference will be deparsed either into alias.*::relation or
 relation.*::relation depending on alias


 I agree there's a bug, although it's easily worked around: in the case of
 your example:

   CREATE VIEW v2 AS
SELECT p AS price FROM price p;

 would do the trick.

 However, is this a change we really want to make?:

   pg_get_triggerdef
   --**--**
 --**--**
 --**-
   - CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH
 ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE PROCEDURE
 trigger_func('modified_any')
   +
pg_get_triggerdef
   +-**--**
 --**--**
 --**--**---
   + CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH
 ROW WHEN (old.*::main_table IS DISTINCT FROM new.*::main_table) EXECUTE
 PROCEDURE trigger_func('modified_any')


 Maybe we need to be a bit more selective about when the cast is supplied.
 It's not adding any extra disambiguation (or clarity) here.


I ran the regression and found that my patch is causing a diff in the
trigger test case, thats why I changed the expected output of the test case
accordingly. This is a side effect of the change I did to fix the bug.



 cheers

 andrew




-- 
--
Abbas
Architect
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Phone: 92-334-5100153

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.

Re: [HACKERS] A problem with dump/restore of views containing whole row references

2012-04-27 Thread Abbas Butt

On Fri, Apr 27, 2012 at 11:21 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Andrew Dunstan and...@dunslane.net writes:
  Right, what I'm asking is whether or not we actually want that side
  effect in all cases, and specifically in this case where it's clearly
  not necessary.

 We could dodge that case by only changing the behavior when showstar is
 false; there is no need to change it otherwise.  The patch has assorted
 other bugs too, in particular its schema-name treatment seems completely
 wrong (hint: RelationIsVisible is not the same as TypeIsVisible, and
 it's at best shaky to assume that a relation's name is the same as its
 rowtype's name anyway).

 More generally, it seems rather inelegant to be forcibly adding a cast
 when in most cases the existing notation is not wrong.  AFAICS the
 plain relname notation is only ambiguous if there is a column of the
 same name as the relation.  I wonder whether we should instead address
 this by not letting the parser strip the no op cast in the first
 place.


You mean that the parser should not strip the no op cast in all cases or
in the case only when the parser somehow detects a column of the same name
as the relation?



regards, tom lane


--
Abbas
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Re: [HACKERS] Need more reviewers!

2008-09-06 Thread Abbas Butt

   On Thu, 2008-09-04 at 10:45 -0700, Josh Berkus wrote:
  We currently have 38 patches pending, and only nine people reviewing
 them.
  At this rate, the September commitfest will take three months.


  If you are a postgresql hacker at all, or even want to be one, we need
 your
  help reviewing patches!  There are several easy patches in the list, so
  I can assign them to beginners.
 
  Please volunteer now!


Hi Josh,

I volunteer as a reviewer, assign a patch to me.

Regards
Abbas
www.enterprisedb.com

Re: [HACKERS] Latches API on windows

[HACKERS] Latches API on windows

Re: [HACKERS] How to run PG TAP tests on windows?

[HACKERS] How to run PG TAP tests on windows?

Re: [HACKERS] PG_TRY & PG_CATCH in FDW development

[HACKERS] PG_TRY & PG_CATCH in FDW development

Re: [HACKERS] An issue in remote query optimization

Re: [HACKERS] An issue in remote query optimization

Re: [HACKERS] An issue in remote query optimization

[HACKERS] An issue in remote query optimization

Re: [HACKERS] Using a latch between a background worker process and a thread

[HACKERS] Using a latch between a background worker process and a thread

Re: [HACKERS] 9.5 open items

Re: [HACKERS] varattno remapping

[HACKERS] A problem with dump/restore of views containing whole row references

Re: [HACKERS] A problem with dump/restore of views containing whole row references

Re: [HACKERS] A problem with dump/restore of views containing whole row references

Re: [HACKERS] Need more reviewers!

18 matches

Site Navigation

Mail list logo

Footer information