[HACKERS] temp table problem

[Abbas]

Hi,
I have come across a problem. When you try to access a temp table
created via SPI_EXEC, you get a table not found error.

  SPI_EXEC("CREATE TEMP TABLE my_temp_table(first_name text, last_name
text)", UTILITY);
  SPI_EXEC("REVOKE ALL ON TABLE my_temp_table FROM PUBLIC", UTILITY);

The second statement generates a table not found error, although the
first statement was successful.

After initdb the system has no temp namespace to hold
temp objects and hence the search path does not contain 
any temp namespace either.
On first call to create a temp table the system first creates
a temp namespace. At this point the system calls recomputeNamespacePath
thinking that it would update search path and include the temp namespace
in it, but that does not happen beccause of override search path stack.
Hence subsquent calls to say insert into the temp table fail.

Any suggestions on how to tackle this problem?

Regards
Abbas
EnterpriseDB   http://www.enterprisedb.com



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

[HACKERS] Postgresql coding conventions

[Abbas]

Hi,
I have noticed two different coding conventions being followed in
postgres code base.

See e.g. function names in syslogger.c

static void set_next_rotation_time(void);
static void sigHupHandler(SIGNAL_ARGS);

and variable names in the same file

int bytes_in_logbuffer = 0;
char*currentLogDir;

Chapter 46 of the documentation does not say much about variable or
function naming.

While writing code or reviewing a path are we supposed to consider the
camel cased names correct or the under-score separated names correct?

Regards
Abbas
www.enterprisedb.com



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

[HACKERS] [REVIEW] Prototype: In-place upgrade v02

[Abbas]

Hi,
I downloaded latest postgresql source code from
 git clone git://git.postgresql.org/git/postgresql.git
and tried to apply the patch 
 http://archives.postgresql.org/pgsql-hackers/2008-09/gza1fGXLvf3L.gz

It does not apply cleanly, see the failures in attached file.

Regards
Abbas
www.enterprisedb.com

patch -p1 
-- 
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] [REVIEW] Prototype: In-place upgrade v02

[Abbas]

Even with that a hunk failed for bufpage.c, but I applied that part
manually to move on.
Regards
Abbas

On Thu, 2008-09-18 at 12:17 +0200, Zdenek Kotala wrote:
> Abbas napsal(a):
> > Hi,
> > I downloaded latest postgresql source code from
> >  git clone git://git.postgresql.org/git/postgresql.git
> > and tried to apply the patch 
> >  http://archives.postgresql.org/pgsql-hackers/2008-09/gza1fGXLvf3L.gz
> > 
> > It does not apply cleanly, see the failures in attached file.
> 
> It clash with hash index patch which was committed four days ago. Try to use 
> little bit older revision from git (without hash index modification).
> 
>   Zdenek
> 
> 
> 


-- 
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] [REVIEW] Prototype: In-place upgrade v02

[Abbas]

Hi,

I have gone through the following stuff

1) previous emails on the patch
2) http://wiki.postgresql.org/wiki/In-place_upgrade
3) http://www.pgcon.org/2008/schedule/attachments/57_pg_upgrade_2008.pdf
4) http://wiki.postgresql.org/wiki/In-place_upgrade:Storage

Here is what I have understood so far, (correct me if I am wrong)

The on disk representation of data has changed from version to version
over the years. For some strange reason (performance may be) the newer
versions of pg were not backwards compatible, meaning that the newer
version would not read data written by an older version if the on disk
representation has changed in between.
The end user would be required to port the data stored using older
version to the newer version format using offline import export.
This project aims upgrades from older to newer version on the fly.
On-disk representation is not the only change that the system should
accommodate, it should also accommodate catalog changes, conf file
changes etc.

Of the available design choices I think you have chosen to go with
on-line data conversion, meaning that pg would now be aware of all the
previous page layouts and based on a switch on page version would handle
each page layout. This will only be done to read old data, newer data
will be written in newer format.

I am supposed to test the patch and for that I have downloaded pg
versions 7.4, 8.0, 8.1, 8.2 and 8.3.

I plan to create a data directory using each of the versions and then
try to read the same using the 8.4 with your patch applied.

What database objects should I create in the test database, should I
just create objects of my choice?

Does sizes (both length and breadth) of tables matter?

Do I have to perform performance tests too?

Regards
Abbas




