Re: [HACKERS] Practical impediment to supporting multiple SSL libraries

[Hiroshi Inoue]

Martijn van Oosterhout wrote:

>On Fri, Apr 14, 2006 at 04:53:53PM +0200, Martijn van Oosterhout wrote:
>  
>
>>Sounds really good.
>>
>>
>
>
>There's a message on the pgsql-odbc mailing list[1] with some reasons
>for not using libpq:
>
>1. The driver sets some session default parameters(DateStyle,
>   client_encoding etc) using start-up message.
>
>As far as I can see it only does this when the environment variables
>are set. Which IMHO is the correct behaviour. 
>

IMHO if libpq is to be a generic library it should first provide exactly
what it can do using the protocol. *Environment varibales* are not
appropriate for per application/datasource settings at all.

>3. Quote: I don't know what libraries the libpq would need in the
>future but it's quite unpleasant for me if the psqlodbc driver can't be
>loaded with the lack of needeless librairies.
>
>It's a reason, just not a good one IMHO. If the user has installed
>libpq with a number of libraries, then that's what the user wants. I'm
>not sure why psqlODBC is worried about that.
>  
>

It's very important to clarify for what the libraries are needed and my
basic policy
is to provide appropriate bindings(linkage) between the libraries for
the current
dependency relation. As for SSL mode it is only a mere extra for the current
enhanced driver. My main purpose was to finish up my unfinished work
before 7.4
using V3 protocol, holdable cursors etc. The current driver under Windows is
available without the existence of libpq.

regards,
Hiroshi Inoue

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Help about physical page layout in PostgreSQL:

[Tom Lane]

"yy h" <[EMAIL PROTECTED]> writes:
> I was trying to modify the physical page layout in PostgreSQL.

Uh, why?

> I understand the source code for physical page organization is located
> at bufpage.c but I wonder what is the external interface for this
> physical page organization.  Interface like InsertRecToPage,
> DeleteRecFromPage, GetAttr, GetRec, etc?

It's not as well modularized as all that.  If you want to do this, it'll
cost you some significant pain, so you might first want to think hard
about what you're really trying to accomplish.

regards, tom lane

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

[HACKERS] Help about physical page layout in PostgreSQL:

[yy h]

Hi,
 
I was trying to modify the physical page layout in PostgreSQL. I understand the source code for physical page organization is located at bufpage.c but I wonder what is the external interface for this physical page organization. Interface like InsertRecToPage, DeleteRecFromPage, GetAttr, GetRec, etc? 

 
My question are 1.where are the source files for these interfaces to physical page?
2. Are there more detailed technical documentation for this kind of stuff? The documentation at the website is more llike a external description of the whole system. Is there any technical documentation for the code?

 
Thank you for your time, any help is appreciated!
Best,
Yeye

Re: [HACKERS] Google SoC--Idea Request

[Robert Treat]

On Saturday 15 April 2006 19:25, Jonah H. Harris wrote:
> On 4/15/06, Neil Conway <[EMAIL PROTECTED]> wrote:
> > Doing the latter is a precondition for implementing the former in a
> > reasonable way, I believe.
> >
> >
> > BTW, these two web log entries summarizing Mono and Mozilla's
> > experiences with SoC might make interesting reading:
>
> Thanks for the reading material.  I don't think our project is exactly
> the same, but it's good information to keep in mind.
>

Agreed. I sent some ideas to Josh, was thinking he might be posting a list 
soon. I kept it aimed at a few ideas I have had/seen that need an initial 
push to get going but beyond that could be (and likely would be) community 
maintained.  Example?  Extendning the build farm code to test external pl 
langs or database drivers or patches other modules.  We've talked about it, 
and if someone had the time to make the push, I believe this would be 
community maintained going forward. 

> > Given the above, I would be wary of such projects bit-rotting. If the
> > upstream project hasn't bothered to add PostgreSQL support, there might
> > be a good reason why: writing truly database-agnostic applications is
> > not always easy (or even desirable).
>
> This isn't always the case.  In a lot of cases, the developers just
> wanted to take the easy route and used MySQL... they have a lot of
> people asking for PostgreSQL support but they don't have the expertise
> to add it themselves.
>

I think more importantly is that the time needed to do an initial port is 
often much greater than it is to maintain a port.

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Is full_page_writes=off safe in conjunction with

[Bruce Momjian]

Hannu Krosing wrote:
> ?hel kenal p?eval, L, 2006-04-15 kell 11:49, kirjutas Tom Lane:
> > Hannu Krosing <[EMAIL PROTECTED]> writes:
> > > If I'm desperate enough to get the 2x reduction of WAL writes, I may
> > > even write my own backup solution.
> > 
> > Given Florian's concern, sounds like you might have to write your own
> > kernel too.  In which case, generating a variant build of Postgres
> > that allows full_page_writes to be disabled is certainly not beyond
> > your powers.  But for the ordinary mortal DBA, I think this combination
> > is just too unsafe to even consider.
> 
> I guess that writing our own pg_tar, which cooperates with postgres
> backends to get full pages, is still in the realm of possible things,
> even on kernels which dont guarantee atomic visibility of write() calls.
> 
> But until such is included in the distribution it is a good idea indeed
> to disable full_page_writes=off when doing PITR.

The cost/benefit of that seems very discouraging.  Most backup
applications allow for a block size to be specified, so it isn't
unreasonable to assume that people who really want PITR and
full_page_writes can easily set the block size to 8k.  However, I don't
think we are going to allow that to be configured --- you would have to
hack up our backend code to allow the combination.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Google SoC--Idea Request

[Jonah H. Harris]

On 4/15/06, Neil Conway <[EMAIL PROTECTED]> wrote:
> Doing the latter is a precondition for implementing the former in a
> reasonable way, I believe.


> BTW, these two web log entries summarizing Mono and Mozilla's
> experiences with SoC might make interesting reading:

Thanks for the reading material.  I don't think our project is exactly
the same, but it's good information to keep in mind.

> Given the above, I would be wary of such projects bit-rotting. If the
> upstream project hasn't bothered to add PostgreSQL support, there might
> be a good reason why: writing truly database-agnostic applications is
> not always easy (or even desirable).

This isn't always the case.  In a lot of cases, the developers just
wanted to take the easy route and used MySQL... they have a lot of
people asking for PostgreSQL support but they don't have the expertise
to add it themselves.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with

[Hannu Krosing]

Ühel kenal päeval, L, 2006-04-15 kell 11:49, kirjutas Tom Lane:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > If I'm desperate enough to get the 2x reduction of WAL writes, I may
> > even write my own backup solution.
> 
> Given Florian's concern, sounds like you might have to write your own
> kernel too.  In which case, generating a variant build of Postgres
> that allows full_page_writes to be disabled is certainly not beyond
> your powers.  But for the ordinary mortal DBA, I think this combination
> is just too unsafe to even consider.

I guess that writing our own pg_tar, which cooperates with postgres
backends to get full pages, is still in the realm of possible things,
even on kernels which dont guarantee atomic visibility of write() calls.

But until such is included in the distribution it is a good idea indeed
to disable full_page_writes=off when doing PITR.

--
Hannu



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

Re: [HACKERS] Google SoC--Idea Request

[Neil Conway]

On Sat, 2006-04-15 at 21:24 +0100, Dave Page wrote:
> one to allow a message to be sent with the notify, and one to move
> from a table based design to shared mem/disk.

Doing the latter is a precondition for implementing the former in a
reasonable way, I believe.

BTW, these two web log entries summarizing Mono and Mozilla's
experiences with SoC might make interesting reading:

http://weblogs.mozillazine.org/gerv/archives/2006/03/summer_of_code_six_months_on.html
http://tirania.org/blog/archive/2006/Apr-13.html

> we should also look at project ideas for porting some of the most used
> third-party OSS software to PostgreSQL too (portals, CMS systems,
> accounting systems, etc.).

Given the above, I would be wary of such projects bit-rotting. If the
upstream project hasn't bothered to add PostgreSQL support, there might
be a good reason why: writing truly database-agnostic applications is
not always easy (or even desirable).

-Neil



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Google SoC--Idea Request

[Dave Page]

-Original Message-
From: "Jonah H. Harris"<[EMAIL PROTECTED]>
Sent: 15/04/06 20:06:27
To: "Pgsql Hackers"
Subject: [HACKERS] Google SoC--Idea Request


> As such, we need to quickly put together a list of oh, 15-20 midlevel
> project ideas.  

There's a couple of listen/notify todos iirc that would be nice to get done - 
one to allow a message to be sent with the notify, and one to move from a table 
based design to shared mem/disk.

Regards, Dave

-Unmodified Original Message-
Hey everyone,

I know we started a discussion a month or so ago regarding ideas for
SoC projects.  However, after reading through the thread, I didn't see
us nail down any actual items.

As such, we need to quickly put together a list of oh, 15-20 midlevel
project ideas.  I'm sure we can pull some off the TODO list, but we
should also look at project ideas for porting some of the most used
third-party OSS software to PostgreSQL too (portals, CMS systems,
accounting systems, etc.).

All ideas welcome!

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

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

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

   http://archives.postgresql.org

Re: [HACKERS] two-argument aggregates and SQL 2003

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Sat, Apr 15, 2006 at 12:51:24AM -0400, Tom Lane wrote:
>> I had an epiphany that might serve as illustration of the above.  We
>> have traditionally thought of COUNT(*) as an "aggregate over any base
>> type".  But wouldn't it be cleaner to think of it as an aggregate over
>> zero inputs?

> Speaking strictly from a users PoV, I'm not sure this is a great idea,
> since it encourages non-standard code (AFAIK no one else accepts
> 'count()'), and getting rid of support for count(*) seems like a
> non-starter, so I'm not sure there's any benefit.

Well, if you want, we can still insist that actual invocations of a
zero-argument aggregate be spelled with (*).  But from a conceptual and
documentation standpoint we should think of them as zero-argument,
not sort-of-one-argument.

regards, tom lane

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

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

Re: [HACKERS] two-argument aggregates and SQL 2003

[Jim C. Nasby]

On Sat, Apr 15, 2006 at 12:51:24AM -0400, Tom Lane wrote:
> I wrote:
> > ... Polya's Inventors' Paradox states that
> > "the more general problem may be easier to solve", and I've found that
> > usually holds up in program design too.
> 
> While fooling around with the grammar patch that I showed earlier today,
> I had an epiphany that might serve as illustration of the above.  We
> have traditionally thought of COUNT(*) as an "aggregate over any base
> type".  But wouldn't it be cleaner to think of it as an aggregate over
> zero inputs?  That would get rid of the rather artificial need to
> convert COUNT(*) to COUNT(1).  We would actually have two separate
> aggregate functions, which could most accurately be described as
>   count()
>   count(anyelement)
> where the latter is the form that has the behavior of counting the
> non-null values of the input.
> 
> While this doesn't really simplify nodeAgg.c, it wouldn't add any
> complexity either (once the code has been recast to support variable
> numbers of arguments).  And it seems to me that it clarifies the
> semantics noticeably --- in particular, there'd no longer be this weird
> special case that an aggregate over ANY should have a one-input
> transition function where everything else takes two-input.  The rule
> would be simple: an N-input aggregate uses an N-plus-one-input
> transition function.

Speaking strictly from a users PoV, I'm not sure this is a great idea,
since it encourages non-standard code (AFAIK no one else accepts
'count()'), and getting rid of support for count(*) seems like a
non-starter, so I'm not sure there's any benefit.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [HACKERS] Control File

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Perhaps an easy means would be to put a PG_VERSION file in each
> tablespace when it's created and then check all of them. Tablespaces
> arguably make it slightly easier to accidentally try and mount something
> from a different version...

I believe we already do put a PG_VERSION file in each tablespace, but
AFAIR there is no subsequent check on their contents.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Control File

[Jim C. Nasby]

On Sat, Apr 15, 2006 at 02:03:50PM -0500, Jim C. Nasby wrote:
> Perhaps an easy means would be to put a PG_VERSION file in each
> tablespace when it's created and then check all of them. Tablespaces
> arguably make it slightly easier to accidentally try and mount something
> from a different version...

Sorry, clearly there already is a PG_VERSION there. I'll go find some
caffine before sending any more email. :(
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

[HACKERS] Google SoC--Idea Request

[Jonah H. Harris]

Hey everyone,

I know we started a discussion a month or so ago regarding ideas for
SoC projects.  However, after reading through the thread, I didn't see
us nail down any actual items.

As such, we need to quickly put together a list of oh, 15-20 midlevel
project ideas.  I'm sure we can pull some off the TODO list, but we
should also look at project ideas for porting some of the most used
third-party OSS software to PostgreSQL too (portals, CMS systems,
accounting systems, etc.).

All ideas welcome!

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

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

Re: [HACKERS] Control File

[Jim C. Nasby]

On Fri, Apr 14, 2006 at 11:10:34AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > As for adding checks to startup scripts, that's a PITA because
> > those scripts will have no idea of where tablespaces might be defined,
> > so you'd have to hard-code that info in.
 
> OTOH you could argue that it'd be just as reasonable for the postmaster
> to make this check, seeing that it does go to the trouble of verifying
> the contents of $PGDATA/PG_VERSION.

Yup. Plus it seems rather silly to expect every port/package of
PostgreSQL to add code to check for functional tablespace mounts.

Perhaps an easy means would be to put a PG_VERSION file in each
tablespace when it's created and then check all of them. Tablespaces
arguably make it slightly easier to accidentally try and mount something
from a different version...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [HACKERS] Time for new releases?

[Marc G. Fournier]

On Sat, 15 Apr 2006, Devrim GUNDUZ wrote:



Hi,

It's been quite a long time since the latest dot releases. Do you think
it is time for the new releases? There are several bugfixes since then.


Any that warrant a new dot release?


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

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

[HACKERS] Time for new releases?

[Devrim GUNDUZ]

Hi,

It's been quite a long time since the latest dot releases. Do you think
it is time for the new releases? There are several bugfixes since then.

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/


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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > I am thinking we should throw an error on pg_start_backup() and
> > pg_stop_backup if full_page_writes is off.
> 
> No, we'll just change the test in xlog.c so that fullPageWrites is
> ignored if XLogArchivingActive.

We should probably throw a LOG message too.

> > Seems archive_command and
> > full_page_writes can still be used if we are not in the process of doing
> > a file system backup.
> 
> Think harder: we are only safe if the first write to a given page after
> it's mis-copied by the archiver is a full page write.  The requirement
> therefore continues after pg_stop_backup.  Unless you want to add
> infrastructure to keep track for *every page* in the DB of whether it's
> been fully written since the last backup?

Ah, yea.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?

[Tom Lane]

Hannu Krosing <[EMAIL PROTECTED]> writes:
> If I'm desperate enough to get the 2x reduction of WAL writes, I may
> even write my own backup solution.

Given Florian's concern, sounds like you might have to write your own
kernel too.  In which case, generating a variant build of Postgres
that allows full_page_writes to be disabled is certainly not beyond
your powers.  But for the ordinary mortal DBA, I think this combination
is just too unsafe to even consider.

regards, tom lane

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

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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?

[Tom Lane]

Bruce Momjian  writes:
> I am thinking we should throw an error on pg_start_backup() and
> pg_stop_backup if full_page_writes is off.

No, we'll just change the test in xlog.c so that fullPageWrites is
ignored if XLogArchivingActive.

> Seems archive_command and
> full_page_writes can still be used if we are not in the process of doing
> a file system backup.

Think harder: we are only safe if the first write to a given page after
it's mis-copied by the archiver is a full page write.  The requirement
therefore continues after pg_stop_backup.  Unless you want to add
infrastructure to keep track for *every page* in the DB of whether it's
been fully written since the last backup?

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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?

[Bruce Momjian]

Tom Lane wrote:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > ??hel kenal p??eval, R, 2006-04-14 kell 16:40, kirjutas Tom Lane:
> >> If the backup-taker reads, say, 4K at a time then it's
> >> certainly possible that it gets a later version of the second half of a
> >> page than it got of the first half.  I don't know about you, but I sure
> >> don't feel comfortable making assumptions at that level about the
> >> behavior of tar or cpio.
> >> 
> >> I fear we still have to disable full_page_writes (force it ON) if
> >> XLogArchivingActive is on.  Comments?
> 
> > Why not just tell the backup-taker to take backups using 8K pages ? 
> 
> How?  (No, I don't think tar's blocksize options control this
> necessarily --- those indicate the blocking factor on the *tape*.
> And not everyone uses tar anyway.)
> 
> Even if this would work for all popular backup programs, it seems
> far too fragile: the consequence of forgetting the switch would be
> silent data corruption, which you might not notice until the slave
> had been in live operation for some time.

Yea, it is a problem.  Even a 10k read is going to read 2k into the next
page.

I am thinking we should throw an error on pg_start_backup() and
pg_stop_backup if full_page_writes is off.  Seems archive_command and
full_page_writes can still be used if we are not in the process of doing
a file system backup.

In fact, could we have pg_start_backup() turn on full_page_writes and
have pg_stop_backup turn it off, if postgresql.conf has it off.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with

[Hannu Krosing]

Ühel kenal päeval, R, 2006-04-14 kell 17:31, kirjutas Tom Lane:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > Ühel kenal päeval, R, 2006-04-14 kell 16:40, kirjutas Tom Lane:
> >> If the backup-taker reads, say, 4K at a time then it's
> >> certainly possible that it gets a later version of the second half of a
> >> page than it got of the first half.  I don't know about you, but I sure
> >> don't feel comfortable making assumptions at that level about the
> >> behavior of tar or cpio.
> >> 
> >> I fear we still have to disable full_page_writes (force it ON) if
> >> XLogArchivingActive is on.  Comments?
> 
> > Why not just tell the backup-taker to take backups using 8K pages ? 
> 
> How? 

use find + dd, or whatever. I just dont want it to be made universally
unavailable just because some users *might* use an file/disk-level
backup solution which is incompatible.

> (No, I don't think tar's blocksize options control this
> necessarily --- those indicate the blocking factor on the *tape*.
> And not everyone uses tar anyway.)

If I'm desperate enough to get the 2x reduction of WAL writes, I may
even write my own backup solution.

> Even if this would work for all popular backup programs, it seems
> far too fragile: the consequence of forgetting the switch would be
> silent data corruption, which you might not notice until the slave
> had been in live operation for some time.

We may declare only one solution to be supported by us with
XLogArchivingActive, say a gnu tar modified to read in Nx8K blocks
( pg_tar :p ).

I guess that even if we can control what operating system does, it is
still possible to get a torn page using some SAN solution, where you can
freeze the image for backup independent of OS.


Hannu







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

Re: [HACKERS] Is full_page_writes=off safe in conjunction with PITR?

[Florian Weimer]

* Tom Lane:

> I think we had originally argued that there was no problem anyway
> because the kernel should cause the page write to appear atomic to other
> processes (since we issue it in a single write() command).

I doubt Linux makes any such guarantees.  See this recent thread on
linux-kernel: 

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

   http://archives.postgresql.org

Re: [HACKERS] libpq Describe Extension [WAS: Bytea and perl]

[Volkan YAZICI]

Hi,

[Sending this message (first) to -hackers for discussion about the
extension and followed implementation.]

On Apr 01 09:39, Volkan YAZICI wrote:
> I've prepared a patch for the Describe <-> ParameterDescription
> messaging which is available via current extended query protocol.

Here's a written from scratch patch for the above mentioned extension.
It adds PQdescribePrepared() and PQdescribePortal() functions to the
libpq. New functions work as follows:

1. Issue a PQdescribePrepared() call.
2. First PQgetResult() will return a PGresult with input parameter
   types of the prepared statement. (You can use PQftype() on this
   PGresult to extract information.)
3. Second PQgetResult() will return another PGresult which holds the
   column information for the will be returned tuples. (All PQf*()
   functions can be used on this result.)

(A PQdescribePortal() call will just skip the 2nd step in the above
list.)

Patch passes regression tests and there're two examples attached for
PQdescribePrepared() and PQdescribePortal() usage.

To mention about the followed implementation, it needed some hack on
pqParseInput3() code to make it understand if a received message is
a reponse to a Describe ('D') query or to another tuple returning
query. To summarize problem, there're two possible forms of a 'D'
response:

 1. Description of a prepared statement: t, T, Z
 2. Description of a portal: T, Z

The problem is, AFAICS, it's not possible to distinguish between a tuple
returning query (T, ..., C, Z or T, E) and a description of a portal (T,
Z). Therefore, I've created a global flag (parsing_row_desc) which is
turned on when we receive a 'T' and turned off if we receive a 'C' or
'E'. It's a kind of ugly method but the only solution I could come up
with.


Regards.
Index: src/backend/tcop/postgres.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.483
diff -c -r1.483 postgres.c
*** src/backend/tcop/postgres.c 4 Apr 2006 19:35:35 -   1.483
--- src/backend/tcop/postgres.c 15 Apr 2006 07:39:49 -
***
*** 1870,1875 
--- 1870,1876 
  static void
  exec_describe_statement_message(const char *stmt_name)
  {
+   MemoryContext   oldContext;
PreparedStatement *pstmt;
TupleDesc   tupdesc;
ListCell   *l;
***
*** 1882,1888 
start_xact_command();
  
/* Switch back to message context */
!   MemoryContextSwitchTo(MessageContext);
  
/* Find prepared statement */
if (stmt_name[0] != '\0')
--- 1883,1889 
start_xact_command();
  
/* Switch back to message context */
!   oldContext = MemoryContextSwitchTo(MessageContext);
  
/* Find prepared statement */
if (stmt_name[0] != '\0')
***
*** 1940,1946 
--- 1941,1950 
  NULL);
else
pq_putemptymessage('n');/* NoData */
+   
+   MemoryContextSwitchTo(oldContext);
  
+   finish_xact_command();
  }
  
  /*
***
*** 1951,1956 
--- 1955,1961 
  static void
  exec_describe_portal_message(const char *portal_name)
  {
+   MemoryContext   oldContext;
Portal  portal;
  
/*
***
*** 1960,1966 
start_xact_command();
  
/* Switch back to message context */
!   MemoryContextSwitchTo(MessageContext);
  
portal = GetPortalByName(portal_name);
if (!PortalIsValid(portal))
--- 1965,1971 
start_xact_command();
  
/* Switch back to message context */
!   oldContext = MemoryContextSwitchTo(MessageContext);
  
portal = GetPortalByName(portal_name);
if (!PortalIsValid(portal))
***
*** 1992,1997 
--- 1997,2006 
  
portal->formats);
else
pq_putemptymessage('n');/* NoData */
+   
+   MemoryContextSwitchTo(oldContext);
+ 
+   finish_xact_command();
  }
  
  
Index: src/interfaces/libpq/fe-exec.c
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v
retrieving revision 1.182
diff -c -r1.182 fe-exec.c
*** src/interfaces/libpq/fe-exec.c  14 Mar 2006 22:48:23 -  1.182
--- src/interfaces/libpq/fe-exec.c  15 Apr 2006 07:39:58 -
***
*** 55,60 
--- 55,62 
  static void parseInput(PGconn *conn);
  static bool PQexecStart(PGconn *conn);
  static PGresult *PQexecFinish(PGconn *conn);
+ static int pqDescribe(PGconn *conn, const char desc_type,
+ const char *desc_target);
  
  
  /* 
***
*** 2281,2286 
--- 2283,2392 
return 0;
  }
  
+ 
+ /*
+  * pqDescribe - Describe given prepar