Re: [HACKERS] parallel restore

[Tom Lane]

Okay, another question --- there are two places in pg_backup_custom.c
where the patch #ifdef's out hasSeek tests on WIN32.  Why is that?
If checkSeek() is wrong on Windows, wouldn't it be better to fix it?

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] How to get SE-PostgreSQL acceptable

[KaiGai Kohei]

Bruce Momjian wrote:

KaiGai Kohei wrote:

Today, I'll debug the modified code...

Wow, that was fast.  Where are you storing the security information for
tables and columns?  Did you add a special column to pg_class, etc?

Security information is stored within padding field of HeapTupleHeader
as we did. It can be fetched via sepgsql_(table|column|...)_getcon()
functions, and can be set via SECURITY_LABEL = 'xxx'.


Well, we are not using row-level security values so why not store it in
its own column regular or as part of the existing ACL structure.  I
think it will be very odd for system tables to have this special column
but not user rows.


Sorry, my description might easily make confusion.
I read it again myself, indeed, it makes confusion. :(

SECURITY_LABEL = 'xxx' means following sytle:

  CREATE TABLE t (
  a  int,
  b  text SECURITY_LABEL = '...'
  ) SECURITY_LABEL = '...';

I don't provide both of "security_label" and "security_acl"
system columns for system/user tables.
I didn't write it explicitly, it might make you confusing.

User cannot see what security label is assigned to them
due to lack of system column, so new sepgsql_xxx_getcon()
functions are provided an interface to see security label.

In this patch, I don't touch new system columns.

Thanks,
--
KaiGai Kohei 

--
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] reloptions with a "namespace"

[Euler Taveira de Oliveira]

Euler Taveira de Oliveira escreveu:

[Forgot the first patch...]

> Alvaro Herrera escreveu:
>> New patch attached, with pg_dump support (thanks to Tom for the SQL
>> heads-up).
>>
> Great! We're close. Just two minor gripes:
> 
> + char   *validnsps[] = { "toast" };
> 
> Surely, you forgot to add a NULL at the end. Patch is attached.
> 
> IIRC, my last patch includes a partial validation code for RESET cases. For
> example, the last SQL will not be atomic (invalid reloption silently ignored).
> So, why not apply the namespace validation code to RESET case too? Patch is
> attached too. It does not handle the reloptions validation because the relOpts
> initialization code is at parseRelOptions(); i leave it for a future refactor.
> 
> euler=# create table foo (a text) with (fillfactor=10);
> CREATE TABLE
> euler=# \d+ foo
>  Tabela "public.foo"
>  Coluna | Tipo | Modificadores | Storage  | Descrição
> +--+---+--+---
>  a  | text |   | extended |
> Têm OIDs: não
> Options: fillfactor=10
> 
> euler=# alter table foo reset (fillfactor,foo.fillfactor);
> ALTER TABLE
> euler=# \d+ foo
>  Tabela "public.foo"
>  Coluna | Tipo | Modificadores | Storage  | Descrição
> +--+---+--+---
>  a  | text |   | extended |
> Têm OIDs: não
> 
> 
> 
> 
> 
> 
> 


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/
diff -cr pgsql.alvaro/src/backend/commands/tablecmds.c 
pgsql.euler/src/backend/commands/tablecmds.c
*** pgsql.alvaro/src/backend/commands/tablecmds.c   2009-01-31 
02:01:22.0 -0200
--- pgsql.euler/src/backend/commands/tablecmds.c2009-01-31 
01:47:32.0 -0200
***
*** 351,357 
Datum   reloptions;
ListCell   *listptr;
AttrNumber  attnum;
!   char   *validnsps[] = { "toast" };
  
/*
 * Truncate relname to appropriate length (probably a waste of time, as
--- 351,357 
Datum   reloptions;
ListCell   *listptr;
AttrNumber  attnum;
!   static char*validnsps[] = { "toast", NULL };
  
/*
 * Truncate relname to appropriate length (probably a waste of time, as
***
*** 6459,6465 
Datum   repl_val[Natts_pg_class];
boolrepl_null[Natts_pg_class];
boolrepl_repl[Natts_pg_class];
!   char   *validnsps[] = { "toast" };
  
if (defList == NIL)
return; /* nothing to do */
--- 6459,6465 
Datum   repl_val[Natts_pg_class];
boolrepl_null[Natts_pg_class];
boolrepl_repl[Natts_pg_class];
!   static char*validnsps[] = { "toast", NULL };
  
if (defList == NIL)
return; /* nothing to do */
diff -cr pgsql.alvaro/src/backend/executor/execMain.c 
pgsql.euler/src/backend/executor/execMain.c
*** pgsql.alvaro/src/backend/executor/execMain.c2009-01-31 
02:01:22.0 -0200
--- pgsql.euler/src/backend/executor/execMain.c 2009-01-31 01:48:19.0 
-0200
***
*** 2832,2838 
Oid intoRelationId;
TupleDesc   tupdesc;
DR_intorel *myState;
!   char   *validnsps[] = { "toast" };
  
Assert(into);
  
--- 2832,2838 
Oid intoRelationId;
TupleDesc   tupdesc;
DR_intorel *myState;
!   static char*validnsps[] = { "toast", NULL };
  
Assert(into);
  
Somente em pgsql.euler/src/backend/parser: gram.c
Somente em pgsql.euler/src/backend/parser: gram.h
Somente em pgsql.euler/src/backend/parser: scan.c
diff -cr pgsql.alvaro/src/backend/tcop/utility.c 
pgsql.euler/src/backend/tcop/utility.c
*** pgsql.alvaro/src/backend/tcop/utility.c 2009-01-31 02:01:22.0 
-0200
--- pgsql.euler/src/backend/tcop/utility.c  2009-01-31 01:47:51.0 
-0200
***
*** 424,430 
if (IsA(stmt, CreateStmt))
{
Datum   toast_options;
!   char   *validnsps[] = { "toast" 
};
  
/* Create the table itself */
relOid = 
DefineRelation((CreateStmt *) stmt,
--- 424,430 
if (IsA(stmt, CreateStmt))
{
Datum   toast_options;
!   static char   *validnsps[] = { 
"toast", NULL };
  
/* Creat

Re: [HACKERS] reloptions with a "namespace"

[Euler Taveira de Oliveira]

Alvaro Herrera escreveu:
> New patch attached, with pg_dump support (thanks to Tom for the SQL
> heads-up).
> 
Great! We're close. Just two minor gripes:

+   char   *validnsps[] = { "toast" };

Surely, you forgot to add a NULL at the end. Patch is attached.

IIRC, my last patch includes a partial validation code for RESET cases. For
example, the last SQL will not be atomic (invalid reloption silently ignored).
So, why not apply the namespace validation code to RESET case too? Patch is
attached too. It does not handle the reloptions validation because the relOpts
initialization code is at parseRelOptions(); i leave it for a future refactor.

euler=# create table foo (a text) with (fillfactor=10);
CREATE TABLE
euler=# \d+ foo
 Tabela "public.foo"
 Coluna | Tipo | Modificadores | Storage  | Descrição
+--+---+--+---
 a  | text |   | extended |
Têm OIDs: não
Options: fillfactor=10

euler=# alter table foo reset (fillfactor,foo.fillfactor);
ALTER TABLE
euler=# \d+ foo
 Tabela "public.foo"
 Coluna | Tipo | Modificadores | Storage  | Descrição
+--+---+--+---
 a  | text |   | extended |
Têm OIDs: não


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--

CREATE PROCEDURAL LANGUAGE plperl;


ALTER PROCEDURAL LANGUAGE plperl OWNER TO euler;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO euler;

--
-- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--

CREATE PROCEDURAL LANGUAGE plpythonu;


ALTER PROCEDURAL LANGUAGE plpythonu OWNER TO euler;

--
-- Name: pltcl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: euler
--

CREATE PROCEDURAL LANGUAGE pltcl;


ALTER PROCEDURAL LANGUAGE pltcl OWNER TO euler;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tst1; Type: TABLE; Schema: public; Owner: euler; Tablespace: 
--

CREATE TABLE tst1 (
a text
)
WITH (fillfactor=10);


ALTER TABLE public.tst1 OWNER TO euler;

--
-- Name: tst2; Type: TABLE; Schema: public; Owner: euler; Tablespace: 
--

CREATE TABLE tst2 (
a text
)
WITH (toast.fillfactor=20);


ALTER TABLE public.tst2 OWNER TO euler;

--
-- Name: tst3; Type: TABLE; Schema: public; Owner: euler; Tablespace: 
--

CREATE TABLE tst3 (
a text
)
WITH (fillfactor=10, toast.fillfactor=20);


ALTER TABLE public.tst3 OWNER TO euler;

--
-- Data for Name: tst1; Type: TABLE DATA; Schema: public; Owner: euler
--

COPY tst1 (a) FROM stdin;
\.


--
-- Data for Name: tst2; Type: TABLE DATA; Schema: public; Owner: euler
--

COPY tst2 (a) FROM stdin;
\.


--
-- Data for Name: tst3; Type: TABLE DATA; Schema: public; Owner: euler
--

COPY tst3 (a) FROM stdin;
\.


--
-- Name: public; Type: ACL; Schema: -; Owner: euler
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM euler;
GRANT ALL ON SCHEMA public TO euler;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

*** pgsql.alvaro/src/backend/access/common/reloptions.c 2009-01-31 
02:01:21.0 -0200
--- pgsql.euler/src/backend/access/common/reloptions.c  2009-01-31 
02:16:29.0 -0200
***
*** 487,492 
--- 487,519 
{
ReloptElem*def = lfirst(cell);
  
+   /*
+* Error out if the namespace is not valid.  A NULL namespace
+* is always valid.
+*/
+   if (def->nmspc != NULL)
+   {
+   boolvalid = false;
+   int i;
+ 
+   if (validnsps)
+   {
+   for (i = 0; validnsps[i]; i++)
+   {
+   if (pg_strcasecmp(def->nmspc, 
validnsps[i]) == 0)
+   {
+   valid = true;
+   break;
+   }
+   }
+   }
+ 
+   if (!valid)
+   ereport(ERROR,
+   
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+errmsg("unrecognized parameter 
namespace \"%s\"",
+   def->nmspc)));
+   }
  
if (isReset)

Re: [HACKERS] 8.4 release planning

[Robert Treat]

On Thursday 29 January 2009 12:03:45 Robert Haas wrote:
> I
> don't believe that you can speed a project up much by adjusting the
> length of the release cycle, but it is *sometimes* possible to speed
> up a project by dividing up the work over more people.
>

This is interesting. We had a problem in 8.3 (and most of the releases before 
that) of too many patches in the queue at the end of the development cycle. 
Most everyone agreed that more reviewers/committers would help, but given no 
way to conjure them up, they realized that wasn't a solution. Instead, we 
went to a tighter development cycle, with one month of dev and then a 
commifest. This allowed us to better parralelize both reviews and commits, 
allowed a number of patches to get bumped through multiple fests with 
relatively few compliants (after all, the next fest was just a month down the 
line), keep the patch queue pretty manageable (right up untill the end, when 
we stopped the cycle), and also delivered us some really big features along 
the way.   

-- 
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

-- 
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] How to get SE-PostgreSQL acceptable

[Bruce Momjian]

KaiGai Kohei wrote:
> >> Today, I'll debug the modified code...
> > 
> > Wow, that was fast.  Where are you storing the security information for
> > tables and columns?  Did you add a special column to pg_class, etc?
> 
> Security information is stored within padding field of HeapTupleHeader
> as we did. It can be fetched via sepgsql_(table|column|...)_getcon()
> functions, and can be set via SECURITY_LABEL = 'xxx'.

Well, we are not using row-level security values so why not store it in
its own column regular or as part of the existing ACL structure.  I
think it will be very odd for system tables to have this special column
but not user rows.


-- 
  Bruce Momjian  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] How to get SE-PostgreSQL acceptable

[KaiGai Kohei]

Bruce Momjian wrote:

KaiGai Kohei wrote:
Hasn't a plan for this already been posted? See 
http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php

FYI:

* previous full-functional SE-PostgreSQL/Row-ACLs

[kai...@fedora10 security]$ wc -l *.c */*.c
729 pgaceCommon.c
   1547 pgaceHooks.c
721 rowacl/rowacl.c
   1200 sepgsql/avc.c
623 sepgsql/core.c
   1019 sepgsql/hooks.c
785 sepgsql/permissions.c
   1097 sepgsql/proxy.c
   7721 total

* A lite SE-PostgreSQL without row-level security,
   large object support, writable system column

[kai...@fedora10 sepgsql]$ wc -l *.c
904 checker.c
   1181 avc.c
360 core.c
 55 dummy.c
683 hooks.c
478 label.c
553 perms.c
   4214 total

Today, I'll debug the modified code...


Wow, that was fast.  Where are you storing the security information for
tables and columns?  Did you add a special column to pg_class, etc?


Security information is stored within padding field of HeapTupleHeader
as we did. It can be fetched via sepgsql_(table|column|...)_getcon()
functions, and can be set via SECURITY_LABEL = 'xxx'.

--
KaiGai Kohei 

--
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] How to get SE-PostgreSQL acceptable

[Bruce Momjian]

KaiGai Kohei wrote:
> > Hasn't a plan for this already been posted? See 
> > http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php
> 
> FYI:
> 
> * previous full-functional SE-PostgreSQL/Row-ACLs
> 
> [kai...@fedora10 security]$ wc -l *.c */*.c
> 729 pgaceCommon.c
>1547 pgaceHooks.c
> 721 rowacl/rowacl.c
>1200 sepgsql/avc.c
> 623 sepgsql/core.c
>1019 sepgsql/hooks.c
> 785 sepgsql/permissions.c
>1097 sepgsql/proxy.c
>7721 total
> 
> * A lite SE-PostgreSQL without row-level security,
>large object support, writable system column
> 
> [kai...@fedora10 sepgsql]$ wc -l *.c
> 904 checker.c
>1181 avc.c
> 360 core.c
>  55 dummy.c
> 683 hooks.c
> 478 label.c
> 553 perms.c
>4214 total
> 
> Today, I'll debug the modified code...

Wow, that was fast.  Where are you storing the security information for
tables and columns?  Did you add a special column to pg_class, etc?

-- 
  Bruce Momjian  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] How to get SE-PostgreSQL acceptable

[KaiGai Kohei]

Andrew Dunstan wrote:



Josh Berkus wrote:

Joshua, Kohei-san,

So, for 8.4:  *if* we included in 8.4 a version of SEPostgres with all 
features *except* row-level security, would it still be useful to the 
SELinux community?


I think we're just not going to work out the headache-inducing issues 
around row-level security in time for 8.4, and it seems to me that 
integrated system-level security labels at the table-and-column level 
are still very useful, even without row-level security.


Hasn't a plan for this already been posted? See 
http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php


FYI:

* previous full-functional SE-PostgreSQL/Row-ACLs

[kai...@fedora10 security]$ wc -l *.c */*.c
   729 pgaceCommon.c
  1547 pgaceHooks.c
   721 rowacl/rowacl.c
  1200 sepgsql/avc.c
   623 sepgsql/core.c
  1019 sepgsql/hooks.c
   785 sepgsql/permissions.c
  1097 sepgsql/proxy.c
  7721 total

* A lite SE-PostgreSQL without row-level security,
  large object support, writable system column

[kai...@fedora10 sepgsql]$ wc -l *.c
   904 checker.c
  1181 avc.c
   360 core.c
55 dummy.c
   683 hooks.c
   478 label.c
   553 perms.c
  4214 total

Today, I'll debug the modified code...
--
KaiGai Kohei 

--
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] How to get SE-PostgreSQL acceptable

[KaiGai Kohei]

Josh Berkus wrote:

Joshua, Kohei-san,

So, for 8.4:  *if* we included in 8.4 a version of SEPostgres with all 
features *except* row-level security, would it still be useful to the 
SELinux community?


Yes, obviously.

I think the granularity of access controls is an aspect of security.

I think we're just not going to work out the headache-inducing issues 
around row-level security in time for 8.4, and it seems to me that 
integrated system-level security labels at the table-and-column level 
are still very useful, even without row-level security.


For example, table-and-column level access control can provide such a
worth which enables to store customer's credit-card-number within
unaccessable column from all the web application (children of Apache)
but accessable from settlement system (child of crond).
It enables to prevent SQL injection to steal very sensitive info.

Thanks,
--
KaiGai Kohei 

--
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] mingw check hung

[Andrew Dunstan]

Hiroshi Inoue wrote:


Eventually does the crash come from the call SetEnvironemntVariable
(.., NULL) on mingw-XP(or older?)?
I'm also interested in this issue and want to know the cause.




The debugger shows that we actually fail on a popen() call in intdb. 
However, if we replace the calls to SetEnvironmentVariable("foo",NULL) 
with calls to SetEnvironmentVariable("foo","") then there is no failure. 
My theory is that on XP somehow the former is corrupting the environment 
such that when popen() tries to copy the environment for the new child 
process, it barfs.


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] mingw check hung

[Hiroshi Inoue]

Andrew Dunstan wrote:



Andrew Dunstan wrote:



Magnus Hagander wrote:

Andrew Dunstan wrote:
 

Magnus Hagander wrote:
  

Are we *sure*, btw, that this is actually a mingw issue, and not
something else in the environment? Could you try a MSVC compiled 
binary

on the same machine?


My MSVC buildfarm animal runs on the same machine, and does not suffer
the same problem.


Meh. Stupid mingw :-)

So how about we #ifdef out that NULL setting based on
WIN32_ONLY_COMPILER, does that seem reasonable?


The odd thing is that it doesn't seem to affect Vista, only XP.

Anyway, yes, I think that would be OK. How do we then test to see if 
the original problem is still fixed?


Further proof that this is a Windows version issue: I took the problem 
build from my XP and put it on my Vista box: the same build that causes 
a problem on XP runs perfectly on Vista. Go figure. Maybe we need a 
version check at runtime? That would be icky.


Eventually does the crash come from the call SetEnvironemntVariable
(.., NULL) on mingw-XP(or older?)?
I'm also interested in this issue and want to know the cause.

However is it necessary to call SetEnvironmentVariable() in the first
place? My original patch doesn't contain SetEnvironmentVariable call
in pg_unsetenv() because _putenv() seems to call SetEnvironmentVariable
internally.

regards,
Hiroshi Inoue


--
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] How to get SE-PostgreSQL acceptable

[Stephen Frost]

* Andrew Dunstan (and...@dunslane.net) wrote:
> Josh Berkus wrote:
>> So, for 8.4:  *if* we included in 8.4 a version of SEPostgres with all  
>> features *except* row-level security, would it still be useful to the  
>> SELinux community?
>>
>> I think we're just not going to work out the headache-inducing issues  
>> around row-level security in time for 8.4, and it seems to me that  
>> integrated system-level security labels at the table-and-column level  
>> are still very useful, even without row-level security.

I tend to agree that they will be very useful.  I'm not sure there will
be much adoption without row-level in the security community though, to
be honest.  I'd like to see it as part of an overall plan to eventually
do row-level support.  Given the size of this overall work and feature
set, I think it's appropriate to do it in a staged manner regardless.

> Hasn't a plan for this already been posted? See  
> http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php

Sure, that's a plan, but Josh's question is certainly appropriate.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] How to get SE-PostgreSQL acceptable

[Andrew Dunstan]

Josh Berkus wrote:

Joshua, Kohei-san,

So, for 8.4:  *if* we included in 8.4 a version of SEPostgres with all 
features *except* row-level security, would it still be useful to the 
SELinux community?


I think we're just not going to work out the headache-inducing issues 
around row-level security in time for 8.4, and it seems to me that 
integrated system-level security labels at the table-and-column level 
are still very useful, even without row-level security.






Hasn't a plan for this already been posted? See 
http://archives.postgresql.org/pgsql-hackers/2009-01/msg02407.php


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] How to get SE-PostgreSQL acceptable

[Robert Haas]

On Fri, Jan 30, 2009 at 5:37 PM, Josh Berkus  wrote:
> Bruce,
>> Are you trying to make some kind of point?
>>
>
> Yeah, that we're certainly not doing any of this for 8.4.
>
> If we're going for radical new approaches for row-level, why not also look
> at the VIEWS approach?  If we worked out the same problems we need to fix
> for Bernd's patch, using automated manatory views to enforce row-level
> access is also plausible.

I'm rather enchanted with the idea of using table partitioning to
implement row-level security, but the obstacles seem rather
formidable.  Right now, a partitioned relation behaves nothing like a
regular relation, and to use it for this purpose you'd need to make it
transparent.  IOW, you'd need to be able to define indices that
spanned multiple partitions (including enforcement of unique
constraints), you'd need to be able to make foreign keys that could
point to a row in arbitrary subset of the partitions, you'd need
automatic creation and deletion of partitions, you'd need better
planner support for partitions, and you'd need to somehow deal with
the issue of pg_class bloat.  Plus, to make it truly transparent,
you'd need multiple layers of partitioning, in case someone wanted to
do row-level security and range partitioning simultaneously.

Now, the plus side is that if we could do all of that, we'd have the
infrastructure to support some truly awesome partitioning stuff, and
not just row-level security.  But it seems awfully hard.

...Robert

-- 
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] How to get SE-PostgreSQL acceptable

[Josh Berkus]

Joshua, Kohei-san,

So, for 8.4:  *if* we included in 8.4 a version of SEPostgres with all 
features *except* row-level security, would it still be useful to the 
SELinux community?


I think we're just not going to work out the headache-inducing issues 
around row-level security in time for 8.4, and it seems to me that 
integrated system-level security labels at the table-and-column level 
are still very useful, even without row-level security.


--Josh


--
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] How to get SE-PostgreSQL acceptable

[Bruce Momjian]

Josh Berkus wrote:
> Bruce,
> 
> > Are you trying to make some kind of point?
> > 
> 
> Yeah, that we're certainly not doing any of this for 8.4.
> 
> If we're going for radical new approaches for row-level, why not also 
> look at the VIEWS approach?  If we worked out the same problems we need 
> to fix for Bernd's patch, using automated manatory views to enforce 
> row-level access is also plausible.

Sure, we can explore that too.

-- 
  Bruce Momjian  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] How to get SE-PostgreSQL acceptable

[Josh Berkus]

Bruce,


Are you trying to make some kind of point?



Yeah, that we're certainly not doing any of this for 8.4.

If we're going for radical new approaches for row-level, why not also 
look at the VIEWS approach?  If we worked out the same problems we need 
to fix for Bernd's patch, using automated manatory views to enforce 
row-level access is also plausible.


--Josh


--
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] How to get SE-PostgreSQL acceptable

[Ron Mayer]

Bruce Momjian wrote:
> Josh Berkus wrote:
>> Bruce Momjian wrote:
>>> Josh Berkus wrote:
> Yea, it would take some work but it is an idea.
 It's *an* idea,yes.  But it introduces as many (or more) problems than 
 it solves.
>>> Ah, but my problems might be easier solved than the row-level permission
>>> problems.  ;-)
>>>
>> Or might not.  Multi-partition indexes?  Multi-partition uniqueness? 
>> Automated moving of rows between partitions?
> 
> Are you trying to make some kind of point?
> 

IMVHO Josh was describing a nice-to-have TODO list for a partitions feature
in general. :-)  Maybe he was saying that when they partitioning feature
is designed that they try to think of polyinstantiation as they design it :-)

-- 
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] How to get SE-PostgreSQL acceptable

[Bruce Momjian]

Josh Berkus wrote:
> Bruce Momjian wrote:
> > Josh Berkus wrote:
> >>> Yea, it would take some work but it is an idea.
> >> It's *an* idea,yes.  But it introduces as many (or more) problems than 
> >> it solves.
> > 
> > Ah, but my problems might be easier solved than the row-level permission
> > problems.  ;-)
> > 
> 
> Or might not.  Multi-partition indexes?  Multi-partition uniqueness? 
> Automated moving of rows between partitions?

Are you trying to make some kind of point?

-- 
  Bruce Momjian  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] How to get SE-PostgreSQL acceptable

[Josh Berkus]

Bruce Momjian wrote:

Josh Berkus wrote:

Yea, it would take some work but it is an idea.
It's *an* idea,yes.  But it introduces as many (or more) problems than 
it solves.


Ah, but my problems might be easier solved than the row-level permission
problems.  ;-)



Or might not.  Multi-partition indexes?  Multi-partition uniqueness? 
Automated moving of rows between partitions?


--Josh

--
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] PQinitSSL broken in some use casesf

[Bruce Momjian]

Andrew Chernow wrote:
> I am using a library that links with and initializes libcrypto (ie. 
> CRYPTO_set_locking_callback) but not SSL.  This causes problems even 
> when using PQinitSSL(FALSE) because things like SSL_library_init(); are 
> not called (unless I manually call them, copy and paste code from 
> fe-secure.c which may change).  If libpq does init ssl, it overwrites 
> (and breaks) the other library's crypto.
> 
> Shouldn't crypto and ssl init be treated as two different things?  If 
> not, how does one determine a version portable way of initializing SSL 
> in a manner required by libpq?  Lots of apps using encryption but don't 
> necessarily use ssl, so they need to know how to init ssl for libpq.

I didn't realize they were could be initialized separately, so we really
don't have an answer for you.  This is the first time I have heard of
this requirement.

-- 
  Bruce Momjian  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] using composite types in insert/update

[Merlin Moncure]

On 1/30/09, Merlin Moncure  wrote:
>  likewise, with aliases
>  select foo f from foo; -- this is how it works now
>  update foo f set f=somefoo; -- again, this is how it should work

thinko:
select f from foo f; -- this is how it really works now

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] using composite types in insert/update

[Merlin Moncure]

On 1/30/09, Sam Mason  wrote:
> But why not just leave INSERT as it is, it works and is unambiguous!

Because *there is no way to insert a composite type!!!* (you can
expand the type via INSERT ... SELECT, but not for UPDATE).

SELECT foo FROM foo; pulls the foo composite from the table, not the
fields.  I still can't understand why you want to not be able to do
this via insert.  You are looking for more flexible way to imput
fields, I am looking for a way to input type directly.

> But why is this better than using a *?

because we are not updating specific fields...'*' denotes 'all
columns'.  we are setting the type to something else.  I want to
update the type directly, not it's fields, because I don't want to
construct the update statement.

(*) is better than *, because at least we are suggesting a composite.
However, let's try and keep the syntax a little regular?

select foo from foo; -- this is how it works now
update foo set foo=somefoo; --why would you want update to work any
way but this way?

likewise, with aliases
select foo f from foo; -- this is how it works now
update foo f set f=somefoo; -- again, this is how it should work

my only point was that there is no aliases in inserts, so there is a
minute probability of case where you can't insert the composite type
directly.

your idea (i think):
update foo f set (*) = somefoo; is a huge departure in syntax and
semantics from the way things work in other places.

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] How to get SE-PostgreSQL acceptable

[Bruce Momjian]

Josh Berkus wrote:
> 
> > Yea, it would take some work but it is an idea.
> 
> It's *an* idea,yes.  But it introduces as many (or more) problems than 
> it solves.

Ah, but my problems might be easier solved than the row-level permission
problems.  ;-)

-- 
  Bruce Momjian  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] How to get SE-PostgreSQL acceptable

[Josh Berkus]

Yea, it would take some work but it is an idea.


It's *an* idea,yes.  But it introduces as many (or more) problems than 
it solves.


--Josh



--
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] How to get SE-PostgreSQL acceptable

[Bruce Momjian]

Joshua Brindle wrote:
> Bruce Momjian wrote:
> > Tom Lane wrote:
> >> Gregory Stark  writes:
> >>> I don't think partitioning is really the same thing as row-level
> >>> security.
> >> Of course not, but it seems to me that it can be used to accomplish most
> >> of the same practical use-cases.  The main gripe about doing it via
> >> partitioning is that the user's nose gets rubbed in the fact that there
> >> can't be an enormous number of different security classifications in the
> >> same table (since he has to explicitly make a partition for each one).
> >> But the proposed implementation of row-level security would poop out
> >> pretty darn quick for such a case, too, and frankly I'm not seeing an
> >> application that would demand it.
> > 
> > OK, putting on my crazy idea hat, if we split the primary and foreign
> > keys by partition, it would give us polyinstantiation:
> > 
> > http://en.wikipedia.org/wiki/Polyinstantiation
> > 
> > because our unique indexes do not apply across partitions. 
> > Polyinstantiation is a desirable security feature and one that would be
> > tough to implement without partitions.
> > 
> 
> Polyinstantiation in this manner won't do it I don't think (if I'm 
> understanding 
> you correctly). As KaiGai already said, SELinux policy is flexible so we'll 
> have 
> more than just BLP policy to worry about.
> 
> Also a top secret user will need to see all rows when he selects, and they 
> should still have unique keys. He won't be able to write to secret or unclass 
> rows but he'll be able to see them.

Yea, it would take some work but it is an idea.

-- 
  Bruce Momjian  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] using composite types in insert/update

[Sam Mason]

On Fri, Jan 30, 2009 at 03:45:54PM -0500, Merlin Moncure wrote:
> On 1/30/09, Sam Mason  wrote:
> > I was talking about your example code:
> >
> >   INSERT INTO foo VALUES '(something)'::foo;
> >
> >  This isn't currently valid, but it sounds as though it needs to be.
> 
> hm. i don't think so...at least not quite (my thinko in orig example).
> I think per spec that would attempt to insert the constructed record
> into the first column.

bah, it would wouldn't it! why the hell was it designed like that, it's
just inviting bugs!  I'll use the following table definition below:

  create temp table foo ( a int, b text );

I was expecting:

  insert into foo values (1);

to fail in the same way as:

  insert into foo (a,b) values (1);

I've never realized before that if you leave off the column list it
guesses what you want to do.

> instead, we would want:
> 
> INSERT INTO foo(foo) VALUES ...

I'm still not sure why anyone would want to do this!

> or
> INSERT INTO foo(foo) SELECT ...

But why not just leave INSERT as it is, it works and is unambiguous!

> Assuming we didn't have a foo column, that would tell pg we are
> pushing in composite type:

I still don't see why you want to encourage people to think "have I
got a similarly named column" the whole time.  It's fine when you're
after some specific column because then you know what it's called and
you're asking for it, so when it's the same as the table it's obvious.
When it's the table you're asking for you don't want to worry about it
breaking when a column gets added.

> 'UPDATE' works simillar: SET foo =
> is the key that we are pushing composite type, not specific fields.

But why is this better than using a *?

> > I agree that the mechanism is good, it's just that the syntax you
> >  proposed comes with it's own built in footgun.  Symmetry is also muddied
> >  by the fact that SELECT and INSERT/UPDATE are built on fundamentally
> >  different premises.  It's only ever possible to modify a set of rows
> >  from one table at a time, whereas a SELECT is designed to work with
> >  multiple tables.
> 
> double-check that statement vs. example above.

I'm not sure what I'm supposed to be checking, more verbosity please!

> I just don't see the
> problem.  Only small gripe I can think of is that since you can't
> alias the table in the insert statement, if you have a column named
> 'foo', you're stuck...oh well.

I'm not sure what you are saying; is this a nail in the coffin for using
the table name as "the key" or have you just deliberately introduced the
(fuzzy, un-checked) rule that you're not allowed to have a column the
same name as the table.

-- 
  Sam  http://samason.me.uk/

-- 
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] array_map not SQL accessible?

[Tom Lane]

Alvaro Herrera  writes:
> It seems there's something wrong here.

Ah, it's a bug in array_unnest: if its argument is toasted, it detoasts
it into function-local memory, and then tries to keep a pointer to that
across calls.  Boo.  Will fix.

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] parallel restore

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  

Tom Lane wrote:


One thing that is bothering me is that
if the connection parameters are such as to cause prompts for passwords,
it's going to be broken beyond usability (multiple threads all trying
to read the terminal at once).  Is there anything we can do about that?
  


  
I thought I had put in changes to cache the password, so you shouldn't 
get multiple prompts.



Ah, you can tell I hadn't gotten to the bottom of the patch yet ;-).
Still, that's not a 100% solution because of the cases where we use
reconnections to change user IDs --- the required password would
(usually) vary.  It might be sufficient to forbid that case with
parallel restore, though; I think it's mostly a legacy thing anyway.

  


I didn't know such a thing even existed. What causes it to happen? I 
agree it should be forbidden.


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] using composite types in insert/update

[Sam Mason]

On Fri, Jan 30, 2009 at 03:29:29PM -0500, Andrew Chernow wrote:
> Sam Mason wrote:
> >On Fri, Jan 30, 2009 at 02:47:49PM -0500, Merlin Moncure wrote:
> >>On 1/30/09, Tom Lane  wrote:
> >>>Merlin Moncure  writes:
> >>> > You are missing the point, using the composite type allows you to
> >>> > build the insert without knowing the specific layout of the
> >>> > table...
> >>>
> >>>Surely at *some* level you have to know that.
> >>You don't (if I understand your meaning) ...you just have to make sure
> >>the destination of the insert is the same as the source.
> >
> >Sounds as though there are at least two levels that know the specific
> >layout of the tables involved then.  1) PG has to know the structure of
> >the tables, and 2) you application relies on the fact that tables of the
> 
> What merlin is trying to solve is home-grown replication.  By 
> definition, the master and slave must have the same table(s).

Yes, we know that, but the code doesn't.  I was just being pedantic and
pointing out where the assumptions of this replication rest.

> > same name have the same structure.  Sounds like a very simple ah-hoc
> > nominal type system to me.
> 
> No.  Its an ad-hoc replication system.  A change to UPDATE is needed for 
> it to work, not a type system.

It seems convenient to think about the resulting assumptions as a type
system.  It did to me anyway, but apparently this is causing much
confusion and it was a bad analogy to have drawn.

-- 
  Sam  http://samason.me.uk/

-- 
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] parallel restore

[Tom Lane]

Andrew Dunstan  writes:
> Tom Lane wrote:
>> One thing that is bothering me is that
>> if the connection parameters are such as to cause prompts for passwords,
>> it's going to be broken beyond usability (multiple threads all trying
>> to read the terminal at once).  Is there anything we can do about that?

> I thought I had put in changes to cache the password, so you shouldn't 
> get multiple prompts.

Ah, you can tell I hadn't gotten to the bottom of the patch yet ;-).
Still, that's not a 100% solution because of the cases where we use
reconnections to change user IDs --- the required password would
(usually) vary.  It might be sufficient to forbid that case with
parallel restore, though; I think it's mostly a legacy thing anyway.

>> Also, how does this interact with single_txn mode?

> Yes. I thought I had done that too, will check.

Yeah, found that too.

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] using composite types in insert/update

[Merlin Moncure]

On 1/30/09, Sam Mason  wrote:
> On Fri, Jan 30, 2009 at 03:12:27PM -0500, Merlin Moncure wrote:
>  > On 1/30/09, Sam Mason  wrote:
>  > >  quite often (i.e. a VALUES command with many singletons).  This seems
>  > >  a bit annoying and appears to be what you were suggesting you wanted
>  > >  before (although you killed the relevant bit of context, making me think
>  > >  we may be talking about different things).
>  >
>  > we are.  See the title of the thread: 'using composite types in
>  > insert/update'. that's what I'm talking about.  I especially am not
>  > talking about the 'values' statement.
>
>
> Humm, I was talking about your example code:
>
>   INSERT INTO foo VALUES '(something)'::foo;
>
>  This isn't currently valid, but it sounds as though it needs to be.

hm. i don't think so...at least not quite (my thinko in orig example).
I think per spec that would attempt to insert the constructed record
into the first column.  instead, we would want:

INSERT INTO foo(foo) VALUES ...
or
INSERT INTO foo(foo) SELECT ...

Assuming we didn't have a foo column, that would tell pg we are
pushing in composite type:

'UPDATE' works simillar: SET foo =
is the key that we are pushing composite type, not specific fields.

> I agree that the mechanism is good, it's just that the syntax you
>  proposed comes with it's own built in footgun.  Symmetry is also muddied
>  by the fact that SELECT and INSERT/UPDATE are built on fundamentally
>  different premises.  It's only ever possible to modify a set of rows
>  from one table at a time, whereas a SELECT is designed to work with
>  multiple tables.

double-check that statement vs. example above.  I just don't see the
problem.  Only small gripe I can think of is that since you can't
alias the table in the insert statement, if you have a column named
'foo', you're stuck...oh well.  I don't think
INSERT INTO foo f(f) VALUES ...
or
INSERT INTO foo(f) f VALUES ...
are worth exploring.

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] using composite types in insert/update

[Sam Mason]

On Fri, Jan 30, 2009 at 03:12:27PM -0500, Merlin Moncure wrote:
> On 1/30/09, Sam Mason  wrote:
> >  quite often (i.e. a VALUES command with many singletons).  This seems
> >  a bit annoying and appears to be what you were suggesting you wanted
> >  before (although you killed the relevant bit of context, making me think
> >  we may be talking about different things).
> 
> we are.  See the title of the thread: 'using composite types in
> insert/update'. that's what I'm talking about.  I especially am not
> talking about the 'values' statement.

Humm, I was talking about your example code:

  INSERT INTO foo VALUES '(something)'::foo;

This isn't currently valid, but it sounds as though it needs to be.

> > For several reasons; mainly because SQL is an abortion of a language,
> >  it's got no regularity and attempts to justify requirements because of
> >  "symmetry" will end up causing more headaches.
> >
> >  Another way of saying what you seem to be saying above is: I want things
> >  to work correctly, unless I happen to have a column name that happens to
> >  be the same as the table at which point I want everything to break.
> 
> Upthread, I noted the usefulness in writing triggers.  There are many
> other uses.  btw, symmetry (making insert work more similarly to
> select) is tangential but surely a good thing.

> I don't know if you are arguing for or against the idea of 'update foo
> set foo = foo' working. (if against, why?)

I agree that the mechanism is good, it's just that the syntax you
proposed comes with it's own built in footgun.  Symmetry is also muddied
by the fact that SELECT and INSERT/UPDATE are built on fundamentally
different premises.  It's only ever possible to modify a set of rows
from one table at a time, whereas a SELECT is designed to work with
multiple tables.

-- 
  Sam  http://samason.me.uk/

-- 
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] using composite types in insert/update

[Andrew Chernow]

Sam Mason wrote:

On Fri, Jan 30, 2009 at 02:47:49PM -0500, Merlin Moncure wrote:

On 1/30/09, Tom Lane  wrote:

Merlin Moncure  writes:
 > You are missing the point, using the composite type allows you to
 > build the insert without knowing the specific layout of the
 > table...

Surely at *some* level you have to know that.

You don't (if I understand your meaning) ...you just have to make sure
the destination of the insert is the same as the source.


Sounds as though there are at least two levels that know the specific
layout of the tables involved then.  1) PG has to know the structure of
the tables, and 2) you application relies on the fact that tables of the


What merlin is trying to solve is home-grown replication.  By 
definition, the master and slave must have the same table(s).  So I 
think he is looking for a more elegant method of performing slave 
updates; rather than mirror.field_a=master.field_a, 
mirror.field_b=master.field_b, etc... until you are blue in the face.


What makes single field updating even worse is the maintained overhead 
involved if the table structure changes; can't just alter the two 
tables, you also have to modify the UPDATE statement.


> same name have the same structure.  Sounds like a very simple ah-hoc
> nominal type system to me.

No.  Its an ad-hoc replication system.  A change to UPDATE is needed for 
it to work, not a type system.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

--
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] parallel restore

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  

Latest patch is attached.



Starting to look at this now.  


Excellent!


One thing that is bothering me is that
if the connection parameters are such as to cause prompts for passwords,
it's going to be broken beyond usability (multiple threads all trying
to read the terminal at once).  Is there anything we can do about that?
If not, we've at least got to warn people to avoid it in the manual.
  


I thought I had put in changes to cache the password, so you shouldn't 
get multiple prompts. That's one reason that we make sure we connect in 
the main thread before we ever fork/spawn children.




Also, how does this interact with single_txn mode?  I suspect that's
just not very sane at all and we should forbid the combination.
  


Yes. I thought I had done that too, will check.


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] using composite types in insert/update

[Merlin Moncure]

On 1/30/09, Sam Mason  wrote:
>  quite often (i.e. a VALUES command with many singletons).  This seems
>  a bit annoying and appears to be what you were suggesting you wanted
>  before (although you killed the relevant bit of context, making me think
>  we may be talking about different things).

we are.  See the title of the thread: 'using composite types in
insert/update'. that's what I'm talking about.  I especially am not
talking about the 'values' statement.

> For several reasons; mainly because SQL is an abortion of a language,
>  it's got no regularity and attempts to justify requirements because of
>  "symmetry" will end up causing more headaches.
>
>  Another way of saying what you seem to be saying above is: I want things
>  to work correctly, unless I happen to have a column name that happens to
>  be the same as the table at which point I want everything to break.

Upthread, I noted the usefulness in writing triggers.  There are many
other uses.  btw, symmetry (making insert work more similarly to
select) is tangential but surely a good thing.

> Record *types* are most definitely not first class objects;
>  record/composite *values* on the other hand have been gaining support

well, I used the terms record types and composite types
interchangeably in this discussion.  Sorry for the confusion.

I don't know if you are arguing for or against the idea of 'update foo
set foo = foo' working. (if against, why?)

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] using composite types in insert/update

[Sam Mason]

On Fri, Jan 30, 2009 at 02:47:49PM -0500, Merlin Moncure wrote:
> On 1/30/09, Tom Lane  wrote:
> > Merlin Moncure  writes:
> >  > You are missing the point, using the composite type allows you to
> >  > build the insert without knowing the specific layout of the
> >  > table...
> >
> > Surely at *some* level you have to know that.
> 
> You don't (if I understand your meaning) ...you just have to make sure
> the destination of the insert is the same as the source.

Sounds as though there are at least two levels that know the specific
layout of the tables involved then.  1) PG has to know the structure of
the tables, and 2) you application relies on the fact that tables of the
same name have the same structure.  Sounds like a very simple ah-hoc
nominal type system to me.

-- 
  Sam  http://samason.me.uk/

-- 
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] reloptions with a "namespace"

[Alvaro Herrera]

New patch attached, with pg_dump support (thanks to Tom for the SQL
heads-up).

Euler Taveira de Oliveira wrote:

> I don't like the spreading validnsps' approach. Isn't there a way to
> centralize those variables in one place, i.e., reloption.h ?

Maybe one option is to create a #define with the options valid for
heaps?

> Also, remove an obsolete comment about toast tables at reloption.h.

I'm not sure about that one -- maybe one day we'll want to separate the
options for toast tables and those for plain tables (for example, surely
we don't need per-row default security in toast tables, or stuff like
that).

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)
Index: src/backend/access/common/reloptions.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.19
diff -c -p -r1.19 reloptions.c
*** src/backend/access/common/reloptions.c	26 Jan 2009 19:41:06 -	1.19
--- src/backend/access/common/reloptions.c	30 Jan 2009 19:42:38 -
***
*** 390,397 
  }
  
  /*
!  * Transform a relation options list (list of DefElem) into the text array
!  * format that is kept in pg_class.reloptions.
   *
   * This is used for three cases: CREATE TABLE/INDEX, ALTER TABLE SET, and
   * ALTER TABLE RESET.  In the ALTER cases, oldOptions is the existing
--- 390,399 
  }
  
  /*
!  * Transform a relation options list (list of ReloptElem) into the text array
!  * format that is kept in pg_class.reloptions, including only those options
!  * that are in the passed namespace.  The output values do not include the
!  * namespace.
   *
   * This is used for three cases: CREATE TABLE/INDEX, ALTER TABLE SET, and
   * ALTER TABLE RESET.  In the ALTER cases, oldOptions is the existing
***
*** 402,415 
   * in the list (it will be or has been handled by interpretOidsOption()).
   *
   * Note that this is not responsible for determining whether the options
!  * are valid.
   *
   * Both oldOptions and the result are text arrays (or NULL for "default"),
   * but we declare them as Datums to avoid including array.h in reloptions.h.
   */
  Datum