On Fri, 2008-09-19 at 14:28 +0200, Zdenek Kotala wrote:
> thanks
> 
> Abbas napsal(a):
> > Even with that a hunk failed for bufpage.c, but I applied that part
> > manually to move on.
> > Regards
> > Abbas
> > 
> > On Thu, 2008-09-18 at 12:17 +0200, Zdenek Kotala wrote:
> >> Abbas napsal(a):
> >>> Hi,
> >>> I downloaded latest postgresql source code from
> >>>  git clone git://git.postgresql.org/git/postgresql.git
> >>> and tried to apply the patch 
> >>>  http://archives.postgresql.org/pgsql-hackers/2008-09/gza1fGXLvf3L.gz
> >>>
> >>> It does not apply cleanly, see the failures in attached file.
> >> It clash with hash index patch which was committed four days ago. Try to 
> >> use 
> >> little bit older revision from git (without hash index modification).
> >>
> >>Zdenek
> >>
> >>
> >>
> > 
> 
> 


-- 
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] [REVIEW] Prototype: In-place upgrade v02

[Abbas]

On Mon, 2008-09-29 at 14:42 +0200, Zdenek Kotala wrote:
> Abbas napsal(a):
> > Hi,
> > 
> > I have gone through the following stuff
> > 
> > 1) previous emails on the patch
> > 2) http://wiki.postgresql.org/wiki/In-place_upgrade
> > 3) http://www.pgcon.org/2008/schedule/attachments/57_pg_upgrade_2008.pdf
> > 4) http://wiki.postgresql.org/wiki/In-place_upgrade:Storage
> > 
> > Here is what I have understood so far, (correct me if I am wrong)
> > 
> > The on disk representation of data has changed from version to version
> > over the years. For some strange reason (performance may be) the newer
> > versions of pg were not backwards compatible, meaning that the newer
> > version would not read data written by an older version if the on disk
> > representation has changed in between.
> > The end user would be required to port the data stored using older
> > version to the newer version format using offline import export.
> > This project aims upgrades from older to newer version on the fly.
> > On-disk representation is not the only change that the system should
> > accommodate, it should also accommodate catalog changes, conf file
> > changes etc.
> 
> It is correct.
> 
> > Of the available design choices I think you have chosen to go with
> > on-line data conversion, meaning that pg would now be aware of all the
> > previous page layouts and based on a switch on page version would handle
> > each page layout. This will only be done to read old data, newer data
> > will be written in newer format.
> 
> Yes.
> 
> > I am supposed to test the patch and for that I have downloaded pg
> > versions 7.4, 8.0, 8.1, 8.2 and 8.3.
> >
> > I plan to create a data directory using each of the versions and then
> > try to read the same using the 8.4 with your patch applied.
> 
> It does not work. The patch is only prototype. It contains framework for 
> implementing old page layout version and it contains partial version 3.
> 
> The main purpose of this prototype is to make decision if this approach is 
> acceptable or not. Or if some part is acceptable - it contains for example 
> useful page API rework and implementation which is useful (by my opinion) in 
> general.
> 
> > What database objects should I create in the test database, should I
> > just create objects of my choice?
> > 
> > Does sizes (both length and breadth) of tables matter?
> 
> These test does not make sense at this moment. I have test script (created by 
> Nidhi) for catalog upgrade already done. However, it uses currently Sun's 
> internal framework. I will modify it and release it.
> 
> > Do I have to perform performance tests too?
> 
> Yes, please. My colleague tested it and got 5% performance drop, but it was 
> not 
> complete version and I tested full patch on Friday and It was surprise for me 
> ...  I got little bit better throughput (about 0,5%) with patch. I'm going to 
> retest it again but it would be good to get result also from others.


Are there defined ways of benchmarking the current performance of pg
database on my system?
Are there any guide lines on what type of data or table design should be
used?
One option of getting the benchmarks could be to use the run time
statistics logging with and without the patch.
And I have to test the performance of the 4 basic queries INSERT,
UPDATE, DELETE and SELECT, right?

Regards
Abbas




> 
>   thanks Zdenek
> 
> 
> > 
> > Regards
> > Abbas
> > 
> > 
> > 
> > 
> > On Fri, 2008-09-19 at 14:28 +0200, Zdenek Kotala wrote:
> >> thanks
> >>
> >> Abbas napsal(a):
> >>> Even with that a hunk failed for bufpage.c, but I applied that part
> >>> manually to move on.
> >>> Regards
> >>> Abbas
> >>>
> >>> On Thu, 2008-09-18 at 12:17 +0200, Zdenek Kotala wrote:
> >>>> Abbas napsal(a):
> >>>>> Hi,
> >>>>> I downloaded latest postgresql source code from
> >>>>>  git clone git://git.postgresql.org/git/postgresql.git
> >>>>> and tried to apply the patch 
> >>>>>  http://archives.postgresql.org/pgsql-hackers/2008-09/gza1fGXLvf3L.gz
> >>>>>
> >>>>> It does not apply cleanly, see the failures in attached file.
> >>>> It clash with hash index patch which was committed four days ago. Try to 
> >>>> use 
> >>>> little bit older revision from git (without hash index modification).
> >>>>
> >>>>  Zdenek
> >>>>
> >>>>
> >>>>
> >>
> > 
> 
> 


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

