[HACKERS] [PgFoundry] Unsigned Data Types

2008-08-15 Thread Ryan Bradetich
Hello all,

Here is the first pass at the unsigned data type I have been working on.

I am planning on adding these to the September 2008 commitfest wiki page.
The unsigned data type is not targeted for core, but for the uint PgFoundry
project.

The uint.c.gz file is the main source file for the uint1, uint2, and uint4
data types.
The uing.sql.gz file contains the SQL statements to add the unsigned data
type to the database.
The pg_atoui.c.gz file is based off the function in the PostgreSQL source
code but works for unsigned data types instead of signed data types.
The Makefile is used to build the unsigned data type shared library on
Linux.

The tests.tar.gz is my unit test suit that I worked on to make sure the
unsigned integer types worked as expected.

The tests cover cases like:
* table creation with the unsigned integer types.
* comparision operations.
* INSERT statements (binary and text forms).
* COPY statements (binary and text forms).
* unique btree index support.

In addition to correctness issues, I would also appreciate feedback on best
practices and portability concerns.

For example:
   I doubt my Makefiles are very portable.
   What is the proper solution to handle this?  pgxs?

Thanks,

- Ryan


uint.c.gz
Description: GNU Zip compressed data


uint.sql.gz
Description: GNU Zip compressed data


pg_atoui.c.gz
Description: GNU Zip compressed data


Makefile
Description: Binary data


tests.tar.gz
Description: GNU Zip compressed data

-- 
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] proposal sql: labeled function params

2008-08-15 Thread Pavel Stehule
2008/8/14 Hannu Krosing [EMAIL PROTECTED]:
 On Thu, 2008-08-14 at 11:56 +0200, Pavel Stehule wrote:
 Hello

 I propose enhance current syntax that allows to specify label for any
 function parameter:

 fcename(expr [as label], ...)
 fcename(colname, ...)

 also fcename(localvar, ...) if called from another function ?

juju

 How is this supposed to interact with argument names ?

There is no interaction between argument names and labels. Primary
case is an using together variadic function where mostly parameters
are in one real parameter.


 I would to allow  same behave of custom functions like xmlforest function:
 postgres=# select xmlforest(a) from foo;
  xmlforest
 ---
  a10/a
 (1 row)

 postgres=# select xmlforest(a as b) from foo;
  xmlforest
 ---
  b10/b
 (1 row)

 Why not just have two arguments to xmlforest(label text,value text) like
 this:

 select xmlforest('b', a) from foo


syntax ... (a as b) is used now in SQL/XML - it's not new concept.
This concept allows shorter queries, because in some cases (where
column name is same as label name), you don't need write label. So
this is only generalisation of actually used concept.

 ?

 Actually I am not sure what is best way for PL languages for acces to
 these info. Using some system variables needed new column in pg_proc,
 because collecting these needs some time and in 99% cases we don't
 need it.

 Exactly, maybe it is just a bad idea in general to pass the label info
 into functions using some special syntax ?

 what is wrong with passing it in regular arguments ?


I thougs about $0 - but it's occupeted now.


 I see very little gain from complicating the syntax (and function API).

 maybe we will some time have keyword arguments as well and then have to
 deal with syntax like

 select func(arg4=7 as 'labelfor4')


it's inconsistent with column labels :(. So better select func(arg4=7
as labelfor4) or select func(arg4=column4), ... but his syntax
collidate with boolean expression. Oracle use = operator

Sal_raise(Sal_incr=500, Emp_id=7369) and I am for respect this syntax.



 So I prefere some system function that returns labels for
 outer function call. Like

 -- test
 create function getlabels() returns varchar[] as $$select '{name,
 age}'::varchar[]$$ language sql immutable;

 create or replace function json(variadic varchar[])
 returns varchar as $$
 select '[' || array_to_string(
  array(
 select (getlabels())[i]|| ':' || $1[i]
from generate_subscripts($1,1) g(i))
,',') || ']'
 $$ language sql immutable strict;

 just write the function to take arguments as pairs (value, 'label', ...)

 select json('Zdenek', 'name','30', 'age');

 select json(name, 'name', age, 'age') from person;

it's possible, sure. But then I have to repeat label when label is
known from column name.



 postgres=# select json('Zdenek' as name,'30' as age);
  json
 --
  [name:Zdenek,age:30]
 (1 row)

 postgres=# select json(name, age) from person;
  json
 --
  [name:Zdenek,age:30]
 (1 row)

 why special-case table fields ?

???
or structured types .. there isn't case, you can put inside only one
scalar attribut - but it's true - it would to accept fields some like:

create type person as (name varchar, age integer);

select json(a.name, a.age, b.name, b.age) from ...
and generate [a.name: , a.age: 

 what if you wanted to rename any table fields ?


it's not renaming, It's function metadata (and default is generated
from column names).

 There are two possibilities
   a) collect labels in parse time
   b) collect labels in executor time

 @a needs info in pg_proc, but it is simpler, @b is little bit
 difficult, but doesn't need any changes in system catalog. I thinking
 about b now.

 Necessary changes:
 =
 labels are searched in parse tree fcinfo-flinfo-fn_expr. I need
 insert label into parse tree, so I it needs special node
 labeled_param, For getting column reference I need to put current
 exprstate to fcinfo.  Function getlabels() should take code from
 ExecEvalVar function.

 Any notes, ideas?

 To me, this whole thing feels backwards - in described cases labels
 seem to be just like any other data and I don't think it justifies a
 special syntax.

 ---
 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] proposal sql: labeled function params

2008-08-15 Thread Pavel Stehule
2008/8/15 Tom Lane [EMAIL PROTECTED]:
 Hannu Krosing [EMAIL PROTECTED] writes:
 How is this supposed to interact with argument names ?

 Yeah, the real problem with this proposal is that it conscripts a syntax
 that we'll probably want to use in the future for argument-name-based
 parameter matching.  The proposed behavior is not nearly as useful as
 that would be.

It isn't. As Hannu showed these features should live in harmony (if we
will accept Oracle's syntax). I see as real problem new column in
pg_proc, that allow quickly chose between labeled and non labeled
functions - and then collect labels in parse time. This is way for
adding parameter info into variadic function - it's main goal of this
proposal. Without column in pg_proc it could same slowdowns like first
variadic function's implementation.

Regards
Pavel Stehule

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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread alexander lunyov


Thanks for the tip.
I want to try new pg_dump to connect to old server, but i can't - old
postgres doesn't listening to network socket. Why postgres 6.5.3 not
binding to network socket? It started with this line:

su pgsql -c '/usr/local/pgsql/bin/postmaster -d 5 -p 5432 -S -o -e -F
-D /usr/local/pgsql/data'  /var/log/postgres.log 21 

There is no errors in logfile about network socket, and postgres not
listening to network:

# sockstat | grep 5432
#

While postgres is up and running

# ps ax | grep postgres
47034  ??  Is 0:00.03 /usr/local/pgsql/bin/postmaster -d 5 -p 5432
-S -o -e -F -D /usr/local/pgsql/data (postgres)

And here's the logfile:

FindExec: found /usr/local/pgsql/bin/postgres using argv[0]
binding ShmemCreate(key=52e2c1, size=1001472)


Kenneth Marshall wrote:

When upgrading, you use the pg_dump from the new version to
dump the old database. Then it can take care of incidental
changes during the process. I think that the mailing list
archives have articles on upgrading from v6.5. I do not think
that you can go straight from v6.5 to v8.3. You will almost
certainly need to use v7.2-7.4 as a intermediate step, maybe
v8.0 will work, check the list archives. Good luck.

Cheers,
Ken

On Thu, Aug 14, 2008 at 10:34:11AM -0400, David Blewett wrote:

On Thu, Aug 14, 2008 at 9:58 AM, alexander lunyov [EMAIL PROTECTED] wrote:

Hello everybody.

We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3.
I need to migrate four DBs from old server to new server (FreeBSD 6.2,
PostgreSQL 8.3.1).

Just an FYI: I advised Alexander to post here, because I thought some
of the devs might have older pg installs/dump tools and might be able
to give some advice.

David Blewett

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






--
alexander lunyov

--
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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Zdenek Kotala
I think that you use to much new pg_dump. I'm not sure but my expectation is 
that latest pg_dump version does not support too old PG. The problem there could 
be network protocol. Currently only version 3 and 2 is supported. I recommend 
you to jump to 7.x (7.2) first and use 7.2 pg_dump and after that to 7.4 and 
8.3. IIRC there could be problem also with foreign keys.


Zdenek

alexander lunyov napsal(a):


Thanks for the tip.
I want to try new pg_dump to connect to old server, but i can't - old
postgres doesn't listening to network socket. Why postgres 6.5.3 not
binding to network socket? It started with this line:

su pgsql -c '/usr/local/pgsql/bin/postmaster -d 5 -p 5432 -S -o -e -F
-D /usr/local/pgsql/data'  /var/log/postgres.log 21 

There is no errors in logfile about network socket, and postgres not
listening to network:

# sockstat | grep 5432
#

While postgres is up and running

# ps ax | grep postgres
47034  ??  Is 0:00.03 /usr/local/pgsql/bin/postmaster -d 5 -p 5432
-S -o -e -F -D /usr/local/pgsql/data (postgres)

And here's the logfile:

FindExec: found /usr/local/pgsql/bin/postgres using argv[0]
binding ShmemCreate(key=52e2c1, size=1001472)


Kenneth Marshall wrote:

When upgrading, you use the pg_dump from the new version to
dump the old database. Then it can take care of incidental
changes during the process. I think that the mailing list
archives have articles on upgrading from v6.5. I do not think
that you can go straight from v6.5 to v8.3. You will almost
certainly need to use v7.2-7.4 as a intermediate step, maybe
v8.0 will work, check the list archives. Good luck.

Cheers,
Ken

On Thu, Aug 14, 2008 at 10:34:11AM -0400, David Blewett wrote:

On Thu, Aug 14, 2008 at 9:58 AM, alexander lunyov [EMAIL PROTECTED] wrote:

Hello everybody.

We have a dusty old server, FreeBSD 3.3-RELEASE, PostgreSQL 6.5.3.
I need to migrate four DBs from old server to new server (FreeBSD 6.2,
PostgreSQL 8.3.1).

Just an FYI: I advised Alexander to post here, because I thought some
of the devs might have older pg installs/dump tools and might be able
to give some advice.

David Blewett

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









--
Zdenek Kotala  Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql


--
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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Guillaume Smet
On Fri, Aug 15, 2008 at 9:54 AM, alexander lunyov [EMAIL PROTECTED] wrote:
 Thanks for the tip.
 I want to try new pg_dump to connect to old server, but i can't - old
 postgres doesn't listening to network socket. Why postgres 6.5.3 not
 binding to network socket? It started with this line:

(Forgot the list, sorry for the double email, Alexander)

Do you have something like tcpip_socket in your postgresql.conf (I
don't know if it was in 6.5)? If so, you have to set it to true to
make PostgreSQL listen on the network.

As for the upgrade path, I'll go with:
- dump with 7.3 pg_dump, insert your dump into a 7.3 db and run
adddepends contrib shipped with 7.3 on your db (it should fix the
foreign key problem reported by Zdenek);
- then dump your 7.3 db with 8.3 pg_dump and insert your dump into
your 8.3 server.

--
Guillaume

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


[HACKERS] XML / XSL rendering in PostgreSQL server?

2008-08-15 Thread Peter Sampson
Hi,

I would like to use one of the XML rendering functions like
table_to_xml_and_xmlschema OR table_to_xml and render the output via XSL,
preferably in one query.

I've searched the mailing lists,site, docs and Google -- to no avail. Also,
I see that xslt_process (from xml2) will be deprecated going forward. We're
building an app that will be used for a long time into the future, and I'd
like to keep it future-compatible.

I've also searched online for sql / xml docs or tutorials, but haven't found
any that directly addresses using xsl in queries itself.

First, is this even possible without  xslt_process ? If so, how?

Any ideas?

Thanks a lot!

Regards,

Peter Sampson


Re: [HACKERS] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread alexander lunyov

Guillaume Smet wrote:

I want to try new pg_dump to connect to old server, but i can't - old
postgres doesn't listening to network socket. Why postgres 6.5.3 not
binding to network socket? It started with this line:

Do you have something like tcpip_socket in your postgresql.conf (I
don't know if it was in 6.5)? If so, you have to set it to true to
make PostgreSQL listen on the network.


i didn't find anything like postgresql.conf on old server. Right now i'm 
tried to start 6.5.3 on windows (downloaded binary from ftp archive on 
postgresql.org, installed last cygwin) with the data dir from old 
server, but there's errors:


D:\pgsql\bind:\pgsql\bin\postgres -p 5432 -D d:\\pgsql\\data
  6 [main] postgres 2820 _cygtls::handle_exceptions: Exception: 
STATUS_ACCESS_VIOLATION
426 [main] postgres 2820 open_stackdumpfile: Dumping stack trace to 
postgres.exe.stackdump
  27692 [main] postgres 2820 _cygtls::handle_exceptions: Exception: 
STATUS_ACCESS_VIOLATION
  28338 [main] postgres 2820 _cygtls::handle_exceptions: Error while 
dumping state (probably corrupted stack)


dump:

Exception: STATUS_ACCESS_VIOLATION at eip=0044EBE3
eax= ebx=00506A90 ecx= edx=0001 esi=00506AD1 
edi=004E83FC
ebp=02862BBC esp=02862BA8 program=d:\pgsql\bin\postgres.exe, pid 2820, 
thread main

cs=001B ds=0023 es=0023 fs=003B gs= ss=0023
Stack trace:
Frame Function  Args
02862BBC  0044EBE3  (0045, 02862BF8, , )
02868BF8  004E5A93  (0001, 004E83C8, , 0286CC80)
02868C0C  004E8460  (04870150, 0286CCF8, 0044F5A4, )
0286CC80  004B3518  (0005, 04870150, 0005, 04870150)
0286CCB4  0044F698  (0005, 04870150, 04870090, 6003)
0286CDA4  610060D8  (, 0286CDDC, 61005450, 0286CDDC)
61005450  61004416  (009C, A02404C7, E8611021, FF48)
  27692 [main] postgres 2820 _cygtls::handle_exceptions: Exception: 
STATUS_ACCESS_VIOLATION
  28338 [main] postgres 2820 _cygtls::handle_exceptions: Error while 
dumping state (probably corrupted stack)


Also tried to compile 6.5.3 from source on newer freebsd - configure 
doesn't do Makefiles, errors are:



creating GNUmakefile
sed: 35: conftest.s1: unescaped newline inside substitute pattern

and for all Makefiles this message, and Makefiles are empty, so i can't 
compile it.


:(


As for the upgrade path, I'll go with:
- dump with 7.3 pg_dump, insert your dump into a 7.3 db and run
adddepends contrib shipped with 7.3 on your db (it should fix the
foreign key problem reported by Zdenek);
- then dump your 7.3 db with 8.3 pg_dump and insert your dump into
your 8.3 server.


Ok, that's understood, thanks (thank you too, Zdenek).
But how can i connect with newer pg_dump to this old server, if it's 
doesn't make a socket to connect to?


--
С уважением
Александр Лунев
МП РТК

--
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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Andreas Pflug

alexander lunyov wrote:

Guillaume Smet wrote:

I want to try new pg_dump to connect to old server, but i can't - old
postgres doesn't listening to network socket. Why postgres 6.5.3 not
binding to network socket? It started with this line: 


Maybe you should just dump schema and data separately with your old 
pg_dump tool, then rework the schema for 8.3 manually.


Regards,
Andreas


--
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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread alexander lunyov

Andreas Pflug wrote:

I want to try new pg_dump to connect to old server, but i can't - old
postgres doesn't listening to network socket. Why postgres 6.5.3 not
binding to network socket? It started with this line: 


Maybe you should just dump schema and data separately with your old 
pg_dump tool, then rework the schema for 8.3 manually.


I can do this, but i don't know how to rework it.

psql:dump:389: WARNING:  aggregate attribute sfunc2 not recognized
psql:dump:389: WARNING:  aggregate attribute stype2 not recognized
psql:dump:389: WARNING:  aggregate attribute initcond2 not recognized
psql:dump:389: ERROR:  function int84div(bigint) does not exist
psql:dump:390: ERROR:  function int4div(integer) does not exist
psql:dump:391: ERROR:  function int2div(smallint) does not exist
psql:dump:392: ERROR:  function float4div(real) does not exist
psql:dump:393: ERROR:  function float8div(double precision) does not exist
psql:dump:394: ERROR:  function cash_div_flt8(money) does not exist
psql:dump:395: ERROR:  type timespan does not exist
psql:dump:396: ERROR:  function numeric_div(numeric) does not exist
psql:dump:410: ERROR:  function int4larger(abstime, abstime) does not exist
psql:dump:422: ERROR:  function int4smaller(abstime, abstime) does not exist
psql:dump:413: ERROR:  type datetime does not exist
psql:dump:429: ERROR:  aggregate stype must be specified

If i understand it right, much of this errors are about AGGREGATEs (they 
are a part of schema):


CREATE AGGREGATE avg ( BASETYPE = int8,  SFUNC1 = int8pl, STYPE1 = int8, 
INITCOND1 = '', SFUNC2 = int4inc, STYPE2 = int4, INITCOND

2 = '0', FINALFUNC = int84div );

CREATE AGGREGATE avg ( BASETYPE = int4,  SFUNC1 = int4pl, STYPE1 = int4, 
INITCOND1 = '', SFUNC2 = int4inc, STYPE2 = int4, INITCOND

2 = '0', FINALFUNC = int4div );

CREATE AGGREGATE avg ( BASETYPE = int2,  SFUNC1 = int2pl, STYPE1 = int2, 
INITCOND1 = '', SFUNC2 = int2inc, STYPE2 = int2, INITCOND

2 = '0', FINALFUNC = int2div );

CREATE AGGREGATE avg ( BASETYPE = float4,  SFUNC1 = float4pl, STYPE1 = 
float4, INITCOND1 = '', SFUNC2 = float4inc, STYPE2 = float4

, INITCOND2 = '0.0', FINALFUNC = float4div );

CREATE AGGREGATE avg ( BASETYPE = float8,  SFUNC1 = float8pl, STYPE1 = 
float8, INITCOND1 = '', SFUNC2 = float8inc, STYPE2 = float8

, INITCOND2 = '0.0', FINALFUNC = float8div );

CREATE AGGREGATE avg ( BASETYPE = money,  SFUNC1 = cash_pl, STYPE1 = 
money, INITCOND1 = '', SFUNC2 = float8inc, STYPE2 = float8, I

NITCOND2 = '0.0', FINALFUNC = cash_div_flt8 );

CREATE AGGREGATE avg ( BASETYPE = timespan,  SFUNC1 = timespan_pl, 
STYPE1 = timespan, INITCOND1 = '', SFUNC2 = float8inc, STYPE2 =

 float8, INITCOND2 = '0.0', FINALFUNC = timespan_div );

CREATE AGGREGATE avg ( BASETYPE = numeric,  SFUNC1 = numeric_add, STYPE1 
= numeric, INITCOND1 = '', SFUNC2 = numeric_inc, STYPE2 =

 numeric, INITCOND2 = '0', FINALFUNC = numeric_div );

So, i have to replace these float4div to some new equivalent? And where 
can i find those equivalents for all these functions and special words, 
that doesn't exists?


--
alexander lunyov

--
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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Guillaume Smet
On Fri, Aug 15, 2008 at 11:42 AM, alexander lunyov [EMAIL PROTECTED] wrote:
 i didn't find anything like postgresql.conf on old server. Right now i'm
 tried to start 6.5.3 on windows (downloaded binary from ftp archive on
 postgresql.org, installed last cygwin) with the data dir from old server,
 but there's errors:

Add the -i option to your startup command line. You should then be
able to connect using the network.

That said, you should also be able to connect using the unix domain
socket using -h /path/to/domain option.

-- 
Guillaume

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


[HACKERS] Reporting the bind-parameter on an error

2008-08-15 Thread Fujii Masao
Hi,

Currently, the SQL statement that causes an error condition is reported
in the server log. On the other hand, the bind-parameter is not reported
as following.

 FATAL:  terminating connection due to administrator command
 STATEMENT:  UPDATE tbl SET name = $1 WHERE id = $2
 LOG:  shutting down

The bind-parameter is useful information for debugging. So, I made
the small patch which reports the bind-parameter together with
the SQL statement on an error.

 FATAL:  terminating connection due to administrator command
 STATEMENT:  UPDATE tbl SET name = $1 WHERE id = $2
 DETAIL:  parameters: $1 = 'hoge', $2 = '1'
 LOG:  shutting down

Any comments welcome!

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


param_patch.diff
Description: Binary data

-- 
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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Andreas Pflug

alexander lunyov wrote:

Andreas Pflug wrote:

I want to try new pg_dump to connect to old server, but i can't - old
postgres doesn't listening to network socket. Why postgres 6.5.3 not
binding to network socket? It started with this line: 


Maybe you should just dump schema and data separately with your old 
pg_dump tool, then rework the schema for 8.3 manually.


I can do this, but i don't know how to rework it.
I wonder if you need these self defined aggregates at all, most or all 
of them are in 8.3 already.


Regards,
Andreas


--
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: plan invalidation vs stored procedures

2008-08-15 Thread Martin Pihlak
Tom Lane wrote:
 Martin Pihlak [EMAIL PROTECTED] writes:
 Changing statement result type is also currently prohibited in
 StorePreparedStatement. There maybe good reasons for this,
 
 How about the SQL spec says so?
 
 Admittedly, it's a bit of a jump from views to prepared statements,
 but the spec is perfectly clear that altering a table doesn't alter
 any views dependent on it: SQL99 11.11 add column definition saith

As you said it is a bit of a jump ... For one thing view definitions are
persistent whereas statements are bound to be replanned sooner or later -
reconnects etc. Disallowing replanning after invalidation just postpones
it and meanwhile the cached plans are left unusable (cached plan must not
change result). IMHO the problem should be left for the application to handle.
Because this is where it will end up anyway.

Attached is a patch that implements plan invalidation on function DROP,
REPLACE and ALTER.  Function oids used by the query are collected in analyze 
phase
and stored in PlannedStmt. Only plans that reference the altered function are
invalidated. The patch also enables replanning on result set change.

regards,
Martin

Index: src/backend/commands/functioncmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/functioncmds.c,v
retrieving revision 1.98
diff -c -r1.98 functioncmds.c
*** src/backend/commands/functioncmds.c	18 Jul 2008 03:32:52 -	1.98
--- src/backend/commands/functioncmds.c	15 Aug 2008 11:12:51 -
***
*** 59,64 
--- 59,65 
  #include utils/rel.h
  #include utils/syscache.h
  #include utils/tqual.h
+ #include utils/inval.h
  
  
  static void AlterFunctionOwner_internal(Relation rel, HeapTuple tup,
***
*** 680,685 
--- 681,687 
  	HeapTuple	languageTuple;
  	Form_pg_language languageStruct;
  	List	   *as_clause;
+ 	Oid			funcOid;
  
  	/* Convert list of names to a name and namespace */
  	namespaceId = QualifiedNameGetCreationNamespace(stmt-funcname,
***
*** 817,823 
  	 * And now that we have all the parameters, and know we're permitted to do
  	 * so, go ahead and create the function.
  	 */
! 	ProcedureCreate(funcname,
  	namespaceId,
  	stmt-replace,
  	returnsSet,
--- 819,825 
  	 * And now that we have all the parameters, and know we're permitted to do
  	 * so, go ahead and create the function.
  	 */
! 	funcOid = ProcedureCreate(funcname,
  	namespaceId,
  	stmt-replace,
  	returnsSet,
***
*** 837,842 
--- 839,848 
  	PointerGetDatum(proconfig),
  	procost,
  	prorows);
+ 
+ 	/* Send invalidation on REPLACE */
+ 	if (stmt-replace)
+ 		CacheInvalidateProcedure(funcOid);
  }
  
  
***
*** 906,911 
--- 912,920 
  	object.objectSubId = 0;
  
  	performDeletion(object, stmt-behavior);
+ 
+ 	/* Notify that cached plans should be replanned */
+ 	CacheInvalidateProcedure(funcOid);
  }
  
  /*
***
*** 1029,1034 
--- 1038,1046 
  
  	heap_close(rel, NoLock);
  	heap_freetuple(tup);
+ 
+ 	/* Need plan invalidation after this */
+ 	CacheInvalidateProcedure(procOid);
  }
  
  /*
***
*** 1294,1299 
--- 1306,1314 
  
  	heap_close(rel, NoLock);
  	heap_freetuple(tup);
+ 
+ 	/* Invalidate plans after this */
+ 	CacheInvalidateProcedure(funcOid);
  }
  
  /*
Index: src/backend/commands/prepare.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/prepare.c,v
retrieving revision 1.89
diff -c -r1.89 prepare.c
*** src/backend/commands/prepare.c	21 Jul 2008 15:26:55 -	1.89
--- src/backend/commands/prepare.c	15 Aug 2008 11:12:52 -
***
*** 188,196 
  	/* Shouldn't have a non-fully-planned plancache entry */
  	if (!entry-plansource-fully_planned)
  		elog(ERROR, EXECUTE does not support unplanned prepared statements);
- 	/* Shouldn't get any non-fixed-result cached plan, either */
- 	if (!entry-plansource-fixed_result)
- 		elog(ERROR, EXECUTE does not support variable-result cached plans);
  
  	/* Evaluate parameters, if any */
  	if (entry-plansource-num_params  0)
--- 188,193 
***
*** 462,468 
    cursor_options,
    stmt_list,
    true,
!   true);
  
  	/* Now we can add entry to hash table */
  	entry = (PreparedStatement *) hash_search(prepared_queries,
--- 459,465 
    cursor_options,
    stmt_list,
    true,
!   false);
  
  	/* Now we can add entry to hash table */
  	entry = (PreparedStatement *) hash_search(prepared_queries,
***
*** 523,533 
  TupleDesc
  FetchPreparedStatementResultDesc(PreparedStatement *stmt)
  {
! 	/*
! 	 * Since we don't allow prepared statements' result tupdescs to change,
! 	 * there's no need for a revalidate call here.
! 	 */
! 	Assert(stmt-plansource-fixed_result);
  	if 

Re: [HACKERS] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread alexander lunyov

Guillaume Smet wrote:

On Fri, Aug 15, 2008 at 11:42 AM, alexander lunyov [EMAIL PROTECTED] wrote:

i didn't find anything like postgresql.conf on old server. Right now i'm
tried to start 6.5.3 on windows (downloaded binary from ftp archive on
postgresql.org, installed last cygwin) with the data dir from old server,
but there's errors:


Add the -i option to your startup command line. You should then be
able to connect using the network.


Thanks a lot! It did a trick, so now i can connect to it. I followed 
your advice and installed 7.3 client and tried to dump and get this error:


[EMAIL PROTECTED]@root # pg_dump -U ira -h 192.168.0.3 itt_user
pg_dump: [archiver (db)] connection to database itt_user failed: 
ERROR:  MultiByte strings (MB) must be enabled to use this function


I searched for meaning of this error, and found solution here:

http://archives.postgresql.org/pgsql-general/2000-09/msg00489.php

Recompile your 7.0.2 without --enable-multibyte option.

but in 7.3 configure no such option.

--
alexander lunyov

--
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] XML / XSL rendering in PostgreSQL server?

2008-08-15 Thread Tino Wildenhain

Hi,

Peter Sampson wrote:

Hi,

I would like to use one of the XML rendering functions like 
table_to_xml_and_xmlschema OR table_to_xml and render the output via 
XSL, preferably in one query.


What do you think would be the benefit of doing that?

I've searched the mailing lists,site, docs and Google -- to no avail. 
Also, I see that xslt_process (from xml2) will be deprecated going 
forward. We're building an app that will be used for a long time into 
the future, and I'd like to keep it future-compatible.


I've also searched online for sql / xml docs or tutorials, but haven't 
found any that directly addresses using xsl in queries itself.


You could try with one of the pl/*u languages.

Regards
Tino


smime.p7s
Description: S/MIME Cryptographic Signature


Re: [HACKERS] XML / XSL rendering in PostgreSQL server?

2008-08-15 Thread Peter Sampson
Tino, thanks for your reply

I would like to use one of the XML rendering functions like
 table_to_xml_and_xmlschema OR table_to_xml and render the output via XSL,
 preferably in one query.


What do you think would be the benefit of doing that?

My main reason would be to embed all of the application logic in the
postgres server. This way, we can build out the app more efficiently by not
relying on middleware. It seems like there would be fewer cycles committed
to the whole application if it was all done in the DB, but I could be wrong.

Yes, I am using pl/pgSQL for all of the internals. I could formulate and
output the data this way, but if Postgres has methods for using XSL to do
this, why not use it instead of re-inventing it (and we can keep the
public-view logic separate from the business logic in the server)?

Thanks.

Peter


On Fri, Aug 15, 2008 at 1:46 AM, Tino Wildenhain [EMAIL PROTECTED] wrote:

 Hi,

 Peter Sampson wrote:

 Hi,

 I would like to use one of the XML rendering functions like
 table_to_xml_and_xmlschema OR table_to_xml and render the output via XSL,
 preferably in one query.


 What do you think would be the benefit of doing that?

  I've searched the mailing lists,site, docs and Google -- to no avail.
 Also, I see that xslt_process (from xml2) will be deprecated going forward.
 We're building an app that will be used for a long time into the future, and
 I'd like to keep it future-compatible.

 I've also searched online for sql / xml docs or tutorials, but haven't
 found any that directly addresses using xsl in queries itself.


 You could try with one of the pl/*u languages.

 Regards
 Tino



Re: [HACKERS] proposal sql: labeled function params

2008-08-15 Thread Peter Eisentraut
Am Thursday, 14. August 2008 schrieb Pavel Stehule:
 I propose enhance current syntax that allows to specify label for any
 function parameter:

 fcename(expr [as label], ...)
 fcename(colname, ...)

 I would to allow  same behave of custom functions like xmlforest function:
 postgres=# select xmlforest(a) from foo;
  xmlforest
 ---
  a10/a
 (1 row)

Do you have a use case for this outside of XML?

-- 
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] XML / XSL rendering in PostgreSQL server?

2008-08-15 Thread Peter Eisentraut
Am Friday, 15. August 2008 schrieb Peter Sampson:
 I've searched the mailing lists,site, docs and Google -- to no avail. Also,
 I see that xslt_process (from xml2) will be deprecated going forward. We're
 building an app that will be used for a long time into the future, and I'd
 like to keep it future-compatible.

There is no future-proof XSL(T) support in PostgreSQL yet.  We need to work 
out how we want to do that.

-- 
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] proposal sql: labeled function params

2008-08-15 Thread Pavel Stehule
2008/8/15 Peter Eisentraut [EMAIL PROTECTED]:
 Am Thursday, 14. August 2008 schrieb Pavel Stehule:
 I propose enhance current syntax that allows to specify label for any
 function parameter:

 fcename(expr [as label], ...)
 fcename(colname, ...)

 I would to allow  same behave of custom functions like xmlforest function:
 postgres=# select xmlforest(a) from foo;
  xmlforest
 ---
  a10/a
 (1 row)

 Do you have a use case for this outside of XML?


JSON and similar (custom) protocols

Pavel

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


[HACKERS] varchar/name casts

2008-08-15 Thread Peter Eisentraut
With this query you can view all casts involving varchar:

SELECT castsource::regtype, casttarget::regtype, castfunc::regprocedure, 
castcontext FROM pg_cast WHERE 'varchar'::regtype IN (castsource, casttarget) 
ORDER BY 1, 2;

Note that varchar mostly borrows the cast functions from the text type.  The 
exception is that there is a separate set of SQL-level functions for casting 
between name and varchar and vice versa.  But these are actually matched to 
the same C-level functions as the casts between text and name (name_text() 
and text_name()).

Does anyone recall a reason for this special case or is it just another dark 
area in the casting maze?  If the latter, I would like to remove the extra 
functions and redefine the casts between varchar and name to use the 
SQL-level casting functions for the text type.

-- 
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] proposal sql: labeled function params

2008-08-15 Thread Peter Eisentraut
Am Friday, 15. August 2008 schrieb Tom Lane:
 Hannu Krosing [EMAIL PROTECTED] writes:
  How is this supposed to interact with argument names ?

 Yeah, the real problem with this proposal is that it conscripts a syntax
 that we'll probably want to use in the future for argument-name-based
 parameter matching.  The proposed behavior is not nearly as useful as
 that would be.

I am not at all convinced about the proposed feature, but is that really a 
syntax we would use for function calls with named parameters?

Random googling shows me that Oracle appears to use a syntax like

name = value

This is actually a feature that I would like to see implemented soonish, so if 
anyone has input on the possible syntax consequences, please comment.

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


[HACKERS] So what about XSLT?

2008-08-15 Thread Peter Eisentraut
An open task in replacing contrib/xml2 is the XSLT support, which the current 
core implementation lacks altogether.  I am known to often be in favor of a 
lean core, so I have so far been hesitant to push this further, but we should 
eventually come up with an implementation for the users' sake.

So where should XSLT functionality live:

- core
- contrib
- pgfoundry

There is also a related but minor question where other interesting XML 
functionality should live, such as pretty-printing and canonicalization.  
These would be relatively straightward passthroughs to functionality provided 
by libxml already.

Comments?

-- 
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] So what about XSLT?

2008-08-15 Thread David Fetter
On Fri, Aug 15, 2008 at 04:11:11PM +0300, Peter Eisentraut wrote:
 An open task in replacing contrib/xml2 is the XSLT support, which
 the current core implementation lacks altogether.  I am known to
 often be in favor of a lean core, so I have so far been hesitant to
 push this further, but we should eventually come up with an
 implementation for the users' sake.
 
 So where should XSLT functionality live:
 
 - core

+1.  I've heard rumors of a PL/XSLT.  Any substance to them?

 - contrib
 - pgfoundry
 
 There is also a related but minor question where other interesting
 XML functionality should live, such as pretty-printing and
 canonicalization.  These would be relatively straightward
 passthroughs to functionality provided by libxml already.
 
 Comments?

Is there some way to do a more generic (de)serialization of which one
target could be XML?  CSV could be one, JSON another, and at the
moment, it appears we're taking each of these as a totally separate
project.

Cheers,
David.
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

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] proposal sql: labeled function params

2008-08-15 Thread Pavel Stehule
Hello

2008/8/15 Peter Eisentraut [EMAIL PROTECTED]:
 Am Friday, 15. August 2008 schrieb Tom Lane:
 Hannu Krosing [EMAIL PROTECTED] writes:
  How is this supposed to interact with argument names ?

 Yeah, the real problem with this proposal is that it conscripts a syntax
 that we'll probably want to use in the future for argument-name-based
 parameter matching.  The proposed behavior is not nearly as useful as
 that would be.

 I am not at all convinced about the proposed feature, but is that really a
 syntax we would use for function calls with named parameters?

 Random googling shows me that Oracle appears to use a syntax like

name = value


I vote this syntax too. So this second feature - named params X labels
for params. Labels for params is related to my work on variadic
functions. Named params needs default param's values - and some more
of changes in parser. Somebody have to solve conflict between params
and expression.

Pavel




 This is actually a feature that I would like to see implemented soonish, so if
 anyone has input on the possible syntax consequences, please comment.


-- 
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] varchar/name casts

2008-08-15 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes:
 Note that varchar mostly borrows the cast functions from the text type.  
 The 
 exception is that there is a separate set of SQL-level functions for casting 
 between name and varchar and vice versa.  But these are actually matched to 
 the same C-level functions as the casts between text and name (name_text() 
 and text_name()).

 Does anyone recall a reason for this special case or is it just another dark 
 area in the casting maze?

I think the idea was to support the functional casting notation, viz
varchar(name_col).  However it seems we've broken that already for
most of the other cases; and in any case it never worked very nicely
for varchar because varchar is a reserved word, so you have to
quote :-(

As a historical note, in 7.3 (the first release with a pg_cast catalog)
your query gives just

castsource |casttarget |castfunc | castcontext 
---+---+-+-
 name  | character varying | varchar(name) | i
 text  | character varying | -   | i
 character | character varying | -   | i
 character varying | name  | name(character varying) | i
 character varying | text  | -   | i
 character varying | character | -   | i
(6 rows)

So it seems the cross-category casts for varchar got accreted on later,
rather than it being a case of things having disappeared.

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] proposal sql: labeled function params

2008-08-15 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes:
 Random googling shows me that Oracle appears to use a syntax like
 name = value
 This is actually a feature that I would like to see implemented soonish, so 
 if 
 anyone has input on the possible syntax consequences, please comment.

We've been over this territory before.  The problem with name = value
is that it requires reserving a perfectly good user-defined operator name.
value AS name, on the other hand, accomplishes the same in a more
SQL-looking fashion with no new reserved word (since AS is already
fully reserved).

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] So what about XSLT?

2008-08-15 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes:
 An open task in replacing contrib/xml2 is the XSLT support, which the current 
 core implementation lacks altogether.  I am known to often be in favor of a 
 lean core, so I have so far been hesitant to push this further, but we should 
 eventually come up with an implementation for the users' sake.

 So where should XSLT functionality live:

That question is unanswerable except in the context of a specific
proposal for the amount of functionality and code involved.  To take
it to extremes: if you are talking about adding 100 lines, no one will
object to putting it in core; if you are talking about adding 10
lines, some of us will object.  I suppose you are thinking of something
in between, but what?

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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Tom Lane
alexander lunyov [EMAIL PROTECTED] writes:
 I want to try new pg_dump to connect to old server, but i can't - old
 postgres doesn't listening to network socket.

It won't work anyway: modern versions of pg_dump are only designed to
work with servers back to 7.0.  I see from the rest of the thread that
you tried to bludgeon it into submission, but I'd very strongly
recommend that you abandon that approach and use 6.5's pg_dump.

A further suggestion is that you use -d or even -D option on the dump.
I think there have been some corner-case changes in COPY data format
since 6.5 days; which might or might not bite you, but why take the
chance ...

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] So what about XSLT?

2008-08-15 Thread Andrew Dunstan



Tom Lane wrote:

Peter Eisentraut [EMAIL PROTECTED] writes:
  
An open task in replacing contrib/xml2 is the XSLT support, which the current 
core implementation lacks altogether.  I am known to often be in favor of a 
lean core, so I have so far been hesitant to push this further, but we should 
eventually come up with an implementation for the users' sake.



  

So where should XSLT functionality live:



That question is unanswerable except in the context of a specific
proposal for the amount of functionality and code involved.  To take
it to extremes: if you are talking about adding 100 lines, no one will
object to putting it in core; if you are talking about adding 10
lines, some of us will object.  I suppose you are thinking of something
in between, but what?

  


well, contrib/xml2/xslt_proc.c has 172 lines. So I suggest we just 
import that to core and drop the rest of the module as redundant.


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] Reporting the bind-parameter on an error

2008-08-15 Thread Tom Lane
Fujii Masao [EMAIL PROTECTED] writes:
 The bind-parameter is useful information for debugging. So, I made
 the small patch which reports the bind-parameter together with
 the SQL statement on an error.

You can't invoke user-defined I/O functions in an already-failed
transaction.  For that matter, you shouldn't really even try to do the
catalog lookups involved in identifying them.

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] migrate data 6.5.3 - 8.3.1

2008-08-15 Thread Tom Lane
Andreas Pflug [EMAIL PROTECTED] writes:
 I wonder if you need these self defined aggregates at all, most or all 
 of them are in 8.3 already.

They aren't self defined in 6.5 either.  I think what is happening
is that he's trying to force a 7.x pg_dump to dump from the 6.5 server
(with -i no doubt), and it's just tremendously confused about what's
what and what it should dump.

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] Parsing of pg_hba.conf and authentication inconsistencies

2008-08-15 Thread Bruce Momjian
Magnus Hagander wrote:
 Magnus Hagander wrote:
 
 [about the ability to use different maps for ident auth, gss and krb
 auth for example]
 
  It wouldn't be very easy/clean to do that w/o breaking the existing
  structure of pg_ident though, which makes me feel like using seperate
  files is probably the way to go.
 
 Actually, I may have to take that back. We already have support for
 multiple maps in the ident file, I'm not really sure anymore of the case
 where this wouldn't be enough :-)
 
 That said, I still think we want to parse pg_hba in the postmaster,
 because it allows us to not load known broken files, and show errors
 when you actually change the file etc. ;-)
 
 I did code up a POC patch for it, and it's not particularly hard to do.
 Mostly it's just moving the codepath from the backend to the postmaster.
 I'll clean it up a but and post it, just so ppl can see what it looks
 like...

To address Magnus' specific question, right now we store the pg_hba.conf
tokens as strings in the postmaster.  I am fine with storing them in a
more native format and throwing errors for values that don't convert. 
What would concern me is calling lots of 3rd party libraries from the
postmaster to validate items.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] So what about XSLT?

2008-08-15 Thread Tom Lane
Andrew Dunstan [EMAIL PROTECTED] writes:
 Peter Eisentraut [EMAIL PROTECTED] writes:
 An open task in replacing contrib/xml2 is the XSLT support, which the 
 current 
 core implementation lacks altogether.

 well, contrib/xml2/xslt_proc.c has 172 lines. So I suggest we just 
 import that to core and drop the rest of the module as redundant.

I assume that wouldn't provide the functionality Peter wants; else the
above would have happened already in 8.3.

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] Mini improvement: statement_cost_limit

2008-08-15 Thread Bruce Momjian
Josh Berkus wrote:
 Greg,
 
  Well that's going to depend on the application But I suppose there's
  nothing wrong with having options which aren't always a good idea to use. 
  The
  real question I guess is whether there's ever a situation where it would be 
  a
  good idea to use this. I'm not 100% sure.
 
 I can think of *lots*.   Primarily, simple web applications, where 
 queries are never supposed to take more than 50ms.  If a query turns up 
 with an estimated cost of 100, then you know something's wrong; 
 in the statistics if not in the query.  In either case, that query has a 
 good chance of dragging down the whole system.
 
 In such a production application, it is better to have false positives 
 and reject otherwise-OK queries becuase their costing is wrong, than to 
 let a single cartesian join bog down an application serving 5000 
 simultaneous users.  Further, with a SQL error, this would allow the 

How about a simpler approach that throws an error or warning for
cartesian products?  That seems fool-proof.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] Mini improvement: statement_cost_limit

2008-08-15 Thread Ron Mayer

Bruce Momjian wrote:

Josh Berkus wrote:
...simple web applications, where 
queries are never supposed to take more than 50ms.  If a query turns up 
with an estimated cost of 100, then you know something's wrong; 
...


How about a simpler approach that throws an error or warning for
cartesian products?  That seems fool-proof.


Seems less fool-proof to me.

Sometimes cartesian products produce plans that run 200 times
faster than plans that don't use the cartesian product.

The first link below shows a cartesian join that took 1.1
seconds (within the range of OK for some web apps), while
plans for the same query that don't use one took 200 seconds.

http://archives.postgresql.org/pgsql-performance/2008-03/msg00391.php
http://archives.postgresql.org/pgsql-performance/2007-12/msg00090.php
http://archives.postgresql.org/pgsql-performance/2008-03/msg00361.php


--
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] Mini improvement: statement_cost_limit

2008-08-15 Thread Bruce Momjian
Ron Mayer wrote:
 Bruce Momjian wrote:
  Josh Berkus wrote:
  ...simple web applications, where 
  queries are never supposed to take more than 50ms.  If a query turns up 
  with an estimated cost of 100, then you know something's wrong; 
  ...
  
  How about a simpler approach that throws an error or warning for
  cartesian products?  That seems fool-proof.
 
 Seems less fool-proof to me.
 
 Sometimes cartesian products produce plans that run 200 times
 faster than plans that don't use the cartesian product.
 
 The first link below shows a cartesian join that took 1.1
 seconds (within the range of OK for some web apps), while
 plans for the same query that don't use one took 200 seconds.
 
 http://archives.postgresql.org/pgsql-performance/2008-03/msg00391.php
 http://archives.postgresql.org/pgsql-performance/2007-12/msg00090.php
 http://archives.postgresql.org/pgsql-performance/2008-03/msg00361.php

My point is that people should _know_ they are using a cartesian
product, and a warning would do that for users who have no need for a
cartesian product and want to be warned about a possible error.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] modifying views

2008-08-15 Thread Martijn van Oosterhout
On Thu, Aug 14, 2008 at 05:57:26PM -0500, Decibel! wrote:
 FWIW, there is desire to be able to re-order columns within real  
 tables, too. But before that can happen we need to divorce  
 presentation order from on-page order (which is actually desirable  
 for other reasons), but that's an awfully big task that no one's  
 taken on.

Actually, ISTR that someone posted a patch and it was rejected on the
basis that it made the backend coding too confusing and would cause
bugs to creep in (by using the wrong position during coding). I don't
buy the argument though, since the end goal is to have logical position
!= physical position, so I don't see how bugs could survive very long.

Have a nice day,
-- 
Martijn van Oosterhout   [EMAIL PROTECTED]   http://svana.org/kleptog/
 Please line up in a tree and maintain the heap invariant while 
 boarding. Thank you for flying nlogn airlines.

-- 
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] Automatic Client Failover

2008-08-15 Thread Bruce Momjian
Simon Riggs wrote:
 When primary server fails, it would be good if the clients connected to
 the primary knew to reconnect to the standby servers automatically.
 
 We might want to specify that centrally and then send the redirection
 address to the client when it connects. Sounds like lots of work though.
 
 Seems fairly straightforward to specify a standby connection service at
 client level: .pgreconnect, or pgreconnect.conf
 No config, then option not used.
 
 Would work with various forms of replication.
 
 Implementation would be to make PQreset() try secondary connection if
 the primary one fails to reset. Of course you can program this manually,
 but the feature is that you wouldn't need to, nor would you need to
 request changes to 27 different interfaces either.

I assumed share/pg_service.conf would help in this regard;  place the
file on a central server and modify that so everyone connects to another
server. Perhaps we could even add round-robin functionality to that.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] modifying views

2008-08-15 Thread Andrew Dunstan



Martijn van Oosterhout wrote:

On Thu, Aug 14, 2008 at 05:57:26PM -0500, Decibel! wrote:
  
FWIW, there is desire to be able to re-order columns within real  
tables, too. But before that can happen we need to divorce  
presentation order from on-page order (which is actually desirable  
for other reasons), but that's an awfully big task that no one's  
taken on.



Actually, ISTR that someone posted a patch and it was rejected on the
basis that it made the backend coding too confusing and would cause
bugs to creep in (by using the wrong position during coding). I don't
buy the argument though, since the end goal is to have logical position
!= physical position, so I don't see how bugs could survive very long.


  



NO, IIRC it was rejected because it didn't implement what we wanted, 
namely Tom's three-number scheme (immutable id, plus mutable logical and 
physical order).


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] Parsing of pg_hba.conf and authentication inconsistencies

2008-08-15 Thread Magnus Hagander
Bruce Momjian wrote:
 Magnus Hagander wrote:
 Magnus Hagander wrote:

 [about the ability to use different maps for ident auth, gss and krb
 auth for example]

 It wouldn't be very easy/clean to do that w/o breaking the existing
 structure of pg_ident though, which makes me feel like using seperate
 files is probably the way to go.
 Actually, I may have to take that back. We already have support for
 multiple maps in the ident file, I'm not really sure anymore of the case
 where this wouldn't be enough :-)

 That said, I still think we want to parse pg_hba in the postmaster,
 because it allows us to not load known broken files, and show errors
 when you actually change the file etc. ;-)

 I did code up a POC patch for it, and it's not particularly hard to do.
 Mostly it's just moving the codepath from the backend to the postmaster.
 I'll clean it up a but and post it, just so ppl can see what it looks
 like...
 
 To address Magnus' specific question, right now we store the pg_hba.conf
 tokens as strings in the postmaster.  I am fine with storing them in a
 more native format and throwing errors for values that don't convert. 
 What would concern me is calling lots of 3rd party libraries from the
 postmaster to validate items.

If I was unclear about that, that part was never part of what I
proposed. I'm only talking aobut parsing the syntax. The only external
calls in the code there now is the getaddrinfo calls to convert the IPs,
IIRC.

//Magnus

-- 
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] Automatic Client Failover

2008-08-15 Thread Simon Riggs

On Fri, 2008-08-15 at 12:24 -0400, Bruce Momjian wrote:
 Simon Riggs wrote:
  When primary server fails, it would be good if the clients connected to
  the primary knew to reconnect to the standby servers automatically.
  
  We might want to specify that centrally and then send the redirection
  address to the client when it connects. Sounds like lots of work though.
  
  Seems fairly straightforward to specify a standby connection service at
  client level: .pgreconnect, or pgreconnect.conf
  No config, then option not used.
  
  Would work with various forms of replication.
  
  Implementation would be to make PQreset() try secondary connection if
  the primary one fails to reset. Of course you can program this manually,
  but the feature is that you wouldn't need to, nor would you need to
  request changes to 27 different interfaces either.
 
 I assumed share/pg_service.conf would help in this regard;  place the
 file on a central server and modify that so everyone connects to another
 server. Perhaps we could even add round-robin functionality to that.

I do want to keep it as simple as possible, but we do need a way that
will work without reconfiguration at the time of danger. It needs to be
preconfigured and tested, then change controlled so we all know it
works.

-- 
 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] Parsing of pg_hba.conf and authentication inconsistencies

2008-08-15 Thread Bruce Momjian
Magnus Hagander wrote:
  To address Magnus' specific question, right now we store the pg_hba.conf
  tokens as strings in the postmaster.  I am fine with storing them in a
  more native format and throwing errors for values that don't convert. 
  What would concern me is calling lots of 3rd party libraries from the
  postmaster to validate items.
 
 If I was unclear about that, that part was never part of what I
 proposed. I'm only talking aobut parsing the syntax. The only external
 calls in the code there now is the getaddrinfo calls to convert the IPs,
 IIRC.

That seems safe to me.  The use of strings for the pg_hba.conf content
was only for convenience;  I can see the advantage of using a more
natural format.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] proposal sql: labeled function params

2008-08-15 Thread Hannu Krosing
On Fri, 2008-08-15 at 10:01 -0400, Tom Lane wrote:
 Peter Eisentraut [EMAIL PROTECTED] writes:
  Random googling shows me that Oracle appears to use a syntax like
  name = value
  This is actually a feature that I would like to see implemented soonish, so 
  if 
  anyone has input on the possible syntax consequences, please comment.
 
 We've been over this territory before.  The problem with name = value
 is that it requires reserving a perfectly good user-defined operator name.

We could declare, that using operator = in function argument expression
requires parenthesis : func( a = (b = c) means param a with value
expression (b = c) nad just func((b = c)) means first param with value
(b=c)

the main use of named params is calling functions with default values,
and giving some params. there I'm more concerned about default args and
rules for finding right function in presence of functions with both
multiple args and default values for some.

create function f(a int) ...

create function f(a int, b int default 7) 

create function f(text text)

and then calling f(4) - which one would it call

what about f('4')

Of course, we could also have default values without named params, and
just require keyword DEFAULT where we want default value :)

 value AS name, on the other hand, accomplishes the same in a more
 SQL-looking fashion with no new reserved word (since AS is already
 fully reserved).

would it be more natural / SQL-like to use value AS name or name AS
value ?

-
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] Automatic Client Failover

2008-08-15 Thread Bruce Momjian
Simon Riggs wrote:
   Implementation would be to make PQreset() try secondary connection if
   the primary one fails to reset. Of course you can program this manually,
   but the feature is that you wouldn't need to, nor would you need to
   request changes to 27 different interfaces either.
  
  I assumed share/pg_service.conf would help in this regard;  place the
  file on a central server and modify that so everyone connects to another
  server. Perhaps we could even add round-robin functionality to that.
 
 I do want to keep it as simple as possible, but we do need a way that
 will work without reconfiguration at the time of danger. It needs to be
 preconfigured and tested, then change controlled so we all know it
 works.

OK, so using share/pg_service.conf as an implementation example, how
would this work?  The application supplies multiple service names and
libpq tries attaching to each one in the list until one works?

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] proposal sql: labeled function params

2008-08-15 Thread Hannu Krosing
On Fri, 2008-08-15 at 14:54 +0200, Pavel Stehule wrote:
 2008/8/15 Peter Eisentraut [EMAIL PROTECTED]:
  Am Thursday, 14. August 2008 schrieb Pavel Stehule:
  I propose enhance current syntax that allows to specify label for any
  function parameter:
 
  fcename(expr [as label], ...)
  fcename(colname, ...)
 
  I would to allow  same behave of custom functions like xmlforest function:
  postgres=# select xmlforest(a) from foo;
   xmlforest
  ---
   a10/a
  (1 row)
 
  Do you have a use case for this outside of XML?
 
 
 JSON and similar (custom) protocols

why not use a format string, or any other separate (sub)language ?

select json('[name:$1, age: $2]', name, age) from students;


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] Mini improvement: statement_cost_limit

2008-08-15 Thread Tom Lane
Bruce Momjian [EMAIL PROTECTED] writes:
 Ron Mayer wrote:
 Seems less fool-proof to me.

 My point is that people should _know_ they are using a cartesian
 product, and a warning would do that for users who have no need for a
 cartesian product and want to be warned about a possible error.

There are quite a lot of standard applications where small cartesian
products make sense --- star schemas are the traditional example.
I recall some discussions awhile back about how to persuade the planner
to consider such cases, in fact.  Right now it'll generally use a series
of hash joins where maybe just one would be better.

I concur with Ron that the only merit of this proposal is that it's
(relatively) simple to implement.

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] Mini improvement: statement_cost_limit

2008-08-15 Thread Robert Haas
 My point is that people should _know_ they are using a cartesian
 product, and a warning would do that for users who have no need for a
 cartesian product and want to be warned about a possible error.

I think Cartesian products are a red herring.  Cartesian products are
primarily bad if they generate bad performance, and bad performance
can be generated without Cartesian products.  I've certainly written
them intentionally, from time to time.  The bigger issue is - if you
start critiquing people's query-writing, where will you stop?

SELECT * FROM foo WHERE a = NULL
WARNING: a = NULL is always false.  Did you mean id IS NULL?

SELECT * FROM foo LEFT JOIN bar ON foo.a = bar.a LEFT JOIN baz ON
foo.b = bar.b AND foo.c = baz.c
WARNING: Maybe you meant foo.b = baz.b instead of foo.b = bar.b?

I'm sure there are a hundred others - these just happen to be a few of
my old mistakes (the first one was generated by some buggy Perl
code... the second by poor cut-and-paste skills).  In any event, I
don't think it's the job of the database to argue with you about
whether you really want the data you asked for - it's job is just to
get you that data.  Of course if doing so will take longer than the
amount of time remaining before the heat death of the universe, a
warning might be appropriate.

...Robert

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


[HACKERS] Misusing functions taking internal via coincidental signature match

2008-08-15 Thread Tom Lane
We have got a whole bunch of functions in the system that accept
arguments of type internal, where the actual meaning of internal
varies wildly (it's generally some non-SQL-visible data structure).
While (I believe that) SQL users cannot call any such functions
directly, they could still cause all sorts of trouble if they could
persuade the system to call one with some other kind of internal
argument than the function is expecting.

Neither I nor the rest of the pgsql-security list could find any places
where there is a hole of this type today.  However there are several
commands accessible to non-superusers that skirt the edge of danger:

CREATE OPERATOR will accept as restriction selectivity function
any function having arguments (internal, oid, internal, int4),
and it'll accept as join selectivity function
any function having arguments (internal, oid, internal, int2).
It doesn't bother to check the return type, nor even whether
you have permission to call the function :-(

CREATE CONVERSION will accept as conversion function any function
having arguments (int4, int4, cstring, internal, int4).  It doesn't
check the return type, either, though at least it checks for EXECUTE
permission.  (Not that that helps much given that the default situation
is public execute permission.)

CREATE TYPE accepts receive functions having arguments either (internal)
or (internal,oid,int4).  Fortunately it insists that the result type be
the type being created, so it doesn't seem that any confusion is
possible.  However, it will also accept as custom analyze function
anything taking (internal) and returning bool.  We would have a problem
with boolrecv there, except that CREATE TYPE also demands ownership
of the function, and a non-superuser won't own boolrecv ... or,
probably, anything else accepting internal.

Now, so far as I can find there are not any functions in core or contrib
that accidentally match the signature requirements for selectivity
estimators or conversion functions.  It's a bit nervous-making though,
particularly since the signatures for GIST and GIN support functions
aren't totally nailed down (nor, indeed, verified by the system anyway).
But the big point here is not so much whether we have a problem today
as what might happen in future.  All of these system support functions
have call signatures that are subject to change.  (In fact, I'm about
to commit changes to allow an additional internal parameter for join
selectivity estimators.)  So it's not hard at all to think that as
we independently change different parts of the system, we might get
an accidental match of the expected signatures for functions intended to
do very different things.  And then we have a security issue if there
are non-privileged commands to tell the system to call one of these
functions in a particular way.  So it seems like we ought to take some
steps to compartmentalize things a bit better.

The cleanest solution I can think of is to invent some more pseudotypes
that act just like INTERNAL, and then to require non-privileged CREATE
commands to reference functions that take one of these types instead
of bare INTERNAL.  There is a backwards compatibility problem here,
of course, but it wouldn't affect anybody who hadn't written a custom
selectivity estimator, conversion, or analyze function.  Which is
probably only the PostGIS project.

Failing that, we could just try to keep a registry of possible
signatures for internal-accepting functions, and make sure we don't
accept any patches that cause conflicts.  This would avoid creating
backwards compatibility problems, but without any automatic enforcement
it seems pretty dangerous.  (Perhaps a new regression test in the spirit
of opr_sanity could help, though.)

Another thing that seems like a real good idea is to tighten up
the above-mentioned commands to check for a specific return type and
demand execute permissions.  Including the return type in the required
signature is in itself a big improvement in reducing the risk of
accidental matches.  This part we could do without creating any
compatibility issues.  I also thought about demanding ownership
rather than just execute permission on the functions.  That doesn't
seem like it'd fly for selectivity estimators, since it's customary
for user datatypes to re-use the built-in ones, but it might be a
real good idea for conversions.

Comments?

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] Misusing functions taking internal via coincidental signature match

2008-08-15 Thread David E. Wheeler

On Aug 15, 2008, at 15:12, Tom Lane wrote:

The cleanest solution I can think of is to invent some more  
pseudotypes

that act just like INTERNAL, and then to require non-privileged CREATE
commands to reference functions that take one of these types instead
of bare INTERNAL.  There is a backwards compatibility problem here,
of course, but it wouldn't affect anybody who hadn't written a custom
selectivity estimator, conversion, or analyze function.  Which is
probably only the PostGIS project.


+1 # cleanliness++


Failing that, we could just try to keep a registry of possible
signatures for internal-accepting functions, and make sure we don't
accept any patches that cause conflicts.  This would avoid creating
backwards compatibility problems, but without any automatic  
enforcement
it seems pretty dangerous.  (Perhaps a new regression test in the  
spirit

of opr_sanity could help, though.)


That won't help pgFoundry modules, though. And since there's been so  
much talk about having improved third-party module support, it seems  
to me that we ought to try to discourage security holes in such  
modules, too.



Another thing that seems like a real good idea is to tighten up
the above-mentioned commands to check for a specific return type and
demand execute permissions.  Including the return type in the required
signature is in itself a big improvement in reducing the risk of
accidental matches.  This part we could do without creating any
compatibility issues.  I also thought about demanding ownership
rather than just execute permission on the functions.  That doesn't
seem like it'd fly for selectivity estimators, since it's customary
for user datatypes to re-use the built-in ones, but it might be a
real good idea for conversions.


I did find this strange:

CREATE OR REPLACE FUNCTION citextrecv(internal)
RETURNS citext
AS 'textrecv'
LANGUAGE 'internal' STABLE STRICT;

CREATE OR REPLACE FUNCTION citextsend(citext)
RETURNS bytea
AS 'textsend'
LANGUAGE 'internal' STABLE STRICT;

It'd make a lot more sense to me if bytea was passed to citextrcv,  
rather than internal, even if, internally, it used internal (so to  
speak). Now you didn't mention CREATE FUNCTION as being one of the  
places where this comes up, but it seems to me that it would be  
valuable to hide the internal struct, and force SQL users to use only  
well-defined types.


Just my $0.02, FWIW.

Best,

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] Mini improvement: statement_cost_limit

2008-08-15 Thread Josh Berkus
Bruce,

 How about a simpler approach that throws an error or warning for
 cartesian products?  That seems fool-proof.

Well, throwing a warning is pretty useless for an unattended application.  

Also, it's perfectly possible to write queries which will never complete 
without a cartesian join.

Basically, *unless* someone has a plan to integrate the greenplum resource 
management stuff sometime soon, I think we should take Hans' idea (pending 
patch quality, of course).  There's an argument to be made that even if we 
took the greenplum resource controller, statement_cost_limit would be much 
simpler and worth having for the small-effort simple-application users.

FYI, the concept behind the Greenplum RM is similar, except that it 
maintains a total pool of query costs on a per-role basis.  

-- 
--Josh

Josh Berkus
PostgreSQL
San Francisco

-- 
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] psql bug -- using old variables and database connection

2008-08-15 Thread Bruce Momjian

Thanks, applied.

---

Gregory Stark wrote:
 
 We're currently printing the warning about connecting to the wrong version of
 the server *before* syncing variables. On reconnecting this results in using
 the *old* server version before reconnecting. I'm not sure what happens for
 the initial connection but it can't be good. connection_warnings also uses
 pset.db which isn't set until a few lines later too which can't be good.
 
 e.g.
 
 postgres=# \c
 psql (8.4devel)
 You are now connected to database postgres.
 postgres=# select version();
  version  

 -
  PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 
 (Debian 4.2.1-5)
 (1 row)
 
 postgres=# \c
 psql (8.4devel, server 8.2.5)
 WARNING: psql version 8.4, server version 8.2.
  Some psql features might not work.
 You are now connected to database postgres.
 
 
 
 
 --- command.c 30 Jul 2008 21:57:07 +0100  1.192
 +++ command.c 12 Aug 2008 10:05:52 +0100  
 @@ -1197,10 +1197,10 @@
* Replace the old connection with the new one, and update
* connection-dependent variables.
*/
 - connection_warnings();
   PQsetNoticeProcessor(n_conn, NoticeProcessor, NULL);
   pset.db = n_conn;
   SyncVariables();
 + connection_warnings(); /* Must be after SyncVariables */
  
   /* Tell the user about the new connection */
   if (!pset.quiet)
 
 
 -- 
   Gregory Stark
   EnterpriseDB  http://www.enterprisedb.com
   Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
 training!
 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] Join Removal/ Vertical Partitioning

2008-08-15 Thread Bruce Momjian
Simon Riggs wrote:
 
 On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
  Simon Riggs [EMAIL PROTECTED] writes:
   We can check for removal of a rel by...
 
 OT comment: I just found a blog about Oracle's optimizermagic, which is
 quite interesting. I notice there is a blog there about join removal,
 posted about 12 hours later than my original post. Seems to validate the
 theory anyway. Our posts have a wider audience than may be apparent :-)

Yes, I think more people admire the work we do than we expect.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] compilig libpq with borland 5.5

2008-08-15 Thread Bruce Momjian
Merlin Moncure wrote:
 On Thu, Jul 31, 2008 at 1:52 PM, claudio lezcano [EMAIL PROTECTED] wrote:
  Hi everybody
 
  Iam traying to build libpq.lib and libpq.dll library using Borland c++ 5.5
  and i got these error:
 
  Error libpq.rc 1 11: Cannot open file: winver.h
 
  I opened the libpq.rc file and i saw the reference to that file #include
  winver.h
 
  I cheched out the path and the existence of the file and everything is all
  right.
 
  I actually find the file in the desired location.
 
  I don't know what to do to carry on my work.
 
  Can somebody help me out please?.
 
 Just in case you can't get it working, you can always take a msvc
 compiled libpq.dll and create a static library out of it with the
 'implib' utility using the underscore option.

I have applied the attached patch to CVS HEAD and 8.3.X to sychronize
bcc with msvc makefiles.  Please let me know if you have further
problems.  Thanks for the report.

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/interfaces/libpq/bcc32.mak
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/bcc32.mak,v
retrieving revision 1.29
diff -c -c -r1.29 bcc32.mak
*** src/interfaces/libpq/bcc32.mak	26 Feb 2008 13:31:40 -	1.29
--- src/interfaces/libpq/bcc32.mak	16 Aug 2008 01:53:58 -
***
*** 93,104 
  	[EMAIL PROTECTED] $(INTDIR)\fe-secure.obj
  	[EMAIL PROTECTED] $(INTDIR)\pqexpbuffer.obj
  	[EMAIL PROTECTED] $(INTDIR)\pqsignal.obj
! 	[EMAIL PROTECTED] $(OUTDIR)\win32.obj
  	[EMAIL PROTECTED] $(INTDIR)\wchar.obj
  	[EMAIL PROTECTED] $(INTDIR)\encnames.obj
  	[EMAIL PROTECTED] $(INTDIR)\pthread-win32.obj
  	[EMAIL PROTECTED] $(INTDIR)\snprintf.obj
  	[EMAIL PROTECTED] $(INTDIR)\strlcpy.obj
  	[EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME).lib
  	[EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME)dll.lib
  	[EMAIL PROTECTED] $(OUTDIR)\libpq.res
--- 93,109 
  	[EMAIL PROTECTED] $(INTDIR)\fe-secure.obj
  	[EMAIL PROTECTED] $(INTDIR)\pqexpbuffer.obj
  	[EMAIL PROTECTED] $(INTDIR)\pqsignal.obj
! 	[EMAIL PROTECTED] $(INTDIR)\win32.obj
  	[EMAIL PROTECTED] $(INTDIR)\wchar.obj
  	[EMAIL PROTECTED] $(INTDIR)\encnames.obj
  	[EMAIL PROTECTED] $(INTDIR)\pthread-win32.obj
  	[EMAIL PROTECTED] $(INTDIR)\snprintf.obj
  	[EMAIL PROTECTED] $(INTDIR)\strlcpy.obj
+ 	[EMAIL PROTECTED] $(INTDIR)\dirent.obj
+ 	[EMAIL PROTECTED] $(INTDIR)\dirmod.obj
+ 	[EMAIL PROTECTED] $(INTDIR)\pgsleep.obj
+ 	[EMAIL PROTECTED] $(INTDIR)\open.obj
+ 	[EMAIL PROTECTED] $(INTDIR)\win32error.obj
  	[EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME).lib
  	[EMAIL PROTECTED] $(OUTDIR)\$(OUTFILENAME)dll.lib
  	[EMAIL PROTECTED] $(OUTDIR)\libpq.res
***
*** 134,139 
--- 139,149 
  	$(INTDIR)\encnames.obj \
  	$(INTDIR)\snprintf.obj \
  	$(INTDIR)\strlcpy.obj \
+ 	$(INTDIR)\dirent.obj \
+ 	$(INTDIR)\dirmod.obj \
+ 	$(INTDIR)\pgsleep.obj \
+ 	$(INTDIR)\open.obj \
+ 	$(INTDIR)\win32error.obj \
  	$(INTDIR)\pthread-win32.obj
  
  
***
*** 238,244 
  
  $(INTDIR)\strlcpy.obj : ..\..\port\strlcpy.c
  	$(CPP) @
! 	$(CPP_PROJ) ..\..\port\strlcpy.c
  
  
  
--- 248,279 
  
  $(INTDIR)\strlcpy.obj : ..\..\port\strlcpy.c
  	$(CPP) @
! 	$(CPP_PROJ) /I. ..\..\port\strlcpy.c
! 
! 
! $(INTDIR)\dirent.obj : ..\..\port\dirent.c
! 	$(CPP) @
! 	$(CPP_PROJ) /I. ..\..\port\dirent.c
! 
! 
! $(INTDIR)\dirmod.obj : ..\..\port\dirmod.c
! 	$(CPP) @
! 	$(CPP_PROJ) /I. ..\..\port\dirmod.c
! 
! 
! $(INTDIR)\pgsleep.obj : ..\..\port\pgsleep.c
! 	$(CPP) @
! 	$(CPP_PROJ) /I. ..\..\port\pgsleep.c
! 
! 
! $(INTDIR)\open.obj : ..\..\port\open.c
! 	$(CPP) @
! 	$(CPP_PROJ) /I. ..\..\port\open.c
! 
! 
! $(INTDIR)\win32error.obj : ..\..\port\win32error.c
! 	$(CPP) @
! 	$(CPP_PROJ) /I. ..\..\port\win32error.c
  
  
  

-- 
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] Replay attack of query cancel

2008-08-15 Thread Bruce Momjian

Added to TODO:

* Prevent query cancel packets from being replayed by an attacker,
  especially when using SSL

  http://archives.postgresql.org/pgsql-hackers/2008-08/msg00345.php



---

Heikki Linnakangas wrote:
 It occurred to me a while ago that our query cancel messages are sent 
 unencrypted, even when SSL is otherwise used. That's not a big issue on 
 its own, because the cancellation message only contains the backend PID 
 and the cancellation key, but it does open us to a replay attack. After 
 the first query in a connection has been cancelled, an eavesdropper can 
 reuse the backend PID and cancellation key to cancel subsequent queries 
 on the same connection.
 
 We discussed this on the security list, and the consensus was that this 
 isn't worth a quick fix and a security release, because
 - it only affects applications that use query cancel, which is rare
 - it only affects SSL encrypted connections (the point is moot 
 non-encrypted connections, as you can just snatch the cancel key from 
 the initial message)
 - it only let's you cancel queries, IOW it's only a DOS attack.
 - there's no simple fix.
 
 However, it is something to keep in mind, and perhaps fix for the next 
 release.
 
 One idea for fixing this is to make cancellation keys disposable, and 
 automatically issue a new one through the main connection when one is 
 used, but that's not completely trivial, and requires a change in both 
 the clients and the server. Another idea is to send the query cancel 
 message only after SSL authentication, but that is impractical for libpq 
 because we PQcancel needs to be callable from a signal handler.
 
 -- 
Heikki Linnakangas
EnterpriseDB   http://www.enterprisedb.com
 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] Explain XML patch submitted

2008-08-15 Thread Tom Raney

Bruce Momjian wrote:

Where are we on this patch?
  


I think I submitted the patch before its time.  The project opened a big 
can of worms with the XML output.  I'd like to rework it with some of 
the comments I received.


The work there was some prep-work for the planner visualizer I've been 
working on.  I haven't had time to revisit this initial work.


-Tom Raney


---

[EMAIL PROTECTED] wrote:
  

I just posted a patch addressing the TODO item:

Allow EXPLAIN output to be more easily processed by scripts, perhaps XML

This is a modified patch originally submitted by Germ?n Po? Caama?o  
last year.  I added the DTD and some other tweaks.


I did *not* delve much into the ecpg code, other than mildly modifying  
prepoc.y by adding the XML and DTD defines.  I'm sure more work is  
required there.


And, I did not include Init Plan and Sub Plan in the XML output,  
which did not fit into the XML in a graceful way.  But, that can also  
be revisited.


Regards,

Tom Raney

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



  



--
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] Explain XML patch submitted

2008-08-15 Thread Bruce Momjian

Where are we on this patch?

---

[EMAIL PROTECTED] wrote:
 I just posted a patch addressing the TODO item:
 
 Allow EXPLAIN output to be more easily processed by scripts, perhaps XML
 
 This is a modified patch originally submitted by Germ?n Po? Caama?o  
 last year.  I added the DTD and some other tweaks.
 
 I did *not* delve much into the ecpg code, other than mildly modifying  
 prepoc.y by adding the XML and DTD defines.  I'm sure more work is  
 required there.
 
 And, I did not include Init Plan and Sub Plan in the XML output,  
 which did not fit into the XML in a graceful way.  But, that can also  
 be revisited.
 
 Regards,
 
 Tom Raney
 
 -- 
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] stat() vs cygwin

2008-08-15 Thread Bruce Momjian

Where are we on this patch?

---

Reini Urban wrote:
 Dave Page schrieb:
  On Tue, Jun 24, 2008 at 9:32 AM, Magnus Hagander [EMAIL PROTECTED] wrote:
  Yes.
 
  As in the cygwin build does build. Nobody really has verified if the fix
  is needed there. But frankly, if you are likely to care about the
  effects of this issue, you won't be running cygwin anyway. It's mostly a
  dead platform for postgresql anyway, AFAICS we only keep it building for
  legacy compatibility. Once it starts taking lots of resources to keep
  building (which it doesn't now), I think we should just drop it instead...
 
 Dead is interesting. We see a lot of cygwin users having postgresql 
 installed.
 
  FWIW, the most recent packages from Cygwin themselves are 8.2.5.
 
 Update: 8.2.9 is latest.
 8.3.x not because the new SSPI doesn't work yet.
 
 currently failing is: --with-gssapi --with-krb5 --with-tcl --with-java 
 --with-ossp-uuid --with-ldap
 (but ldap works okay with 8.2.9)
 
 currently testing is: --enable-nls --with-CXX --with-openssl --with-perl 
 --with-python --with-libxml --with-libxslt
 
 current cygwin patch in testing is attached.
 -- 
 Reini Urban
 postgresql cygwin maintainer
 


-- 
  Bruce Momjian  [EMAIL PROTECTED]http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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