! transformRelOptions(Datum oldOptions, List *defList,
! 	bool ignoreOids, bool isReset)
  {
  	Datum		result;
  	ArrayBuildState *astate;
--- 404,420 
   * in the list (it will be or has been handled by interpretOidsOption()).
   *
   * Note that this is not responsible for determining whether the options
!  * are valid, but it does check that namespaces for all the options given are
!  * listed in validnsps.  The NULL namespace is always valid and needs not be
!  * explicitely listed.  Passing a NULL pointer means that only the NULL
!  * namespace is valid.
   *
   * Both oldOptions and the result are text arrays (or NULL for "default"),
   * but we declare them as Datums to avoid including array.h in reloptions.h.
   */
  Datum
! transformRelOptions(Datum oldOptions, List *defList, char *namspace,
! 	char *validnsps[], bool ignoreOids, bool isReset)
  {
  	Datum		result;
  	ArrayBuildState *astate;
***
*** 444,454 
  			/* Search for a match in defList */
  			foreach(cell, defList)
  			{
! DefElem*def = lfirst(cell);
! int			kw_len = strlen(def->defname);
  
  if (text_len > kw_len && text_str[kw_len] == '=' &&
! 	pg_strncasecmp(text_str, def->defname, kw_len) == 0)
  	break;
  			}
  			if (!cell)
--- 449,471 
  			/* Search for a match in defList */
  			foreach(cell, defList)
  			{
! ReloptElem *def = lfirst(cell);
! int			kw_len;
  
+ /* ignore if not in the same namespace */
+ if (namspace == NULL)
+ {
+ 	if (def->nmspc != NULL)
+ 		continue;
+ }
+ else if (def->nmspc == NULL)
+ 	continue;
+ else if (pg_strcasecmp(def->nmspc, namspace) != 0)
+ 	continue;
+ 
+ kw_len = strlen(def->optname);
  if (text_len > kw_len && text_str[kw_len] == '=' &&
! 	pg_strncasecmp(text_str, def->optname, kw_len) == 0)
  	break;
  			}
  			if (!cell)
***
*** 468,474 
  	 */
  	foreach(cell, defList)
  	{
! 		DefElem*def = lfirst(cell);
  
  		if (isReset)
  		{
--- 485,492 
  	 */
  	foreach(cell, defList)
  	{
! 		ReloptElem*def = lfirst(cell);
! 
  
  		if (isReset)
  		{
***
*** 483,504 
  			const char *value;
  			Size		len;
  
! 			if (ignoreOids && pg_strcasecmp(def->defname, "oids") == 0)
  continue;
  
  			/*
! 			 * Flatten the DefElem into a text string like "name=arg". If we
! 			 * have just "name", assume "name=true" is meant.
  			 */
  			if (def->arg != NULL)
! value = defGetString(def);
  			else
  value = "true";
!

Re: [HACKERS] ecpg grammar in CVS is annoying

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > Is it only me, or having the generated ecpg grammar is a bit obnoxious?
> > I am getting troubled because whenever I touch gram.y it gets updated
> > when I run make, and then it shows in "cvs diff".
> 
> Huh?  preproc.y was "cvs remove"d two months ago.

Doh!  I was missing --delete to rsync (again -- I fixed it on my
workstation months ago and had forgotten the laptop ...)

Thanks.

-- 
Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"This is a foot just waiting to be shot"(Andrew Dunstan)

-- 
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] using composite types in insert/update

[Merlin Moncure]

On 1/30/09, Tom Lane  wrote:
> Merlin Moncure  writes:
>  > You are missing the point, using the composite type allows you to
>  > build the insert without knowing the specific layout of the
>  > table...
>
> Surely at *some* level you have to know that.

You don't (if I understand your meaning) ...you just have to make sure
the destination of the insert is the same as the source.  With 'tables
as composite types', this is trivially easy as long as you make sure
the destination schema matches (basically, the whole point of ad-hoc
dblink based replication).

Fix up the composite types, and you can now make context free triggers
that ship records around without exposing any detail of the record
except a candidate key, which can be solved by convention
(foo->foo_id).

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] ecpg grammar in CVS is annoying

[Tom Lane]

Alvaro Herrera  writes:
> Is it only me, or having the generated ecpg grammar is a bit obnoxious?
> I am getting troubled because whenever I touch gram.y it gets updated
> when I run make, and then it shows in "cvs diff".

Huh?  preproc.y was "cvs remove"d two months ago.

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] parallel restore

[Tom Lane]

Andrew Dunstan  writes:
> Latest patch is attached.

Starting to look at this now.  One thing that is bothering me is that
if the connection parameters are such as to cause prompts for passwords,
it's going to be broken beyond usability (multiple threads all trying
to read the terminal at once).  Is there anything we can do about that?
If not, we've at least got to warn people to avoid it in the manual.

Also, how does this interact with single_txn mode?  I suspect that's
just not very sane at all and we should forbid the combination.

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] ecpg grammar in CVS is annoying

[Alvaro Herrera]

Alvaro Herrera wrote:

> Oh, another thing -- ecpg has a dependency on libpq, but it is not
> declared in Makefiles, so if you build ecpg without first building
> libpq, it errors out.

This seems to fix the problem, but I'd prefer a rule that declared the
dependency without recursing ... is there a way to do that?

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)
Index: src/interfaces/ecpg/ecpglib/Makefile
===
RCS file: /home/alvherre/cvs/pgsql/src/interfaces/ecpg/ecpglib/Makefile,v
retrieving revision 1.62
diff -c -p -r1.62 Makefile
*** src/interfaces/ecpg/ecpglib/Makefile	14 Jan 2009 14:54:35 -	1.62
--- src/interfaces/ecpg/ecpglib/Makefile	30 Jan 2009 19:18:12 -
*** ifeq ($(PORTNAME), win32)
*** 42,48 
  SHLIB_LINK += -lshfolder
  endif
  
! all: all-lib
  
  # Shared library stuff
  include $(top_srcdir)/src/Makefile.shlib
--- 42,54 
  SHLIB_LINK += -lshfolder
  endif
  
! all: libpq pgtypeslib all-lib
! 
! libpq:
! 	$(MAKE) -C $(top_builddir)/src/interfaces/libpq all
! 
! pgtypeslib:
! 	$(MAKE) -C $(top_builddir)/src/interfaces/ecpg/pgtypeslib all
  
  # Shared library stuff
  include $(top_srcdir)/src/Makefile.shlib

-- 
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] ecpg grammar in CVS is annoying

[Alvaro Herrera]

Alvaro Herrera wrote:
> Hi,
> 
> Is it only me, or having the generated ecpg grammar is a bit obnoxious?
> I am getting troubled because whenever I touch gram.y it gets updated
> when I run make, and then it shows in "cvs diff".

Hmm, and then weird things happen anyway; I just built it and it errors
out with:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g 
-pthread  -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DECPG_COMPILE 
-I../include -I/pgsql/source/00orig/src/interfaces/ecpg/include 
-I/pgsql/source/00orig/src/interfaces/ecpg/preproc -DMAJOR_VERSION=4 
-DMINOR_VERSION=5 -DPATCHLEVEL=0 -I../../../../src/include 
-I/pgsql/source/00orig/src/include -D_GNU_SOURCE -I/usr/include/libxml2   -c -o 
preproc.o /pgsql/source/00orig/src/interfaces/ecpg/preproc/preproc.c -MMD -MP 
-MF .deps/preproc.Po
In file included from 
/pgsql/source/00orig/src/interfaces/ecpg/preproc/preproc.y:7128:
/pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l: In function 
‘base_yylex’:
/pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:484: error: ‘UCONST’ 
undeclared (first use in this function)
/pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:484: error: (Each 
undeclared identifier is reported only once
/pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:484: error: for each 
function it appears in.)
/pgsql/source/00orig/src/interfaces/ecpg/preproc/pgc.l:573: error: ‘UIDENT’ 
undeclared (first use in this function)
make[1]: *** [preproc.o] Error 1
make[1]: se sale del directorio 
`/home/alvherre/Code/CVS/pgsql/build/00orig/src/interfaces/ecpg/preproc'
make: *** [all] Error 2

This is on a clean checkout, so I'm not sure what's going on ...

-- 
Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Para tener más hay que desear menos"

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

[HACKERS] ecpg grammar in CVS is annoying

[Alvaro Herrera]

Hi,

Is it only me, or having the generated ecpg grammar is a bit obnoxious?
I am getting troubled because whenever I touch gram.y it gets updated
when I run make, and then it shows in "cvs diff".

I'm not sure what's the solution -- maybe something is missing in a
.cvsignore file somewhere?

Oh, another thing -- ecpg has a dependency on libpq, but it is not
declared in Makefiles, so if you build ecpg without first building
libpq, it errors out.

-- 
Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"Siempre hay que alimentar a los dioses, aunque la tierra esté seca" (Orual)

-- 
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] array_map not SQL accessible?

[Alvaro Herrera]

Alvaro Herrera wrote:
> Tom Lane wrote:

> > > As it happens, I need to use it in the pg_dump support for TOAST
> > > reloptions.
> > 
> > Maybe something involving
> > ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)
> 
> Hmm, I'll have a look at this.

It seems there's something wrong here.

alvherre=# select c.oid,tc.oid,c.relname, c.reloptions, array(select 'toast.' 
|| x from unnest(tc.reloptions) x) from pg_class c join pg_class tc on 
c.reltoastrelid = tc.oid where c.relname = 'foo';
-[ RECORD 1 
]
oid| 16395
oid| 16398
relname| foo
reloptions | {fillfactor=10,bogusopt=15}
?column?   | 
{toast.fillfactor=20,toast.\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F}

To reproduce easily:

create table text (a text[]);
insert into text values ('{fillfactor=10,bogusval=20}');
select array(select 'foobar.' || x from unnest(a) x) from text;

-- 
Alvaro Herrera   Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"I can see support will not be a problem.  10 out of 10."(Simon Wittber)
  (http://archives.postgresql.org/pgsql-general/2004-12/msg00159.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] array_map not SQL accessible?

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > I'm wondering why don't we expose the array_map() function to the SQL
> > level.
> 
> It requires some notion of "reference to function", which doesn't really
> exist in SQL.  (Please don't say you're going to pass it a function
> OID.)

regproc maybe?

> > As it happens, I need to use it in the pg_dump support for TOAST
> > reloptions.
> 
> Maybe something involving
>   ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)

Hmm, I'll have a look at this.

-- 
Alvaro Herrera   http://www.PlanetPostgreSQL.org/
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

-- 
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] array_map not SQL accessible?

[Tom Lane]

Alvaro Herrera  writes:
> I'm wondering why don't we expose the array_map() function to the SQL
> level.

It requires some notion of "reference to function", which doesn't really
exist in SQL.  (Please don't say you're going to pass it a function
OID.)

> As it happens, I need to use it in the pg_dump support for TOAST
> reloptions.

Maybe something involving
ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)

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] Should IS DISTINCT FROM work with ANY()?

[David E. Wheeler]

On Jan 29, 2009, at 5:50 PM, Tom Lane wrote:


I don't think we want it to come true.  If we treat IS DISTINCT FROM
as a weirdly-named operator then we have to provide an implementation
for every datatype (oh, and another one for IS NOT DISTINCT FROM).
The PITA factor is enormous.  Much better to handle it the way we
are now, where it's a specialized expression node type.

To get it to work with ANY/ALL you'd probably need some special hack  
to

create new sublink types, or something like that.  Also a PITA, but
a lot more localized ...


Okay, I don't know much about the internals, so of course it may be a  
PITA, but the documentation doesn't really sound like it. Maybe the  
docs need updating? For example, the documentation for ANY and SOME  
says:



expression operator ANY (array expression)
expression operator SOME (array expression)


Which makes me think that it will work with any comparison operator.  
Conveniently, IS (NOT)? DISTINCT FROM is listed on the comparison  
operators page, which says:



expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression


Since `ANY(ARRAY['foo'])` is an expression, I had expected it to work.  
Furthermore, the docs for ANY and SOME say:


For non-null inputs, IS DISTINCT FROM is the same as the <>  
operator. However, when both inputs are null it will return false,  
and when just one input is null it will return true.


Reading this, I assumed that IS DISTINCT FROM should work with any two  
operands to which <> applies. Meaning the underlying function would  
check for NULL values and return the proper value as appropriate, and  
simply re-dispatch to the function for the <> operator if neither  
operand is NULL. If that's the case, based on the docs, I'd just  
expect IS DISTINCT FROM ANY() to be supported, and we just have a  
parsing problem.


So maybe this isn't accurate? Should IS DISTINCT FROM *not* be  
documented as a binary operator? Or maybe it should be documented that  
it somehow doesn't rely on the = operator internally?


IOW, I get that you say it'd be a PITA to support this in in the code,  
Tom, so maybe the docs should be updated to explain what operands IS  
DISTINCT FROM can and cannot apply to?


Thanks,

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] How to learn all information on the user of a database?

[Robert Haas]

I think you might want to ask this question on pgsql-novice or pgsql-general.

You might also want to take a look at the pg_user and pg_stat_activity views.

...Robert

On Fri, Jan 30, 2009 at 7:16 AM,   wrote:
> How to learn all information on the user of a database? (user name,
> host name, sleep or running,connect or disconnect)
>
> Thanks
>
> Regards,
> Mr. St
>
> --
> 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

[HACKERS] array_map not SQL accessible?

[Alvaro Herrera]

Hi,

I'm wondering why don't we expose the array_map() function to the SQL
level.  As it happens, I need to use it in the pg_dump support for TOAST
reloptions.

Why?  Well, TOAST reloptions are stored in the pg_class tuple of the
TOAST table, so when I extract them directly, it looks like a simple
array of normally-named reloptions.  Like this:

alvherre=# select c.oid,c.relname, c.reloptions, tc.reloptions
alvherre-# from pg_class c join pg_class tc on c.reltoastrelid = tc.oid
alvherre-# where c.relname = 'foo';
  oid  | relname |   reloptions|   reloptions
---+-+-+-
 48372 | foo | {fillfactor=10} | {fillfactor=15}
(1 fila)

So I need the second array to look like this instead:

toast.fillfactor=15

The easiest way to do that that I can see is using array_map and a
function that prepends "toast." to each element.

So, can I just go ahead and try to expose it for this usage?

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

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

[HACKERS] [PATCH] Psql List Languages

[Fernando Ike]

Hi,,

My job, I maintainer some postgres server for clients. We have
many PL/(Java, Perl, Ruby, Python, R)  and to more easy
administration, I worked new little psql attribute to list languages
com shorcurt/function \dL.


postg...@darkside:/media/disk/devel/pg$ bin/psql -U postgres test
psql (8.4devel)
Type "help" for help.

test=# \dL
   List of languages
   Name   |  Owner   | Procedural Language |  Trusted   |Call
Handler |Validator
--+--+-++-+-
 c| postgres | No  | Unstrusted |
   | fmgr_c_validator
 internal | postgres | No  | Unstrusted |
   | fmgr_internal_validator
 plperl   | postgres | Yes | Trusted|
plperl_call_handler | plperl_validator
 sql  | postgres | No  | Trusted|
   | fmgr_sql_validator
(4 rows)

test=#



   I know that this moment is inappropriate to submit patch, with the
discussions about features for 8.4. But, if can added for commitfest
to 8.5 version. I'm appreciate.



Regards,
--
Fernando Ike
http://www.midstorm.org/~fike/weblog
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***
*** 375,380  exec_command(const char *cmd,
--- 375,383 
  			case 'l':
  success = do_lo_list();
  break;
+ 			case 'L':
+ success = listLanguages(pattern, show_verbose);
+ break;
  			case 'n':
  success = listSchemas(pattern, show_verbose);
  break;
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***
*** 2018,2023  listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
--- 2018,2081 
  	return true;
  }
  
+ /*
+  * \dL
+  *
+  * Describes Languages.
+  */
+ bool
+ listLanguages(const char *pattern, bool verbose)
+ {
+ 	PQExpBufferData buf;
+ 	PGresult   *res;
+ 	printQueryOpt myopt = pset.popt;
+ 
+ 	initPQExpBuffer(&buf);
+ 
+ 	printfPQExpBuffer(&buf,
+ 	  "SELECT l.lanname as \"%s\",\n"
+ 	  "   pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n"
+ 	  "   CASE WHEN l.lanispl = 't' THEN 'Trusted' WHEN l.lanispl = 'f' THEN 'Untrusted' END AS \"%s\",\n"
+ 	  "   CASE WHEN l.lanpltrusted='t' THEN 'Trusted' WHEN lanpltrusted='f' THEN 'Unstrusted' END AS \"%s\",\n"
+ 	  "   CASE WHEN p.oid = 0 THEN NULL ELSE p.proname END AS \"%s\",\n"
+ 	  "   CASE WHEN q.oid = 0 THEN NULL ELSE q.proname END AS \"%s\"\n",
+ 	  gettext_noop("Name"),
+ 	  gettext_noop("Owner"),
+ 	  gettext_noop("Procedural Language"),
+ 	  gettext_noop("Trusted"),
+ 	  gettext_noop("Call Handler"),
+ 	  gettext_noop("Validator"));
+ 
+ 	if (verbose)
+ 	{
+ 		appendPQExpBuffer(&buf, ",\n");
+ 		printACLColumn(&buf, "l.lanacl");
+ 	}
+  
+  	appendPQExpBuffer(&buf, " FROM pg_catalog.pg_language l\n");
+ 	appendPQExpBuffer(&buf, "  LEFT JOIN pg_catalog.pg_proc p on l.lanplcallfoid = p.oid\n");
+  	appendPQExpBuffer(&buf, "  LEFT JOIN pg_catalog.pg_proc q on l.lanvalidator = q.oid\n");
+  
+  	processSQLNamePattern(pset.db, &buf, pattern, false, false,
+  		  NULL, "l.lanname", NULL, NULL);
+  
+ 	appendPQExpBuffer(&buf, "ORDER BY 1;");
+  
+ 	res = PSQLexec(buf.data, false);
+ 	termPQExpBuffer(&buf);
+ 	if (!res)
+ 		return false;
+  
+ 	myopt.nullPrint = NULL;
+ 	myopt.title = _("List of languages");
+ 	myopt.translate_header = true;
+  
+ 	printQuery(res, &myopt, pset.queryFout, pset.logfile);
+  
+ 	PQclear(res);
+ 	return true;
+ 
+ }
  
  /*
   * \dD
*** a/src/bin/psql/describe.h
--- b/src/bin/psql/describe.h
***
*** 75,79  extern bool listForeignServers(const char *pattern, bool verbose);
--- 75,81 
  /* \deu */
  extern bool listUserMappings(const char *pattern, bool verbose);
  
+ /* \dL */
+ extern bool listLanguages(const char *pattern, bool verbose);
  
  #endif   /* DESCRIBE_H */
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
***
*** 215,220  slashUsage(unsigned short int pager)
--- 215,221 
  	fprintf(output, _("  \\dg  [PATTERN]list roles (groups)\n"));
  	fprintf(output, _("  \\di[S+]  [PATTERN]list indexes\n"));
  	fprintf(output, _("  \\dl   list large objects, same as \\lo_list\n"));
+ 	fprintf(output, _("  \\dL   list (procedural) languages\n"));
  	fprintf(output, _("  \\dn[+]   [PATTERN]list schemas\n"));
  	fprintf(output, _("  \\do[S]   [PATTERN]list operators\n"));
  	fprintf(output, _("  \\dp  [PATTERN]list table, view, and sequence access privileges\n"));

-- 
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] using composite types in insert/update

[Sam Mason]

On Fri, Jan 30, 2009 at 11:01:53AM -0500, Merlin Moncure wrote:
> On 1/30/09, Sam Mason  wrote:
> > The VALUES command is just a convenient way of getting lots of tuples
> >  into PG isn't it?  If the above was valid, PG would have to support
> >  similar syntax elsewhere, which seems independent of the feature you're
> >  really asking for.
> 
> You are missing the point, using the composite type allows you to
> build the insert without knowing the specific layout of the
> table...only the table itself and the fields that comprise the key for
> update statements.

But this has nothing to do with the VALUES command!  Going back to what
I interpret as your original point, I find myself wanting to write:

  SELECT *
  FROM foo f, (VALUES 1, 5, 7, 23, 47) v
  WHERE f.id = v;

but end up having to write the following:

  SELECT *
  FROM foo f, (VALUES (1), (5), (7), (23), (47)) x(v)
  WHERE f.id = x.v;

quite often (i.e. a VALUES command with many singletons).  This seems
a bit annoying and appears to be what you were suggesting you wanted
before (although you killed the relevant bit of context, making me think
we may be talking about different things).

> >  > ideally,
> >  > UPDATE foo SET foo = foo;
> >  > would be valid.
> >
> > Sounds useful, but seems to break existing syntax (imagine if the table
> >  "foo" had a column called "foo").  Takahiro suggests using a * to
> >  indicate what you're asking for and this seems to have nicer semantics
> >  to me.
> 
> I don't think it would...right now select statements work the way I
> want.  If there is table and column with the same name, the column
> name is assumed.  It's an issue of symmetry...why can't you insert the
> same way you select?

For several reasons; mainly because SQL is an abortion of a language,
it's got no regularity and attempts to justify requirements because of
"symmetry" will end up causing more headaches.

Another way of saying what you seem to be saying above is: I want things
to work correctly, unless I happen to have a column name that happens to
be the same as the table at which point I want everything to break.

> By the way, record types are virtually first class objects starting with 8.4:
> create index foo_idx on foo(foo);
> select (1,2)::foo = (3,4)::foo;
> select foo from foo order by foo;
> select foo::text::foo;  -- got this in 8.3
> 
> are all valid.

Record *types* are most definitely not first class objects;
record/composite *values* on the other hand have been gaining support
for a while.  There are a few weirdo's left, like VALUES commands only
working with records, but the dichotomy between record and "non-record"
types is slowly vanishing.

> >   UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;
> 
> Hm. IMO, set (*) is a completely new invention of what '*' means.

In my head, * has always meant all the columns associated with some
record.  This is just putting it in a new place in the grammar.  It's
nice because it doesn't introduce any ambiguities, whereas using the
table name does.

I'm not sure if the brackets are needed, but I thought it safer to leave
them in.

> >  > Aside from fixing a surprising behavior
> >
> > Or have I missed the point and you mean the "surprising behavior" is
> >  that you expect PG to generate WHERE clauses for you automatically.
> >  This seems impossible in the general case.
> 
> The surprising behavior is that 'select foo from foo' works, but
> 'update foo set foo = x::foo' does not.

Then blame the original designers of SQL; they optimized the syntax for
a different set of use cases!  Is the symmetry more obvious when you
compare:

  SELECT * FROM foo;

with

  UPDATE foo SET * = x;

?

-- 
  Sam  http://samason.me.uk/

-- 
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] Space reservation v02

[Tom Lane]

Gregory Stark  writes:
> Well having a column in pg_class does have some advantages. Like, you could
> look at the value from an sql session more easily. And if there are operations
> which we know are unsafe -- such as adding columns -- we could clear it from
> the server side easily.

Why would there be any unsafe operations?  Surely the patch would add
sufficient logic to prevent the old version from de-fixing any page
that had already been fixed.  If this is not so, the entire concept
is broken, because you're still going to have to go to single-user mode
for a long time to make sure that the whole database is in good shape.

On the whole I agree with Heikki's earlier criticism: this is all
about guessing the future, and the odds seem high that the actual
requirements will not be what you designed for anyway.

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] [PATCH] Space reservation v02

[Bruce Momjian]

Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > Heikki Linnakangas wrote:
> >>> For example CREATE/ALTER TABLE can cause problems.
> >> Yeah, if the pre-upgrade script determines the amount of reserved space 
> >> for each table, and sets it in pg_class or reloptions or whatever, it's 
> >> not clear how mwhat to do with tables created after the script is run. I 
> >> guess we need quick scan of pg_class before the actual upgrade to check 
> >> that you don't have newly-created tables, and refuse the upgrade if 
> >> there is.
> > 
> > This is where a pg_class column would be useful.  You default the column
> > value to -1.  The pre-upgrade script sets the proper reserved space, and
> > new tables get a -1 and you check for those just before the upgrade. 
> 
> You can do that with a flat file too. If there's any tables in the 
> database that were not present when pre-upgrade script was started, 
> throw an error.
> 
> It might be a bit simpler with a pg_class column, but if we don't know 
> what exactly we need to store there, and might need to resort to 
> different storage anyway, it doesn't seem worth it.
> 
> An extra table as Zdenek suggested in the passing might give the best of 
> both worlds. The pre-upgrade script can create it when it's run, so we 
> don't need to decide beforehand what columns we need, and it's a table 
> so you can query it etc.

Yep, makes sense.  I had forgotten that idea;  sorry.

-- 
  Bruce Momjian  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] [PATCH] Space reservation v02

[Heikki Linnakangas]

Bruce Momjian wrote:

Heikki Linnakangas wrote:

For example CREATE/ALTER TABLE can cause problems.
Yeah, if the pre-upgrade script determines the amount of reserved space 
for each table, and sets it in pg_class or reloptions or whatever, it's 
not clear how mwhat to do with tables created after the script is run. I 
guess we need quick scan of pg_class before the actual upgrade to check 
that you don't have newly-created tables, and refuse the upgrade if 
there is.


This is where a pg_class column would be useful.  You default the column
value to -1.  The pre-upgrade script sets the proper reserved space, and
new tables get a -1 and you check for those just before the upgrade. 


You can do that with a flat file too. If there's any tables in the 
database that were not present when pre-upgrade script was started, 
throw an error.


It might be a bit simpler with a pg_class column, but if we don't know 
what exactly we need to store there, and might need to resort to 
different storage anyway, it doesn't seem worth it.


An extra table as Zdenek suggested in the passing might give the best of 
both worlds. The pre-upgrade script can create it when it's run, so we 
don't need to decide beforehand what columns we need, and it's a table 
so you can query it etc.


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

Re: [HACKERS] [PATCH] Space reservation v02

[Bruce Momjian]

Heikki Linnakangas wrote:
> > For example CREATE/ALTER TABLE can cause problems.
> 
> Yeah, if the pre-upgrade script determines the amount of reserved space 
> for each table, and sets it in pg_class or reloptions or whatever, it's 
> not clear how mwhat to do with tables created after the script is run. I 
> guess we need quick scan of pg_class before the actual upgrade to check 
> that you don't have newly-created tables, and refuse the upgrade if 
> there is.

This is where a pg_class column would be useful.  You default the column
value to -1.  The pre-upgrade script sets the proper reserved space, and
new tables get a -1 and you check for those just before the upgrade. 

-- 
  Bruce Momjian  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] [PATCH] Space reservation v02

[Gregory Stark]

Heikki Linnakangas  writes:

> Zdenek Kotala wrote:
>> Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500:
>>> Well, I was thinking the new pg_class column would allow the upgrade to
>>> verify the pre-upgrade script was run properly, but a flat file works
>>> just as well if we assume we are going to pre-upgrade in one pass.
>>
>> Flat file or special table for pg_upgrade will work fine. 
>
> Right, there's no difference in what you can achieve, whether you store the
> additional info in a flat file, special table or extra pg_class columns. If 
> you
> can store something in pg_class, you can store it elsewhere just as well.

Well having a column in pg_class does have some advantages. Like, you could
look at the value from an sql session more easily. And if there are operations
which we know are unsafe -- such as adding columns -- we could clear it from
the server side easily.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

-- 
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] mingw check hung

[Bruce Momjian]

Andrew Dunstan wrote:
> > Anyway, yes, I think that would be OK. How do we then test to see if 
> > the original problem is still fixed?
> >
> >
> 
> Further proof that this is a Windows version issue: I took the problem 
> build from my XP and put it on my Vista box: the same build that causes 
> a problem on XP runs perfectly on Vista. Go figure. Maybe we need a 
> version check at runtime? That would be icky.

At a minimum we need to document this behavior in a source code comment.

-- 
  Bruce Momjian  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] [PATCH] Space reservation v02

[Heikki Linnakangas]

Zdenek Kotala wrote:

Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500:

Well, I was thinking the new pg_class column would allow the upgrade to
verify the pre-upgrade script was run properly, but a flat file works
just as well if we assume we are going to pre-upgrade in one pass.


Flat file or special table for pg_upgrade will work fine. 


Right, there's no difference in what you can achieve, whether you store 
the additional info in a flat file, special table or extra pg_class 
columns. If you can store something in pg_class, you can store it 
elsewhere just as well.



However,  I am afraid requiring this pre-upgrade to run while the server
is basically in single-user mode will make upgrade-in-place be a long
process for many users, and if it takes a significant time compared to
dump/reload, they might as well dump/reload.


pre_upgrade script should be run during normal operation. There will be
some limitation.


Right. That's the whole point of having a pre-upgrade script. Otherwise 
you might as well run the conversion in the new version.



For example CREATE/ALTER TABLE can cause problems.


Yeah, if the pre-upgrade script determines the amount of reserved space 
for each table, and sets it in pg_class or reloptions or whatever, it's 
not clear how mwhat to do with tables created after the script is run. I 
guess we need quick scan of pg_class before the actual upgrade to check 
that you don't have newly-created tables, and refuse the upgrade if 
there is.


However, if we have the logic to determine how much space to reserve for 
a table in the backend, as a back-ported patch, then we can invoke it 
for new tables just as well.


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

Re: [HACKERS] using composite types in insert/update

[Tom Lane]

Merlin Moncure  writes:
> You are missing the point, using the composite type allows you to
> build the insert without knowing the specific layout of the
> table...

Surely at *some* level you have to know that.

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] mingw check hung

[Andrew Dunstan]

Andrew Dunstan wrote:



Magnus Hagander wrote:

Andrew Dunstan wrote:
 

Magnus Hagander wrote:
   

Are we *sure*, btw, that this is actually a mingw issue, and not
something else in the environment? Could you try a MSVC compiled 
binary

on the same machine?


My MSVC buildfarm animal runs on the same machine, and does not suffer
the same problem.



Meh. Stupid mingw :-)

So how about we #ifdef out that NULL setting based on
WIN32_ONLY_COMPILER, does that seem reasonable?


  


The odd thing is that it doesn't seem to affect Vista, only XP.

Anyway, yes, I think that would be OK. How do we then test to see if 
the original problem is still fixed?





Further proof that this is a Windows version issue: I took the problem 
build from my XP and put it on my Vista box: the same build that causes 
a problem on XP runs perfectly on Vista. Go figure. Maybe we need a 
version check at runtime? That would be icky.


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] using composite types in insert/update

[Merlin Moncure]

On 1/30/09, Sam Mason  wrote:
> On Wed, Jan 28, 2009 at 12:03:56PM -0500, Merlin Moncure wrote:
>  > IMO, composite types on insert/update should work as they do on select:
>
> The VALUES command is just a convenient way of getting lots of tuples
>  into PG isn't it?  If the above was valid, PG would have to support
>  similar syntax elsewhere, which seems independent of the feature you're
>  really asking for.

You are missing the point, using the composite type allows you to
build the insert without knowing the specific layout of the
table...only the table itself and the fields that comprise the key for
update statements.

>  > but we have a workaround:
>  > INSERT INTO foo SELECT  ('(something)'::foo).* -- expands foo into foo 
> columns
>
>
> Or if you wanted to insert multiple rows:
>
>   INSERT INTO foo SELECT (txt::foo).* FROM
> (VALUES ('(something)'), ('(something else)')) x(txt);
>
>
>  > however no such workaround exists for update. ideally,
>  > UPDATE foo SET foo = foo;
>  >
>  > would be valid.
>
> Sounds useful, but seems to break existing syntax (imagine if the table
>  "foo" had a column called "foo").  Takahiro suggests using a * to
>  indicate what you're asking for and this seems to have nicer semantics
>  to me.

I don't think it would...right now select statements work the way I
want.  If there is table and column with the same name, the column
name is assumed.  It's an issue of symmetry...why can't you insert the
same way you select?

By the way, record types are virtually first class objects starting with 8.4:
create index foo_idx on foo(foo);
select (1,2)::foo = (3,4)::foo;
select foo from foo order by foo;
select foo::text::foo;  -- got this in 8.3

are all valid.

>  There seem to be two different improvements needed; the first would be
>  in allowing composite values on the RHS, the second in allowing the
>  column list on the LHS to be replaced with a *. E.g. we start with the
>  following code:
>
>   CREATE TEMP TABLE foo ( a INT, b TEXT );
>   INSERT INTO foo ( 1, 'a' );
>
>  the following is currently valid:
>
>   UPDATE foo SET (a,b) = (x.a,x.b) FROM (SELECT ('(2,c)'::foo).*) x;
>
>  The first step would allow you to do:
>
>   UPDATE foo SET (a,b) = x FROM (SELECT ('(2,c)'::foo).*) x;
>
>  and the second step allow you to do:
>
>   UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;

Hm. IMO, set (*) is a completely new invention of what '*' means.  I
guess it's ok though, but I think the composite type is more natural.
I think if you went this route you should think about other places
that this syntax might be valid.  I'm not arguing against what you're
saying, but the composite type should work too.

>  > Aside from fixing a surprising behavior
>
> Or have I missed the point and you mean the "surprising behavior" is
>  that you expect PG to generate WHERE clauses for you automatically.
>  This seems impossible in the general case.

The surprising behavior is that 'select foo from foo' works, but
'update foo set foo = x::foo' does not.

>  > , it would
>  > greatly aid in writing triggers that do things like ship updates over
>  > dblink _much_ easier (in fact...the dblink_build_xxx family would
>  > become obsolete).
>  >
>  > e.g.
>  > perform dblink.dblink('UPDATE foo SET foo = \'' || new || '\'::foo);
>  >
>  > I call the existing behavior of insert/update of composite types
>  > broken to the point of almost being a bug.  Fixing the above to work
>  > would close the loop on a broad new set of things you can do with
>  > composite types.
>
> How well would something like this work in practice?  If for some reason
>  "foo" had been created with the columns in a different order in the two
>  databases then you'd end up with things breaking pretty quickly.  One

That's a separate application specific issue that applies only to
dblink style replication (and I don't think keeping to schemas similar
is really all that difficult).  Composite type insertion has
usefulness far beyond dblink triggers.  Imagine a global trigger that
captures record to text and logs to text table.  Now it's trivial if
you want to render the text back into the table, since you don't have
to look up the field list to generate the statement.

>  naive way out seems to be to include the column names in serialized
>  tuples.  This has advantages (i.e. we're always told not to rely on
>  column order and this would be one less place we implicitly had to) as
>  well as disadvantages (i.e. the size of the resulting serialized value
>  would go up and well as the complexity of the serialization routine).
>

That's nice as well, but should be considered separately from powering
composite types.

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] reloptions with a "namespace"

[Alvaro Herrera]

Euler Taveira de Oliveira wrote:
> Alvaro Herrera escreveu:

> > Okay, so I've changed things so that the transformRelOptions' caller is
> > now in charge of passing an array of valid option namespaces.  This is
> > working A-OK.  I'm now going to figure out appropriate pg_dump support
> > and commit as soon as possible.
> > 
> I don't like the spreading validnsps' approach. Isn't there a way to
> centralize those variables in one place, i.e., reloption.h ? Also, remove an
> obsolete comment about toast tables at reloption.h.

No, that doesn't work, because we don't know centrally what's the
allowed list of namespaces.  In fact that's precisely the problem: for
example, there's no point in having a "toast" namespace for index
reloptions.  And for a user-defined access method, we don't know what
the valid namespaces are.  Of course, the easiest way is to just state
that there are no valid namespaces other than NULL, and only allow
"toast" for heap, but I think that's not thinking far enough ahead.

The other option I considered was to have another AM entry point that
returns the list of valid namespaces, but that seems to be way overkill,
particularly considering that the current arrays are all NULL.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

-- 
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] Space reservation v02

[Zdenek Kotala]

Bruce Momjian píše v pá 30. 01. 2009 v 10:41 -0500:
> Heikki Linnakangas wrote:
> > Bruce Momjian wrote:
> > > The patch has two space reservations, one per page, another per tuple. 
> > > Now, thinking back, what types of changes have we made that increase
> > > storage size.  The one that I can think of first is where we made a data
> > > type require larger storage.  (I think inet/cidr.)  This could not be
> > > handled by this patch because if a row had _two_ values of that type,
> > > there would be no way to specify this using the two supplied parameters.
> > 
> > Well, I believe the idea was that the pre-upgrade script that sets the 
> > space reservation would look at the catalogs to decide the right 
> > reservation for each table.
> 
> Interesting --- so you set the reservation per table --- that seems much
> better than a GUC, certainly.  I assume we would still need a per-page
> GUC that affects all tables?  Or one for heap and one for index pages?

Each access methods has different requirements and it heavily depends on
specific relations. Also TOAST tables has different requirements. GUC
variable is not good option.

> > > One thing I think would help would be a pg_class column that says
> > > whether the table is ready for upgrading.  This is something we can't
> > > easily backpatch and would be helpful so people could do their upgrade
> > > preparation in a staged manner, rather than having to do it all at once,
> > > and would give the upgrade scripts confidence that the backpatch had
> > > done everying needed.  The backpatched code would set this pg_class
> > > column value when it was done making sure the table is ready for upgrade
> > > (probably via vacuum).  I recommend an int2 column to store
> > > PG_VERSION_NUM / 100.
> > 
> > I think that being able to stop and restart the pre-upgrade process is a 
> > luxury we can add later. Also note that the pre-upgrade tool can use a 
> > flat file in the data directory to store state in a more free-form 
> > fashion. To implement restartability, for example, you could dump a list 
> > of relfilenodes not yet scanned to the file at start, and strike them 
> > out as you go.
> 
> Well, I was thinking the new pg_class column would allow the upgrade to
> verify the pre-upgrade script was run properly, but a flat file works
> just as well if we assume we are going to pre-upgrade in one pass.

Flat file or special table for pg_upgrade will work fine. 

> However,  I am afraid requiring this pre-upgrade to run while the server
> is basically in single-user mode will make upgrade-in-place be a long
> process for many users, and if it takes a significant time compared to
> dump/reload, they might as well dump/reload.

pre_upgrade script should be run during normal operation. There will be
some limitation. For example CREATE/ALTER TABLE can cause problems.

Zdenek


-- 
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] Space reservation v02

[Bruce Momjian]

Heikki Linnakangas wrote:
> Bruce Momjian wrote:
> > The patch has two space reservations, one per page, another per tuple. 
> > Now, thinking back, what types of changes have we made that increase
> > storage size.  The one that I can think of first is where we made a data
> > type require larger storage.  (I think inet/cidr.)  This could not be
> > handled by this patch because if a row had _two_ values of that type,
> > there would be no way to specify this using the two supplied parameters.
> 
> Well, I believe the idea was that the pre-upgrade script that sets the 
> space reservation would look at the catalogs to decide the right 
> reservation for each table.

Interesting --- so you set the reservation per table --- that seems much
better than a GUC, certainly.  I assume we would still need a per-page
GUC that affects all tables?  Or one for heap and one for index pages?

> > One thing I think would help would be a pg_class column that says
> > whether the table is ready for upgrading.  This is something we can't
> > easily backpatch and would be helpful so people could do their upgrade
> > preparation in a staged manner, rather than having to do it all at once,
> > and would give the upgrade scripts confidence that the backpatch had
> > done everying needed.  The backpatched code would set this pg_class
> > column value when it was done making sure the table is ready for upgrade
> > (probably via vacuum).  I recommend an int2 column to store
> > PG_VERSION_NUM / 100.
> 
> I think that being able to stop and restart the pre-upgrade process is a 
> luxury we can add later. Also note that the pre-upgrade tool can use a 
> flat file in the data directory to store state in a more free-form 
> fashion. To implement restartability, for example, you could dump a list 
> of relfilenodes not yet scanned to the file at start, and strike them 
> out as you go.

Well, I was thinking the new pg_class column would allow the upgrade to
verify the pre-upgrade script was run properly, but a flat file works
just as well if we assume we are going to pre-upgrade in one pass.

However,  I am afraid requiring this pre-upgrade to run while the server
is basically in single-user mode will make upgrade-in-place be a long
process for many users, and if it takes a significant time compared to
dump/reload, they might as well dump/reload.

But again, all this is trying to handle cases where the data size
increases, which is a rare event for us.

-- 
  Bruce Momjian  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] mingw check hung

[Andrew Dunstan]

Magnus Hagander wrote:

Andrew Dunstan wrote:
  

Magnus Hagander wrote:


Are we *sure*, btw, that this is actually a mingw issue, and not
something else in the environment? Could you try a MSVC compiled binary
on the same machine?
  
  

My MSVC buildfarm animal runs on the same machine, and does not suffer
the same problem.



Meh. Stupid mingw :-)

So how about we #ifdef out that NULL setting based on
WIN32_ONLY_COMPILER, does that seem reasonable?


  


The odd thing is that it doesn't seem to affect Vista, only XP.

Anyway, yes, I think that would be OK. How do we then test to see if the 
original problem is still fixed?


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

[Andrew Dunstan]

Jean-Michel Riet wrote:

Hello,

I'm coding some functions in C language on Windows for Postgres server 
(calling from PG).


After dll generation and put it to the correct postgres directory the 
problem is as follow :


Postgres stop running when i call  /fflush() / function on a file 
stream and no data are in my file.


The behavior is already the same,  Postgres process dies.

Is it a know problem ?,
or there is a way to protect the calling function, or  to implement 
with caution 
the soft.




That seems very odd. I think you'll need to show us the whole function code.

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] mingw check hung

[Magnus Hagander]

Andrew Dunstan wrote:
> 
> 
> Magnus Hagander wrote:
>>
>> Are we *sure*, btw, that this is actually a mingw issue, and not
>> something else in the environment? Could you try a MSVC compiled binary
>> on the same machine?
>>   
> 
> My MSVC buildfarm animal runs on the same machine, and does not suffer
> the same problem.

Meh. Stupid mingw :-)

So how about we #ifdef out that NULL setting based on
WIN32_ONLY_COMPILER, does that seem reasonable?

//Magnus


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

[HACKERS] fflush

[Jean-Michel Riet]

Hello,

I'm coding some functions in C language on Windows for Postgres server
(calling from PG).

After dll generation and put it to the correct postgres directory the
problem is as follow :

Postgres stop running when i call  fflush()  function on a file
stream and no data are in my file.

The behavior is already the same,  Postgres process dies.

Is it a know problem ?, 
or there is a way to protect the calling function, or  to
implement with caution 
the soft.
Best regards
Jean-Michel 

[HACKERS] How to learn all information on the user of a database?

[mmf . stavelot]

How to learn all information on the user of a database? (user name,
host name, sleep or running,connect or disconnect)

Thanks

Regards,
Mr. St

-- 
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] mingw check hung

[Andrew Dunstan]

Magnus Hagander wrote:


Are we *sure*, btw, that this is actually a mingw issue, and not
something else in the environment? Could you try a MSVC compiled binary
on the same machine?
  


My MSVC buildfarm animal runs on the same machine, and does not suffer 
the same problem.


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] using composite types in insert/update

[Sam Mason]

On Wed, Jan 28, 2009 at 12:03:56PM -0500, Merlin Moncure wrote:
> IMO, composite types on insert/update should work as they do on select:

> INSERT INTO foo VALUES '(something)'::foo -- fails,

The VALUES command is just a convenient way of getting lots of tuples
into PG isn't it?  If the above was valid, PG would have to support
similar syntax elsewhere, which seems independent of the feature you're
really asking for.

> but we have a workaround:
> INSERT INTO foo SELECT  ('(something)'::foo).* -- expands foo into foo columns

Or if you wanted to insert multiple rows:

  INSERT INTO foo SELECT (txt::foo).* FROM
(VALUES ('(something)'), ('(something else)')) x(txt);

> however no such workaround exists for update. ideally,
> UPDATE foo SET foo = foo;
> 
> would be valid.

Sounds useful, but seems to break existing syntax (imagine if the table
"foo" had a column called "foo").  Takahiro suggests using a * to
indicate what you're asking for and this seems to have nicer semantics
to me.

There seem to be two different improvements needed; the first would be
in allowing composite values on the RHS, the second in allowing the
column list on the LHS to be replaced with a *. E.g. we start with the
following code:

  CREATE TEMP TABLE foo ( a INT, b TEXT );
  INSERT INTO foo ( 1, 'a' );

the following is currently valid:

  UPDATE foo SET (a,b) = (x.a,x.b) FROM (SELECT ('(2,c)'::foo).*) x;

The first step would allow you to do:

  UPDATE foo SET (a,b) = x FROM (SELECT ('(2,c)'::foo).*) x;

and the second step allow you to do:

  UPDATE foo SET (*) = x FROM (SELECT ('(2,c)'::foo).*) x;

> Aside from fixing a surprising behavior

Or have I missed the point and you mean the "surprising behavior" is
that you expect PG to generate WHERE clauses for you automatically.
This seems impossible in the general case.

> , it would
> greatly aid in writing triggers that do things like ship updates over
> dblink _much_ easier (in fact...the dblink_build_xxx family would
> become obsolete).
> 
> e.g.
> perform dblink.dblink('UPDATE foo SET foo = \'' || new || '\'::foo);
> 
> I call the existing behavior of insert/update of composite types
> broken to the point of almost being a bug.  Fixing the above to work
> would close the loop on a broad new set of things you can do with
> composite types.

How well would something like this work in practice?  If for some reason
"foo" had been created with the columns in a different order in the two
databases then you'd end up with things breaking pretty quickly.  One
naive way out seems to be to include the column names in serialized
tuples.  This has advantages (i.e. we're always told not to rely on
column order and this would be one less place we implicitly had to) as
well as disadvantages (i.e. the size of the resulting serialized value
would go up and well as the complexity of the serialization routine).

-- 
  Sam  http://samason.me.uk/

-- 
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] Synch Replication - Synch rep 0114

[Fujii Masao]

Hi,

On Fri, Jan 30, 2009 at 8:05 PM, Patil, Smita (NSN - IN/Bangalore)
 wrote:
> Hi,
> I have been testing in recent, the Synch Replication(Synch rep 0114 (Jan 14,
> 2009) ) on PostgreSQL version 8.4 (
> postgresql-8.4devel_20081229.tar.bz2)

Thanks for your testing and report!

I'm afraid that the base HEAD version
(postgresql-8.4devel_20081229.tar.bz2) is old,
which might have caused the following error. So, please try to apply
synch-rep v0128
patch to the latest HEAD, and test it.

If you can use cvs, the following document might be helpful for you to
get the latest HEAD.
http://www.postgresql.org/docs/8.3/static/anoncvs.html

> As per wiki, I am able to bring up the walsender and the walreceiver process
> in a single server as well when primary and seconday are setup on different
> nodes(making necessary changes to the test script)

What kind of change was required?

> Then I am able to see the walsender and walreceiver process are in progress.

Good!

> Then I try to insert some records into the table created (within the script)
> as below:
> ./psql
> psql (8.4devel)
> Type "help" for help.
>
> postgres=# insert into temp values(5,'e');

Please let me know the DDL of creating "temp" table. I'll test it also on
my machine.

> After this, I see both walsender and walreceiver are down and writer process
> is still running.
> Is this because, there is no provision of replication between primary and
> secondary?

Yes, it's because unexpected error terminated replication (ie. walsender
and walreceiver). But, such termination of replication doesn't affect the
primary's normal processing, so walwriter was still running on the primary.

> Or is it because write transactions are not supported?

Write transactions are also supported like original postgres.

Regards,

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

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

[HACKERS] Synch Replication - Synch rep 0114

[Patil, Smita (NSN - IN/Bangalore)]

Hi,
I have been testing in recent, the Synch Replication(Synch rep 0114
  (Jan 14,
2009) ) on PostgreSQL version 8.4 ( 
postgresql-8.4devel_20081229.tar.bz2
 ) 
I followed the steps in Readme as well used the test script provided in
patch for the setup.
As per wiki, I am able to bring up the walsender and the walreceiver
process in a single server as well when primary and seconday are setup
on different nodes(making necessary changes to the test script)
 
Then I am able to see the walsender and walreceiver process are in
progress.
 
Then I try to insert some records into the table created (within the
script) as below:
./psql
psql (8.4devel)
Type "help" for help.
 
postgres=# insert into temp values(5,'e');
 
I get the following output :
Standby 6820 FATAL:  unexpected EOF on replication connection: lost
synchronization with server: got message type "c", length -805175295
 
Primary 6821 LOG:  unexpected EOF on replication connection
Primary 6821 LOG:  replication done at: write 0/100 (file
0001), flush 0/100 (file
0001)
Standby 6820 LOG:  replication done at: write 0/100 (file
0001), flush 0/100 (file
0001)
Standby 6812 LOG:  could not open file
"pg_xlog/00010001" (log file 0, segment 1): No such file
or directory
Standby 6812 LOG:  redo done at 0/4A983C
Standby 6812 PANIC:  could not open file
"pg_xlog/0001" (log file 0, segment 0): No such file
or directory
Standby 6809 LOG:  startup process (PID 6812) was terminated by signal
6: Aborted
Standby 6809 LOG:  aborting startup due to startup process failure
INSERT 0 1

After this, I see both walsender and walreceiver are down and writer
process is still running.
Is this because, there is no provision of replication between primary
and secondary?
Or is it because write transactions are not supported?
 
In case where primary and standby are run on two different nodes, I am
able to bring up the walsender and walreceiver process.
But atleast read transactions( records inserted in primary ) are not
getting reflected in the standby node.
 
In such cases I would like to know about what exact features are working
with this patch?
Because, in the Readme section of Synch Replication wiki, it is
mentioned to check whether the walsender and walreceiver process are in
progress.
How about replication and read - write transactions?
 
Also with the latest patch Synch rep 0128
  (Jan 28,
2009), Am getting compilation errors.
Please let me about the correct status of the Synch Replication about
what features are working properly.
 
Regards, 
Smita Patil 

 

 
<>

[HACKERS] Synch Replication - Synch rep 0114

[smitap3]

Hi,
I have been testing in recent, the Synch Replication(Synch rep 0114 (Jan 14,
2009) ) on PostgreSQL version 8.4 (postgresql-8.4devel_20081229.tar.bz2)
I followed the steps in Readme as well used the test script provided in
patch for the setup.
As per wiki, I am able to bring up the walsender and the walreceiver process
in a single server as well when primary and seconday are setup on different
nodes(making necessary changes to the test script)
 
Then I am able to see the walsender and walreceiver process are in progress.
 
Then I try to insert some records into the table created (within the script)
as below:
./psql
psql (8.4devel)
Type "help" for help.
 
postgres=# insert into temp values(5,'e');
 
I get the following output :
Standby 6820 FATAL:  unexpected EOF on replication connection: lost
synchronization with server: got message type "c", length -805175295
 
Primary 6821 LOG:  unexpected EOF on replication connection
Primary 6821 LOG:  replication done at: write 0/100 (file
0001), flush 0/100 (file 0001)
Standby 6820 LOG:  replication done at: write 0/100 (file
0001), flush 0/100 (file 0001)
Standby 6812 LOG:  could not open file "pg_xlog/00010001"
(log file 0, segment 1): No such file or directory
Standby 6812 LOG:  redo done at 0/4A983C
Standby 6812 PANIC:  could not open file "pg_xlog/0001"
(log file 0, segment 0): No such file or directory
Standby 6809 LOG:  startup process (PID 6812) was terminated by signal 6:
Aborted
Standby 6809 LOG:  aborting startup due to startup process failure
INSERT 0 1

After this, I see both walsender and walreceiver are down and writer process
is still running.
Is this because, there is no provision of replication between primary and
secondary?
Or is it because write transactions are not supported?
 
In case where primary and standby are run on two different nodes, I am able
to bring up the walsender and walreceiver process.
But atleast read transactions( records inserted in primary ) are not getting
reflected in the standby node.
 
In such cases I would like to know about what exact features are working
with this patch?
Because, in the Readme section of Synch Replication wiki, it is mentioned to
check whether the walsender and walreceiver process are in progress.
How about replication and read - write transactions?
 
Also with the latest patch Synch rep 0128 (Jan 28, 2009), Am getting
compilation errors.
Please let me about the correct status of the Synch Replication about what
features are working properly.
 
Regards, 
Smita Patil 


 
-- 
View this message in context: 
http://www.nabble.com/Synch-Replication---Synch-rep-0114-tp21745679p21745679.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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

[HACKERS] Synch Replication - Synch rep 0114

[smitap3]

Hi,
I have been testing in recent, the Synch Replication(Synch rep 0114 (Jan 14,
2009) ) on PostgreSQL version 8.4 (postgresql-8.4devel_20081229.tar.bz2)
I followed the steps in Readme as well used the test script provided in
patch for the setup.
As per wiki, I am able to bring up the walsender and the walreceiver process
in a single server as well when primary and seconday are setup on different
nodes(making necessary changes to the test script)
 
Then I am able to see the walsender and walreceiver process are in progress.
 
Then I try to insert some records into the table created (within the script)
as below:
./psql
psql (8.4devel)
Type "help" for help.
 
postgres=# insert into temp values(5,'e');
 
I get the following output :
Standby 6820 FATAL:  unexpected EOF on replication connection: lost
synchronization with server: got message type "c", length -805175295
 
Primary 6821 LOG:  unexpected EOF on replication connection
Primary 6821 LOG:  replication done at: write 0/100 (file
0001), flush 0/100 (file 0001)
Standby 6820 LOG:  replication done at: write 0/100 (file
0001), flush 0/100 (file 0001)
Standby 6812 LOG:  could not open file "pg_xlog/00010001"
(log file 0, segment 1): No such file or directory
Standby 6812 LOG:  redo done at 0/4A983C
Standby 6812 PANIC:  could not open file "pg_xlog/0001"
(log file 0, segment 0): No such file or directory
Standby 6809 LOG:  startup process (PID 6812) was terminated by signal 6:
Aborted
Standby 6809 LOG:  aborting startup due to startup process failure
INSERT 0 1

After this, I see both walsender and walreceiver are down and writer process
is still running.
Is this because, there is no provision of replication between primary and
secondary?
Or is it because write transactions are not supported?
 
In case where primary and standby are run on two different nodes, I am able
to bring up the walsender and walreceiver process.
But atleast read transactions( records inserted in primary ) are not getting
reflected in the standby node.
 
In such cases I would like to know about what exact features are working
with this patch?
Because, in the Readme section of Synch Replication wiki, it is mentioned to
check whether the walsender and walreceiver process are in progress.
How about replication and read - write transactions?
 
Also with the latest patch Synch rep 0128 (Jan 28, 2009), Am getting
compilation errors.
Please let me about the correct status of the Synch Replication about what
features are working properly.
 
Regards, 
Smita Patil 


 
-- 
View this message in context: 
http://www.nabble.com/Synch-Replication---Synch-rep-0114-tp21745603p21745603.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] Hot standby, recovery infra

[Heikki Linnakangas]

Simon Riggs wrote:

On Thu, 2009-01-29 at 19:20 +0200, Heikki Linnakangas wrote:
Hmm, seems like we haven't thought through how shutdown during 
consistent recovery is supposed to behave in general. Right now, smart 
shutdown doesn't do anything during consistent recovery, because the 
startup process will just keep going. And fast shutdown will simply 
ExitPostmaster(1), which is clearly not right.


That whole area was something I was leaving until last, since immediate
shutdown doesn't work either, even in HEAD. (Fujii-san and I discussed
this before Christmas, briefly).


We must handle shutdown gracefully, can't just leave bgwriter running 
after postmaster exit.


Hmm, why does pg_standby catch SIGQUIT? Seems it could just let it kill 
the process.


I wonder if bgwriter should perform a restartpoint before exiting? 
You'll have to start with recovery on the next startup anyway, but at 
least we could minimize the amount of WAL that needs to be replayed.


That seems like extra work for no additional benefit.

I think we're beginning to blur the lines between review and you just
adding some additional stuff in this area. There's nothing to stop you
doing further changes after this has been committed.


Sure. I think the "shutdown restartpoint" might actually fall out of the 
way the code is structured anyway: bgwriter normally performs a 
checkpoint before exiting.



We can also commit
what we have with some caveats also, i.e. commit in pieces.


This late in the release cycle, I don't want to commit anything that we 
would have to rip out if we run out of time. There is no difference from 
review or testing point of view whether the code is in CVS or not.


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

Re: [HACKERS] Hot standby, recovery infra

[Heikki Linnakangas]

Simon Riggs wrote:

I'm thinking to add a new function that will allow crash testing easier.

pg_crash_standby() will issue a new xlog record, XLOG_CRASH_STANDBY,
which when replayed will just throw a FATAL error and crash Startup
process. We won't be adding that to the user docs...

This will allow us to produce tests that crash the server at specific
places, rather than trying to trap those points manually.


Heh, talk about a footgun ;-). I don't think including that in CVS is a 
good idea.


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

Re: [HACKERS] Hot standby, recovery infra

[Simon Riggs]

On Thu, 2009-01-29 at 14:21 +0200, Heikki Linnakangas wrote:
> It looks like if you issue a fast shutdown during recovery, postmaster 
> doesn't kill bgwriter.

Thanks for the report.

I'm thinking to add a new function that will allow crash testing easier.

pg_crash_standby() will issue a new xlog record, XLOG_CRASH_STANDBY,
which when replayed will just throw a FATAL error and crash Startup
process. We won't be adding that to the user docs...

This will allow us to produce tests that crash the server at specific
places, rather than trying to trap those points manually.

> Seems that reaper() needs to be taught that bgwriter can be active 
> during consistent recovery. I'll take a look at how to do that.
> 
> 
> BTW, the message "terminating connection ..." is a bit misleading. It's 
> referring to the startup process, which is hardly a connection. We have 
> that in CVS HEAD too, so it's not something introduced by the patch, but 
> seems worth changing in HS, since we then let real connections in while 
> startup process is still running.
> 
-- 
 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] Hot standby, recovery infra

[Simon Riggs]

On Fri, 2009-01-30 at 11:33 +0200, Heikki Linnakangas wrote:
> I just realized that the new minSafeStartPoint is actually exactly the 
> same concept as the existing minRecoveryPoint. As the recovery 
> progresses, we could advance minRecoveryPoint just as well as the new 
> minSafeStartPoint.
> 
> Perhaps it's a good idea to keep them separate anyway though, the 
> original minRecoveryPoint might be a useful debugging aid. Or what do 
> you think?

I think we've been confusing ourselves substantially. The patch already
has everything it needs, but there is a one-line-fixable bug where
Fujii-san says.

The code comments already explain how this works

* There are two points in the log that we must pass. The first
* is minRecoveryPoint, which is the LSN at the time the
* base backup was taken that we are about to rollforward from.
* If recovery has ever crashed or was stopped there is also
* another point also: minSafeStartPoint, which we know the
* latest LSN that recovery could have reached prior to crash.

The later message

FATAL  WAL ends before end time of backup dump

was originally triggered if

if (XLByteLT(EndOfLog, ControlFile->minRecoveryPoint))

and I changed that. Now I look at it again, I see that the original if
test, shown above, is correct and should not have been changed.

Other than that, I don't see the need for further change. Heikki's
suggestions to write a new minSafeStartPoint are good ones and fit
within the existing mechanisms and meanings of these variables.

-- 
 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] Hot standby, recovery infra

[Simon Riggs]

On Thu, 2009-01-29 at 19:20 +0200, Heikki Linnakangas wrote:
> Heikki Linnakangas wrote:
> > It looks like if you issue a fast shutdown during recovery, postmaster 
> > doesn't kill bgwriter.
> 
> Hmm, seems like we haven't thought through how shutdown during 
> consistent recovery is supposed to behave in general. Right now, smart 
> shutdown doesn't do anything during consistent recovery, because the 
> startup process will just keep going. And fast shutdown will simply 
> ExitPostmaster(1), which is clearly not right.

That whole area was something I was leaving until last, since immediate
shutdown doesn't work either, even in HEAD. (Fujii-san and I discussed
this before Christmas, briefly).

> I'm thinking that in both smart and fast shutdown, the startup process 
> should exit in a controlled way as soon as it's finished with the 
> current WAL record, and set minSafeStartPoint to the current point in 
> the replay.

That makes sense, though isn't required.

> I wonder if bgwriter should perform a restartpoint before exiting? 
> You'll have to start with recovery on the next startup anyway, but at 
> least we could minimize the amount of WAL that needs to be replayed.

That seems like extra work for no additional benefit.

I think we're beginning to blur the lines between review and you just
adding some additional stuff in this area. There's nothing to stop you
doing further changes after this has been committed. We can also commit
what we have with some caveats also, i.e. commit in pieces.

-- 
 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] Hot standby, recovery infra

[Simon Riggs]

On Thu, 2009-01-29 at 20:35 +0200, Heikki Linnakangas wrote:
> Hmm, another point of consideration is how this interacts with the 
> pause/continue. In particular, it was suggested earlier that you
> could 
> put an option into recovery.conf to start in paused mode. If you
> pause 
> recovery, and then stop and restart the server, and have that option
> in 
> recovery.conf, I would expect that when you enter consistent recovery 
> you're at the exact same paused location as before stopping the
> server. 
> The upshot of that is that we need to set minSafeStartPoint to that 
> exact location, at least when you pause & stop in a controlled
> fashion.

OK, makes sense.

-- 
 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] Hot standby, recovery infra

[Heikki Linnakangas]

I just realized that the new minSafeStartPoint is actually exactly the 
same concept as the existing minRecoveryPoint. As the recovery 
progresses, we could advance minRecoveryPoint just as well as the new 
minSafeStartPoint.


Perhaps it's a good idea to keep them separate anyway though, the 
original minRecoveryPoint might be a useful debugging aid. Or what do 
you think?


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

Re: Commitfest infrastructure (was Re: [HACKERS] 8.4 release planning)

[Zdenek Kotala]

Stefan Kaltenbrunner píše v čt 29. 01. 2009 v 18:29 +0100:
> Peter Eisentraut wrote:
> > On Thursday 29 January 2009 11:40:48 Stefan Kaltenbrunner wrote:
> >> well from a quick glance there is the bugzilla demo install as well as
> >> pieces of reviewboard and patchwork on the trackerdemo jail.
> > 
> > So what's the URL and where can we sign up?
> 
> note the "pieces" part of my mail :-) As far as I recall the patchworks 
> install somehow collided with the reviewboard one so it was disabled 
> because Zdenek was still actively using reviewboard.

I don't use it at this moment. You can disable reviewboard if you want.

Zdenek


-- 
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] using composite types in insert/update

[ITAGAKI Takahiro]

Merlin Moncure  wrote:

> however no such workaround exists for update. ideally,
> UPDATE foo SET foo = foo;

+1.
"UPDATE foo SET (*) = (foo.*)" would be another candidate of syntax.

I want to use this kind of queries to apply a changeset log
to another table. It is just like log application in Slony-I.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



-- 
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] mingw check hung

[Magnus Hagander]

Andrew Dunstan wrote:
> 
> 
> Magnus Hagander wrote:
>> Andrew Dunstan wrote:
>>  
>>> Magnus Hagander wrote:
>>>
> Specifically, it's the SetEnvironmentVariable() call from
> pgwin32_putenv() called from pgwin32_unsetenv(). When this is disabled
> things work just fine.
> 
 That's strange :( What arguments are it sent to the function? Since
 this
 is an API function, it really shouldn't behave differently between
 mingw
 and msvc, so it must be something that goes wrong with the arguments.

 Also, Tom mentioned earlier that we may be including *two* replacements
 for unsetenv(), which could be what's causing the problem. Can you
 check
 if that is happening and try to disable the one in port/unsetenv.c and
 see if that changes things?


 
>>> I've already ruled out that hypothesis by forcing the call direct to
>>> pgwin32_unsetenv() instead of relying on the macro, in initdb.c.
>>>
>>> There are only two such calls in initdb.c: the arguments are "LC_ALL"
>>> and "PGCLIENTENCODING".
>>>
>>> I wonder if this version of SetEnvironmentVariable is sufficiently dumb
>>> that it fails badly if given a NULL second argument for a value that is
>>> not in fact in the environment (as I would normally expect of these on
>>> Windows)?
>>> 
>>
>> But that should be a win32 API call. It's not a runtime call. So it
>> should be identical between mingw and msvc!
>>
>> Try removing the code that sets it to NULL if it's empty string. Having
>> it as empty string made it fail on MSVC, and the API documentation says
>> it should be NULL, but maybe mingw is somehow intercepting the call and
>> breaking it...
>>
>>
>>   
> 
> Mingw is just passing the call on.
> 
> You're right. When I comment out the NULL assignment, it all works.
> 
> MSDN says this ():
> 
>If the value parameter is not empty and the environment variable
>named by the variable parameter does not exist, the environment
>variable is created and assigned the contents of value. Solely for
>purposes of this operation, value is considered empty if it is a
>null reference (Nothing in Visual Basic), contains a zero-length
>string, or contains an initial hexadecimal zero character (0x00).
> 
>If variable contains a non-initial hexadecimal zero character, the
>characters before the zero character are considered the environment
>variable name and all subsequent characters are ignored.
> 
>If value contains a non-initial hexadecimal zero character, the
>characters before the zero character are assigned to the environment
>variable and all subsequent characters are ignored.
> 
>If value is empty and the environment variable named by variable
>exists, the environment variable is deleted. If variable does not
>exist, no error occurs even though the operation cannot be performed.
> 
> 
> So it looks like we could remove that NULL assignment happily and expect
> the right thing to be done.

I'm doing training all day today, but I can hopefully look at it this
weekend if you haven't already. However, I do recall *adding* that part
specifically for MSVC compatibility - I got a crash without it. Perhaps
we need to #ifdef it on mingw, but I'd like to understand *why*, since
it's just an API call...

Are we *sure*, btw, that this is actually a mingw issue, and not
something else in the environment? Could you try a MSVC compiled binary
on the same machine?

//Magnus

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