[HACKERS] Latches API on windows

[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 <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] Latches API on windows

[Abbas Butt]

Thanks for the suggestion.


On Mon, Oct 9, 2017 at 6:56 PM, Tom Lane  wrote:

> Craig Ringer  writes:
> > On 9 October 2017 at 21:26, Abbas Butt 
> 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] Using a latch between a background worker process and a 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 <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] Using a latch between a background worker process and a 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  wrote:

> On 2 November 2016 at 02:10, Robert Haas  wrote:
> > On Tue, Nov 1, 2016 at 12:35 PM, Abbas Butt 
> 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] How to run PG TAP tests on windows?

[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 <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] How to run PG TAP tests on windows?

[Abbas Butt]

On Tue, Aug 1, 2017 at 7:35 PM, Michael Paquier 
wrote:

> On Tue, Aug 1, 2017 at 10:24 AM, Abbas Butt 
> 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] PG_TRY & PG_CATCH in FDW development

[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 <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] PG_TRY & PG_CATCH in FDW development

[Abbas Butt]

Thanks for the reply.

On Tue, Apr 25, 2017 at 7:45 PM, Tom Lane  wrote:

> Abbas Butt  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] A problem with dump/restore of views containing whole row references

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

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

[Abbas Butt]

On Fri, Apr 27, 2012 at 6:25 PM, Andrew Dunstan  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

[Abbas Butt]

On Fri, Apr 27, 2012 at 11:21 PM, Tom Lane  wrote:

> Andrew Dunstan  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] varattno remapping

[Abbas Butt]

On Tue, Dec 24, 2013 at 11:47 AM, Craig Ringer wrote:

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

[Abbas Butt]

_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.223 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: 13.963 ms  plan:
Query Text: select test_pg_fdw();
Result  (cost=0.00..0.26 rows=1 width=0)
  Output: test_pg_fdw()

Note that the remote query does not contain the WHERE clause after the 5th
invocation.

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?

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

[Abbas Butt]

On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita 
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>

Re: [HACKERS] An issue in remote query optimization

[Abbas Butt]

On Tue, Jan 31, 2017 at 3:15 AM, Etsuro Fujita 
wrote:

> On 2017/01/31 19:53, Abbas Butt wrote:
>
>> On Tue, Jan 31, 2017 at 2:25 AM, Etsuro Fujita
>> 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

[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 
> 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
> >>> 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] 9.5 open items

[Abbas Butt]

GC=Garbage Collector

On Mon, May 18, 2015 at 9:24 AM, Michael Paquier 
wrote:

> On Mon, May 18, 2015 at 12:35 PM, Josh Berkus  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] schema_to_xmlschema() seems a bit less than finished

[Sibte Abbas]

On 7/12/07, Tom Lane <[EMAIL PROTECTED]> wrote:

In the regression database:

regression=# select schema_to_xmlschema('public',false,false,'foo');
ERROR:  cache lookup failed for type 0

I have no idea what this function should produce, but surely not that?

regards, tom lane



The following test case reproduces the problem:

create domain domtxt as text;
create table dom_tab(col1 domtxt);
drop domain domtxt cascade;
select schema_to_xmlschema('public',false,false,'foo');

Since domtxt domain is being dropped dom_tab should not contain any columns now.

However It appears that the tuple descriptor which
map_sql_typecoll_to_xmlschema_types() (in xml.c) gets for dom_tab
still shows one column (tupdesc->natts = 1). Subsequently when
SPI_gettypeid() is invoked it returns 0, which gets inserted in the
uniquetypes list.

Since the following foreach statement simply traverses the uniquetypes
list and invokes getBaseType() on its oid values, therefore 0 gets
passed to getBaseType()  which results in the "cache lookup failed
..." error.

Considering the above fact, perhaps the actual problem is that when a
column gets removed from a table as a result of drop   cascade, the tuple descriptor (more specifically
rel->rd_att field) for that relation is not updated properly?

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [HACKERS] [GENERAL] 8.2.4 signal 11 with large transaction

[Sibte Abbas]

On 7/20/07, Tom Lane <[EMAIL PROTECTED]> wrote:


I guess what we need to do is hack the emergency-recovery path for
error-during-error-processing such that it will prevent trying to print
a very long debug_query_string.  Maybe we should just not try to print
the command at all in this case, or maybe there's some intermediate
possibility like only printing the first 1K or so.  Thoughts?

regards, tom lane



I think printing the first 1K would make more sense.

If I understand you correctly, the code path which you are referring
to is the send_message_to_server_log() function in elog.c?

thanks,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [GENERAL] 8.2.4 signal 11 with large transaction

[Sibte Abbas]

On 7/23/07, Tom Lane <[EMAIL PROTECTED]> wrote:


No, the place that has to change is where errstart() detects that we're
recursing.  We could possibly have it first try to make a shorter string
and only give up entirely if recursion happens again, but given that
this is such a corner case I don't think it's worth the complexity and
risk of further bugs.  I've made it just drop the statement at the same
time that it decides to give up on printing other context (which can
also be a source of out-of-memory problems btw).
http://archives.postgresql.org/pgsql-committers/2007-07/msg00215.php



Makes sense.

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[HACKERS] TODO item: add \# which lists line numbers, and allows command execution

[Sibte Abbas]

Hi all,

Realizing that the mentioned TODO item discussed at
http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php<http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php>can
be useful for myself and others as well, I would like to go ahead and
implement it.

Synopsis:
==

Sticking to the syntax and semantics which Joshua already mentioned in the
thread:

\#: displays the command history. Like \s but prefixes the lines with line
numbers

\# : executes the command(if any) executed at the line specified by
line_no


Display the history contents:
=

Well the answer to this pretty much lies in the \s (without any filename)
implementation. However \s simply writes the contents of the history to the
TTY while \# would prefix all the history lines with their respective line
numbers.

Because of this difference, we'll have to employ a more flexible approach
rather than simply using the write_history() function.

A solution to this is to use history_list() function to get a list of
HIST_ENTRY structures and simply display them as per our needs i.e. to
prefix each line with incrementing numbers in our case.

Execute a line based on line number:
==

For this we can simply use the history_get(int offset) function of the gnu
history api to return a HIST_ENTRY structure corresponding to the given
offset.

The returned HIST_ENTRY can then be used to access the command that was
executed at that specific line number.

Comments are welcomed.

regards,
-- 
Sibte Abbas

Re: [HACKERS] TODO item: add \# which lists line numbers, and allows command execution

[Sibte Abbas]

On 9/8/07, Sibte Abbas <[EMAIL PROTECTED]> wrote:
>
>
> Hi all,
>
> Realizing that the mentioned TODO item discussed at 
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php
> <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php> can be
> useful for myself and others as well, I would like to go ahead and implement
> it.
>
> Synopsis:
> ==
>
> Sticking to the syntax and semantics which Joshua already mentioned in the
> thread:
>
> \#: displays the command history. Like \s but prefixes the lines with line
> numbers
>
> \# : executes the command(if any) executed at the line specified
> by line_no
>
>
> Display the history contents:
> =
>
> Well the answer to this pretty much lies in the \s (without any filename)
> implementation. However \s simply writes the contents of the history to the
> TTY while \# would prefix all the history lines with their respective line
> numbers.
>
> Because of this difference, we'll have to employ a more flexible approach
> rather than simply using the write_history() function.
>
> A solution to this is to use history_list() function to get a list of
> HIST_ENTRY structures and simply display them as per our needs i.e. to
> prefix each line with incrementing numbers in our case.
>
> Execute a line based on line number:
> ==
>
> For this we can simply use the history_get(int offset) function of the gnu
> history api to return a HIST_ENTRY structure corresponding to the given
> offset.
>
> The returned HIST_ENTRY can then be used to access the command that was
> executed at that specific line number.
>
> Comments are welcomed.
>
> regards,
> --
> Sibte Abbas
>


Patch submitted to pgsql-patches.

regards,
-- 
Sibte Abbas

Re: [HACKERS] Raw device I/O for large objects

[Sibte Abbas]

On 9/17/07, Georgi Chulkov <[EMAIL PROTECTED]> wrote:
>
> Could someone please point me to the right places to look at, and how/where to
> get started? Would such a development be useful at all? Is anyone working on
> anything related?
>
> Any feedback / information would be highly appreciated!
>

http://www.postgresql.org/docs/techdocs
http://www.postgresql.org/docs/faq/

The postgresql documentation:
http://www.postgresql.org/docs/8.2/interactive/index.html

Also, If you have the source, the src/tools/backend directory has some
useful material for starters.

regards,
--
Sibte Abbas

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] NULL and plpgsql rows

[Sibte Abbas]

On 10/2/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > However, the test right above that means that we'll fail if the user
> > tries something like "row_variable := NULL;":
>
> The patch you seem to have in mind would allow
> row_variable := int_variable;
> to succeed if the int_variable chanced to contain NULL, which is surely
> not very desirable.
>
> The real issue here is that the bare NULL has type UNKNOWN and we're not
> making any effort to cast it.  I'm not sure whether it'd work to simply
> apply exec_cast_value --- that looks like it's only meant to handle
> scalars, where in general you'd need something close to
> ExecEvalConvertRowtype().
>
> > Of course, setting a row variable to null is a lot more useful if we can
> > actually test for it after the fact, and I'm not really sure how to make
> > that happen.
>
> Doesn't IS NULL work (as of CVS HEAD)?
>

Is there a specific reason why we keep the tuple descriptor of an
unassigned record type to NULL?

Surely we don't know what tuple descriptor it will actually contain,
however, maybe we can have "special" tuple descriptors for un-assigned
record types.

For example, if for NULL/unassigned record type we create a tuple
descriptor of "VOID" type, and then initialize its corresponding (one
column) row to null, we 'll have the  IS NULL check working on
unassigned or NULL record types as well.

regards,
-- 
Sibte Abbas

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Need more reviewers!

[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] [PATCHES] psql slash# command

[Sibte Abbas]

On Sun, Mar 30, 2008 at 3:09 PM, Tom Lane <[EMAIL PROTECTED]> wrote:

>
> I looked this over and realized that it has little to do with the
> functionality that was so painfully hashed out in the original
> discussion thread here:
>
> http://archives.postgresql.org/pgsql-hackers/2006-12/msg00207.php
>
> As I understood it, the consensus was:
>
> 1. Invent a switch (probably a variable instead of a dedicated \-command)
> that determines whether \s includes command numbers in its output.
>
> 2. Add "\# n" to re-execute command number n.
>
> You've twisted this around into
>
> >> \#: displays the command history. Like \s but prefixes the lines with
> line
> >> numbers
> >>
> >> \# : executes the command(if any) executed at the line
> specified by
> >> line_no
>

This patch implements the specification described here:

http://archives.postgresql.org/pgsql-hackers/2006-12/msg00255.php


>
>
> This is a serious regression in functionality from what was agreed to,
> because there is no possibility of shoehorning the equivalent of "\s file"
> into it --- you've already decided that any argument is a line number.


It made sense to assume anything following a \# to be a number, since "#"
here denotes a number. However in order to prevent from bad input, there is
a check in the get_hist_entry() function.


> > The attached patch adds the following new functionality:
> > \#e : Will open the command at the given lineno in an editor.
> > \#e with no lineno will behave exactly like \e.
>
> None of that was anywhere in the original discussion; and what pray
> tell is the use of the second variant?


The above mentioned link contains definitions for both of these. Also the
second variant here is just for completeness sake.


> I wonder whether it wouldn't be safer and more convenient if we defined
> '\# n' as pulling command n into the edit buffer, rather than
> immediately executing it.  Actual execution is only a  away,
> but this definition would allow you to edit the command a bit more
> before you execute it --- including \e to use an editor.  It also
> closes the loop in terms of providing some confidence that you typed
> the number you should have typed.
>

This makes more sense and also appears to be much safer. I will start
modifying the patch as per this approach now.

regards,
-- Sibte Abbas

Re: [HACKERS] psql slash# command

[Sibte Abbas]

On Sat, Apr 5, 2008 at 12:36 AM, Tom Lane <[EMAIL PROTECTED]> wrote:

>
> Actually, I suggested that to the patch author and he accepted it as a
> good idea:
> http://archives.postgresql.org/pgsql-hackers/2008-04/msg00245.php
>
> so let's see what he comes up with...
>
>
New patch submitted:

http://archives.postgresql.org/pgsql-patches/2008-04/msg00240.php

regards,
-- Sibte Abbas

Re: [HACKERS] unchecked malloc

[Sibtay Abbas]

This dicussion reminds me of a possible memory leak in plpgsql's code. In case you are interested in it;



in pl_comp.c, plpgsql_build_variable takes a pointer to a PLpgSQL_type
structure, which is always a malloc'ed instance(since we always use
plpgsql_build_datatype function). The switch statement in plpgsql_build_variable function elicits that its reference is only
kept in case the type structure represents a PLPGSQL_TTYPE_SCALAR,
otherwise it is not kept and needed in case its either
PLPGSQL_TTYPE_ROW or PLPGSQL_TTYPE_REC.

So is it intensional or a memory leak?



Thank you
On 9/27/05, Tom Lane <[EMAIL PROTECTED]> wrote:
"Qingqing Zhou" <[EMAIL PROTECTED]> writes:> "Tom Lane" <[EMAIL PROTECTED]> wrote>> No, because you're thinking in terms of the backend environment, and
>> generally in the backend the answer to "when to use malloc directly">> is "never".> Well, except before MemoryContext mechanism is set up? For example, the> functions(
e.g., GUC, vfd) used during bootstrap.I think you need to take another look at the startup sequences.  Thosemodules are not run before MemoryContextInit.  In any case, the oddsof running out of memory before we get to MemoryContextInit are so small
that I don't have a problem with crashing if it happens.regards,
tom lane---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriate   subscribe-nomail command to 
[EMAIL PROTECTED] so that your   message can get through to the mailing list cleanly

[HACKERS] Oid?

[Sibtay Abbas]

hi everyone 

i see an attribute 'oid_value' with 'Oid' type in
the ListCell union.

can anyone tell me what does it represents?

Thank you



__ 
Do you Yahoo!? 
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
http://info.mail.yahoo.com/mail_250

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] main entry point for queries?

[Sibtay Abbas]

hello

i am trying to understand the source code of
postgresql. 
Whenever a user enters a query, whether from the
Socket end or the interactive back end, which function
is always invoked that would start manipulation of
that query...

i ve noticed that the primitive parsing is done by the
raw_parser() function which is called by
pg_parse_query() is pg_parse_query() the main entry
point for all queries?

thank you




__ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] how can i add my own procedural language?

[Sibtay Abbas]

hi

is "Procedural language handler function" the
interface for adding your own procedural languages to
postgres?

I ve read the documentation but i am not able to
understand where do we deal with stuff like parse
trees, query trees, plan trees etc.

Ofcourse any procedural language should pass through
all the steps ie parsing, planning/optimization and
execution.

so where do we define these steps? and what interface
is provided by postgresql?

thank you



__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[HACKERS] spi and other languages

[Sibtay Abbas]

i guess the answer to my previous question was spi...i
ve got another question, can we call pgsql or plpgsql
functions using spi?

like can we do something like

SPI_execute("CREATE FUNCTION blah() RETURNS Integer
."






__ 
Do you Yahoo!? 
All your favorites on one personal page – Try My Yahoo!
http://my.yahoo.com 

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] spi and other languages

[Sibtay Abbas]

i am still in my R&D phase so i ve not yet tried it.
actually i chose the wrong example. 

We can call SQL statements like SPI_Execute("SELECT *
FROM sometable") from the spi interface. My question
is that can we enter other procedural languages as
well, like pgplsql statements.



__ 
Do you Yahoo!? 
Yahoo! Mail - 250MB free storage. Do more. Manage less. 
http://info.mail.yahoo.com/mail_250

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[HACKERS] adding procedural languages

[Sibtay Abbas]

hi everyone

ok i ve tried to add my own procedural language using
the call handler interface.

I am doining nothing in the handler function except
tyring to print a mess through errmsg function 
(plz correct me if i am wrong here, since i cant see
the message which i am printing)

Now my question is that how can i explicitly specify
that i want my pl language to be used.

I think i have added my language successfully by
following the procedure mentioned in the
documentation. 

So now how do i inform the database engine to use my
language for a specific query

thank you



__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] old-style handler problem

[Sibtay Abbas]

hi everyone

i ve added my own pl language in postgresql. i ve
followed the process mentioned in the documentation.
for the handler function i ve followed the following
template

PG_FUNCTION_INFO_V1(myhandler);

Datum
plsample_call_handler(PG_FUNCTION_ARGS)
{
Datum rv;

...my code


return rv;
}

then i created a function using my language...but when
i invoked it later on with SELECT funcName() i
received the following error

ERROR:  language 25450 has old-style handler

why am i getting this message and how can it be
rectified...thank you



__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] old-style handler error

[Sibtay Abbas]

i have added my own pocedural language in postgresql.
by using the Create function command i ve also written
a procedure in that language.

now when invoke that function with 
SELECT (func_name) i receive the following error
message

ERROR:  language 17254 has old-style handler.




__ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[HACKERS] cant write to file within call handler interface

[Sibtay Abbas]

hello

i am not able to write to file until the pl call
handler interface. this is the template which i am
following

PG_FUNCTION_INFO_V1(my_call_handler);

Datum
my_call_handler(PG_FUNCTION_ARGS)
{
...my code...

int fd = open("filename",O_WRONLY);
write(fd,buffer,strlen(buffer) + 1);

//fsync(fd)...i tried this as well but   
 did'nt work

}


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] yyin's value of postgresql parser

[Sibtay Abbas]

hello

what is the value of yyin variable for postgresql
parser. 

It might be the default("stdout") when postgresql is
in interactive backend mode...but what happens when
clients from different workstations sends their
queries?




__ 
Do you Yahoo!? 
The all-new My Yahoo! - What will yours do?
http://my.yahoo.com 

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[HACKERS] cant execute yyparse() within postgresql

[Sibtay Abbas]

hello

i am not able to execute the yyparse() function
generated by flex from within the pl call handler
function.

