Re: [HACKERS] EXEC_BACKEND

On Tue, 2008-09-23 at 16:35 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> >
> > > > I can't find anything coherent in docs/readme/comments to explain why it
> > > > exists and what its implications are.
> > >
> > > It exists because Windows doesn't have fork(), only the equivalent of
> > > fork-and-exec. Which means that no state variables will be inherited
> > > from the postmaster by its child processes, and any state that needs to
> > > be carried across has to be handled explicitly. You can define
> > > EXEC_BACKEND in a non-Windows build, for the purpose of testing code
> > > to see if it works in that environment.
> >
> > OK, if its that simple then I see why its not documented. Thanks. I
> > thought there might be more to it than that.
>
> I added a little documentation at the top of
> postmaster.c::backend_forkexec().

Thanks.

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

[ADMIN] error

Hi, Folks,

What would cause this error?

SQL error:

ERROR:  could not access status of transaction 10274530

DETAIL:  Could not open file "pg_clog/0009": No such file or directory.

Carol

Re: [HACKERS] EXEC_BACKEND

Magnus Hagander wrote:
> Bruce Momjian wrote:
> > Simon Riggs wrote:
> >> On Tue, 2008-09-16 at 15:53 -0400, Tom Lane wrote:
> >>> Simon Riggs <simon@2ndQuadrant.com> writes:
> >>>> We keep talking about EXEC_BACKEND mode, though until recently I had
> >>>> misunderstood what that meant. I also realised that I have more than
> >>>> once neglected to take it into account when writing a patch - one recent
> >>>> patch failed to do this.
> >>>> I can't find anything coherent in docs/readme/comments to explain why it
> >>>> exists and what its implications are.
> >>> It exists because Windows doesn't have fork(), only the equivalent of
> >>> fork-and-exec. Which means that no state variables will be inherited
> >>> from the postmaster by its child processes, and any state that needs to
> >>> be carried across has to be handled explicitly. You can define
> >>> EXEC_BACKEND in a non-Windows build, for the purpose of testing code
> >>> to see if it works in that environment.
> >> OK, if its that simple then I see why its not documented. Thanks. I
> >> thought there might be more to it than that.
> >
> > I added a little documentation at the top of
> > postmaster.c::backend_forkexec().
>
> Doesn't that make more sense in say, the Developer FAQ?

I figured I should put it where it is used; the developer's FAQ is for
more generalized issues, I feel.

--
Bruce Momjian <bruce@momjian.us> 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] EXEC_BACKEND

Bruce Momjian wrote:
> Simon Riggs wrote:
>> On Tue, 2008-09-16 at 15:53 -0400, Tom Lane wrote:
>>> Simon Riggs <simon@2ndQuadrant.com> writes:
>>>> We keep talking about EXEC_BACKEND mode, though until recently I had
>>>> misunderstood what that meant. I also realised that I have more than
>>>> once neglected to take it into account when writing a patch - one recent
>>>> patch failed to do this.
>>>> I can't find anything coherent in docs/readme/comments to explain why it
>>>> exists and what its implications are.
>>> It exists because Windows doesn't have fork(), only the equivalent of
>>> fork-and-exec. Which means that no state variables will be inherited
>>> from the postmaster by its child processes, and any state that needs to
>>> be carried across has to be handled explicitly. You can define
>>> EXEC_BACKEND in a non-Windows build, for the purpose of testing code
>>> to see if it works in that environment.
>> OK, if its that simple then I see why its not documented. Thanks. I
>> thought there might be more to it than that.
>
> I added a little documentation at the top of
> postmaster.c::backend_forkexec().

Doesn't that make more sense in say, the Developer FAQ?

//Magnus

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

Re: [HACKERS] EXEC_BACKEND

Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.564
diff -c -c -r1.564 postmaster.c
*** src/backend/postmaster/postmaster.c 23 Sep 2008 09:20:36 -0000 1.564
--- src/backend/postmaster/postmaster.c 23 Sep 2008 20:33:14 -0000
***************
*** 3286,3291 ****
--- 3286,3295 ----
/*
* backend_forkexec -- fork/exec off a backend process
*
+ * Some operating systems (WIN32) don't have fork() so we have to simulate
+ * it by storing parameters that need to be passed to the child and
+ * then create a new child process.
+ *
* returns the pid of the fork/exec'd process, or -1 on failure
*/
static pid_t
Simon Riggs wrote:
>
> On Tue, 2008-09-16 at 15:53 -0400, Tom Lane wrote:
> > Simon Riggs <simon@2ndQuadrant.com> writes:
> > > We keep talking about EXEC_BACKEND mode, though until recently I had
> > > misunderstood what that meant. I also realised that I have more than
> > > once neglected to take it into account when writing a patch - one recent
> > > patch failed to do this.
> >
> > > I can't find anything coherent in docs/readme/comments to explain why it
> > > exists and what its implications are.
> >
> > It exists because Windows doesn't have fork(), only the equivalent of
> > fork-and-exec. Which means that no state variables will be inherited
> > from the postmaster by its child processes, and any state that needs to
> > be carried across has to be handled explicitly. You can define
> > EXEC_BACKEND in a non-Windows build, for the purpose of testing code
> > to see if it works in that environment.
>
> OK, if its that simple then I see why its not documented. Thanks. I
> thought there might be more to it than that.

I added a little documentation at the top of
postmaster.c::backend_forkexec().

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

[COMMITTERS] pgsql: Add comment about the use of EXEC_BACKEND.

Log Message:
-----------
Add comment about the use of EXEC_BACKEND.

Modified Files:
--------------
pgsql/src/backend/postmaster:
postmaster.c (r1.564 -> r1.565)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c?r1=1.564&r2=1.565)

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

Re: [JDBC] PreparedStatement.toString() creates valid SQL; was: Q: use setObject also for int or string

Kris Jurka schrieb:
>
>> Attached file contains all changed java sources to make your testcase
>> working. I added to ParameterList interface a method toSQLString(int).
>
> What is the reason for this instead of using the existing toString(int)
> method? If toSQLString is required, how can CompositeParameterList's
> toSQLString just call toString?

I used separate method cause I do not know of any side effects,
maybe toString(int) has to output exactly what it did before?
If not, toSQLString() can replace toString().
Then also the CompositeParameterList would be ok immediately.

I did not look into CompositeParameterList how it's toString works.
Now I've seen that it simply calls the SimpleParameterList.toString().
So if toString can be the new output, all is ok.

What's about the v2 Parameter? Should also output new style?

> Also, context diffs are appreciated instead of the whole file.

Noted.

Michael

--
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

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

Fwd: [pgsql-es-ayuda] Ayuda consulta SQL

---------- Forwarded message ----------
From: Cristián Osorio <cosorio.maldonado@gmail.com>
Date: 2008/9/23
Subject: Re: [pgsql-es-ayuda] Ayuda consulta SQL
To: "Miguel Beltran R." <yourpadre@gmail.com>

desde el primer registro encontrado, en este caso 2008/06/01 1:00:00 se buscan 15 mins, o sea hasta 2008/06/01 1:14:59, luego desde 2008/06/01 1:15:00 se buscan los registros hasta 2008/06/01 1:30:00, y asi sucesivamente.

2008/9/23 Miguel Beltran R. <yourpadre@gmail.com>

Antes de pensar en tu consulta tengo un duda
Si es en un intervalo de 15 minutos, tiene que ser lo que cumplan de
2008/06/01 1:00:00 a 2008/06/01 1:14:59 y su promedio.
o por cada registro checar los que esten dentro de los 15 minutos
siguentes, con tus datos seria,
tomando como inicio el de 1:05:00 incluiria el 1:10 y 1:15 y su promedio
luego tambien 1:10 incluiria 1:15 y 1:25 y su promedio

o cómo?

2008/9/22 Cristián Osorio <cosorio.maldonado@gmail.com>:

> Hola amigos, me encuentro algo aproblemado con una consulta en SQL
> tengo la siguiente tabla:
>
> timestamp                | valor
> -----------------------------------------
> 2008/06/01 1:05:00    | 10
> 2008/06/01 1:10:00    | 11
> 2008/06/01 1:15:00    | 12
> 2008/06/01 1:25:00    | 9
> 2008/06/01 1:35:00    | 10
> 2008/06/01 1:40:00    | 10
> 2008/06/01 1:45:00    | 13
> 2008/06/01 1:55:00    | 2
> 2008/06/01 2:05:00    | 10
> 2008/06/01 2:10:00    | 10
> 2008/06/01 2:15:00    | 1
> 2008/06/01 2:20:00    | 123
> 2008/06/01 2:25:00    | 10
> 2008/06/01 2:30:00    | 111
>
> Necesito hacer una consulta que debe calcular el promedio de los valores de
> acuerdo un intervalo de tiempo. Este intervalo puede ser de a 15, 30 o 60
> minutos, en realidad creo que lo que se necesita hacer es una funcion que me
> retorne un conjunto de resultados, pero no le pego mucho al pgsql. Si
> alguien puede ayudarme realizando la funcion u orientandome como hacerlo se
> lo agradecería, si alguien sabe alguna forma de hacerlo con SQL, con alguna
> instruccion especial de PostgreSql mucho mejor.
> De antemano, Gracias.
>
> --
> Saludos,
> Cristián Osorio M.
>

--
________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida

--
Saludos,
Cristián Osorio M.

--
Saludos,
Cristián Osorio M.

[COMMITTERS] pgsql: Mention battery-backed cache under hardware selection options.

Log Message:
-----------
Mention battery-backed cache under hardware selection options.

Modified Files:
--------------
pgsql/doc:
FAQ (r1.462 -> r1.463)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/FAQ?r1=1.462&r2=1.463)
pgsql/doc/src/FAQ:
FAQ.html (r1.419 -> r1.420)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/FAQ.html?r1=1.419&r2=1.420)

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

Re: [pgsql-es-ayuda] duda/problema con query y funcion

2008/9/23 Miguel Beltran R. <yourpadre@gmail.com>:
> No se si se pueda pero haz un explian analyze a ambas
Ok, aca esta.

>
> Y nos mandas el resultado
> por cierto que version de postgres usas?
>
Estoy ocupando la 8.2 de ubuntu 7.10 (postgresql-8.2 8.2.6-0ubuntu0).

Los explain analyze
Para la query:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=8.25..8.47 rows=29 width=23) (actual time=2.135..2.353
rows=12 loops=1)
-> Sort (cost=8.25..8.33 rows=29 width=23) (actual
time=2.126..2.203 rows=34 loops=1)
Sort Key: articulo.idarticulo, public.costo.costoultimo
-> Hash Join (cost=5.62..7.55 rows=29 width=23) (actual
time=1.322..1.677 rows=34 loops=1)
Hash Cond: ((public.costo.idarticulo)::text =
(articulo.idarticulo)::text)
-> Seq Scan on costo (cost=0.00..1.52 rows=29
width=14) (actual time=0.036..0.184 rows=34 loops=1)
Filter: ((fecha <= '2008-08-25'::date) AND (((NOT
flag) AND (idproveedor IS NULL)) OR flag OR (NOT flag)))
-> Hash (cost=5.45..5.45 rows=14 width=23) (actual
time=1.243..1.243 rows=14 loops=1)
-> Hash Left Join (cost=4.08..5.45 rows=14
width=23) (actual time=1.059..1.183 rows=14 loops=1)
Hash Cond: ((articulo.idarticulo)::text =
(public.costo.idarticulo)::text)
-> Seq Scan on articulo (cost=0.00..1.14
rows=14 width=15) (actual time=0.009..0.046 rows=14 loops=1)
-> Hash (cost=3.93..3.93 rows=12
width=22) (actual time=1.014..1.014 rows=12 loops=1)
-> Hash Join (cost=2.24..3.93
rows=12 width=22) (actual time=0.720..0.961 rows=12 loops=1)
Hash Cond:
(public.costo.idcosto = fechacosto.idcosto)
-> Seq Scan on costo
(cost=0.00..1.42 rows=42 width=26) (actual time=0.008..0.114 rows=42
loops=1)
-> Hash (cost=2.09..2.09
rows=12 width=4) (actual time=0.634..0.634 rows=12 loops=1)
-> Subquery Scan
fechacosto (cost=1.79..2.09 rows=12 width=4) (actual
time=0.492..0.590 rows=12 loops=1)
-> HashAggregate
(cost=1.79..1.97 rows=12 width=22) (actual time=0.485..0.524 rows=12
loops=1)
-> Seq Scan
on costo (cost=0.00..1.52 rows=35 width=22) (actual time=0.018..0.136
rows=34 loops=1)
Filter:
(fecha <= '2008-08-25'::date)
Total runtime: 2.755 ms
(21 filas)

y para la funcion:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Function Scan on buscandoError (cost=0.00..0.01 rows=1 width=4)
(actual time=110.452..110.455 rows=1 loops=1)
Total runtime: 110.526 ms
(2 filas)

Las diferencias de "runtime" de seguro que se debe al raise notice... o no?

Muchas gracias por la ayuda.
Saludos!
Roberto
--
visita mi weblog!
http://trasto.hopto.org
Softwarelibre@diinf
http://softwarelibre.diinf.usach.cl
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [JDBC] stored procedure returning result set.

On Tue, 23 Sep 2008, Gohil, Hemant wrote:

> I am trying to convert a working SQL query to stored function and get
> the resultset back.
>
> select * FROM sp_allCodes(1542) AS
> myresult(firstColumn,codeid,category,categoryid,CdLnk,codevalue,allcodes
> desc,codelink,maskfmt,sortseqnumber,adddate,changedate,addopid,changeopi
> d,allcodeslongdesc)
>
> ERROR: a column definition list is required for functions returning
> "record"

You need type information as well in the output list for record returning
functions.

> Basically I am using Sybase ASE presently and I am exploring the option
> to migrate to PostgreSQL, for Sybase I would just write
>
> Execute sp_allCodes 1542
>
> and it will return the result set with all the columns and rows. Is
> there a way to achieve similar functionality ?
>

To use "setof record" you must explicitly name the output colums in the
select. Other options are to create a new type to represent the output of
the function ("returning setof mytype") or to use output parameters. In
that case you can just say "select * from myfunc()".

http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resultset-setof

The final option is to return a refcursor which you can then turn into a
ResultSet. This is the most flexible as it doesn't require naming the
columns during function creation or function execution.

http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resultset-refcursor

Kris Jurka

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

Re: [HACKERS] PostgreSQL future ideas

Chris,

> This does not strike me as a particularly useful exercise. If I
> intended such a rewrite, I'd much rather consider using something
> *interestingly* different from C, like Erlang or Eiffel or Haskell.

And if you were going to do *that*, you'd also rewrite the database to
operate entirely in-memory over a cluster of anonymous servers.

At which point the only thing left of PostgreSQL would be the parser.
Hmmm, this is sounding familiar somehow ...

--
--Josh

Josh Berkus
PostgreSQL
San Francisco

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

Re: [pgsql-es-ayuda] Grupo local en españa: Parte II

Jordi Molina wrote:
> Bueno,
>
> en vista de que habia gente interesada en crear el grupo, pues será
> cuestión de empezar a hacer trabajo, más o menos, creo que tendriamos
> que
> - registrar algun dominio (pgsql.es, postgresql.es, se aceptan ideas!)
> - crear una lista de correo para que la gente interesada se apunte
> - crear una página web (habia pensado en drupal + postgre, más que
> nada porque ya hay los templates de otros sitios de pgsql)
> - mirar a ver si podemos organizar alguna actividad o colaborar con
> alguna que se organize en algún sitio cercano relacionada con el SW
> libre (o las bases de datos!)
>
>
> Este año creo que llegamos tarde para el pgsqlday que están
> organizando en Italia :(
>
>
No se, solo como recomendacion no seria mejor seguir trabajando como
hasta ahora, al fin y al cabo todos somos usuarios de postgresql
independientemente de donde vivamos y pienso que el mantener una lista
de distribucion por separado puede ser algo no tan beneficioso ya que
complica mas la busqueda de respuestas. Ahora por otro lado hay que
tener cuidado de no caer en el mismo error de postgresql.org.mx que fue
suceptible a ataques desde internet y los robots destrozaron el sitio
plagandolo de informacion publicitaria y demas.

--
Saludos,

Julio Cesar Sánchez González.

--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.

www.sistemasyconectividad.com.mx http://darkavngr.blogspot.com/

--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit

Devrim GÜNDÜZ wrote:
> On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote:
>> I'll look at doing that. We need the SUSE builds also.
>
> I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building
> Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires
> special attention from someone who is experienced in packaging (me :-) )
> and who is familiar with pg code..

Yeah, as you can see here

https://build.opensuse.org/project/show?project=server:database:postgresql

SLES builds have been broken for a while. I have not analyzed that yet.
Bugs and patches welcome.

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

Re: [pgsql-es-ayuda] duda/problema con query y funcion

No se si se pueda pero haz un explian analyze a ambas

Asi:
EXPLAIN ANALYZE SELECT articulo.idarticulo, costoultimo.costoultimo
FROM articulo
LEFT OUTER JOIN costo USING (idarticulo)
LEFT OUTER JOIN (select costo.idarticulo, costoultimo from
costo,(select max(idcosto) as idcosto,idarticulo,max (fecha) from costo
where fecha<='25-08-2008' group by idarticulo) as fechacosto where
fechacosto.idcosto = costo.idcosto) as costoultimo USING (idarticulo)
where
(costo.flag = false and costo.fecha<='25-08-2008' and costo.idproveedor
is NULL) or (costo.flag=true and costo.fecha<='25-08-2008') or
(costo.flag = false and costo.fecha<='25-08-2008')
group by articulo.idarticulo, costoultimo.costoultimo
order by idarticulo;

y despues un

EXPLAIN ANALYZE SELECT * FROM buscandoError('25-08-2008');

Y nos mandas el resultado
por cierto que version de postgres usas?

El día 23 de septiembre de 2008 14:28, Roberto Rodríguez Pino
<rodpin@gmail.com> escribió:
> 2008/9/23 Miguel Beltran R. <yourpadre@gmail.com>:
>> Se me ocurre que es algo con las fechas, que no te las interprete
>> igual y por lo tanto no busque en el mismo rango.
>> Cómo pasas la fecha a la funcion? Trata probando mandadola como
>> YYYY-MM-AA. Pruebalo en ambas partes, consulta directa y funcion.
>> Tambien checa en tu postgresql.conf que tengas puesto que pueda
>> interpretar la fecha es ese formato. Yo lo tengo datestyle = 'iso,
>> DMY'
>>
> Hola Miguel, muchas gracias por tu respuesta pero no resultó. Probé
> poniendo la fecha de varias formas y al parecer no es eso. Probé tanto
> en la query y la función.
> Tenia la misma configuración para datastyle, aunque con minsuculas. Lo
> puse en mayúsculas (aunque no debe influir mucho) pero tampoco hubo
> cambios.
> ¿Más pistas?
> Muy agradecido por más comentario.
> Saludos!
> Roberto
>
> --
> visita mi weblog!
> http://trasto.hopto.org
> Softwarelibre@diinf
> http://softwarelibre.diinf.usach.cl
>

--
________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [HACKERS] parallel pg_restore

On Tue, 2008-09-23 at 12:43 -0700, Joshua Drake wrote:
> On Tue, 23 Sep 2008 08:44:19 +0100
> Simon Riggs <simon@2ndQuadrant.com> wrote:
>
> >
> > On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote:
> >
> > > j and m happen to be two of those that are available.
> > >
> > > I honestly don't have a terribly strong opinion about what it
> > > should be called. I can live with jobs or multi-threads.
> >
> > Perhaps we can use -j for jobs and -m for memory, so we can set memory
> > available across all threads with a single total value.
> >
> > I can live with jobs or multi-threads also, whichever we decide.
> > Neither one is confusing to explain.
> >
>
> Memory? Where did that come from. Andrew is that in your spec?

No, but it's in mine. As I said upthread, no point in making it more
parallel than memory allows. Different operations need more/less memory
than others, so we must think about that also. We can quickly work out
how big a table is, so we can work out how much memory it will need to
perform sorts for index builds and thus how many parallel builds can
sensibly take place.

--
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: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)

Added to TODO under features not wanted:

Incomplete itemObfuscated function source code (not wanted)

Obfuscating function source code has minimal protective benefits
because anyone with super-user access can find a way to view the code.
To prevent non-super-users from viewing function source code, remove
SELECT permission on pg_proc.

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

Merlin Moncure wrote:
> On Tue, Sep 16, 2008 at 9:15 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> >
> > As much as I'm impressed with the "we do it properly or not at all" attitude, it'd be nice if there was an option to stop the casual user from viewing code.
> >
> > I'll admit to obfusicating bits and pieces using C, even though the function and everything it acts on are tied down with permissions. I understand in reality it provides no real extra security but somehow users being able to easily view something they don't have access to execute beyond it's name just feels wrong.
>
> This is one of those threads that reappears like magic every six
> months or so. The last round of discussion went longer than normal
> including a couple of routes to implementation.
>
> One big reason why nothing hasn't been done is that there is a decent
> 'low tech' obfuscation tactic already: remove select access from
> pg_proc to the user accounts in question and 'public'. This will
> essentially disable casual browsing of procedure code from user
> accounts.
>
> Any real solution should focus on:
> *) key management (any serious discussion with encryption starts here)
> *) other things you can do with function source besides encryption
>
> for example, take a look at one idea I had (not at all vetted, but a start):
> http://archives.postgresql.org/pgsql-performance/2007-12/msg00337.php
>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit

Dave Page wrote:
> 2008/9/23 Peter Eisentraut <peter_e@gmx.net>:
>> Gerd König wrote:
>>> a few months ago we started using Postgres on Opensuse10.3-64bit.
>>> We installed Postgres 8.3.1 with the (at that time) latest available
>>> rpm's.
>>> But now Postgres' current version is 8.3.4 and I'm wondering why there
>>> are no new rpm's for Opensuse ?!?!
>> The answer is quite simply that no one is volunteering to do the work.
>
> Oh, I thought you were looking after that build. If it's not being
> maintained, we'll need to remove it from the download pages unless
> someone else can volunteer?

I am looking after it, but I cannot guarantee real-time releases with
the resources available to me. Certainly, one or two more people
joining the looking after would be welcome. I did some work recently to
put all the pieces in place, so at this time we basically only need to
replace the tarballs and release once in a while.

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

Re: [HACKERS] parallel pg_restore

On Tue, 23 Sep 2008 08:44:19 +0100
Simon Riggs <simon@2ndQuadrant.com> wrote:

>
> On Mon, 2008-09-22 at 15:05 -0400, Andrew Dunstan wrote:
>
> > j and m happen to be two of those that are available.
> >
> > I honestly don't have a terribly strong opinion about what it
> > should be called. I can live with jobs or multi-threads.
>
> Perhaps we can use -j for jobs and -m for memory, so we can set memory
> available across all threads with a single total value.
>
> I can live with jobs or multi-threads also, whichever we decide.
> Neither one is confusing to explain.
>

Memory? Where did that come from. Andrew is that in your spec?

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/

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

On Tue, 23 Sep 2008 09:14:33 +0200
"Stephen R. van den Berg" <srb@cuci.nl> wrote:

> Joshua D. Drake wrote:
> >Andrew Dunstan wrote:
> >>There are in fact very few letters available, as we've been fairly
> >>profligate in our use of option letters in the pg_dump suite.
>
> >>j and m happen to be two of those that are available.
>
> >--max-workers
>
> Perhaps, but please do not use that as justification for using -m.
> That would be equally silly as abbreviating "number of workers" to -n.

Actually I came up with it because it coincides with existing
terminology. Autovacuum has the concept of max_workers.

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/

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

Re: [BUGS] BUG #4435: pg_ctl start doesn't detach when run via SSH

That works, but interestingly, I still get a SIGHUP reload sometimes:

LOG:  received SIGHUP, reloading configuration files

On Tue, Sep 23, 2008 at 1:54 PM, Steve Clark <sclark@netwolves.com> wrote:

Hal Black wrote:

The following bug has been logged online:

Bug reference:      4435
Logged by:          Hal Black
Email address:      black@ieee.org
PostgreSQL version: 8.3.3
Operating system:   Ubuntu Hardy 8.04.1 LTS
Description:        pg_ctl start doesn't detach when run via SSH
Details:
When I run "pg_ctl start" via SSH, the database service starts, but it never
returns control to the shell.  If I run the same command from an interactive
shell, it works as expected.

This is with or without the -w option.  Here's an example commandline.

ssh root@example.com "su -c '/home/appuser/local/bin/pg_ctl start -w -s -D
/home/appuser/postgresql_data' postgres"

This problem exists both when installed from source and when using the
postgresql installed via the debian package manager (of course using a
different path to the pg_ctl binary)

try using ssh -t ...

Re: [DOCS] Russian FAQ page charset problem

Bruce Momjian wrote:
> Dave Page wrote:
>> On Tue, Sep 23, 2008 at 7:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>> Dave Page wrote:
>>>> On Mon, Sep 15, 2008 at 8:37 AM, Nikolay Samokhvalov
>>>> <samokhvalov@gmail.com> wrote:
>>>>> On Mon, Sep 15, 2008 at 11:30 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>>>>>> This was already discussed on the pgsql-www list recently, but there has
>>>>>> been no resolution on what to do yet.
>>>>> What about using UTF8 for Russian text? Why not simply "iconv -f
>>>>> koi8-r -t utf8 ..."?
>>>> I thought that was what Bruce was going to do. He has other priorities
>>>> at the moment though, so perhaps one of the the committers could
>>>> handle it.
>>> No, the conversion to a UTF8 header is done by the web infrastructure;
>>> the file in CVS is in the native Russian encoding, koi8-r, and I don't
>>> know how to change that.
>> That's the iconv -f bit suggested above, that I thought you were doing.
>
> Nope, never did that, and I don't have the proper locales on my sever.

That's not locale, that's encoding. And it's independent of what locales
you have - AFAIK as long as you have iconv, you should be good.

For next time, that is - since it's already fixed this time :-)

//Magnus

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

Re: [pgsql-es-ayuda] duda/problema con query y funcion

2008/9/23 Miguel Beltran R. <yourpadre@gmail.com>:
> Se me ocurre que es algo con las fechas, que no te las interprete
> igual y por lo tanto no busque en el mismo rango.
> Cómo pasas la fecha a la funcion? Trata probando mandadola como
> YYYY-MM-AA. Pruebalo en ambas partes, consulta directa y funcion.
> Tambien checa en tu postgresql.conf que tengas puesto que pueda
> interpretar la fecha es ese formato. Yo lo tengo datestyle = 'iso,
> DMY'
>
Hola Miguel, muchas gracias por tu respuesta pero no resultó. Probé
poniendo la fecha de varias formas y al parecer no es eso. Probé tanto
en la query y la función.
Tenia la misma configuración para datastyle, aunque con minsuculas. Lo
puse en mayúsculas (aunque no debe influir mucho) pero tampoco hubo
cambios.
¿Más pistas?
Muy agradecido por más comentario.
Saludos!
Roberto

