PostgreSQL: May 18, 2008

Sunday, May 18, 2008

Re: [pgsql-es-ayuda] Iniciar el postmaster.!!!

El día 18 de mayo de 2008 19:55, Mario Cassanelli
<mcassan@speedy.com.ar> escribió:
> Si Marcos, en la instalacion de postgreSQL no tengo problemas,
> como decia en el mail anterior, hace tiempo que uso ese comando
> y nunca lo hice de otra manera, es mas tambien lo he instalado
> en Debian sin terminal grafica, o sea todo por comandos...!
>
> Si, lo bajas y lo instalas de los repositorios de fedora, ya que
> en todas las versiones fueron incluyendo las ultimas,
> si usas yumex se te instala solo, el tema es iniciar el postmaster
> que ya te digo yo lo hacia insertando la instruccion en rc.local
> ahora lo tengo que hacer desde la consola.
> Cdo inicio linux, no le da bola a la instrucción en el rc.local
> debe haber alguna otra instruccion que lo apaga...
>

en la familia de las distros de redhat existe la orden "chkconfig" con
la cual indicas en que nivel un servicio debe subir o bajar, si no mal
recuerdo es algo como

chkconfig --level 5 postgresql on

donde 5 es el runlevel en el cual tu maquina parte... saludos.-

> Bueno vamos a ver que pasa
>
> Gracias
> Mario
>
>
>
> Marcos Saldivar wrote:
>>
>> El día 17 de mayo de 2008 20:34, Mario Cassanelli
>> <mcassan@speedy.com.ar> escribió:
>>
>>>
>>> siempre puse el comando
>>> su -c 'postmaster -i -D /var/lib/pgsql/data > /var/log/pg_sql/pg.log 2>&1
>>> &
>>> ' postgres en el rc.local, nunca tuve problemas, siempre okidokis, pero
>>> cambie de version
>>> de linux FC8-FC9 y no funca, then
>>> lo probe en la consola y todo bien
>>> lo ejecuto desde rc.local y no funk,
>>> alguna aidea
>>>
>>
>>
>>
>> mmmmmmmmmmm intentas levantar un PostgreSQL compilado ?
>>
>> La mayoría de las distros linux traen todo lo necesario para usar
>> fácilmente servicios como PostgreSQL, en el caso particular de Fedora
>> instalas PostgreSQL desde los repositorios oficiales y luego haces un
>> "service postgresql initdb" o un /etc/init.d/postgresql initdb y ya
>> estas listo para usar postgresql, de hecho fedora trae herramientas
>> gráficas para subir y bajar cualquier servicio
>>
>> Yo te recomiendo mejor utilizar las herramientas que trae Fedora, a
>> menos que tengas una necesidad muy particular...
>>
>> ps: no recuerdo bien si el nombre del script es
>> /etc/init.d/postgresql o /etc/init.d/postgres pero el procedimiento
>> que hacia yo era ese...
>>
>>
>>>
>>> Salutti
>>> Gracias
>>>
>>>
>>> PD. Alguien por ahi me pregunto si era posible encriptar toda la base de
>>> datos, para mayor seguridad,
>>> yo creo que no, en todo caso habra otras maneras de implementar la
>>> seguridad, no creo -y perdonen mi
>>> ignorancia como decia el maestro- que sea optimo encriptar la base.
>>>
>>
>> yo creo que si entran a tu server, aun que la info este encriptada ya
>> es tarde, que impide que la borren o corrompan ??
>>
>> E-mail clasificado por el Idenfificador de Spam Inteligente.
>> Para modificar la categoría clasificada acceda a su webmail
>>
>> Este mensaje ha sido verificado por el E-mail Protegido.
>> Antivirus actualizado en 17/05/2008 / Versión: 0.92.1/7144
>>
>>
>>
>>
>
> --
> TIP 4: No hagas 'kill -9' a postmaster
>
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [ADMIN] pg_restore test procedures (a bit OT)

> I was just wondering if anyone has any clever way of testing their backups
taken with pg_dump on a daily basis?
I have a daily bash script to backup of one of my databases to file, then
file to tape, which I test restore once per week. I've changed some of my
variables to hard-coded strings to make it shorter and easier to understand
at a quick read. Yes I know it's kinda fschked but it's working atm. I'll
tidy it up eventually....

<snip>
if [ "$DAY" = "Mon" ] ; then
echo "Test restoring database..." >> $LOG_FILE

if [ 1 -eq 1 ] ; then
# Debugging
$ECHO "Restoring from: ${BACKUP_PATH}"
$ECHO "Restoring to Directory: ${TMPDNAME}"
$ECHO "Restore Database: ${DBNAME}${DATESTRING}"
$ECHO "Restore Source File: ${TMPFILE}"
fi

echo -n "Extracting database dump from tape... "
tar xf /dev/st0 --directory /tmp
if [ $? -ne 0 ] ; then
echo "FAIL"
echo " Failed to Restore from /dev/st0 to /tmp/" >>
$LOG_FILE
SHOW_ERR=true
fi

if [ ${SHOW_ERR} != true ] ; then
### Note: $DATESTRING = yymmdd (eg. 080519)
echo "Creating new database ${DBNAME}${DATESTRING}" >>
$LOG_FILE
/usr/local/bin/psql --command "CREATE DATABASE
${DBNAME}${DATESTRING} ENCODING = 'SQL_ASCII';" > /dev/null
if [ $? -eq 0 ] ; then
echo "Restoring /tmp/psqldump.sql.502 to
${DBNAME}${DATESTRING}" >> $LOG_FILE
/usr/local/bin/psql ${DBNAME}${DATESTRING} <
/tmp/psqldump.sql.502
else
echo "Failed to Create Database!" >> $LOG_FILE
SHOW_ERR=true
fi
fi
Fi
</snip>

> su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers -c
-d $database $backup_dir/$server-$database-data.gz" >> $logfile
> or
> su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers -c
-d $database $backup_dir/$server-$database-data.gz >> $logfile"

Perhaps something like this: (it's Monday morning, so I might be suggesting
something stupid)
LOG=`su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers
-c -d $database $backup_dir/$server-$database-data.gz"`
echo $LOG >> $logfile

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

--
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] Formatear mensajes de error

On Sun, May 18, 2008 at 6:21 PM, Juan Manuel Fernandez
<jmfernandez@unlu.edu.ar> wrote:
> Buenas Lista!
>
> Pregunto despues de un rato de googlear y revisar el manual de
> PostgreSQL sin mucho exito (consideren que esta en ingles :P), hay
> alguna forma de lookear los mensajes de error?
>
> Por ejemplo, es posible que en vez de que cuando ingreso una clave
> repetida me aparezca ERROR: duplicate key value violates unique
> constraint "t_deptos_academicos_pkey", aparezca un mensaje redactado
> por mi?
>

Puedes usar el nombre de los constraints, por ejemplo:
"""
create table tab1 (col1 serial);
alter table tab1 add constraint "Registro ya existe." primary key (col1);

insert into tab1 values(1), (1);
"""

me da este mensaje:
ERROR: llave duplicada viola restricción de unicidad «Registro ya existe.»

O puedes poner el insert en una función y usar RAISE EXCEPTION

"""
CREATE FUNCTION insertar(int) RETURNS VOID AS $$
BEGIN
BEGIN
INSERT INTO tab1 VALUES ($1);
RETURN;
EXCEPTION WHEN unique_violation THEN
RAISE EXCEPTION 'Registro ya existe.';
END;
END;
$$ LANGUAGE plpgsql;

select insertar(1);
select insertar(1);

"""

y obtendras el error que prefieras, por ejemplo: "ERROR: Registro ya existe."

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
TIP 8: explain analyze es tu amigo

Re: [pgsql-es-ayuda] Iniciar el postmaster.!!!

Si Marcos, en la instalacion de postgreSQL no tengo problemas,
como decia en el mail anterior, hace tiempo que uso ese comando
y nunca lo hice de otra manera, es mas tambien lo he instalado
en Debian sin terminal grafica, o sea todo por comandos...!

Si, lo bajas y lo instalas de los repositorios de fedora, ya que
en todas las versiones fueron incluyendo las ultimas,
si usas yumex se te instala solo, el tema es iniciar el postmaster
que ya te digo yo lo hacia insertando la instruccion en rc.local
ahora lo tengo que hacer desde la consola.
Cdo inicio linux, no le da bola a la instrucción en el rc.local
debe haber alguna otra instruccion que lo apaga...

Bueno vamos a ver que pasa

Gracias
Mario

Marcos Saldivar wrote:
> El día 17 de mayo de 2008 20:34, Mario Cassanelli
> <mcassan@speedy.com.ar> escribió:
>
>> siempre puse el comando
>> su -c 'postmaster -i -D /var/lib/pgsql/data > /var/log/pg_sql/pg.log 2>&1 &
>> ' postgres en el rc.local, nunca tuve problemas, siempre okidokis, pero
>> cambie de version
>> de linux FC8-FC9 y no funca, then
>> lo probe en la consola y todo bien
>> lo ejecuto desde rc.local y no funk,
>> alguna aidea
>>
>
>
>
> mmmmmmmmmmm intentas levantar un PostgreSQL compilado ?
>
> La mayoría de las distros linux traen todo lo necesario para usar
> fácilmente servicios como PostgreSQL, en el caso particular de Fedora
> instalas PostgreSQL desde los repositorios oficiales y luego haces un
> "service postgresql initdb" o un /etc/init.d/postgresql initdb y ya
> estas listo para usar postgresql, de hecho fedora trae herramientas
> gráficas para subir y bajar cualquier servicio
>
> Yo te recomiendo mejor utilizar las herramientas que trae Fedora, a
> menos que tengas una necesidad muy particular...
>
> ps: no recuerdo bien si el nombre del script es
> /etc/init.d/postgresql o /etc/init.d/postgres pero el procedimiento
> que hacia yo era ese...
>
>
>> Salutti
>> Gracias
>>
>>
>> PD. Alguien por ahi me pregunto si era posible encriptar toda la base de
>> datos, para mayor seguridad,
>> yo creo que no, en todo caso habra otras maneras de implementar la
>> seguridad, no creo -y perdonen mi
>> ignorancia como decia el maestro- que sea optimo encriptar la base.
>>
>
> yo creo que si entran a tu server, aun que la info este encriptada ya
> es tarde, que impide que la borren o corrompan ??
>
> E-mail clasificado por el Idenfificador de Spam Inteligente.
> Para modificar la categoría clasificada acceda a su webmail
>
> Este mensaje ha sido verificado por el E-mail Protegido.
> Antivirus actualizado en 17/05/2008 / Versión: 0.92.1/7144
>
>
>
>
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [pgsql-es-ayuda] Iniciar el postmaster.!!!

Con lo de la base encriptada totalmente de acuerdo, lo que es obvio si
hay algo que encryptar
sera algun campo pero bueno eso es otra cosa.

El tema de arrancar postgres:
esta instruccion la vengo usando desde postgresql 7.4,
incluso esta en los manuales,
no dice que debo insertarla en el rc.local,
habla de hacer un script, ahora como nunca tuve problemas
no se me ocurrio buscar otra cosa, si se que hay
instrucciones para arrancar postgres, pero lo que quiero
es que cuando se inicia linux, entre las cosas que cargue o inicie
es postgres.

Salutti
Mario

Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1

Merlin Moncure wrote:

On Sun, May 18, 2008 at 5:22 PM, Zoltan Boszormenyi <zb@cybertec.at> wrote:

Can we get the rows in tree order, please? I.e. something like this:

 Is ordering by tree order defined in the standard when no explicit order is given?  If not, it probably returns them in the order they are pulled up, which might be the fastest way

+1 for the fastest way, which I expect to often be "find all level 1 matches", "find all level 2 matches", ... If ORDER BY is important, it should be specified (although it may be difficult or impossible to properly represent ORDER BY for a tree? not sure?) I think most uses of recursive require extra client side code to deal with anyways, so only relative order is important (order within a particular branch).

There are things I'd like to use this for right now. Currently I use plpgsql procedures to implement my own recursion. :-)

Cheers,
mark

--  Mark Mielke <mark@mielke.cc>

[pgsql-es-ayuda] Formatear mensajes de error

Buenas Lista!

Pregunto despues de un rato de googlear y revisar el manual de
PostgreSQL sin mucho exito (consideren que esta en ingles :P), hay
alguna forma de lookear los mensajes de error?

Por ejemplo, es posible que en vez de que cuando ingreso una clave
repetida me aparezca ERROR: duplicate key value violates unique
constraint "t_deptos_academicos_pkey", aparezca un mensaje redactado
por mi?

Muchas gracias y saludos!

Juan
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [GENERAL] Link tables from different databases

> I want it so that every operation on db1.table1 (ie. INSERT, UPDATE, DELETE)
> also happens to db2.table2, but similarly I want it that application can do
> those operations on db2.table2 but actually it does it on db1.table1 behind
> the scene.

If the tables could be in the same database but in different schemas,
you should be able to arrange a view in schema2 (called "table2") with
rules that operate on table1 in schema1. See the manual for a discuss of
rules and views.

If you really want these tables in separate *databases* (same machine or
not), then you probably want dblink, a stiff drink, and a long-hard
think about the prudence of such a design.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

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

Re: [PATCHES] WITH RECURSIVE patch V0.1

On Sun, May 18, 2008 at 5:22 PM, Zoltan Boszormenyi <zb@cybertec.at> wrote:
> Can we get the rows in tree order, please? I.e. something like this:

Is ordering by tree order defined in the standard when no explicit
order is given? If not, it probably returns them in the order they
are pulled up, which might be the fastest way.

merlin

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

Re: [HACKERS] Link requirements creep

Tom Lane wrote:
> I've been testing a new patch and do not see the problem on Fedora 8,
> so at least that platform's readline seems to be fixed. I find
> the hack Martijn proposes in the above message to be pretty ugly,
> so what I'm inclined to do is leave that out for now and see what
> failures we get. The availability of the buildfarm makes experimenting
> with this kind of thing a lot safer ...
>
>
>

It broke my FC6 box :-(

The box is due to be upgraded when I return from pgcon, but we have a
few oldish boxes on the buildfarm.

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: [PATCHES] WITH RECURSIVE patch V0.1

create table test_connect_by (
parent integer,
child integer,
constraint uq_tcb unique (child)
);

insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);

insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);

insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);

insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);

insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);

insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);

insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);

David Fetter írta:
> On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote:
>
>> WITH RECURSIVE patch V0.1
>>
>> Here are patches to implement WITH RECURSIVE clause. There are some
>> limitiations and TODO items(see the "Current limitations" section
>> below). Comments are welcome.
>>
>> 1. Credit
>>
>> These patches were developed by Yoshiyuki Asaba (y-asab@sraoss.co.jp)
>> with some discussions with Tatsuo Ishii (ishii@sraoss.co.jp).
>>
>
> This is really great! Kudos to all who made this happen :)
>
> I tried a bunch of different queries, and so far, only these two
> haven't worked. Any ideas what I'm doing wrong here?
>
> WITH RECURSIVE t(n) AS (
> SELECT 1
> UNION ALL
> SELECT n+1
> FROM t
> WHERE n < 100
> )
> SELECT * FROM t;
> ERROR: cannot extract attribute from empty tuple slot
>
> WITH RECURSIVE t(n) AS (
> VALUES (1)
> UNION ALL
> SELECT n+1
> FROM t
> WHERE n < 100
> )
> SELECT * FROM t;
> ERROR: cannot extract attribute from empty tuple slot
>
> Cheers,
> David.
>

Here's a test case attached shamelessly stolen from
http://www.adp-gmbh.ch/ora/sql/connect_by.html

This query (without naming toplevel columns) works:

# with recursive x as (select * from test_connect_by where parent is
null union all select base.* from test_connect_by as base, x where
base.parent = x.child) select * from x;
parent | child
--------+-------
| 38
| 26
| 18
18 | 11
18 | 7
26 | 13
26 | 1
26 | 12
38 | 15
38 | 17
38 | 6
17 | 9
17 | 8
15 | 10
15 | 5
5 | 2
5 | 3
(17 rows)

It even works when I add my "level" column:

# with recursive x(level, parent, child) as (select 1::bigint, * from
test_connect_by where parent is null union all select x.level + 1,
base.* from test_connect_by as base, x where base.parent = x.child)
select * from x;
level | parent | child
-------+--------+-------
1 | | 38
1 | | 26
1 | | 18
2 | 18 | 11
2 | 18 | 7
2 | 26 | 13
2 | 26 | 1
2 | 26 | 12
2 | 38 | 15
2 | 38 | 17
2 | 38 | 6
3 | 17 | 9
3 | 17 | 8
3 | 15 | 10
3 | 15 | 5
4 | 5 | 2
4 | 5 | 3
(17 rows)

But I have a little problem with the output.
If it's not obvious, here is the query tweaked a little below.

# with recursive x(level, parent, child) as (select 1::integer, * from
test_connect_by where parent is null union all select x.level + 1,
base.* from test_connect_by as base, x where base.parent = x.child)
select lpad(' ', 4*level - 1) || child from x;
?column?
------------------
38
26
18
11
7
13
1
12
15
17
6
9
8
10
5
2
3
(17 rows)

Can we get the rows in tree order, please? I.e. something like this:

?column?
------------------
38
15
10
5
2
3
17
9
8
6
26
13
1
12
18
11
7
(17 rows)

After all, I didn't specify any ORDER BY clauses in the base, recursive
or the final queries.

Also, it seems there are no infinite recursion detection:

# with recursive x(level, parent, child) as (
select 1::integer, * from test_connect_by where parent is null
union all
select x.level + 1, base.* from test_connect_by as base, x where
base.child = x.child
) select * from x;
... it waits and waits and waits ...

Also, there's another rough edge:

# with recursive x as (select * from test_connect_by where parent is
null) select * from x;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Re: [HACKERS] odd output in restore mode

On Sun, May 18, 2008 at 1:38 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> I don't know how you tested,

Copy a large file across a relatively slow network, and check the size
on the destination drive before it finishes.

> but could you please repeat the test with
> GnuWin32's cp.exe? If it doesn't preallocate the space then I think our way
> forward is reasonably clear:

It does not pre-allocate.

> . we recommend its use for Windows archive_command settings
> . we provide the delay kluge as switchable behaviour on Windows instead of
> having it always on.

Sounds reasonable to me.

--
Dave Page
EnterpriseDB UK: 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: [BUGS] BUG #4182: Enum in Foreign Key broken

"Rod Taylor" <rod.taylor@gmail.com> writes:
> The Update in the below script results in the following
> ERROR: no conversion function from some_enum to anyenum

This is coming from

if (pathtype != COERCION_PATH_FUNC &&
pathtype != COERCION_PATH_RELABELTYPE)
{
/* If target is ANYARRAY, assume it's OK, else punt. */
if (lefttype != ANYARRAYOID)
elog(ERROR, "no conversion function from %s to %s",
format_type_be(typeid),
format_type_be(lefttype));
}

I suspect this code needs to make an exception for ANYENUM as well, but
no time to look closely right now.

regards, tom lane

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

Re: [HACKERS] New DTrace probes proposal

Greg Smith wrote:
> There's also a big DTrace probe set patch available from OmniTI:
> https://labs.omniti.com/project-dtrace/trunk/postgresql/
> http://labs.omniti.com/trac/project-dtrace/wiki/Applications#PostgreSQL
>
> I don't know if you've looked at that before. There's some overlap
> but many unique and handy probes to each set. I think it would be
> nice to consider a superset union of the two.
I heard about Theo's probes recently, but I haven't had a chance to look
at them closely. I'm more than happy to adapt his probes for 8.4 and
remove any duplicates if there are no objections from Theo.
> I would guess OmniTI would be glad to have their set assimilated into
> core as well so they don't have to maintain their patch past 8.3;
I'd think so too!

-Robert

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

[pgadmin-hackers] SVN Commit by dpage: r7296 - trunk/pgadmin3/pgadmin/include/utils

Author: dpage

Date: 2008-05-18 21:37:42 +0100 (Sun, 18 May 2008)

New Revision: 7296

Revision summary: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/?rev=7296&view=rev

Log:
Add missing header

Modified:
trunk/pgadmin3/pgadmin/include/utils/sysSettings.h

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

[HACKERS] ERRORDATA_STACK_SIZE panic crashes on Windows

Have any Windows-using hackers tried to look into the reports of
$SUBJECT on 8.3? We have two fresh reports:
http://archives.postgresql.org/pgsql-bugs/2008-05/msg00106.php
http://archives.postgresql.org/pgsql-bugs/2008-05/msg00109.php
and this isn't the first time we've heard of it.

I spent some time chasing the theory that the conversion from
UTF8 to the client encoding was failing; but if that's the case
it should be reproducible on non-Windows machines, and I didn't
have any luck with that.

What I'm currently thinking is that maybe gettext() isn't on the
same page as us concerning what encoding it's supposed to produce
its output in. This is reinforced by the mention of changing
lc_messages in the first report above. We had had some discussions
of trying to adjust the LC_XXX values to ensure that they all
represent the same encoding choice, but I don't believe that got done.
It might also be significant that both complainants are using UTF8
database encoding; IIRC that has some weird status in the Windows
locale world.

I am also toying with the idea of disabling gettext usage once we
get past two or so levels of error nesting, in order to prevent the
recursion panic in this type of scenario --- but it would be real
nice to know for certain what is happening before we try 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

[PHP] pg_connect error

Hello, I have this problem with a connection to postgres server (running in a win2000 server).

When I connect with pgAdmin to the same postgres server works ok, but when I try to connect from php5 the connection fails.

Can help me?

Thanks.

$db_conn = pg_connect("host=SQLMACHINE port=5432 password=xxxx user=postgres dbname=compraventa1");
if (!$db_conn): ?>
<H1>Failed connecting to postgres database <? echo "compraventa1" ?></H1> <?
exit;
endif;

Ovax

Re: [BUGS] BUG #4180: PANIC while PQExec on Client with differen locale from database

"bishop" <polimax@mail.ru> writes:
> PQsetClientEncoding(conn,"WIN1251");
> res = PQexec(conn, "INSERT INTO \"table_name\" (\"name\") VALUES
> ('test');");

What do you get, exactly, if you do the same command but with
client_encoding = UTF8? I suppose this must be a problem with
failing to convert the encoding of an error message, but without
knowing what the original error message is, it's hard to investigate.

regards, tom lane

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

[COMMITTERS] pgsql: Make another try at using -Wl,--as-needed to suppress linking of

Log Message:
-----------
Make another try at using -Wl,--as-needed to suppress linking of unnecessary
shared libraries. We've tried this before and had problems with libreadline
not linking properly on some platforms, but that seems to be a libreadline
bug that may have been fixed by now. In any case, it's early enough in the
8.4 devel cycle that we can afford to have some transient breakage while
we work out any portability problems.

On Darwin, we try -Wl,-dead_strip_dylibs, which seems to be the equivalent
incantation there.

Modified Files:
--------------
pgsql/config:
c-compiler.m4 (r1.16 -> r1.17)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/config/c-compiler.m4?r1=1.16&r2=1.17)
pgsql:
configure (r1.594 -> r1.595)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/configure?r1=1.594&r2=1.595)
configure.in (r1.560 -> r1.561)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/configure.in?r1=1.560&r2=1.561)

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

Re: [HACKERS] notification information functions

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>
>> I suggest:
>>
>
>
>> pg_listened_events(out event name) returns setof record
>> pg_pending_events(out event name, out message text) returns setof record
>>
>
>
>> The first would show events being listened on by the current backend,
>> while the second would show all pending events for the current db.
>>
>
> pg_listened_events seems reasonable, but what's a "pending event"?
> If you mean stuff that hasn't yet been removed from the shared circular
> buffer, it seems like that would be too transient (not to mention
> implementation-dependent) to be interesting.
>
>
>

Fair enough. I'm all for less work ;-)

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] notification information functions

Andrew Dunstan <andrew@dunslane.net> writes:
> I suggest:

> pg_listened_events(out event name) returns setof record
> pg_pending_events(out event name, out message text) returns setof record

> The first would show events being listened on by the current backend,
> while the second would show all pending events for the current db.

pg_listened_events seems reasonable, but what's a "pending event"?
If you mean stuff that hasn't yet been removed from the shared circular
buffer, it seems like that would be too transient (not to mention
implementation-dependent) to be interesting.

regards, tom lane

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

[HACKERS] notification information functions

I am working on moving the notification buffer into shared memory as
previously discussed. Since pg_listener will no longer exist, I think we
need to provide a couple of information functions.

I suggest:

pg_listened_events(out event name) returns setof record
pg_pending_events(out event name, out message text) returns setof record

The first would show events being listened on by the current backend,
while the second would show all pending events for the current db.

Given that there will no longer be any central place where events will
be registered to be listened on, it will not be possible to show all
such events for the current db.

Comments?

cheers

andrew

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

Re: [pgsql-es-ayuda] charla sobre postgres OT

ESTOY DESAROLLANDO UN APLICACION CON PHP Y POSTGRES 8.0.4 PERO NECESITO INFORMACION DE COMO TRABAJAN LA VISTA LOGICAS, DE MANERA DE COMO MANIPULARLA DESDE EL CODIGO PHP Y DE ESTA MANERA DE NO TOCAR LOS DATOS EN LA BASES DE DATOS SE LO AGRADECERIA

[BUGS] BUG #4182: Enum in Foreign Key broken

The following bug has been logged online:

Bug reference: 4182
Logged by: Rod Taylor
Email address: rod.taylor@gmail.com
PostgreSQL version: 8.3.1
Operating system: amd64-portbld-freebsd7.0, compiled by GCC cc (GCC) 4.2.1
20070719 [FreeBSD]
Description: Enum in Foreign Key broken
Details:

The Update in the below script results in the following
ERROR: no conversion function from some_enum to anyenum

CREATE TYPE some_enum AS ENUM('Value 1', 'Value 2');
CREATE TABLE t1
( col1 varchar(10)
, col2 some_enum
, testboolean boolean
, PRIMARY KEY(col1, col2)
);
CREATE TABLE t2
( col1 varchar(10)
, col2 some_enum
, PRIMARY KEY(col1, col2)
, FOREIGN KEY (col1, col2) REFERENCES t1(col1, col2) on update cascade on
delete restrict
);

INSERT INTO t1 VALUES ('varcharval', 'Value 1');
INSERT INTO t1 VALUES ('varcharval', 'Value 2');

INSERT INTO t2 VALUES ('varcharval', 'Value 1');

UPDATE t1 SET testboolean = false;
-- ERROR: no conversion function from some_enum to anyenum

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

Re: [PATCHES] WITH RECURSIVE patch V0.1

On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote:
> WITH RECURSIVE patch V0.1
>
> Here are patches to implement WITH RECURSIVE clause. There are some
> limitiations and TODO items(see the "Current limitations" section
> below). Comments are welcome.
>
> 1. Credit
>
> These patches were developed by Yoshiyuki Asaba (y-asab@sraoss.co.jp)
> with some discussions with Tatsuo Ishii (ishii@sraoss.co.jp).

This is really great! Kudos to all who made this happen :)

I tried a bunch of different queries, and so far, only these two
haven't worked. Any ideas what I'm doing wrong here?

WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1
FROM t
WHERE n < 100
)
SELECT * FROM t;
ERROR: cannot extract attribute from empty tuple slot

WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1
FROM t
WHERE n < 100
)
SELECT * FROM t;
ERROR: cannot extract attribute from empty tuple slot

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

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

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

Re: [HACKERS] ignore $PostgreSQL lines in regression tests?

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>
>> Recently while adding $PostgreSQL markers to a bunch of .c and .h files
>> I ran into trouble with the ecpg regression tests and had to revert the
>> change for a handful of files. However, it occurred to me that we could
>> have pg_regress tell diff to ignore such lines, by passing it the
>> arguments "-I '\$PostgreSQL:' ", which would tell it to ignore
>> additions or deletions of lines matching that regex.
>>
>
>
>> Would this be a good thing to do?
>>
>
> I'm inclined to think not. It's easy to think of scenarios where such
> a switch would mask errors.
>
>
>

OK.

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] ignore $PostgreSQL lines in regression tests?

Andrew Dunstan <andrew@dunslane.net> writes:
> Recently while adding $PostgreSQL markers to a bunch of .c and .h files
> I ran into trouble with the ecpg regression tests and had to revert the
> change for a handful of files. However, it occurred to me that we could
> have pg_regress tell diff to ignore such lines, by passing it the
> arguments "-I '\$PostgreSQL:' ", which would tell it to ignore
> additions or deletions of lines matching that regex.

> Would this be a good thing to do?

I'm inclined to think not. It's easy to think of scenarios where such
a switch would mask errors.

regards, tom lane

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

[HACKERS] ignore $PostgreSQL lines in regression tests?

Recently while adding $PostgreSQL markers to a bunch of .c and .h files
I ran into trouble with the ecpg regression tests and had to revert the
change for a handful of files. However, it occurred to me that we could
have pg_regress tell diff to ignore such lines, by passing it the
arguments "-I '\$PostgreSQL:' ", which would tell it to ignore
additions or deletions of lines matching that regex.

That would probably also allow us to add such markers as comments in
results files, which mightn't be a bad thing either.

I'm a bit unsure how portable this is, though. Linux, Windows and OSX
should be ok, but they are the only OSs I run so I don't know about others.

Would this be a good thing to do?

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: [GENERAL] tsearch2 on-demand dictionary loading & using functions in tsearch2

Tom Lane wrote:

> What I think *is* worth doing is spending some time on making dictionary
> loading go faster.

This is probably a stupid question, but: with PostgreSQL's use of shared
memory, is it possible to load dictionaries into a small reserved shm
area when the first backend starts, then use the preloaded copy in
subsequent backends?

That way the postmaster doesn't have to do any risky work.

Anything that reduces backend startup costs and per-backend unshared
memory would have to be a good thing.

I've found it useful in the past to share resources with an mmap()ped
file, too, especially if I want write protection from some or all
processes. If the postmaster forked a process to generate the mmap()able
compiled dictionary files on startup then it'd be pretty safe from any
misbehaviour of the dictionary compiling process.

Then again, I can't say I've personally noticed the cost of loading
tsearch2 dictionaries.

--
Craig Ringer

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

[BUGS] BUG #4181: Service PostgreSQL database server 8.2 failed to start

The following bug has been logged online:

Bug reference: 4181
Logged by: Dmitriy
Email address: Lyasman@gmail.com
PostgreSQL version: 8.2.6
Operating system: Win XP SP2
Description: Service PostgreSQL database server 8.2 failed to start
Details:

Hi ,
When i install postgreSQL 8.26 i got this problem:

"Service 'PostgreSQL Database Server 8.2' (pgsql-8.2) failed to start.
Verify that you have sufficient priveleges to start system services." .
I loged as administrator , and the secondary logon service running on my
system system.
What must i do?

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

Re: [GENERAL] tsearch2 on-demand dictionary loading & using functions in tsearch2

Teodor Sigaev <teodor@sigaev.ru> writes:
>> Hmm, good point; I presume "accept the fact that settings change won't
>> propagate to other backends until reconnect" would not be acceptable
>> behavior, even if documented along with the relevant configuration option?

> I suppose so. That was one of the reasons to move tsearch into core and it will
> be too regrettable to lost that feature again.

The whole idea is pretty much a nonstarter anyway, because the only
place to "preload" dictionaries would be in the postmaster process.
That will never fly for several reasons:

1. The postmaster doesn't have database access and so couldn't read
the dictionary definition.

2. If it did have database access, it wouldn't know which database
of the cluster to look in.

3. The point of the postmaster process is to be so simple as to be
nearly crashproof. We would never accept a patch that made it execute
dictionary-loading code, because of the likely impact on system
reliability. (This is in fact the reason behind point #1.)

4. The approach would never work anyway on Windows, since that platform
does fork/exec not just fork.

What I think *is* worth doing is spending some time on making dictionary
loading go faster. Has any focused profiling and optimization effort
been spent on that code at all? One idea is to precompile the
text-based config files into some format designed to load fast.
(But it'd be premature to adopt that idea without some profiling
results to prove where the bottleneck is.)

regards, tom lane

--
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] Postgresql MVCC, Read Committed Isolation Level and taking "snapshot"

On Sun, May 18, 2008 at 8:04 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:

> What is the effect of having nested functions all declared
> SERIALIZABLE?
>
> What if just the outermost is declared SERIALIZABLE?

SERIALIZABLE applies to the entire transaction, not to individual
function calls.

-Doug

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

Re: [GENERAL] Postgresql service stops every few minutes on Vista Biz

alisazegar <ali.sazegarnejad@gmail.com> writes:
> I'm having difficulties with Postgres lately. Postgres service for (pgsql
> 8.2 )kept stopping every few minutes.
> Then I read a note somewhere saying that there is a bug in 8.2 which causing
> the problem.
> I switched to 8.3 and have the same problem.

> What I found so far is that the last event before stopping is "loaded
> library LOG: loaded library "$libdir/plugins/plugin_debugger.dll" which I
> see it in windows logs (events) for applications.

Does the problem go away if you remove that from the preload_libraries
setting?

regards, tom lane

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

Re: [pgsql-es-ayuda] INSTALR EN OTRA PARTICION

Yo en realidad Trabajo diferente, no se si lo hago mal o no gano mucho con esta idea, pero cuando tengo el postgres arriba solo creo un TableSpace en la ubicación donde quiero guardar la información y luego ligo la Base de Datos con ese TableSpace. no tengo que detener procesos, sinembargo no se que implicaciones positivas o negativas puede tener este proceso

El día 18 de mayo de 2008 10:17, Luis Fernando Curiel Cabrera <lcuriel@gmail.com> escribió:

Muchas gracias por su ayuda, creo que si estamos mal acostumbrados a simplemente darle siguiente siguiente..

Lo hare de esa forma, ahora me surge una duda más...

Si ya lo tengo instalado y ahora quiero mover la carpeta de data a otra parición, qque es lo que debo hacer?

2008/5/18 Oswaldo Hernández <listas@soft-com.es>:

Jaime Casanova escribió:

2008/5/17 Oswaldo Hernández <listas@soft-com.es>:

Luis Fernando Curiel Cabrera escribió:

Ahora sí, tengoq eu instalar postgres en un servidor con windows XP
Profesional. Quiero instalarlo de tal forma que toda la carpeta dinde se
guardan fisicamente las bases de datos ponerlo en una particion diferente a
C:

Para mi lo mas sencillo es:

* Instalar normalmente
* detener el servicio
* mover la carpeta data a la nueva ubicación
* modificar en el registro el comando que se utiliza para iniciar el
servicio cambiandole la ruta de 'data'
* iniciar el servicio.

y que tal instalar, indicando que la ubicacion de la carpeta data esta
en otro lado? eso lo haces en la pantalla en la que escojes que
quieres instalar (server, odbc, hdbc, headers, etc)

Opss. La verdad es que ni me habia dado cuenta de que estaba ahi la opcion para establecer el directorio data. Esto del siguiente, siguiente ... acabará volviendome tonto ;)

Saludos,

--
*****************************************
Oswaldo Hernández
oswaldo (@) soft-com (.) es
*****************************************
PD:
Antes de imprimir este mensaje, asegúrese de que es necesario.
El medio ambiente está en nuestra mano.

--
Luis Fernando Curiel Cabrera
- Professional ABACO DE BOLITAS Developer.
- Certified ABACO DE BOLITAS Programmer.

--

-----------------------
MVJR

Re: [HACKERS] Can't t compile current HEAD

On Thu, 15 May 2008, Nikhils wrote:

> On Thu, May 15, 2008 at 11:59 AM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>>> I always use a ~/.cvsrc containing
> My .cvsrc also includes:

Good hints, and there's now a little section including them all at

http://wiki.postgresql.org/wiki/Working_with_CVS#Initial_setup

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [PERFORM] Strange behavior: pgbench and new Linux kernels

On Fri, 18 Apr 2008, Matthew wrote:

> So what happens if you run pgbench on a separate machine to the server?
> Does the problem still exist in that case?

It does not. At the low client counts, there's a big drop-off relative to
running on localhost just because of running over the network. But once I
get to 4 clients the remote pgbench setup is even with the localhost one.
At 50 clients, the all local setup is at 8100 tps while the remote pgbench
is at 26000.

So it's pretty clear to me now that the biggest problem here is the
pgbench client itself not working well at all with the newer kernels.
It's difficult to see through that to tell for sure how well each kernel
version is handling the server portion of the job underneath. I hope to
have time this week to finally submit all this to lkml.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [pgsql-es-ayuda] INSTALR EN OTRA PARTICION

Muchas gracias por su ayuda, creo que si estamos mal acostumbrados a simplemente darle siguiente siguiente..

Lo hare de esa forma, ahora me surge una duda más...

Si ya lo tengo instalado y ahora quiero mover la carpeta de data a otra parición, qque es lo que debo hacer?

2008/5/18 Oswaldo Hernández <listas@soft-com.es>:

Jaime Casanova escribió:

2008/5/17 Oswaldo Hernández <listas@soft-com.es>:

Luis Fernando Curiel Cabrera escribió:

Ahora sí, tengoq eu instalar postgres en un servidor con windows XP
Profesional. Quiero instalarlo de tal forma que toda la carpeta dinde se
guardan fisicamente las bases de datos ponerlo en una particion diferente a
C:

Para mi lo mas sencillo es:

* Instalar normalmente
* detener el servicio
* mover la carpeta data a la nueva ubicación
* modificar en el registro el comando que se utiliza para iniciar el
servicio cambiandole la ruta de 'data'
* iniciar el servicio.

y que tal instalar, indicando que la ubicacion de la carpeta data esta
en otro lado? eso lo haces en la pantalla en la que escojes que
quieres instalar (server, odbc, hdbc, headers, etc)

Opss. La verdad es que ni me habia dado cuenta de que estaba ahi la opcion para establecer el directorio data. Esto del siguiente, siguiente ... acabará volviendome tonto ;)

Saludos,

--
*****************************************
Oswaldo Hernández
oswaldo (@) soft-com (.) es
*****************************************
PD:
Antes de imprimir este mensaje, asegúrese de que es necesario.
El medio ambiente está en nuestra mano.

--
Luis Fernando Curiel Cabrera
- Professional ABACO DE BOLITAS Developer.
- Certified ABACO DE BOLITAS Programmer.

Re: [GENERAL] Link tables from different databases

Reuben D. Budiardja wrote:
> Hello,
> Suppose I have two tables db1.table1 and db2.table2. Is there anyway I can
> actually have db2.table2 a link to db1.table1 ? What I meant is similar to a
> symlink in Unix filesystem (db2.table2 is a symlink to db1.table1).
>
> I want it so that every operation on db1.table1 (ie. INSERT, UPDATE, DELETE)
> also happens to db2.table2, but similarly I want it that application can do
> those operations on db2.table2 but actually it does it on db1.table1 behind
> the scene.
>
> Is there anything like this in postgresql ? I am using version 7.4 by the way.
>
> Thanks in advance for any help.
> RDB

You will get people saying that this is bad design and they will be
right. You should think about your design before going in this direction.

Does this same data belong to both db's? Can the two db's be merged into
one using different schema to separate the data instead of different db's?

There isn't a simple way (like a simple symlink) but dblink in the
contrib folder may help.

To automate the process you will need to add triggers to pass along all
the changes but I think you will find that being able to update the
table in both db's will cause you some headaches (think endless loops).

You may want to have one db with the table and use functions to access
the data from the other db.

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
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] tsearch2 on-demand dictionary loading & using functions in tsearch2

> Hmm, good point; I presume "accept the fact that settings change won't
> propagate to other backends until reconnect" would not be acceptable
> behavior, even if documented along with the relevant configuration option?

I suppose so. That was one of the reasons to move tsearch into core and it will
be too regrettable to lost that feature again.

>>> As for my second question, is it possible to use functions in
>>> tsearch2? For example, writing my own stemmer in PL/pgSQL or in C as
>>> a postgres function.
> I've had something different in mind. Considering there are already
> facilities to use functions, be it PL/pgSQL, PL/Python or C, why not
> just use those with the condition that the function must accept
> some-arguments and return some-result? Or would using this, even while
> using C as the language used for the actual parser, slow things down too?

API to dictionary and parser intentionally utilizes complex (and nested)
C-structures to decrease overheads. During parse of text postgres makes two call
of parser (one call - parser returns word, second - word delimiter. Space is a
lexeme too! Although it's not a subject to index) and one call of dictionary per
word. So, if your language can work with C-structures then you can use that
language with tsearch with more or less performance pay. PL/pgSQL hasn't this
capability.

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

--
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] updating a row referenced by a foreign key

Michael P. Soulier wrote:

> Is there a simple way to update the row, and cascade the change to any
> and all rows referencing this row as a foreign key? I searched on
> cascade in the postgres docs and while I found references to deletion, I
> didn't find one for updates.

http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK

"Analogous to ON DELETE there is also ON UPDATE which is invoked when a
referenced column is changed (updated). The possible actions are the same."

--
Craig Ringer

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

[GENERAL] updating a row referenced by a foreign key

Hi,

I have to update a row of data that is referenced via a foreign key
constraint from another table.

I cannot change the id of the referenced record because it is referenced
as a foreign key from another table.

Is there a simple way to update the row, and cascade the change to any
and all rows referencing this row as a foreign key? I searched on
cascade in the postgres docs and while I found references to deletion, I
didn't find one for updates.

Thanks,
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 613-592-2122 x2522
"Any intelligent fool can make things bigger and more complex... It
takes a touch of genius - and a lot of courage to move in the opposite
direction." --Albert Einstein

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

Re: [PATCHES] libpq object hooks (libpq events)

Merlin Moncure wrote:
> On Sat, May 17, 2008 at 8:28 AM, Andrew Chernow <ac@esilo.com> wrote:
>> Here is an updated patch for what was called object hooks. This is now
>> called libpq events. If someone has a better name or hates ours, let us
>> know.
>
>
> Let's decide where to go with this. We have no objections to pushing
> this back to the next fest (tom's comments on the wiki were noted).
> If that's the case though, we would like to wrap up a consenus on the
> general implementation in the meantime. Just give us a heads up and
> I'll update the wiki.
>
> merlin
>
>

Yeah, it would be nice to avoid another push back into July by getting some
feedback now for June; it would be great to squeeze it into May. I'm not
talking about a review, but maybe a couple of "I hate this" or "This works well"
or "Give up coding" :) The implementation of the events (as well as when they
were object hooks) is actually very simple, so a quick glance can probably raise
most concerns. There is very little going on.

In the end, something like this can be done several different ways (choice here
is a matter of taste & style). It would be nice to iron out the API and focus
on other aspects; namely is this general concept appealing enough.

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

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

Re: [GENERAL] Postgresql service stops every few minutes on Vista Biz

alisazegar wrote:
> Hi All,
>
> I'm having difficulties with Postgres lately. Postgres service for (pgsql
> 8.2 )kept stopping every few minutes.
> Then I read a note somewhere saying that there is a bug in 8.2 which causing
> the problem.
> I switched to 8.3 and have the same problem.

It's running happily on my Vista Business laptop. Well, as happily as
anything runs on Windows ;-)

> What I found so far is that the last event before stopping is "loaded
> library LOG: loaded library "$libdir/plugins/plugin_debugger.dll" which I
> see it in windows logs (events) for applications.

That's normal and appears in my logs as well.

Try having a look at the logs in the database directory. By default,
that'll be:

%PROGRAMFILES%\PostgreSQL\8.3\data\pg_log

If you don't see anything useful, consider increasing the log level by
editing postgresql.conf and changing log_min_messages from:

#log_min_messages = notice

log_min_messages = debug1

Also, check to see if all postgresql processes are terminating or
whether it's just the master.

--
Craig Ringer

--
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] tsearch2 on-demand dictionary loading & using functions in tsearch2

Teodor Sigaev wrote:
>> As for downsides, I only really see two:
>> * Tracking updates of dictionaries - but it's reasonable to believe
>> that new connections get open more often than the dictionary gets
>> updated. Also, this might be easily solved by stat()-ing the
>> dictionary file before starting up session, and only have the server
>> reload it if there's a notified change.
>> * Possibly complicated to implement?
>
> Keeping dictionary up to date - it's a most difficult part here.
> Configuration of dictionary might be done by ALTER command - so, parent
> process (and all currently running backends) should get that information
> to reload dictionary.

Hmm, good point; I presume "accept the fact that settings change won't
propagate to other backends until reconnect" would not be acceptable
behavior, even if documented along with the relevant configuration option?

>> As for my second question, is it possible to use functions in
>> tsearch2? For example, writing my own stemmer in PL/pgSQL or in C as a
>> postgres function.
>
> Yes, of course, you can develop your dictionary (-ies) and parser. Dut
> only in C, because they are critical for performance.

I've had something different in mind. Considering there are already
facilities to use functions, be it PL/pgSQL, PL/Python or C, why not
just use those with the condition that the function must accept
some-arguments and return some-result? Or would using this, even while
using C as the language used for the actual parser, slow things down too?

Best regards,
Steve

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

Re: [PATCHES] [HACKERS] use of pager on Windows psql

Bruce Momjian wrote:
>
>
>> This broke the buildfarm and finally explains the following kluge which
>> has been puzzling me for four years:
>>
>> /*
>> * for some reason MinGW (and MSVC) outputs an extra newline, so
>> this
>> * suppresses it
>> */
>> #ifndef WIN32
>> fputc('\n', fout);
>>

Re: [pgsql-de-allgemein] WebApplication und Betriebssystem Performance Fragen.

*hrmpf*

On Sun, 18 May 2008 12:13:35 +0200 rudi@je-more.de wrote:

> Andreas 'ads' Scherbaum schrieb:

> Sorry, aber das ist etwas zu generell gesprochen.

Natürlich. Deine "Anforderungen" waren nicht mehr als 6 generelle
Punkte. Was erwartest du?

> Ich weiß nicht wie alt Du bist und wie viel KnowHow Du in deinem Leben
> schon sammeln kontest,
> aber ich bin 33 Jahre alt und seit 15 Jahren in der Softwareentwicklung
> mit Datenbanken der
> verschiedensten Herrsteller.

Dann bin ich ein Jahr jünger und etwas länger damit beschäftigt. Was
wird das jetzt, ein *Vergleich?

> Über Banalitäten wie "Know How ins Spiel bringen" und "benutz mal
> Google" ähnliches brauchen wir uns wirklich nicht zu unterhalten.

Ich habe nichts von "Google" gesagt. Eine Suchmaschine soll sich jeder
selbst aussuchen und bedienen können.

Was ich jedoch sage (und meine): du startest hier ein Projekt, mit
welchem du 100% Verfügbarkeit erreichen möchtest und gibst gleichzeitig
zu, dich mit PostgreSQL noch nicht viel beschäftigt zu haben.
Was soll ich dir sonst empfehlen ausser: du brauchst dafür mehr
Erfahrung. Wenn du dich davon persönlich angegriffen fühlst, ist das
nicht mein Problem.

> Ich gehe davon aus, das Du Dich mit Postgres beschäftigt hast und
> vielleicht bist Du in einem Projekt
> mal damit befasst gewesen wie man Tabellenspalten des Typ Postgres/Text
> mit durchschnittlich
> einer Bildschirmseite Usertext 1 bis 1000 Zeichen am besten organisiert.
> Wenn nicht, dann hast
> Du zumindest eine "kleine".

Eine "kleine" was?
Ich habe mehrere Projekte umgesetzt, bei denen binäre Daten und/oder
längere Texte in Datenbanken gehalten werden. Das war nicht nur
PostgreSQL, da kamen mehrere Datenbanken dabei vor.

Ich möchte also schon sagen, dass ich nicht nur das wiedergebe, was
andere zu dem Thema sagen sondern auch meine ganz eigenen Erfahrungen
gesammelt habe. In keinem Fall hat es etwas gebracht, nur die Datenbank
zu entwickeln. Dazu gehört jedesmal auch eine Betrachtung der
I/O-Leistung der Server und eine Betrachtung der sonstigen eingesetzten
Software (Caches, Webserver, Programmiersprache ect.)

> > Konzept anpassen, Konzept verändern, Datenbankstruktur überprüfen ect.
> > Da gibt es viele Möglichkeiten.
> >
> Ich weiß nicht ob Du Dich manchmal in die Rolle dessen hinneinversetzt
> der deine Texte am Ende
> ließt, aber genau solche Aussagen sind, um es diplomatisch auszudrücken
> - eher gewöhnungsbedürftig
> (nicht technisch, aber zwiswchenmenschlich).

LOL
Ich habe dich nach konkreten(!) technischen Anforderungen gefragt und du
lieferst einen 6-Punkte Plan ab. Damit kann niemand etwas anfangen.

Deine aktuelle Datenbank platzt anscheinend aus den Nähten, sonst
würdet ihr das nicht auf eine neue Datenbank portieren wollen. Das ist
ein geeigneter Zeitpunkt, auch mal auf die aktuellen Probleme zu
schauen und zu überprüfen, welche Konzeptänderungen man einbringen kann.

Da du allerdings nichts konkretes sagst - was erwartest du zu hören?
Irgendwelche konkreten Lösungen?

> > Ich wette mal dass von deinen vielen Millionen oder Milliarden
> > Nachrichten nur ein sehr geringer Teil ständig abgerufen wird. Dafür
> > gibt es geeignete Technologien, so etwas verfügbar zu machen.
> >
> Ok, ich habe nun verstanden das es Dir offensichtlich auf
> Dampfplauderrei ankommt
> oder wie ist es sonst möglich das Du ständig mit so vielen wort nichts
> zum Ausdruck bringst?

Du hast gar nichts verstanden.
Du hast ein Nachrichtensystem. Wieviele Nachrichten davon werden
ständig benötigt und wieviele bloss vorgehalten, um sie ev. mal
abzurufen? In der Regel ruft man die letzten Nachrichten ab.
Diese Nachrichten kann man anderweitig (memcache, andere Tabelle ect)
vorhalten und von dort abrufen. Das entlastet deine eigentliche
Datenbank und das sorgt allgemein für schnellere Antwortzeiten.

Du kannst das natürlich als "Dampfplauderrei" (Dampfplauderei) abtun ...
sorgst aber nur dafür, dass ich mich nicht weiter mit dir unterhalten
will.

> Hochverfügbarkeit ist korrekt. Mein Provider verfügt über die technische
> Infrastructure derartige
> technische Rahmenbedinungen zu garantieren (das sind alles einzelne
> Rootserver in einem
> Hochverfügbarkeitsrechenzentrum mit Diesel-Notstrohmagregaten u.s.w -
> das bieten jedoch heute
> schon viele).

Das genügt (noch) nicht, aber lassen wir das aussen vor.

> Ich bin also ein Neuling weil ich die Frage stelle wie man eine derzeit
> 700 GByte grosse Tabelle besser
> normalisieren der skaliarbar restrukturieren kann?

Du hast selbst gesagt, dass du dich bei Oracle gut auskennst, aber
nicht bei PostgreSQL. Du fragst selbst, wie man das ganze besser
strukturieren kann, aber in dem Moment, in dem ich ein Nachdenken über
das aktuelle (laufende) Konzept vorschlage und einige Ansätze liefere,
wirfst du mir "Dampfplauderrei" vor. Geh doch und löse deine Probleme
allein.

> Denk mal bitte etwas ausführlicher darüber nach bevor
> Du antwortest, denn bisher hatte ich den Eindruck das Du, auch wenn Du
> Dich manchmal komisch äußerst
> ein kompetenter Entwickler bist mit dem man sich austauschen kann.

[X] Geh weg.

Du schaffst es, mit allen hier anzuecken.

> > Warum ist da immer noch PG 8.1 im Spiel? Oder welche Version läuft da
> > bei dir?
> >
> Also 8.1 läuft derzeit auf dem Produktionsserver aber zur Entwicklung
> arbeite ich mit 8.3.
> Leider gibt es für Debian Etch noch kein Stablepaket für 8.3 und ich
> wollte deshalb noch
> etwas abwarten.

Dann ist Debian Etch die falsche Plattform. Dafür wird es nie eine
offizielle 8.3 geben. Nach dem Feature Freeze wird die Software
Plattform nicht mehr geändert, es wird nur noch Bugfixes geben.
Die nächste Debian Version kommt dann in 1-3 Jahren heraus.

Jedoch gibt es von Debian-Maintainern bereitgestellte 8.3 Pakete.

> Das Textfeld muss darüber hinnaus noch Steuerzeichen zur
> Bildformatierung, die nicht HTML sind
> beinhalten die vom Webfrontend beim dynamischen HTML generieren
> umgesetzt werden.

Anderswo parst man das HTML einmal und legt das geparste HTML dann
ebenfalls in der Datenbank oder in einem Filesystemcache ab. Spart
Performance.

> Ist das nun konkret genug? :-)

Nein. Was bringt eine Tabelle, wenn das Gesamtkonzept nicht steht?
Wie du die Tabelle besser strukturieren oder aufteilen könntest, habe
ich einige Mails vorher schon mal geschrieben und wiederhole das hier
nicht.

So, ich gehe mal Sonne genießen

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors

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

Re: [PATCHES] [HACKERS] use of pager on Windows psql

Andrew Dunstan wrote:
> >>>> In fact, it looks to me like it would be much more sensible to #include
> >>>> "settings.h" and then simply test pset.notty for all platforms.
> >>>>
> >>>>
> >>> Yes, we could do that but does the isatty() value ever change while psql
> >>> is running? When you do '\g filename' does stdout then have isatty as
> >>> false?
> >>>
> >>>
> >> Good point. I think the best thing would just be to remove the #ifndef
> >> WIN32 /

Re: [PATCHES] libpq object hooks (libpq events)

On Sat, May 17, 2008 at 8:28 AM, Andrew Chernow <ac@esilo.com> wrote:
> Here is an updated patch for what was called object hooks. This is now
> called libpq events. If someone has a better name or hates ours, let us
> know.

Let's decide where to go with this. We have no objections to pushing
this back to the next fest (tom's comments on the wiki were noted).
If that's the case though, we would like to wrap up a consenus on the
general implementation in the meantime. Just give us a heads up and
I'll update the wiki.

merlin

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

Re: [HACKERS] odd output in restore mode

Dave Page wrote:
> On Tue, May 13, 2008 at 5:11 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
>
>> Andrew Dunstan wrote:
>>
>> > I would be very surprised if xcopy did not exhibit the same
>> > preallocating behaviour as copy.
>>
>> I, on the other hand, would not say anything until someone tried it, and
>> then wouldn't be surprised if it behaved either way :-)
>>
>
> It pre-allocates the space as copy does. And yes, I did test :-p
>
>
>

Dave,

I don't know how you tested, but could you please repeat the test with
GnuWin32's cp.exe? If it doesn't preallocate the space then I think our
way forward is reasonably clear:

. we recommend its use for Windows archive_command settings
. we provide the delay kluge as switchable behaviour on Windows instead
of having it always on.

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

[GENERAL] Postgresql service stops every few minutes on Vista Biz

Hi All,

I'm having difficulties with Postgres lately. Postgres service for (pgsql
8.2 )kept stopping every few minutes.
Then I read a note somewhere saying that there is a bug in 8.2 which causing
the problem.
I switched to 8.3 and have the same problem.

What I found so far is that the last event before stopping is "loaded
library LOG: loaded library "$libdir/plugins/plugin_debugger.dll" which I
see it in windows logs (events) for applications.

Any hint or idea to solve this problem is appretiated.

Regards,

Ali
--
View this message in context: http://www.nabble.com/Postgresql-service-stops-every-few-minutes-on-Vista-Biz-tp17300359p17300359.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Sunday, May 18, 2008

Blog Archive