does any one knows the reason for this failure? and
how can it be rectified

thank you




__ 
Do you Yahoo!? 
Send a seasonal email greeting and help others. Do good. 
http://celebrity.mail.yahoo.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] cant execute yyparse() within postgresql

[Sibtay Abbas]

i am sorry if i hurt your ego, by asking this
question. And i mentioned flex mistakenly...actually
by flex i meant yacc (i am using lex & yacc). yyparse
is generated by yacc..which i want to invoke.

"
>Second, you can't just ask
> random, highly specific 
> questions out of the blue and expect someone to
>have
> an answer for you.  
"
i never happen to read this rule when i signed up to
this mailing list...so if you ve made up a rule by
urself for this mailing list just do us a favor by
officially announcing it

i think anyone who has knowledge of adding new
languages to postgresql will understand what pl call
handler means.

i ve been getting alot of help from this mailing list
and i thank all the ppl who helped me.

Peter's attitude was rude and i complain against it
and i ask the mailing list maintainers to follow up on
this.

Peter if you dont understand someone's question the
nice way is to ask him the details instead of trying
to insult him.

thank you

--- Peter Eisentraut <[EMAIL PROTECTED]> wrote:

> Sibtay Abbas wrote:
> > i am not able to execute the yyparse() function
> > generated by flex from within the pl call handler
> > function.
> 
> First of all, flex does not generate a yyparse()
> function.  At most it 
> generates yylex().  Second, you can't just ask
> random, highly specific 
> questions out of the blue and expect someone to have
> an answer for you.  
> Why are you calling yyparser(), what happens when
> you do, what PL 
> handlers are you talking about, etc., would be some
> of the required 
> context information.
> 
> -- 
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
> 




__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] cant execute yyparse() within postgresql

[Sibtay Abbas]

Datum mylanguage_handler(PG_FUNCTION_ARGS){

if (CALLED_AS_TRIGGER(fcinfo))
/*do nothing

 else{  
char   *proc_source;
Datum   prosrcdatum;
boolisnull;

//get the oid of the function
Oid funcOid = fcinfo->flinfo->fn_oid;

HeapTuple procTup =
SearchSysCache(PROCOID,ObjectIdGetDatum(funcOid),0, 0,
0);

//get the attribute that holds the function's source
prosrcdatum = SysCacheGetAttr(PROCOID,
procTup,Anum_pg_proc_prosrc, &isnull);

//convert prosrcdatum to C style string
proc_source =
DatumGetCString(DirectFunctionCall1(textout,
prosrcdatum));

if (isnull)
elog(ERROR, "null prosrc");
else
{
  elog(INFO,"\n Invoking parser \n");

  /*the problem area*/  
  yyparse();
  elog(INFO,"\n parser invoked \n");
}
}//end of function


i receive the following error
"server closed the connection unexpectedly
   This probably means the server terminated
abnormally before or while processing the request.
The connection to the server was lost. Attempting
reset: Failed."


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] CreateFunctionStmt struct

[Sibtay Abbas]

hello,

typedef struct CreateFunctionStmt
{
NodeTag type;
boolreplace;
List   *funcname;   
List   *parameters; 
TypeName   *returnType; 
List   *options;
List   *withClause; 
} CreateFunctionStmt;

this the the CreateFunctionStmt struct found in the
postgresql implementation.

Can anyone tell me which field here is used to
indicated the language of the function, in which it is
written.

like when we create a function we mention $$language
bla in the end.

so is there any field in this structure which
represents its language?



__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] exception handling in plpgsql

[Sibtay Abbas]

hello

I am using the following sytex to handle exceptions in
plpgsql (I am using postgres 8 rc1)

some code 

EXCEPTION
WHEN NO_DATA THEN  
RAISE NOTICE 'NO DATA';
WHEN OTHERS THEN
RAISE NOTICE 'An exception occurred';
RETURN emp_rec;

and i receive the following error 
ERROR:  unrecognized exception condition "no_data"

How can i rectify this error?



__ 
Do you Yahoo!? 
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] storage of compiled functions

[Sibtay Abbas]

Hello everyone

i have been studying postgres's function handling philosophy lately. I
got stuck at a point where i could'nt get the answer myself

All the information related to functions are stored in pg_proc system
catalog. But when a function is compiled (probably the data structure
that contains a compiled function is pgfunction) where is it stored
for persistence.

I checked plpgsql's code and noticed there that the function after
compilation was saved in
fcinfo->flinfo->fn_extra. But how do you get persistence (of compiled
functions) by storing its value in fcinfo->flinfo->fn_extra. Or in
other words how is the FmgrInfo information stored to persistence in
postgresql

Thankz

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] passing parameters by reference

[Sibtay Abbas]

