Saturday, September 20, 2008

Re: [GENERAL] PDF Documentation for 8.3?

Kevin Hunter wrote:
> At 2:56pm -0400 on Fri, 19 Sep 2008, Michelle Konzack wrote:
>> I was searching the site but there are no PDF's for 8.3 in format A4 or
>> do I missing something?
>
> http://www.postgresql.org/docs/manuals/
>
> Hmm, this page seems to advertise both US Letter and A4. A cursory
> inspection suggests that the A4 document at least has larger pages and
> less of them ...
>

First, I have been drinking so if this sounds stupid, blame the vodka
:). Second, A4 is larger than letter so it would have less pages would
it not?

Joshua D. Drake


> Kevin
>


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

[COMMITTERS] varint - varint: Initial commit of an arbitrary precision unsigned

Log Message:
-----------
Initial commit of an arbitrary precision unsigned integer data type with
bitwise operators.

The following operators are implemented so far (not optimally, but I'm not
worried about that yet):
& (bitwise and)
| (bitwise or)
^ (bitwise xor)
<< (bitwise left shift)
>> (bitwise right shift)
+ (arithmetic add)
- (arithmetic subtract)
* (arithmetic multiply)

I plan to implement comparison operators and division next.

Added Files:
-----------
varint:
Makefile (r1.1)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/varint/varint/Makefile?rev=1.1&content-type=text/x-cvsweb-markup)
varint.c (r1.1)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/varint/varint/varint.c?rev=1.1&content-type=text/x-cvsweb-markup)
varint.h (r1.1)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/varint/varint/varint.h?rev=1.1&content-type=text/x-cvsweb-markup)
varint.sql.in (r1.1)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/varint/varint/varint.sql.in?rev=1.1&content-type=text/x-cvsweb-markup)

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

[COMMITTERS] varint - varint: New Directory

Update of /cvsroot/varint/varint
In directory pgfoundry.org:/tmp/cvs-serv46248/varint

Log Message:
Directory /cvsroot/varint/varint added to the repository


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

Re: [GENERAL] PDF Documentation for 8.3?

At 2:56pm -0400 on Fri, 19 Sep 2008, Michelle Konzack wrote:
> I was searching the site but there are no PDF's for 8.3 in format A4 or
> do I missing something?

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

Hmm, this page seems to advertise both US Letter and A4. A cursory
inspection suggests that the A4 document at least has larger pages and
less of them ...

Kevin

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

Re: [HACKERS] Foreign key constraint for array-field?

David Fetter wrote:
> On Sun, Sep 21, 2008 at 04:38:56AM +0400, Dmitry Koterov wrote:
>> Hello.
>>
>> Is it possible to create a foreign key constraint for ALL elements of
>> an array field?
>
> Whether it's possible or not--it probably is--it's a very bad idea.
> Just normalize :)

+1

>
> Cheers,
> David.


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Magnus Hagander wrote:
>>> Are we ready enough to actually put a *timeline* on the website?
>
>> I would think so. IMO:
>> 3 years - Maintenance mode only
>> 5 years - End of life
>
> I'm not really in favor of a one-size-fits-all approach to this.
> Our various releases have had different levels of uptake and don't
> necessarily all deserve the same support lifespan.
>
> Case in point: we already obsoleted 8.0 and 8.1 for our Windows users.
> How sensible is it to argue that they'll deserve a lifespan equivalent
> to 8.2's?

I believe those are different arguments though. The Windows product is
still a relatively young and immature release. Our nix product is not.
Besides there is always an exception to the rule :).

I have no problem with, unless otherwise specified...

>
> The above numbers seem reasonable as a rough guideline, but I think the
> actual decisions will need to be taken on a release-by-release basis.
>

Nod.

Joshua D. Drake


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

Re: [HACKERS] Foreign key constraint for array-field?

On Sun, Sep 21, 2008 at 04:38:56AM +0400, Dmitry Koterov wrote:
> Hello.
>
> Is it possible to create a foreign key constraint for ALL elements of
> an array field?

Whether it's possible or not--it probably is--it's a very bad idea.
Just normalize :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

Tatsuo Ishii <ishii@postgresql.org> writes:
>> PlanState.has_recursivescan seems like a complete kluge. Can't it just be
>> removed? It looks to me like it is working around bugs that hopefully aren't
>> there anymore. There is certainly no reason why a recursive CTE should be
>> more in need of rescanning than any other kind of plan.

> I don't think so. Recursion plan needs the hash table used by sublan
> be re-created at each recursion loop stage. Remember that in each
> evaluation of recursive plan, the recursive name is replaced by a
> working table which is holding previous evalution result of recursion
> stage. Thus the hash table corresponding to the work table needs to
> be re-created.

Oh, I see. I keep getting confused about whether RecursiveScan is at the
top or the bottom of the recursion plan tree :-(. Maybe it would help
to use a different name for it? RecursionInjector or something like
that?

>> If it is needed then
>> the current implementation is completely broken anyway, since it would only
>> detect a RecursiveScan node that is directly underneath an agg or hash node.

> Yeah, that's right. What I have in my mind is to implement something
> similar to UpdateChangedParamSet family like mechanism which will
> inherit working table change event to child node.

I think it could actually *be* UpdateChangedParamSet, if you just
associate some otherwise-unused Param with each RecursiveScan node,
and have the Recursion node signal a change of that Param when it
revises the work table.

In fact, why not combine that with getting rid of the klugy addition to
ExecutorState? Make the param be actually useful: it could contain
some internal datastructure that passes the work table down to the
RecursiveScan node.

regards, tom lane

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

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> PlanState.has_recursivescan seems like a complete kluge. Can't it just be
> removed? It looks to me like it is working around bugs that hopefully aren't
> there anymore. There is certainly no reason why a recursive CTE should be
> more in need of rescanning than any other kind of plan.

I don't think so. Recursion plan needs the hash table used by sublan
be re-created at each recursion loop stage. Remember that in each
evaluation of recursive plan, the recursive name is replaced by a
working table which is holding previous evalution result of recursion
stage. Thus the hash table corresponding to the work table needs to
be re-created.

> If it is needed then
> the current implementation is completely broken anyway, since it would only
> detect a RecursiveScan node that is directly underneath an agg or hash node.

Yeah, that's right. What I have in my mind is to implement something
similar to UpdateChangedParamSet family like mechanism which will
inherit working table change event to child node.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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

Re: [GENERAL] Getting several columns from subselect with LIMIT 1

On Sat, Sep 20, 2008 at 11:36 AM, Pierre Thibaudeau <pierdeux@gmail.com> wrote:
> I have a PERSONS table.
> I also have a PROGENY table, which is a many-to-many association table
> with two foreign keys to the PERSONS table to itself.
> (In this day and age, not only can an individual have any number of
> children, but also a person can have any number of parents! At least,
> let's assume that's true for the sake of this setup.)
>
> Suppose I wish to construct a view of the persons, along with the name
> of their first-born (if they have one; NULL otherwise). The
> following SELECT does just that:
>
> SELECT
> persons.*,
> (
> SELECT child.name
> FROM progeny JOIN persons child ON child.id = progeny.child
> WHERE progeny.parent = persons.id
> ORDER BY child.birthdate ASC
> LIMIT 1
> ) AS firstborn_name
> FROM persons;
>
> Now, this is probably not the most elegant piece of code, but the real
> problem is that
> I cannot see how to extend it to the case where I want not only the
> firstborn's name but also the firstborn's ID
> (short of repeating the entire subselect a second time). At the
> moment, with this current syntax, my subSELECT statement would not be
> allowed to return more than a single column.

SELECT (person).*, (progeny).* from
(
select persons as person,
(
SELECT progeny
FROM progeny JOIN persons child ON child.id = progeny.child
WHERE progeny.parent = persons.id
ORDER BY child.birthdate ASC
LIMIT 1
) AS firstborn
FROM persons;
) q;

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

Re: [GENERAL] offtopic, about subject prefix

On Fri, Sep 19, 2008 at 3:01 PM, Michelle Konzack
<linux4michelle@tamay-dogan.net> wrote:
> Am 2008-09-03 13:33:05, schrieb Fernando Moreno:
>> Hello, I'm new to this mailing list, and I have a couple of questions:
>>
>> Is it really necessary to add the [GENERAL] prefix?
>
> No it is not since the PostgreQL Lists can be filtered perfectly with:
>
> ----[ /usr/share/tdtools-procmail/ML_pgsql ]----------------------------

A lot of people don't have a way to run procmail and have crappy
mailers that can't filter on arbitrary headers. I guess we're trying
to be nice to them.

-Doug

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

Re: [HACKERS] [patch] fix dblink security hole

Joe Conway <mail@joeconway.com> writes:
> Good point -- I'll look into that and post something tomorrow. How does
> "requirepassword" sound for the option? It is consistent with
> "requiressl" but a bit long and hard to read. Maybe "require_password"?

Well, no, because it's not requiring a password.

Perhaps "ignore_pgpass"?

[ looks at code a moment... ] Actually, there's another possibility.
I see that the code already allows the location of .pgpass to be
specified via the environment variable PGPASSFILE, but very
non-orthogonally fails to have an equivalent conninfo option.
So here's a more concrete proposal: fix it so that pgpassfile is
also a conninfo option, and allow "pgpassfile = none" to silently
suppress use of the pgpass file. (You could almost get there today
with putenv("PGPASSFILE=/dev/null"), except that (a) it would generate
complaints in the postmaster log, and (b) we probably don't want dblink
messing up the backend environment settings for possible other uses
of libpq.)

BTW, a possible hole in this scheme would be if a user could supply a
conninfo string that was intentionally malformed in a way that would
cause a tacked-on pgpassfile option to be ignored by libpq. We might
need to add some validity checks to dblink, or tighten libpq's own
checks.

regards, tom lane

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

Re: [HACKERS] Foreign key constraint for array-field?

On Sat, Sep 20, 2008 at 8:38 PM, Dmitry Koterov <dmitry@koterov.ru> wrote:
> Hello.
>
> Is it possible to create a foreign key constraint for ALL elements of
> an array field?
>
> CREATE TABLE a(id INTEGER);
> CREATE TABLE b(id INTEGER, a_ids INTEGER[]);
>
> Field b.a_ids contains a list of ID's of "a" table. I want to ensure
> that each element in b.a_ids exists in a in any time. Is it possible
> to create an automatic foreign key?

Well, it is possible to basically do this with triggers. However,
ISTM you are doing something that is much easier done with a map
table:

create table a_b_map
(
a_id int references a(a_id),
b_id int references b(b_id),
primary key(a_id, b_id)
);

Also, I would suggest not using columns named 'id' (as in the above
example). For various reasons, it creates a mess.

merlin

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

Re: [HACKERS] [patch] fix dblink security hole

Tom Lane wrote:
> I think there is an alternative solution, if we are only going to patch
> this in 8.4 and up: provide a new libpq conninfo-string option saying
> not to use .pgpass, and have dblink add that to the passed-in conninfo
> string instead of trying to check after the fact. Then we aren't
> changing dblink's API at all, only replacing a leaky security check
> with a better one.

Good point -- I'll look into that and post something tomorrow. How does
"requirepassword" sound for the option? It is consistent with
"requiressl" but a bit long and hard to read. Maybe "require_password"?

Joe

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

Re: [HACKERS] Predictable order of SQL commands in pg_dump

"Dmitry Koterov" <dmitry@koterov.ru> writes:
> Utility pg_dump dumps the identical database schemas not always
> identically: sometimes it changes an order of SQL statements.

Please provide a concrete example. The dump order for modern servers
(ie, since 7.3) is by object type, and within a type by object name,
except where another order is forced by dependencies. And there is no
random component to the dependency solver ;-). So it should be
behaving the way you want.

regards, tom lane

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

[HACKERS] Predictable order of SQL commands in pg_dump

Hello.

Utility pg_dump dumps the identical database schemas not always
identically: sometimes it changes an order of SQL statements.
E.g.:

1. Dump of database A:

ALTER TABLE xxx ADD CONSTRAINT ...;
ALTER TABLE yyy ADD CONSTRAINT ...;

2. Dump of database B which has identical structure as A ("pg_dump A |
psql -d B" was executed)

ALTER TABLE yyy ADD CONSTRAINT ...;
ALTER TABLE xxx ADD CONSTRAINT ...;

This behaviour is not good, because I cannot execute diff to visually
view what was changed between databases A and B. (I use this diff only
for visual detection, please do not refer I want to use this diff for
schema migration - I don't want it!).

Is it possible to make pg_dump more predictable in SQL ordering?
What order does it use by default?

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

Re: [HACKERS] [patch] fix dblink security hole

Joe Conway <mail@joeconway.com> writes:
> I took a look and can partially see Marko's point. The scenario exists
> within this context:

> 1. "superuser" installs dblink on db1, running on postgres server
> under the "superuser" account
> 2. "superuser" has .pgpass file
> 3. the "superuser" .pgpass file is set up with wildcards, e.g.
> *:*:*:postgres:mypassword
> 4. "superuser" creates login for "luser" in db1

> This depends on "superuser" to not only make use of .pgpass, but
> specifically to use it in an insecure way, i.e. using wildcards to
> specify that the login credentials should be sent to any arbitrary
> Postgres installation.

It seems to me that this is a pretty far-fetched scenario; someone
who'd set up his .pgpass that way would be at risk from his own typos,
not just from nefarious users. I'm not sure how far out of our way we
need to go to protect stupid DBAs. But anyway:

The main thing that bothers me about the proposed patch is that it takes
away the security mechanism that existed before. Now you have either no
trust or 100% trust, you don't have the option to trust people who know
a password. That's less secure, not more, if you ask me. Marko's
original patch is just as bad.

If I understand the complaint correctly, it is not that a luser can make
a connection, it is that the password will be sent before dblink rejects
the connection. So really this problem is not specific to dblink ---
what it's saying is that PQconnectionUsedPassword is broken by design
and we should deprecate using that for security purposes.

I think there is an alternative solution, if we are only going to patch
this in 8.4 and up: provide a new libpq conninfo-string option saying
not to use .pgpass, and have dblink add that to the passed-in conninfo
string instead of trying to check after the fact. Then we aren't
changing dblink's API at all, only replacing a leaky security check
with a better one.

regards, tom lane

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

[HACKERS] Foreign key constraint for array-field?

Hello.

Is it possible to create a foreign key constraint for ALL elements of
an array field?

CREATE TABLE a(id INTEGER);
CREATE TABLE b(id INTEGER, a_ids INTEGER[]);

Field b.a_ids contains a list of ID's of "a" table. I want to ensure
that each element in b.a_ids exists in a in any time. Is it possible
to create an automatic foreign key?

According to http://www.postgresql.org/docs/current/static/catalog-pg-constraint.html
, seems to me it is possible if I create a custom entry in
pg_constraint with my custom conpfeqop, conppeqop and conffeqop
fields.

Am I right?

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

[pgsql-es-ayuda] Obtener los 3 valores mas altos por grupo de una serie de registros

Alguien tiene idea de como armar una consulta sql (si es que se puede)
para obtener los 3 valores máximos y agrupandolos por una determinada
columna, por ejemplo teniendo los siguientes datos:

grupo, valor
1, 2
1, 3
1, 4
1, 5
2, 1
2, 10
2, 11
2, 5

me tendría que devolver
1, 5
1, 4
1, 3
2, 11
2, 10
2, 5

Saludos y gracias, Alejandro
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [HACKERS] [patch] fix dblink security hole

Index: dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.c,v
retrieving revision 1.74
diff -c -r1.74 dblink.c
*** dblink.c 3 Jul 2008 03:56:57 -0000 1.74
--- dblink.c 10 Aug 2008 04:59:05 -0000
***************
*** 93,99 ****
static HeapTuple get_tuple_of_interest(Oid relid, int2vector *pkattnums, int16 pknumatts, char **src_pkattvals);
static Oid get_relid_from_relname(text *relname_text);
static char *generate_relation_name(Oid relid);
! static void dblink_security_check(PGconn *conn, remoteConn *rconn);
static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);

/* Global */
--- 93,99 ----
static HeapTuple get_tuple_of_interest(Oid relid, int2vector *pkattnums, int16 pknumatts, char **src_pkattvals);
static Oid get_relid_from_relname(text *relname_text);
static char *generate_relation_name(Oid relid);
! static void dblink_security_check(void);
static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);