--
visita mi weblog!
http://trasto.hopto.org
Softwarelibre@diinf
http://softwarelibre.diinf.usach.cl
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [pgsql-es-ayuda] Ayuda consulta SQL

Antes de pensar en tu consulta tengo un duda
Si es en un intervalo de 15 minutos, tiene que ser lo que cumplan de
2008/06/01 1:00:00 a 2008/06/01 1:14:59 y su promedio.
o por cada registro checar los que esten dentro de los 15 minutos
siguentes, con tus datos seria,
tomando como inicio el de 1:05:00 incluiria el 1:10 y 1:15 y su promedio
luego tambien 1:10 incluiria 1:15 y 1:25 y su promedio

o cómo?

2008/9/22 Cristián Osorio <cosorio.maldonado@gmail.com>:
> Hola amigos, me encuentro algo aproblemado con una consulta en SQL
> tengo la siguiente tabla:
>
> timestamp | valor
> -----------------------------------------
> 2008/06/01 1:05:00 | 10
> 2008/06/01 1:10:00 | 11
> 2008/06/01 1:15:00 | 12
> 2008/06/01 1:25:00 | 9
> 2008/06/01 1:35:00 | 10
> 2008/06/01 1:40:00 | 10
> 2008/06/01 1:45:00 | 13
> 2008/06/01 1:55:00 | 2
> 2008/06/01 2:05:00 | 10
> 2008/06/01 2:10:00 | 10
> 2008/06/01 2:15:00 | 1
> 2008/06/01 2:20:00 | 123
> 2008/06/01 2:25:00 | 10
> 2008/06/01 2:30:00 | 111
>
> Necesito hacer una consulta que debe calcular el promedio de los valores de
> acuerdo un intervalo de tiempo. Este intervalo puede ser de a 15, 30 o 60
> minutos, en realidad creo que lo que se necesita hacer es una funcion que me
> retorne un conjunto de resultados, pero no le pego mucho al pgsql. Si
> alguien puede ayudarme realizando la funcion u orientandome como hacerlo se
> lo agradecería, si alguien sabe alguna forma de hacerlo con SQL, con alguna
> instruccion especial de PostgreSql mucho mejor.
> De antemano, Gracias.
>
> --
> Saludos,
> Cristián Osorio M.
>

--
________________________________________
Lo bueno de vivir un dia mas
es saber que nos queda un dia menos de vida
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit

On Tue, 2008-09-23 at 21:05 +0300, Devrim GÜNDÜZ wrote:
> Hi,
>
> On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote:
> > I'll look at doing that. We need the SUSE builds also.
>
> I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building
> Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires
> special attention from someone who is experienced in packaging (me :-) )
> and who is familiar with pg code..
>
> I fixed spec file. The only remaining issue is libpgport.a. If someone
> who is familiar to PG code can comment on how this file is built, I can
> push that spec file to my repo, so that Simon or others can continue
> maintaining it. That file is built on Fedora/RHEL, but I'm not sure why
> SLES skips it. I need that file for Slony-I on SLES,since AFAICS Slony
> version is 1.2.6 on SLES :-(
>
> I don't have access to that SLES server. I'll ask my friend to post the
> spec to me, and I'll let you know about the status.

Having looked at where we are now, it's going to take more than a little
work to get everything in order. Peter had a good go at it, but that was
a few releases ago.

I've been wanting to make SUSE releases better for a few years now, so
am happy to take this on long term. It's not a snap decision since I
build on OpenSUSE daily, but from source, not RPMs.

As you say, you're in a better position to sort out a stopgap in the
short term and I can then take on the task after feature freeze when
I'll have more time. In any case, we'll need to make them as consistent
as possible between different Linuxes so I'll be relying on your
packaging experience.

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

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

Re: [HACKERS] PostgreSQL future ideas

Gevik Babakhani wrote:
> Has there been any idea to port PG to a more modern programming language
> like C++? Of course there are some minor obstacles like a new OO design,
> this being a gigantic task to perform and rewriting almost everything etc...
> I am very interested to hear your opinion.

Gevik, of course you're free to fork the project and try this yourself.

I'd caution you that neither OO nor C++ are particularly "modern"
(Stroustrup's objects-on-C work dates back to the 1970's). And that
of the OO languages, C++ is one of the worst in terms of OO capabilities.

If your theory favoring a "modern" language is thinking that this'll
give you efficiencies (either in development time or runtime), you
might consider Erlang instead. It's Functional and Concurrency and
Fault Tolerance oriented features would IMHO be more useful for large
reliable servers than anything C++ has to offer.

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

[JDBC] stored procedure returning result set.

Hi,

 

I am trying to convert a working SQL query to stored function and get the resultset back.

 

Here is my actual function

==============================================================

CREATE FUNCTION sp_allCodes(integer) RETURNS SETOF record AS $$

 

DECLARE

    acodes RECORD;

BEGIN

FOR acodes in

SELECT

            case

                   when $1  = codeId then '******'

                   else ''

             end AS "firstColumn" ,

             allCodes.codeId,

             category,

             allCodes.categoryId,

             SUBSTRING(dataValue, 1, 8) AS "CdLnk",

             allCodes.codeValue,

             allCodes.allCodesDesc,

             allCodes.codeLink,

             allCodes.maskfmt,

             allCodes.sortSeqNumber,

             allCodes.addDate,

             allCodes.changeDate,

             allCodes.addOpId,

             allCodes.changeOpId,

             allCodes.allCodesLongDesc

FROM  allCodes  INNER JOIN  category  ON  allCodes.categoryId  = category.categoryId   LEFT OUTER JOIN  codeLink  ON  codeId  = codeLinkId 

WHERE allCodes.categoryId  in

      (

      SELECT categoryId

      FROM  allCodes

      WHERE codeId  = $1

      )

ORDER BY codeId LOOP

   return next acodes;

END LOOP;

END;

$$ LANGUAGE plpgsql;

GO

================================================================

 

Here is how I am calling it,

==========

select * FROM sp_allCodes(1542) AS myresult(firstColumn,codeid,category,categoryid,CdLnk,codevalue,allcodesdesc,codelink,maskfmt,sortseqnumber,adddate,changedate,addopid,changeopid,allcodeslongdesc)

============

 

And getting following error -

 

ERROR: a column definition list is required for functions returning "record"

 

I have also tried calling using

select * FROM sp_allCodes(1542)

 

Which gave the same error.

 

============================================================================================

I am using Aqua Data Studio as my SQL client.

 

Basically I am using Sybase ASE presently and I am exploring the option to migrate to PostgreSQL, for Sybase I would just write

 

Execute sp_allCodes 1542

 

and it will return the result set with all the columns and rows. Is there a way to achieve similar functionality ?

 

We have lots of stored procedure and it would be extremely cumbersome to list ALL the columns getting returned from the function/stored procedure. Apart from that we had to modify our Java code accessing the data as well and XML files containing the SQL and that would make it proprietary for PostgreSQL, I would prefer to keep them as portable as possible.

 

I would appreciate any suggestions, links etc in this regards.

 

Thanks in advance

Hemant

Re: [PERFORM] Choosing a filesystem

On Tue, 2008-09-23 at 13:02 -0400, Bruce Momjian wrote:
> Merlin Moncure wrote:
> > > although for postgres the thing that you are doing the fsync on is the WAL
> > > log file. that is a single (usually) contiguous file. As such it is very
> > > efficiant to write large chunks of it. so while you will degrade from the
> > > battery-only mode, the fact that the controller can flush many requests
> > > worth of writes out to the WAL log at once while you fill the cache with
> > > them one at a time is still a significant win.
> >
> > The heap files have to be synced as well during checkpoints, etc.
>
> True, but as of 8.3 those checkpoint fsyncs are spread over the interval
> between checkpoints.

No, the fsyncs still all happen in a tight window after we have issued
the writes. There's no waits in between them at all. The delays we
introduced are all in the write phase. Whether that is important or not
depends upon OS parameter settings.

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

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

Re: [ADMIN] Fat16/32 linking problems

On Tue, 23 Sep 2008 16:07:45 +0200
Jonny Rabovsky <jonny@rabovsky.de> wrote:

> Hi,
> I have a little trouble with running Postgress on a embedded linux
> with only Fat availible.
> it runs for hours, but the it crashes and I cannot start it again.
> Any Ideas ?

As I recall, NTFS is required for Win32.

Joshua D. Drake

>
> LOG: database system was shut down at 2008-09-23 15:32:05 UTC
> LOG: checkpoint record is at 0/C4643C
> LOG: redo record is at 0/C4643C; undo record is at 0/0; shutdown TRUE
> LOG: next transaction ID: 2371; next OID: 25547
> PANIC: could not link file "/home/es4/pgsql/pg_xlog/xlogtemp.3438"
> to "/home/es4/pgsql/pg_xlog/000000010000000000000001" (initialization
> of log file 0, segment 1): Operation not permitted
> LOG: startup process (PID 3438) was terminated by signal 6
> LOG: aborting startup due to startup process failure
>
>
>
> Jonny
>

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/

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

[HACKERS] Hot Standby Design

Hot Standby design has been growing on the PostgreSQL Wiki for some
weeks now.

I've updated the design to reflect all feedback received so far on
various topics.

http://wiki.postgresql.org/wiki/Hot_Standby

It's not hugely detailed in every area, but it gives a flavour of the
topics and issues related to them.

Comments or questions welcome here, or I will discuss specific areas in
more detail as I tackle those topics.

--
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: [SQL] Special grouping on sorted data.

I which distinct on ...
I wish distinct on ...

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

[pgsql-www] Bad link for win32/latest

Folks,

http://www.postgresql.org/ftp/binary/latest, which should point to
8.3.4, is still pointing to 8.3.3.

--Josh

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

Re: [DOCS] Russian FAQ page charset problem

Dave Page wrote:
> On Tue, Sep 23, 2008 at 7:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Dave Page wrote:
> >> On Mon, Sep 15, 2008 at 8:37 AM, Nikolay Samokhvalov
> >> <samokhvalov@gmail.com> wrote:
> >> > On Mon, Sep 15, 2008 at 11:30 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> >> >> This was already discussed on the pgsql-www list recently, but there has
> >> >> been no resolution on what to do yet.
> >> >
> >> > What about using UTF8 for Russian text? Why not simply "iconv -f
> >> > koi8-r -t utf8 ..."?
> >>
> >> I thought that was what Bruce was going to do. He has other priorities
> >> at the moment though, so perhaps one of the the committers could
> >> handle it.
> >
> > No, the conversion to a UTF8 header is done by the web infrastructure;
> > the file in CVS is in the native Russian encoding, koi8-r, and I don't
> > know how to change that.
>
> That's the iconv -f bit suggested above, that I thought you were doing.

Nope, never did that, and I don't have the proper locales on my sever.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit

Hi,

On Tue, 2008-09-23 at 09:49 +0100, Simon Riggs wrote:
> I'll look at doing that. We need the SUSE builds also.

I actually built 8.3.4 on SLES 10.2 on..err..Friday, while building
Fedora/RH RPMs. 8.3.1 spec of SLES is broken IMHO, and it requires
special attention from someone who is experienced in packaging (me :-) )
and who is familiar with pg code..

I fixed spec file. The only remaining issue is libpgport.a. If someone
who is familiar to PG code can comment on how this file is built, I can
push that spec file to my repo, so that Simon or others can continue
maintaining it. That file is built on Fedora/RHEL, but I'm not sure why
SLES skips it. I need that file for Slony-I on SLES,since AFAICS Slony
version is 1.2.6 on SLES :-(

I don't have access to that SLES server. I'll ask my friend to post the
spec to me, and I'll let you know about the status.

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

Re: [DOCS] Russian FAQ page charset problem

On Tue, Sep 23, 2008 at 7:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Dave Page wrote:
>> On Mon, Sep 15, 2008 at 8:37 AM, Nikolay Samokhvalov
>> <samokhvalov@gmail.com> wrote:
>> > On Mon, Sep 15, 2008 at 11:30 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> >> This was already discussed on the pgsql-www list recently, but there has
>> >> been no resolution on what to do yet.
>> >
>> > What about using UTF8 for Russian text? Why not simply "iconv -f
>> > koi8-r -t utf8 ..."?
>>
>> I thought that was what Bruce was going to do. He has other priorities
>> at the moment though, so perhaps one of the the committers could
>> handle it.
>
> No, the conversion to a UTF8 header is done by the web infrastructure;
> the file in CVS is in the native Russian encoding, koi8-r, and I don't
> know how to change that.

That's the iconv -f bit suggested above, that I thought you were doing.

> However, it now seems all the FAQs are in ASCII or UTF8 so I think we
> are OK going into the future.

Yup, Magnus converted them.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

[HACKERS] 8.3 .4 + Vista + MingW + initdb = ACCESS_DENIED

I'm trying to upgrade my copy of postgresql from 8.2.x to 8.3.4 on a
Windows Vista SP1 laptop. I build postgres using mingw/msys and have had
no issues with 8.1.x and 8.2.x. However, with 8.3.4 I run into problems.

First, building fails:

c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/sspi.h:60:

error: syntax error before "SECURITY_STRING"
In file included from
c:/Development/MingW/bin/../lib/gcc/mingw32/3.4.5/../../../../include/security.h:39,
from ../../../../src/include/libpq/libpq-be.h:50,

This also happens from libpq-int.h. The solution in both cases is to
add an additional header file:

#ifdef ENABLE_SSPI
#define SECURITY_WIN32
#include <ntsecapi.h> <------- Add this include
#include <security.h>
#undef SECURITY_WIN32

That fixes the build issue.

Second, once I've successfully built and installed postgres, I run into
a bigger problem. When using initdb, I get this error:

creating template1 database in c:/Data/postgres30/base/1 ... FATAL:
could not create shared memory segment: 5
DETAIL: Failed system call was CreateFileMapping(size=1802240,
name=Global\PostgreSQL:c:/Data/postgres).

A bit of googling and reading MSDN docs shows that applications that
don't run in Session 0 on Vista are not allowed to create shared memory
in the Global namespace. Since initdb is invoked from the command line,
it runs in Session 1.

To get around this, you can give the user running and application the
"Create Global objects" right using the Local Security Policy. Needless
to say I did that without any luck.

I then installed the pre-built binaries for Vista using the official
windows installer. Calling initdb in the same way with the same user works.

With 8.3.x the installer uses binaries built with VC 2005 instead of
mingw - so clearly there are lots of differences. But I'm wondering if
there is some difference in the way security is setup - maybe the
addition of a manifest file to initdb that allows it to create global
shared memory? I also assume it has to do with the way DACLs are setup,
as described in this thread:

http://archives.postgresql.org/pgsql-patches/2008-02/msg00074.php

Or maybe its the way the executables are installed - I see that the
installer makes SYSTEM their owner which of course doesn't happen with
make install on MingW/msys.

So I'm stumped - the same user running initdb built with VC++ works but
running initdb with MingW fails. Any help much appreciated...

Thanks,

Charlie

--
Charlie Savage
http://cfis.savagexi.com

Re: [HACKERS] PostgreSQL future ideas

pgdev@xs4all.nl ("Gevik Babakhani") writes:
> It might look like an impossible goal to achieve.. But if there is
> any serious plan/idea/ammo for this, I believe it would be very
> beneficial to the continuity of PG.

Actually, I imagine that such a rewrite would run a very considerable
risk of injuring the continuity of PostgreSQL VERY BADLY, to the point
of causing community fractures and forks of the codebase.

When you write something in C++, you have to pick a subset of the
language that is supported fairly identically (in semantics) by all of
the compilers that you wish to support.

Seeing as how PostgreSQL is already a mature system written in C, a
rewrite into C++, *which is a different language* that is NOT simply a
superset of C functionality, would require substantial effort, lead to
fractious disagreements, and would, without ANY doubt, fracture the
code base into *AT LEAST* two versions, namely:

a) The existing C code base, and
b) One (possibly more) C++ rewrites

This does not strike me as a particularly useful exercise. If I
intended such a rewrite, I'd much rather consider using something
*interestingly* different from C, like Erlang or Eiffel or Haskell.
--
"cbbrowne","@","linuxdatabases.info"
http://linuxfinances.info/info/sgml.html
For a good prime call:
391581 * 2^216193 - 1
-- smr2@cornell.edu (Szymon Rusinkiewicz)

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

Re: [pgsql-es-ayuda] Hay informacion del API C...

2008/9/23 Manu <manuexposito@gmail.com>:
> Por favor no pasar de la consulta ya que estoy muy interesado...
>
> Al menos saber si hay algún sitio donde conseguir ejemplos del API en C
>
> Muchas gracias
>

Creo que buscas esto http://www.postgresql.org/docs/8.3/interactive/libpq.html

--
"Linux is for people who hate Windows, BSD is for people who love UNIX".
"Social Engineer -> Because there is no patch for human stupidity"
"The Unix Guru's View of Sex unzip ; strip ; touch ; grep ; finger ;
mount ; fsck ; more ; yes ; umount ; sleep."
"Documentation is like sex: when it is good, it is very, very good;
and when it is bad, it is better than nothing."
--
TIP 7: no olvides aumentar la configuración del "free space map"

Re: [DOCS] Russian FAQ page charset problem

Dave Page wrote:
> On Mon, Sep 15, 2008 at 8:37 AM, Nikolay Samokhvalov
> <samokhvalov@gmail.com> wrote:
> > On Mon, Sep 15, 2008 at 11:30 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> >> This was already discussed on the pgsql-www list recently, but there has
> >> been no resolution on what to do yet.
> >
> > What about using UTF8 for Russian text? Why not simply "iconv -f
> > koi8-r -t utf8 ..."?
>
> I thought that was what Bruce was going to do. He has other priorities
> at the moment though, so perhaps one of the the committers could
> handle it.

No, the conversion to a UTF8 header is done by the web infrastructure;
the file in CVS is in the native Russian encoding, koi8-r, and I don't
know how to change that.

However, it now seems all the FAQs are in ASCII or UTF8 so I think we
are OK going into the future.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

Re: [GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints

On Tuesday 23 September 2008, William Garrison <postgres@mobydisk.com>
wrote:
> 1) other workarounds
> 2) someone else who can confirm that this bug is either fixed, or not
> fixed. If it is supposedly fixed, then I guess I need to make a smaller
> version of my database to demonstrate the problem.

AFAIK, the dumps created by pg_dump create all the constraints after the
table data is all loaded - there are no foreign keys in place when the data
is restored, so conflicts are not possible.

You might need to elaborate on how you're restoring this database.

--
Alan

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

Re: [SQL] Special grouping on sorted data.

Craig Ringer wrote:
>>
>> b | n | stamp
>> ----------------------------------------
>> A | 1 | 2008-09-20 06:07:47.981445 [1]
>> A | 1 | 2008-09-20 06:08:13.294306 [1]
>> A | 1 | 2008-09-20 06:12:02.046596 [1]
>> A | 2 | 2008-09-20 06:12:26.267786 [2]
>> A | 2 | 2008-09-20 06:12:47.750429 [2]
>> A | 1 | 2008-09-20 06:13:12.152512 [3]
>> A | 2 | 2008-09-20 06:13:39.052528 [4]
>> A | 2 | 2008-09-20 06:14:12.875389 [4]
>>
>
> I'd be tempted to use a set-returning PL/PgSQL function to process an
> input set ordered by stamp and return a result whenever the (b,n) pair
> changed. I'm sure there's a cleverer set-oriented approach, but it's
> eluding me at present.
>
> You need a way to express the notion of "contiguous runs of (b,n)"
> which doesn't really exist in (set-oriented) SQL.

The numbers you have next to each row is exactly what I'm looking for.
You mention PL/PgSQL, I'm familiar with creating triggered procedures so
I'll look into that

> I suspect that Crystal Reports may be pulling the whole data set from
> PostgreSQL then doing its processing client-side.
Crystal report is running a simple pass through query that I wrote,
select b.n.stamp from table where stamp .... order by stamp
Then I use its grouping features, I group by b, then n but when I group
by n I don't specify ascending or descending order but "in original order"
And it ends up doing what I'm looking for.

I which distinct on was more flexible, it's not happy when the order by
set is different than the distinct on set.

I would like to be able to write select distinct on (b,n) b,n,stamp from
table where ... order by stamp;

Nicolas

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

Re: [BUGS] BUG #4435: pg_ctl start doesn't detach when run via SSH

Hal Black wrote:
> The following bug has been logged online:
>
> Bug reference: 4435
> Logged by: Hal Black
> Email address: black@ieee.org
> PostgreSQL version: 8.3.3
> Operating system: Ubuntu Hardy 8.04.1 LTS
> Description: pg_ctl start doesn't detach when run via SSH
> Details:
>
> When I run "pg_ctl start" via SSH, the database service starts, but it never
> returns control to the shell. If I run the same command from an interactive
> shell, it works as expected.
>
> This is with or without the -w option. Here's an example commandline.
>
> ssh root@example.com "su -c '/home/appuser/local/bin/pg_ctl start -w -s -D
> /home/appuser/postgresql_data' postgres"
>
> This problem exists both when installed from source and when using the
> postgresql installed via the debian package manager (of course using a
> different path to the pg_ctl binary)
>
try using ssh -t ...

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

Re: [pgsql-advocacy] Binaries vs Source

On Mon, 2008-09-22 at 20:52 -0500, Jaime Casanova wrote:
>
> OTOH, if you install from sources you can install patches as soon as
> they are committed... then you can always have your installation at
> the most recent minor version... and of course more protected from
> bugs.

Heh :) So you assume that the patches are well-tested before committing,
and they have no bugs?

...also what you wrote can be done easily with the binary packages.
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)

KaiGai Kohei wrote:
> > [1] Make a consensus that different security mechanisms have differences
> > in its decision making, its gulanuality and its scope
> >
> > I think it is the most straightforward answer.
> > As operating system doing, DAC and MAC based access controls should be
> > independently applied on accesses from users, and this model is widely
> > accepted.
> > These facilities can also have different results, gulanualities and scopes.
> >
> >
> > [2] Make a new implementation of OS-independent fine grained access control
> >
> > If it is really really necessary, I may try to implement a new separated
> > fine-grained access control mechanism due to the CommitFest:Nov.
> > However, we don't have enough days to develop one more new feature from
> > the scratch by the deadline.
>
> I reconsidered the above two options have no differences fundamentally.
>
> In other word, making a new enhanced security implementation based on
> requirements also means making a consensus various security mechanism
> can have its individual rules including guranuality of access controls.
>
> So, I'll decide to try to implement "fine-grained-only" security
> mechanism also, because someone have such a requirememt.
> However, its schedule is extremely severe, if is has to be submitted
> due to the deadline of CommitFest:Nov.
>
> It is my hope to concentrate development of SE-PostgreSQL in v8.4
> development cycle, and I think the above "fine-grained-only" one
> should be pushed to v8.5 cycle.

Well, those might be your priorities, but I don't think they are the
community's priorities.

I think the community's priorities are to add security at the SQL
level, and then we can see clearly what SE-PostgreSQL requires. This
has been discussed before so it should not come as a surprise.

What you can do is to do things in this order:

1) Add SE-PostgreSQL capabilities that layer over existing Postgres
SQL-level permissions
2) Implement "fine-grained" permissions at the SQL level
3) Add SE-PostgreSQL capabilities for "fine-grained" permissions

Perhaps you can only get #1 done for 8.4; I don't know, but I knew
months ago that #2 had to be done before #3, and I think that was
communicated.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] Proposal of SE-PostgreSQL patches (for CommitFest:Sep)

Robert Haas wrote:
> > It's too early to vote. :-)
> >
> > The second and third option have prerequisite.
> > The purpose of them is to match granularity of access controls
> > provided by SE-PostgreSQL and native PostgreSQL. However, I have
> > not seen a clear reason why these different security mechanisms
> > have to have same granuality in access controls.
>
> Have you seen a clear reason why they should NOT have the same granularity?

Agreed. If we implement SE-PostgreSQL row-level security first, we
might find that we have to replace the code once we implement SQL-level
row-level security. If we do SQL-level security first, we can then
adjust it to match what SE-PostgreSQL needs.

--
Bruce Momjian <bruce@momjian.us> 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

[BUGS] BUG #4435: pg_ctl start doesn't detach when run via SSH

The following bug has been logged online:

Bug reference: 4435
Logged by: Hal Black
Email address: black@ieee.org
PostgreSQL version: 8.3.3
Operating system: Ubuntu Hardy 8.04.1 LTS
Description: pg_ctl start doesn't detach when run via SSH
Details:

When I run "pg_ctl start" via SSH, the database service starts, but it never
returns control to the shell. If I run the same command from an interactive
shell, it works as expected.

This is with or without the -w option. Here's an example commandline.

ssh root@example.com "su -c '/home/appuser/local/bin/pg_ctl start -w -s -D
/home/appuser/postgresql_data' postgres"

This problem exists both when installed from source and when using the
postgresql installed via the debian package manager (of course using a
different path to the pg_ctl binary)

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

[GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints

I have several .SQL files created from pg_dump, and I find that when I
feed them into psql that I get tons of foreign key errors because the
INSERT statements in the dump are not in the correct order. After
reading the docs, mailing lists, and googling, I see posts saying this
problem was fixed back in the 7.x days. I'm using postgres 8.2.9. This
database doesn't do any "unusual" foreign key constraints like check
constraints, functions in the constraints, or circular foreign keys.
From the looks of the SQL dump, it is just simply in the wrong order
(it might even be alphabetical... the first table starts with c. But I
haven't looked at the entire 26GB dump to see if that is the case).

Since I did a data only dump, I think my only option is to create the
schema, manually disable all the constraints, then restore, then
re-enable the constraints. I'm looking for 2 things:

1) other workarounds
2) someone else who can confirm that this bug is either fixed, or not
fixed. If it is supposedly fixed, then I guess I need to make a smaller
version of my database to demonstrate the problem.

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

Re: [PERFORM] Choosing a filesystem

Merlin Moncure wrote:
> > although for postgres the thing that you are doing the fsync on is the WAL
> > log file. that is a single (usually) contiguous file. As such it is very
> > efficiant to write large chunks of it. so while you will degrade from the
> > battery-only mode, the fact that the controller can flush many requests
> > worth of writes out to the WAL log at once while you fill the cache with
> > them one at a time is still a significant win.
>
> The heap files have to be synced as well during checkpoints, etc.

True, but as of 8.3 those checkpoint fsyncs are spread over the interval
between checkpoints.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

[COMMITTERS] psqlodbc - psqlodbc: Suppress a compiler warning.

Log Message:
-----------
Suppress a compiler warning.

Modified Files:
--------------
psqlodbc:
socket.c (r1.63 -> r1.64)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/psqlodbc/psqlodbc/socket.c.diff?r1=1.63&r2=1.64)

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

Re: [pgsql-es-ayuda] Hay informacion del API C...

Por favor no pasar de la consulta ya que estoy muy interesado...

Al menos saber si hay algún sitio donde conseguir ejemplos del API en C

Muchas gracias

Manu escribió:
> Hay informacion del API C de postgres en español?
>
> Gracias
> --
> TIP 2: puedes desuscribirte de todas las listas simultáneamente
> (envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)
>

--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo