Thread

7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-11-29T18:51:26Z

Hi guys,

Just out of interest, if someone was going to pay you to hack on Postgres
for 6 months, what would you like to code for 7.4?

My ones are:

* Compliant ADD COLUMN
* Integrated full text indexes
* pg_dump dependency ordering

What would you guys do?  Even if it isn't feasible right now...

Chris

Re: 7.4 Wishlist

Hannu Krosing <hannu@tm.ee> — 2002-11-29T21:54:20Z

Christopher Kings-Lynne kirjutas R, 29.11.2002 kell 23:51:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
> 
> My ones are:
> 
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
> 
> What would you guys do?  Even if it isn't feasible right now...

As I don't have a permanent job starting next year (my main employer
went bust), I'm planning to do more on postgreSQL anyway (at least until
I run out of money ;)

I have done some (or sometimes a lot of) brain-twitching on items in the
following list, but very little actual useful coding on most.

My personal todo list is:

"Application server support"
----------------------------
* better XML integration 
  
  - XML(*) aggregate function returning XML representation of subquery 

  - XML input/output to/from tables

  - XML searchable/indexable in fields)

* Overhaul of OO features (moving closer to SQL99)

  - type/table inheritance, 

    table inheritance would be done using SQL99's UNDER and would be
    single inheritance, stored in single logical table, possibly
    subdivided in physical tables reusing our current huge table 1GB
    split mechanisms

    type inheritance would be done using SQL99's LIKE and would be
    multiple inheritance and would reuse as much as possible the
    current code for ADD/DROP/RENAME column

    - check constraints would apply to both type and table inheritance

    - pk/fk constraints would apply only to table inheritance

  - types as base of tables,

  - study feasibility of reference types,

  - dynamic invocation of table function on queries over hierarchies


* WITH (as part of query/view)

* WITH RECURSIVE for recursive queries

* better NOTIFY (with optional argument, using shared memory
  instead of tables)


General stuff
-------------

* making array types btree-indexable in a general way

* study feasibility of using SQL99's ARRAY syntax for arrays

Data warehousing
----------------
* bitmap indexes,

  - using bitmap "indexes" internally for star joins

  - real bitmap indexes

  - clustered multiple bitmap indexes especially
    clustering on group of bitmap indexes

* clustering in general - specifying pages to be filled only to a
  certain percentage in clustered tables so that updated tuples can
  be placed near original ones if needed and parallel vacuum can
  then reclaim the space and keep table clustered with less shuffling.

* OLAP features
   - WINDOW clause, PARTITION BY
   - GROUPING SETS, ROLLUP, CUBE, () 


WAL-based master-slave replication
----------------------------------

* if someone is not doing it (which I hope is not true ;)


UNICODE / Localization
----------------------

* UTEXT, UCHAR, UVARCHAR types using IBM's ICU, stored in UTF-16 or SCSU

* fast LIKE, ILIKE, REGEX code for UTF-16, possibly lifted from python2

* field-level localization, again using ICU


FE/BE protocol
--------------

all can be worked on independently

* try to find a better wire protocol from existing ones (X-window
  system seems simple enough, perhaps DB2's DRDA) or fix the existing
  one for high performance (mainly make sure that as big chunks as
  possible have preceeding length), make it easy to send
  out-of-band/optional data (Notifications, info on actual query
  performance (so one can visualize it for user),  ...)

* standardize a fire-level binary protocol for field types (currently
  whatever is stored is sent)

* work on making python use this protocol and port some postgres
  datatypes (initially timestamp/date/time and varbit)to python 


Really Dark Arts
------------------

* making backend internals available to a scripting language (for me it
  means python ;) for making more parts (especially planner/optimizer)
  more easily hackable

* using the stuff from previous point ;)


And that's all ;)

----------------
Hannu Krosing

Re: 7.4 Wishlist

Matthew T. O'Connor <matthew@zeut.net> — 2002-11-29T22:29:32Z

pg_dump, our upgrade process is painful enough having to do a dump, reload.
I think we should be able to guarantee (or at least let much closer to it)
that the process works in all cases.

Personally pg_upgrade would be even nicer.

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, November 29, 2002 1:51 PM
Subject: [HACKERS] 7.4 Wishlist


> Hi guys,
>
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
>
> My ones are:
>
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
>
> What would you guys do?  Even if it isn't feasible right now...
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: 7.4 Wishlist

Daniele Orlandi <daniele@orlandi.com> — 2002-11-29T23:06:46Z

Christopher Kings-Lynne wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Replication. Replication. Replication. Replication. Replication.
Replication. Replication. Replication. Replication. Replication.
Replication. Replication. Replication. Replication. Replication.

Well, jokes apart, I think this is one of the most needed features to 
me. Currently I'm using strange voodoo to replicate some tables on other 
machines in order to spread load and resilency. Compared to what I am 
doing now a good master to slave replication would be heaven.

I understand that a good replication is painful but in my experience, if 
you start by integrating some rude, experimental implementation in the 
mainstream PostgreSQL the rest will come by itself.

For example, RI was something I wouldn't consider "production level" in 
7.2, but was a start, now in 7.3 is much much better, probably complete 
in the most important parts.

Other wishes (not as important as the replication issue) are:

- Better granularity of security and access control, like in mysql.

- Ability to reset the state of an open backend, including aborting open 
transaction to allow for better connection pooling and reusing, maybe 
giving the client the ability to switch between users...

Bye!

-- 
  Daniele Orlandi
  Planet Srl

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-11-29T23:31:56Z

Wow Hannu - your list puts mine to shame!

> "Application server support"
> ----------------------------
> * better XML integration
>
>   - XML(*) aggregate function returning XML representation of subquery
>
>   - XML input/output to/from tables
>
>   - XML searchable/indexable in fields)

I've had thoughts about XML too.  Since XML is hierachical, imagine being
able to index xml using contrib/ltree or something!

ie. We create a new 'xml' column type.

We create a new indexing scheme for it based on ltree & gist.

You index the xml column.

Then you can do sort of XPath queries:

SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
'Bob';

And it would be indexed.  Imaging being able to pull up all XML documents
that had certain properties, etc.

MS-SQL has a SELECT ... FOR XML clause, but we could always just create
function called xml_select() or something now that we can return recordsets.

Chris

Re: 7.4 Wishlist

Philip Warner <pjw@rhyme.com.au> — 2002-11-30T00:45:20Z

At 10:51 AM 29/11/2002 -0800, Christopher Kings-Lynne wrote:
>* pg_dump dependency ordering

I've actually started working on pg_dump in the background, but if you want 
to do it let me know.

In terms of things I would like to see:

- background/integrated vacuum (not just an overwriting storage manager)

- insert/update...returning

- function result caches -- assuming I can demonstrate that they are a Good 
Thing.

- COPY TO/FROM with a list of columns (maybe we have it?) - it's useful for 
making metadata changes then reloading data (Inserts are much slower).




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-11-30T00:56:24Z

> I've actually started working on pg_dump in the background, but if you
want
> to do it let me know.

Nah - you can do it!  I don't have much time to code Postgres as it is.
I'll stick to working on making ADD COLUMN compliant.

> In terms of things I would like to see:
>
> - background/integrated vacuum (not just an overwriting storage manager)

Work has started on this already, which is neat.

> - insert/update...returning

That would be so useful for our web app where we're always needing to get
the primary key as opposed to oid.  We keep needing to requery.

> - function result caches -- assuming I can demonstrate that they are a
Good
> Thing.
>
> - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful
for
> making metadata changes then reloading data (Inserts are much slower).

We do already have it in 7.3:

http://developer.postgresql.org/docs/postgres/sql-copy.html

Chris

Re: 7.4 Wishlist

Philip Warner <pjw@rhyme.com.au> — 2002-11-30T01:22:03Z

At 04:56 PM 29/11/2002 -0800, Christopher Kings-Lynne wrote:
> >
> > - COPY TO/FROM with a list of columns (maybe we have it?) - it's useful
>for
> > making metadata changes then reloading data (Inserts are much slower).
>
>We do already have it in 7.3:

Excellent. Then I just need to add support in pg_dump.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-11-30T01:33:09Z

> > > - COPY TO/FROM with a list of columns (maybe we have it?) - it's
useful
> >for
> > > making metadata changes then reloading data (Inserts are much slower).
> >
> >We do already have it in 7.3:
>
> Excellent. Then I just need to add support in pg_dump.

Hmmm.  I could have sworn that someone (Neil?) already did that?

Chris

Locale-dependent case conversion in {identifier}

Nicolai Tufar <ntufar@apb.com.tr> — 2002-11-30T01:40:39Z

Comment in {identifier} section in src/backend/parser/scan.l states:
                                     [...]
           * Note: here we use a locale-dependent case conversion,
           * which seems appropriate under SQL99 rules, whereas
           * the keyword comparison was NOT locale-dependent.
           */

And in ScanKeywordLookup() in src/backend/parser/keywords.c:

        /*
         * Apply an ASCII-only downcasing.      We must not use tolower()
since it
         * may produce the wrong translation in some locales (eg, Turkish),
         * and we don't trust isupper() very much either.  In an ASCII-based
         * encoding the tests against A and Z are sufficient, but we also
         * check isupper() so that we will work correctly under EBCDIC.  The
         * actual case conversion step should work for either ASCII or
EBCDIC.
         */

And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as
you
may know our "I" is not your "I":

    pgsql=# create table a(x char(1));
    CREATE TABLE
    pgsql=# grant SELECT ON a to PUBLIC;
    ERROR:  user "public" does not exist
    pgsql=#

Oracle, the second best database I have does seem to convert relation names
in
locale-dependent fassion:

   SQL> alter session set NLS_LANGUAGE='TURKISH';
   Session altered.
   SQL> create table a(x char(1));
   Table created.
   SQL> grant select on a to PUBLIC;
   Grant succeeded.

Further, if I try to create a table in oracle using Turkish-specific
characters,
it is creating it alright, without trying to make them upper-case as it
usually does.

So I have changed lower-case conversion code in scan.l to make it purely
ASCII-based
as in keywords.c. Mini-patch is given below. Please bear in mind that it is
my first
attempt at hacking PostgreSQL code, so there can be some mistakes.

Regards,
Nick


diff -Nur src/backend/parser/scan.l.orig src/backend/parser/scan.l
--- src/backend/parser/scan.l.orig      Sat Nov 30 02:54:06 2002
+++ src/backend/parser/scan.l   Sat Nov 30 02:57:45 2002
@@ -551,9 +551,12 @@
                                        ident = pstrdup(yytext);
                                        for (i = 0; ident[i]; i++)
                                        {
-                                               if (isupper((unsigned char)
ident[i]))
-                                                       ident[i] =
tolower((unsigned char) ident[i]);
+                                               char            ch =
ident[i];
+                                               if (ch >= 'A' && ch <= 'Z'
&& isupper((unsigned char) ch))
+                                                       ch += 'a' - 'A';
+                                                       ident[i] = ch;
                                        }
+                                       ident[i] = '\0';
                                        if (i >= NAMEDATALEN)
                     {
                                                int len;

Re: 7.4 Wishlist

Philip Warner <pjw@rhyme.com.au> — 2002-11-30T01:40:56Z

At 05:33 PM 29/11/2002 -0800, Christopher Kings-Lynne wrote:
>Hmmm.  I could have sworn that someone (Neil?) already did that?

Not AFAICT - at least based on looking at the manual. I'll check the code.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Locale-dependent case conversion in {identifier}

Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-30T04:24:08Z

"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> So I have changed lower-case conversion code in scan.l to make it purely
> ASCII-based.
> as in keywords.c. Mini-patch is given below.

Rather than offering a patch, you need to convince us why our reading of
the SQL standard is wrong.  ("Oracle does it that way" is not an
argument that will carry a lot of weight.)

SQL99 states that identifier case conversions are done on the basis of
the Unicode upper/lower case equivalences, so it seems clear that they
intend more than ASCII-only conversion for identifiers.  Locale-based
conversion might not be an exact implementation of the spec, but it's
surely closer than ASCII-only.

			regards, tom lane

Re: 7.4 Wishlist

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2002-11-30T05:55:07Z

On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote:

> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Well, nobody is paying me, but I want to 

- fix the btree problem leaking unused pages (I think I'm getting near,
  I just haven't had free time during the last month).  This one is a
  must to me.

- try different regexp algorithms, compare efficiency.  Both Henry
  Spencer's new code for Tcl, and Baeza-Navarro shift-or approach (can
  be much faster than "traditional" regex engines)
  (do people care for allowing "search with errors", similar to what
  agrep and nrgrep do?)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Nunca confiaré en un traidor.  Ni siquiera si el traidor lo he creado yo"
(Barón Vladimir Harkonnen)

Re: Locale-dependent case conversion in {identifier}

Nicolai Tufar <ntufar@apb.com.tr> — 2002-11-30T07:57:44Z

By no means I would try to convince that your reading of
the SQL standards is wrong. What I am trying to tell is
that Turkish alphabet is broken beyond repair. And since
there is absolutely no way to change our alphabet, we
may can code a workaround in the code.

So i do not claim that your code is wrong. It is
behaviang according to specification. But unfortunately
folks at SQL99 probably were not aware of the woes
of Turkish "I".

The very special case of letter "I" in Turkish is not
only PostgreSQL's problem. Many java programs have
failed miserably trying to open files with "I"s in
pathnames.

So basically, there are two letters "I" in Trukish.
The wone is with dot on top and another is without.
The with dot on top walways has the dot and the one
without never has it. Simple. The problem is
with the standard Latin "I". So why small "i" does
have a dot and capital "I" does not?

Standard conversion is
Lower: "I" -> "y'" and "Y'" -> "i".
Upper: "y'"  -> "I" and "i" -> "Y'".
(font may not be displayed correctly in your mail reader)

Historically programs that operate in Turkish locale have
chosen to hardcode the capitalisation of "i" in system
messages and identifier names like this:

Lower: "I" -> "i" and "Y'" -> "i".
Upper: "y'"  -> "I" and "i" -> "I".

With this, no matter what kind of "I" you used in names,
it is always going to end up a valid ASCII character.

Would it be acceptable if I submit a path that applies this
special logic in src/backend/parser/scan.l if the locale is "tr_TR"?

Because for many folks setting locale to Turkish would
render their database unusable. For, god forbid, if your
sql has a column name written in capitlas including "I".
It is not working. So I deeply believe that PostgreSQL community
have to provide a workaround for this problem.

So what should I do?

Best regards,
Nick

Tom Lane wrote:
> "Nicolai Tufar" <ntufar@apb.com.tr> writes:
> 
>>So I have changed lower-case conversion code in scan.l to make it purely
>>ASCII-based.
>>as in keywords.c. Mini-patch is given below.
> 
> 
> Rather than offering a patch, you need to convince us why our reading of
> the SQL standard is wrong.  ("Oracle does it that way" is not an
> argument that will carry a lot of weight.)
> 
> SQL99 states that identifier case conversions are done on the basis of
> the Unicode upper/lower case equivalences, so it seems clear that they
> intend more than ASCII-only conversion for identifiers.  Locale-based
> conversion might not be an exact implementation of the spec, but it's
> surely closer than ASCII-only.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

Re: 7.4 Wishlist

Neil Conway <neilc@samurai.com> — 2002-11-30T08:06:16Z

On Fri, 2002-11-29 at 18:06, Daniele Orlandi wrote:
> - Better granularity of security and access control, like in mysql.

Can you be more specific on exactly what features you'd like to see?

> - Ability to reset the state of an open backend, including aborting open 
> transaction to allow for better connection pooling and reusing

IIRC, it's been suggested that we can implement this by passing back the
transaction state as part of the FE/BE protocol -- if we're doing a
protocol change for 7.4, this could be part of it.

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: 7.4 Wishlist

Neil Conway <neilc@samurai.com> — 2002-11-30T08:18:30Z

On Fri, 2002-11-29 at 13:51, Christopher Kings-Lynne wrote:
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Here's some of my current TODO list:

* FOR EACH STATEMENT triggers (already done)

* column lists for UPDATE triggers (will be done soon)

* Improve the buffer manager's replacement algorithm (LRU-K, perhaps?)

* Implement support for hugetlb pages on linux 2.5

* Finish off PITR (if no one else does ...)

* Asynchronous notification improvements (optional message, store
notifications in shared memory)

* Rowtype assignment in PL/PgSQL

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: 7.4 Wishlist

Hans-Jürgen Schönig <postgres@cybertec.at> — 2002-11-30T09:19:53Z

What I'd like to have in future versions of PostgreSQL:

    - replication, replication, ... (you have seen that before). i guess 
most people would like to see that.

    - a dblink like system for connecting to remote database systems 
(not just PostgreSQL???)
        something like CREATE REMOTE VIEW would be damn good.
        it would solve many problem when it comes to migration

    - tablespaces (the directory based stuff which has been discussed on 
the list)      

    - somebody has mentioned XML before. the XPath stuff would be really 
cool

    - PL/Sh should be in contrib. i know that the core team has decided 
not to put it in the core but contrib would be fine (I keep forgetting 
the URL of Peters website :( ...)

    - packages: is there a way to define a set of functions as a package 
so that they can be removed using just one DROP PACKAGE or so? would be 
nice for huge projects

    - urgent: being able to use PL/Perl in combination with SPI (There 
is a Pg-SPI but it is 0.01 - see  
http://search.cpan.org/author/APILOS/DBD-PgSPI-0.01/PgSPI.pm). a full 
and reliable implementation would be fine.

    - preforking for faster startup

    - declare MySQL as evil *g*.


        Thanks a lot :)

            Hans

Re: 7.4 Wishlist

Hans-Jürgen Schönig <postgres@cybertec.at> — 2002-11-30T09:35:00Z

Oops, there is something I have forgotten:

    - "nicing" backends: this would be nice for administration tasks

    - CREATE DATABASE ... WITH MAXSIZE (many providers would like to see 
that; quotas are painful in this case - especially when porting the 
database to a different or a second server)

    Hans

Re: 7.4 Wishlist

Al Sutton <al@alsutton.com> — 2002-11-30T10:58:11Z

My list is;

Point to Point and Broadcast replication
----------------------------------------
With point to point you specify multiple endpoints, with broadcast you can
specify a subnet address and the updates are broadcast over that subnet.

The difference being that point to point works well for cross network
replication, or where you have a few replicants. I have multiple database
servers which could have a deadicated class C network that they are all on,
by broadcasting updates you can cutdown the amount of traffic on that net by
a factor of n minus 1 (where n is the number of servers involved).

Ability to use raw partitions
----------------------------

I've not seen an install of PostgreSQL yet that didn't put the database
files onto a filesystem, so I'm assuming it's the only way of doing it. By
using the filesystem the files are at the mercy of filesystem handler code
as to where they end up on the disk, and thus the speed of access will
always have some dependancy on the speed of the filesystem.

With a raw partition it would be possible to use two devices (e.g. /dev/hde
and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then
ensure the WALs were located on one the disk with the entries running
sequentally, and that the database files were located on the other disk in
the most appropriate location (e.g. index data starting near the center of
the disk, and user table data starting near the outside).

Win32 Port
------------
I've explained the reasons before. Apart from that it's always useful to
open PostgreSQL up to a larger audience.

----- Original Message -----
From: "Daniele Orlandi" <daniele@orlandi.com>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, November 29, 2002 11:06 PM
Subject: [mail] Re: [HACKERS] 7.4 Wishlist

> Christopher Kings-Lynne wrote:
> > Hi guys,
> >
> > Just out of interest, if someone was going to pay you to hack on
Postgres
> > for 6 months, what would you like to code for 7.4?
>
> Replication. Replication. Replication. Replication. Replication.
> Replication. Replication. Replication. Replication. Replication.
> Replication. Replication. Replication. Replication. Replication.
>
> Well, jokes apart, I think this is one of the most needed features to
> me. Currently I'm using strange voodoo to replicate some tables on other
> machines in order to spread load and resilency. Compared to what I am
> doing now a good master to slave replication would be heaven.
>
> I understand that a good replication is painful but in my experience, if
> you start by integrating some rude, experimental implementation in the
> mainstream PostgreSQL the rest will come by itself.
>
> For example, RI was something I wouldn't consider "production level" in
> 7.2, but was a start, now in 7.3 is much much better, probably complete
> in the most important parts.
>
> Other wishes (not as important as the replication issue) are:
>
> - Better granularity of security and access control, like in mysql.
>
> - Ability to reset the state of an open backend, including aborting open
> transaction to allow for better connection pooling and reusing, maybe
> giving the client the ability to switch between users...
>
> Bye!
>
> --
>   Daniele Orlandi
>   Planet Srl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: 7.4 Wishlist

Kaare Rasmussen <kar@kakidata.dk> — 2002-11-30T11:07:09Z

> And that's all ;)
> ----------------
> Hannu Krosing

- and what will you do after January? ;-)

Just kidding. I hope you have a big fat bank account if you want to finish 
all that!

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               Åben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lørdag 12.00-16.00       Web:      www.suse.dk

Re: 7.4 Wishlist

Alice Lottini <alice_lottini@yahoo.it> — 2002-11-30T11:15:15Z

Hi,
although I'm just a novice in this mailing list I'd like to give my
contribution to the 7.4 wishlist.
I'd like to add to the PostgreSQL code some new low-level, primitive
fuctions in order to give native support to FP-based algorithms for rule
mining (Frequent Pattern Growth and extensions such as CLOSET and so on).
As a matter of fact, this is more than just a wish to me... this is the task
I have to accomplish for my thesis (I'm going to degree in Informatics
Engineering at the Politecnico di Torino, Italy on next July), and so I can
assure you that this will be done (and working) by the end of June.
Obviously, any kind of hint and suggestion by you guruz is welcome! :)
Bye, alice

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, November 29, 2002 7:51 PM
Subject: [HACKERS] 7.4 Wishlist


> Hi guys,
>
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
>
> My ones are:
>
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
>
> What would you guys do?  Even if it isn't feasible right now...
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


______________________________________________________________________
Scarica il nuovo Yahoo! Messenger: con webcam, nuove faccine e tante altre novità.
http://it.yahoo.com/mail_it/foot/?http://it.messenger.yahoo.com/

Re: 7.4 Wishlist

Haris Peco <snpe@snpe.co.yu> — 2002-11-30T11:31:07Z

On Friday 29 November 2002 06:51 pm, Christopher Kings-Lynne wrote:
> Hi guys,
>
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
>
> My ones are:
>
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
>
> What would you guys do?  Even if it isn't feasible right now...
>
> Chris
>

My wishlist :
	- savepoint
	- cursor out of a transaction
	- distributed databases and replication (two phase commit)
	- only share lock in foreign keys 
	- prepare/execute on backend level
	- error in a statement break a statement, not complete transaction

regards
Haris Peco

about ODBC

Horacio Miranda <hmiranda@yale.cl> — 2002-11-30T14:54:11Z

Someone know any ODBC driver without problem with UPPER CASE ?
--
                 Saludos Horacio Miranda.
                     hmiranda@yale.cl
------------------------------------------------------------
PostgreSQL.  Because life's too short to learn Oracle.    :)
                      Billy O'Connor

             IBM --> Immer Backup Machen

Re: Locale-dependent case conversion in {identifier}

Hannu Krosing <hannu@tm.ee> — 2002-11-30T15:41:17Z

On Sat, 2002-11-30 at 01:40, Nicolai Tufar wrote:
> And I happen to have bad luck to use PostgreSQL with Turkish locale. And, as
> you
> may know our "I" is not your "I":
> 
>     pgsql=# create table a(x char(1));
>     CREATE TABLE
>     pgsql=# grant SELECT ON a to PUBLIC;
>     ERROR:  user "public" does not exist
>     pgsql=#
> 
> Oracle, the second best database I have does seem to convert relation names
> in
> locale-dependent fassion:
> 
>    SQL> alter session set NLS_LANGUAGE='TURKISH';
>    Session altered.
>    SQL> create table a(x char(1));
>    Table created.
>    SQL> grant select on a to PUBLIC;
>    Grant succeeded.

could it just be that we store identifiers in lower case, whereas most others
(including SQL spec IIRC)have them in upper case ?

Could you try the grant in both databases also in lower case ?

i.e.:

grant select on a to public;

------------------
Hannu

Re: Locale-dependent case conversion in {identifier}

Hannu Krosing <hannu@tm.ee> — 2002-11-30T15:47:58Z

On Sat, 2002-11-30 at 07:57, Nicolai Tufar wrote:
> With this, no matter what kind of "I" you used in names,
> it is always going to end up a valid ASCII character.
> 
> Would it be acceptable if I submit a path that applies this
> special logic in src/backend/parser/scan.l if the locale is "tr_TR"?
> 
> Because for many folks setting locale to Turkish would
> render their database unusable. For, god forbid, if your
> sql has a column name written in capitlas including "I".
> It is not working. So I deeply believe that PostgreSQL community
> have to provide a workaround for this problem.
> 
> So what should I do?

In SQL in general and in postgreSQL in particular, you can always use
quoted names and thus escape the stupidities of case conversion:

grant SELECT ON "a" to "public";

should work everywhere (except Oracle and other DB's where it should be
 grant SELECT ON "A" to "PUBLIC";
)

I can't help you on Win32/VMS filenames ;)

---------------
Hannu

Re: 7.4 Wishlist

Hannu Krosing <hannu@tm.ee> — 2002-11-30T15:57:52Z

On Sat, 2002-11-30 at 05:55, Alvaro Herrera wrote:
> On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote:
> 
> > Just out of interest, if someone was going to pay you to hack on Postgres
> > for 6 months, what would you like to code for 7.4?
> 
> Well, nobody is paying me, but I want to 
> 
> - fix the btree problem leaking unused pages (I think I'm getting near,
>   I just haven't had free time during the last month).  This one is a
>   must to me.
> 
> - try different regexp algorithms, compare efficiency.  Both Henry
>   Spencer's new code for Tcl, and Baeza-Navarro shift-or approach (can
>   be much faster than "traditional" regex engines)

Perhaps bigger effect could be possible if we could could make
LIKE/REGEXP use indexes - perhaps some approach based on trigrams could
be usable here ?

>   (do people care for allowing "search with errors", similar to what
>   agrep and nrgrep do?)

Yes, especially if integrated with some full text index scheme.

-- 
Hannu Krosing <hannu@tm.ee>

Re: Locale-dependent case conversion in {identifier}

Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-30T16:02:19Z

Nicolai Tufar <ntufar@apb.com.tr> writes:
> Historically programs that operate in Turkish locale have
> chosen to hardcode the capitalisation of "i" in system
> messages and identifier names like this:

> Lower: "I" -> "i" and "Y'" -> "i".
> Upper: "y'"  -> "I" and "i" -> "I".

If that's the behavior you want, why don't you set up a variant locale
definition that does it that way?  That would fix *all* your locale-
dependent programs, not just Postgres ...

> Would it be acceptable if I submit a path that applies this
> special logic in src/backend/parser/scan.l if the locale is "tr_TR"?

It really seems like an inappropriate wart to me :-(

> Because for many folks setting locale to Turkish would
> render their database unusable. For, god forbid, if your
> sql has a column name written in capitlas including "I".
> It is not working.

I am not seeing why this is any worse than the universal problems of
using upper-case letters without double-quoting 'em.  If you
consistently spell the name the same way, you will not have a problem;
if you don't, you might have a problem, but why is it worse than
anyone else's?

			regards, tom lane

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-30T16:13:51Z

Hans-Jrgen Schnig wrote:
> What I'd like to have in future versions of PostgreSQL:
>
>     - PL/Sh should be in contrib. i know that the core team has decided 
> not to put it in the core but contrib would be fine (I keep forgetting 
> the URL of Peters website :( ...)

I like PL/Sh too, but too many people are concerned it isn't
transaction-safe and has poor performance.  I want it in /contrib, but
Peter, the author, doesn't want it in there, so there isn't much we can
do.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-11-30T17:33:23Z

Hannu Krosing wrote:
> On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote:
> > Hans-J?rgen Sch?nig wrote:
> > > What I'd like to have in future versions of PostgreSQL:
> > >
> > >     - PL/Sh should be in contrib. i know that the core team has decided 
> > > not to put it in the core but contrib would be fine (I keep forgetting 
> > > the URL of Peters website :( ...)
> 
> You could put the URL in /contrib

The URL used to be main site under:

	http://www.us.postgresql.org/interfaces.html

but I don't see it there anymore.  In fact, that page needs updating
because some projects have moved.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-11-30T17:47:53Z

On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote:

> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

> What would you guys do?  Even if it isn't feasible right now...

Hmm, mine would probably be fixing foreign keys (along with making it
work with inheritance and match partial) and check constraints with
subselects.

Re: 7.4 Wishlist

Gabor Csuri <gcsuri@auto999.com> — 2002-11-30T18:58:51Z

Hi All,

 here is my wishlist /very short/ :

* Oracle syntax support in OUTER JOIN

Thanks, Gabor


> Hans-Jürgen Schönig wrote:
> > What I'd like to have in future versions of PostgreSQL:
> >
> >     - PL/Sh should be in contrib. i know that the core team has decided
> > not to put it in the core but contrib would be fine (I keep forgetting
> > the URL of Peters website :( ...)
>
> I like PL/Sh too, but too many people are concerned it isn't
> transaction-safe and has poor performance.  I want it in /contrib, but
> Peter, the author, doesn't want it in there, so there isn't much we can
> do.
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square,
> Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: 7.4 Wishlist

Hannu Krosing <hannu@tm.ee> — 2002-11-30T19:02:35Z

On Sat, 2002-11-30 at 16:13, Bruce Momjian wrote:
> Hans-Jürgen Schönig wrote:
> > What I'd like to have in future versions of PostgreSQL:
> >
> >     - PL/Sh should be in contrib. i know that the core team has decided 
> > not to put it in the core but contrib would be fine (I keep forgetting 
> > the URL of Peters website :( ...)

You could put the URL in /contrib

> I like PL/Sh too, but too many people are concerned it isn't
> transaction-safe and has poor performance.  I want it in /contrib, but
> Peter, the author, doesn't want it in there, so there isn't much we can
> do.

perhaps the URL and a file WARNING.TXT ;)

-- 
Hannu Krosing <hannu@tm.ee>

Re: 7.4 Wishlist

Oleg Bartunov <oleg@sai.msu.su> — 2002-11-30T19:24:49Z

Me and Teodor hope to work on contrib/ltree to add support for sort of
xml. Any ideas are welcome !

	Regards,

		Oleg
On Fri, 29 Nov 2002, Christopher Kings-Lynne wrote:

> Wow Hannu - your list puts mine to shame!
>
> > "Application server support"
> > ----------------------------
> > * better XML integration
> >
> >   - XML(*) aggregate function returning XML representation of subquery
> >
> >   - XML input/output to/from tables
> >
> >   - XML searchable/indexable in fields)
>
> I've had thoughts about XML too.  Since XML is hierachical, imagine being
> able to index xml using contrib/ltree or something!
>
> ie. We create a new 'xml' column type.
>
> We create a new indexing scheme for it based on ltree & gist.
>
> You index the xml column.
>
> Then you can do sort of XPath queries:
>
> SELECT * FROM requests WHERE xml_xpath('/request/owner/name', datafield) =
> 'Bob';
>
> And it would be indexed.  Imaging being able to pull up all XML documents
> that had certain properties, etc.
>
> MS-SQL has a SELECT ... FOR XML clause, but we could always just create
> function called xml_select() or something now that we can return recordsets.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Re: 7.4 Wishlist

bpalmer <bpalmer@crimelabs.net> — 2002-11-30T20:06:15Z

> * Compliant ADD COLUMN

I've missed the thread (if there was one),  how is it non-compliant?

Thanks,
- Brandon

----------------------------------------------------------------------------
 c: 917-697-8665                                            h: 201-798-4983
 b. palmer,  bpalmer@crimelabs.net           pgp:crimelabs.net/bpalmer.pgp5

Re: 7.4 Wishlist

Rod Taylor <rbt@rbt.ca> — 2002-11-30T20:13:48Z

On Sat, 2002-11-30 at 15:06, bpalmer wrote:
> > * Compliant ADD COLUMN
> 
> I've missed the thread (if there was one),  how is it non-compliant?

ALTER TABLE .. ADD COLUMN colname integer DEFAULT 42 NOT NULL
CHECK(colname <= 42) REFERENCES tab2 ON DELETE CASCADE;

Can't do the above in a single statement.  It takes five statements.

It's something I'd like to see added as well.

-- 
Rod Taylor <rbt@rbt.ca>

Re: 7.4 Wishlist

Dan Langille <dan@langille.org> — 2002-11-30T20:19:09Z

Can you see this tying in with my recent hack of contrib/ltree to work
with a wider range of node names?

On Sat, 30 Nov 2002, Oleg Bartunov wrote:

> Me and Teodor hope to work on contrib/ltree to add support for sort of
> xml. Any ideas are welcome !

Re: Locale-dependent case conversion in {identifier}

Nicolai Tufar <ntufar@apb.com.tr> — 2002-11-30T21:18:34Z

----- Original Message -----
From: "Hannu Krosing" <hannu@tm.ee>
To: "Nicolai Tufar" <ntufar@apb.com.tr>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, November 30, 2002 5:41 PM
Subject: Re: [HACKERS] Locale-dependent case conversion in {identifier}

             [ ... ]
>
> could it just be that we store identifiers in lower case, whereas most
others
> (including SQL spec IIRC)have them in upper case ?

That seem to be the case. All the databases I used, automaticaly convert
identifiers to upper case.
And they all do it using ASCII-only conversion.

>
> Could you try the grant in both databases also in lower case ?
>
> i.e.:
>
> grant select on a to public;

The statement works in both databases. But the problem is that it was
pg_dumpall who created SQL statements with PUBLIC. Why
pg_dumpall does not enclose identifiers in quotes, like:

REVOKE ALL ON TABLE "tamara2" FROM "public";
      insted of
REVOKE ALL ON TABLE tamara2 FROM PUBLIC;
as it does now.

I will make an attempt to modify pg_dump accordingly, and will send a
patch to the list.

Regards,
Nic

Re: 7.4 Wishlist

Joe Conway <mail@joeconway.com> — 2002-11-30T21:44:40Z

Christopher Kings-Lynne wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?
> 
> My ones are:
> 
> * Compliant ADD COLUMN
> * Integrated full text indexes
> * pg_dump dependency ordering
> 
> What would you guys do?  Even if it isn't feasible right now...

Well, I might as well join in the fun. Here's my personal TODO for 7.4 (with 
much wishing that I'll actually have the time to do most if not all of it ;-))

* continue to improve usability of bytea datatype
   - easier explicit casting between bytea and text
* stored procedures (procedures not functions)
   - no return value, but allow projection of results similar to SHOW ALL
     (i.e. also similar to MSSQL and Sybase)
   - CREATE PROCEDURE sp_my_stored_proc() AS '...' LANGUAGE '...';
   - CALL sp_my_stored_proc;
* array related improvements (note: some of this may exist in contrib as
     I haven't looked too close yet, but I'm aiming for these to be internal
     backend functions)
   - function to return users in a group as rows instead of as an array
   - generic table function to unspool an array into rows [and columns for
     2 dim array]
   - split --  split string into array on delimiter
   - implode -- join array elements into a string using given string delimiter
   - array_contains -- Return TRUE if a value exists in an array
   - array_search -- Searches the array for a given value and returns the
     corresponding key if successful
* PL/R
   - new PL interface to R (statistical analysis package based on the S
     language)
* improvements to contrib/tablefunc
   - enhanced crosstab functionality
   - possibly enhanced connectby functionality (may not be worth it if
     RECURSIVE JOIN functionality makes it into 7.4)
* improvements to dblink
   - see details on other recently sent message (ability to connect to
     non-PostgreSQL databases)
* revisit table function scan issues (i.e. tuplestore vs streaming vs
   portal based)

Things not on my list, but that I'm keenly interested in (in no particular order):
- native win32 port
- two-phase commit
- PITR
- replication
- recursive joins (CONNECT BY PRIOR ... START WITH ...)

Joe

Re: 7.4 Wishlist

David Wheeler <david@wheeler.net> — 2002-11-30T22:35:32Z

On Saturday, November 30, 2002, at 01:44  PM, Joe Conway wrote:

> * continue to improve usability of bytea datatype
>   - easier explicit casting between bytea and text

This wouldn't happen to include the idea of somehow eliminating the 
difference between how text strings are delimited and how bytea strings 
are delimited, would it?

Best,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org

Re: 7.4 Wishlist

ow <oneway_111@yahoo.com> — 2002-11-30T23:39:48Z

Cross-db queries.




__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Re: 7.4 Wishlist

Joe Conway <mail@joeconway.com> — 2002-12-01T00:14:12Z

David Wheeler wrote:
> On Saturday, November 30, 2002, at 01:44  PM, Joe Conway wrote:
>> * continue to improve usability of bytea datatype
>>   - easier explicit casting between bytea and text
> 
> This wouldn't happen to include the idea of somehow eliminating the 
> difference between how text strings are delimited and how bytea strings 
> are delimited, would it?

Not quite sure what you mean by delimiter -- are you referring to double
escaping vs single escaping?

Joe

Re: 7.4 Wishlist

David Wheeler <david@wheeler.net> — 2002-12-01T00:25:57Z

On Saturday, November 30, 2002, at 04:14  PM, Joe Conway wrote:

> Not quite sure what you mean by delimiter -- are you referring to 
> double
> escaping vs single escaping?

Oh crap, yes, that's exactly what I meant.

s/delimited/escaped/g;

Sorry. :-)

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-01T00:27:50Z

> > My ones are:
> >
> > * Compliant ADD COLUMN
> > * Integrated full text indexes
> > * pg_dump dependency ordering
> >
> > What would you guys do?  Even if it isn't feasible right now...

Actually - I think I might add MODIFY COLUMN to that list.  Just look at the
list of poor buggers in the interactive docs who can't change their column
types.  Guess that means I'd need to bring in attlognum's tho.

Chris

Hard-coded PUBLIC in pg_dump

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T01:45:46Z

src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
It completly breaks dumps when run with Turksh locale setting. In my
opinion making it lower-case would do much good and no harm. A mini
patch is given below.

On the other hand, I was thinking about wrapping all the identifiers in
dump files in single quotes. It is done in "SET SESSION AUTHORIZATION"
clause. Is there a reason for not doing this with table and colum names?

Regards,
Nic



 

*** ./src/bin/pg_dump/pg_dump.c.orig	Sun Dec  1 03:23:56 2002
--- ./src/bin/pg_dump/pg_dump.c	Sun Dec  1 03:24:48 2002
***************
*** 4918,4924 ****
  	 * wire-in knowledge about the default public privileges for different
  	 * kinds of objects.
  	 */
! 	appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",
  					  type, name);
  
  	/* Make a working copy of acls so we can use strtok */
--- 4918,4924 ----
  	 * wire-in knowledge about the default public privileges for different
  	 * kinds of objects.
  	 */
! 	appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM public;\n",
  					  type, name);
  
  	/* Make a working copy of acls so we can use strtok */
***************
*** 4980,4986 ****
  				if (eqpos == tok)
  				{
  					/* Empty left-hand side means "PUBLIC" */
! 					appendPQExpBuffer(sql, "PUBLIC;\n");
  				}
  				else if (strncmp(tok, "group ", strlen("group ")) == 0)
  					appendPQExpBuffer(sql, "GROUP %s;\n",
--- 4980,4986 ----
  				if (eqpos == tok)
  				{
  					/* Empty left-hand side means "PUBLIC" */
! 					appendPQExpBuffer(sql, "public;\n");
  				}
  				else if (strncmp(tok, "group ", strlen("group ")) == 0)
  					appendPQExpBuffer(sql, "GROUP %s;\n",

Re: Hard-coded PUBLIC in pg_dump

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-01T02:05:29Z

> src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.
> It completly breaks dumps when run with Turksh locale setting. In my
> opinion making it lower-case would do much good and no harm. A mini
> patch is given below.


Hmmmm...does putting double quotes (eg. "PUBLIC") around the public word fix
it?

> On the other hand, I was thinking about wrapping all the identifiers in
> dump files in single quotes. It is done in "SET SESSION AUTHORIZATION"
> clause. Is there a reason for not doing this with table and colum names?

You can't put single quotes around table and column names.  You need to use
double quotes as they are identifiers rather than literals.

Bear in mind that some improvements have been made in Postgres 7.3 with
regards to quoting, so have you checked 7.3?

Chris

Re: Hard-coded PUBLIC in pg_dump

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T02:29:27Z

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
To: "Nicolai Tufar" <ntufar@apb.com.tr>; <pgsql-hackers@postgresql.org>
Sent: Sunday, December 01, 2002 4:05 AM
Subject: Re: [HACKERS] Hard-coded PUBLIC in pg_dump

>
> Hmmmm...does putting double quotes (eg. "PUBLIC") around the public word
fix
> it?

No:
    apb=> GRANT SELECT ON TABLE maras2.esya TO "PUBLIC";
    ERROR:  user "PUBLIC" does not exist
    apb=> GRANT SELECT ON TABLE maras2.esya TO 'PUBLIC';
    ERROR:  parser: parse error at or near "'PUBLIC'" at character 38
    apb=> GRANT SELECT ON TABLE maras2.esya TO public;
    GRANT
    apb=>

The problem here is case conversion from capital "I" to lower-case "i".
In Turkish locale tolower('I') is not equal to 'i'. So, since "public" role
is lower-case internally, why would we not make it lower-case in dump file.

>
> You can't put single quotes around table and column names.  You need to
use
> double quotes as they are identifiers rather than literals.
>
> Bear in mind that some improvements have been made in Postgres 7.3 with
> regards to quoting, so have you checked 7.3?

I stand corrected. It is indeed has to be double-quoted.

7.3 is quoting only SET SESSION AUTHORIZATION 'role' clause in my dump.
Possibly,
because it has been added recently. Old code does not quote anything.

>
> Chris

Regards,
Nic.

Re: 7.4 Wishlist

Neil Conway <neilc@samurai.com> — 2002-12-01T03:16:08Z

On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote:
> check constraints with subselects.

Have we decided how this would even work? Last I heard, Tom still had
some major reservations about the practicality of implementing these --
for example, would you re-evaluate all constraints that SELECT from a
table when the table changes?

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Re: 7.4 Wishlist

Joe Conway <mail@joeconway.com> — 2002-12-01T03:24:39Z

David Wheeler wrote:
> On Saturday, November 30, 2002, at 04:14  PM, Joe Conway wrote:
>> Not quite sure what you mean by delimiter -- are you referring to double
>> escaping vs single escaping?
> 
> Oh crap, yes, that's exactly what I meant.
> 
> s/delimited/escaped/g;
> 

That is one thing I'd like to take a look at. I think the problem is that 
certain byte-sequence/multibyte-encoding combinations are illegal, so it's not 
as simple an issue as it might first appear.

Joe

Segmentation fault while COPY in 7.3

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T03:52:46Z

Help!
Backend crashes on any COPY command I issue:
PostgreSQL is 7.3 release, is compiled from sources.
The message I get is:

apb=# COPY maras2.mrk_yyn (bsvr_no, yyn_tur, yyn_no, yyn_syf, yyn_trh) TO
stdout;
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.
!#


The output of strace on backend is following:

# strace -p 18883
recv(7, "QCOPY maras2.mrk_yyn (bsvr_no, y"..., 8192, 0) = 77
gettimeofday({1038713111, 629131}, NULL) = 0
_llseek(11, 0, [0], SEEK_SET)           = 0
read(11, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\37\360\37\1 b1\5\0\1"..., 8192)
= 8192
read(11, "\0\0\0\0x#\375\"\20\0\0\0008\0h\35\360\37\1 \370\235\220"...,
8192) = 8192
_llseek(12, 0, [0], SEEK_SET)           = 0
read(12, "\0\0\0\0\0#\375\"\20\0\0\0008\0@\34\0 \1 \200\237\0\1\0"..., 8192)
= 8192
_llseek(21, 40960, [40960], SEEK_SET)   = 0
read(21, "\0\0\0\0\4\251\3#\20\0\0\0004\4p\17\360\37\1 \340\237 "..., 8192)
= 8192
_llseek(22, 106496, [106496], SEEK_SET) = 0
read(22, "\0\0\0\0(\326\377\"\20\0\0\0\10\1\200\1\0 \1 \200\237\0"..., 8192)
= 8192
read(17, "\0\0\0\0\20\0\0\0\1\0\0\0\360\0\20\1\0 \1 p\237 \1\340"..., 8192)
= 8192
open("/data/pgsql/base/2015749/2015757", O_RDWR|O_LARGEFILE) = 39
_llseek(39, 0, [20037632], SEEK_END)    = 0
_llseek(39, 0, [0], SEEK_SET)           = 0
read(39, "\0\0\0\0\270\7\4#\20\0\0\0\300\1\350\1\0 \1 \270\237\216"...,
8192) = 8192
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
#

I have recompiled postgres with debug information enabled, run gdb, attached
to backend process
and caught SIGSEGV as following:

(gdb) continue
Continuing.

Program received signal SIGSEGV, Segmentation fault.
0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
480             (*header->context->methods->free_p) (header->context,
pointer);
(gdb)


The output of bt is:

(gdb) bt
#0  0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
#1  0x080b37be in CopyTo (rel=0x402be088, attnumlist=0x828a3f8, binary=0
'\0', oids=0 '\0',
    fp=0x0, delim=0x81c2a57 "\t", null_print=0x81b2c22 "\\N") at copy.c:671
#2  0x080b32f6 in DoCopy (stmt=0x827b398) at copy.c:491
#3  0x08118a6a in pg_exec_query_string (query_string=0x827af48, dest=Remote,
    parse_context=0x8245378) at postgres.c:789
#4  0x08119b89 in PostgresMain (argc=4, argv=0xbfffd2a0, username=0x8240a81
"postgres")
    at postgres.c:2016
#5  0x08101d7c in DoBackend (port=0x8240950) at postmaster.c:2293
#6  0x081016c2 in BackendStartup (port=0x8240950) at postmaster.c:1915
#7  0x08100875 in ServerLoop () at postmaster.c:1000
#8  0x08100326 in PostmasterMain (argc=1, argv=0x82270c0) at
postmaster.c:779
#9  0x080debab in main (argc=1, argv=0xbfffdc34) at main.c:210
#10 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)



Thanks in advance,
Nic

Re: Segmentation fault while COPY in 7.3

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T04:20:50Z

While waiting for help I decided to fix my problem by
brute-forcing it. I commented out offending call to pfree()
in src/backend/commands/copy.c at line 671. I may introduced
a memory leak, but it works fine for me now.

Best regards,
Nic.




*** ./src/backend/commands/copy.c.orig	Sun Dec  1 06:02:34 2002
--- ./src/backend/commands/copy.c	Sun Dec  1 06:02:48 2002
***************
*** 668,674 ****
  								  ObjectIdGetDatum(elements[attnum - 1]),
  							Int32GetDatum(attr[attnum - 1]->atttypmod)));
  					CopyAttributeOut(fp, string, delim);
! 					pfree(string);
  				}
  				else
  				{
--- 668,674 ----
  								  ObjectIdGetDatum(elements[attnum - 1]),
  							Int32GetDatum(attr[attnum - 1]->atttypmod)));
  					CopyAttributeOut(fp, string, delim);
! 					/*pfree(string);*/
  				}
  				else
  				{

Re: 7.4 Wishlist

David Wheeler <david@wheeler.net> — 2002-12-01T04:24:17Z

On Saturday, November 30, 2002, at 07:24  PM, Joe Conway wrote:

> That is one thing I'd like to take a look at. I think the problem is 
> that certain byte-sequence/multibyte-encoding combinations are 
> illegal, so it's not as simple an issue as it might first appear.

My understanding is that the nul character is legal in a byte sequence, 
but if it's not properly escaped, it'll be parsed as the end of the 
statement. Unfortunately, I think that it's a very tough problem to 
solve.

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org

Re: Hard-coded PUBLIC in pg_dump

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T04:53:33Z

Nicolai Tufar <ntufar@apb.com.tr> writes:
> src/bin/pg_dump/pg_dump.c happen to have hard-coded PUBLIC role name.

As it should.  I think the real problem here is the hack in gram.y:

grantee:    ColId
                {
                    PrivGrantee *n = makeNode(PrivGrantee);
                    /* This hack lets us avoid reserving PUBLIC as a keyword*/
                    if (strcmp($1, "public") == 0)
                        n->username = NULL;
                    else
                        n->username = $1;
                    n->groupname = NULL;
                    $$ = (Node *)n;
                }

If the parser treated PUBLIC as an actual keyword, you'd not be having
this problem, because keywords are case-folded on an ASCII-only basis
(which is consistent with the SQL99 spec, amazingly enough).

We put in the above hack after someone complained that PUBLIC didn't use
to be a reserved word ... but considering that SQL92 clearly lists it as
a reserved word, there's not a lot of ground for that complaint to stand
on.

I'd prefer shifting PUBLIC back to the true-keyword category over any
of the other workarounds you've suggested ...

			regards, tom lane

Re: Segmentation fault while COPY in 7.3

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T04:57:26Z

"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> (gdb) bt
> #0  0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
> #1  0x080b37be in CopyTo (rel=0x402be088, attnumlist=0x828a3f8, binary=0
> '\0', oids=0 '\0',
>     fp=0x0, delim=0x81c2a57 "\t", null_print=0x81b2c22 "\\N") at copy.c:671
> #2  0x080b32f6 in DoCopy (stmt=0x827b398) at copy.c:491

Hm.  Offhand it would seem that somebody's output function is returning
a non-palloc'd string.  What are the datatypes in that table, exactly?

			regards, tom lane

Re: Segmentation fault while COPY in 7.3

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T05:09:56Z

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> 
> Hm.  Offhand it would seem that somebody's output function is returning
> a non-palloc'd string.  What are the datatypes in that table, exactly?

apb=> \d maras2.mrk_yyn
           Table "maras2.mrk_yyn"
 Column  |         Type          | Modifiers
---------+-----------------------+-----------
 bsvr_no | character varying(10) |
 yyn_tur | character varying(1)  |
 yyn_no  | numeric               |
 yyn_syf | numeric               |
 yyn_trh | date                  |

apb=>

> regards, tom lane

Thanks for attention.
Regards,
Nic

Re: Hard-coded PUBLIC in pg_dump

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T05:11:31Z

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> ... but considering that SQL92 clearly lists it as
> a reserved word, there's not a lot of ground for that complaint to stand
> on.
> 
> I'd prefer shifting PUBLIC back to the true-keyword category over any
> of the other workarounds you've suggested ...

It will work for me.
But why not change PUBLIC in pg_dump output to lower-case as well?

> 
> regards, tom lane

Nic.

Re: Segmentation fault while COPY in 7.3

Joe Conway <mail@joeconway.com> — 2002-12-01T05:32:22Z

Nicolai Tufar wrote:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>>Hm.  Offhand it would seem that somebody's output function is returning
>>a non-palloc'd string.  What are the datatypes in that table, exactly?
> 
> apb=> \d maras2.mrk_yyn
>            Table "maras2.mrk_yyn"
>  Column  |         Type          | Modifiers
> ---------+-----------------------+-----------
>  bsvr_no | character varying(10) |
>  yyn_tur | character varying(1)  |
>  yyn_no  | numeric               |
>  yyn_syf | numeric               |
>  yyn_trh | date                  |

FWIW, I just tried to repeat this on cvs tip and could not. Seems to work just 
fine:

regression=# create table foo(bsvr_no character varying(10),yyn_tur character 
varying(1),yyn_no numeric,yyn_syf numeric,yyn_trh date);
CREATE TABLE
regression=# insert into foo values('a','a','1.0','1.0',now()::date);
INSERT 556825 1
regression=# copy foo to stdout;
a       a       1.0     1.0     2002-11-30
regression=# copy foo(bsvr_no) to stdout;
a
regression=# copy foo(bsvr_no,yyn_tur,yyn_no,yyn_syf,yyn_trh) to stdout;
a       a       1.0     1.0     2002-11-30
regression=# insert into foo values('a','a','1.0','1.0',null);
INSERT 556826 1
regression=# copy foo(bsvr_no,yyn_tur,yyn_no,yyn_syf,yyn_trh) to stdout;
a       a       1.0     1.0     2002-11-30
a       a       1.0     1.0     \N


Joe

Re: 7.4 Wishlist

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T05:55:14Z

Joe Conway <mail@joeconway.com> writes:
>    - possibly enhanced connectby functionality (may not be worth it if
>      RECURSIVE JOIN functionality makes it into 7.4)

Several of my Red Hat cohorts are pretty interested in making the
RECURSIVE query stuff work for 7.4.  (The fact that they're ex-DB2
folk might explain their preference for the SQL99 syntax, nee DB2
syntax, over Oracle's CONNECT BY ... but I'm with them ;-(.  Oracle's
recursive-join syntax is nearly as bad as their outer-join syntax.)

			regards, tom lane

Re: Segmentation fault while COPY in 7.3

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T06:10:29Z

Joe Conway <mail@joeconway.com> writes:
> FWIW, I just tried to repeat this on cvs tip and could not. Seems to work just 
> fine:

I have a sneaking suspicion that it can only be reproduced with Nicolai's
locale setting.  Not sure exactly what aspect of the locale is causing
the problem though ...

			regards, tom lane

Re: Segmentation fault while COPY in 7.3

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T06:22:55Z

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>


> Joe Conway <mail@joeconway.com> writes:
> > FWIW, I just tried to repeat this on cvs tip and could not. Seems to
work just
> > fine:
>
> I have a sneaking suspicion that it can only be reproduced with Nicolai's
> locale setting.  Not sure exactly what aspect of the locale is causing
> the problem though ...

I am trying to reproduce the same on another server with no success.
Locale is what also comes to my mind. I have compiled PostgreSQL
with --with-nls option. All databases, including template0 are in
LATIN5 encoding. My postgresql.conf contains:

LC_MESSAGES = 'tr_TR'
LC_MONETARY = 'tr_TR'
LC_NUMERIC = 'tr_TR'
LC_TIME = 'tr_TR'
datestyle = 'European'


Regards,
Nic.


>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: Segmentation fault while COPY in 7.3

Joe Conway <mail@joeconway.com> — 2002-12-01T06:32:57Z

Nicolai Tufar wrote:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>>I have a sneaking suspicion that it can only be reproduced with Nicolai's
>>locale setting.  Not sure exactly what aspect of the locale is causing
>>the problem though ...
> 
> I am trying to reproduce the same on another server with no success.
> Locale is what also comes to my mind. I have compiled PostgreSQL
> with --with-nls option. All databases, including template0 are in
> LATIN5 encoding. My postgresql.conf contains:
> 
> LC_MESSAGES = 'tr_TR'
> LC_MONETARY = 'tr_TR'
> LC_NUMERIC = 'tr_TR'
> LC_TIME = 'tr_TR'
> datestyle = 'European'
> 

Here's what I have:
$ pg_config --configure
'--with-tcl' '--enable-integer-datetimes' '--enable-debug' '--enable-cassert' 
'--enable-nls' '--enable-depend' 
'DOCBOOKSTYLE=/usr/share/sgml/docbook/dsssl-stylesheets-1.76'

testl5=# select * from pg_settings where name like 'lc%' or name = 'DateStyle';
     name     |            setting
-------------+-------------------------------
  DateStyle   | ISO with European conventions
  lc_messages | tr_TR
  lc_monetary | tr_TR
  lc_numeric  | tr_TR
  lc_time     | tr_TR
(5 rows)

testl5=# \l
          List of databases
     Name    |  Owner   | Encoding
------------+----------+-----------
  parts      | postgres | SQL_ASCII
  regression | postgres | SQL_ASCII
  template0  | postgres | SQL_ASCII
  template1  | postgres | SQL_ASCII
  test       | postgres | SQL_ASCII
  testjp     | postgres | EUC_JP
  testl5     | postgres | LATIN5
(7 rows)

testl5=#  copy foo(bsvr_no,yyn_tur,yyn_no,yyn_syf,yyn_trh) to stdout;
a       a       1.0     1.0     2002-11-30

Still works for me. Could it be related to --enable-integer-datetimes?

Joe

Re: Segmentation fault while COPY in 7.3

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T06:34:32Z

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> 
> Can you extract more info?  For example, it would be useful to see
> exactly which line of the table the COPY dies at, and what's in that
> row.

COPY dies at the first row and the data is as following:

apb=>  select * from mrk_yyn  limit 3;
 bsvr_no | yyn_tur | yyn_no | yyn_syf | yyn_trh
---------+---------+--------+---------+---------
 045030  | T       |    176 |      39 |
 045033  | T       |    176 |      39 |
 045035  | T       |    176 |      39 |
(3 rows)

apb=>

yyn_trh semm to be null. I tried to COPY other tables, the ones
without date columns, I created new tables, but it still segfaults.
And I can not reproduce it on other system.

> 
> regards, tom lane
> 

Nic

Re: Segmentation fault while COPY in 7.3

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T06:50:38Z

Ohhh ...

Nicolai, are you running with a client encoding different from server
encoding?

I'm thinking that the real problem is that pg_server_to_client() can
return its input pointer as its result, meaning that CopyAttributeOut
might try to pfree() its input.

			regards, tom lane

Re: Segmentation fault while COPY in 7.3

Nicolai Tufar <ntufar@apb.com.tr> — 2002-12-01T07:32:47Z

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>


> Ohhh ...
>
> Nicolai, are you running with a client encoding different from server
> encoding?

Got it!
Gentlemen, thank you very much for assistance. The body of evidence was
slowly
growing, then, finaly Tom Lan's message have enlightened me.

It all started with initdb's warning that it can not set client's connection
to 'LATIN5'.
Okay, I said, maybe some system files are not installed. Then I tried to
create a database
with ENCODING='LATIN5'. I did not pay much attention either since my
template1 and
template0 are in LATIN5. Then on Tom's suggestion I tried to change client
encoding:

aaa=# \encoding
LATIN5
aaa=# \encoding SQL_ASCII
SQL_ASCII: invalid encoding name or conversion procedure not found
aaa=# \encoding LATIN5
LATIN5: invalid encoding name or conversion procedure not found
aaa=# \encoding SQL_ASCii
aaa=# \encoding
SQL_ASCII
aaa=# \encoding LATiN5
aaa=# \encoding
LATIN5
aaa=#

So it all falls back to Turkish "I" problem I mentioned earlier. To
perform COPY successfully I had to set client locale to 'LATiN5'
(note quotes and lower-case "i"). Any other combinations result
in segmentation fault.

Now, would it be right to change locale name handling to use ASCII-only
lower and upper-case conversions?

Many thanks to Tom Lane and Joe Conway.


I've got to get some sleep :-)
Regards,
Nic.

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-01T10:33:19Z

On 30 Nov 2002, Neil Conway wrote:

> On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote:
> > check constraints with subselects.
>
> Have we decided how this would even work? Last I heard, Tom still had
> some major reservations about the practicality of implementing these --
> for example, would you re-evaluate all constraints that SELECT from a
> table when the table changes?

You'd have to either do it in all cases or come up with something that was
smart enough to limit the cases to some extent based on the expression. I
doubt that it'd perform terribly well, especially at first.  I can't see
any justification for doing it as insert/update on main table only since
by my reading of the spec the constraint is logically checked at the end
of each statement (or transaction) even if we would normally not do so in
practice when we know the constraint shouldn't be violated.

Of course this was in the general set of, if I had months and months and
nothing else to do (like work) then I'd want to look at it because I think
it'd be useful.

Re: 7.4 Wishlist

Florian Weimer <weimer@cert.uni-stuttgart.de> — 2002-12-01T12:50:25Z

"Matthew T. O'Connor" <matthew@zeut.net> writes:

> pg_dump, our upgrade process is painful enough having to do a dump,
> reload.  I think we should be able to guarantee (or at least let
> much closer to it) that the process works in all cases.

I would already be happy if pg_dump backed up my databases correctly,
so that I dont have to reorder SQL statements manually in the dump
before psql can execute it.

-- 
Florian Weimer 	                  Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          fax +49-711-685-5898

Re: 7.4 Wishlist

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T16:24:18Z

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On 30 Nov 2002, Neil Conway wrote:
>> Have we decided how this would even work? Last I heard, Tom still had
>> some major reservations about the practicality of implementing these --
>> for example, would you re-evaluate all constraints that SELECT from a
>> table when the table changes?

> You'd have to either do it in all cases or come up with something that was
> smart enough to limit the cases to some extent based on the expression. I
> doubt that it'd perform terribly well, especially at first.

Note that you can get the "stupid" semantics (run the subselect only
when the constrained table changes) today: just hide the subselect in
a user-defined function that's called from the constraint expression.
Or put the whole check in a trigger instead of using a constraint.

I don't think we should bother with direct support of subselects in
constraints unless we can come up with an implementation that is
significantly better than what you can accomplish with these
workarounds.

			regards, tom lane

Re: Segmentation fault while COPY in 7.3

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T17:38:06Z

"Nicolai Tufar" <ntufar@apb.com.tr> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> Ohhh ...
>> 
>> Nicolai, are you running with a client encoding different from server
>> encoding?

> Got it!

Okay, the problem is a double free of memory in COPY OUT.  I've applied
the following patch for 7.3.1.  (A better fix would be for COPY OUT to
run in a local memory context that it could reset every so often, like
once per row, and then we could remove these risky retail pfree's entirely.
I plan to do that instead in development sources.)

			regards, tom lane

*** src/backend/commands/copy.c.orig	Fri Oct 18 20:25:36 2002
--- src/backend/commands/copy.c	Sun Dec  1 12:28:24 2002
***************
*** 1470,1478 ****
  	char	   *string;
  	char		c;
  	char		delimc = delim[0];
- 
  	bool		same_encoding;
! 	char	   *string_start;
  	int			mblen;
  	int			i;
  
--- 1470,1477 ----
  	char	   *string;
  	char		c;
  	char		delimc = delim[0];
  	bool		same_encoding;
! 	char	   *string_start = NULL;
  	int			mblen;
  	int			i;
  
***************
*** 1481,1492 ****
  	{
  		string = (char *) pg_server_to_client((unsigned char *) server_string,
  											  strlen(server_string));
! 		string_start = string;
  	}
  	else
  	{
  		string = server_string;
- 		string_start = NULL;
  	}
  
  	for (; (c = *string) != '\0'; string += mblen)
--- 1480,1491 ----
  	{
  		string = (char *) pg_server_to_client((unsigned char *) server_string,
  											  strlen(server_string));
! 		if (string != server_string)
! 			string_start = string;
  	}
  	else
  	{
  		string = server_string;
  	}
  
  	for (; (c = *string) != '\0'; string += mblen)

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-01T17:53:55Z

On Sun, 1 Dec 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On 30 Nov 2002, Neil Conway wrote:
> >> Have we decided how this would even work? Last I heard, Tom still had
> >> some major reservations about the practicality of implementing these --
> >> for example, would you re-evaluate all constraints that SELECT from a
> >> table when the table changes?
>
> > You'd have to either do it in all cases or come up with something that was
> > smart enough to limit the cases to some extent based on the expression. I
> > doubt that it'd perform terribly well, especially at first.
>
> Note that you can get the "stupid" semantics (run the subselect only
> when the constrained table changes) today: just hide the subselect in
> a user-defined function that's called from the constraint expression.
> Or put the whole check in a trigger instead of using a constraint.
>
> I don't think we should bother with direct support of subselects in
> constraints unless we can come up with an implementation that is
> significantly better than what you can accomplish with these
> workarounds.

Well, the problem is that user defined triggers trying to do the real
semantics for update/insert on the "other" tables of the constraint seem
to me like they'll have the same issues as foreign keys do currently,
either you'll be forced to write something too strong and deadlock alot,
or you'll write something too weak and end up with constraint violations
with concurrent transactions unless you basically write a very low level C
function to do it for you.  I guess this, since in general, the non-action
foreign keys really are just check constraints with a subselect
effectively.

Re: Hard-coded PUBLIC in pg_dump

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-01T18:44:06Z

Tom Lane wrote:
> If the parser treated PUBLIC as an actual keyword, you'd not be having
> this problem, because keywords are case-folded on an ASCII-only basis
> (which is consistent with the SQL99 spec, amazingly enough).
> 
> We put in the above hack after someone complained that PUBLIC didn't use
> to be a reserved word ... but considering that SQL92 clearly lists it as
> a reserved word, there's not a lot of ground for that complaint to stand
> on.
> 
> I'd prefer shifting PUBLIC back to the true-keyword category over any
> of the other workarounds you've suggested ...

PUBLIC doesn't seem like a very common column name --- seems safe to
make it reserved.  We made 'value' reserved in 7.3, and that was a much
more common one.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Hard-coded PUBLIC in pg_dump

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T18:49:30Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> PUBLIC doesn't seem like a very common column name --- seems safe to
> make it reserved.  We made 'value' reserved in 7.3, and that was a much
> more common one.

I'm still quite unhappy about 'value', and would like to look into
making it unreserved again.  This business does show that there are some
pitfalls in that, though :-(

			regards, tom lane

Re: Hard-coded PUBLIC in pg_dump

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-01T18:51:26Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> We made 'value' reserved in 7.3, and that was a much
> more common one.

BTW, you mean "current" not "7.3".  That patch has still got some
serious problems anyway:

7.3:

regression=# select value;
ERROR:  Attribute "value" not found

HEAD:

regression=# select value;
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.

			regards, tom lane

Re: Hard-coded PUBLIC in pg_dump

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-01T18:52:58Z

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > We made 'value' reserved in 7.3, and that was a much
> > more common one.
> 
> BTW, you mean "current" not "7.3".  That patch has still got some
> serious problems anyway:

Yes, I realized later it was current. I was fixing the dbdpg regression
tests, and git bitten by that, and forgot I was using current and not
7.3.

> 
> 7.3:
> 
> regression=# select value;
> ERROR:  Attribute "value" not found
> 
> HEAD:
> 
> regression=# select value;
> 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.

Yow!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Hard-coded PUBLIC in pg_dump

David Wheeler <david@wheeler.net> — 2002-12-01T18:54:15Z

On Sunday, December 1, 2002, at 10:49  AM, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> PUBLIC doesn't seem like a very common column name --- seems safe to
>> make it reserved.  We made 'value' reserved in 7.3, and that was a 
>> much
>> more common one.
>
> I'm still quite unhappy about 'value', and would like to look into
> making it unreserved again.  This business does show that there are 
> some
> pitfalls in that, though :-(

Actually, I don't think it's reserved in 7.3, only in the 7.4 
development sources. Otherwise, Bricolage would fail hard, and it 
doesn't. So there's some time to play with this issue, I think.

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org

Re: Hard-coded PUBLIC in pg_dump

Rod Taylor <rbt@rbt.ca> — 2002-12-01T19:04:21Z

> > regression=# select value;
> > ERROR:  Attribute "value" not found
> > 
> > HEAD:
> > 
> > regression=# select value;
> > 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.
> 
> Yow!

I believe these are fixed in the patch I sent in last week.

-- 
Rod Taylor <rbt@rbt.ca>

Re: Hard-coded PUBLIC in pg_dump

Rod Taylor <rbt@rbt.ca> — 2002-12-01T19:04:22Z

> > regression=# select value;
> > ERROR:  Attribute "value" not found
> > 
> > HEAD:
> > 
> > regression=# select value;
> > 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.
> 
> Yow!

I believe these are fixed in the patch I sent in last week.

-- 
Rod Taylor <rbt@rbt.ca>

Re: 7.4 Wishlist

Joe Conway <mail@joeconway.com> — 2002-12-02T03:36:45Z

David Wheeler wrote:
> My understanding is that the nul character is legal in a byte sequence, 
> but if it's not properly escaped, it'll be parsed as the end of the 
> statement. Unfortunately, I think that it's a very tough problem to solve.

No question wrt '\0' bytes -- they would have to be escaped when casting from 
bytea to text.

The harder issue is that there are apparently many other multiple byte 
sequences that, while valid in an ASCII encoding, are not valid in one or more 
multibyte encodings. See this thread:

http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php

This is why currently all "non printable characters" are escaped (which I 
think is all bytes > 127). Text on the other hand is already known to be valid 
for a particular encoding, so it doesn't need escaping.

I'm not sure what happens when the backend encoding and client encoding don't 
match -- I'd guess there is some probability of invalid byte sequences in that 
case too.

Joe

7.4 Wishlist

Lee Kindness <lkindness@csl.co.uk> — 2002-12-02T10:54:10Z

Christopher Kings-Lynne writes:
 > Just out of interest, if someone was going to pay you to hack on Postgres
 > for 6 months, what would you like to code for 7.4?

Well judging by the hoards on Slashdot, it would appear that
replication is the hot enhancement...

 Slashdot | PostgreSQL 7.3 Released
 http://developers.slashdot.org/article.pl?sid=02/11/30/1815200

Lee.

Re: 7.4 Wishlist

Karel Zak <zakkr@zf.jcu.cz> — 2002-12-02T11:26:12Z

On Fri, Nov 29, 2002 at 10:51:26AM -0800, Christopher Kings-Lynne wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

 This is interesting discussion.. 
 
 my wish:
 
  * error codes. It's very interesting that nobody other wants it...


    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Re: 7.4 Wishlist

Prasanna Phadke <pmp_pg@yahoo.co.in> — 2002-12-02T13:00:34Z

How about giving OLAP (Dimension / Measure) functionality to PG.

Catch all the cricket action. Download Yahoo! Score tracker

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-02T18:09:46Z

Joe Conway wrote:
> David Wheeler wrote:
> > My understanding is that the nul character is legal in a byte sequence, 
> > but if it's not properly escaped, it'll be parsed as the end of the 
> > statement. Unfortunately, I think that it's a very tough problem to solve.
> 
> No question wrt '\0' bytes -- they would have to be escaped when casting from 
> bytea to text.
> 
> The harder issue is that there are apparently many other multiple byte 
> sequences that, while valid in an ASCII encoding, are not valid in one or more 
> multibyte encodings. See this thread:
> 
> http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php
> 
> This is why currently all "non printable characters" are escaped (which I 
> think is all bytes > 127). Text on the other hand is already known to be valid 
> for a particular encoding, so it doesn't need escaping.
> 
> I'm not sure what happens when the backend encoding and client encoding don't 
> match -- I'd guess there is some probability of invalid byte sequences in that 
> case too.

I think there is some idea of changing the frontend/backend protocol to
prevent the need for escaping > \127 characters.  I believe it is
currently only required when the frontend/backend protocol have
different encodings.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-02T19:46:39Z

Mysql is planning on making this work:

 SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.

Do we have anything like it (After a discussion with Tom i figure no).
User variables is nice, especially in these kind of queries.

Nice would be to be able to use them as in C (almost everywhere):

SELECT id, @x FROM table_name t where (@x := date_part('days'. now() -
f.created)) > 100;

As Tom said in earlier mail, it might not be that big of a win in this
case, but if uses expensive functions, it will.

Magnus

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-02T20:25:34Z

> Mysql is planning on making this work:
> 
>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
> 
> Do we have anything like it (After a discussion with Tom i figure no).
> User variables is nice, especially in these kind of queries.

Well of course they have to make that work - they don't have subselects :P

Chris

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-02T20:34:20Z

Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>> Mysql is planning on making this work:
>>
>>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY
>> id.
>>
>> Do we have anything like it (After a discussion with Tom i figure
>> no). User variables is nice, especially in these kind of queries.
>
> Well of course they have to make that work - they don't have
> subselects :P
>
> Chris

Yeah, but there is a point about running count(*) one time too many.
Say if i would like to get a prettyprinting query like this:

SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;

That would be DAMN expensive doing with a subselect:

SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
table_name;

I know this example suck eggs, but you get the point where it hurts,
right?

Magnus - sorry for the dupe, chris

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-02T20:48:38Z

> Yeah, but there is a point about running count(*) one time too many.
> Say if i would like to get a prettyprinting query like this:
>
> SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;
>
> That would be DAMN expensive doing with a subselect:
>
> SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
> table_name;
>
> I know this example suck eggs, but you get the point where it hurts,
> right?

Are you sure that postgres evaluates that subselect more than once?  It
looks to me like it returns a constant result for every row and hence it
will be evaluated once per statement, not once per row.  I'm no expert tho.
Can someone answer this?

And if the subselect changes for each row (ie. it's a correlated subquery)
then you cannot use the variable anyway.

It seems to me that if postgres doesn't consider count(*) as a constant then
perhaps it should be taught to?  Should be safe shouldn't it?  I guess if a
function in your select statemnt is inserting a row then there's trouble.
But if there is, then the sum/count(*) is nonsensical anyway.

Chris

Re: Segmentation fault while COPY in 7.3

Medi Montaseri <medi.montaseri@intransa.com> — 2002-12-02T21:02:30Z

Something is funny about this pfree()....here is a similar crash on a 7.2.3

(gdb)                            
#0  0x00420874 in heap_freetuple ()
#1  0x004a8390 in acquire_sample_rows ()
#2  0x004a75c8 in analyze_rel ()
#3  0x0049f690 in vacuum ()
#4  0x005585d8 in ProcessUtility ()
#5  0x00553c78 in pg_exec_query_string ()
#6  0x00555850 in PostgresMain ()
#7  0x00524660 in DoBackend ()
#8  0x00523d08 in BackendStartup ()
#9  0x00521c18 in ServerLoop ()
#10 0x005211c8 in PostmasterMain ()
#11 0x004df3d8 in main ()
#12 0x2ae34928 in __libc_start_main () from /lib/libc.so.6
(gdb) 



Nicolai Tufar wrote:

>Help!
>Backend crashes on any COPY command I issue:
>PostgreSQL is 7.3 release, is compiled from sources.
>The message I get is:
>
>apb=# COPY maras2.mrk_yyn (bsvr_no, yyn_tur, yyn_no, yyn_syf, yyn_trh) TO
>stdout;
>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.
>!#
>
>
>The output of strace on backend is following:
>
># strace -p 18883
>recv(7, "QCOPY maras2.mrk_yyn (bsvr_no, y"..., 8192, 0) = 77
>gettimeofday({1038713111, 629131}, NULL) = 0
>_llseek(11, 0, [0], SEEK_SET)           = 0
>read(11, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\37\360\37\1 b1\5\0\1"..., 8192)
>= 8192
>read(11, "\0\0\0\0x#\375\"\20\0\0\0008\0h\35\360\37\1 \370\235\220"...,
>8192) = 8192
>_llseek(12, 0, [0], SEEK_SET)           = 0
>read(12, "\0\0\0\0\0#\375\"\20\0\0\0008\0@\34\0 \1 \200\237\0\1\0"..., 8192)
>= 8192
>_llseek(21, 40960, [40960], SEEK_SET)   = 0
>read(21, "\0\0\0\0\4\251\3#\20\0\0\0004\4p\17\360\37\1 \340\237 "..., 8192)
>= 8192
>_llseek(22, 106496, [106496], SEEK_SET) = 0
>read(22, "\0\0\0\0(\326\377\"\20\0\0\0\10\1\200\1\0 \1 \200\237\0"..., 8192)
>= 8192
>read(17, "\0\0\0\0\20\0\0\0\1\0\0\0\360\0\20\1\0 \1 p\237 \1\340"..., 8192)
>= 8192
>open("/data/pgsql/base/2015749/2015757", O_RDWR|O_LARGEFILE) = 39
>_llseek(39, 0, [20037632], SEEK_END)    = 0
>_llseek(39, 0, [0], SEEK_SET)           = 0
>read(39, "\0\0\0\0\270\7\4#\20\0\0\0\300\1\350\1\0 \1 \270\237\216"...,
>8192) = 8192
>--- SIGSEGV (Segmentation fault) ---
>+++ killed by SIGSEGV +++
>#
>
>I have recompiled postgres with debug information enabled, run gdb, attached
>to backend process
>and caught SIGSEGV as following:
>
>(gdb) continue
>Continuing.
>
>Program received signal SIGSEGV, Segmentation fault.
>0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
>480             (*header->context->methods->free_p) (header->context,
>pointer);
>(gdb)
>
>
>The output of bt is:
>
>(gdb) bt
>#0  0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
>#1  0x080b37be in CopyTo (rel=0x402be088, attnumlist=0x828a3f8, binary=0
>'\0', oids=0 '\0',
>    fp=0x0, delim=0x81c2a57 "\t", null_print=0x81b2c22 "\\N") at copy.c:671
>#2  0x080b32f6 in DoCopy (stmt=0x827b398) at copy.c:491
>#3  0x08118a6a in pg_exec_query_string (query_string=0x827af48, dest=Remote,
>    parse_context=0x8245378) at postgres.c:789
>#4  0x08119b89 in PostgresMain (argc=4, argv=0xbfffd2a0, username=0x8240a81
>"postgres")
>    at postgres.c:2016
>#5  0x08101d7c in DoBackend (port=0x8240950) at postmaster.c:2293
>#6  0x081016c2 in BackendStartup (port=0x8240950) at postmaster.c:1915
>#7  0x08100875 in ServerLoop () at postmaster.c:1000
>#8  0x08100326 in PostmasterMain (argc=1, argv=0x82270c0) at
>postmaster.c:779
>#9  0x080debab in main (argc=1, argv=0xbfffdc34) at main.c:210
>#10 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6
>(gdb)
>
>
>
>Thanks in advance,
>Nic
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>  
>

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-02T21:40:49Z

On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >> Mysql is planning on making this work:
> >>
> >>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY
> >> id.
> >>
> >> Do we have anything like it (After a discussion with Tom i figure
> >> no). User variables is nice, especially in these kind of queries.
> >
> > Well of course they have to make that work - they don't have
> > subselects :P
> >
> > Chris
>
> Yeah, but there is a point about running count(*) one time too many.
> Say if i would like to get a prettyprinting query like this:
>
> SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;
>
> That would be DAMN expensive doing with a subselect:
>
> SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
> table_name;
>
> I know this example suck eggs, but you get the point where it hurts,
> right?


If you use a scalar subquery, yes, but I think a subselect in from would
help, maybe something like (if you want the total count)

select table_name.id, sum(sum_col)||'/'||t2.count from table_name, (select
count(*) as count from table_name) as t2 group by table_name.id,t2.count;

or (if you want each count the counter per group) either

 select id, sum(sum_col)||'/'||count(*) from table_name
group by id;

or

 select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
(select id, count(*) as count from table_name group by id) as t2 where
table_name.id=t2.id group by table_name.id,t2.count;

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-02T22:18:13Z

Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Are you sure that postgres evaluates that subselect more than once?
> It looks to me like it returns a constant result for every row and
> hence it will be evaluated once per statement, not once per row.  I'm
> no expert tho. Can someone answer this?
>
> And if the subselect changes for each row (ie. it's a correlated
> subquery) then you cannot use the variable anyway.
>
> It seems to me that if postgres doesn't consider count(*) as a
> constant then perhaps it should be taught to?  Should be safe
> shouldn't it?  I guess if a function in your select statemnt is
> inserting a row then there's trouble. But if there is, then the
> sum/count(*) is nonsensical anyway.
>
> Chris
>

It looks like it (7.2.x):

# time psql genline -c "select id from xxxx" > /dev/null
real    0m0.694s
user    0m0.147s
sys     0m0.025s
# time psql genline -c "select id,id||'/'||(select count(*) from xxxx)
as x from xxxx" > /dev/null

real    0m2.202s
user    0m0.263s
sys     0m0.040s

# time psql genline -c "select id,(select count(*) from bildsekvens) as
x from xxxx" > /dev/null

real    0m1.479s
user    0m0.254s
sys     0m0.047s

They were taken from a busy system, but i ran the several times showing
about the same result.

Magnus

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-02T22:22:30Z

Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
>
> If you use a scalar subquery, yes, but I think a subselect in from
> would help, maybe something like (if you want the total count)
>
> select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> (select count(*) as count from table_name) as t2 group by
> table_name.id,t2.count;
>
> or (if you want each count the counter per group) either
>
>  select id, sum(sum_col)||'/'||count(*) from table_name
> group by id;
>
> or
>
>  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> (select id, count(*) as count from table_name group by id) as t2 where
> table_name.id=t2.id group by table_name.id,t2.count;
>

Give it up already, i was MAKING A POINT, not trying to make an
optimized count(*) thing :)
There are other examples that you cannot get around, that will be
evaluated more than once when a local "user variable" would make it not
need to.

Magnus

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-02T22:51:20Z

On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > If you use a scalar subquery, yes, but I think a subselect in from
> > would help, maybe something like (if you want the total count)
> >
> > select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select count(*) as count from table_name) as t2 group by
> > table_name.id,t2.count;
> >
> > or (if you want each count the counter per group) either
> >
> >  select id, sum(sum_col)||'/'||count(*) from table_name
> > group by id;
> >
> > or
> >
> >  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select id, count(*) as count from table_name group by id) as t2 where
> > table_name.id=t2.id group by table_name.id,t2.count;
> >
>
> Give it up already, i was MAKING A POINT, not trying to make an
> optimized count(*) thing :)
> There are other examples that you cannot get around, that will be
> evaluated more than once when a local "user variable" would make it not
> need to.

For most cases sticking an expression in a subselect in from works to not
re-evaluate it (unless it's correlated in which case I don't think local
variables help).  It might not be as optimal in all cases, but probably is
sufficient in most cases.

Re: 7.4 Wishlist

Hannu Krosing <hannu@tm.ee> — 2002-12-02T23:29:24Z

Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18:
> It looks like it (7.2.x):
> 
> # time psql genline -c "select id from xxxx" > /dev/null
> real    0m0.694s
> user    0m0.147s
> sys     0m0.025s
> # time psql genline -c "select id,id||'/'||(select count(*) from xxxx)
> as x from xxxx" > /dev/null
> 
> real    0m2.202s
> user    0m0.263s
> sys     0m0.040s
> 
> # time psql genline -c "select id,(select count(*) from bildsekvens) as
> x from xxxx" > /dev/null
> 
> real    0m1.479s
> user    0m0.254s
> sys     0m0.047s

what is the time for

select id,x
  from xxxx,
       (select count(*) as x from bildsekvens) c ;



> They were taken from a busy system, but i ran the several times showing
> about the same result.
> 
> Magnus
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-- 
Hannu Krosing <hannu@tm.ee>

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-02T23:39:23Z

Hannu Krosing <hannu@tm.ee> wrote:
> Magnus Naeslund(f) kirjutas T, 03.12.2002 kell 03:18:
>> It looks like it (7.2.x):
>>
>> # time psql genline -c "select id from xxxx" > /dev/null
>> real    0m0.694s
>> user    0m0.147s
>> sys     0m0.025s
>> # time psql genline -c "select id,id||'/'||(select count(*) from
>> xxxx) as x from xxxx" > /dev/null
>>
>> real    0m2.202s
>> user    0m0.263s
>> sys     0m0.040s
>>
>> # time psql genline -c "select id,(select count(*) from bildsekvens)
>> as x from xxxx" > /dev/null
>>
>> real    0m1.479s
>> user    0m0.254s
>> sys     0m0.047s
>
> what is the time for
>
> select id,x
>   from xxxx,
>        (select count(*) as x from bildsekvens) c ;
>
>
>

time psql genline -c "select id,x from xxxx, (select count(*) as x from
xxxx) c ;" > /dev/null

real    0m1.354s
user    0m0.268s
sys     0m0.028s

The xxxx and the other table is the same table, sorry i screwed up in
the last cut'n'paste operation.
As i said it's a loaded system, the figures vary a little bit between
runs.

Magnus

Re: 7.4 Wishlist

Gavin Sherry <swm@linuxworld.com.au> — 2002-12-03T00:10:56Z

I want to see:

i) proper resource management a-la Oracle. This would allow a DBA to
limited the amount of time any given user spends in the parser, planner or
executor. It would be limited with a more sophisticated user system,
including things like CREATE USER PROFILE ...

ii) Auditing. Who accessed what, how, when and for how long with the
ability to store queries, planner stats, executor stats and what ever else
we can tie to a query. Very useful for debugging and security. You can get
this from the logs but it is non trivial to perform adhoc reporting and
statistical analysis. Why not store it.. in a database? :-)

iii) SQL99 error code

iv) Updatable and insertable-into views (per SQL99). Perhaps a two stage
implementation: i) rules ii) have the planner/executor handle it, instead
of the rewriter. The latter will take more coding, and might touch too
much of the code, considering the other significant changes planned for
7.4.

v) Better PL/PgSQL parser and memory handling

vi) A larger number of case studies on the advocacy site, with a larger
degree of financial and ROI analysis, all that jazz

vii) Collections of information about migrating from other significant
platforms: oracle, db2, sybase, interbase, SQL server -- a very popular
request on irc.openprojects.net

viii) General advocacy, particularly in pushing mainstream IT media
coverage, conferences and university usage -- both for teaching SQL and
for teach database engineering concepts for senior undergrads.

I've no idea how much time I can put into these, but they're on my TODO
list.

Gavin

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-03T00:50:20Z

> i) proper resource management a-la Oracle. This would allow a DBA to
> limited the amount of time any given user spends in the parser, planner or
> executor. It would be limited with a more sophisticated user system,
> including things like CREATE USER PROFILE ...

Hehehe - yeah this would be neat.  Would be somewhat better than MySQL's
MAX_QUERIES_PER_HOUR setting :P

> vii) Collections of information about migrating from other significant
> platforms: oracle, db2, sybase, interbase, SQL server -- a very popular
> request on irc.openprojects.net

There's lots of good information on this on techdocs.postgresql.org.  BTW,
what happened to the PostgreSQL portal site that someone was working on?
It'd be very interested in taking that on...

> viii) General advocacy, particularly in pushing mainstream IT media
> coverage, conferences and university usage -- both for teaching SQL and
> for teach database engineering concepts for senior undergrads.

Definitely.  How about a resource for college/uni professors on how to use
PostgreSQL in their courses?

Chris

Re: 7.4 Wishlist

Gavin Sherry <swm@linuxworld.com.au> — 2002-12-03T01:28:40Z

On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:

> > viii) General advocacy, particularly in pushing mainstream IT media
> > coverage, conferences and university usage -- both for teaching SQL and
> > for teach database engineering concepts for senior undergrads.
> 
> Definitely.  How about a resource for college/uni professors on how to use
> PostgreSQL in their courses?

I might get together with some of the lecturers I've worked with in
Sydney to give such a document some weight. I must say, the problem is not
a technical one though. I've given talks to 3rd and 4th year students
about PostgreSQL -- technical, conceptual, political talks... you name
it. Out of 200 odd students, only about 5-10 actually seem interested. Its
terrible.

Why aren't they interested? They think that if they study Oracle
(instead) for 6 months they'll walk straight into a job with an extremely
high salary. Its a myth, but I cannot shake that perception.

In fact, things got very heated when two universities in Sydney moved
their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will
be down next year for the courses and Australian universities are heavily
geared toward bums on seats not facilitation of education.

Gavin

Re: 7.4 Wishlist

Mike Mascari <mascarm@mascari.com> — 2002-12-03T01:51:33Z

Gavin Sherry wrote:
> I want to see:
> 
> i) proper resource management a-la Oracle. This would allow a DBA to
> limited the amount of time any given user spends in the parser, planner or
> executor. It would be limited with a more sophisticated user system,
> including things like CREATE USER PROFILE ...

Amen:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3DB99C0A.70900%40mascari.com&rnum=1&prev=/groups%3Fq%3DCREATE%2BPROFILE%2BMike%2BMascari%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8

To avoid unnecessary cycles being spent on loading the profile 
after session authorization, we could have a GUC as was 
suggested to turn the feature on or off. This mirrors Oracle, 
where you have to set RESOURCE_LIMIT in your init[SID].ora file 
before PROFILEs are enforced. Some people like sticking 
everything in postgresql.conf though, including resource limits. 
I'm not sure how remote administration is supposed to work under 
such a scenario though...

Mike Mascari
mascarm@mascari.com

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-03T02:16:19Z

> I might get together with some of the lecturers I've worked with in
> Sydney to give such a document some weight. I must say, the problem is not
> a technical one though. I've given talks to 3rd and 4th year students
> about PostgreSQL -- technical, conceptual, political talks... you name
> it. Out of 200 odd students, only about 5-10 actually seem interested. Its
> terrible.

I've given a talk in the 2002 honours lecture series at UWA about Postgres
and some of the things it can do.  All of those guys were interested.
Especially since the deptartment does a lot of work in genetic algoriithms.

Tell me when you start working on a document - I'm happy to help.  Since I'm
only just out of Uni, I'd like to write a set of possible assignments and
learning outcomes and how you can use postgres to support them.

My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
scholarship there a year or two back, so I can get interest from the dept,
including the databases lecturer.  Might help for another point of view and
feedback.

> Why aren't they interested? They think that if they study Oracle
> (instead) for 6 months they'll walk straight into a job with an extremely
> high salary. Its a myth, but I cannot shake that perception.

That's tragic.  Teaching kids to admin oracle is something you do in Tafe,
or an Oracle course, not a university.  Anyway, what kind of course teaches
you about how to admin oracle as opposed to teaching you about ACID
properties, MVCC, distributed transactions and partitioning?  Most of which
can be demonstrated with Postgres.  We learnt about relational model,
algebra and calculus well before learning about SQL!

Hell, my Uni (UWA) actually uses MS Access for crying out loud!  We learn
heaps of theory for 'real' databases (as above), but then our semester
project is to implement in MS Access a bunch of tables and queries for a UN
aid mission, for example.  Not once do you have to use SQL - you just use
the query builder.  How lame!

I have friends who have worked with people who've gone thru the oracle
course.  They say it's frustrating because they only understand what they've
been told to understand and have a lack of knowledge about basic, database
principles.

> In fact, things got very heated when two universities in Sydney moved
> their SQL courses from Oracle and Sybase to PostgreSQL. Enrollments will
> be down next year for the courses and Australian universities are heavily
> geared toward bums on seats not facilitation of education.

Universities are supposed to have a tradition of open source support.  Just
imagine if the professors could not only teach about how to do SQL, but ALSO
teach kids how a parser and executor and rewriter work by looking at the
actual source code!

Imagine those kids who go on to do honours, masters and PHD's in database
theory, indexing or whatever who could end up contributing to Postgres? ;)

What a sell!  (For a real uni, that is ;) )

Chris

PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Gavin Sherry <swm@linuxworld.com.au> — 2002-12-03T02:40:13Z

On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:

> I've given a talk in the 2002 honours lecture series at UWA about Postgres
> and some of the things it can do.  All of those guys were interested.
> Especially since the deptartment does a lot of work in genetic algoriithms.

Excellent. Can you put that talk online somewhere?

> Tell me when you start working on a document - I'm happy to help.  Since I'm
> only just out of Uni, I'd like to write a set of possible assignments and
> learning outcomes and how you can use postgres to support them.
> 
> My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
> scholarship there a year or two back, so I can get interest from the dept,
> including the databases lecturer.  Might help for another point of view and
> feedback.

Excellent. Are there any other people involved in PostgreSQL and
universities or educational institutions? If so we could put something
together about experiences for the advocacy Web site.

> That's tragic.  Teaching kids to admin oracle is something you do in Tafe,
> or an Oracle course, not a university.  Anyway, what kind of course teaches
> you about how to admin oracle as opposed to teaching you about ACID
> properties, MVCC, distributed transactions and partitioning?  Most of which
> can be demonstrated with Postgres.  We learnt about relational model,
> algebra and calculus well before learning about SQL!

Your interest in this is clearly the same as mine: Universities
(should) teach concept not product. I'm disgusted that this is not the
case.

If other people are interested we could work on this in January when I am
over your way, as discussed in private email.

Gavin

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Justin Clift <justin@postgresql.org> — 2002-12-03T03:41:27Z

Gavin Sherry wrote:
> On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:
> 
> 
>>I've given a talk in the 2002 honours lecture series at UWA about Postgres
>>and some of the things it can do.  All of those guys were interested.
>>Especially since the deptartment does a lot of work in genetic algoriithms.
> 
> 
> Excellent. Can you put that talk online somewhere?
> 
> 
>>Tell me when you start working on a document - I'm happy to help.  Since I'm
>>only just out of Uni, I'd like to write a set of possible assignments and
>>learning outcomes and how you can use postgres to support them.
>>
>>My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
>>scholarship there a year or two back, so I can get interest from the dept,
>>including the databases lecturer.  Might help for another point of view and
>>feedback.
> 
> 
> Excellent. Are there any other people involved in PostgreSQL and
> universities or educational institutions? If so we could put something
> together about experiences for the advocacy Web site.

Is this the kind of thing that the Techdocs Guides area would be good 
for?  (http://techdocs.postgresql.org/guides)

:-)

Regards and best wishes,

Justin Clift


<snip>
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T05:01:06Z

Magnus Naeslund(f) wrote:
> >  select id, sum(sum_col)||'/'||count(*) from table_name
> > group by id;
> >
> > or
> >
> >  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select id, count(*) as count from table_name group by id) as t2 where
> > table_name.id=t2.id group by table_name.id,t2.count;
> >
> 
> Give it up already, i was MAKING A POINT, not trying to make an
> optimized count(*) thing :)
> There are other examples that you cannot get around, that will be
> evaluated more than once when a local "user variable" would make it not
> need to.

Here's an even slimmer query that makes a var:
	
	test=> select var1.* from (select 1) as var1;
	 ?column? 
	----------
	        1
	(1 row)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Segmentation fault while COPY in 7.3

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T05:02:53Z

Yes, we found a double pfree in 7.3.  There will be a fix in 7.3.1.

---------------------------------------------------------------------------

Medi Montaseri wrote:
> Something is funny about this pfree()....here is a similar crash on a 7.2.3
> 
> (gdb)                            
> #0  0x00420874 in heap_freetuple ()
> #1  0x004a8390 in acquire_sample_rows ()
> #2  0x004a75c8 in analyze_rel ()
> #3  0x0049f690 in vacuum ()
> #4  0x005585d8 in ProcessUtility ()
> #5  0x00553c78 in pg_exec_query_string ()
> #6  0x00555850 in PostgresMain ()
> #7  0x00524660 in DoBackend ()
> #8  0x00523d08 in BackendStartup ()
> #9  0x00521c18 in ServerLoop ()
> #10 0x005211c8 in PostmasterMain ()
> #11 0x004df3d8 in main ()
> #12 0x2ae34928 in __libc_start_main () from /lib/libc.so.6
> (gdb) 
> 
> 
> 
> Nicolai Tufar wrote:
> 
> >Help!
> >Backend crashes on any COPY command I issue:
> >PostgreSQL is 7.3 release, is compiled from sources.
> >The message I get is:
> >
> >apb=# COPY maras2.mrk_yyn (bsvr_no, yyn_tur, yyn_no, yyn_syf, yyn_trh) TO
> >stdout;
> >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.
> >!#
> >
> >
> >The output of strace on backend is following:
> >
> ># strace -p 18883
> >recv(7, "QCOPY maras2.mrk_yyn (bsvr_no, y"..., 8192, 0) = 77
> >gettimeofday({1038713111, 629131}, NULL) = 0
> >_llseek(11, 0, [0], SEEK_SET)           = 0
> >read(11, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\37\360\37\1 b1\5\0\1"..., 8192)
> >= 8192
> >read(11, "\0\0\0\0x#\375\"\20\0\0\0008\0h\35\360\37\1 \370\235\220"...,
> >8192) = 8192
> >_llseek(12, 0, [0], SEEK_SET)           = 0
> >read(12, "\0\0\0\0\0#\375\"\20\0\0\0008\0@\34\0 \1 \200\237\0\1\0"..., 8192)
> >= 8192
> >_llseek(21, 40960, [40960], SEEK_SET)   = 0
> >read(21, "\0\0\0\0\4\251\3#\20\0\0\0004\4p\17\360\37\1 \340\237 "..., 8192)
> >= 8192
> >_llseek(22, 106496, [106496], SEEK_SET) = 0
> >read(22, "\0\0\0\0(\326\377\"\20\0\0\0\10\1\200\1\0 \1 \200\237\0"..., 8192)
> >= 8192
> >read(17, "\0\0\0\0\20\0\0\0\1\0\0\0\360\0\20\1\0 \1 p\237 \1\340"..., 8192)
> >= 8192
> >open("/data/pgsql/base/2015749/2015757", O_RDWR|O_LARGEFILE) = 39
> >_llseek(39, 0, [20037632], SEEK_END)    = 0
> >_llseek(39, 0, [0], SEEK_SET)           = 0
> >read(39, "\0\0\0\0\270\7\4#\20\0\0\0\300\1\350\1\0 \1 \270\237\216"...,
> >8192) = 8192
> >--- SIGSEGV (Segmentation fault) ---
> >+++ killed by SIGSEGV +++
> >#
> >
> >I have recompiled postgres with debug information enabled, run gdb, attached
> >to backend process
> >and caught SIGSEGV as following:
> >
> >(gdb) continue
> >Continuing.
> >
> >Program received signal SIGSEGV, Segmentation fault.
> >0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
> >480             (*header->context->methods->free_p) (header->context,
> >pointer);
> >(gdb)
> >
> >
> >The output of bt is:
> >
> >(gdb) bt
> >#0  0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
> >#1  0x080b37be in CopyTo (rel=0x402be088, attnumlist=0x828a3f8, binary=0
> >'\0', oids=0 '\0',
> >    fp=0x0, delim=0x81c2a57 "\t", null_print=0x81b2c22 "\\N") at copy.c:671
> >#2  0x080b32f6 in DoCopy (stmt=0x827b398) at copy.c:491
> >#3  0x08118a6a in pg_exec_query_string (query_string=0x827af48, dest=Remote,
> >    parse_context=0x8245378) at postgres.c:789
> >#4  0x08119b89 in PostgresMain (argc=4, argv=0xbfffd2a0, username=0x8240a81
> >"postgres")
> >    at postgres.c:2016
> >#5  0x08101d7c in DoBackend (port=0x8240950) at postmaster.c:2293
> >#6  0x081016c2 in BackendStartup (port=0x8240950) at postmaster.c:1915
> >#7  0x08100875 in ServerLoop () at postmaster.c:1000
> >#8  0x08100326 in PostmasterMain (argc=1, argv=0x82270c0) at
> >postmaster.c:779
> >#9  0x080debab in main (argc=1, argv=0xbfffdc34) at main.c:210
> >#10 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6
> >(gdb)
> >
> >
> >
> >Thanks in advance,
> >Nic
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >  
> >
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-03T05:14:13Z

Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Magnus Naeslund(f) wrote:
>>>  select id, sum(sum_col)||'/'||count(*) from table_name
>>> group by id;
>>>
>>> or
>>>
>>>  select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
>>> (select id, count(*) as count from table_name group by id) as t2
>>> where table_name.id=t2.id group by table_name.id,t2.count;
>>>
>>
>> Give it up already, i was MAKING A POINT, not trying to make an
>> optimized count(*) thing :)
>> There are other examples that you cannot get around, that will be
>> evaluated more than once when a local "user variable" would make it
>> not need to.
>
> Here's an even slimmer query that makes a var:
>
> test=> select var1.* from (select 1) as var1;
> ?column?
> ----------
>         1
> (1 row)

Good!
Now convert this query so that it only evaluates the date_part thing
ONCE:

select t.id, date_part('days',now()-t.stamp) from table_name t where
date_part('days',now()-t.stamp) > 20;

I hope you all are kidding me in not seeing the real issue i'm trying to
show here.

Cheers
Magnus

Re: 7.4 Wishlist

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-03T05:20:17Z

> Good!
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
>
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;

Yes, it's a bit of a bugger that one.

> I hope you all are kidding me in not seeing the real issue i'm trying to
> show here.

I see, yes.  I guess the MySQL idea is to make it explicit to the parser
which parts of the expression are constant and equivalent.

Still, if the parser was smart enough, it could handle the above example
automatically.  However, I think it would be an O(n^2) problem to solve -
matching all subexpressions against all other subexpressions to find the
ones that match...so the MySQL idea is a valid one.

One trick that many people don't know is that you can do it fast in the
GROUP BY clause, just not the WHERE clause:

select t.id, date_part('days',now()-t.stamp) from table_name t group by 2;

Which is something I just discovered recently.

Chris

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T05:27:03Z

Magnus Naeslund(f) wrote:
> Good!
> Now convert this query so that it only evaluates the date_part thing
> ONCE:
> 
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;
> 
> I hope you all are kidding me in not seeing the real issue i'm trying to
> show here.
> 
> Cheers
> Magnus
> 
> 
> 

Does this work?

	SELECT t.id, x.date_part 
	FROM table_name t, (select date_part('days',now()-t.stamp)) as x
	WHERE x.date_part > 20;

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-03T05:28:39Z

On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:

> Now convert this query so that it only evaluates the date_part thing
> ONCE:

That's not a good idea as long as t.stamp varies from row to row. ;)
Perhaps once per row, maybe... :)

> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;

Potentially I think something like this would do it:
select t.id, t.foo from (select id, date_part('days', now()-stamp)
as foo from table_name except select null, null) as t where foo>20;

It's not really an optimization given the required except, but if there
was some way to tell the system not to push clauses down into a subselect
you wouldn't even need that.

Re: 7.4 Wishlist

Magnus Naeslund(f) <mag@fbab.net> — 2002-12-03T05:31:52Z

Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> 
>> Now convert this query so that it only evaluates the date_part thing
>> ONCE:
> 
> That's not a good idea as long as t.stamp varies from row to row. ;)
> Perhaps once per row, maybe... :)
> 

I give up

Re: 7.4 Wishlist

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-12-03T05:32:49Z

On Tue, 3 Dec 2002, Bruce Momjian wrote:

> Magnus Naeslund(f) wrote:
> > Good!
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> >
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
> >
> > I hope you all are kidding me in not seeing the real issue i'm trying to
> > show here.
>
> Does this work?
>
> 	SELECT t.id, x.date_part
> 	FROM table_name t, (select date_part('days',now()-t.stamp)) as x
> 	WHERE x.date_part > 20;

No, because the values in x are correlated to the particular row in
table_name, so I think you have to make it one big subselect in from.  In
addition the optimizer is "smart" enough to push the condition down in
most cases which I think will force the function to be called twice unless
you trigger one of its cases that prevent it from doing so. That's an
optimizer hint I'd like (don't push conditions into this subquery,
really...). :)

Re: Segmentation fault while COPY in 7.3

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-03T06:52:57Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, we found a double pfree in 7.3.  There will be a fix in 7.3.1.

The double pfree was in COPY, though.  This looks to be a different
issue.

>> (gdb)                            
>> #0  0x00420874 in heap_freetuple ()
>> #1  0x004a8390 in acquire_sample_rows ()
>> #2  0x004a75c8 in analyze_rel ()
>> #3  0x0049f690 in vacuum ()
>> #4  0x005585d8 in ProcessUtility ()
>> #5  0x00553c78 in pg_exec_query_string ()
>> #6  0x00555850 in PostgresMain ()

			regards, tom lane

Re: 7.4 Wishlist

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-03T06:57:59Z

"Magnus Naeslund(f)" <mag@fbab.net> writes:
> Mysql is planning on making this work:
>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.

We're supposed to spend our time emulating nonstandard features that
don't even exist yet?  I think I have better things to do ...

			regards, tom lane

Re: 7.4 Wishlist

Dennis Bjorklund <db@zigo.dhs.org> — 2002-12-03T09:20:23Z

On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:

> Now convert this query so that it only evaluates the date_part thing
> ONCE:
> 
> select t.id, date_part('days',now()-t.stamp) from table_name t where
> date_part('days',now()-t.stamp) > 20;

Something like this could work:

select *
  from (select t.id, date_part('days',now()-t.stamp) AS d
          from table_name t) AS t1
 where t1.d > 20;

That aside I also would like some sort of local names. Something like the
let construct used in many functional languages (not exaclty what you want
above, but still):

let t1 = select * from foo;
    t2 = select * from bar;
in select * from t1 natural join t2;

But even though I would like to give name to subexpressions like above, I
still think postgresql should stick to standards as close as possible.

-- 
/Dennis

Re: 7.4 Wishlist

Karel Zak <zakkr@zf.jcu.cz> — 2002-12-03T09:45:41Z

On Mon, Dec 02, 2002 at 12:48:38PM -0800, Christopher Kings-Lynne wrote:
> But if there is, then the sum/count(*) is nonsensical anyway.

 You must to use it in SERIALIZABLE transaction isolation. 

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Jan Hartmann <jhart@frw.uva.nl> — 2002-12-03T09:58:45Z

I'm certainly interested! I am working here on Geographical Information 
Systems with PostgreSQL/PostGIS with the Minnesota MapServer, with a lot 
of regular database work thrown in. PostgreSQL has great potential for 
teaching databases and SQL, and  when the native Windows port is ready, 
it will also be ideal for smaller, individual teaching projects (e.g. at 
home).

Jan Hartmann
Department of Geography
University of Amsterdam
jhart@frw.uva.nl

Gavin Sherry wrote:
> On Mon, 2 Dec 2002, Christopher Kings-Lynne wrote:
> 
> 
>>I've given a talk in the 2002 honours lecture series at UWA about Postgres
>>and some of the things it can do.  All of those guys were interested.
>>Especially since the deptartment does a lot of work in genetic algoriithms.
> 
> 
> Excellent. Can you put that talk online somewhere?
> 
> 
>>Tell me when you start working on a document - I'm happy to help.  Since I'm
>>only just out of Uni, I'd like to write a set of possible assignments and
>>learning outcomes and how you can use postgres to support them.
>>
>>My girlfriend is a PhD student at UWA CS dept :) plus I won the honours
>>scholarship there a year or two back, so I can get interest from the dept,
>>including the databases lecturer.  Might help for another point of view and
>>feedback.
> 
> 
> Excellent. Are there any other people involved in PostgreSQL and
> universities or educational institutions? If so we could put something
> together about experiences for the advocacy Web site.
> 
> 
>>That's tragic.  Teaching kids to admin oracle is something you do in Tafe,
>>or an Oracle course, not a university.  Anyway, what kind of course teaches
>>you about how to admin oracle as opposed to teaching you about ACID
>>properties, MVCC, distributed transactions and partitioning?  Most of which
>>can be demonstrated with Postgres.  We learnt about relational model,
>>algebra and calculus well before learning about SQL!
> 
> 
> Your interest in this is clearly the same as mine: Universities
> (should) teach concept not product. I'm disgusted that this is not the
> case.
> 
> If other people are interested we could work on this in January when I am
> over your way, as discussed in private email.
> 
> Gavin
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
>

Re: 7.4 Wishlist

Dennis Bjorklund <db@zigo.dhs.org> — 2002-12-03T10:05:42Z

On 3 Dec 2002, Hannu Krosing wrote:

> the standard way of doing it would be SQL99's WITH :

Great! I havn't looked too much at sql99 yet so I've missed this. It's 
exactly what I want. Now I know what I will use in the future (when it's 
all implemented).

-- 
/Dennis

Re: 7.4 Wishlist

Hannu Krosing <hannu@tm.ee> — 2002-12-03T11:46:09Z

On Tue, 2002-12-03 at 09:20, Dennis Björklund wrote:
> On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> 
> > Now convert this query so that it only evaluates the date_part thing
> > ONCE:
> > 
> > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > date_part('days',now()-t.stamp) > 20;
> 
> Something like this could work:
> 
> select *
>   from (select t.id, date_part('days',now()-t.stamp) AS d
>           from table_name t) AS t1
>  where t1.d > 20;
> 
> That aside I also would like some sort of local names. Something like the
> let construct used in many functional languages (not exaclty what you want
> above, but still):
> 
> let t1 = select * from foo;
>     t2 = select * from bar;
> in select * from t1 natural join t2;
> 
> But even though I would like to give name to subexpressions like above, I
> still think postgresql should stick to standards as close as possible.

the standard way of doing it would be SQL99's WITH :

with t1 as (select * from foo)
     t2 as (select * from bar)
select * from t1 natural join t2;

you can even use preceeding queries

with t1 as (select a,b from foo)
     t1less as (select a,b from t1 where a < 0)
     t1zero as (select a,b from t1 where a = 0)
select * from t1zero, t1less, where t1zero.b = t1less.a;

Having working WITH clause is also a prerequisite to implementing SQL99
recursive queries (where each query in WITH clause sees all other
queries in the WITH clause)

I sent a patch to this list recently that implements the above syntax,
but I currently dont have knowledge (nor time to aquire it), so if
someone else does not do it it will have to wait until January.

OTOH, I think that turning my parsetree to a plan would be quite easy
for someone familiar with turning parestrees into plans ;)

I offer to check if it works in current (and make it work again if it
does not) if someone would be willing to hold my hand in implementation
parsetree-->plan part ;). 

I think that for non-recursive queries this is all that needs to be
done, i.e. the plan would not care if the subqueries were from FROM,
from WITH or from separately defined views.

-- 
Hannu Krosing <hannu@tm.ee>

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T16:00:47Z

Is WITH a TODO item?

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > 
> > > Now convert this query so that it only evaluates the date_part thing
> > > ONCE:
> > > 
> > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > date_part('days',now()-t.stamp) > 20;
> > 
> > Something like this could work:
> > 
> > select *
> >   from (select t.id, date_part('days',now()-t.stamp) AS d
> >           from table_name t) AS t1
> >  where t1.d > 20;
> > 
> > That aside I also would like some sort of local names. Something like the
> > let construct used in many functional languages (not exaclty what you want
> > above, but still):
> > 
> > let t1 = select * from foo;
> >     t2 = select * from bar;
> > in select * from t1 natural join t2;
> > 
> > But even though I would like to give name to subexpressions like above, I
> > still think postgresql should stick to standards as close as possible.
> 
> the standard way of doing it would be SQL99's WITH :
> 
> with t1 as (select * from foo)
>      t2 as (select * from bar)
> select * from t1 natural join t2;
> 
> you can even use preceeding queries
> 
> with t1 as (select a,b from foo)
>      t1less as (select a,b from t1 where a < 0)
>      t1zero as (select a,b from t1 where a = 0)
> select * from t1zero, t1less, where t1zero.b = t1less.a;
> 
> Having working WITH clause is also a prerequisite to implementing SQL99
> recursive queries (where each query in WITH clause sees all other
> queries in the WITH clause)
> 
> I sent a patch to this list recently that implements the above syntax,
> but I currently dont have knowledge (nor time to aquire it), so if
> someone else does not do it it will have to wait until January.
> 
> OTOH, I think that turning my parsetree to a plan would be quite easy
> for someone familiar with turning parestrees into plans ;)
> 
> I offer to check if it works in current (and make it work again if it
> does not) if someone would be willing to hold my hand in implementation
> parsetree-->plan part ;). 
> 
> I think that for non-recursive queries this is all that needs to be
> done, i.e. the plan would not care if the subqueries were from FROM,
> from WITH or from separately defined views.
> 
> -- 
> Hannu Krosing <hannu@tm.ee>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Segmentation fault while COPY in 7.3

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T16:03:33Z

Ewe, yea.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, we found a double pfree in 7.3.  There will be a fix in 7.3.1.
> 
> The double pfree was in COPY, though.  This looks to be a different
> issue.
> 
> >> (gdb)                            
> >> #0  0x00420874 in heap_freetuple ()
> >> #1  0x004a8390 in acquire_sample_rows ()
> >> #2  0x004a75c8 in analyze_rel ()
> >> #3  0x0049f690 in vacuum ()
> >> #4  0x005585d8 in ProcessUtility ()
> >> #5  0x00553c78 in pg_exec_query_string ()
> >> #6  0x00555850 in PostgresMain ()
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T17:38:44Z

TODO updated.  Thanks for the clarification.

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> > Is WITH a TODO item?
> 
> It is disguised as 
> 
> Exotic Features
> ===============
> 
> * Add sql3 recursive unions
> 
> Which was added at my request in dark times, possibly when PostgreSQL
> was called postgres95 ;)
> 
> This should be changed  to two items
> 
> * Add SQL99 WITH clause to SELECT
> 
> * Add SQL99 WITH RECURSIVE to SELECT
> 
> 
> > ---------------------------------------------------------------------------
> > 
> > Hannu Krosing wrote:
> > > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > > > 
> > > > > Now convert this query so that it only evaluates the date_part thing
> > > > > ONCE:
> > > > > 
> > > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > > date_part('days',now()-t.stamp) > 20;
> > > > 
> > > > Something like this could work:
> > > > 
> > > > select *
> > > >   from (select t.id, date_part('days',now()-t.stamp) AS d
> > > >           from table_name t) AS t1
> > > >  where t1.d > 20;
> > > > 
> > > > That aside I also would like some sort of local names. Something like the
> > > > let construct used in many functional languages (not exaclty what you want
> > > > above, but still):
> > > > 
> > > > let t1 = select * from foo;
> > > >     t2 = select * from bar;
> > > > in select * from t1 natural join t2;
> > > > 
> > > > But even though I would like to give name to subexpressions like above, I
> > > > still think postgresql should stick to standards as close as possible.
> > > 
> > > the standard way of doing it would be SQL99's WITH :
> > > 
> > > with t1 as (select * from foo)
> > >      t2 as (select * from bar)
> > > select * from t1 natural join t2;
> > > 
> > > you can even use preceeding queries
> > > 
> > > with t1 as (select a,b from foo)
> > >      t1less as (select a,b from t1 where a < 0)
> > >      t1zero as (select a,b from t1 where a = 0)
> > > select * from t1zero, t1less, where t1zero.b = t1less.a;
> > > 
> > > Having working WITH clause is also a prerequisite to implementing SQL99
> > > recursive queries (where each query in WITH clause sees all other
> > > queries in the WITH clause)
> > > 
> > > I sent a patch to this list recently that implements the above syntax,
> > > but I currently dont have knowledge (nor time to aquire it), so if
> > > someone else does not do it it will have to wait until January.
> > > 
> > > OTOH, I think that turning my parsetree to a plan would be quite easy
> > > for someone familiar with turning parestrees into plans ;)
> > > 
> > > I offer to check if it works in current (and make it work again if it
> > > does not) if someone would be willing to hold my hand in implementation
> > > parsetree-->plan part ;). 
> > > 
> > > I think that for non-recursive queries this is all that needs to be
> > > done, i.e. the plan would not care if the subqueries were from FROM,
> > > from WITH or from separately defined views.
> > > 
> > > -- 
> > > Hannu Krosing <hannu@tm.ee>
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > > 
> -- 
> Hannu Krosing <hannu@tm.ee>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Hannu Krosing <hannu@tm.ee> — 2002-12-03T19:06:57Z

On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> Is WITH a TODO item?

It is disguised as 

Exotic Features
===============

* Add sql3 recursive unions

Which was added at my request in dark times, possibly when PostgreSQL
was called postgres95 ;)

This should be changed  to two items

* Add SQL99 WITH clause to SELECT

* Add SQL99 WITH RECURSIVE to SELECT


> ---------------------------------------------------------------------------
> 
> Hannu Krosing wrote:
> > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > > 
> > > > Now convert this query so that it only evaluates the date_part thing
> > > > ONCE:
> > > > 
> > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > date_part('days',now()-t.stamp) > 20;
> > > 
> > > Something like this could work:
> > > 
> > > select *
> > >   from (select t.id, date_part('days',now()-t.stamp) AS d
> > >           from table_name t) AS t1
> > >  where t1.d > 20;
> > > 
> > > That aside I also would like some sort of local names. Something like the
> > > let construct used in many functional languages (not exaclty what you want
> > > above, but still):
> > > 
> > > let t1 = select * from foo;
> > >     t2 = select * from bar;
> > > in select * from t1 natural join t2;
> > > 
> > > But even though I would like to give name to subexpressions like above, I
> > > still think postgresql should stick to standards as close as possible.
> > 
> > the standard way of doing it would be SQL99's WITH :
> > 
> > with t1 as (select * from foo)
> >      t2 as (select * from bar)
> > select * from t1 natural join t2;
> > 
> > you can even use preceeding queries
> > 
> > with t1 as (select a,b from foo)
> >      t1less as (select a,b from t1 where a < 0)
> >      t1zero as (select a,b from t1 where a = 0)
> > select * from t1zero, t1less, where t1zero.b = t1less.a;
> > 
> > Having working WITH clause is also a prerequisite to implementing SQL99
> > recursive queries (where each query in WITH clause sees all other
> > queries in the WITH clause)
> > 
> > I sent a patch to this list recently that implements the above syntax,
> > but I currently dont have knowledge (nor time to aquire it), so if
> > someone else does not do it it will have to wait until January.
> > 
> > OTOH, I think that turning my parsetree to a plan would be quite easy
> > for someone familiar with turning parestrees into plans ;)
> > 
> > I offer to check if it works in current (and make it work again if it
> > does not) if someone would be willing to hold my hand in implementation
> > parsetree-->plan part ;). 
> > 
> > I think that for non-recursive queries this is all that needs to be
> > done, i.e. the plan would not care if the subqueries were from FROM,
> > from WITH or from separately defined views.
> > 
> > -- 
> > Hannu Krosing <hannu@tm.ee>
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> > 
-- 
Hannu Krosing <hannu@tm.ee>

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Marc G. Fournier <scrappy@hub.org> — 2002-12-03T19:13:56Z

On Tue, 3 Dec 2002, Justin Clift wrote:

> > Excellent. Are there any other people involved in PostgreSQL and
> > universities or educational institutions? If so we could put something
> > together about experiences for the advocacy Web site.
>
> Is this the kind of thing that the Techdocs Guides area would be good
> for?  (http://techdocs.postgresql.org/guides)

Seems that any discussions about "experiences" belongs on Advocacy, no?

Re: Segmentation fault while COPY in 7.3

Medi Montaseri <medi.montaseri@intransa.com> — 2002-12-03T19:36:36Z

Can I have a patch and would it work against 7.2.3.....

Thanks
Bruce Momjian wrote:

>Yes, we found a double pfree in 7.3.  There will be a fix in 7.3.1.
>
>---------------------------------------------------------------------------
>
>Medi Montaseri wrote:
>  
>
>>Something is funny about this pfree()....here is a similar crash on a 7.2.3
>>
>>(gdb)                            
>>#0  0x00420874 in heap_freetuple ()
>>#1  0x004a8390 in acquire_sample_rows ()
>>#2  0x004a75c8 in analyze_rel ()
>>#3  0x0049f690 in vacuum ()
>>#4  0x005585d8 in ProcessUtility ()
>>#5  0x00553c78 in pg_exec_query_string ()
>>#6  0x00555850 in PostgresMain ()
>>#7  0x00524660 in DoBackend ()
>>#8  0x00523d08 in BackendStartup ()
>>#9  0x00521c18 in ServerLoop ()
>>#10 0x005211c8 in PostmasterMain ()
>>#11 0x004df3d8 in main ()
>>#12 0x2ae34928 in __libc_start_main () from /lib/libc.so.6
>>(gdb) 
>>
>>
>>
>>Nicolai Tufar wrote:
>>
>>    
>>
>>>Help!
>>>Backend crashes on any COPY command I issue:
>>>PostgreSQL is 7.3 release, is compiled from sources.
>>>The message I get is:
>>>
>>>apb=# COPY maras2.mrk_yyn (bsvr_no, yyn_tur, yyn_no, yyn_syf, yyn_trh) TO
>>>stdout;
>>>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.
>>>!#
>>>
>>>
>>>The output of strace on backend is following:
>>>
>>># strace -p 18883
>>>recv(7, "QCOPY maras2.mrk_yyn (bsvr_no, y"..., 8192, 0) = 77
>>>gettimeofday({1038713111, 629131}, NULL) = 0
>>>_llseek(11, 0, [0], SEEK_SET)           = 0
>>>read(11, "\0\0\0\0\20\0\0\0\1\0\0\0\24\0\360\37\360\37\1 b1\5\0\1"..., 8192)
>>>= 8192
>>>read(11, "\0\0\0\0x#\375\"\20\0\0\0008\0h\35\360\37\1 \370\235\220"...,
>>>8192) = 8192
>>>_llseek(12, 0, [0], SEEK_SET)           = 0
>>>read(12, "\0\0\0\0\0#\375\"\20\0\0\0008\0@\34\0 \1 \200\237\0\1\0"..., 8192)
>>>= 8192
>>>_llseek(21, 40960, [40960], SEEK_SET)   = 0
>>>read(21, "\0\0\0\0\4\251\3#\20\0\0\0004\4p\17\360\37\1 \340\237 "..., 8192)
>>>= 8192
>>>_llseek(22, 106496, [106496], SEEK_SET) = 0
>>>read(22, "\0\0\0\0(\326\377\"\20\0\0\0\10\1\200\1\0 \1 \200\237\0"..., 8192)
>>>= 8192
>>>read(17, "\0\0\0\0\20\0\0\0\1\0\0\0\360\0\20\1\0 \1 p\237 \1\340"..., 8192)
>>>= 8192
>>>open("/data/pgsql/base/2015749/2015757", O_RDWR|O_LARGEFILE) = 39
>>>_llseek(39, 0, [20037632], SEEK_END)    = 0
>>>_llseek(39, 0, [0], SEEK_SET)           = 0
>>>read(39, "\0\0\0\0\270\7\4#\20\0\0\0\300\1\350\1\0 \1 \270\237\216"...,
>>>8192) = 8192
>>>--- SIGSEGV (Segmentation fault) ---
>>>+++ killed by SIGSEGV +++
>>>#
>>>
>>>I have recompiled postgres with debug information enabled, run gdb, attached
>>>to backend process
>>>and caught SIGSEGV as following:
>>>
>>>(gdb) continue
>>>Continuing.
>>>
>>>Program received signal SIGSEGV, Segmentation fault.
>>>0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
>>>480             (*header->context->methods->free_p) (header->context,
>>>pointer);
>>>(gdb)
>>>
>>>
>>>The output of bt is:
>>>
>>>(gdb) bt
>>>#0  0x081697bf in pfree (pointer=0x828a7b0) at mcxt.c:480
>>>#1  0x080b37be in CopyTo (rel=0x402be088, attnumlist=0x828a3f8, binary=0
>>>'\0', oids=0 '\0',
>>>   fp=0x0, delim=0x81c2a57 "\t", null_print=0x81b2c22 "\\N") at copy.c:671
>>>#2  0x080b32f6 in DoCopy (stmt=0x827b398) at copy.c:491
>>>#3  0x08118a6a in pg_exec_query_string (query_string=0x827af48, dest=Remote,
>>>   parse_context=0x8245378) at postgres.c:789
>>>#4  0x08119b89 in PostgresMain (argc=4, argv=0xbfffd2a0, username=0x8240a81
>>>"postgres")
>>>   at postgres.c:2016
>>>#5  0x08101d7c in DoBackend (port=0x8240950) at postmaster.c:2293
>>>#6  0x081016c2 in BackendStartup (port=0x8240950) at postmaster.c:1915
>>>#7  0x08100875 in ServerLoop () at postmaster.c:1000
>>>#8  0x08100326 in PostmasterMain (argc=1, argv=0x82270c0) at
>>>postmaster.c:779
>>>#9  0x080debab in main (argc=1, argv=0xbfffdc34) at main.c:210
>>>#10 0x42017589 in __libc_start_main () from /lib/i686/libc.so.6
>>>(gdb)
>>>
>>>
>>>
>>>Thanks in advance,
>>>Nic
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>> 
>>>
>>>      
>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>>    
>>
>
>  
>

Re: Segmentation fault while COPY in 7.3

Medi Montaseri <medi.montaseri@intransa.com> — 2002-12-03T19:42:23Z

FYI, I'm experiencing this with Async Queries (Async Purge and Aysnc 
Vacuum).

Tom Lane wrote:

>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>  
>
>>Yes, we found a double pfree in 7.3.  There will be a fix in 7.3.1.
>>    
>>
>
>The double pfree was in COPY, though.  This looks to be a different
>issue.
>
>  
>
>>>(gdb)                            
>>>#0  0x00420874 in heap_freetuple ()
>>>#1  0x004a8390 in acquire_sample_rows ()
>>>#2  0x004a75c8 in analyze_rel ()
>>>#3  0x0049f690 in vacuum ()
>>>#4  0x005585d8 in ProcessUtility ()
>>>#5  0x00553c78 in pg_exec_query_string ()
>>>#6  0x00555850 in PostgresMain ()
>>>      
>>>
>
>			regards, tom lane
>  
>

Re: Segmentation fault while COPY in 7.3

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-03T19:51:23Z

I was wrong.  We found a COPY bug in 7.3 that will be fixed in 7.3.1. 
Would you compile with symbols, -g, and send a new backtrace?

---------------------------------------------------------------------------

Medi Montaseri wrote:
> FYI, I'm experiencing this with Async Queries (Async Purge and Aysnc 
> Vacuum).
> 
> Tom Lane wrote:
> 
> >Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >  
> >
> >>Yes, we found a double pfree in 7.3.  There will be a fix in 7.3.1.
> >>    
> >>
> >
> >The double pfree was in COPY, though.  This looks to be a different
> >issue.
> >
> >  
> >
> >>>(gdb)                            
> >>>#0  0x00420874 in heap_freetuple ()
> >>>#1  0x004a8390 in acquire_sample_rows ()
> >>>#2  0x004a75c8 in analyze_rel ()
> >>>#3  0x0049f690 in vacuum ()
> >>>#4  0x005585d8 in ProcessUtility ()
> >>>#5  0x00553c78 in pg_exec_query_string ()
> >>>#6  0x00555850 in PostgresMain ()
> >>>      
> >>>
> >
> >			regards, tom lane
> >  
> >
> 
> 
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Vince Vielhaber <vev@michvhf.com> — 2002-12-03T20:08:50Z

On Tue, 3 Dec 2002, Marc G. Fournier wrote:

> On Tue, 3 Dec 2002, Justin Clift wrote:
>
> > > Excellent. Are there any other people involved in PostgreSQL and
> > > universities or educational institutions? If so we could put something
> > > together about experiences for the advocacy Web site.
> >
> > Is this the kind of thing that the Techdocs Guides area would be good
> > for?  (http://techdocs.postgresql.org/guides)
>
> Seems that any discussions about "experiences" belongs on Advocacy, no?

Where have you been?  The lines of distinction between all of the lists
have gotten so blurred it hardly makes a difference.

Vince.
-- 
   http://www.meanstreamradio.com       http://www.unknown-artists.com
         Internet radio: It's not file sharing, it's just radio.

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Marc G. Fournier <scrappy@hub.org> — 2002-12-03T20:24:41Z

On Tue, 3 Dec 2002, Vince Vielhaber wrote:

> On Tue, 3 Dec 2002, Marc G. Fournier wrote:
>
> > On Tue, 3 Dec 2002, Justin Clift wrote:
> >
> > > > Excellent. Are there any other people involved in PostgreSQL and
> > > > universities or educational institutions? If so we could put something
> > > > together about experiences for the advocacy Web site.
> > >
> > > Is this the kind of thing that the Techdocs Guides area would be good
> > > for?  (http://techdocs.postgresql.org/guides)
> >
> > Seems that any discussions about "experiences" belongs on Advocacy, no?
>
> Where have you been?  The lines of distinction between all of the lists
> have gotten so blurred it hardly makes a difference.

Actually, there are lines, Justin just occasionally appears to 'blur' them
until I get a chance to refresh them ... eh Justin?:)

Re: 7.4 Wishlist

Kevin Brown <kevin@sysexperts.com> — 2002-12-03T20:49:58Z

Al Sutton wrote:
> Point to Point and Broadcast replication
> ----------------------------------------
> With point to point you specify multiple endpoints, with broadcast you can
> specify a subnet address and the updates are broadcast over that subnet.
> 
> The difference being that point to point works well for cross network
> replication, or where you have a few replicants. I have multiple database
> servers which could have a deadicated class C network that they are all on,
> by broadcasting updates you can cutdown the amount of traffic on that net by
> a factor of n minus 1 (where n is the number of servers involved).

Yech.  Now you can't use TCP anymore, so the underlying replication
code has to handle all the issues that TCP deals with transparently,
like error checking, retransmits, data windows, etc.  I don't think
it's wise to assume that your transport layer is 100% reliable.

Further, this doesn't even address the problem of bringing up a leaf
server that's been down a while.  It can be significantly out of date
relative to the other servers on the subnet.

I suspect you'll be better off implementing a replication protocol
that has the leaf nodes keeping each other up to date, to minimize the
traffic coming from the next level up.  Then you can use TCP for the
connections but minimize the traffic generated by any given node.

> Ability to use raw partitions
> ----------------------------
> 
> I've not seen an install of PostgreSQL yet that didn't put the database
> files onto a filesystem, so I'm assuming it's the only way of doing it. By
> using the filesystem the files are at the mercy of filesystem handler code
> as to where they end up on the disk, and thus the speed of access will
> always have some dependancy on the speed of the filesystem.
> 
> With a raw partition it would be possible to use two devices (e.g. /dev/hde
> and /dev/hdg on an eight channel ide linux box), and PostgreSQL could then
> ensure the WALs were located on one the disk with the entries running
> sequentally, and that the database files were located on the other disk in
> the most appropriate location (e.g. index data starting near the center of
> the disk, and user table data starting near the outside).

Yeah, but now you have to worry about optimizing placement of blocks,
optimizing writes, etc.  These are things the OS should worry about,
not the database server.

If you're really that concerned about these issues, store the WAL on
one (empty) filesystem and the tables on another (empty and separate)
filesystem.  With any reasonable filesystem you'll get reasonably
close to optimal performance, especially if the filesystem code is
capable of analyzing the write patterns and adapting itself
accordingly.

In short, I'd much rather spend the effort improving the filesystem
(where everyone can benefit) than improving PostgreSQL (where only
PostgreSQL users can benefit) for this item.

The one good reason for making it possible to use raw partitions is to
make it possible to use the PostgreSQL engine as a filesystem!  :-)

> Win32 Port
> ------------
> I've explained the reasons before. Apart from that it's always useful to
> open PostgreSQL up to a larger audience.

Agreed.

- Kevin Brown

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Dan Langille <dan@langille.org> — 2002-12-03T20:51:18Z

On 3 Dec 2002 at 15:08, Vince Vielhaber wrote:

> Where have you been?  The lines of distinction between all of the
> lists have gotten so blurred it hardly makes a difference.

So consider this a wake up call.
-- 
Dan Langille : http://www.langille.org/

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Hans-Jürgen Schönig <postgres@cybertec.at> — 2002-12-03T21:16:50Z

Hi ...

I just wanted to admit that an important collegue in Vienna already uses
PostgreSQL instead of Oracle which makes me really proud :).

We have done a training course this year and they use PostgreSQL instead 
of free Oracle
I am happy that Austrian students are tortured with the things I have 
taught this year *g*..

    Hans


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>

Re: 7.4 Wishlist

Rod Taylor <rbt@rbt.ca> — 2002-12-03T21:30:13Z

> The one good reason for making it possible to use raw partitions is to
> make it possible to use the PostgreSQL engine as a filesystem!  :-)

Hmm..  Something just doesn't seem right about that thought ;)

CREATE DATABASE filesystem;
\c filesystem
CREATE EXPORT /mnt AS NFS;
\q

mount_nfs -o port=5432 localhost:/mnt /mnt
ls /mnt/pg_class



-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Justin Clift <justin@postgresql.org> — 2002-12-03T23:09:04Z

Marc G. Fournier wrote:
> On Tue, 3 Dec 2002, Justin Clift wrote:
> 
> 
>>>Excellent. Are there any other people involved in PostgreSQL and
>>>universities or educational institutions? If so we could put something
>>>together about experiences for the advocacy Web site.
>>
>>Is this the kind of thing that the Techdocs Guides area would be good
>>for?  (http://techdocs.postgresql.org/guides)
> 
> 
> Seems that any discussions about "experiences" belongs on Advocacy, no?

Good point.

Have put a *really basic* Zwiki framework at:

http://advocacy.postgresql.org/documents

It's the same collaborative software used for the PostgreSQL Guides 
section, but without the look+feel added.

If you want to start editing stuff right away, then feel free to use it. 
  If you'd like it to look better first though, then it'll be a few days...

:-)

Regards and best wishes,

Justin Clift

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Re: 7.4 Wishlist

Peter Eisentraut <peter_e@gmx.net> — 2002-12-03T23:15:31Z

Joe Conway writes:

> That is one thing I'd like to take a look at. I think the problem is that
> certain byte-sequence/multibyte-encoding combinations are illegal, so it's not
> as simple an issue as it might first appear.

The bytea type really shouldn't come even close to having to care about
this.

Actually, if you want to improve the ugly bytea literal syntax, implement
the standard BLOB type.

-- 
Peter Eisentraut   peter_e@gmx.net

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Justin Clift <justin@postgresql.org> — 2002-12-03T23:17:40Z

Marc G. Fournier wrote:
<snip>
> Actually, there are lines, Justin just occasionally appears to 'blur' them
> until I get a chance to refresh them ... eh Justin?:)

[innocent whistle]

+ Justin

Zwiki (moved from -hackers)

Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-12-04T00:06:16Z

> Have put a *really basic* Zwiki framework at:
>
> http://advocacy.postgresql.org/documents
>
> It's the same collaborative software used for the PostgreSQL Guides
> section, but without the look+feel added.
>
> If you want to start editing stuff right away, then feel free to use it.

How do you actually edit something or create something - I can't find any
way of doing that.

Chris

Re: Zwiki (moved from -hackers)

Justin Clift <justin@postgresql.org> — 2002-12-04T02:01:53Z

Christopher Kings-Lynne wrote:
<snip>
> How do you actually edit something or create something - I can't find any
> way of doing that.

Oops...

There definitely needs to be a lot more intro stuff on there along the 
lines of "how do I use this?"...  :-/

You create a new document by going to the "intended URL".

i.e.

http://techdocs.postgresql.org/guides/SomeURL

or:

http://advocacy.postgresql.org/documents/ADifferentURL

For both of these, as there isn't really anything there, it will offer 
you the option of creating a new document there.  It does that instead 
of the standard 404 Webserver error.

That's generally the easiest way of creating a new document.

So, as a test you might want to try out:

http://techdocs.postgresql.org/guides/ChrisesDocs

or similar.

:-)

Regards and best wishes,

Justin Clift

> Chris
> 

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)

Manuel Cabido <manny@tinago.msuiit.edu.ph> — 2002-12-04T04:18:13Z

Hi:

   We at the Department of Information Technology of the Mindanao State
University-Iligan Institute of Technology (MSU-IIT) in Iligan City,
Philippines had been using PostgreSQL since 1998 in teaching courses in
Databases, SQL, and as a support tool in teaching Software Engineering and
Web Application Development. We are even utilizing it as our database
backend in all applications we developed in-house like Payroll, Student
Enrollment, Financial Applications, etc.  At the rate PostgreSQL is
performing, we are not for any reason tempted to migrate to another
database for the next ten years.

   THANKS TO THE POSTGRESQL DEVELOPMENT TEAM. 

   We Salute YOU!


PROF. MANUEL C. CABIDO
Chair
Department of Information Technology
MSU-IIT
Iligan City 9200
Philippines

Re: Segmentation fault in 7.3

Frank van Vugt <ftm.van.vugt@foxi.nl> — 2002-12-04T09:01:23Z

I encountered a reproducable backend crash in the following situation:

- while running a conversion tool written in TrollTech's Qt that uses libpq

- apart from the tool, only one inactive psql-client is connected to the same 
database (for extra monitoring)

- during the conversion session this tool creates a number of temporary tables

The backend crashes when at the end of the conversion a 'vacuum full analyse' 
is started, more specifically it dies upon doing work on one of these 
temporary tables....

Obviously, no problem is encountered when vacuuming *after* the tool has 
finished.


Is this known behaviour?

Otherwise, I'd be glad to assist with a backtrace.



Regards,




Frank.

Broadcast replication (Was Re: 7.4 Wishlist)

Al Sutton <al@alsutton.com> — 2002-12-04T09:47:16Z

----- Original Message -----
From: "Kevin Brown" <kevin@sysexperts.com>
To: <pgsql-hackers@postgresql.org>
Sent: Tuesday, December 03, 2002 8:49 PM
Subject: [mail] Re: [HACKERS] 7.4 Wishlist

> Al Sutton wrote:
> > Point to Point and Broadcast replication
> > ----------------------------------------
> > With point to point you specify multiple endpoints, with broadcast you
can
> > specify a subnet address and the updates are broadcast over that subnet.
> >
> > The difference being that point to point works well for cross network
> > replication, or where you have a few replicants. I have multiple
database
> > servers which could have a deadicated class C network that they are all
on,
> > by broadcasting updates you can cutdown the amount of traffic on that
net by
> > a factor of n minus 1 (where n is the number of servers involved).
>
> Yech.  Now you can't use TCP anymore, so the underlying replication
> code has to handle all the issues that TCP deals with transparently,
> like error checking, retransmits, data windows, etc.  I don't think
> it's wise to assume that your transport layer is 100% reliable.
>
> Further, this doesn't even address the problem of bringing up a leaf
> server that's been down a while.  It can be significantly out of date
> relative to the other servers on the subnet.
>
> I suspect you'll be better off implementing a replication protocol
> that has the leaf nodes keeping each other up to date, to minimize the
> traffic coming from the next level up.  Then you can use TCP for the
> connections but minimize the traffic generated by any given node.
>

I wasn't saying that ALL replication traffic must be broadcast, if a
specific server needs a refresh when it comes then point to point is fine
because only one machine needs the data, and thus broadcasting it to all
would load machines with data they didn't need.

The aim of using broadcast is to cut down the ongoing traffic, say, for
example, I have a cluster of ten database servers I can connect them onto a
dedicated LAN shared only by database servers and I would see 10% of the
traffic I would get if I were using point to point (this is assuming that
the addition of error checking, retransmits, etc. to the broadcast protocol
adds a similiar overhead per packet as TCP point to point).

If others wish to know more about this I can prepare an overview for how I
see it working.

[Other points snipped]

Re: Segmentation fault in 7.3

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-04T14:09:46Z

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> Is this known behaviour?

Nope.

> Otherwise, I'd be glad to assist with a backtrace.

Please.

			regards, tom lane

Re: 7.4 Wishlist

Kevin Brown <kevin@sysexperts.com> — 2002-12-04T21:57:31Z

Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> Hi guys,
> 
> Just out of interest, if someone was going to pay you to hack on Postgres
> for 6 months, what would you like to code for 7.4?

Well, on top of the oft-requested replication support and savepoint
support, I'd like to see UPDATE, er, updated to be able to make use of
cursors.

I'd also like to see (if this is even possible) a transaction
isolation mode that would make it possible for multiple concurrent
updates to the same row to happen without blocking each other (I
imagine one way to make this possible would be for the last
transaction to commit to be the one that "wins".  Each transaction
that commits gets its updates written so that other transactions that
begin after they commit will see them, of course).  Neither "read
committed" nor "serialized" modes offer this.  Don't know if it's
possible, but it would be nice (such that a transaction sees the
database as if it has it all to itself and doesn't block on
updates)...

- Kevin

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-05T06:21:12Z

Kevin Brown wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> > Hi guys,
> > 
> > Just out of interest, if someone was going to pay you to hack on Postgres
> > for 6 months, what would you like to code for 7.4?
> 
> Well, on top of the oft-requested replication support and savepoint
> support, I'd like to see UPDATE, er, updated to be able to make use of
> cursors.

I think this could be easily done by using the tid of the cursor row for
the update, assuming there is a clear tid for the SELECT.  Jan has
talked about doing that.

> I'd also like to see (if this is even possible) a transaction
> isolation mode that would make it possible for multiple concurrent
> updates to the same row to happen without blocking each other (I
> imagine one way to make this possible would be for the last
> transaction to commit to be the one that "wins".  Each transaction
> that commits gets its updates written so that other transactions that
> begin after they commit will see them, of course).  Neither "read
> committed" nor "serialized" modes offer this.  Don't know if it's
> possible, but it would be nice (such that a transaction sees the
> database as if it has it all to itself and doesn't block on
> updates)...

How would you do the update if you don't know of the transaction commits
or aborts?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Segmentation fault in 7.3 while vacuuming

Frank van Vugt <ftm.van.vugt@foxi.nl> — 2002-12-05T12:16:18Z

> Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> > Is this known behaviour?
>
TL> Nope.
>
> > Otherwise, I'd be glad to assist with a backtrace.
>
TL> Please.

** PostgreSQL v7.3 configured with:
./configure --prefix=/usr/src/postgresql_debug/pgsql --enable-debug 
--with-openssl --with-perl --with-python --with-tcl --with-pgport=5433

** while using a client written in TrollTech's QT v3.1 that creates a number 
of temporary tables, uses a number lot of 'copy' statements and at the end 
tries to 'vacuum full analyse' the database

** the backend crashes.... ;(

** last information received by the frontend:
INFO:  --Relation pg_temp_2.t_article_details--
Warning: QSqlDatabaseManager unable to open database: FATAL:  The database 
system is in recovery mode


** backend's log:
LOG:  query: update stock_pick set amount = case when pallet_amount > 
amount_stock then amount_stock else pallet_amount end             from 
t_stock where stock_pick.article_id = t_stock.id
LOG:  recycled transaction log file 0000000000000017
LOG:  query: update t_stock set amount_stock = case when pallet_amount > 
amount_stock then 0 else amount_stock - pallet_amount end              where 
exists (select 1 from stock_pick where article_id = t_stock.id)
LOG:  query: insert into stock_bulk select 5, id, amount_stock, 0, 1, false, 
null from t_stock
LOG:  query: SELECT 1 FROM ONLY "public"."location" x WHERE "id" = $1 FOR 
UPDATE OF x
LOG:  query: SELECT 1 FROM ONLY "public"."article" x WHERE "id" = $1 FOR 
UPDATE OF x
LOG:  query: SELECT 1 FROM ONLY "public"."pallet_type" x WHERE "id" = $1 FOR 
UPDATE OF x
LOG:  query: truncate table t_stock
LOG:  query: drop table aux_country, aux_language, aux_purchaser, 
aux_salesperson, aux_valuta, aux_address
LOG:  query: drop index temp01, temp02, temp03
LOG:  query: vacuum full verbose analyse
LOG:  recycled transaction log file 000000000000001A
LOG:  recycled transaction log file 0000000000000018
LOG:  recycled transaction log file 0000000000000019
LOG:  server process (pid 32663) was terminated by signal 11
LOG:  terminating any other active server processes
WARNING:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
LOG:  connection received: host=192.168.0.79 port=33762
FATAL:  The database system is in recovery mode
LOG:  all server processes terminated; reinitializing shared memory and 
semaphores
LOG:  database system was interrupted at 2002-12-05 12:22:50 CET
LOG:  checkpoint record is at 0/1DAEA234
LOG:  redo record is at 0/1D8D2C10; undo record is at 0/0; shutdown FALSE
LOG:  next transaction id: 5145; next oid: 451153
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  redo starts at 0/1D8D2C10
LOG:  ReadRecord: record with zero length at 0/1DB74550
LOG:  redo done at 0/1DB7452C
LOG:  recycled transaction log file 000000000000001B
LOG:  recycled transaction log file 000000000000001C
LOG:  database system is ready


** backtrace of the coredump:
#0  _mdfd_getrelnfd (reln=0x0) at md.c:854
854             fd = RelationGetFile(reln);
(gdb) bt
#0  _mdfd_getrelnfd (reln=0x0) at md.c:854
#1  0x8123a0f in _mdfd_getseg (reln=0x0, blkno=13) at md.c:878
#2  0x812349d in mdwrite (reln=0x0, blocknum=13, buffer=0x848dde8 "") at 
md.c:461
#3  0x8123e31 in smgrwrite (which=0, reln=0x0, blocknum=13, buffer=0x848dde8 
"") at smgr.c:328
#4  0x811be2a in LocalBufferAlloc (reln=0x413979d0, blockNum=0, 
foundPtr=0xbfffebb7 "¿Ìëÿ¿@§\021\bÐy9A") at localbuf.c:100
#5  0x811a7c2 in ReadBufferInternal (reln=0x413979d0, blockNum=0, 
bufferLockHeld=0 '\000') at bufmgr.c:144
#6  0x811a740 in ReadBuffer (reln=0x413979d0, blockNum=0) at bufmgr.c:112
#7  0x80d7d19 in scan_heap (vacrelstats=0x8389430, onerel=0x413979d0, 
vacuum_pages=0xbfffed50, fraged_pages=0xbfffed40) at vacuum.c:1069
#8  0x80d7a79 in full_vacuum_rel (onerel=0x413979d0, vacstmt=0x8313858) at 
vacuum.c:910
#9  0x80d799e in vacuum_rel (relid=374498, vacstmt=0x8313858, 
expected_relkind=114 'r') at vacuum.c:827
#10 0x80d73a3 in vacuum (vacstmt=0x8313858) at vacuum.c:290
#11 0x8126ff6 in ProcessUtility (parsetree=0x8313858, dest=Remote, 
completionTag=0xbfffee30 "") at utility.c:713
#12 0x8124dcc in pg_exec_query_string (query_string=0x8313688, dest=Remote, 
parse_context=0x82ddb70) at postgres.c:789
#13 0x8125df7 in PostgresMain (argc=4, argv=0xbffff058, username=0x82d4a81 
"postgres") at postgres.c:2016
#14 0x810eab9 in DoBackend (port=0x82d4950) at postmaster.c:2293
#15 0x810e467 in BackendStartup (port=0x82d4950) at postmaster.c:1915
#16 0x810d7d4 in ServerLoop () at postmaster.c:1018
#17 0x810d331 in PostmasterMain (argc=4, argv=0x82bce00) at postmaster.c:779
#18 0x80ee909 in main (argc=4, argv=0xbffff974) at main.c:210
#19 0x400d4577 in __libc_start_main () from /lib/libc.so.6
(gdb)



If you need more details on machine/tables/etc. just let me know.
 -> the backend doesn't crash if the vacuum is done through psql AFTER the 
clients work is done (and the temporary tables are automatically destroyed)











Regards,






Frank.

Re: Segmentation fault in 7.3 while vacuuming

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-05T14:33:28Z

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> ** while using a client written in TrollTech's QT v3.1 that creates a number 
> of temporary tables, uses a number lot of 'copy' statements and at the end 
> tries to 'vacuum full analyse' the database

Drat --- looks like the local buffer manager code has gotten out of sync
with the relcache code.  In particular I note the following in localbuf.c

        Relation    bufrel = RelationNodeCacheGetRelation(bufHdr->tag.rnode);

        /*
         * The relcache is not supposed to throw away temp rels, so this
         * should always succeed.
         */
        Assert(bufrel != NULL);

which was true at the time it was written, but is so no longer :-(

Will fix.

			regards, tom lane

Re: Segmentation fault in 7.3 while vacuuming

Tom Lane <tgl@sss.pgh.pa.us> — 2002-12-05T22:51:48Z

I said:
> Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
>> ** while using a client written in TrollTech's QT v3.1 that creates a number
>> of temporary tables, uses a number lot of 'copy' statements and at the end 
>> tries to 'vacuum full analyse' the database

> Drat --- looks like the local buffer manager code has gotten out of sync
> with the relcache code.

Here is the fix if you need to patch this locally.

			regards, tom lane


*** src/backend/storage/buffer/localbuf.c.orig	Wed Sep  4 16:31:25 2002
--- src/backend/storage/buffer/localbuf.c	Thu Dec  5 17:48:10 2002
***************
*** 90,108 ****
  	{
  		Relation	bufrel = RelationNodeCacheGetRelation(bufHdr->tag.rnode);
  
- 		/*
- 		 * The relcache is not supposed to throw away temp rels, so this
- 		 * should always succeed.
- 		 */
- 		Assert(bufrel != NULL);
- 
  		/* flush this page */
! 		smgrwrite(DEFAULT_SMGR, bufrel, bufHdr->tag.blockNum,
! 				  (char *) MAKE_PTR(bufHdr->data));
! 		LocalBufferFlushCount++;
  
! 		/* drop refcount incremented by RelationNodeCacheGetRelation */
! 		RelationDecrementReferenceCount(bufrel);
  	}
  
  	/*
--- 90,113 ----
  	{
  		Relation	bufrel = RelationNodeCacheGetRelation(bufHdr->tag.rnode);
  
  		/* flush this page */
! 		if (bufrel == (Relation) NULL)
! 		{
! 			smgrblindwrt(DEFAULT_SMGR,
! 						 bufHdr->tag.rnode,
! 						 bufHdr->tag.blockNum,
! 						 (char *) MAKE_PTR(bufHdr->data));
! 		}
! 		else
! 		{
! 			smgrwrite(DEFAULT_SMGR, bufrel,
! 					  bufHdr->tag.blockNum,
! 					  (char *) MAKE_PTR(bufHdr->data));
! 			/* drop refcount incremented by RelationNodeCacheGetRelation */
! 			RelationDecrementReferenceCount(bufrel);
! 		}
  
! 		LocalBufferFlushCount++;
  	}
  
  	/*

Re: 7.4 Wishlist

Kevin Brown <kevin@sysexperts.com> — 2002-12-06T10:10:28Z

Bruce Momjian wrote:
> Kevin Brown wrote:
> > I'd also like to see (if this is even possible) a transaction
> > isolation mode that would make it possible for multiple concurrent
> > updates to the same row to happen without blocking each other (I
> > imagine one way to make this possible would be for the last
> > transaction to commit to be the one that "wins".  Each transaction
> > that commits gets its updates written so that other transactions that
> > begin after they commit will see them, of course).  Neither "read
> > committed" nor "serialized" modes offer this.  Don't know if it's
> > possible, but it would be nice (such that a transaction sees the
> > database as if it has it all to itself and doesn't block on
> > updates)...
> 
> How would you do the update if you don't know of the transaction commits
> or aborts?

Maybe I should explain what I'm thinking a little further.

What I'm proposing is a transaction isolation model where each
transaction thinks that it's the only one making changes to the
database.  That assumption obviously fails *outside* of a transaction,
but inside a transaction it should be possible to maintain the
illusion.

We already get this with serialized transaction isolation mode, with
one caveat: when an update conflicts with that of another transaction
that committed while the transaction of interest is in progress, the
transaction of interest gets rolled back immediately with a
serialization error.

What I'm proposing is to extend the illusion to updates.  A
transaction running in this new isolation mode (call it "full
isolation" or something) never sees updates that other committed
transactions perform, whether they would conflict or not.  The view
the running transaction sees of the database is a snapshot in time, as
it were, plus any updates the transaction itself has made.

Now, there are a couple of approaches we can take at transaction
commit that I can think of right now:

1.  If there were any serialization errors, abort the transaction at
    commit time.  This allows transactions to safely make changes to their
    local view of the database without compromising serialization.  This
    probably wouldn't yield any benefits over the serializable isolation
    level except that it would make it possible to perform experiments
    on a database that currently can't be performed (since serializable
    isolation aborts the transaction at the first serialization
    error).  Applications would be easier to write since there would
    be only one point during a transaction that the application would
    have to check for unexpected errors: the commit.

2.  We commit the changed rows.  Updates only happen to rows that
    exist at commit time.  Rows which the transaction deleted and which
    still exist at commit time are deleted.  Referential integrity
    rules are enforced, of course, so it's possible for the database to
    retain some of its sanity even in the face of this model.  But the
    overall state of the database will probably be unpredictable (in
    this scenario, the last concurrent transaction to commit "wins",
    more or less).

3.  We do something more sophisticated than 1 or 2.  Perhaps something
    analogous to the branch merge functions that CVS and other concurrent
    version control systems can perform, where multiple branches are
    finally merged into a single unified source snapshot.  I have no idea
    how this would work for real, or if it's even possible (I believe
    CVS requires manual intervention to resolve branch conflicts during
    a merge, an option that would obviously not be available to us).

How useful would it be?  Beats me.  Like I said, you could perform
some "what if" games with a database this way that you currently
can't, but I don't know how useful that would be.  On thinking about
it a bit, it seems option 1 would be the most useful and perhaps the
most sensible.

Of course, perhaps the whole thing is just another stupid idea...

Re: 7.4 Wishlist

Kevin Brown <kevin@sysexperts.com> — 2002-12-06T11:16:34Z

Tom Lane wrote:
> "Magnus Naeslund(f)" <mag@fbab.net> writes:
> > Mysql is planning on making this work:
> >  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id.
> 
> We're supposed to spend our time emulating nonstandard features that
> don't even exist yet?  I think I have better things to do ...

MySQL doesn't have it, but I believe MS SQL does (and thus Sybase
probably does as well).

I agree with others that variables would be quite handy, especially if
they persist between statements (and they might be even handier if
they persist between transactions).

That's not to say that you don't have better things to work on,
though.  :-)

-- 
Kevin Brown					      kevin@sysexperts.com
This is your .signature virus: < begin 644 .signature (9V]T8VAA(0K0z end >
	    This is your .signature virus on drugs: <>
			Any questions?

Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-06T15:50:25Z

Kevin Brown wrote:
> How useful would it be?  Beats me.  Like I said, you could perform
> some "what if" games with a database this way that you currently
> can't, but I don't know how useful that would be.  On thinking about
> it a bit, it seems option 1 would be the most useful and perhaps the
> most sensible.
> 
> 
> Of course, perhaps the whole thing is just another stupid idea...

We would need to have some people who want this first.  We don't add
stuff of questionable value because then the feature set becomes
confusing to end users.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: 7.4 Wishlist

Stephen L. <jleelim@hotmail.com> — 2002-12-10T15:36:12Z

Hi, if I may add to the wishlist for 7.4 in order of importance. Some items
may have been mentioned or disputed already but I think they are quite
important:

1. Avoid needing REINDEX after large insert/deletes or make REINDEX not use
exclusive lock on table.
2. Automate VACUUM in background and make database more
interactive/responsive during long VACUUMs
3. Replication
4. Point-in-time recovery
5. Maintain automatic clustering (CLUSTER) even after subsequent
insert/updates.
6. Compression between client/server interface like in MySQL

Thanks,

Stephen
jleelim(at)hotmail.com

Re: 7.4 Wishlist

Greg Copeland <greg@copelandconsulting.net> — 2002-12-10T16:56:32Z

On Tue, 2002-12-10 at 09:36, Stephen L. wrote:
> 6. Compression between client/server interface like in MySQL
> 

Mammoth is supposed to be donating their compression efforts back to
this project, or so I've been told.  I'm not exactly sure of their
time-line as I've slept since my last conversation with them.  The
initial feedback that I've gotten back from them on this subject is that
the compression has been working wonderfully for them with excellent
results.  IIRC, in their last official release, they announced their
compression implementation.  So, I'd think that it would be available
for 7.4 of 7.5 time frame.

-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting

Re: [mail] Re: 7.4 Wishlist

Al Sutton <al@alsutton.com> — 2002-12-10T17:25:30Z

Would it be possible to make compression an optional thing, with the default
being off?

I'm in a position that many others may be in where the link between my app
server and my database server isn't the bottleneck, and thus any time spent
by the CPU performing compression and decompression tasks is CPU time that
is in effect wasted.

If a database is handling numerous small queries/updates and the
request/response packets are compressed individually, then the overhead of
compression and decompression may result in slower performance compared to
leaving the request/response packets uncompressed.

Al.

----- Original Message -----
From: "Greg Copeland" <greg@CopelandConsulting.Net>
To: "Stephen L." <jleelim@hotmail.com>
Cc: "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org>
Sent: Tuesday, December 10, 2002 4:56 PM
Subject: [mail] Re: [HACKERS] 7.4 Wishlist

> On Tue, 2002-12-10 at 09:36, Stephen L. wrote:
> > 6. Compression between client/server interface like in MySQL
> >
>
> Mammoth is supposed to be donating their compression efforts back to
> this project, or so I've been told.  I'm not exactly sure of their
> time-line as I've slept since my last conversation with them.  The
> initial feedback that I've gotten back from them on this subject is that
> the compression has been working wonderfully for them with excellent
> results.  IIRC, in their last official release, they announced their
> compression implementation.  So, I'd think that it would be available
> for 7.4 of 7.5 time frame.
>
>
> --
> Greg Copeland <greg@copelandconsulting.net>
> Copeland Computer Consulting
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: [mail] Re: 7.4 Wishlist

Greg Copeland <greg@copelandconsulting.net> — 2002-12-10T19:19:42Z

On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> Would it be possible to make compression an optional thing, with the default
> being off?
> 

I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
what appears.  What I originally had envisioned was a per database and
user permission model which would better control use.  Since compression
can be rather costly for some use cases, I also envisioned it being
negotiated where only the user/database combo with permission would be
able to turn it on.  I do recall that compression negotiation is part of
the Mammoth implementation but I don't know if it's a simple capability
negotiation or part of a larger scheme.

The reason I originally imagined a user/database type approach is
because I would think only a subset of a typical installation would be
needing compression.  As such, this would help prevent users from
arbitrarily chewing up database CPU compressing data because:
	o datasets are uncompressable or poorly compresses
	o environment cpu is at a premium
	o is in a bandwidth rich environment

> I'm in a position that many others may be in where the link between my app
> server and my database server isn't the bottleneck, and thus any time spent
> by the CPU performing compression and decompression tasks is CPU time that
> is in effect wasted.

Agreed.  This is why I'd *guess* that Mammoth's implementation does not
force compression.

> 
> If a database is handling numerous small queries/updates and the
> request/response packets are compressed individually, then the overhead of
> compression and decompression may result in slower performance compared to
> leaving the request/response packets uncompressed.

Again, this is where I'm gray on their exact implementation.  It's
possible they implemented a compressed stream even though I'm hoping
they implemented a per packet compression scheme (because adaptive
compression becomes much more capable and powerful; in both
algorithmically and logistical use).  An example of this would be to
avoid any compression on trivially sized result sets. Again, this is
another area where I can imagine some tunable parameters.

Just to be on the safe side, I'm cc'ing Josh Drake at Command Prompt
(Mammoth) to see what they can offer up on it.  Hope you guys don't
mind.

Greg

> ----- Original Message -----
> From: "Greg Copeland" <greg@CopelandConsulting.Net>
> To: "Stephen L." <jleelim@hotmail.com>
> Cc: "PostgresSQL Hackers Mailing List" <pgsql-hackers@postgresql.org>
> Sent: Tuesday, December 10, 2002 4:56 PM
> Subject: [mail] Re: [HACKERS] 7.4 Wishlist
> 
> 
> > On Tue, 2002-12-10 at 09:36, Stephen L. wrote:
> > > 6. Compression between client/server interface like in MySQL
> > >
> >
> > Mammoth is supposed to be donating their compression efforts back to
> > this project, or so I've been told.  I'm not exactly sure of their
> > time-line as I've slept since my last conversation with them.  The
> > initial feedback that I've gotten back from them on this subject is that
> > the compression has been working wonderfully for them with excellent
> > results.  IIRC, in their last official release, they announced their
> > compression implementation.  So, I'd think that it would be available
> > for 7.4 of 7.5 time frame.
> >
> >
> > --
> > Greg Copeland <greg@copelandconsulting.net>
> > Copeland Computer Consulting
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting

Re: [mail] Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-10T19:38:41Z

Greg Copeland wrote:
> On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> > Would it be possible to make compression an optional thing, with the default
> > being off?
> > 
> 
> I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
> what appears.  What I originally had envisioned was a per database and
> user permission model which would better control use.  Since compression
> can be rather costly for some use cases, I also envisioned it being
> negotiated where only the user/database combo with permission would be
> able to turn it on.  I do recall that compression negotiation is part of
> the Mammoth implementation but I don't know if it's a simple capability
> negotiation or part of a larger scheme.

I haven't heard anything about them contributing it.  Doesn't mean it
will not happen, just that I haven't heard it.

I am not excited about per-db/user compression because of the added
complexity of setting it up, and even set up, I can see cases where some
queries would want it, and others not.  I can see using GUC to control
this.  If you enable it and the client doesn't support it, it is a
no-op.  We have per-db and per-user settings, so GUC would allow such
control if you wish.

Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
meaning it would determine if there was value in the compression and do
it only when it would help.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [mail] Re: 7.4 Wishlist

Greg Copeland <greg@copelandconsulting.net> — 2002-12-10T20:06:23Z

On Tue, 2002-12-10 at 13:38, Bruce Momjian wrote:

> I haven't heard anything about them contributing it.  Doesn't mean it
> will not happen, just that I haven't heard it.
> 

This was in non-mailing list emails that I was told this by Joshua Drake
at Command Prompt.  Of course, that doesn't have to mean it will be
donated for sure but nonetheless, I was told it will be.

Here's a quote from one of the emails.  I don't think I'll be too far
out of line posting this.  On August 9, 2002, Joshua Drake said, "One we
plan on releasing this code to the developers after 7.3 comes out. We
want to be good members of the community but we have to keep a slight
commercial edge (wait to you see what we are going to do to vacuum)."

Obviously, I don't think that was official speak, so I'm not holding
them to the fire, nonetheless, that's what was said.  Additional follow
ups did seem to imply that they were very serious about this and REALLY
want to play nice as good shared source citizens.

> I am not excited about per-db/user compression because of the added
> complexity of setting it up, and even set up, I can see cases where some
> queries would want it, and others not.  I can see using GUC to control
> this.  If you enable it and the client doesn't support it, it is a
> no-op.  We have per-db and per-user settings, so GUC would allow such
> control if you wish.
> 

I never thought beyond the need for what form an actual implementation
of this aspect would look like.  The reason for such a concept would be
to simply limit the number of users that can be granted compression.  If
you have a large user base all using compression or even a small user
base where very large result sets are common, I can imagine your
database server becoming CPU bound.  The database/user thinking was an
effort to allow the DBA to better manage the CPU effect.

> Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> meaning it would determine if there was value in the compression and do
> it only when it would help.

Yes, that makes sense and was something I had originally envisioned. 
Simply stated, some installations may never want compression while
others may want it for every connection.  Beyond that, I believe there
needs to be something of a happy medium where a DBA can better control
who and what is taking his CPU away (e.g. only that one remote location
being fed via ISDN).  If GUC can fully satisfy, I certainly won't argue
against it.

-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting

Re: [mail] Re: 7.4 Wishlist

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-12-10T20:13:19Z

Yes, the issue was that give our TODO list, compressed transfer wasn't
very high, and it was unknown how valuable it would be.  However, if it
were contributed, we could easily test its value with little work on our
part and include the code if it were a win.

---------------------------------------------------------------------------

Joshua D. Drake wrote:
> Hello,
> 
>    We would probably be open to contributing it if there was interest. 
> There wasn't interest initially.
> 
> Sincerely,
> 
> Joshua Drake
> 
> 
> Bruce Momjian wrote:
> > Greg Copeland wrote:
> > 
> >>On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> >>
> >>>Would it be possible to make compression an optional thing, with the default
> >>>being off?
> >>>
> >>
> >>I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
> >>what appears.  What I originally had envisioned was a per database and
> >>user permission model which would better control use.  Since compression
> >>can be rather costly for some use cases, I also envisioned it being
> >>negotiated where only the user/database combo with permission would be
> >>able to turn it on.  I do recall that compression negotiation is part of
> >>the Mammoth implementation but I don't know if it's a simple capability
> >>negotiation or part of a larger scheme.
> > 
> > 
> > I haven't heard anything about them contributing it.  Doesn't mean it
> > will not happen, just that I haven't heard it.
> > 
> > I am not excited about per-db/user compression because of the added
> > complexity of setting it up, and even set up, I can see cases where some
> > queries would want it, and others not.  I can see using GUC to control
> > this.  If you enable it and the client doesn't support it, it is a
> > no-op.  We have per-db and per-user settings, so GUC would allow such
> > control if you wish.
> > 
> > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> > meaning it would determine if there was value in the compression and do
> > it only when it would help.
> > 
> 
> -- 
> <COMPANY>CommandPrompt	- http://www.commandprompt.com	</COMPANY>
> <CONTACT>       <PHONE>+1.503.222-2783</PHONE>          </CONTACT>
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [mail] Re: 7.4 Wishlist

Kyle <kaf@nwlink.com> — 2002-12-11T00:45:51Z

Without getting into too many details, why not send toast data to
non-local clients?  Seems that would be the big win.  The data is
already compressed, so the server wouldn't pay cpu time to recompress
anything.  And since toast data is relatively large anyway, it's the
stuff you'd want to compress before putting it on the wire anyway.

If this is remotely possible let me know, I might be interested in
taking a look at it.

-Kyle

Bruce Momjian wrote:
> 
> I am not excited about per-db/user compression because of the added
> complexity of setting it up, and even set up, I can see cases where some
> queries would want it, and others not.  I can see using GUC to control
> this.  If you enable it and the client doesn't support it, it is a
> no-op.  We have per-db and per-user settings, so GUC would allow such
> control if you wish.
> 
> Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> meaning it would determine if there was value in the compression and do
> it only when it would help.

Re: [mail] Re: 7.4 Wishlist

Greg Copeland <greg@copelandconsulting.net> — 2002-12-11T01:15:54Z

This has been brought up a couple of times now.  Feel free to search the
old archives for more information.  IIRC, it would of made the
implementation more problematic, or so I think it was said.

When I originally brought the topic (compression) up, it was not well
received.  As such, it may of been thought that additional effort on
such an implementation would not be worth the return on a feature which
most seemingly didn't see any purpose in supporting in the first place. 
You need to keep in mind that many simply advocated using a compressing
ssh tunnel.

Seems views may of changed some since then so it may be worth
revisiting.  Admittedly, I have no idea what would be required to move
the toast data all the way through like that.  Any idea?  Implementing a
compression stream (which seems like what was done for Mammoth) or even
packet level compression were both something that I could comfortably
put my arms around in a timely manner.  Moving toast data around wasn't.

Greg

On Tue, 2002-12-10 at 18:45, Kyle wrote:
> Without getting into too many details, why not send toast data to
> non-local clients?  Seems that would be the big win.  The data is
> already compressed, so the server wouldn't pay cpu time to recompress
> anything.  And since toast data is relatively large anyway, it's the
> stuff you'd want to compress before putting it on the wire anyway.
> 
> If this is remotely possible let me know, I might be interested in
> taking a look at it.
> 
> -Kyle
> 
> Bruce Momjian wrote:
> > 
> > I am not excited about per-db/user compression because of the added
> > complexity of setting it up, and even set up, I can see cases where some
> > queries would want it, and others not.  I can see using GUC to control
> > this.  If you enable it and the client doesn't support it, it is a
> > no-op.  We have per-db and per-user settings, so GUC would allow such
> > control if you wish.
> > 
> > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> > meaning it would determine if there was value in the compression and do
> > it only when it would help.

-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting

Re: [mail] Re: 7.4 Wishlist

Al Sutton <al@alsutton.com> — 2002-12-11T07:16:10Z

I'd like to show/register interest.

I can see it being very useful when combined with replication for situations
where the replicatiant databases are geographically seperated (i.e. Disaster
Recover sites or systems maintaining replicants in order to reduce the
distance from user to app to database). The bandwidth cost savings from
compressing the replication information would be immensly useful.

Al.

----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "Greg Copeland" <greg@CopelandConsulting.Net>; "Al Sutton"
<al@alsutton.com>; "Stephen L." <jleelim@hotmail.com>; "PostgresSQL Hackers
Mailing List" <pgsql-hackers@postgresql.org>
Sent: Tuesday, December 10, 2002 8:04 PM
Subject: Re: [mail] Re: [HACKERS] 7.4 Wishlist


> Hello,
>
>    We would probably be open to contributing it if there was interest.
> There wasn't interest initially.
>
> Sincerely,
>
> Joshua Drake
>
>
> Bruce Momjian wrote:
> > Greg Copeland wrote:
> >
> >>On Tue, 2002-12-10 at 11:25, Al Sutton wrote:
> >>
> >>>Would it be possible to make compression an optional thing, with the
default
> >>>being off?
> >>>
> >>
> >>I'm not sure.  You'd have to ask Command Prompt (Mammoth) or wait to see
> >>what appears.  What I originally had envisioned was a per database and
> >>user permission model which would better control use.  Since compression
> >>can be rather costly for some use cases, I also envisioned it being
> >>negotiated where only the user/database combo with permission would be
> >>able to turn it on.  I do recall that compression negotiation is part of
> >>the Mammoth implementation but I don't know if it's a simple capability
> >>negotiation or part of a larger scheme.
> >
> >
> > I haven't heard anything about them contributing it.  Doesn't mean it
> > will not happen, just that I haven't heard it.
> >
> > I am not excited about per-db/user compression because of the added
> > complexity of setting it up, and even set up, I can see cases where some
> > queries would want it, and others not.  I can see using GUC to control
> > this.  If you enable it and the client doesn't support it, it is a
> > no-op.  We have per-db and per-user settings, so GUC would allow such
> > control if you wish.
> >
> > Ideally, it would be a tri-valued parameter, that is ON, OFF, or AUTO,
> > meaning it would determine if there was value in the compression and do
> > it only when it would help.
> >
>
> --
> <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY>
> <CONTACT>       <PHONE>+1.503.222-2783</PHONE>          </CONTACT>
>
>