/* Global */
***************
*** 164,169 ****
--- 164,170 ----
} \
else \
{ \
+ dblink_security_check(); \
connstr = conname_or_str; \
conn = PQconnectdb(connstr); \
if (PQstatus(conn) == CONNECTION_BAD) \
***************
*** 175,181 ****
errmsg("could not establish connection"), \
errdetail("%s", msg))); \
} \
- dblink_security_check(conn, rconn); \
freeconn = true; \
} \
} while (0)
--- 176,181 ----
***************
*** 229,234 ****
--- 229,237 ----

if (connname)
rconn = (remoteConn *) palloc(sizeof(remoteConn));
+
+ /* only connect if superuser */
+ dblink_security_check();
conn = PQconnectdb(connstr);

MemoryContextSwitchTo(oldcontext);
***************
*** 246,254 ****
errdetail("%s", msg)));
}

- /* check password used if not superuser */
- dblink_security_check(conn, rconn);
-
if (connname)
{
rconn->conn = conn;
--- 249,254 ----
***************
*** 2232,2253 ****
}

static void
! dblink_security_check(PGconn *conn, remoteConn *rconn)
{
if (!superuser())
{
! if (!PQconnectionUsedPassword(conn))
! {
! PQfinish(conn);
! if (rconn)
! pfree(rconn);
!
! ereport(ERROR,
! (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
! errmsg("password is required"),
! errdetail("Non-superuser cannot connect if the server does not request a password."),
! errhint("Target server's authentication method must be changed.")));
! }
}
}

--- 2232,2246 ----
}

static void
! dblink_security_check()
{
if (!superuser())
{
! ereport(ERROR,
! (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED),
! errmsg("superuser is required"),
! errdetail("Non-superuser cannot connect remotely."),
! errhint("Use dblink_connect_u to connect as superuser.")));
}
}

Index: dblink.sql.in
===================================================================
RCS file: /opt/src/cvs/pgsql/contrib/dblink/dblink.sql.in,v
retrieving revision 1.17
diff -c -r1.17 dblink.sql.in
*** dblink.sql.in 5 Apr 2008 02:44:42 -0000 1.17
--- dblink.sql.in 11 Aug 2008 03:44:34 -0000
***************
*** 3,10 ****
-- Adjust this setting to control where the objects get created.
SET search_path = public;

! -- dblink_connect now restricts non-superusers to password
! -- authenticated connections
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
--- 3,9 ----
-- Adjust this setting to control where the objects get created.
SET search_path = public;

! -- dblink_connect now rejects all non-superusers
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
***************
*** 16,23 ****
LANGUAGE C STRICT;

-- dblink_connect_u allows non-superusers to use
! -- non-password authenticated connections, but initially
! -- privileges are revoked from public
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
--- 15,21 ----
LANGUAGE C STRICT;

-- dblink_connect_u allows non-superusers to use
! -- connections, but initially privileges are revoked from public
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
***************
*** 202,204 ****
--- 200,229 ----
RETURNS text
AS 'MODULE_PATHNAME', 'dblink_error_message'
LANGUAGE C STRICT;
+
+ -- dblink_u and dblink_exec_u allows non-superusers to use
+ -- connect strings, but initially privileges are revoked from public
+
+ CREATE OR REPLACE FUNCTION dblink_u (text, text)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C STRICT SECURITY DEFINER;
+ REVOKE ALL ON FUNCTION dblink_u (text, text) FROM public;
+
+ CREATE OR REPLACE FUNCTION dblink_u (text, text, boolean)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record'
+ LANGUAGE C STRICT SECURITY DEFINER;
+ REVOKE ALL ON FUNCTION dblink_u (text, text, boolean) FROM public;
+
+ CREATE OR REPLACE FUNCTION dblink_exec_u (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec'
+ LANGUAGE C STRICT SECURITY DEFINER;
+ REVOKE ALL ON FUNCTION dblink_exec_u (text, text) FROM public;
+
+ CREATE OR REPLACE FUNCTION dblink_exec_u (text, text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec'
+ LANGUAGE C STRICT SECURITY DEFINER;
+ REVOKE ALL ON FUNCTION dblink_exec_u (text, text, boolean) FROM public;
I'm clearly out of practice -- this time with the attachment
------------------------------------------------------------

Marko Kreen wrote:
> In addition to breaking standard security policy, dblink exposes
> .pgpass/pg_service.conf contents of the OS user database is running
> under to the non-privileged database user. (Esp. passwords)

I took a look and can partially see Marko's point. The scenario exists
within this context:

1. "superuser" installs dblink on db1, running on postgres server
under the "superuser" account
2. "superuser" has .pgpass file
3. the "superuser" .pgpass file is set up with wildcards, e.g.
*:*:*:postgres:mypassword
4. "superuser" creates login for "luser" in db1

This depends on "superuser" to not only make use of .pgpass, but
specifically to use it in an insecure way, i.e. using wildcards to
specify that the login credentials should be sent to any arbitrary
Postgres installation.

So although it may make sense to lock this down for 8.4, I don't agree
with backporting it due to the backward compatibility hit. Also, I think
we still need a way that people who don't allow real end-users directly
in their databases and don't care about Marko's threat scenario can get
their work done with minimal pain.

Attached is my version of a more complete patch. It aims to prevent any
dblink connection by non-superusers. But it also creates "_u" versions
of dblink() and dblink_exec(), and initially revokes privileges from
public in a similar vain. dblink_u(), dblink_exec_u (), and the
previously created dblink_connect_u() are all SECURITY_DEFINER functions
that can be granted to trusted non-superuser logins.

Beyond Marko and I, no one else has publicly weighed in on this. If I
don't hear any objections, I'll apply to cvs HEAD *only* in about 24 hours.

Thanks,

Joe

Re: [HACKERS] [patch] fix dblink security hole

Marko Kreen wrote:
> In addition to breaking standard security policy, dblink exposes
> .pgpass/pg_service.conf contents of the OS user database is running
> under to the non-privileged database user. (Esp. passwords)

I took a look and can partially see Marko's point. The scenario exists
within this context:

1. "superuser" installs dblink on db1, running on postgres server
under the "superuser" account
2. "superuser" has .pgpass file
3. the "superuser" .pgpass file is set up with wildcards, e.g.
*:*:*:postgres:mypassword
4. "superuser" creates login for "luser" in db1

This depends on "superuser" to not only make use of .pgpass, but
specifically to use it in an insecure way, i.e. using wildcards to
specify that the login credentials should be sent to any arbitrary
Postgres installation.

So although it may make sense to lock this down for 8.4, I don't agree
with backporting it due to the backward compatibility hit. Also, I think
we still need a way that people who don't allow real end-users directly
in their databases and don't care about Marko's threat scenario can get
their work done with minimal pain.

Attached is my version of a more complete patch. It aims to prevent any
dblink connection by non-superusers. But it also creates "_u" versions
of dblink() and dblink_exec(), and initially revokes privileges from
public in a similar vain. dblink_u(), dblink_exec_u (), and the
previously created dblink_connect_u() are all SECURITY_DEFINER functions
that can be granted to trusted non-superuser logins.

Beyond Marko and I, no one else has publicly weighed in on this. If I
don't hear any objections, I'll apply to cvs HEAD *only* in about 24 hours.

Thanks,

Joe


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

Fw: [pgsql-es-ayuda] CLAVES EN POSTGRES

 
BUENOS Tardes
 
 
tengo una duda de si se puede hacer o no ... a mis usuarios de la base de datos en el momento de asignar el password  siempre le agrego unos caracteres al primicipio y al final de la clave que digita el usuario
y por la aplicacion cuando se va a conectar al motor hago lo mismo, pero estoy tratando de hacer lo siguiente ... que esos caracteres que agreago al principio y final de clave los pueda estar cambiando periodiacamente, entonces quisiera saber como puedo hacer en postgres con el super usuario para cambiar las caracteres que me conforman la clave en el el motor
 
ejemplo
 
la clave del usuario X es   123456 en el motor esta    SG123456SG
 
quiero cambiar la para que la clave para el usuario sera 123456 pero en el motor   ASD123456ASD
 
pero sin necesidad de que tenga que saberme todas las claves de todos los usuarios para hacer eso .... no se si se puede por medio del superusuario o de otra forma
 
 
 
MUCHAS GRACIAS POR LA ATENCIÓN
 
 
 
FRANZ MARIN
Colombia
 
 

Re: [HACKERS] Do we really need a 7.4.22 release now?

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Magnus Hagander wrote:
>> Are we ready enough to actually put a *timeline* on the website?

> I would think so. IMO:
> 3 years - Maintenance mode only
> 5 years - End of life

I'm not really in favor of a one-size-fits-all approach to this.
Our various releases have had different levels of uptake and don't
necessarily all deserve the same support lifespan.

Case in point: we already obsoleted 8.0 and 8.1 for our Windows users.
How sensible is it to argue that they'll deserve a lifespan equivalent
to 8.2's?

The above numbers seem reasonable as a rough guideline, but I think the
actual decisions will need to be taken on a release-by-release basis.

regards, tom lane

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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Andrew Dunstan <andrew@dunslane.net> writes:
> Joshua D. Drake wrote:
>> Of course we need to define what maintenance mode only means.

> We effectively put each release into maintenance mode on day 1, ISTM.

Well, that would depend on your definition of "maintenance mode" ;-)

Your statement would be true if you define it as "no new features" but
that is nowhere near what I have in mind here. I'm thinking something
closer to "we'll only fix critical security and data-loss risks"; and
it would only apply to releases that are approaching the end of their
life cycle.

In particular, we need to define things in a way that explains/justifies
changing more stuff in 8.3 than in 7.4. "Maintenance mode starts on
day of release" is not only unhelpful but counterproductive for that
discussion.

regards, tom lane

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

Re: [HACKERS] PostgreSQL future ideas

On Sat, 2008-09-20 at 09:06 -0400, D'Arcy J.M. Cain wrote:
> On Sat, 20 Sep 2008 13:47:10 +0300
> Hannu Krosing <hannu@2ndQuadrant.com> wrote:
> > On Fri, 2008-09-19 at 16:37 -0400, D'Arcy J.M. Cain wrote:
> > > I don't think that we should rush into any one language without
> > > checking the alternatives. Personally I think we should port everything
> > > to Intercal.
> >
> > My choice would be whitespace , see
> > http://en.wikipedia.org/wiki/Whitespace_(programming_language)
>
> Hey, we could write two modules into each file, one in Intercal and
> another in Whitespace. :-)

Good idea! if we are smart, we can interweave them so that the
whitespace inside intercal forms the source code of the Whitespace
program.

That would almost be Literate Programming - if you are not sure, what
some part of the program does, you can immediately look at the other
implementation.

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


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

2008/9/20 Joshua D. Drake <jd@commandprompt.com>:
> Andrew Dunstan wrote:
>>
>>
>> Joshua D. Drake wrote:
>>>
>>>
>>> 3 years - Maintenance mode only
>>> 5 years - End of life
>>>
>>> Of course we need to define what maintenance mode only means.
>>>
>>>
>>
>> We effectively put each release into maintenance mode on day 1, ISTM.
>>
>
> True enough.
>

Surely it should be x years from the release of the next major version
or something Not x years from release. If we say End of Life is x
years from release then don't get round to releasing for ages a
version may reach end of life with nothing to replace it. (Which will
happen if we ever exhaust the To Do list (bit pie in the sky but never
mind))

Peter

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

Re: [HACKERS] PostgreSQL future ideas

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Who can resist the programming language game?

Le 19 sept. 08 à 22:37, D'Arcy J.M. Cain a écrit :
> On Fri, 19 Sep 2008 20:57:36 +0100
> "Dave Page" <dpage@pgadmin.org> wrote:
>> On Fri, Sep 19, 2008 at 8:54 PM, Gevik Babakhani <pgdev@xs4all.nl>
>> wrote:
>>> Has there been any idea to port PG to a more modern programming
>>> language
>>> like C++? Of course there are some minor obstacles like a new OO
>>> design,
>>
>> The plan is to start porting it to Java after the next release -
>> probably at the beginning of April.
>
> I don't think that we should rush into any one language without
> checking the alternatives. Personally I think we should port
> everything
> to Intercal.

May I recall Greenspun's Tenth Rule of Programming: "any sufficiently
complicated C or Fortran program contains an ad hoc informally-
specified bug-ridden slow implementation of half of Common Lisp".

Regards,
- --
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkjVVZUACgkQlBXRlnbh1bl/6gCcDhLEAdy+pZnjGnKSly3jmZqC
5pYAoMbseRc3Di49dRnr4XLDIGJOApFz
=Qj2e
-----END PGP SIGNATURE-----

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

Re: [HACKERS] Where to Host Project

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

Le 20 sept. 08 à 09:42, Dave Page a écrit :
> On Sat, Sep 20, 2008 at 8:37 AM, Joshua D. Drake
> <jd@commandprompt.com> wrote:
>> Dave Page wrote:
>>
>>> Well that's not strictly true - I persuaded one of the GForge
>>> developers to work on the upgrade. As far as I'm aware, we're still
>>> waiting for the hardware/OS platform to be sorted out after some
>>> initial problems. I suspect JD will tell me something different
>>> though
>>> - that being the case, perhaps we can work out the issues and get on
>>> with the upgrade.

I suppose the plan is to upgrade to a newer GForge. Is it still time
to propose something completely different? I have real good feedbacks
about VHFFS, a perl based clean-room re-implementation of it, if you
want to see it this way.
http://www.vhffs.org/wiki/index.php
http://fr.wikipedia.org/wiki/VHFFS (easy to grasp keywords)

Hope this helps, regards,
- --
dim

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (Darwin)

iEYEARECAAYFAkjVUqcACgkQlBXRlnbh1bnuWwCgsWMSrYACh2lOt+xbeqa6DCbO
j7AAnifgloNY7ldaA+54S9HLlLxqBvuC
=LoLv
-----END PGP SIGNATURE-----

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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Andrew Dunstan wrote:
>
>
> Joshua D. Drake wrote:
>>
>>
>> 3 years - Maintenance mode only
>> 5 years - End of life
>>
>> Of course we need to define what maintenance mode only means.
>>
>>
>
> We effectively put each release into maintenance mode on day 1, ISTM.
>

True enough.

Joshua d. Drake


> cheers
>
> andrew
>


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Joshua D. Drake wrote:
>
>
> 3 years - Maintenance mode only
> 5 years - End of life
>
> Of course we need to define what maintenance mode only means.
>
>

We effectively put each release into maintenance mode on day 1, ISTM.

cheers

andrew

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

Re: [pgsql-www] sf.net updated

On Sat, 2008-09-20 at 11:23 -0700, Joshua D. Drake wrote:
> Thanks! What about Freshmeat?

I don't have access to our freshmeat page.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [pgsql-www] sf.net updated

Devrim GÜNDÜZ wrote:
> Hi,
>
> I just updated sf.net with new releases. The only missing thing is
> release announcement. I'll submit that once the releases are officially
> announced.

Thanks! What about Freshmeat?

>
> Regards,


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Magnus Hagander wrote:

>>> Shall we set an exact date, such as October 1, 2009?
>> Let's include 8.0 in that announcement so we aren't having this
>> discussion again in a year.
>
> Are we ready enough to actually put a *timeline* on the website?
> Meaning, can we already put in preliminary dates for *all* released
> versions?

I would think so. IMO:

3 years - Maintenance mode only
5 years - End of life

Of course we need to define what maintenance mode only means.

Joshua D. Drake


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Joshua D. Drake wrote:
> Greg Sabino Mullane wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: RIPEMD160
>>
>>
>> Tom Lane wrote:
>>
>>> The suggestion I started this thread with amounted to not bothering with
>>> pushing 7.4.x updates in update cycles where we'd made no "serious" bug
>>> fixes in it; which is a very long way from desupport. Maybe an
>>> appropriate compromise is to announce now that 7.4 is in maintenance
>>> mode and will receive only really critical bug fixes (which are the only
>>> ones that 7.4.x users are going to pay attention to anyway, so nothing
>>> is lost); and that actual desupport will occur a year from now.
>>
>> +1
>>
>> Shall we set an exact date, such as October 1, 2009?
>
> Let's include 8.0 in that announcement so we aren't having this
> discussion again in a year.

Are we ready enough to actually put a *timeline* on the website?
Meaning, can we already put in preliminary dates for *all* released
versions?

//Magnus


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

[pgsql-www] sf.net updated

Hi,

I just updated sf.net with new releases. The only missing thing is
release announcement. I'll submit that once the releases are officially
announced.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

[HACKERS] Re: Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle

*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 4090,4095 **** SET XML OPTION { DOCUMENT | CONTENT };
--- 4090,4117 ----
</listitem>
</varlistentry>

+ <varlistentry id="guc-intervalstyle" xreflabel="IntervalStyle">
+ <term><varname>IntervalStyle</varname> (<type>string</type>)</term>
+ <indexterm>
+ <primary><varname>IntervalStyle</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Sets the display format for interval values.
+ The value <literal>sql_standard</> will output SQL Standard
+ strings when given intervals that conform to the SQL
+ standard (either year-month only or date-time only; and no
+ mixing of positive and negative components).
+ The value <literal>postgres</> will output intervals in
+ a format that matches what old releases had output when
+ the DateStyle was set to <literal>'ISO'</>.
+ The value <literal>postgres_verbose</> will output intervals in
+ a format that matches what old releases had output when
+ the DateStyle was set to <literal>'SQL'</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-timezone" xreflabel="timezone">
<term><varname>timezone</varname> (<type>string</type>)</term>
<indexterm>
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***************
*** 2213,2218 **** January 8 04:05:06 1999 PST
--- 2213,2305 ----
</para>
</sect2>

+ <sect2 id="interval-output">
+ <title>Interval Output</title>
+
+ <indexterm>
+ <primary>interval</primary>
+ <secondary>output format</secondary>
+ <seealso>formatting</seealso>
+ </indexterm>
+
+ <para>
+ The output format of the interval types can be set to one of the four
+ styles <literal>sql_standard</>,
+ <literal>postgres</>, or <literal>postgres_verbose</>.The default
+ is the <literal>postgres</> format.
+ <xref
+ linkend="interval-style-output-table"> shows examples of each
+ output style.
+ </para>
+
+ <para>
+ The <literal>sql_standard</> style will output SQL standard
+ interval literal strings where the value of the interval
+ value consists of only a year-month component or a datetime
+ component (as required by the sql standard). For an interval
+ containing both a year-month and a datetime component, the
+ output will be a SQL Standard unquoted year-month literal
+ string joined to a SQL Standard unquoted datetime literal
+ string with a space in between.
+ </para>
+
+ <para>
+ The <literal>postgres</> style will output intervals that match
+ the style PostgreSQL 8.3 outputed when the <xref linkend="guc-datestyle">
+ parameter was set to <literal>ISO</>.
+ </para>
+
+ <para>
+ The <literal>postgres_verbose</> style will output intervals that match
+ the style PostgreSQL 8.3 outputed when the <xref linkend="guc-datestyle">
+ parameter was set to <literal>SQL</>.
+ </para>
+
+ <table id="interval-style-output-table">
+ <title>Interval Style Example</title>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Style Specification</entry>
+ <entry>Year-Month Interval</entry>
+ <entry>DateTime Interval</entry>
+ <entry>Nonstandardrd Extended Interval</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>sql_standard</entry>
+ <entry>1-2</entry>
+ <entry>3 4:05:06</entry>
+ <entry>-1-2 +3 -4:05:06</entry>
+ </row>
+ <row>
+ <entry>postgres</entry>
+ <entry>1 year 2 mons</entry>
+ <entry>3 days 04:05:06</entry>
+ <entry> -1 years -2 mons +3 days -04:05:06</entry>
+ </row>
+ <row>
+ <entry>postgres_verbose</entry>
+ <entry>@ 1 year 2 mons</entry>
+ <entry>@ 3 days 4 hours 5 mins 6 secs</entry>
+ <entry>@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ Note that <literal>sql_standard</> style will only produce strictly
+ standards-conforming string sliterals when given a strictly SQL-standard interval
+ value - meaning that it needs to be a pure year-month or datetime
+ interval and not mix positive and negative components.
+ </para>
+
+ </sect2>
+
+
+
<sect2 id="datatype-timezones">
<title>Time Zones</title>

*** a/src/backend/commands/variable.c
--- b/src/backend/commands/variable.c
***************
*** 229,234 **** assign_datestyle(const char *value, bool doit, GucSource source)
--- 229,271 ----


/*
+ * assign_intervalstyle: GUC assign_hook for datestyle
+ */
+ const char *
+ assign_intervalstyle(const char *value, bool doit, GucSource source)
+ {
+ int newIntervalStyle = IntervalStyle;
+ char * result = (char *) malloc(32);
+ if (pg_strcasecmp(value, "postgres") == 0)
+ {
+ newIntervalStyle = INTSTYLE_POSTGRES;
+ }
+ else if (pg_strcasecmp(value, "postgres_verbose") == 0)
+ {
+ newIntervalStyle = INTSTYLE_POSTGRES_VERBOSE;
+ }
+ else if (pg_strcasecmp(value, "sql_standard") == 0)
+ {
+ newIntervalStyle = INTSTYLE_SQL_STANDARD;
+ }
+ else
+ {
+ ereport(GUC_complaint_elevel(source),
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unrecognized \"intervalstyle\" key word: \"%s\"",
+ value)));
+ return NULL;
+ }
+ if (doit)
+ {
+ IntervalStyle = newIntervalStyle;
+ strcpy(result, value);
+ }
+ return result;
+ }
+
+
+ /*
* TIMEZONE
*/

*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 3605,3610 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3605,3624 ----
return TRUE;
}

+ /*
+ * small helper funciton to avoid copy&paste of this ifdef below
+ */
+ void
+ AppendFsec(char * cp,fsec_t fsec) {
+ if (fsec==0) return;
+ #ifdef HAVE_INT64_TIMESTAMP
+ sprintf(cp, ".%06d", Abs(fsec));
+ #else
+ sprintf(cp, ":%012.9f", fabs(fsec));
+ #endif
+ TrimTrailingZeros(cp);
+ }
+

/* EncodeInterval()
* Interpret time structure as a delta time and convert to string.
***************
*** 3613,3618 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
--- 3627,3643 ----
* Actually, afaik ISO does not address time interval formatting,
* but this looks similar to the spec for absolute date/time.
* - thomas 1998-04-30
+ *
+ * Actually, afaik, ISO 8601 does specify formats for "time
+ * intervals...[of the]...format with time-unit designators", which
+ * are pretty ugly. The format looks something like
+ * P1Y1M1DT1H1M1.12345S
+ * but useful for exchanging data with computers instead of humans.
+ * - ron 2003-07-14
+ *
+ * And ISO's SQL 2008 standard specifies standards for
+ * "year-month literal"s (that look like '2-3') and
+ * "day-time literal"s (that look like ('4 5:6:7')
*/
int
EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
***************
*** 3621,3626 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
--- 3646,3658 ----
bool is_nonzero = FALSE;
char *cp = str;

+ int year = tm->tm_year;
+ int mon = tm->tm_mon;
+ int mday = tm->tm_mday;
+ int hour = tm->tm_hour;
+ int min = tm->tm_min;
+ int sec = tm->tm_sec;
+
/*
* The sign of year and month are guaranteed to match, since they are
* stored internally as "month". But we'll need to check for is_before and
***************
*** 3628,3635 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
*/
switch (style)
{
! /* compatible with ISO date formats */
! case USE_ISO_DATES:
if (tm->tm_year != 0)
{
sprintf(cp, "%d year%s",
--- 3660,3738 ----
*/
switch (style)
{
! /* SQL Standard interval literals */
! case INTSTYLE_SQL_STANDARD:
! {
! bool has_negative = (year < 0) || (mon < 0) ||
! (mday < 0) || (hour < 0) ||
! (min < 0) || (sec < 0) || (fsec<0);
! bool has_positive = (year > 0) || (mon > 0) ||
! (mday > 0) || (hour > 0) ||
! (min > 0) || (sec > 0) || (fsec>0);
! bool has_year_month = (year != 0) || (mon != 0);
! bool has_datetime = (hour != 0) || (min != 0) ||
! (sec != 0) || (fsec!= 0) || (mday != 0);
! bool has_day = (mday != 0);
! bool sql_standard_value = (!(has_negative && has_positive)) &&
! (!(has_year_month && has_datetime));
! /*
! * SQL Standard wants only 1 "<sign>" preceeding the whole
! * interval.
! */
! if (has_negative && sql_standard_value)
! {
! sprintf(cp,"-");
! cp++;
! year = -year;
! mon = -mon;
! mday = -mday;
! hour = -hour;
! min = -min;
! sec = -sec;
! fsec = -fsec;
! }
! if (!has_negative && !has_positive)
! {
! sprintf(cp,"0");
! }
! else if (!sql_standard_value)
! {
! /*
! * For non sql-standard interval values,
! * force outputting the signs to avoid
! * ambiguities with intervals with mixed
! * sign components.
! */
! char year_sign = (year<0 || mon<0) ? '-' : '+';
! char day_sign = (mday<0) ? '-' : '+';
! char sec_sign = (hour<0 || min<0 || sec<0 || fsec<0)
! ? '-' : '+';
! sprintf(cp,"%c%d-%d %c%d %c%d:%02d:%02d",
! year_sign,abs(year),abs(mon),
! day_sign,abs(mday),
! sec_sign,abs(hour),abs(min),abs(sec));
! AppendFsec(cp+strlen(cp),fsec);
! }
! else if (has_year_month)
! {
! sprintf(cp,"%d-%d",year,mon);
! }
! else if (has_day)
! {
! sprintf(cp,"%d %d:%02d:%02d",mday,hour,min,sec);
! AppendFsec(cp+strlen(cp),fsec);
! }
! else
! {
! sprintf(cp,"%d:%02d:%02d",hour,min,sec);
! AppendFsec(cp+strlen(cp),fsec);
! }
! cp += strlen(cp);
! break;
! }
!
! /* compatible with postgresql 8.3 when DateStyle = 'iso' */
! case INTSTYLE_POSTGRES:
if (tm->tm_year != 0)
{
sprintf(cp, "%d year%s",
***************
*** 3692,3700 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
cp += strlen(cp);
}
}
break;

! case USE_POSTGRES_DATES:
default:
strcpy(cp, "@ ");
cp += strlen(cp);
--- 3795,3809 ----
cp += strlen(cp);
}
}
+ if (!is_nonzero)
+ {
+ strcat(cp, "0");
+ cp += strlen(cp);
+ }
break;

! /* compatible with postgresql 8.3 when DateStyle = 'sql' */
! case INTSTYLE_POSTGRES_VERBOSE:
default:
strcpy(cp, "@ ");
cp += strlen(cp);
***************
*** 3821,3842 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
is_before = (tm->tm_sec < 0);
is_nonzero = TRUE;
}
break;
}

- /* identically zero? then put in a unitless zero... */
- if (!is_nonzero)
- {
- strcat(cp, "0");
- cp += strlen(cp);
- }
-
- if (is_before && (style != USE_ISO_DATES))
- {
- strcat(cp, " ago");
- cp += strlen(cp);
- }
-
return 0;
} /* EncodeInterval() */

--- 3930,3948 ----
is_before = (tm->tm_sec < 0);
is_nonzero = TRUE;
}
+ if (!is_nonzero)
+ {
+ strcat(cp, "0");
+ cp += strlen(cp);
+ }
+ if (is_before)
+ {
+ strcat(cp, " ago");
+ cp += strlen(cp);
+ }
break;
}

return 0;
} /* EncodeInterval() */

*** a/src/backend/utils/adt/nabstime.c
--- b/src/backend/utils/adt/nabstime.c
***************
*** 671,677 **** reltimeout(PG_FUNCTION_ARGS)
char buf[MAXDATELEN + 1];

reltime2tm(time, tm);
! EncodeInterval(tm, 0, DateStyle, buf);

result = pstrdup(buf);
PG_RETURN_CSTRING(result);
--- 671,677 ----
char buf[MAXDATELEN + 1];

reltime2tm(time, tm);
! EncodeInterval(tm, 0, IntervalStyle, buf);

result = pstrdup(buf);
PG_RETURN_CSTRING(result);
*** a/src/backend/utils/adt/timestamp.c
--- b/src/backend/utils/adt/timestamp.c
***************
*** 677,683 **** interval_out(PG_FUNCTION_ARGS)
if (interval2tm(*span, tm, &fsec) != 0)
elog(ERROR, "could not convert interval to tm");

! if (EncodeInterval(tm, fsec, DateStyle, buf) != 0)
elog(ERROR, "could not format interval");

result = pstrdup(buf);
--- 677,683 ----
if (interval2tm(*span, tm, &fsec) != 0)
elog(ERROR, "could not convert interval to tm");

! if (EncodeInterval(tm, fsec, IntervalStyle, buf) != 0)
elog(ERROR, "could not format interval");

result = pstrdup(buf);
*** a/src/backend/utils/init/globals.c
--- b/src/backend/utils/init/globals.c
***************
*** 88,93 **** bool ExitOnAnyError = false;
--- 88,94 ----

int DateStyle = USE_ISO_DATES;
int DateOrder = DATEORDER_MDY;
+ int IntervalStyle = INTSTYLE_POSTGRES;
bool HasCTZSet = false;
int CTimeZone = 0;

*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 367,372 **** static bool session_auth_is_superuser;
--- 367,373 ----
static double phony_random_seed;
static char *client_encoding_string;
static char *datestyle_string;
+ static char *intervalstyle_string;
static char *locale_collate;
static char *locale_ctype;
static char *server_encoding_string;
***************
*** 2098,2103 **** static struct config_string ConfigureNamesString[] =
--- 2099,2114 ----
"ISO, MDY", assign_datestyle, NULL
},

+ {
+ {"IntervalStyle", PGC_USERSET, CLIENT_CONN_LOCALE,
+ gettext_noop("Sets the display format for interval values."),
+ gettext_noop(""),
+ GUC_REPORT
+ },
+ &intervalstyle_string,
+ "postgres", assign_intervalstyle, NULL
+ },
+
{
{"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the default tablespace to create tables and indexes in."),
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 426,431 ****
--- 426,432 ----
# - Locale and Formatting -

#datestyle = 'iso, mdy'
+ #intervalstyle = 'postgres'
#timezone = unknown # actually, defaults to TZ environment
# setting
#timezone_abbreviations = 'Default' # Select the set of available time zone
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***************
*** 1953,1958 **** psql_completion(char *text, int start, int end)
--- 1953,1965 ----

COMPLETE_WITH_LIST(my_list);
}
+ else if (pg_strcasecmp(prev2_wd, "IntervalStyle") == 0)
+ {
+ static const char *const my_list[] =
+ {"postgres","postgres_verbose", "sql_standard", NULL};
+
+ COMPLETE_WITH_LIST(my_list);
+ }
else if (pg_strcasecmp(prev2_wd, "GEQO") == 0)
{
static const char *const my_list[] =
*** a/src/include/commands/variable.h
--- b/src/include/commands/variable.h
***************
*** 15,20 ****
--- 15,22 ----

extern const char *assign_datestyle(const char *value,
bool doit, GucSource source);
+ extern const char *assign_intervalstyle(const char *value,
+ bool doit, GucSource source);
extern const char *assign_timezone(const char *value,
bool doit, GucSource source);
extern const char *show_timezone(void);
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***************
*** 191,196 **** extern PGDLLIMPORT Oid MyDatabaseTableSpace;
--- 191,208 ----

extern int DateStyle;
extern int DateOrder;
+
+ /*
+ * IntervalStyles
+ * INTSTYLE_POSTGRES Like Postgres8.3 when DateStyle = 'iso'
+ * INTSTYLE_POSTGRES_VERBOSE Like Postgres8.3 when DateStyle = 'sql'
+ * INTSTYLE_SQL_STANDARD SQL standard interals
+ */
+ #define INTSTYLE_POSTGRES 0
+ #define INTSTYLE_POSTGRES_VERBOSE 1
+ #define INTSTYLE_SQL_STANDARD 2
+
+ extern int IntervalStyle;

/*
* HasCTZSet is true if user has set timezone as a numeric offset from UTC.
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
***************
*** 60,65 **** ALTER TABLE tmp ADD COLUMN y float4[];
--- 60,67 ----

ALTER TABLE tmp ADD COLUMN z int2[];

+ SET IntervalStyle to postgres_verbose;
+
INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
v, w, x, y, z)
VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
***************
*** 283,288 **** select '{ }}'::text[];
--- 283,289 ----
select array[];
-- none of the above should be accepted

+ SET intervalstyle to postgres_verbose;
-- all of the following should be accepted
select '{}'::text[];
select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
*** a/src/test/regress/sql/horology.sql
--- b/src/test/regress/sql/horology.sql
***************
*** 73,79 **** SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time
--
-- timestamp, interval arithmetic
--
!
SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
--- 73,79 ----
--
-- timestamp, interval arithmetic
--
! set IntervalStyle to postgres_verbose;
SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
*** a/src/test/regress/sql/interval.sql
--- b/src/test/regress/sql/interval.sql
***************
*** 94,99 **** FROM INTERVAL_MULDIV_TBL;
--- 94,100 ----
DROP TABLE INTERVAL_MULDIV_TBL;

SET DATESTYLE = 'postgres';
+ SET INTERVALSTYLE = 'postgres_verbose';

SELECT '' AS ten, * FROM INTERVAL_TBL;

***************
*** 118,123 **** SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
--- 119,126 ----

-- test fractional second input, and detection of duplicate units
SET DATESTYLE = 'ISO';
+ SET INTERVALSTYLE = 'postgres';
+
SELECT '1 millisecond'::interval, '1 microsecond'::interval,
'500 seconds 99 milliseconds 51 microseconds'::interval;
SELECT '3 days 5 milliseconds'::interval;
*** a/src/test/regress/sql/reltime.sql
--- b/src/test/regress/sql/reltime.sql
***************
*** 2,7 ****
--- 2,10 ----
-- RELTIME
--

+ -- DateStyle is 'Postgres, MDY' here...
+ SET intervalstyle to postgres_verbose;
+
CREATE TABLE RELTIME_TBL (f1 reltime);

INSERT INTO RELTIME_TBL (f1) VALUES ('@ 1 minute');
***************
*** 23,29 **** INSERT INTO RELTIME_TBL (f1) VALUES ('badly formatted reltime');
INSERT INTO RELTIME_TBL (f1) VALUES ('@ 30 eons ago');

-- test reltime operators
-
SELECT '' AS six, * FROM RELTIME_TBL;

SELECT '' AS five, * FROM RELTIME_TBL
--- 26,31 ----
*** a/src/test/regress/sql/timestamp.sql
--- b/src/test/regress/sql/timestamp.sql
***************
*** 163,168 **** SELECT '' AS "16", d1 FROM TIMESTAMP_TBL
--- 163,171 ----
SELECT '' AS "49", d1 FROM TIMESTAMP_TBL
WHERE d1 >= timestamp without time zone '1997-01-02';

+ -- DateStyle was 'Postgres, MDY' at this point.
+ SET intervalstyle to postgres_verbose;
+
SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';

*** a/src/test/regress/sql/timestamptz.sql
--- b/src/test/regress/sql/timestamptz.sql
***************
*** 182,187 **** SELECT '' AS "16", d1 FROM TIMESTAMPTZ_TBL
--- 182,190 ----
SELECT '' AS "49", d1 FROM TIMESTAMPTZ_TBL
WHERE d1 >= timestamp with time zone '1997-01-02';

+ -- Datestyle was Postgres, MDY here
+ SET intervalstyle TO postgres_verbose;
+
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';

Ron Mayer wrote:
> Tom Lane wrote:
>> ...GUC that selected PG traditional, SQL-standard... interval output
>> format seems like it could be a good idea.
>

This is an update to the earlier SQL-standard-interval-literal output
patch that I submitted here:
http://archives.postgresql.org/message-id/48D15471.6080305@cheapcomplexdevices.com

This version fixes a couple bugs in my last patch related to reltime output and
with the new GUC variable, and updated the regression tests to adjust the
new IntervalStyle guc to match the output of the previous regression tests
where the interval output depended on DateStyle.

I've also added it to the Nov CommitFest wiki page.

Fwd: [GENERAL] Getting several columns from subselect with LIMIT 1

Thanks for the suggestion. Unfortunately I get an INVALID COLUMN
REFERENCE (SQL state: 42P10)
to the effect that the subselect in the FROM clause cannot reference
other tables at the same request level.


2008/9/20 Marcus Engene <mengpg2@engene.se>:
>>
>> SELECT
>> persons.*,
>> (
>> SELECT child.name
>> FROM progeny JOIN persons child ON child.id = progeny.child
>> WHERE progeny.parent = persons.id
>> ORDER BY child.birthdate ASC
>> LIMIT 1
>> ) AS firstborn_name
>> FROM persons;
>>
>> Now, this is probably not the most elegant piece of code, but the real
>> problem is that
>> I cannot see how to extend it to the case where I want not only the
>> firstborn's name but also the firstborn's ID
>> (short of repeating the entire subselect a second time). At the
>> moment, with this current syntax, my subSELECT statement would not be
>> allowed to return more than a single column.
>
> Would this work?
>
> select
> p.*
> ,pp.*
> from
> persons p
> ,(
> SELECT child.name, child.id
> FROM progeny JOIN persons child ON child.id = progeny.child
> WHERE progeny.parent = p.id
> ORDER BY child.birthdate ASC
> LIMIT 1
> ) as kid(kid_name,kid_id)

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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> Tom Lane wrote:
>
>> The suggestion I started this thread with amounted to not bothering with
>> pushing 7.4.x updates in update cycles where we'd made no "serious" bug
>> fixes in it; which is a very long way from desupport. Maybe an
>> appropriate compromise is to announce now that 7.4 is in maintenance
>> mode and will receive only really critical bug fixes (which are the only
>> ones that 7.4.x users are going to pay attention to anyway, so nothing
>> is lost); and that actual desupport will occur a year from now.
>
> +1
>
> Shall we set an exact date, such as October 1, 2009?

Let's include 8.0 in that announcement so we aren't having this
discussion again in a year.

Joshua D. Drake


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


Tom Lane wrote:

> The suggestion I started this thread with amounted to not bothering with
> pushing 7.4.x updates in update cycles where we'd made no "serious" bug
> fixes in it; which is a very long way from desupport. Maybe an
> appropriate compromise is to announce now that 7.4 is in maintenance
> mode and will receive only really critical bug fixes (which are the only
> ones that 7.4.x users are going to pay attention to anyway, so nothing
> is lost); and that actual desupport will occur a year from now.

+1

Shall we set an exact date, such as October 1, 2009?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200809201226
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkjVJDgACgkQvJuQZxSWSsjCuACgxGqmADfgGlHekGI+TXfQTAnr
CroAnAuMs9sMcRvjBBDFlYV5+dY8wlra
=c5fb
-----END PGP SIGNATURE-----

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

Re: [HACKERS] macport for libpqxx

videoHi Dave,

Thanks for getting back to me. Please find attached a draft Portfile
for libpqxx-2.6.9 (the stable version). It's easy to read the
Portfile to see what is going on. I think it should work fine, but I
would appreciate any advice about any configure options that should be
enabled.

I've got a problem within macports (not specific to pg or libpqxx).
MacPorts will not locate the pg_config.sh file during the macport
build. I can't just modify the $PATH env because the macport build
ignores it. There is an internal variable called $binpath in
macports, but it's read-only. I can't figure out how to get the
macport configure process to find the right pg_config. Any help
appreciated.

Thanks, Darren

PS, If you want to try out this Portfile, take a look at the macports
guide (esp. sections 4,5):
http://guide.macports.org/chunked/
Then follow the instructions to create your local repository here:
http://guide.macports.org/chunked/development.local-repositories.html
Then put this Portfile into databases/libpqxx within your repository.

On Sat, Sep 20, 2008 at 12:27 AM, Dave Page <dpage@pgadmin.org> wrote:
> On Sat, Sep 20, 2008 at 7:30 AM, Darren Weber
> <darren.weber.lists@gmail.com> wrote:
>> http://pqxx.org/development/libpqxx/
>>
>> I'm in the process of creating a macport for libpqxx. I could use
>> some help from anyone with experience in building postgresql or
>> libpqxx on OSX, esp. against the macport libraries.
>
> Never built libpqxx or a MacPort), but I'm used to building Postgres
> and other PG apps and the fu required to get universal binaries. What
> do you need?
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>

Re: [HACKERS] Assert Levels

On Sat, 2008-09-20 at 11:28 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
> > On Fri, 2008-09-19 at 17:47 -0400, Tom Lane wrote:
> >> Well, there are certain things that --enable-cassert turns on that are
> >> outrageously expensive; notably CLOBBER_FREED_MEMORY and
> >> MEMORY_CONTEXT_CHECKING. It wouldn't be too unreasonable to decouple
> >> those things somehow (with a means more accessible than editing
> >> pg_config_manual.h).
>
> > That's mostly what I'm hoping for. If we call the CLOBBER checks as
> > class 3, all current Asserts as class 2 then we can invent a class 1 of
> > specifically lightweight checks (only). We can then have
> > --enable-cassert=X rather than just y or n
>
> Hold on a minute. I don't mind refactoring the way that configure
> controls those existing build switches. I do object to complexifying
> routine uses of Assert when absolutely zero evidence of a benefit has
> been presented. How do you know that the run-of-the-mill Asserts aren't
> lightweight enough already?

Well, we don't. That's why I'd suggest to do it slowly and classify
everything as medium weight until proven otherwise. Also think we need
to take code location into account, because a cheap test in a critical
place could end up costing more than an expensive test that hardly ever
gets executed.

Anyway, if we do it at all, I think this probably should be classified
as code cleanup and done later in release cycle. If you think it's a
good idea after a couple of months we can start on it.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support


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

Re: [HACKERS] Do we really need a 7.4.22 release now?

Shane Ambler <pgsql@Sheeky.Biz> writes:
> The few postings I have noticed with users running 7.4 has been with a
> release several less than the newest. ...
> Supporting old versions is a great and noble thing but there comes a
> time when it is a waste of resources because the effort goes unused.

Yeah, that's a really good point. An example is that Red Hat is still
shipping/supporting 7.4.x in RHEL 4, but it's been quite a long time
since I've been able to persuade them to push a 7.4.x update that didn't
involve a security issue. (They're currently shipping 7.4.19, and I'm
not even going to bother suggesting an update to .22.)

Probably everyone has got their own slightly different set of hot-button
considerations for whether it's worth updating to a new minor release,
but it's really unclear that there's going to be any uptake at all for
7.4.22 as constituted, because the bugs it fixes are so minor.

The suggestion I started this thread with amounted to not bothering with
pushing 7.4.x updates in update cycles where we'd made no "serious" bug
fixes in it; which is a very long way from desupport. Maybe an
appropriate compromise is to announce now that 7.4 is in maintenance
mode and will receive only really critical bug fixes (which are the only
ones that 7.4.x users are going to pay attention to anyway, so nothing
is lost); and that actual desupport will occur a year from now.

regards, tom lane

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

Re: [GENERAL] Getting several columns from subselect with LIMIT 1

Pierre Thibaudeau wrote:
> I have a PERSONS table.
> I also have a PROGENY table, which is a many-to-many association table
> with two foreign keys to the PERSONS table to itself.
> (In this day and age, not only can an individual have any number of
> children, but also a person can have any number of parents! At least,
> let's assume that's true for the sake of this setup.)
>
> Suppose I wish to construct a view of the persons, along with the name
> of their first-born (if they have one; NULL otherwise). The
> following SELECT does just that:
>
> SELECT
> persons.*,
> (
> SELECT child.name
> FROM progeny JOIN persons child ON child.id = progeny.child
> WHERE progeny.parent = persons.id
> ORDER BY child.birthdate ASC
> LIMIT 1
> ) AS firstborn_name
> FROM persons;
>
> Now, this is probably not the most elegant piece of code, but the real
> problem is that
> I cannot see how to extend it to the case where I want not only the
> firstborn's name but also the firstborn's ID
> (short of repeating the entire subselect a second time). At the
> moment, with this current syntax, my subSELECT statement would not be
> allowed to return more than a single column.
>
> Any suggestion?
>
>
Would this work?

select
p.*
,pp.*
from
persons p
,(
SELECT child.name, child.id
FROM progeny JOIN persons child ON child.id = progeny.child
WHERE progeny.parent = p.id
ORDER BY child.birthdate ASC
LIMIT 1
) as kid(kid_name,kid_id)

best regards,
Marcus

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

[pgsql-www] PDF docs updated

Hi,

I updated 8.3 A-4, 8.3-US and 8.2 A-4 PDFs to latest minor versions on
pg website.

FYI.

Regards,
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

[GENERAL] Getting several columns from subselect with LIMIT 1

I have a PERSONS table.
I also have a PROGENY table, which is a many-to-many association table
with two foreign keys to the PERSONS table to itself.
(In this day and age, not only can an individual have any number of
children, but also a person can have any number of parents! At least,
let's assume that's true for the sake of this setup.)

Suppose I wish to construct a view of the persons, along with the name
of their first-born (if they have one; NULL otherwise). The
following SELECT does just that:

SELECT
persons.*,
(
SELECT child.name
FROM progeny JOIN persons child ON child.id = progeny.child
WHERE progeny.parent = persons.id
ORDER BY child.birthdate ASC
LIMIT 1
) AS firstborn_name
FROM persons;

Now, this is probably not the most elegant piece of code, but the real
problem is that
I cannot see how to extend it to the case where I want not only the
firstborn's name but also the firstborn's ID
(short of repeating the entire subselect a second time). At the
moment, with this current syntax, my subSELECT statement would not be
allowed to return more than a single column.

Any suggestion?

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