hello all

as i have mentioned this before, as part of my academic project
i am required to make modifications in postgresql.

One of my tasks includes allowing plpgsql functions to pass parameters by
reference. For this purpose I have been digging in the code for plpgsql 
and postgresql for 2 days now but i am not able to find any thing tangible.

The first thing which i would love to clarify and would be very gratefull if
anyone can help me in this, is that to achieve this am i required to
make modifications
in plpgsql or postgresql codebase itself?

To achieve this my first approach was to make modifications in
function call manager where we fill the
FmgrInfo and FunctionCallInfo structures for a function. There we can
get the references of the Datums actually
passed and assign it to fcinfo.arg[0]

(i am refering to the "Datum DirectFunctionCall1(PGFunction func,
Datum arg1)" function in fmgr.c and i am quite
sure that this approach is wrong)

If anyone can point out the area (in terms of functions or .c files)
where i should start
doing my research in this context it would do wonders to me.

Thank you


P.S : I have discovered that you cannot assign values to function
parameters inside the function,
but i have managed to get pass this restriction by setting the
"isconst" field of function parameters
to false, in the "do_compile" function of plpgsql. This way users will
have write access
to function parameters.

---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] getting oid of function

[Sibtay Abbas]

Hello all

Is it possible to get the oid of a function on the basis of its name?.

The scenario which i am currently facing is that i have the function name, now
i want search the pg_proc system catalog on the basis of the function
name and retrieve its Oid.

Another confusion which i am facing is that, I am not sure whether Oid
of a function is entered in pg_proc system catalog or not. Because i
am not able to identify any relevant field.

thank you

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] getting oid of function

[Sibtay Abbas]

thank you for the detailed reply
But what i wanted to know is that how can we actually get a function's
oid from its
name from within postgresql code itself

Actually i ve introduced a new statement in plpgsql, that supports calling
a plpgsql function from within a plpgsql function for example

CALL function_name( );

Here i only have the function name, i need to fetch the Oid of the
function so that
i can construct FmgrInfo and FunctionCallInfoData structures, so that
i can compile (if required) and execute that function.

There is a function like SearchSysCache(PROCOID,.
that would retrieve the pg_proc Tuple on the basis of function Oid
passed to it, so can use this function to return a pg_proc Tuple on
the basis of a function name?
Or is there any alternate function avaible that can achieve this

Thank you

regards
Sibtay

In case i am taking a wrong approach I would be thankful if anyone can
correct me.

On Mon, 14 Feb 2005 01:14:07 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Mon, Feb 14, 2005 at 12:47:44PM +0500, Sibtay Abbas wrote:
> >
> > Is it possible to get the oid of a function on the basis of its name?.
> 
> One way is to cast the function name to regproc (or, with arguments,
> to regprocedure) and then to oid:
> 
> SELECT 'atan'::regproc::oid;
> SELECT 'length(text)'::regprocedure::oid;
> 
> See "Object Identifier Types" in the documentation for more info:
> 
> http://www.postgresql.org/docs/8.0/static/datatype-oid.html
> 
> > The scenario which i am currently facing is that i have the function name, 
> > now
> > i want search the pg_proc system catalog on the basis of the function
> > name and retrieve its Oid.
> 
> SELECT oid FROM pg_proc WHERE proname = 'funcname';
> 
> A function can have multiple records in pg_proc if it can take
> different types and/or numbers of arguments, so you might have to
> allow for that.
> 
> > Another confusion which i am facing is that, I am not sure whether Oid
> > of a function is entered in pg_proc system catalog or not. Because i
> > am not able to identify any relevant field.
> 
> oid is a system column; tools that describe tables usually don't
> show system columns.  You can query pg_attribute to see all of a
> table's columns.
> 
> http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html
> http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] getting oid of function

[Sibtay Abbas]

>How would this differ from PERFORM?

I think perform goes through the SQL by using SPI to execute the function,
where as this statement will invoke a plpgsql function without going
through the
sql ( :-) ..in case i manage to add this statement )

thankz alot for your replies

regards
Sibtay


On Tue, 15 Feb 2005 14:55:38 +1100, Neil Conway <[EMAIL PROTECTED]> wrote:
> On Mon, 2005-02-14 at 17:02 +0500, Sibtay Abbas wrote:
> > thank you for the detailed reply
> > But what i wanted to know is that how can we actually get a function's
> > oid from its
> > name from within postgresql code itself
> 
> You'll want to query the syscache. Note that due to function
> overloading, there may be multiple functions with the same name, so
> you'll need to figure out which one ought to be invoked by using the
> number and types of the parameters. See FuncnameGetCandidates() in
> namespace.c for an example.
> 
> -Neil
> 
>

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster