Re: 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

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

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(at)familyhealth(dot)com(dot)au>
To: <pgsql-hackers(at)postgresql(dot)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
>

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

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

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

> 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

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

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

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;

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

"Nicolai Tufar" <ntufar(at)apb(dot)com(dot)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

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)

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(at)apb(dot)com(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

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(at)samurai(dot)com> || PGP Key ID: DB3C29FC

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(at)samurai(dot)com> || PGP Key ID: DB3C29FC

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

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

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(at)orlandi(dot)com>
To: <pgsql-hackers(at)postgresql(dot)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(at)postgresql(dot)org)
>

> 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(at)kakidata(dot)dk
2000 Frederiksberg Lørdag 12.00-16.00 Web: www.suse.dk

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(at)familyhealth(dot)com(dot)au>
To: <pgsql-hackers(at)postgresql(dot)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/

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

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

IBM --> Immer Backup Machen

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

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

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(at)tm(dot)ee>

Nicolai Tufar <ntufar(at)apb(dot)com(dot)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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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.

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(at)candle(dot)pha(dot)pa(dot)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

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(at)tm(dot)ee>

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(at)postgresql(dot)org)
>

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

> * 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(at)crimelabs(dot)net pgp:crimelabs.net/bpalmer.pgp5

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(at)rbt(dot)ca>

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 !

----- Original Message -----
From: "Hannu Krosing" <hannu(at)tm(dot)ee>
To: "Nicolai Tufar" <ntufar(at)apb(dot)com(dot)tr>
Cc: <pgsql-hackers(at)postgresql(dot)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

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

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(at)wheeler(dot)net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org

Cross-db queries.

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

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

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(at)wheeler(dot)net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org

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

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",

> 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

----- Original Message -----
From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Nicolai Tufar" <ntufar(at)apb(dot)com(dot)tr>; <pgsql-hackers(at)postgresql(dot)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.

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(at)samurai(dot)com> || PGP Key ID: DB3C29FC

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

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(at)\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

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
{

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(at)wheeler(dot)net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org

Nicolai Tufar <ntufar(at)apb(dot)com(dot)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

"Nicolai Tufar" <ntufar(at)apb(dot)com(dot)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

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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.

Nicolai Tufar wrote:
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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

Joe Conway <mail(at)joeconway(dot)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

Joe Conway <mail(at)joeconway(dot)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

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Joe Conway <mail(at)joeconway(dot)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
>

Nicolai Tufar wrote:
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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

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

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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.

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.

"Matthew T. O'Connor" <matthew(at)zeut(dot)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(at)CERT(dot)Uni-Stuttgart(dot)DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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

"Nicolai Tufar" <ntufar(at)apb(dot)com(dot)tr> writes:
> From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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)

On Sun, 1 Dec 2002, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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.

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)wheeler(dot)net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org

> > 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(at)rbt(dot)ca>

> > 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(at)rbt(dot)ca>

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

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.

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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

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

Catch all the cricket action. Download Yahoo! Score tracker

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

> 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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

> 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

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(at)\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(at)postgresql(dot)org
>
>

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

> Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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;

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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

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

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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.

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(at)tm(dot)ee>

Hannu Krosing <hannu(at)tm(dot)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

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

> 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

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

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(at)mascari(dot)com

> 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

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

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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)\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(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

> 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

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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.

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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

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...). :)

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

"Magnus Naeslund(f)" <mag(at)fbab(dot)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

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

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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

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(at)frw(dot)uva(dot)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
>

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

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(at)tm(dot)ee>

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(at)tm(dot)ee>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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

Ewe, yea.

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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)tm(dot)ee>
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > message can get through to the mailing list cleanly
> > >
> --
> Hannu Krosing <hannu(at)tm(dot)ee>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

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

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